I have two identical servers (in terms of hardware), they are both standard installations of windows server 2008 r2, with minimal software installed (basically my code and required stuff like jvm etc).

On the one server, I am running sql server 2005, on the second server postgresql 9.1. The difference in performance b/n these 2 servers is staggering, it's so bad on postgresql that I'm regretting my initial "let's use postgresql instead of paying for the sql server license" speech to my boss. We're talking differences of 30 seconds vs 15 mins for the same command, and it's not just this one command, it's any query or command I throw at it. They both have pretty much the same data (records were inserted in different order), and both databases have the exact same structure / indexes etc.

But I'm hoping it's just a matter of performance tuning. The thing is, sql server is pretty much using all 32 gigs of ram on the server, whereas postgresl is using nothing, definitely less than a gig though I haven't actually figured it out in fine detail.

How do I get postgresql to use 20+ gigs of ram? These servers were built specifically for this database stuff, so any ram not in use by the database and supporting processes is wasted in my opinion.

  • 4
    Did you change anything to the initial tuning? Step1: SET effective_cache_size=18G; (the default setting is extremely low) BTW: assuming this is a 64 bit machine (no PTE)
    – wildplasser
    Commented May 28, 2012 at 21:12
  • 1
    You're really not giving us enough to help a lot. Other than "It's slow" we don't know much about your dataset, how you're accessing it, what types of queries are generally running slowly, what you've already done to tune (and possibly mis-tune) your server. Heck, on a linux machine with lots of cores and memory channels, you can get crappy performance long before you've installed postgresql. Are you CPU or IO bound? What non-default settings do you have already? What kinds of queries are slow? Commented May 29, 2012 at 4:53
  • 3
    Postgres doesn't "use ram" the way you speak of it. It relies on the OS file system page cache for the bulk of its caching, so when you watch ram usage on a system running postgres you typically see many GBs in use by OS buffers/cache, and individual postgres backend processes using only a few to a few tens of MBs each.
    – dbenhur
    Commented Jun 1, 2012 at 22:34
  • 1
    See this link: tekadempiere.blogspot.ae/2014/09/… And find your resource based conf values from here: pgtune.leopard.in.ua
    – Sajeev
    Commented Jan 13, 2017 at 11:09
  • related question, maybe of interest: stackoverflow.com/questions/47311485/… Commented Nov 15, 2017 at 15:52

4 Answers 4


There are many tweakable constants, initialised via postgres.conf. The most important ones are:

  • max_connections: the number of concurrent sessions
  • work_mem : the maximal amount of memory to be used for intermediate results such as hash tables, and for sorting
  • shared_buffers the amount of memory dedicated to 'pinned' buffer space.
  • effective_cache_size the amount of memory assumed to be used by the OS's LRU buffers.
  • random_page_cost : an estimate for the relative cost of disk seeks.

max_connections should not be set higher than needed, connections cost resources even when idle; in most cases a connection would spend more time waiting inside than waiting outside. (at the price of concurrency) A nice rule-of-thumb formula is "number of spindles+number of processors+X"

work_mem is tricky: is can be applied to every subquery, so a query with 5 HASHJOINS might cost 5*work_mem. And for worst-case scenarios, you should also think of multiple sessions consuming this amount (again a reason to keep max_connections low).

shared_buffers is (IMHO) overrated. Normally it is advised to set it to about 1/4...1/2 of all available "free" memory, but I tend to keep it low, and set effective_cache_size to all available "free" memory.

