Alerting on Spatial Query Regressions

Problem Statement

A deploy at 14:00 makes one spatial statement forty percent slower. Nothing errors, no dashboard turns red, and the regression is discovered a week later when someone happens to compare a graph. This page builds the alert that would have caught it within an hour, extending the pg_stat_statements workflow from investigation into monitoring.

Why the Naive Approach Fails

Alerting directly on mean_exec_time from the view does not work, for a reason that is easy to miss: the counters are cumulative.

Cumulative means hide regressions Two series across a day with a regression starting at 14:00. The cumulative mean, computed since the last statistics reset three months ago, rises from 4.0 to 4.2 milliseconds — invisible. The windowed mean, computed from differenced snapshots, jumps from 4 to 11 milliseconds within one interval. The same regression, two ways of measuring it 12 ms 0 windowed mean — 4 ms → 11 ms cumulative mean — 4.0 ms → 4.2 ms deploy 14:00 A threshold on the amber line never fires. A threshold on the red line fires within one interval — and both series come from exactly the same source, differing only in whether consecutive readings are differenced.

Production-Ready Implementation

Snapshot the spatial slice on a fixed interval into a table you own:

sql
CREATE TABLE pgss_snapshot (
    taken_at        timestamptz NOT NULL DEFAULT now(),
    queryid         bigint      NOT NULL,
    calls           bigint      NOT NULL,
    total_exec_time double precision NOT NULL,
    rows            bigint      NOT NULL,
    query_sample    text,
    PRIMARY KEY (taken_at, queryid)
);
CREATE INDEX ON pgss_snapshot (queryid, taken_at DESC);
python
import psycopg

SNAPSHOT = """
    INSERT INTO pgss_snapshot (queryid, calls, total_exec_time, rows, query_sample)
    SELECT queryid, calls, total_exec_time, rows, left(query, 200)
    FROM pg_stat_statements
    WHERE query ILIKE '%ST\\_%'          -- the spatial slice only
      AND calls > 0
"""


def take_snapshot(dsn: str) -> int:
    with psycopg.connect(dsn) as conn, conn.cursor() as cur:
        cur.execute(SNAPSHOT)
        n = cur.rowcount
        conn.commit()
    return n

Differencing turns the cumulative counters into per-window rates:

sql
CREATE OR REPLACE VIEW pgss_windows AS
SELECT
    queryid,
    taken_at,
    calls - lag(calls) OVER w                                   AS window_calls,
    total_exec_time - lag(total_exec_time) OVER w               AS window_ms,
    (total_exec_time - lag(total_exec_time) OVER w)
        / nullif(calls - lag(calls) OVER w, 0)                  AS window_mean_ms,
    query_sample
FROM pgss_snapshot
WINDOW w AS (PARTITION BY queryid ORDER BY taken_at);

A negative window_calls means the statistics were reset between snapshots; filtering those out is the one piece of defensive logic this view needs.

The alert compares each window against the same hour on previous weeks:

sql
WITH current AS (
    SELECT queryid, window_mean_ms, window_calls, query_sample
    FROM pgss_windows
    WHERE taken_at > now() - interval '15 minutes'
      AND window_calls > 100                       -- ignore rare statements
),
baseline AS (
    SELECT queryid,
           percentile_cont(0.5) WITHIN GROUP (ORDER BY window_mean_ms) AS median_ms
    FROM pgss_windows
    WHERE taken_at > now() - interval '28 days'
      AND extract(dow  FROM taken_at) = extract(dow  FROM now())
      AND extract(hour FROM taken_at) = extract(hour FROM now())
      AND window_calls > 100
    GROUP BY queryid
)
SELECT c.queryid,
       round(c.window_mean_ms::numeric, 2)          AS now_ms,
       round(b.median_ms::numeric, 2)               AS baseline_ms,
       round((c.window_mean_ms / b.median_ms)::numeric, 2) AS ratio,
       left(c.query_sample, 80)                     AS query
FROM current c
JOIN baseline b USING (queryid)
WHERE b.median_ms > 0
  AND c.window_mean_ms / b.median_ms > 2.0          -- twice as slow as usual
ORDER BY ratio DESC;
From counter to alert in four steps A snapshot job writes the spatial slice every five minutes. A view differences consecutive snapshots into per-window rates. A baseline query takes the median of the same weekday and hour over four weeks. A comparison fires when the current window exceeds twice the baseline with enough calls to be meaningful. Four steps, all of them SQL 1 · snapshot every 5 minutes spatial slice only 2 · difference lag() over queryid per-window mean 3 · baseline same weekday + hour median of 4 weeks 4 · compare ratio > 2.0 calls > 100 The snapshot table is the only new state. Everything else is a view and a query, which means the whole pipeline can be inspected and adjusted from psql during an incident rather than by redeploying a collector. Retain snapshots for about ninety days: enough for a seasonal baseline, small enough to ignore.

