Performance Tuning

Monitoring tells you something is slow. This lesson is about the levers for making it fast — finding the specific slow query, skipping unnecessary planning work, and sizing the memory Postgres has to work with.

pg_stat_statements

pg_stat_activity only shows what's running right now. pg_stat_statements aggregates every query the server has run, grouped by query shape (literal values normalized away), ranked by total time, calls, or average latency:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query, calls, total_exec_time, mean_exec_time,
     rows / calls AS avg_rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

This is almost always where real tuning starts: not by guessing, but by asking Postgres which query shape has actually cost the most cumulative time. A query that runs in 2ms but fires 50,000 times an hour can outrank one slow 2-second report that runs twice a day.

Prepared statements

A normal query gets parsed and planned fresh every single time it's sent. A prepared statement does that work once and reuses the plan on subsequent executions with different parameter values:

PREPARE find_order (int) AS
SELECT * FROM orders WHERE id = $1;

EXECUTE find_order(4821);
EXECUTE find_order(4822);

Most drivers do this transparently for parameterized queries. It matters most for simple, extremely frequent queries, where planning time is a real fraction of total execution time — for a query that's mostly waiting on I/O or scanning millions of rows, re-planning cost is noise.

Under the hood

Postgres switches from a plan tailored to each call's specific parameter values to one "generic" plan (safe for any value) after enough executions of the same prepared statement. If a query's ideal plan genuinely depends on the parameter value (e.g. a very selective vs. very common value for an indexed column), this generic-plan switch can make a previously-fast prepared query suddenly slow. This is also why connection pooling in transaction mode complicates prepared statements — the backend a client lands on next may not be the one that prepared the statement.

Key memory settings

shared_buffers = 25% of RAMtypical starting pointwork_mem = 16MBper sort/hash operationmaintenance_work_mem = 256MBfor VACUUM, CREATE INDEXeffective_cache_size = 50-75% of RAMa planner hint, not an allocation

shared_buffers — Postgres's own cache of table/index pages, separate from the OS page cache. A common starting point is ~25% of system RAM; higher isn't always better, since the OS cache backs up whatever doesn't fit here anyway.
work_mem — memory available for each sort, hash join, or hash aggregate operation before it spills to disk.
maintenance_work_mem — a separate, usually much larger budget for VACUUM and index builds, which benefit from more memory but run far less often than everyday queries.
effective_cache_size — doesn't allocate anything; it just tells the planner roughly how much memory is available for caching across shared_buffers and the OS, so it can judge whether an index scan's random I/O is likely to hit cache or hit disk.

Common mistake

work_mem is per sort/hash operation, not per query and not per connection — a single complex query with three sorts and two hash joins can use 5 × work_mem, and that multiplies again by however many such queries run concurrently. A work_mem that looks conservative at 100 connections can OOM a server at 500. Size it against worst-case concurrency, not the happy path — this is exactly the mechanism behind the OOM failure mode in Production Failure Scenarios.

Tuning, in order: find the actual slow query with pg_stat_statements, check its plan with EXPLAIN ANALYZE, fix the work_mem/index gap it reveals, then size the memory settings above for the concurrency you actually run — not before, and not the other way around. Last: what happens when tuning doesn't happen in time — Production Failure Scenarios.

pg_stat_statements

Prepared statements

Key memory settings

On this page