Zero-downtime index on a high-write transactions table
An admin report endpoint was taking three seconds on large accounts. EXPLAIN ANALYZE pointed to a missing composite index. The fix took an afternoon. The lesson about column ordering took longer.
- Date
- Q2 2025
- Role
- Individual contributor
- Stack
- PostgreSQL · NestJS · TypeORM
- Scale
- Large monorepo · high-write table
- Status
- Shipped Q2 2025
The admin panel had a transaction report: filter by account, filter by status, sort by date. On small accounts it was fast. On the largest accounts, the ones with several years of history, it was taking three seconds or more. People had started exporting to CSV and filtering locally.
The transactions table had a single-column index on
user_id. The query filtered on
user_id, status, and ordered by
created_at. The index was helping with the first
condition and doing nothing for the other two.
No maintenance window. The transactions table is written to continuously. Any operation that holds an exclusive lock on it blocks writes for the full build duration.
Write overhead had to be measured before deploying. An index on a high-write table adds cost to every insert. That cost needs a number, not a shrug.
Could not change the query shape. It was generated by a shared ORM layer used across multiple endpoints. Changing it was out of scope.
EXPLAIN ANALYZE on the slow query. The planner
was using the existing user_id index, scanning
several thousand rows for a given account, applying a filter
on status that discarded about 80% of them, and
then sorting the remainder by created_at. The
sort was happening in memory because the index gave the planner
no ordering on that column.
A composite index on
(user_id, status, created_at DESC) would let the
planner satisfy all three conditions without a post-scan filter
or a separate sort. It would read only the rows that matched,
in order, and stop.
Before building the index, I measured write throughput on the table in staging under load. The difference on insert latency was a few percent. Within the acceptable range given what the read improvement looked like.
The planner was discarding 80% of rows after scanning them. The single-column index was helping with the least selective condition first.
Built the composite index using
CREATE INDEX CONCURRENTLY, which builds without
holding an exclusive lock. Writes continue uninterrupted during
the build. The index becomes available when it finishes.
CREATE INDEX CONCURRENTLY idx_transactions_user_status_date
ON transactions (user_id, status, created_at DESC)
After the index was live, I ran EXPLAIN ANALYZE
again to confirm the planner picked it up. It did. The query
plan went from an index scan with a filter and an in-memory
sort to a single index-only scan.
The existing single-column index on user_id was
left in place. Removing it safely would have required auditing
every other query on the table, which was outside the scope
of this change.
- Report endpoint latency
- 3.2s → 180ms
- Downtime
- none
- Insert overhead added
- roughly 4% on p99
- Planner change
- full scan + filter → index-only scan
EXPLAIN ANALYZE before any performance fix. The
instinct is to add an index on whatever column appears in the
WHERE clause. Column ordering in a composite index
matters: most selective condition first, ordering column last.
Getting that wrong produces an index the planner will not use.
This should have been caught in code review when the query was first written. A query filtering on three columns with no composite index covering them is easy to spot in a diff and hard to find six months later in a latency report.
The single-column index on user_id is still there.
It is now redundant. The composite index covers anything it
could satisfy, and it adds unnecessary overhead to every insert.
Cleaning it up properly means auditing every query on the table,
which I have not done. That is the deferred cost of keeping the
scope narrow.