PostgreSQL Performance Tuning Playbook: A Top-Down Method for Faster Queries

Most PostgreSQL performance problems are not solved by adding hardware — they are solved by finding the one query, the one missing index, or the one misconfigured parameter that is responsible for 80% of the pain. The hard part is knowing where to look and in what order. This playbook gives you a repeatable, top-to-bottom tuning methodology: start from the workload, measure before you change anything, fix the highest-leverage layer first, and verify the result. Every step uses standard PostgreSQL tooling (pg_stat_statements, EXPLAIN (ANALYZE, BUFFERS), pg_stat_* views) so it works on self-managed PostgreSQL, RDS, Aurora, Cloud SQL, or Azure Database for PostgreSQL.

The Tuning Methodology: Work in Layers, Top-Down

The single most common mistake in PostgreSQL tuning is reaching for postgresql.conf first. Parameters are the last layer, not the first. A query doing a sequential scan over 50 million rows will be slow no matter how much memory you give it; the fix is an index, not a bigger work_mem. Work down this hierarchy in order, and stop as soon as a change moves your target metric:

The leverage drops as you descend. A rewritten query can be 1000× faster; a missing index, 100×; a parameter change, usually 2–5×. Spend your time where the leverage is.

Within any single layer, the work itself is a loop. You never change something and walk away — you measure, change exactly one thing, and verify it moved the metric you care about before touching the next.

Step 1: Measure First — Find What Is Actually Slow

You cannot tune what you have not measured. The foundation of every PostgreSQL tuning session is pg_stat_statements, the official extension that aggregates execution statistics for every normalized query. Enable it once and it pays for itself forever.

-- In postgresql.conf, then restart:
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

-- After restart, in the target database:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Now rank queries by total time spent — not average time, total time. A query that takes 5 ms but runs a million times a minute is a far bigger problem than a 2-second report that runs once an hour.

SELECT
  round(total_exec_time::numeric, 1)          AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2)           AS mean_ms,
  round(100 * total_exec_time / sum(total_exec_time) OVER (), 1) AS pct_total,
  round((shared_blks_hit * 100.0
        / nullif(shared_blks_hit + shared_blks_read, 0))::numeric, 1) AS cache_hit_pct,
  left(query, 80)                             AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

This one query tells you almost everything you need to plan a tuning session: which statements own your database time, whether they are hitting cache or disk, and whether the cost is from being slow (mean_ms) or from being called too often (calls). The Pareto principle is reliable here — the top 10 rows usually account for 70–90% of total execution time.

The wait-event view: where time is being spent

When queries are slow but the plans look fine, the bottleneck is contention or I/O waits. Sample pg_stat_activity to see what running backends are waiting on:

SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE state = 'active' AND wait_event IS NOT NULL
GROUP BY 1, 2
ORDER BY 3 DESC;

Common signatures: IO / DataFileRead means you are reading from disk (undersized cache or missing index); Lock / transactionid means lock contention (long transactions, hot rows); LWLock / WALWrite means the WAL path is the bottleneck (commit storm, slow disk).

Step 2: Read the Plan — EXPLAIN (ANALYZE, BUFFERS)

Once you have your worst offenders, look at how PostgreSQL executes them. EXPLAIN alone shows the planner's estimate; EXPLAIN (ANALYZE, BUFFERS) actually runs the query and reports real timing, real row counts, and buffer access.

You do not have to catch slow queries by hand. The auto_explain module automatically logs the full plan of any statement that exceeds a duration threshold, so the plan for your 2 AM latency spike is waiting in the log the next morning instead of being unreproducible.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= now() - interval '7 days'
  AND o.status = 'pending';

A plan is a tree, and the executor pulls rows up from the leaves to the root — so you read it bottom-up and ask where the time and rows accumulate. This is the path every row in the example query above travels:

Reading a plan is a skill, but four signals catch the vast majority of problems:

The most important habit: compare estimated rows vs actual rows at each node. The planner makes every decision — index vs sequential scan, nested loop vs hash join, join order — based on row estimates. When the estimate is wildly wrong, every downstream decision is wrong too. The fix is usually fresher statistics (ANALYZE), a higher default_statistics_target, or extended statistics for correlated columns.

Step 3: Indexes — The Highest-Leverage Fix After Query Rewrites

Most "slow database" tickets are really "missing index" tickets. PostgreSQL supports a rich set of index types and shapes; matching the right one to the access pattern is where the gains live.