random_page_cost is the cost for a seek+read on the disk. It is relative to the sequential_disk_cost, which is 1. The default (4) for random_page_cost is set too high for modern machines and network storage, normally it can be lowered to between 2 and 1.x. For SSD disks yould even set it to 1.0, since seeking is almost for free on SSDs.

  • Excellent! I never saw the significance of effective_cache_size, always fooled around only with shared_buffers. This really made a huge difference. I run pgtune as well and it recommended 20GB of 96 to be used for shard_buffers, but 64GB for effective_cache_size. Thanks!
    – user44466
    Commented Jul 26, 2014 at 22:28
  • 1
    FWIW, I went through these and the other settings suggested in the Postgres docs, and did an analysis for our server.
    – mlissner
    Commented Nov 19, 2015 at 0:28
  • Thank you very much for the answer. Can I ask what the recommended work_mem is when the max_connections is default 100 and the server RAM is 32GB (dedicated postgres server)? I knew I need to tune this by myself based on daily queries. I am just wondering if you can tell me a "one size fits all answer" value (or a starting point value). Is 50MB too big? Thanks a lot.
    – sgon00
    Commented Jun 12, 2019 at 14:00
  • It depends on the typical concurrent activity on your machine. 100 sessions wanting 50M (on top of their 10..20M) each might fit. Or, it might not. To get an impression, monitor vmstat or top. Plus:it depends on your query (and the others). Just look at the plans. Commented Jun 12, 2019 at 14:20
  • @wildplasser thank you very much for the quick reply. I found an interesting website pgtune.leopard.in.ua . I think I will use 40MB as the starting point from its suggestion and tune based on that. Cheers.
    – sgon00
    Commented Jun 12, 2019 at 15:43

Consider using PGTune to help you tune the PostgreSQL configuration.

PostgreSQL's default configuration is very conservative and that tool is meant to help with this exact situation. The documentation is a light read and using the tool is pretty straightforward.

Keep in mind that there's no need to use PGTune's exact suggestions. Playing with its settings and watching the resulting changes to the conf file will give you a better understanding of PostgreSQL's configuration and how to tweak it manually.

More info on PGTune and an alternative tool called ClusterControl: PGTune Alternatives - ClusterControl PostgreSQL Configuration

  • 9
    Last update of pgtune was in 2009, that's 5 years ago and still counting. I am wondering if its still valid for 9.1-9.2-9.3 series.
    – sorin
    Commented Feb 12, 2014 at 13:35
  • 10
    pgtune is now available online
    – Alfabravo
    Commented Dec 2, 2015 at 18:29
  • pgtune url points to another website. Commented Jul 23, 2020 at 12:47
  • @DimitriosDesyllas fixed, thanks! Commented Jul 27, 2020 at 20:59

If every query or command is running slowly I suspect that:

  • you connect to database for every query you run;
  • you have configured some kind of authentication method, which does not work and it halts your queries until this particular authentication method times out.

Could you please tell us how much time it takes to run a query like select version()? If should be instant (0,16ms on my workstation).


If EVERY query is that much slower something is terribly wrong with the server or something. In my experience each db has a few things it's better at than the other, but performance wise pgsql is easily in the same realm as mssql server.

So, what OS are you running pgsql on? What hardware? What settings have you changed already? How big is your dataset? What's an example of a poor query and the output of explain analyze (Run your query like this:

explain analyze select ...rest of query here... ;

Post the output to http://explain.depesz.com/ and post the link here.

  • 1
    Yes, every query / command is running slowly, and yes "something" is terribly wrong hence my question. The issue is that mssql is making full use of the available ram on the server (so heavy caching) whereas psql is not. I appreciate comments and advice, but you must have missed the bulk of my question and the subject line itself... I just want to know how to get psql to make use of the available ram; currently trying some suggestions listed by the others...
    – user85116
    Commented May 29, 2012 at 20:39
  • 1
    Using your RAM is NOT the problem. Postgresql relies on the OS to do most of the caching. So, it doesn't NEED to use all the RAM. Again, you missed the bulk of my point. You're giving us precious little to help you with. I drive 5000 TPS postgresql clusters for a living. You can take my advice, or keep thinking you know how pgsql works and argue. Commented May 29, 2012 at 23:00
  • @user85116, please hear Scott, we already have a workflow with MySQL that is super latency dependent, so currently MySQL is using 64GB ram to do that queries fast, whereas the same can be achieved on 2G Postgres with just materialized views. Caching all the database into RAM will not solve your problem, it just makes it less visible. If you have the same problems in DB structure Postgres will not fix it for you nor try hiding it.
    – kworr
    Commented Apr 24, 2019 at 20:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.