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
