1. The Problem
Filtering tenant task records using `WHERE tenant_id = ? AND updated_at >= ?` triggered full sequential table scans over 5.2M rows, causing database CPU spikes to 98%.
2. What I Initially Thought
3. What I Learned
Index column order matters. A composite index `(tenant_id, updated_at)` allows the B-Tree engine to jump directly to the tenant branch and range-scan timestamps in one operation.
4. What I Built
Composite index strategy across core transactional tables (`CREATE INDEX idx_tasks_tenant_updated ON tasks(tenant_id, updated_at DESC)`).
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_tasks_tenant_updated ON tasks (tenant_id, updated_at DESC);
5. The Experiment
Filter query scanning 5.2M rows via Sequential Scan. Duration: 12.3 seconds. CPU utilization: 98%.
Created composite B-Tree index `(tenant_id, updated_at DESC)` using `CREATE INDEX CONCURRENTLY`.
Query duration dropped from 12.3 seconds to 4.1 milliseconds. Scan method switched to Index Scan.
6. What Went Wrong
Initial index creation without `CONCURRENTLY` locked the production table for 45 seconds, blocking write queries.
7. Engineering Decision & Trade-offs
Mandated `CREATE INDEX CONCURRENTLY` in production migration scripts and required EXPLAIN ANALYZE validation for all new query patterns.
8. What I Would Do Differently in Production
Monitor index bloat and unused indexes using `pg_stat_user_indexes` to avoid write performance degradation during INSERT operations.
Questions I Can Now Answer Confidently in an Interview:
- How do you read a PostgreSQL EXPLAIN ANALYZE plan output?
- Why does column order matter in a composite B-Tree index?
- Why should production indexes always be created with the CONCURRENTLY keyword in PostgreSQL?