A performance methodology · PostgreSQL

Everything in this part has been a technique. This lesson is the order to apply them in, because most unsuccessful performance work is competent techniques applied to the wrong thing.

Step 1: define what “slow” means

Before touching the database, get a specific claim. “The application is slow” cannot be investigated; the following can.

Which operation? A page, an endpoint, a job.
How slow, against what? p95 was 200 ms, is now 4 s.
Since when? A deploy, a data volume threshold, gradually.
All the time, or sometimes? Intermittent points at plan instability, lock waits or cache effects; consistent points at volume or a plan change.
Is the database implicated at all? Application-side time is often the answer, and hours are lost assuming otherwise.

Refusing to proceed without these is not pedantry. It is the difference between measuring and guessing.

Step 2: find where the time goes

pg_stat_statements is the single most valuable extension for this. Install it before you need it.

-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT round(total_exec_time)::bigint          AS total_ms,
       calls,
       round(mean_exec_time::numeric, 2)       AS mean_ms,
       round(stddev_exec_time::numeric, 2)     AS stddev_ms,
       shared_blks_hit + shared_blks_read      AS total_blocks,
       round(100.0 * total_exec_time
             / sum(total_exec_time) OVER (), 1) AS pct_of_total,
       left(query, 80)                          AS query
  FROM pg_stat_statements
 ORDER BY total_exec_time DESC
 LIMIT 20;

Rank by total time, not by mean

A query taking 4 seconds and running 3 times a day costs 12 seconds. A query taking 40 milliseconds and running 200,000 times a day costs 8,000 seconds.

The first is what users complain about. The second is what is consuming your server.

Both are worth fixing and they are different problems, so read the table twice:

total_exec_time descending finds what the server is spending its capacity on. This is where headroom comes from.
mean_exec_time descending, filtered to calls > 100 finds what users experience. The filter matters, or a single ad-hoc query from last Tuesday tops the list.
stddev_exec_time high relative to the mean finds the unstable ones — plan flapping, lock waits, or cache dependence. These are frequently the most damaging and rarely the ones reported.

Step 3: check what sessions are waiting on

Before analysing a plan, establish that the problem is the plan.

SELECT wait_event_type, wait_event, state, count(*)
  FROM pg_stat_activity
 WHERE backend_type = 'client backend'
 GROUP BY 1, 2, 3
 ORDER BY count(*) DESC;

Dominant `wait_event_type`	Points at	Part
`Lock`	Blocking, DDL, contention	IX
`IO`	Reads that are not cached	XI
`LWLock`	Internal contention, often buffer mapping	XI
`Client`	The application is not reading results	IV
`null` while `active`	Genuinely computing	This part

If sessions are waiting on Lock, no amount of query tuning helps. This step takes ten seconds and routinely redirects an entire investigation.

Step 4: examine one plan

Take the top query from step 2 and get its plan — from auto_explain if it is running in production, otherwise EXPLAIN (ANALYZE, BUFFERS) in the safe form from lesson X-03.

Then apply the procedure from lesson X-02:

Read Buffers at the top. That is the total work.
Find the node with the largest Buffers.
Check its loops.
Compare its estimate to its actual — bottom up, first divergence.
Look at timing last.

Step 5: one hypothesis at a time

Write it down before testing it. A hypothesis has a predicted observation:

“The orders sequential scan is the cost. An index on (status, created_at) should reduce buffers from 16,667 to under 500.”

Then test exactly that. Changing three things at once and observing an improvement teaches you nothing about which of them mattered, and leaves two changes in production that may be costing you.

Tuning settings before understanding the query

The sequence that wastes the most time: a query is slow, so shared_buffers, work_mem, random_page_cost and effective_cache_size are all adjusted, the server is restarted, and the query is still slow.

Configuration matters and it is almost never the first answer. In rough order of how often each is the actual cause:

A missing or wrong index — including an index the planner cannot use because of an operator class or a cast.
A bad estimate producing a nested loop, from lesson X-04.
The query itself — an unnecessary join, a subquery that could be a join, SELECT * pulling TOASTed columns nobody reads.
Bloat making a scan read several times the pages it should.
Lock contention, which is not a query problem at all.
Configuration.

Work the list in order. When configuration genuinely is the answer, the evidence from steps 2 to 4 will say so — a plan spilling to disk points at work_mem, and a plan avoiding indexes on flash points at random_page_cost.

Step 6: verify with a comparison

-- before
EXPLAIN (ANALYZE, BUFFERS) SELECT …;

-- make exactly one change

-- after
EXPLAIN (ANALYZE, BUFFERS) SELECT …;

Compare buffers, not seconds. Timing varies with cache state, with concurrent load, and with what the storage happens to be doing. Buffer counts are deterministic for a given plan and data set, and a change that reduces buffers has reduced work on any hardware.

