We dropped 8 indexes and added 3 without a single production statistic

A round of database work in our app fixed a handful of genuinely expensive reads and rationalised the indexes on the three hottest tables. Every finding came from reading application code and the SQL it generates, because pg_stat_statements was not enabled and there were no production query statistics to read at all.

That worked better than it should have, and it is still the wrong way round. So the output of the round includes a checked-in SQL runbook whose header says so plainly: the work was prioritised structurally, and these queries exist so the next round is driven by evidence rather than inference.

The findings a structural read is good at

Reading code finds a specific class of bug very reliably: work the application asks for and then throws away.

The dashboard's "recent sessions" read used to run a $count over the user's entire session history alongside the rows, to report total and hasMore. Nothing had ever read either field. Its only caller destructures { sessions }. So every dashboard render paid for a full aggregate over the largest table in the app to compute two numbers that were immediately discarded. Genuine pagination already existed elsewhere, on the repository method the history page uses, which is the other half of the lesson: there were two implementations and only one of them was wanted.

The same read also selected metrics, a jsonb column that is by far the largest on the most-written table. The question it answers is "which games has this user played lately", never "how did that attempt go". It now selects eight named columns, and the comment above it says which question it is for, because the next person to add a field will be deciding exactly that.

Then there is a Drizzle-specific trap worth knowing:

db.$count(auditLogsTable, eq(auditLogsTable.user_id, userId))

The filter has to be passed into $count. Without it, Drizzle emits an unfiltered count(*) over the whole table, which is correct-looking code that answers a different question than the one surrounding it, and gets slower in proportion to every other user's activity rather than yours.

Two more of the same shape. The dashboard layout fetched the user row with a cache() function keyed on email while the pages below fetched the identical row with one keyed on id, so the same row was read twice per render; the layout now uses the id-keyed one and shares the cache entry. And the trend analysis in our analytics engine gained an optional preloadedSessions argument, because its caller needed the same rows for its own response and was fetching them separately, and sequentially. React's cache() cannot help there at all: route handlers have no render scope, so the dedupe that works inside a render pass does nothing in an API route.

The index pass, and the DESC that would not have worked

One migration drops 8 indexes and adds 3, and the header explains every drop in one of three categories.

Prefix-redundant, because Postgres can use a leading subset of a composite index, so a standalone index on a prefix column is dead weight:

audit_logs_user_id_idx      (user_id)         ⊂ audit_logs_user_created_idx
game_sessions_user_game_idx (user_id,game_id) ⊂ game_sessions_user_game_completed_idx

Duplicates of an implicit unique index, because email and stripe_id are declared UNIQUE, which already creates a btree. And unused by any query in the codebase: every read of our audit log has the identical shape, WHERE user_id = $1 ORDER BY created_at DESC LIMIT n, so indexes on action or on created_at alone were pure write cost. game_sessions is written once per completed game, and this removed 3 of its 7 secondary indexes.

The added composites are ascending, deliberately, and this is the detail I would most want to have known earlier:

A btree is scannable backwards, so (user_id, created_at) satisfies ORDER BY created_at DESC. Declaring the column DESC would emit DESC NULLS LAST, which does not match Postgres's default DESC NULLS FIRST and would leave the planner unable to use the index for the sort.

Writing DESC in the index to match the DESC in the query is the intuitive move, and it is the one that silently costs you the sort.

Every statement is IF EXISTS or IF NOT EXISTS, because the migration history contains indexes for tables that no longer exist in the schema, so the live database may already have diverged. Idempotence means one already-absent index cannot abort the whole migration.

The runbook, and the honest caveat in it

The migration ends by telling you not to trust it:

-- After applying, confirm the "unused" claims against real statistics.
SELECT relname, indexrelname, idx_scan FROM pg_stat_user_indexes
WHERE schemaname = 'public' ORDER BY idx_scan;

The runbook has eight sections, and the two that matter most are not the slow-query list. The first is ordering by total time rather than mean time, because a 5ms query run 100,000 times costs more than a 2 second query run twice, and only one of them is worth your afternoon. The second is this, which is a detector for the exact class of bug described above:

SELECT calls, rows, round((rows::numeric / GREATEST(calls, 1)), 1) AS rows_per_call,
       round(mean_exec_time::numeric, 2) AS mean_ms, left(query, 160) AS query
FROM pg_stat_statements
WHERE calls > 10 AND (rows::numeric / GREATEST(calls, 1)) > 100
ORDER BY rows_per_call DESC;

High rows per call is the signature of a missing LIMIT, an unfiltered count, or a query that fetches a whole history to use three rows of it. It is worth re-running as a regression check, because all three come back.

And the caveat the runbook states about our own numbers: at a few hundred users most of these tables are trivially small, which is why several findings in the round were about correctness and write amplification rather than raw scan cost. Dropping five redundant indexes on a table with ten thousand rows does not make anything measurably faster today. It makes every future write cheaper, and it stops the next person inheriting four indexes they have to reason about.

See it

The reproducible part is the detector, and it runs on your database rather than ours. Enable the extension, run the rows_per_call query above, and look at anything over 100 that is called more than ten times. In our case the top entries were not slow queries, they were correct queries fetching far more than their caller used.

For the public half, this endpoint is one of the bounded reads that came out of the same pass, and it needs no account:

curl -s "https://cogniprep.app/api/games/population-stats/shl-numerical"

It answers from a five-minute cache with a fixed set of aggregates and a lastUpdated stamp, rather than by scanning a session history per request. If you want to see what it feeds, the practice dashboard it backs is behind a free account at cogniprep.app, and every percentile it shows you is one of the figures in that response.