About the PostgreSQL Tune Calculator
PostgreSQL ships with defaults sized for a machine far smaller than anything you will run it on. They are deliberately conservative so the server starts anywhere, which means an untuned instance on a large machine uses a fraction of the memory available to it.
Four settings do most of the work. Shared buffers is PostgreSQL's own cache, conventionally around a quarter of system memory. Effective cache size is not an allocation at all — it tells the planner how much memory the operating system is likely to be using for cache, which changes whether it chooses an index scan or a sequential one. Work memory is per sort or hash operation, and maintenance work memory applies to vacuum and index builds.
The setting that causes outages is work memory, because it is allocated per operation rather than per connection. A generous value multiplied by many concurrent connections each running several sorts can exhaust memory on a server that looked comfortable. Size it against realistic concurrency, not against a single query.
Frequently asked questions
Why is shared_buffers usually recommended at about 25% of RAM?
PostgreSQL relies on the operating system's page cache as well as its own, so allocating everything to shared buffers double-caches data and starves the OS. A quarter is a well-tested starting point that leaves room for the OS cache, work memory and connections. Larger values can help write-heavy workloads, but measure rather than assume.
What does effective_cache_size actually do?
Nothing to memory allocation — it is a hint to the query planner about how much memory the OS is likely using to cache data. A value that is too low makes the planner pessimistic about index scans and choose sequential scans instead. It is one of the cheapest settings to get right because it costs no memory to raise.
How do I avoid running out of memory with work_mem?
Remember it is per operation, not per connection. One query with several sorts and hashes can allocate it several times over, and that multiplies by concurrent connections. Set a modest global value and raise it per session or per role for known heavy analytical queries rather than globally for everything.
Do these settings still matter on managed PostgreSQL?
Yes, though providers set sensible defaults from instance size, so the gains are smaller than on a self-managed server with stock configuration. Work memory and the planner-related settings are still worth reviewing for your workload. Note some providers restrict which parameters you may change.
What should I change after applying a tuning profile?
Measure before deciding anything else. Enable slow query logging and look at what is actually slow — most database performance problems are missing indexes or inefficient queries rather than configuration. Tuning turns a badly configured server into a correctly configured one; it does not fix a query doing a sequential scan over ten million rows.
Need this managed for you, not just automated?
We're also a hands-on DevOps consultancy — Kubernetes, CI/CD, and cloud infrastructure.