How an Unindexed Column Killed Our Database
A single missing index turned an 8 ms API into an 8-second nightmare.
We ran a load test before a major release. Local development handled 100 rows fine. In staging we simulated 500 users against 1.5 million rows.
Everything broke.
- API response times jumped from 45 ms to 8,000 ms.
- Database CPU hit 100 %.
- Connection pools exhausted.
- Requests timed out.
The culprit was a simple query that fetched order history by user_id and status.
Running EXPLAIN ANALYZE on PostgreSQL showed a Sequential Scan. With no index on user_id, the engine read all 1.5 million rows for every request.
At 100 concurrent requests the database scanned 150 million rows at once.
The fix took five minutes. We created a composite index on (user_id, status, created_at DESC) using CREATE INDEX CONCURRENTLY so the table stayed online.
Results:
- Query time fell from 8,150 ms to 0.14 ms.
- API latency dropped from 8 seconds to 12 ms.
- CPU usage slid from 100 % to under 8 %.
Lessons learned:
- Local tests mislead; 100 rows aren’t a proxy for a million.
- Index foreign keys—most ORMs skip this.
- Run
EXPLAIN ANALYZE; the database points out the problem. - Optimize queries before buying more hardware.
Don’t scale servers first. Scale queries.