Databases & System Design•Part 1: Candid Database Experience & Intro Scenarios (1 - 5)
Describe a severe database performance issue you resolved.
How to Explain in an Interview:
- Problem: Primary PostgreSQL CPU hit 100% and connection pool exhaustion caused 504 errors.
- Root Cause: Running
EXPLAIN ANALYZErevealed a sequential scan across 15M rows due to a missing composite index on a foreign key join. - Fix: Created the composite index concurrently on
(user_id, created_at DESC), added PgBouncer in transaction pooling mode to reuse connections, and cached hot catalog reads in Redis.
sql
-- Root Cause Diagnosis:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 'c8b21...' ORDER BY created_at DESC LIMIT 10;
-- Output showed: Seq Scan on orders (cost=0.00..385200.12 rows=15200000)
-- Resolution:
CREATE INDEX CONCURRENTLY idx_orders_user_created ON orders (user_id, created_at DESC);
-- New plan: Index Scan using idx_orders_user_created (cost=0.43..8.45 rows=10)