Then confirm it in production against the same measurement that identified the problem:

SELECT pg_stat_statements_reset();
-- wait for a representative period
-- re-run the step 2 query and compare total_exec_time for that statement

An improvement that shows in a hand-run EXPLAIN and not in pg_stat_statements has not helped the workload, whatever it did to the test case.

The extensions to install before the incident

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements,auto_explain'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pgstattuple;

ALTER SYSTEM SET auto_explain.log_min_duration = '1s';
ALTER SYSTEM SET auto_explain.log_analyze = on;
ALTER SYSTEM SET auto_explain.log_buffers = on;
ALTER SYSTEM SET auto_explain.log_timing = off;
ALTER SYSTEM SET auto_explain.log_nested_statements = on;

ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM SET log_min_duration_statement = '1s';
ALTER SYSTEM SET log_autovacuum_min_duration = '1s';
ALTER SYSTEM SET log_line_prefix = '%m [%p] %q%u@%d/%a ';

Both pg_stat_statements and auto_explain require a restart to load, which means they cannot be added during the incident that needs them. Both carry some overhead, which is worth measuring on your own workload rather than taking on faith, and in exchange they turn “the database was slow last night” from an unanswerable question into a query.

log_timing = off alongside log_analyze = on removes the per-node clock overhead from lesson X-02 while keeping row counts and buffers.

What pg_stat_statements normalises, and the traps in reading it

Statements are aggregated by a hash of their parsed form with constants replaced by placeholders, so WHERE id = 1 and WHERE id = 2 are one entry. That is what makes the view useful, and it has consequences.

One entry can contain plans that differ. A skewed column produces fast executions and slow ones under the same queryid, and the mean describes neither. stddev_exec_time is what reveals it, which is why it belongs in the query above.

IN lists of different lengths used to be different statements. IN ($1, $2) and IN ($1, $2, $3) hashed differently, so an application building variable-length lists scattered its cost across many entries and none reached the top of the list. PostgreSQL 18 changes this, per the release notes:

Have query id computation of constant lists consider only the first and last constants … Jumbling is used by pg_stat_statements.

The same release also parameterises SET values for the same reason, so a workload issuing SET with varying constants no longer bloats the view. Both are worth knowing when comparing figures across a major version upgrade: a statement that appears to have arrived from nowhere after upgrading to 18 may simply be the sum of entries that were previously scattered.

Three columns added in 18 are directly useful here, and all three were confirmed present on pg_stat_statements 1.12 on a live 18.6 cluster: parallel_workers_to_launch, parallel_workers_launched and wal_buffers_full.

SELECT calls, parallel_workers_to_launch, parallel_workers_launched,
       wal_buffers_full, left(query, 60) AS query
  FROM pg_stat_statements
 WHERE parallel_workers_to_launch > 0
 ORDER BY parallel_workers_to_launch - parallel_workers_launched DESC;

parallel_workers_to_launch against parallel_workers_launched is the aggregate form of the Workers Planned versus Workers Launched diagnostic from lesson X-01 — a persistent gap means the worker pool is undersized for the workload, which a single EXPLAIN could only ever hint at.

The view has a fixed size. pg_stat_statements.max defaults to 5,000. When exceeded, the least-executed entries are evicted, so a workload with many distinct statements loses exactly the long tail you might be looking for. pg_stat_statements_info.dealloc counts how often this has happened — a non-zero and growing value means the view is lossy.

SELECT * FROM pg_stat_statements_info;

Counters are cumulative since the last reset. A ranking that covers six months and one bad afternoon is dominated by the six months. Reset deliberately before a measurement window, and record when you did.

Time is execution time. total_plan_time is tracked separately and only when pg_stat_statements.track_planning is on, which is off by default. On a workload of many short statements, planning can be a substantial share of the total and be entirely invisible until that is enabled.

What to take from this

Get a specific claim before investigating. “Slow” is not one.
Rank by total_exec_time for capacity, by mean_exec_time for experience, by stddev for instability.
Check wait_event_type before analysing a plan. Ten seconds, and it redirects investigations.
One hypothesis, one change, one measurement.
Compare buffers, not seconds. Then confirm in pg_stat_statements.
Install pg_stat_statements and auto_explain now; they need a restart.

Cross-course references

Linux for Production Sysadmins — Part XXXVIII (Linux performance fundamentals) and Part LXXIX (Troubleshooting methodology) cover the same discipline one layer down: measure, form a hypothesis, change one thing, measure again.
Observability for Production Sysadmins — Part XCVIII (Troubleshooting methodology) and Part CII (Slow queries) cover starting from the user-visible latency rather than from the database’s own opinion of itself.
Git, CI/CD & GitOps — Part CXIV (Deployment markers) covers putting deploys on the same timeline as the latency, which answers “what changed” faster than any query does.