0

I have been trying to figure out how to go about configuration of a PostgreSQL instance that will have 64GB ram (which is far larger than the database) and if I should opt for more cores or faster cores.(Assuming passmark total is within 15-20% difference)

Some extra details: The most intensive READ will be JSONB through lots of UNION. The OS in this case is CentOS 6.

closed as off-topic by RolandoMySQLDBA, Paul White, Max Vernon, Vérace, dezso Jul 15 '15 at 7:44

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Shopping list question - questions about which tool, library, product or resource you should use are off-topic here because they quickly become obsolete and often are just about the preferences of the answerer. If you have an issue with or a question about a specific tool, please revise your question to conform to that scope." – Paul White, Max Vernon, Vérace
If this question can be reworded to fit the rules in the help center, please edit the question.

6

Memory related settings

You've already addressed the key bottleneck for read heavy applications, that is, having sufficient RAM for caching. Just make sure you've set appropriately high values for shared_bufferes, work_mem, maintenance_work_mem, and effective_cache_size within your postgresql.conf file.

Actually, there's a litany of good info in this DBA.SE thread.

Also, here's a link to the Postgres Docs about these settings, amongst others.

Processor choice

As for choice of processors, it is important to keep in mind that Postgres is not capable of using more than one CPU per database process. If there will be many concurrently connected users, a multi-core CPU can spread out the user queries amongst the cores, but cannot parallelize on a per-query basis. Effectively, you will only get to truly leverage a benefit from multiple cores when there are multiple connected users.

I guess, to answer succinctly, that if you have only one (or a small number) of users, favor faster cores, whereas if you'll have many concurrent users, you could benefit from a little of both.

Refer to this DBA.SE thread which covers some of this info.

If you are going to have many concurrently users, I would suggest you also look in to connection pooling.

  • 2
    Agreed. Depends almost totally on the workload. Big complex queries: fewer faster cores. Lots of concurrent simple queries: more faster cores. If in doubt, go for fewer faster cores, since you can queue up high-query-rate workloads via something like PgBouncer, but you can't (unfortunately) make one query use more than one core in PostgreSQL. (Yes, I know about PL/Proxy, but you've got to be desperate to go that way). – Craig Ringer Jul 15 '15 at 4:05
  • 1
    9.6 and later can use multiple cpus for one query. postgresql.org/docs/9.6/static/parallel-query.html – Gajus Jun 3 '18 at 12:45

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