Postgres: the 12 settings that matter

Postgres has 350+ configuration parameters. The good news: at any reasonable scale, twelve of them matter. The bad news: they all interact, and the defaults are conservative for a 1990s-era machine you've never owned.

This is the cheat sheet I wish someone had given me eight years ago.

Memory#

shared_buffers — how much memory Postgres reserves for its own page cache. The default (128MB) is hilariously low. Set to 25% of RAM on a dedicated DB host. Don't go above 40%; the OS's own cache and PG's other memory areas need room.

effective_cache_size — what Postgres believes the OS page cache is. Doesn't allocate anything; it just hints the planner. Set to 75% of RAM.

work_mem — memory per sort/hash operation, per query, per node. Conservative default (4MB). Bump to 64MB for analytics workloads, 16MB for OLTP. Watch out: a query with 5 hash joins running 50 connections deep can use 5 × 50 × work_mem = 16GB. Don't crank this globally.

maintenance_work_mem — used by VACUUM, CREATE INDEX, ALTER TABLE. Higher = faster. Set to 1GB on hosts with ≥16GB RAM.

ALTER SYSTEM SET shared_buffers = '4GB';                 -- 25% of 16GB
ALTER SYSTEM SET effective_cache_size = '12GB';          -- 75% of 16GB
ALTER SYSTEM SET work_mem = '16MB';
ALTER SYSTEM SET maintenance_work_mem = '1GB';
SELECT pg_reload_conf();

shared_buffers requires a restart; the others reload.

Throughput vs `shared_buffers`#

The relationship is non-linear. You see big jumps as you go from default to ~25% of RAM, then diminishing returns:

OLTP throughput (qps) vs shared_buffers (16GB host)

128MB100
512MB380
1GB620
2GB890
4GB1,100
8GB1,180
12GB1,200

Concurrency#

max_connections — peak concurrent connections. Default 100 is fine for most workloads. Don't crank to 1000 — each connection has overhead. Use PgBouncer as a connection pooler instead.

max_parallel_workers_per_gather — workers per parallel query. Default 2. Bump to 4–8 for analytical workloads with big tables. Has no effect on OLTP.

Write durability#

wal_buffers — staging area for WAL before fsync. Default -1 (1/32 of shared_buffers). Usually fine. Bump to 64MB if you see waits on wal_insert_lock.

wal_compression — compress WAL pages. Set on (default on PG14+). CPU is cheap, disk and replication bandwidth is expensive.

checkpoint_timeout — how often to checkpoint. Default 5 min. Set to 15 min for write-heavy workloads to spread out the I/O burst.

max_wal_size — soft cap on WAL between checkpoints. Default 1GB. Bump to 4–16GB based on write volume. Higher = less frequent checkpoints = better throughput but longer crash recovery.

ALTER SYSTEM SET checkpoint_timeout = '15min';
ALTER SYSTEM SET max_wal_size = '8GB';
ALTER SYSTEM SET wal_compression = 'on';

Planner#

random_page_cost — planner's cost estimate for a random disk seek. Default 4.0 (mechanical disk). On SSD set to 1.1. The default makes the planner avoid index scans on large tables when they're often the right choice on SSD.

effective_io_concurrency — how many concurrent I/O ops the disk can handle. SSD: 200. NVMe: 400. HDD: 2.

ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_io_concurrency = 200;

Auto-vacuum#

This is the one nobody tunes and everyone should. The defaults are conservative; on a write-heavy table you'll bloat aggressively unless you tighten them.

Per-table autovacuum overrides for hot tables:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05,    -- vacuum at 5% dead tuples (default 20%)
  autovacuum_analyze_scale_factor = 0.02,   -- analyze at 2% changes
  autovacuum_vacuum_cost_limit = 1000        -- be more aggressive (default 200)
);

The most common bloat-induced perf issue: a frequently-UPDATEd row hits 100K dead tuples, the row's index lookup walks all of them. Manifests as "this query that should be 1ms is 200ms; an hour later it's 1ms again." That's autovacuum running. Tighten the thresholds so it runs sooner.

Connection pooling: PgBouncer#

Not a Postgres setting per se, but the most impactful single change for a Rails / Django / Node app talking to PG. Without PgBouncer, every request opens a connection (~10ms cost, ~10MB RSS each). With PgBouncer in transaction mode in front, your app holds 1000 "logical" connections sharing 50 "real" Postgres connections.

# pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 50

This isn't optional at scale. It's required.

Verification: how to know it worked#

After tuning, run something like pgbench or — better — replay a representative chunk of your production query log:

# Quick sanity check
pgbench -i -s 100 mydb        # build a 1.5GB test set
pgbench -c 50 -j 4 -T 60 mydb # 50 connections, 60 seconds

Compare TPS before and after. Watch the system: iostat -x 1 should show non-saturated disk; vmstat 1 should show CPU well below 100% with no swap.

If TPS isn't moving, you're bottlenecked somewhere else — disk IOPS, app CPU, network — not on Postgres config.

Things that aren't on this list#

synchronous_commit = off — yes, faster writes. Yes, you'll lose the last second on crash. Don't do it on user data without explicit "I accept that" buy-in.
fsync = off — never. Just no.
Most enable_seqscan / enable_indexscan planner toggles — fix the indexes, not the planner.
huge_pages — small win on Linux, mostly not worth the OS-side setup.

When it's not config#

Half the "Postgres is slow" tickets are actually:

Missing indexes
Wrong indexes (composite ordering wrong)
N+1 queries from the ORM
Connection pool exhaustion
Long-running transactions blocking VACUUM
Lock contention

Tune the queries first. Tune the config second. Most "we need a bigger box" decisions disappear when one missing index gets added.