PostgreSQL Production Configuration
The postgresql.conf settings that matter in production, what each one actually controls, and how to size them for your hardware and workload.
PostgreSQL ships with settings that let it start on a small machine. Nearly all of them are wrong for a production server, and a handful of them account for most of the difference.
A worked example
# --- Memory ------------------------------------------------------------
shared_buffers = 16GB # ~25% of RAM; the rest serves the OS page cache
effective_cache_size = 48GB # planner estimate of total cache; not an allocation
work_mem = 32MB # per sort/hash node, per parallel worker — see below
maintenance_work_mem = 2GB # speeds up VACUUM, CREATE INDEX, ALTER TABLE
# --- Connections -------------------------------------------------------
max_connections = 200 # sized for the pooler, not for application instances
superuser_reserved_connections = 5
# --- Write-ahead log ---------------------------------------------------
wal_level = replica # 'logical' if logical replication or CDC is needed
max_wal_size = 16GB # larger means fewer, bigger checkpoints
min_wal_size = 2GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9 # spread the flush over the interval
wal_compression = on
# --- Storage behaviour -------------------------------------------------
random_page_cost = 1.1 # NVMe/SSD: random reads are nearly as cheap as sequential
effective_io_concurrency = 200 # concurrent I/O requests the storage can serve
default_statistics_target = 100 # raise per column where estimates are poor
# --- Parallelism -------------------------------------------------------
max_worker_processes = 16 # ≈ vCPU count
max_parallel_workers = 16
max_parallel_workers_per_gather = 4
# --- Autovacuum --------------------------------------------------------
autovacuum_max_workers = 6
autovacuum_vacuum_cost_limit = 2000 # default 200 is far too slow for large tables
autovacuum_naptime = 15s
# --- Observability -----------------------------------------------------
shared_preload_libraries = 'pg_stat_statements'
log_min_duration_statement = 500ms
log_checkpoints = on
log_lock_waits = on
log_temp_files = 0 # log every temp file: a sign work_mem is too small
log_autovacuum_min_duration = 0Where the numbers come from
shared_buffers is PostgreSQL's own page cache. Around a quarter of RAM is the conventional
starting point because the operating system page cache holds the same data more cheaply for
sequential access. Raising it much higher tends to help only workloads with a large, uniformly hot
working set — measure before doing it.
effective_cache_size allocates nothing. It tells the planner how much data it can assume is
cached, which affects whether it chooses an index scan or a sequential scan. Set it to roughly
shared_buffers plus the OS page cache you expect to be available.
work_mem is the dangerous one. It is allocated per sort or hash operation, not per
query. A query with four hash joins running with three parallel workers can allocate many
multiples of it. Size it as a fraction of RAM divided by expected concurrency, and raise it for
specific sessions rather than globally:
SET LOCAL work_mem = '256MB'; -- inside a transaction, for one heavy queryrandom_page_cost defaults to 4.0, a ratio that describes spinning disks. On SSD or NVMe it
makes the planner avoid index scans it should choose. Lowering it to around 1.1 is one of the
highest-value single changes on modern storage.
max_wal_size controls checkpoint frequency. Too small means constant checkpoints and I/O
spikes; too large means longer crash recovery. Watch log_checkpoints output — checkpoints
triggered by WAL volume rather than by timeout mean the value is too small.
Tier differences
Changing settings safely
-- Which settings need a restart rather than a reload?
SELECT name, setting, unit, context
FROM pg_settings
WHERE context IN ('postmaster', 'sighup')
AND name IN ('shared_buffers', 'max_connections', 'wal_level', 'work_mem');context = 'postmaster' requires a restart; sighup requires only SELECT pg_reload_conf().
Prefer ALTER SYSTEM SET over hand-editing, so changes land in postgresql.auto.conf and are
visible in one place — then reload and verify with SHOW.