Tracing Migration Impact with pg_stat_statements

The migration replaced two single-column indexes with one composite index, and the dashboards looked fine: overall database CPU was flat, p99 API latency moved by a few milliseconds. A week later someone noticed that the nightly export, which used one of the old indexes, now took three hours instead of twenty minutes. Aggregate metrics hide per-query changes, and migrations — index changes above all — change plans query by query. pg_stat_statements records execution statistics for every normalised query, which makes it the natural tool for asking “what did this migration change?”: snapshot before, snapshot after, and compare each query’s mean time, rows and buffer usage. This guide sets up that before/after comparison, turns it into a routine post-migration check, and uses it to confirm that dropped structure is really unused. It belongs to Migration Observability.

Symptom / Error Signatures

Per-query regressions from migrations tend to look like this:

• Overall metrics unchanged, but one batch job, report or rarely called endpoint dramatically slower.
• A query’s plan switching from an index scan to a sequential scan after an index was dropped or replaced.
• New query shapes appearing after a deploy that come from ORM changes accompanying the migration.
• Buffer reads (shared_blks_read) for a table jumping after a type change or rewrite made its rows wider.

In pg_stat_statements terms: a queryid whose mean_exec_time or shared_blks_read / calls rises sharply between snapshots.

Root Cause Analysis

pg_stat_statements normalises queries (replacing literal values with placeholders), assigns each shape a queryid, and accumulates counters: calls, total and mean execution time, rows, buffer hits and reads, and more. The counters are cumulative since the last reset, so a single reading mixes behaviour before and after the migration. Taking a snapshot before the migration and another after a representative window, and subtracting, gives clean per-query statistics for the post-migration period to compare with an equivalent pre-migration period.

Migrations affect queries through a few mechanisms: dropped or replaced indexes change plans; new indexes can attract plans that turn out worse; type changes and rewrites change row width and caching; new constraints add work to writes; and statistics may be stale right after large changes until ANALYZE runs.

Immediate Mitigation

1. Find the queries that changed most, even without a prior snapshot, by resetting the statistics right after the migration and comparing with a replica or a day-old export if you have one. With snapshots, the query is straightforward:

-- PostgreSQL · read-only · requires pg_stat_statements and two snapshot tables of the same window length
SELECT a.queryid, left(a.query, 80) AS query,
       round((b.mean_ms)::numeric, 2) AS before_ms,
       round((a.mean_ms)::numeric, 2) AS after_ms,
       round((100 * (a.mean_ms - b.mean_ms) / nullif(b.mean_ms, 0))::numeric, 0) AS pct_change
FROM stmt_after a JOIN stmt_before b USING (queryid)
WHERE a.calls > 50
ORDER BY pct_change DESC NULLS LAST
LIMIT 20;

2. Inspect the regressed query’s plan with EXPLAIN (ANALYZE, BUFFERS) on a replica, and compare with the plan before the change (from a staging copy or saved plans).

3. Restore the missing access path if an index change caused it — recreate the dropped index concurrently, as in building indexes with CREATE INDEX CONCURRENTLY — or run ANALYZE if statistics are stale after a large change.

Permanent Fix / Long-Term Pattern

Make the before/after comparison part of every migration that touches indexes, types or large tables. The pipeline takes a snapshot before the migration step and another after a fixed window (for example, 24 hours covering daily jobs), stores both, and produces a report of the top regressions and improvements, posted to the release record.

-- PostgreSQL · pipeline step before the migration · snapshot of cumulative counters
CREATE TABLE IF NOT EXISTS ops.stmt_snapshots (
  taken_at timestamptz, label text, queryid bigint, query text,
  calls bigint, total_exec_time double precision, rows bigint, shared_blks_read bigint
);
INSERT INTO ops.stmt_snapshots
SELECT now(), 'pre-release-2026-09-18', queryid, query, calls, total_exec_time, rows, shared_blks_read
FROM pg_stat_statements WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database());

The same data answers a question that matters for contract migrations: is this index, column or table still used? Before dropping an index, confirm pg_stat_user_indexes.idx_scan has not increased over a full business cycle; before dropping a column, search pg_stat_statements for queries mentioning it, as in dropping indexes online without blocking queries. Keep pg_stat_statements.max large enough that rare queries (monthly reports) are not evicted before they are measured, and be aware that statistics are per server — check replicas too, since reports often run there.

Verification Checklist

• pg_stat_statements is installed on the primary and on replicas that serve reads, with a max large enough to keep rare queries.
• The pipeline snapshots statement statistics before migrations that touch indexes, types or large tables.
• A post-migration report lists per-query changes in mean time and buffer reads after a representative window.
• Regressions above a threshold create tickets automatically.
• Index and column drops are preceded by a usage check against statement and index statistics.

Frequently Asked Questions

Why not just reset pg_stat_statements before the migration? Resetting destroys the baseline you want to compare against. Snapshot the counters into a table instead, then compute deltas; reset only if you have already saved what you need.

How long should the comparison window be? Long enough to include the workloads that matter — at least a day to cover daily jobs, longer if weekly or monthly reports use the affected tables. Compare against a pre-migration window of the same length and time of week.

Does pg_stat_statements capture queries on replicas? Each server keeps its own statistics. Reports and analytics often run on replicas, so include them in the comparison, or a regression in exactly those workloads will be missed.

Can this detect that an index is unused before dropping it? Partly. pg_stat_user_indexes.idx_scan shows whether the index was used at all; pg_stat_statements shows which queries run against the table. Together, over a full business cycle, they give good evidence — but a query that runs once a quarter can still be missed.

What about MySQL? MySQL’s Performance Schema provides the equivalent in events_statements_summary_by_digest. Snapshot it before and after the migration and compare AVG_TIMER_WAIT and rows examined per digest.