I have a client who has a legacy application using a Postgres DB. They have SEVERE performance issues, so they figured it might be a good idea to scale their DB server to the GCP's max. Performance somewhat improved, but only slightly. During investigation, I found that the problem was never really related to the DB in the first place...

Now there's a question of scaling down the DB, because the client is clearly overpaying. I can estimate approx how much RAM the DB needs by inspecting pg_buffercache and pg_stat_statements - basically focusing on how much of the shared buffer I need to run the queries mostly off-RAM and slapping up to work_memory X max_connections on top.

But how do I estimate how many CPUs I need? I know "the more the better", but perhaps the client can get away with fewer CPUs (and less expenses) and still very good performance.

Some articles on the internet suggest that CPUs are mostly used as parallel workers. How can I know if I need a few more workers and when I have enough?

Sure, I could do a change-and-test-see-what-happens method, but that's incredibly time-consuming.

Are there any rules of thumb or metrics I could resort to to establish a reasonable estimate of how many CPUs / parallel workers I need?

P.S. I'm not a DBA, just a humble DevOps trying to learn DB stuff

  • 4
    PostgreSQL doesn't have a rich set of tools for this. It relies on you using your system tools (top, sar, iostat, vmstat, window's process monitor, or whatever monitoring solution your cloud provider provides). There are also third-party tools designed to monitor PostgreSQL, but I don't know which ones might be best for this task.
    – jjanes
    Nov 9 at 16:19
  • 2
    To add a bit to what jjanes comment said; if you have access to the server and just watch the CPU utilization for a period of time, and you see it is using say10% of the CPU on average, 20% max, well you could likely cut the CPUs in half. This is just a wild guess; it would depend a lot on current hardware, OS, etc. I'm familiar with Oracle on Linux servers, so YMMV. Nov 10 at 3:15
  • 1
    But then the question is: is the cpu% usage low because DB does not need more, or is it low because it's capped it to use too few processes and it's actually choking saturating them completely "thinking" it can only use 8 processes (8 CPUs) when there are 20+more available?
    – netikras
    Nov 10 at 7:07
  • 1
    Unless you have a misconfigured NUMA machine, PostgreSQL will use all CPU cores there are. Nov 10 at 7:37


Your Answer

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

Browse other questions tagged or ask your own question.