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 do...
For further actions, you may consider blocking this person and/or reporting abuse
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! 🚀
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!
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!
The choice to index on
(user_id, status, created_at DESC)rather than justuser_idalone is what makes this a great fix rather than a good one. A single column index would still require a secondary sort step oncreated_at, so PostgreSQL might not even use it for theORDER BY ... LIMIT 20shape. TheCONCURRENTLYflag is the other piece most tutorials leave out — building a standard index on a live table with 1.5 million rows would have caused write lock contention during peak hours.One thing worth adding: did you set
work_memhigher during the index build? On large tables that can halve the build time.Spot on, Mudassir! Raising work_mem for heavy index builds is such a pro tip—definitely saves a ton of time on large tables. Appreciate you diving into the details and reading the post!
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.
the 45ms to 8000ms jump under load is such a classic missing index story. what gets me is that it only showed up at production-sized data — local with 100 rows would never catch this. do you now load test against realistic data volumes for every release, or was this a one-time lesson?
Totally a lesson learned the hard way! We definitely integrated staging load tests against production-scale data volumes into our release pipeline after that incident. Local environments with 100 rows just give you a false sense of security every single time.