Tuning guide

The configurator emits ~30 parameter changes for a typical OLTP host. This guide walks through each category — what we change, why, and when the defaults break down.

Memory & cache

shared_buffers

25% of RAM is the long-standing rule of thumb on Linux. Higher rarely helps because the OS page cache already keeps the same data warm; lower starves the buffer pool of frequently-hit pages. We cap at 32 GB — past that point, the marginal returns flatten and the OS cache wins.

OLAP and time-series workloads benefit from a larger share (~35%) because their working sets are denser. Desktops get 15% to leave RAM for the rest of the system.

work_mem

Per-connection, per-sort, per-hash. The safe global is driven by the worst-case concurrent multiplier: max_connections × work_mem × ops_per_queryshouldn't exceed available RAM. We pick a small baseline (16 MB OLTP, 64 MB OLAP) and recommend raising it locally for known-heavy queries via SET LOCAL work_mem = '128MB';.

effective_cache_size

Not an allocation — just a hint to the planner about how much data is likely cached. Setting it to ~75% of RAM biases the planner toward index scans on warm tables. Cheap to set, easy to forget.

WAL & checkpointing

The default max_wal_size of 1 GB makes PostgreSQL checkpoint frequently, generating a full-page-write storm and burning I/O. Larger values spread writes and reduce overhead — at the cost of longer crash recovery. We use 2× shared_buffers by default, capped at 16 GB.

checkpoint_timeout at 15 minutes (the documented ceiling for OLTP) is the other half of the equation — both need adjusting together.

Query planner

random_page_cost

Default of 4.0 assumes spinning rust. Flash storage makes random reads almost as cheap as sequential, so we lower it to 1.1 (NVMe) or 1.5 (SSD). The visible effect: the planner picks indexes more readily.

jit

Just-in-time compilation pays off on long analytic queries and is pure overhead on short OLTP. We default off for OLTP, on for OLAP / time-series / vector workloads.

Autovacuum

The defaults assume 2010-era hardware. We adjust four things:

• autovacuum_max_workers up from 3 to 6+ for OLTP/mixed — more parallel work on hot tables.
• autovacuum_vacuum_scale_factor from 0.2 to 0.05 — fire earlier on busy tables.
• autovacuum_analyze_scale_factor from 0.1 to 0.02 — keep stats fresh.
• autovacuum_vacuum_cost_limit from 200 to 1000 — modern flash can vacuum faster without affecting query latency.

Connections

PostgreSQL backends are processes; each one consumes a few MB of RAM at idle and competes for CPU when active. Past a few hundred backends, scalability suffers. The right answer is pgBouncer in transaction pooling mode in front of PostgreSQL — we recommend max_connections values consistent with that pattern.

Logging

Universal wins: log_checkpoints and log_lock_waits. Plus log_min_duration_statementat 1 s for production — without it, you can't see what's slow. None of these add noticeable overhead. For temp-file pressure, watch pg_stat_database.temp_files / temp_bytes rather than turning on log_temp_files — the catalog view gives you the signal without the log spam.

Apply with care

Always test in non-production first. Some settings (notably max_connections, shared_buffers, max_worker_processes) require a restart. The configurator flags these explicitly so you can plan the change.

-- example workflow
psql -f postgresql-tuning.sql       -- apply via ALTER SYSTEM
SELECT pg_reload_conf();             -- for live-reloadable params
sudo systemctl restart postgresql    -- if any [restart] params changed

Stuck? Talk to us.