Composite indexes and column order

For a query filtering on multiple columns, a single composite index usually beats two separate indexes. Column order matters: put equality columns first, then the range/sort column. The rule of thumb is equality, then range, then ORDER BY.

-- Query: WHERE status = 'pending' AND created_at >= ... ORDER BY created_at
-- Right order: equality column first, then the range/sort column
CREATE INDEX idx_orders_status_created
  ON orders (status, created_at);

Partial indexes — index only what you query

If you almost always query a small subset (e.g. only pending orders out of millions), a partial index is dramatically smaller, faster, and cheaper to maintain.

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

Covering indexes — answer the query from the index alone

An index-only scan never touches the heap. Add the columns you SELECT as INCLUDE payload so the index can satisfy the whole query:

CREATE INDEX idx_orders_covering
  ON orders (customer_id) INCLUDE (total, status);

Find missing and unused indexes

Two queries every PostgreSQL DBA should keep handy. First, tables taking sequential scans that may need an index:

SELECT relname,
       seq_scan, idx_scan,
       seq_tup_read,
       n_live_tup
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 20;

Second, indexes that are never used — pure write overhead and wasted disk that should be dropped:

SELECT s.relname AS table_name,
       s.indexrelname AS index_name,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
       s.idx_scan
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique          -- keep unique/PK constraints
ORDER BY pg_relation_size(s.indexrelid) DESC;

Step 4: Schema and Data Types

Schema decisions compound. The right type and the right partitioning strategy save memory, I/O, and planning time on every single query against the table.

• Use the narrowest correct type. int over bigint when range allows, timestamptz over storing epoch in text, text over varchar(n) (no performance difference, no arbitrary cap). Narrower rows mean more rows per 8 KB page and a higher cache hit ratio.
• Declare NOT NULL and constraints. They are not just correctness — the planner uses them. A NOT NULL column lets the planner skip null-handling branches and use indexes more aggressively.
• Partition very large tables. Native declarative partitioning (range, list, hash) lets the planner prune entire partitions and lets autovacuum work per-partition. The classic use case is time-series data partitioned by month, where old partitions can be detached and archived cheaply.
• Avoid over-indexing wide JSONB. Index the specific paths you query with expression indexes rather than slapping a GIN index on the whole document if you only ever filter on two keys.

Step 5: Memory Parameters

Now — and only now — we touch postgresql.conf. The four memory parameters below cover the majority of tuning value. The examples assume a dedicated 32 GB host.

Step 6: I/O, Planner Cost, and WAL Tuning

Two changes here have outsized impact on modern hardware.

Tell the planner you have SSDs

The default random_page_cost = 4.0 assumes spinning disks where random reads are 4× costlier than sequential. On SSD and NVMe that ratio is near 1. Leaving the default makes the planner irrationally avoid index scans in favor of sequential scans.

-- For SSD / NVMe / cloud block storage:
random_page_cost = 1.1
effective_io_concurrency = 200   -- allow concurrent async I/O on SSD

Smooth out checkpoints and WAL

Aggressive, frequent checkpoints cause I/O spikes and force full-page writes. Spreading them out reduces write amplification and latency jitter.

checkpoint_timeout = 15min        -- default 5min is too aggressive
max_wal_size = 8GB                -- let WAL grow between checkpoints
checkpoint_completion_target = 0.9 -- spread the flush over 90% of the interval
wal_compression = on              -- compress full-page writes (CPU for I/O)

Step 7: Connection Pooling — Stop Paying the Per-Connection Tax

PostgreSQL forks a full OS process per connection, each consuming several megabytes plus its own catalog cache. A few hundred mostly-idle connections waste gigabytes of RAM and add context-switch overhead. The fix is not raising max_connections — it is a connection pooler.

PgBouncer in transaction-pooling mode multiplexes thousands of client connections onto a small set of real backends — a backend is only held for the duration of a transaction, then returned to the pool. This is the standard production topology. A sane sizing for the real pool is roughly (2–4) × CPU cores, not hundreds. See the PgBouncer getting-started guide for setup.

Step 8: Autovacuum — Tune It to Run More, Not Less

MVCC means every UPDATE and DELETE leaves dead tuples behind. Autovacuum reclaims that space and refreshes the statistics the planner depends on. The instinct to "turn autovacuum down so it doesn't interfere" is exactly backwards — under-vacuumed tables bloat, indexes degrade, and statistics go stale, which makes everything slower.

