Tuning PostgreSQL without guesswork

The defaults are sized for a tiny machine. These notes cover the handful of settings and habits that matter on a real server, with the reasoning behind each number.

Memory

PostgreSQL has its own buffer cache and also relies on the operating system page cache. Giving everything to shared_buffers usually makes things worse, because the same pages end up cached twice.

Setting	Starting point	Why
shared_buffers	25% of RAM	PostgreSQL's own cache; the OS cache holds the rest
effective_cache_size	50–75% of RAM	only a hint for the planner, allocates nothing
work_mem	16–64 MB	per sort or hash node, per query — multiply carefully
maintenance_work_mem	512 MB – 2 GB	VACUUM, CREATE INDEX, ALTER TABLE
huge_pages	try	fewer TLB misses with large shared_buffers

A query with five hash joins can use five times work_mem. With 200 connections that adds up fast — raise it per session for reports instead of globally.

SET work_mem = '256MB';   -- only for this heavy report
SELECT ... ;
RESET work_mem;

WAL & checkpoints

Every change is written to the write-ahead log first. A checkpoint flushes dirty pages to the data files; if checkpoints happen too often, the server spends its time writing full-page images.

max_wal_size = 8GB                 # checkpoints by size happen rarely
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9 # spread writes over the interval
wal_compression = zstd             # PostgreSQL 15+

Check the log for "checkpoints are occurring too frequently" and look at pg_stat_checkpointer (PostgreSQL 17, earlier versions keep these numbers in pg_stat_bgwriter): requested checkpoints should be rare compared to timed ones.

Autovacuum

MVCC leaves old row versions behind after UPDATE and DELETE. Autovacuum reclaims them and protects against transaction ID wraparound — never turn it off; make it faster instead.

-- big, hot table: vacuum after 1% of rows change, not the default 20%
ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.01,
                         autovacuum_analyze_scale_factor = 0.02);

-- who needs vacuum right now
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

On fast disks raise autovacuum_vacuum_cost_limit (for example to 2000) so workers are not throttled, and keep an eye on long-running transactions: they prevent cleanup for the whole cluster.

Indexes

Type	Use it for
B-tree	equality, ranges, ORDER BY — the default for a reason
partial	`WHERE status = 'pending'`: small index for the rows you actually query
INCLUDE	covering index, enables index-only scans
GIN	jsonb, arrays, full-text search
BRIN	huge append-only tables ordered by time — tiny and cheap

CREATE INDEX CONCURRENTLY orders_pending_idx
  ON orders (created_at) INCLUDE (customer_id)
  WHERE status = 'pending';

Unused indexes still cost writes. pg_stat_user_indexes.idx_scan = 0 after a few weeks is a good hint to drop one.

Reading plans

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Compare estimated rows with actual rows on each node: a large gap means stale or insufficient statistics — run ANALYZE or raise the statistics target for that column. "Buffers: shared read" shows what came from disk rather than cache.

Enable pg_stat_statements to find the queries that cost the most in total, not just the slowest single run. On SSD storage set random_page_cost = 1.1 so the planner stops avoiding index scans.

Connections

Each connection is a separate backend process with its own memory. Hundreds of idle connections from an application pool waste RAM and make lock contention worse. Put PgBouncer in transaction mode in front and keep max_connections in the low hundreds.

SELECT state, count(*) FROM pg_stat_activity GROUP BY 1;
SELECT pid, now() - xact_start AS age, query
FROM pg_stat_activity WHERE state = 'idle in transaction' ORDER BY age DESC;

Set idle_in_transaction_session_timeout so a forgotten transaction cannot block vacuum for hours.