DEV Community

Daniel Pertu
Daniel Pertu

Posted on

We wrote a query runbook because we had no query statistics, and two of its own references had already rotted

CogniPrep runs on Postgres through Supabase. lib/db/migrations holds 54 generated migrations. Beside them sit three hand-written .sql files that drizzle never runs, and this post is about the strangest one: a 118-line file that is not a schema change at all.

It is a runbook.

Why it exists

Migration 0036 dropped eight indexes and added three. Its header comment walks through the reasoning for every drop: four were prefix-redundant because Postgres can use a leading subset of a composite index, two duplicated the implicit B-tree behind a UNIQUE column, and two were not referenced by any query in the codebase.

Then it admits the problem with all of that:

-- After applying, confirm the "unused" claims against real statistics - they are
-- based on reading the codebase, not on measurement.
Enter fullscreen mode Exit fullscreen mode

Eight indexes were dropped on the strength of a careful read. Nothing had measured them. lib/db/observability.sql is the file written to close that gap, and it opens by saying so:

-- Context: the query work in this codebase was prioritised structurally - by
-- reading code and generated SQL - because no production query statistics were
-- available. These queries close that gap so the next round is driven by evidence
-- rather than inference.
Enter fullscreen mode Exit fullscreen mode

One statement that changes anything

The file is eight numbered sections, and section 1 is labelled as the only one that is not read-only:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
Enter fullscreen mode Exit fullscreen mode

That single line is the reason a runbook can live in version control at all. Everything after it is a SELECT, which means the file can be pasted into a SQL editor by anyone on the team without a review gate, a migration number, or a rollback plan. The destructive thing is one idempotent CREATE EXTENSION, and the genuinely destructive thing, pg_stat_statements_reset(), sits in section 8 commented out with a note that it is "destructive only to statistics".

The queries worth stealing

Section 2 orders by total time, not mean time, and explains why in two clauses:

-- Total time matters more than per-call time: a 5ms query run 100k times costs
-- more than a 2s query run twice, and only the first is worth optimising.
Enter fullscreen mode Exit fullscreen mode

Both pg_stat_statements queries scope themselves to the current database and exclude the extension's own view:

WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
  AND query NOT ILIKE '%pg_stat_statements%'
Enter fullscreen mode Exit fullscreen mode

Section 3 is the one I had not seen before. It hunts for queries that return far more rows than they can possibly need:

WHERE calls > 10
  AND (rows::numeric / GREATEST(calls, 1)) > 100
ORDER BY rows_per_call DESC
Enter fullscreen mode Exit fullscreen mode

GREATEST(calls, 1) is there so a zero-call row cannot divide by zero. The comment names the bug class this detects: "a missing LIMIT, an unfiltered count, or a query that fetches a whole history to use three rows of it". That is a much better smell than slowness, because a query can be both fast and wasteful, and the fast-and-wasteful one only hurts once the table grows.

Section 4 computes the share of sequential scans per table, with a guard for the table nobody has touched yet:

CASE WHEN seq_scan + COALESCE(idx_scan, 0) = 0 THEN NULL
     ELSE round(100.0 * seq_scan / (seq_scan + COALESCE(idx_scan, 0)), 1)
END AS pct_seq
Enter fullscreen mode Exit fullscreen mode

Section 6 measures table and index sizes, and its comment is the most honest line in the file:

-- Confirms whether "big table" concerns are real yet. At a few hundred users most
-- of these should be trivially small, which is why several findings in this pass
-- were about correctness and write amplification rather than raw scan cost.
Enter fullscreen mode Exit fullscreen mode

Two references that have rotted

A runbook's whole value is that someone reads it later. Both of its cross-references into the rest of the codebase are now wrong.

One names a script that does not exist. Section 5 says its index-usage query is "the same data as pnpm db:check-drift, kept here so it can be run from the SQL editor". Migration 0036's header recommends the same command. There is no db:check-drift in package.json. The seven db: scripts there are generate, migrate, push, studio, check-popstats, seed-population-stats and seed-sector-questions. Two files point a reader at a command that was either never written or deleted without a grep.

One quotes a number that moved. Section 7 measures connection pressure and says it is "relevant to the pool sizing in lib/db/db.ts (max: 3 per instance in production)". It was true when the runbook was committed on 10 August. On 14 September the pool became max: process.env.NODE_ENV === 'production' ? 8 : 5. The guidance that follows, "if active plus idle_in_transaction approaches the pooler's limit, revisit that number rather than raising it blindly", is still right. The number it hangs off is 35 days out of date.

Neither rots loudly. A .sql file is not typechecked, not linted, not imported, and not executed by CI, so the only thing that can catch a stale cross-reference in it is somebody reading the file for a different reason. Which is what happened.

The number that makes this worth finishing

Migration 0036 dropped eight indexes it had not measured. The runbook exists to check that decision. In the 18 migrations since, this repository has added 35 more indexes and dropped 5, and as far as anything in version control can prove, the file was written, committed, and never run.

If you only take one idea from this: put the rows-per-call query in your own repo. The slow-query list is the one everybody already has. The query that finds work you are doing and throwing away is the one that catches problems before they are slow.

See it

The only statistic about CogniPrep's database that a visitor can read is on the public health endpoint, and it is deliberately a single ping rather than a measurement:

https://cogniprep.app/api/health
Enter fullscreen mode Exit fullscreen mode

It answers with "database":{"status":"healthy","responseTime":3}. One round trip, in milliseconds, right now. That number tells you the connection is up. It cannot tell you which query costs the most, how many rows per call anything returns, or whether an index has ever been chosen. That is the entire gap the runbook was written to fill, and the difference between the two is the point.

While you are there, the nightly aggregate job writes its results to a public endpoint, so you can see a real query's output without an account:

https://cogniprep.app/api/games/population-stats/arrows
Enter fullscreen mode Exit fullscreen mode

Read lastUpdated and you will find a timestamp at 02:00, which is the cron that produced it.

Top comments (0)