PostgreSQL Performance Tuning Guide [2026 Cheat Sheet]

PostgreSQL 18 gives operators better visibility into I/O, WAL, and plan behavior, but the winning workflow is still the same: measure, verify, then change one lever at a time. This cheat sheet is built for fast lookup on live systems and pairs well with the official docs for EXPLAIN, pg_stat_statements, and runtime resource settings.

Quick Start

Use this order in production: check top queries, inspect actual plans, confirm buffer and I/O pressure, review bloat and autovacuum, then adjust configuration. If you need to share plans or logs externally, redact sensitive literals first with TechBytes' Data Masking Tool.

• Top offenders: pg_stat_statements, pg_stat_activity, pg_stat_io.
• Plan truth: EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS).
• Maintenance truth: dead tuples, vacuum lag, index bloat, stale stats.
• Config last: shared_buffers, work_mem, effective_cache_size, autovacuum thresholds, planner costs.

Live Command Filter

Filter by task, command, or keyword. Try terms like vacuum, buffers, locks, index, or autovacuum.

SELECT queryid,
       calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       rows,
       shared_blks_hit,
       shared_blks_read,
       temp_blks_written,
       query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, SUMMARY)
SELECT *
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 50;

SELECT pid,
       usename,
       state,
       wait_event_type,
       wait_event,
       now() - query_start AS runtime,
       query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;

SELECT backend_type,
       object,
       context,
       reads,
       read_bytes,
       writes,
       write_bytes,
       writebacks,
       fsyncs
FROM pg_stat_io
ORDER BY reads DESC, writes DESC
LIMIT 30;

Query Profiling

Keep the mental model simple: query tuning is usually about row count accuracy, access path choice, and memory pressure.

• Rows estimate far from actual: run ANALYZE, raise per-column statistics, or rewrite predicates.
• Seq Scan unexpectedly chosen: confirm selectivity, table size, correlation, and whether the index matches filtering and ordering.
• Temp files or temp blocks climbing: a sort or hash is spilling; revisit work_mem carefully.
• High shared_blks_read: likely cache misses or cold data.
• High WAL bytes: bulk writes, updates, or index maintenance may be the real cost center.

ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
ALTER SYSTEM SET compute_query_id = 'auto';
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

pg_stat_statements is the baseline extension for fleet-level tuning. For slow-query logging without manually running plans, use auto_explain.

LOAD 'auto_explain';
SET auto_explain.log_min_duration = '250ms';
SET auto_explain.log_analyze = on;
SET auto_explain.log_buffers = on;
SET auto_explain.log_wal = on;

Use session-level auto_explain first. Global logging is powerful, but noisy if you turn it on without a threshold.

Indexes and Access Paths

Index tuning is about matching the query shape, not following a checklist. Build the smallest index that satisfies the hottest predicate, join, and ordering pattern.

• B-tree: default for equality, range, and ordered lookups.
• INCLUDE columns: good for index-only scans when a few extra columns avoid heap reads.
• Partial indexes: excellent when the workload touches a narrow slice of rows.
• Expression indexes: useful when the query applies a function and that shape is stable.
• GIN / GiST / BRIN: pick by data type and data distribution, not by habit.

CREATE INDEX CONCURRENTLY idx_orders_customer_created
ON orders (customer_id, created_at DESC)
INCLUDE (status, total_amount);

Use CREATE INDEX CONCURRENTLY on live systems to avoid blocking writes. It takes longer and does more work, but it is the safer rollout path.

CREATE INDEX CONCURRENTLY idx_jobs_pending
ON jobs (scheduled_at)
WHERE status = 'pending';

A partial index is often the highest-ROI fix for queue tables, status-driven workflows, and soft-delete patterns.

Index Red Flags

• Too many overlapping indexes on write-heavy tables.
• Leading column does not match the real filter pattern.
• Covering indexes added everywhere without measuring heap-read savings.
• Unused indexes left in place because nobody audits pg_stat_user_indexes.

Vacuum and Maintenance

Many production incidents that look like slow SQL are really bloat, dead tuples, or autovacuum lag. If plans degrade over time, maintenance is usually the missing variable.

SELECT relname,
       n_live_tup,
       n_dead_tup,
       last_vacuum,
       last_autovacuum,
       last_analyze,
       last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

VACUUM (ANALYZE, VERBOSE) orders;

REINDEX INDEX CONCURRENTLY idx_orders_customer_created;

Maintenance Rules That Age Well

• Lower autovacuum_vacuum_scale_factor on high-churn tables instead of waiting for table-wide defaults.
• Use ANALYZE after major data shape changes, bulk loads, or partition churn.
• Treat VACUUM FULL as a last resort because it rewrites the table and needs stronger locking.
• Prefer REINDEX CONCURRENTLY for bloated production indexes when write traffic must stay online.

Configuration Knobs

These settings matter, but only after the query and index layer are under control. Think of them as force multipliers, not substitutes for good access paths.

shared_buffers = 8GB
work_mem = 64MB
maintenance_work_mem = 1GB
effective_cache_size = 24GB
random_page_cost = 1.1
effective_io_concurrency = 200
track_io_timing = on
track_wal_io_timing = on

What Each Knob Usually Means

• shared_buffers: shared cache size. Bigger is not always better; oversizing can increase checkpoint pressure.
• work_mem: per operation, not per session. Large values can explode memory under parallel sorts and hashes.
• maintenance_work_mem: helps index builds, vacuum, and DDL maintenance.
• effective_cache_size: planner hint for available OS plus PostgreSQL cache.
• random_page_cost: planner cost for non-sequential reads. SSD-backed systems often run lower than the old defaults.
• effective_io_concurrency: helps the planner and executor on storage that can sustain parallel I/O.
• track_io_timing and track_wal_io_timing: essential when you need evidence instead of guesses.

Safe Configuration Workflow

1. Change one knob at a time.
2. Capture before-and-after EXPLAIN, query stats, and I/O counters.
3. Prefer session-level testing before cluster-wide rollout.
4. Write down the workload assumption that justified the change.

Advanced Usage

These techniques matter when the basics are already in place and you need another step-function improvement.

• Partitioning: use it for operational isolation, retention, and pruning, not as a universal speed trick.
• Parallel query: helps scans and aggregates, but only when row counts, CPU, and memory patterns support it.
• JIT: useful for some CPU-heavy analytical paths, often irrelevant for OLTP latency.
• Connection strategy: too many active backends can destroy cache locality and increase context switching.
• Prepared statements: watch for generic-plan pathologies on skewed predicates.

SET work_mem = '256MB';
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT customer_id, sum(total_amount)
FROM orders
GROUP BY customer_id
ORDER BY sum(total_amount) DESC
LIMIT 100;

Session-scoped tests are the fastest way to validate whether a memory or planner change affects the exact workload you care about.

SELECT schemaname,
       relname,
       seq_scan,
       idx_scan,
       n_live_tup,
       n_dead_tup
FROM pg_stat_user_tables
ORDER BY seq_scan DESC
LIMIT 20;

Use this to catch tables where the planner is repeatedly preferring sequential scans. Sometimes that is correct; often it signals missing or mismatched indexes.

Keyboard Shortcuts

These are the fastest psql and shell-adjacent shortcuts for tuning sessions.

Final Checklist

• Step 1: Rank expensive queries.
• Step 2: Capture actual plans with buffers and WAL.
• Step 3: Fix query shape or index design.
• Step 4: Check autovacuum, analyze cadence, and bloat.
• Step 5: Tune config only after the workload is understood.