A 2026 Postgres tuning cheat sheet. The 20% of knobs and tactics that produce 80% of the perf you can squeeze without rewriting your app.

Config knobs that matter

For a 32 GB / 8 vCPU server:

# postgresql.conf
shared_buffers = 8GB              # ~25% of RAM
effective_cache_size = 24GB       # ~75% of RAM (planner hint)
work_mem = 64MB                   # per sort/hash operation
maintenance_work_mem = 2GB        # for VACUUM, CREATE INDEX
wal_buffers = 64MB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
default_statistics_target = 100
random_page_cost = 1.1            # for SSD
effective_io_concurrency = 200    # for SSD/cloud disk
max_wal_size = 4GB
min_wal_size = 1GB

Postgres 18’s async I/O (Postgres 18 ) uses these:

io_method = io_uring             # Linux 5.1+
io_max_concurrency = 16

Diagnostic queries

Slowest queries

SELECT
  substring(query, 1, 80) AS q,
  calls,
  total_exec_time,
  mean_exec_time,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Indexes that aren’t being used

SELECT s.relname AS table, i.indexrelname AS idx, idx_scan
FROM pg_stat_user_indexes i
JOIN pg_stat_user_tables s ON s.relid = i.relid
WHERE idx_scan = 0
ORDER BY pg_relation_size(i.indexrelid) DESC;

Drop indexes with 0 scans and meaningful size.

Tables with high dead tuple ratio

SELECT relname,
       n_dead_tup,
       n_live_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
ORDER BY dead_ratio DESC NULLS LAST
LIMIT 10;

If dead_ratio is high, autovacuum isn’t keeping up. Tune.

Long-running transactions

SELECT pid, now() - xact_start AS duration, state, query
FROM pg_stat_activity
WHERE state != 'idle' AND xact_start IS NOT NULL
ORDER BY duration DESC;

Long transactions block vacuum across the cluster. Investigate or kill.

Reading EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;

What to look for:

  • Seq Scan on a big table: missing index.
  • Sort Method: external merge: work_mem too low.
  • Buffers: shared read=N: cold cache; ideally shared hit=N after warmup.
  • Rows estimate vs actual: bad statistics; ANALYZE the table.
  • Filter + 1M rows removed: missing index for that filter column.

Indexing rules

  • B-tree: equality, range, ORDER BY.
  • GIN: array containment, jsonb @>, full-text search.
  • GiST: range types, geo, full-text alternatives.
  • BRIN: huge time-series; tiny indexes for time-based scans.
  • HNSW: vectors (pgvector). See pgvector Deep Dive .

Composite index column order: equality first, range last:

CREATE INDEX orders_lookup ON orders (customer_id, status, created_at DESC);
-- works for: WHERE customer_id = ? AND status = 'paid' ORDER BY created_at DESC

Partial indexes for selective predicates:

CREATE INDEX orders_pending ON orders (created_at) WHERE status = 'pending';

For deeper indexing patterns: PostgreSQL Indexing and EXPLAIN .

Vacuum

Autovacuum runs by default; tune if it can’t keep up:

autovacuum_naptime = 30s
autovacuum_vacuum_scale_factor = 0.05      # vacuum at 5% dead tuples (default 20%)
autovacuum_analyze_scale_factor = 0.02
autovacuum_max_workers = 6

Per-table override for hot tables:

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01);

Avoid VACUUM FULL in production — it locks the table. Use pg_repack instead.

Connection pooling

Postgres connections cost ~10 MB each. Don’t open thousands. Use PgBouncer in transaction mode:

pool_mode = transaction
max_client_conn = 10000
default_pool_size = 25

10k clients → 25 backend connections. Scales serverless cleanly.

Read replicas

Reads to async replicas reduce primary load. Application routing:

read_pool = create_pool(replica_dsn)
write_pool = create_pool(primary_dsn)

async def list_orders():
    return await read_pool.fetch("...")    # eventually consistent OK

async def create_order(...):
    return await write_pool.execute("...")

For read-after-write consistency, send those reads to primary.

What surprises new tuners

  • work_mem is per operation, not per query. A query with a sort + a hash uses 2 × work_mem.
  • Statistics matter as much as indexes. Stale pg_stats produces bad plans.
  • Vacuum is critical. Without it, dead tuples accumulate, indexes bloat, queries slow.
  • Connections cost. 10 connections at 10 GB RAM is fine; 1000 isn’t.
  • Cold cache is real. First query after restart is slow; pre-warm important tables.

What I’d tune first

For a slow Postgres:

  1. Run pg_stat_statements — find the top 5 slow queries.
  2. EXPLAIN each — index missing? Stats stale?
  3. Set shared_buffers + work_mem to sane values.
  4. Add a connection pooler if you don’t have one.
  5. Check vacuum — high dead tuples? Tune autovacuum.

In that order. Each step often resolves the slowness with no rewrite.

Read this next

If you want my Postgres tuning runbook, it’s at rajpoot.dev .

Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .