How I run a Postgres index audit on a production snapshot

Indexes drift. Queries change, features get added, someone builds an index by hand during an incident, and a year later nobody can say which indexes the app needs or which ones it carries for nothing. A Postgres index audit fixes that, and the version I trust runs against a copy of production data. This is the process I used on Buffdudes, the fitness platform I run the backend for (Node.js, TypeScript, TypeORM, PostgreSQL). It found fourteen missing indexes, one index missing from the models, and two duplicates, and every slow query I checked dropped under 2 ms.

Start with the diff between models and database

Before reading a single query plan, I want to know whether the code and the database agree about what exists. If they don't, every later step is built on a guess.

TypeORM can print the SQL that schema sync would run against a database, without running it. Point it at the DataSource file and read the output:

npx typeorm-ts-node-commonjs schema:log -d src/data-source.ts
npx typeorm-ts-node-commonjs migration:generate src/migrations/Audit -d src/data-source.ts --dryrun

Anything in that output is a disagreement. On Buffdudes it showed one index the database had that the models did not declare: a plan-streak index that enforces one open streak per user and plan. That is a correctness index, and it lived only in the database. I declared it on its model so the next schema change could not quietly remove it. That failure mode is real, and I wrote about it separately in why TypeORM synchronize dropped my indexes.

Find queries that filter or join on unindexed columns

Next I go through the code that talks to the database and list every column a query filters on, joins on or sorts by. Then I check each one against the indexes that exist. The question is simple: when this query runs, can Postgres find its rows without reading the whole table?

The pg_stat_user_tables view gives a useful second opinion: its seq_scan and idx_scan columns count sequential and index scans per table since the stats were last reset. Sort by seq_scan and compare with n_live_tup. A large table near the top of that list is worth a look. A small lookup table there is usually fine.

On Buffdudes this pass produced fourteen proposed indexes. I wrote them first as commented-out @Index lines on the models plus a migration, so the proposal was reviewable as a diff before anything touched a database.

Check every candidate with EXPLAIN ANALYZE on a production snapshot

A proposed index is a hypothesis. Local databases are too small to test it: with a few thousand rows, Postgres will happily scan the table and the plan tells you nothing about production.

So I checked each candidate on a production snapshot taken on 2026-10-17. For every query I ran EXPLAIN ANALYZE without the index, created the index, and ran it again. The PostgreSQL docs on EXPLAIN are the best reference for reading the output; the two things I look for are the scan type and the actual time.

The result was consistent. Every one of those queries was a sequential scan without its index, and every one dropped under 2 ms with it. The clearest example was loading plan histories by streak, which went from 117 ms to 1.5 ms.

That check is what makes the audit safe to ship. Each index in the migration has a measured reason to exist, and the reviewer can see it.

Order the columns by how rows are touched

Column order in a composite index matters, and the right order comes from how the code reads and writes the table. On user_stat_timelines the index leads with user_workout_id, because every edit and delete of a workout reverts its stats by workout alone. A query that filters only on the second column of an index gets far less help from it, so the column that appears alone in the hot path goes first. Use The Index Luke explains why in its chapter on concatenated indexes.

This is the same lesson as the composite index that took the workout history screen from about 4 s to 200 ms in scaling Buffdudes to 60k users. Match the index to the shape of the query.

Hunt for duplicate and redundant indexes

Missing indexes cost reads. Extra indexes cost writes, disk and memory, on every insert and update, forever. An audit should remove as well as add.

Exact duplicates are easy to find from the catalog. This groups indexes by table, columns, operator classes, expressions and predicate, and returns any group with more than one member:

SELECT indrelid::regclass AS table_name,
       array_agg(indexrelid::regclass) AS duplicate_indexes,
       pg_size_pretty(sum(pg_relation_size(indexrelid))) AS total_size
FROM pg_index
GROUP BY indrelid, indkey::text, indclass::text, indcollation::text,
         coalesce(indexprs::text, ''), coalesce(indpred::text, '')
HAVING count(*) > 1;

On Buffdudes it found two identical indexes on post likes. One was dropped.

The subtler case is an index that duplicates the one Postgres builds for a unique constraint. A unique constraint already gives you an index on its columns. Adding a plain index on the same columns gives you a second copy to maintain. A later pass found exactly that on a login-streak table: an index that duplicated the unique constraint's own index, taking 14 MB and adding a second write on every app open. It was dropped.

Use partial indexes for queries that only touch a slice

Some queries only ever look at a small part of a table. A partial index covers just those rows, so it is smaller and cheaper to keep up to date. The partial index docs cover the rules: the query's WHERE clause has to imply the index predicate, or the planner won't use it.

On Buffdudes, a partial index on the recent-login timestamp column turned the recent-login scan from a sequential scan into an index scan: 10.7 ms to 0.34 ms, measured locally. Treat a local number like that as a direction rather than a promise, and confirm the plan on the snapshot before relying on it.

Keep every index on the model and in a migration

The last rule is the one that stops the drift from coming back. Every index on Buffdudes is declared on its TypeORM model and created by a migration. The models describe the database, the migration history explains how it got there, and the schema diff from step one comes back empty.

Two habits help here:

• Hand-made indexes go into a migration the same day. An index created in a psql session during an incident is invisible to the next person.
• The commit body carries the evidence. Before and after timings from the snapshot belong in the commit message, so a reviewer a year later knows why the index exists. I go into that habit in commit messages that explain why.

How often should you run a Postgres index audit?

I'd run one whenever a feature changes how a large table is queried, and a full pass once or twice a year on any database that has been growing for a while. The schema diff and the duplicate-index query take minutes, so they can run far more often than the full snapshot check. If the app has gone a long time without one, the first audit usually finds the most.

Where I'd start

1. Run the schema diff and fix any disagreement between models and database.
2. List the unindexed filters and joins, and check each against a production snapshot with EXPLAIN ANALYZE.
3. Drop exact duplicates and indexes that repeat a unique constraint.
4. Ship every index on the model and in a migration, with the measured timings in the commit.

If you want a second pair of eyes on a database that has started to slow down, that is the kind of work I take on through my services.