PostgreSQL configuration tuning (postgresql.conf)

The default postgresql.conf ships conservative values so PostgreSQL can start on almost any hardware. That makes the defaults a poor fit for a dedicated server, but it does not make any fixed set of numbers a "best practice." The values below are starting points for PostgreSQL 18, derived from common rules of thumb: apply them, then validate against your own workload before and after each change.

Measure before tuning

Changing GUCs without a baseline is how a slow system becomes a differently slow system. Before touching anything:

• enable pg_stat_statements (add it to shared_preload_libraries, restart, then CREATE EXTENSION pg_stat_statements;) so you can rank queries by total time and by shared_blks_read vs shared_blks_hit;
• record current latency, I/O, and checkpoint behavior, so every change can be attributed;
• change one group of settings at a time and re-measure.

SELECT query, calls, total_exec_time, mean_exec_time,
       shared_blks_hit, shared_blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

If pg_stat_statements shows the hot queries are missing indexes or doing full scans of small tables, no memory GUC will fix that. Tune what the measurements point at.

Core memory GUCs

shared_buffers

PostgreSQL's own page cache, allocated from shared memory at startup. Requires a restart.

ALTER SYSTEM SET shared_buffers = '4GB';

A common starting point is 25% of RAM on a dedicated database server. The frequently quoted "~25%, never above 40%" ceiling is folklore, not a documented limit — write-heavy workloads can benefit from more, and read-mostly workloads that already fit in shared_buffers may benefit from less. Validate larger values with pg_buffercache hit rates and end-to-end latency instead of assuming more is better.

effective_cache_size

Not an allocation — a planner hint about how much data is likely cached across shared_buffers plus the OS page cache. It mainly influences index scan vs sequential scan choices. Reloadable.

ALTER SYSTEM SET effective_cache_size = '12GB';

A reasonable starting point is 50–75% of RAM. Setting it far above actual cacheable memory makes the planner over-optimistic about index scans; measure plan changes before and after.

work_mem

Memory limit per sort, hash, or similar operation — a single complex query can consume several times this amount, across every concurrent backend. This is the GUC that causes OOM when raised carelessly.

ALTER SYSTEM SET work_mem = '16MB';

• The default 4MB forces many sorts and hashes to spill to disk; log_temp_files (see Logging) tells you whether this is happening.
• Raise it per role or per session for heavy analytical users instead of globally: ALTER ROLE analytics SET work_mem = '256MB';
• A safe global upper bound is roughly RAM × 0.25 / max_connections, and even that assumes every backend runs one big operation at a time.

maintenance_work_mem

Used by VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY, and similar maintenance operations.

ALTER SYSTEM SET maintenance_work_mem = '1GB';

Larger values speed up index builds and vacuum. Remember that parallel index builds and parallel vacuum workers each allocate their own share, so the total can be several times this value.

Connections and pooling

ALTER SYSTEM SET max_connections = 200;

Each PostgreSQL connection is a separate backend process with several MB of memory and its own scheduling cost, so max_connections is a budget, not a throughput dial. If the application needs more client concurrency than the database can serve, put a pooler in front rather than raising this number indefinitely — see PostgreSQL production stack and HA for PgBouncer trade-offs, including what breaks under transaction pooling.

SELECT count(*), state FROM pg_stat_activity GROUP BY state;

A large idle in transaction count means the application is holding transactions open; fix that in the application before raising any connection limit. max_connections requires a restart.

WAL and checkpoints

wal_compression              = on
max_wal_size                 = 4GB
min_wal_size                 = 1GB
checkpoint_timeout           = 15min
checkpoint_completion_target = 0.9

max_wal_size that is too small forces frequent checkpoints and I/O spikes; too large lengthens crash recovery and WAL replay. Values between 4GB and 16GB are common on write-heavy systems — pick from your measured WAL generation rate (pg_stat_bgwriter, pg_stat_wal), not from a table. wal_buffers defaults to -1 (auto-sized from shared_buffers) and usually does not need an explicit value.

Planner and I/O costing

random_page_cost           = 1.1     # SSD; the default 4.0 dates from the HDD era
effective_io_concurrency   = 200     # SSD/NVMe can sustain far more than the default 16
jit                        = off     # JIT helps long analytical queries; often a cost in OLTP

default_statistics_target defaults to 100 and rarely needs a global change. For specific skewed columns, raise statistics per column instead: ALTER TABLE t ALTER COLUMN c SET STATISTICS 1000; followed by ANALYZE t;.

jit defaults to on in PostgreSQL 18. If your workload is dominated by short OLTP queries, disabling it globally is a defensible starting point; enable it per session for reporting queries that actually benefit.

Three ways to change a setting

-- 1. ALTER SYSTEM — writes postgresql.auto.conf, survives restarts
ALTER SYSTEM SET work_mem = '32MB';
SELECT pg_reload_conf();

-- 2. Edit postgresql.conf directly, then
--    sudo systemctl reload postgresql

