Five Postgres settings we change on every new cluster

PostgreSQL ships with defaults that will start on a Raspberry Pi. That's a feature: it runs anywhere. But it means a fresh install on a 64 GB production server is using a tiny fraction of the machine, and the query planner is making decisions for hardware from another decade.

These are the five settings we look at first on every new cluster. None of them is magic, and every workload is different, so treat the numbers below as starting points to measure from, not rules.

You can change all of them with ALTER SYSTEM, which writes to postgresql.auto.conf, followed by a reload or, where noted, a restart.

1 · shared_buffers

shared_buffers is PostgreSQL's own cache of table and index pages. The default is 128 MB, regardless of how much memory the server has.

A common starting point is about 25% of the server's RAM. PostgreSQL also relies on the operating system's file cache, which is why we don't give it most of the memory: the two caches work together.

ALTER SYSTEM SET shared_buffers = '16GB';  -- on a 64 GB server
-- needs a restart to take effect

2 · effective_cache_size

This one doesn't allocate anything. It's a hint to the query planner about how much memory is available for caching data overall, PostgreSQL's cache plus the operating system's. With a realistic value, the planner is more willing to use index scans.

The default is 4 GB. Somewhere between 50% and 75% of RAM is typical.

ALTER SYSTEM SET effective_cache_size = '44GB';  -- on a 64 GB server
SELECT pg_reload_conf();

3 · work_mem

work_mem is the memory a single sort or hash operation may use before spilling to temporary files on disk. The default is 4 MB, so large sorts, ORDER BY, DISTINCT and hash joins often spill.

Be careful: this is per operation, not per connection. One complex query can run several sorts and hashes at once, and every connection can do it at the same time. A rough sanity check is:

-- worst case, roughly:
-- max_connections × (operations per query) × work_mem   must fit in RAM

We usually start between 16 MB and 64 MB, then look for queries that still spill (see the bonus section below) and raise work_mem only for those, per role or per session:

ALTER ROLE reporting SET work_mem = '256MB';  -- just for the reporting user

4 · random_page_cost

The planner estimates how expensive it is to read pages in random order (as an index scan does) compared with reading them in sequence. The default random_page_cost is 4.0, which made sense for spinning disks, where random reads really were much slower.

On SSD or NVMe storage, random reads cost almost the same as sequential ones. With the default value, the planner can wrongly prefer a full table scan over a perfectly good index. We set it close to the sequential cost:

ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();

5 · max_wal_size

Every change in PostgreSQL is first written to the write-ahead log (WAL). A checkpoint then flushes changed pages to the data files, so the old WAL can be recycled. Checkpoints happen on a timer (checkpoint_timeout, 5 minutes by default) or when the WAL reaches max_wal_size, which defaults to 1 GB.

On a busy database, 1 GB of WAL can fill up in well under five minutes. Then checkpoints happen back to back, writes come in bursts, and right after each checkpoint every changed page has to be written to WAL in full again, which creates even more WAL.

Raising max_wal_size lets checkpoints happen on the timer instead, spread out and calm:

ALTER SYSTEM SET max_wal_size = '8GB';
SELECT pg_reload_conf();

You can see whether checkpoints are triggered by the timer or forced by WAL volume in the checkpoint statistics (pg_stat_bgwriter, or pg_stat_checkpointer in PostgreSQL 17). If most of them are requested rather than timed, raise max_wal_size further. checkpoint_completion_target (0.9 by default in current versions) already spreads the writes across the interval.

Bonus: see your slow queries

Tuning without measuring is guessing. Two things we turn on everywhere:

-- every statement's runtime, call count and I/O, aggregated
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';  -- restart needed
CREATE EXTENSION pg_stat_statements;

-- log any query slower than half a second
ALTER SYSTEM SET log_min_duration_statement = '500ms';

-- log sorts and hashes that spill to disk (helps tune work_mem)
ALTER SYSTEM SET log_temp_files = 0;
SELECT pg_reload_conf();

Then the most expensive queries are one query away:

SELECT round(total_exec_time) AS total_ms, calls, round(mean_exec_time, 1) AS mean_ms, left(query, 70) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

The settings we don't touch on day one

It's just as important to know what to leave alone until you have measurements: max_connections (use a connection pooler instead of raising it), the autovacuum settings for individual tables (see our post on autovacuum), and parallel query settings. Start with the five above, measure, and change one thing at a time.