PostgreSQL Performance Tuning

PostgreSQL is fast by default, but production performance comes from understanding how it executes queries and maintains itself. Most "Postgres is slow" problems trace to a handful of causes: a query the planner runs badly (usually a missing index), autovacuum falling behind, too many connections, or configuration left at conservative defaults. This page is the practical toolkit for diagnosing and fixing them.

The central skill is reading what Postgres actually does rather than guessing. EXPLAIN ANALYZE shows the real execution plan and timings; from there, most fixes are targeted — add the right index, help the planner with better statistics, tune a parameter, or fix a vacuum problem — rather than shots in the dark.

TL;DR

Quick Example

EXPLAIN ANALYZE reveals why a query is slow — here, a sequential scan that an index would eliminate:

The plan told you exactly what was wrong (scanning 500k rows to return 3) and the fix confirmed it.

Core Concepts

Reading EXPLAIN ANALYZE

EXPLAIN shows the planner's chosen plan and its estimates; EXPLAIN ANALYZE actually runs the query and adds real timings and row counts. What to look for:

Reading plans is the single most useful performance skill — it replaces guessing with evidence.

MVCC, Dead Tuples, and Autovacuum

Postgres uses MVCC (Multi-Version Concurrency Control): an UPDATE doesn't overwrite a row — it writes a new version and marks the old one dead. This is why readers never block writers, but it means dead row versions accumulate. Autovacuum runs in the background to reclaim that dead space and update planner statistics.

When autovacuum can't keep up (very high write/update rates, long-running transactions holding old versions), tables bloat — they grow with dead tuples, queries slow, and indexes lose efficiency. Symptoms: a table far larger than its live data, degrading query times, n_dead_tup climbing in pg_stat_user_tables. Fixes: tune autovacuum to run more aggressively on hot tables, avoid long idle-in-transaction sessions, and reclaim severe bloat with maintenance.

Statistics and the Planner

The planner chooses plans using statistics about your data (row counts, value distributions). Stale stats — after a big data load or lots of changes — lead to bad plans (the classic estimate-vs-actual mismatch). ANALYZE refreshes them; autovacuum normally does this, but a manual ANALYZE after bulk changes helps immediately.

Configuration That Matters

Postgres defaults are deliberately conservative to run anywhere. On a real server, a few parameters matter most:

⚠️ Warning: work_mem is allocated per sort/hash node, per connection — not globally. A high value times many concurrent complex queries can exhaust RAM and trigger the OOM killer. Raise it deliberately, and consider setting it per-session for heavy analytical queries rather than globally.

Connections Are Processes

Each Postgres connection is a backend process with real memory overhead. Hundreds of connections — especially idle ones from an app that opens a connection per request — waste memory and hurt performance. The answer is almost never "raise max_connections"; it's a connection pooler (PgBouncer) that multiplexes many app clients onto a small set of database connections. See PostgreSQL Connection Pooling.

Best Practices

Find the Real Culprits With pg_stat_statements

Enable the pg_stat_statements extension to see which queries consume the most total time, run most often, and average slowest. Optimize by evidence — the queries actually costing you — instead of the ones you assume are slow. It's the highest-leverage first step on a struggling database.

Fix the Query and Index Before the Config

Most slowness is a specific query missing a specific index, not a global config problem. Reach for EXPLAIN ANALYZE and indexing first; tune parameters after, when the plans are already sound. See Query Optimization.

Keep Autovacuum Healthy

Monitor dead tuples and autovacuum activity. For high-write tables, make autovacuum more aggressive (per-table settings) rather than disabling it. Avoid long-running and idle-in-transaction sessions — they pin old row versions and prevent cleanup, driving bloat.

Refresh Statistics After Big Changes

After bulk loads, large deletes, or major data shifts, run ANALYZE so the planner has current statistics and picks good plans. Don't wait for autovacuum if a plan just went bad after a data change.

Pool Connections; Don't Just Raise the Limit

If you're hitting connection limits, add a pooler before increasing max_connections. More backends means more memory pressure and context-switching, often making things worse. A pooler solves the actual problem.

Common Mistakes

Tuning Config Before Reading the Plan

Cranking work_mem Globally

Setting a large global work_mem looks helpful until concurrent complex queries each allocate it several times over and the server runs out of memory. Set it modestly globally and raise it per-session for specific heavy queries.

Ignoring Bloat and Autovacuum

Treating a steadily slowing, ever-growing table as "needs more hardware" when the cause is autovacuum falling behind. Check n_dead_tup and table size vs live rows; fix vacuuming, not just the machine.

FAQ

How do I find out why a query is slow?

Run EXPLAIN ANALYZE on it. It executes the query and shows the real plan with actual timings and row counts, so you can see where time goes — typically a sequential scan on a large table (missing index) or a big gap between estimated and actual rows (stale statistics). To find which queries to investigate across the whole database, use the pg_stat_statements extension.

What is autovacuum and why does it matter?

Because of MVCC, updates and deletes leave dead row versions behind; autovacuum is the background process that reclaims that space and refreshes planner statistics. If it falls behind — under heavy writes or long-held transactions — tables bloat with dead tuples, queries slow down, and indexes get less efficient. Keeping autovacuum healthy (and avoiding long idle-in-transaction sessions) is core to sustained performance.

Which config parameters should I tune first?

shared_buffers (Postgres's cache, ~25% of RAM is a common start), effective_cache_size (planner's cache estimate, ~50–75% of RAM), work_mem (memory per sort/hash — raise carefully, it's per-operation-per-connection), and maintenance_work_mem. But fix slow queries and missing indexes first; config tuning helps most once the plans are already sound.

Why shouldn't I just raise max_connections?

Each connection is a separate backend process with real memory cost, so many connections (especially idle ones) waste RAM and add overhead — often making performance worse, not better. Instead of raising max_connections, put a connection pooler (PgBouncer) in front to multiplex many app clients onto a small pool of database connections. See PostgreSQL Connection Pooling.

My table keeps growing and getting slower — what's wrong?

Likely bloat from autovacuum not keeping up: MVCC leaves dead row versions that aren't being reclaimed fast enough, so the table grows well beyond its live data and queries slow. Check n_dead_tup and compare table size to live-row count in pg_stat_user_tables. Make autovacuum more aggressive on that table, eliminate long-running/idle-in-transaction sessions, and reclaim severe bloat with maintenance.

Related Topics

References