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.

1 min readPublished June 16, 2026
Share on X

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,210

Composite 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%.

  • Databases
  • Performance
  • Postgres
KI

Kadirul IslamVerified author

Founder, A.K Solution

Follow on X

Founder of A.K Solution. Full-stack engineer building AI agent tools like KiCode, JSON form engines like ki-forms, and high-scale SEO systems. Obsessed with shipping fast, zero-bloat software that lasts.

More from Kadirul Islam

A Slow App Is Often Just a Database Waking Up

Free tiers suspend on idle. The first request after a pause pays a cold start, and it looks exactly like a slow product.

2 min readPerformance