Home / Databases / PostgreSQL

DatabasesHow-toIntermediate

Optimization: PostgreSQL memory settings - shared_buffers, work_mem, maintenance_work_mem and effective_cache_size

PostgreSQL's defaults suit a tiny server. Size shared_buffers relative to RAM, set effective_cache_size so the planner knows about the OS cache, raise work_mem carefully because it multiplies per sort and connection, give maintenance work enough memory, and verify with buffer hit ratios and temp file statistics.

Deepak Bhardwaj2 min read

Applies toPostgreSQL 13 and later, self-managed; managed services expose most of these as server parameters with their own defaults

The main settings

Setting What it does Common starting point (dedicated server)
shared_buffers PostgreSQL's own page cache ~25% of RAM
effective_cache_size Planner's estimate of total cache (shared buffers + OS cache) 50–75% of RAM
work_mem Memory per sort/hash operation, per query node, per connection Small default; raise carefully
maintenance_work_mem VACUUM, CREATE INDEX, ALTER TABLE ADD FK Larger, e.g. 1–2 GB on big servers
autovacuum_work_mem Memory for autovacuum workers (falls back to maintenance_work_mem) Set explicitly if many workers
huge_pages Use HugePages for shared memory on Linux try or on with HugePages configured

Starting points only. Managed services often set their own defaults by instance size; check them before changing.

work_mem is per operation

Memory used ≈ work_mem × sort/hash nodes per query × concurrent queries. With 300 connections and complex queries, a large global work_mem can exhaust RAM.

Safer pattern:

-- Keep global modest; raise for reporting roles or sessions
ALTER ROLE reporting SET work_mem = '256MB';
-- or per transaction
SET LOCAL work_mem = '512MB';

Evidence

-- Temp files written (sorts/hashes spilling to disk)
SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp_size
FROM pg_stat_database ORDER BY temp_bytes DESC;

# Log each temp file above 10 MB to find the queries
log_temp_files = 10240

-- Buffer cache hit ratio per database
SELECT datname, round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_pct
FROM pg_stat_database;

A low hit ratio for OLTP points to working set larger than cache (or scans). With pg_buffercache you can see which relations occupy shared buffers.

Connections and memory

Many idle connections waste memory and CPU. Use a pooler (see the PgBouncer transformation post) so you can keep max_connections low and give each active query more work_mem.

Apply and verify

  • shared_buffers and huge_pages need a restart; work_mem and effective_cache_size reload.
  • Compare temp file volume, hit ratio and top query times (pg_stat_statements) before and after.

Warning

Raising work_mem globally to fix one slow report is a common cause of out-of-memory kills later. Raise it for the role or session that needs it.

Checklist

  • shared_buffers and effective_cache_size set for RAM
  • work_mem modest globally, raised per role/session
  • maintenance/autovacuum memory adequate
  • HugePages on Linux where possible
  • Temp files and hit ratio measured
  • Connection pooling in place

Related fixes