Performance
The Index You Meant to Create
I wrote the migration, the model was correct, and the production database never saw it. Full-table scans for weeks.
I wrote the database schema. I added `tenant_id` and `created_at` to the model. I pushed the code, ran migrations on staging, tested with twenty records, and everything felt instantaneous. Then we went to production.
Two months later, response times climbed from 40ms to 1.8 seconds. CPU utilisation on our database instance spiked to 94% on simple dashboard loads.
The missing index
On ten thousand rows, a sequential table scan takes two milliseconds. Postgres fits the whole table in RAM, ignores the lack of an index, and gives you sub-millisecond responses on your local machine. At two million rows, Postgres has to read hundreds of megabytes of disk blocks for every single query.
-- The slow query
SELECT * FROM events
WHERE tenant_id = 'org_912'
ORDER BY created_at DESC
LIMIT 50;
-- What EXPLAIN ANALYZE showed:
-- Seq Scan on events (cost=0.00..42190.12 rows=50 width=284)
-- Filter: (tenant_id = 'org_912'::text)
-- Rows Removed by Filter: 1,840,210Composite indexes must match the query order
The fix was one composite index: `CREATE INDEX idx_events_tenant_created ON events (tenant_id, created_at DESC);`. Response times dropped from 1,800ms to 4ms immediately. CPU usage plummeted from 94% to 3%.