SQL performance failures rarely begin as outages. A query that completes in 40 milliseconds can drift toward 400 milliseconds as tables grow, value distributions shift, cache behavior changes, or the optimizer selects a different plan. PostgreSQL’s planner bases decisions on statistics and estimated costs, while EXPLAIN ANALYZE exposes gaps between estimated and actual row counts. Regression detection is therefore different from slow-query logging as the goal is to detect a meaningful change relative to established behavior before an absolute threshold is crossed.

A practical detector needs stable query identity, historical latency distributions, and enough execution context to explain change. PostgreSQL exposes most of the required primitives, so a lightweight Python and SQL service can add baselining, anomaly scoring, plan comparison, and CI enforcement without becoming a full observability platform.

Identity Before Measurement

Raw SQL text is a poor time-series key because literal values create artificial cardinality. PostgreSQL’s pg_stat_statements addresses that problem with queryid, a hash identifying normalized queries as statements with the same structure are typically grouped even when constants differ. The view also records calls, execution time, returned or affected rows, shared-buffer activity, temporary-block activity, WAL volume, and optional storage timing.

A collector can snapshot cumulative counters every minute:

INSERT INTO query_snapshot
    (captured_at, query_id, calls, exec_ms, rows_out, blocks_read, temp_written)
SELECT
    now(), queryid, calls, total_exec_time, rows,
    shared_blks_read, temp_blks_written
FROM pg_stat_statements
WHERE calls > 0;

Each interval is calculated as the delta between consecutive snapshots. exec_ms_delta / calls_delta gives interval mean execution time, while rows_out_delta / calls_delta describes result size. One distinction prevents misleading diagnoses as pg_stat_statements.rows counts rows retrieved or affected, not rows scanned internally. Scan amplification must be inferred from buffers and confirmed from plans.

Percentiles Need Real Samples

Means are insufficient because a stable average can hide a deteriorating tail. pg_stat_statements stores minimum, maximum, mean, and standard deviation, but not individual execution durations, so true p50, p95, and p99 latency cannot be reconstructed from it.

PostgreSQL logging can supply a controlled sample. log_min_duration_sample enables sampled duration logging, log_statement_sample_rate controls the selected fraction, and %Q in log_line_prefix emits the current query identifier when query-ID computation is enabled. Bind-parameter logging can be disabled with log_parameter_max_length = 0. PostgreSQL warns that logged statements can reveal sensitive data, making parameter handling part of the detector design.

compute_query_id = on
log_line_prefix = '%m [%p] query_id=%Q '
log_min_duration_sample = 0
log_statement_sample_rate = 0.05
log_parameter_max_length = 0

After sampled durations enter query_latency_sample(query_id, observed_at, duration_ms), PostgreSQL can calculate percentiles directly:

SELECT query_id,
       percentile_cont(ARRAY[0.50, 0.95, 0.99])
       WITHIN GROUP (ORDER BY duration_ms) AS latency_ms
FROM query_latency_sample
WHERE observed_at >= now() - interval '1 hour'
GROUP BY query_id;

percentile_cont computes continuous percentiles over an ordered set, keeping the calculation inside SQL rather than relying on dashboard-specific aggregation.

Baselines Should Describe Normal, Not Yesterday

A detector should compare current behavior with a representative historical window rather than one previous measurement. Workload seasonality, cache warmth, batch windows, and concurrency can make adjacent intervals legitimately different. A useful baseline can retain recent p50, p95, p99, calls, rows per call, blocks read per call, and temporary blocks per call for each query identifier and comparable time bucket.

A robust first detector does not require machine learning. Median absolute deviation provides a resistant scale estimate, and NIST documents a modified z-score based on the median and MAD, with absolute scores above 3.5 treated as potential outliers.

def modified_z(current, history):
    center = median(history)
    mad = median([abs(value - center) for value in history])
    if mad == 0:
        return 0.0 if current == center else float("inf")
    return 0.6745 * (current - center) / mad

The score should be paired with an effect-size rule and minimum sample count. A p95 increase of at least 50 percent, a modified z-score above 3.5, and sufficient executions in both windows can prevent tiny statistical movements from failing a build. The threshold is a policy choice where statistical unusualness and operational impact should remain separate signals.

Plans Explain the Regression

Detection without explanation produces another alert queue. Once a query crosses the gate, the detector should obtain a machine readable plan. PostgreSQL recommends JSON, XML, or YAML when EXPLAIN output is processed programmatically. EXPLAIN ANALYZE actually executes the statement and reports real row counts and runtimes, so it belongs on controlled replicas, staging data, or explicitly safe read-only statements rather than arbitrary production writes.

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT ...

Plan comparison should focus on structural and cardinality changes instead of raw planner cost. A switch from an index scan to a sequential scan, a nested loop repeating an inner node thousands of times, increased shared reads, new temporary-file activity, or a large estimated-versus-actual row mismatch provides evidence about cause. PostgreSQL highlights actual row counts when validating estimates, while sort and hash nodes expose memory and disk-related details.

Cardinality errors often trace back to statistics. ANALYZE samples table contents and builds most-common-value lists and histograms as larger statistics targets can improve accuracy at added analysis cost. Ordinary per-column statistics cannot represent cross-column correlation, while extended statistics can capture functional dependencies, multivariate distinct counts, and multicolumn most-common-value information.

The explanation engine can therefore emit diagnoses such as “estimated rows diverged from actual rows after distribution change,” “partition pruning disappeared,” or “physical-order correlation declined.” Partition pruning excludes partitions proven irrelevant to a predicate, while pg_stats.correlation influences the estimated attractiveness of index scans. CLUSTER can physically reorder a table according to an index, but subsequent updates do not automatically preserve that ordering.

Move the Gate Left

Production detection catches regressions before users report them and CI detection can catch a subset before deployment. A CI job can restore representative data volume, execute critical queries repeatedly, calculate latency percentiles, and compare EXPLAIN (FORMAT JSON) output with an approved baseline. Plain EXPLAIN does not execute the query, making structural checks suitable when runtime execution is unsafe.

The gate should fail on meaningful combinations rather than any plan change. A new plan can be faster, and identical plans can become slower as data or storage behavior changes. A defensible gate combines p95 or p99 degradation, row-estimation error, scan amplification, temporary-file activity, and structural changes. Production observations should update baselines only after a stable period, preventing a fresh regression from immediately redefining “normal.”

Performance Regressions Should Become Test Failures

A useful SQL regression detector is not a slow-query list with a lower threshold. Stable fingerprints connect executions across time, sampled durations preserve p50/p95/p99 behavior, cumulative statistics expose workload and storage shifts, robust baselines distinguish noise from meaningful change, and execution plans explain why the change occurred. PostgreSQL provides normalized query identifiers, sampled duration logging, planner statistics, machine-readable plans, buffer measurements, and partition and index diagnostics required for that pipeline.  The remaining engineering work is deliberately small as persist history, score regressions, capture evidence, and make unacceptable performance changes fail before they become production incidents.