For large, write-heavy tables, the default scale factors trigger vacuum far too late (default 0.2 = wait until 20% of the table is dead). Lower them so vacuum runs in smaller, more frequent passes:

-- Make autovacuum more aggressive on a hot table
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,   -- vacuum at 2% dead, not 20%
  autovacuum_analyze_scale_factor = 0.01,  -- re-analyze at 1% changed
  autovacuum_vacuum_cost_limit = 2000      -- let it do more work per cycle
);

Monitor bloat and vacuum activity directly:

SELECT relname,
       n_live_tup, n_dead_tup,
       round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

A Ready-to-Adapt Baseline Configuration

Pulling the parameter layers together, here is a sensible starting postgresql.conf for a dedicated OLTP host with 32 GB RAM, 8 vCPU, and NVMe storage. Treat it as a baseline to measure against — not a finish line.

# --- Memory ---
shared_buffers = 8GB
effective_cache_size = 24GB
work_mem = 32MB
maintenance_work_mem = 2GB

# --- Planner / I/O (NVMe) ---
random_page_cost = 1.1
effective_io_concurrency = 200
default_statistics_target = 100

# --- WAL / Checkpoints ---
wal_compression = on
max_wal_size = 8GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9

# --- Connections (front with PgBouncer) ---
max_connections = 200

# --- Autovacuum (more aggressive than defaults) ---
autovacuum_max_workers = 4
autovacuum_vacuum_cost_limit = 2000
idle_in_transaction_session_timeout = 300000   # 5 min, in ms

Tuning Workflow Checklist

Frequently Asked Questions

What should I set shared_buffers to in PostgreSQL?

Start at roughly 25% of total RAM (e.g. 8 GB on a 32 GB host). PostgreSQL relies on the operating system page cache as a second tier, so pushing shared_buffers much higher than 40% rarely helps and can starve the OS cache. Pair it with effective_cache_size set to ~60–75% of RAM so the planner knows how much total cache is realistically available.

How do I find slow queries in PostgreSQL?

Enable the pg_stat_statements extension, then rank queries by total_exec_time (cumulative time, not average). The top 10 rows almost always account for 70–90% of all database time. For finished or historical queries, analyze your server logs with pgBadger or enable auto_explain to capture plans of statements that exceed a duration threshold.

Why is PostgreSQL not using my index?

The three most common causes are: (1) stale statistics — run ANALYZE so the planner has accurate row estimates; (2) default random_page_cost = 4.0 on SSD/NVMe, which makes the planner over-value sequential scans — lower it to 1.1; and (3) the filter is not selective enough, so a sequential scan is genuinely cheaper. Confirm with EXPLAIN (ANALYZE, BUFFERS).

Should I increase work_mem to make queries faster?

Carefully. work_mem is allocated per sort/hash node, per concurrent query — not once per connection — so a high global value multiplied across many connections and operations can trigger out-of-memory kills. Keep the global value modest (32–64 MB) and raise it locally with SET work_mem = '256MB' inside specific heavy analytical jobs.

Does turning autovacuum off improve performance?

No — it does the opposite. Disabling or throttling autovacuum lets dead tuples accumulate (table and index bloat) and lets statistics go stale, which makes the planner choose worse plans. The correct move for write-heavy tables is to tune autovacuum to run more often by lowering autovacuum_vacuum_scale_factor and raising autovacuum_vacuum_cost_limit.

Tuning PostgreSQL with JusDB

A tuning playbook gives you the method; production tuning gives you the edge cases — lock storms at 2 AM, replication lag that only appears under checkpoint load, bloat from a forgotten long-running transaction, query plans that flip the moment statistics drift. JusDB's PostgreSQL DBA team does this work day in and day out across self-managed and managed (RDS, Aurora, Cloud SQL, Azure) PostgreSQL fleets: query and index optimization, configuration tuning, autovacuum and bloat management, connection-pooling architecture, and 24×7 production support.

If you want a second set of eyes on a slow cluster — or a standing partner to keep it fast as it grows — talk to the JusDB team. To go deeper on the internals behind this playbook, read the PostgreSQL Architecture Deep Dive, and to set up continuous visibility, see PostgreSQL monitoring with Prometheus and postgres_exporter.