Configuration and Tuning Knobs

Snapshot interval of five minutes balances resolution against volume. Shorter intervals make each window noisier because fewer calls land in it; longer ones delay detection.

pg_stat_statements.max must be large enough that the statements you care about are not evicted. The default of 5,000 is low for a database with many distinct queries; 10,000 costs a few megabytes of shared memory and removes a class of confusing gaps.

The minimum call count in the alert is the main noise control. A hundred calls per window is a reasonable floor — below that, one slow execution moves the mean enough to fire.

The ratio threshold of 2.0 catches real regressions without firing on ordinary variance. Tightening it to 1.5 finds more and pages more; the right value depends on how stable your workload already is.

Alert quality by configuration False and true positives per week under four alert configurations: alerting on cumulative mean never fires, a fixed threshold fires 34 times a week mostly wrongly, a ratio against the previous hour fires 11 times, and a ratio against the same hour last week fires twice and catches both real regressions. Alerts per week, by how the baseline is chosen cumulative mean 0 alerts — and 0 regressions caught fixed ms threshold 34/week · fires every morning peak vs previous hour 11/week · fires on the diurnal curve vs same hour last week 2/week · both were real An alert that fires every morning is muted within a fortnight, which makes it worse than no alert.

Verification Steps

sql
-- does the pipeline produce sensible windows?
SELECT queryid, taken_at, window_calls, round(window_mean_ms::numeric, 2)
FROM pgss_windows
WHERE window_calls > 0
ORDER BY taken_at DESC
LIMIT 20;

-- deliberately regress something and confirm the alert fires
BEGIN;
UPDATE pg_index SET indisvalid = false
WHERE indexrelid = 'idx_parcels_geom'::regclass;
-- run the workload for one interval, check the alert query, then:
ROLLBACK;

Testing an alert by causing the condition is the only way to know it works. Hiding the index for one interval is a safe, instantly reversible way to make a spatial query genuinely slower — and if the alert does not fire, better to learn that on a Tuesday afternoon.

Gotchas Checklist

  • A statistics reset produces negative windows. Filter them out or the alert fires on the reset itself.
  • queryid is not stable across PostgreSQL major versions. After an upgrade, the baseline is gone; expect a quiet period while it rebuilds and do not chase the gap.
  • The same SQL from two applications shares a queryid. If one of them regresses, the mean moves for both; a tagging comment per caller separates them.
  • Alert on mean, chart on total. The alert catches regressions; the chart shows which query is costing the most, and they are different questions.
  • Do not alert per statement without a floor. A database has thousands of statements, most of them rare, and alerting on all of them is how a monitoring system gets muted.

Choosing Thresholds That Do Not Page at Three in the Morning

The failure mode of query alerting is not missing regressions; it is firing so often that the alert is muted, after which it misses everything. Three choices decide which side of that line an alert lands on.

Alert on a ratio against the query’s own baseline, not on an absolute duration. A spatial join that normally takes 900 milliseconds and now takes 1,400 is a regression worth knowing about; a tile query that normally takes 4 milliseconds and now takes 30 is a much larger regression that a 1-second absolute threshold would never see. Storing a rolling median per queryid and alerting when the current window exceeds two times that median treats both correctly.

Alert on a percentile, not a mean. pg_stat_statements gives you mean_exec_time for free, and it is the wrong number, because a single 40-second query mixed into ten thousand fast ones barely moves it. If the extension is compiled with the option, use the per-statement percentile columns; otherwise sample the running query duration from pg_stat_activity and compute a p95 in the collector. The difference shows up precisely on the workloads that matter — those where a small fraction of requests hit a bad plan.

Require persistence before firing. A single bad window is usually a checkpoint, an autovacuum, or a backup. Two consecutive windows above the threshold cuts the noise by roughly an order of magnitude and delays a genuine alert by exactly one window. For a five-minute window that is a trade worth making every time.

Finally, attach the diagnosis to the alert. An alert that carries the queryid, the normalised query text, the current and baseline timings, and the number of calls in the window lets whoever is woken decide in thirty seconds whether to act. An alert that says “database slow” guarantees a twenty-minute investigation before that decision can even be made.