-- 3. Session or transaction scope, for one-off work
SET work_mem = '256MB';
SET LOCAL work_mem = '256MB';   -- current transaction only

Prefer ALTER SYSTEM for persistent changes: it is auditable (pg_settings shows source = 'configuration file' with the auto.conf path) and keeps hand-edited files out of the change path. Remove a setting with ALTER SYSTEM RESET name;.

Not every change applies on reload. Check the context before assuming:

SELECT name, setting, context FROM pg_settings
WHERE context IN ('postmaster', 'superuser-backend')
ORDER BY name;
-- context = 'postmaster' requires a full restart

Common restart-required GUCs: shared_buffers, max_connections, shared_preload_libraries.

Starter templates by machine size

These are starting points for a dedicated PostgreSQL 18 server on SSD/NVMe storage, to be validated with the measurement workflow above.

4 vCPU / 16 GB

shared_buffers                  = 4GB
effective_cache_size            = 12GB
maintenance_work_mem            = 1GB
work_mem                        = 16MB
max_connections                 = 100
wal_compression                 = on
max_wal_size                    = 4GB
random_page_cost                = 1.1
effective_io_concurrency        = 200
max_parallel_workers            = 4
max_parallel_workers_per_gather = 2

8 vCPU / 32 GB

shared_buffers                  = 8GB
effective_cache_size            = 24GB
maintenance_work_mem            = 2GB
work_mem                        = 32MB
max_connections                 = 200
wal_compression                 = on
max_wal_size                    = 8GB
random_page_cost                = 1.1
effective_io_concurrency        = 200
max_parallel_workers            = 6
max_parallel_workers_per_gather = 4

16 vCPU / 64 GB

shared_buffers                  = 16GB
effective_cache_size            = 48GB
maintenance_work_mem            = 4GB
work_mem                        = 64MB
max_connections                 = 500    # with a pooler in front
wal_compression                 = on
max_wal_size                    = 16GB
random_page_cost                = 1.1
effective_io_concurrency        = 200
max_parallel_workers            = 12
max_parallel_workers_per_gather = 6

PGTune generates an equivalent template from machine specs if you want a second opinion to compare against.

Logging worth enabling in production

log_min_duration_statement  = '500ms'
log_checkpoints             = on
log_connections             = on
log_disconnections          = on
log_lock_waits              = on
log_temp_files              = 0
log_autovacuum_min_duration = 0
log_line_prefix             = '%t [%p] %u@%d %a '

log_temp_files = 0 logs every temporary file creation and is the direct signal that work_mem is too small for real queries. log_autovacuum_min_duration = 0 makes autovacuum behavior auditable — combine it with the queries in autovacuum and table bloat. log_connections/log_disconnections are cheap on pooled workloads but can be noisy with very short-lived connections; adjust to taste. See Monitoring and logging for the full observability setup.

Parallel query

max_worker_processes            = 8    # total background workers, including replication
max_parallel_workers            = 6    # workers available to parallel query
max_parallel_workers_per_gather = 4    # workers a single query node can use
min_parallel_table_scan_size    = '8MB'

max_worker_processes is the global budget for all background workers (parallel query, logical replication apply workers, and extensions), so it must be at least as large as max_parallel_workers plus whatever replication and extensions need; both it and max_parallel_workers are typically sized from vCPU count. Parallelism pays off on large scans; small OLTP queries rarely trigger it once min_parallel_table_scan_size is at or above the default 8MB.

Inspect the running configuration

-- everything that differs from defaults, and where it came from
SELECT name, setting, unit, source
FROM pg_settings
WHERE source <> 'default'
ORDER BY name;

-- current value vs the value at last startup (pending restarts)
SELECT name, setting, boot_val, pending_restart
FROM pg_settings
WHERE setting <> boot_val OR pending_restart;

pending_restart = true means an ALTER SYSTEM or config edit is waiting for a restart to take effect — check it before concluding a change "did nothing."

Prompt: have AI draft a config for your hardware

Draft a tuned PostgreSQL 18 postgresql.conf.

Environment:
- Server: <vCPU> vCPU / <RAM> GB
- Storage: <NVMe SSD / cloud block storage / HDD>, ~<IOPS> IOPS
- OS: <Ubuntu 24.04 / RHEL 9>
- Workload: <OLTP / OLAP / mixed>, ~<QPS>, ~<concurrent connections>
- Features enabled: <logical replication / pgvector / PostGIS / partitioning>

Output:
1. A postgresql.conf snippet with a comment explaining each non-default choice.
2. The equivalent ALTER SYSTEM SET statements.
3. OS-level checks: vm.swappiness, huge pages, ulimits.
4. A validation plan: which pg_stat_statements / pg_stat_bgwriter / pg_stat_io queries to compare before and after rollout.

Treat the output the same way as the templates on this page: a hypothesis to verify against measurements, not a finished configuration.

Related pages

• Connection pooling, backup, and HA decisions → PostgreSQL production stack and HA
• Autovacuum tuning and bloat detection → autovacuum and table bloat
• Metrics and log pipelines → Monitoring and logging