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 worked fine with 100 records. Then we simulated 500 users against 1.5 million rows in production-sized data.

The results were bad:

  • API response times jumped from 45 ms to 8,000 ms.
  • Database CPU hit 100 %.
  • Connection pools exhausted, causing timeout errors.

We checked the slow-query log and found an endpoint fetching order history by user ID and status.

Running EXPLAIN ANALYZE on PostgreSQL showed a Sequential Scan. The engine read all 1.5 million rows for every request because there was no index on user_id.

With 100 concurrent requests, it scanned 150 million rows at once.

The fix took five minutes.

Instead of a simple index on user_id, we created a composite index on (user_id, status, created_at DESC). That let the database:

  • Filter by user_id.
  • Filter by status.
  • Return the newest rows immediately.
  • Skip extra sorting steps.

We used CREATE INDEX CONCURRENTLY so the table stayed unlocked during the operation.

After the fix:

  • 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 testing is misleading; 100 rows don’t represent a million.
  • Index your foreign keys—most ORMs skip this.
  • Run EXPLAIN ANALYZE before buying more hardware.
  • Design indexes around the queries you actually run.

Don’t scale servers first. Scale queries.

Source: https://dev.to/mia_keller_ffd2584c046ecb/how-an-unindexed-column-silently-killed-our-database-under-load-and-the-5-minute-fix-m32