How a Missing Database Index Turned an 8ms API into an 8-Second Nightmare
Every developer has a fear of the silent killer: a bug that doesn’t throw a 500 error or crash your server immediately, but slowly chokes your application until your database connections completely freeze under heavy traffic.
This is the story of how a single missing index on a foreign key brought down our staging API during a load test—and how a simple 5-minute fix brought response times down from 8 seconds to 12ms.
😱 The Incident: Connection Timeout & 100% DB CPU
It all started during a routine load test prior to a major feature release. Everything ran smoothly in local development with 100 sample records, but as soon as we simulated 500 concurrent users against production-sized data (around 1.5 million rows):
- API Response Times exploded from 45ms to over 8,000ms.
- Database CPU hit 100% and stayed pinned there.
- Connection Pool Exhaustion: Requests started timing out with ETIMEDOUT errors because every database connection was stuck waiting on open queries.
🔍 The Investigation: Uncovering the Full Table Scan
We pulled up query performance metrics and looked at the slow query log. The offending endpoint was /api/v1/orders/history, which fetches recent orders for a user filtered by status.
📦 Express.js Route
// Express.js route handling user order history
app.get('/api/v1/orders/history', async (req, res) => {
const { userId, status } = req.query;
try {
const orders = await db.query(
`SELECT * FROM orders
WHERE user_id = $1
AND status = $2
ORDER BY created_at DESC
LIMIT 20`,
[userId, status]
);
res.json({
success: true,
data: orders.rows
});
} catch (err) {
res.status(500).json({
error: 'Database query timed out'
});
}
});
When we ran EXPLAIN ANALYZE on PostgreSQL for that query, the diagnosis was brutal.
📦 Query Plan (Before Index)
QUERY PLAN
----------------------------------------------------------------------------------
Seq Scan on orders (cost=0.00..38420.00 rows=15 width=128)
(actual time=412.304..8150.120 rows=20 loops=1)
Filter: ((user_id = 10482)
AND ((status)::text = 'completed'::text))
Rows Removed by Filter: 1499980
Planning Time: 0.182 ms
Execution Time: 8150.210 ms
Because there was no index on user_id, PostgreSQL had to perform a Sequential Scan, reading through all 1.5 million rows for every single API request.
With 100 concurrent requests, the database engine was effectively scanning 150 million rows simultaneously.
🛠️ The Fix: Compound Indexing
The fix was deceptively simple, but choosing the right index mattered.
Adding an index on only user_id would improve filtering, but a composite index on (user_id, status, created_at DESC) allows PostgreSQL to:
- Filter by user_id
- Filter by status
- Return the newest rows immediately
- Avoid an extra sorting step
📦 Migration Script
// Database Migration Script (Knex.js)
exports.up = async function (knex) {
// Build the index without blocking writes
await knex.raw(`
CREATE INDEX CONCURRENTLY IF NOT EXISTS
idx_orders_user_status_created
ON orders (user_id, status, created_at DESC);
`);
};
💡 Pro Tip: In production, build indexes using
CONCURRENTLYwhenever possible so PostgreSQL doesn't lock the table for incoming writes while creating the index.
📊 The Results
Running EXPLAIN ANALYZE after the index was created:
📦 Query Plan (After Index)
QUERY PLAN
----------------------------------------------------------------------------------
Index Scan using idx_orders_user_status_created on orders
(cost=0.43..8.45 rows=20 width=128)
(actual time=0.042..0.118 rows=20 loops=1)
Planning Time: 0.085 ms
Execution Time: 0.142 ms
🚀 Performance Improvements
- Query Execution Time: 8,150ms ➜ 0.14ms (99.9% faster)
- API Latency: 8 seconds ➜ 12ms
- Database CPU Usage: 100% ➜ under 8%
- Connection Pool: No more timeout errors under load
💡 Key Takeaways
- Local development can be misleading. Queries that perform perfectly on 100 rows may completely fall apart with 1,000,000+ rows.
-
Index your foreign keys. Many ORMs don't automatically create indexes for columns like
user_id,tenant_id, ororganization_id. -
Always use
EXPLAIN ANALYZE. Before upgrading hardware or increasing database resources, inspect the execution plan. The database usually tells you exactly what's wrong. - Design indexes around your queries, not just your schema.
🧠 Final Thoughts
Performance problems don't always come from bad code.
Sometimes, a single missing database index is enough to turn a fast API into an unusable one under real traffic.
Before scaling your infrastructure, make sure you've optimized your queries.
Your database—and your future self—will thank you.
If this post helped you, consider dropping a ❤️ and sharing your own database performance horror story in the comments.
Remember:
Don't scale your servers...
Scale your queries first. 🚀
Happy coding! 💙
Top comments (9)
Great write-up! 👍 EXPLAIN ANALYZE really is the first place to look before throwing more hardware at a performance problem.
One thing I’d add is that indexes should always be driven by workload, not by a general rule. A composite index like (user_id, status, created_at DESC) is excellent for this specific query pattern, but a different access pattern might require a different index—or none at all if write overhead outweighs the benefit.
Performance tuning is all about measuring, validating, and iterating. Nice reminder that the execution plan usually tells the real story.
Spot on, Mustafa! 🎯 You're completely right—indexes definitely aren't a 'one size fits all' solution, and write overhead is something a lot of folks overlook when adding composite indexes. In our case, the read-to-write ratio on order history heavily favored this approach, but measuring workload first is always the key. Appreciate the great addition to the post!
Awesome article, Mia! The tip about using CONCURRENTLY in production migrations is so important—a lot of devs learn that the hard way when CREATE INDEX locks the table. Thanks for putting this together! 🚀
Thanks, Zahab! Spot on—table locking during index creation in production is definitely a right-of-passage mistake for many devs! CONCURRENTLY is such a life-saver there. 😅 Appreciate you reading!
Anytime! Keep up the great work and awesome write-ups! 🚀
This is such a fantastic reminder of why staging load tests are non-negotiable! It’s so easy to get a false sense of security when everything flies smoothly with 100 sample records locally. The breakdown of how fast 150 million rows accumulate across concurrent requests really puts the scale into perspective. Thanks for sharing this breakdown, Mia!
Thanks Zoe. Appreciate it.
Great write-up, Mia! 🚀 The practical breakdown of how a single missing index can snowball into a full system lock under heavy load is such an important reminder—especially highlighting CREATE INDEX CONCURRENTLY to prevent table locking in production.
Quick question for you: when building composite indexes like (user_id, status, created_at DESC), how do you usually balance creating tailored multi-column indexes versus keeping the total index write-overhead manageable as the schema evolves? Would love to hear your rule of thumb for when to combine columns into one index!
Great question! My main rule of thumb is query frequency and critical SLA. I only build composite indexes for high-throughput or latency-critical endpoints (like user feeds or checkout paths).
For general filtering, I try to rely on single-column indexes on high-cardinality keys like user_id. PostgreSQL can often combine separate single-column indexes using a Bitmap Index Scan if needed, which saves us from creating dozens of hyper-specific composite indexes as the schema grows!