Background
As our dataset grew past 50M rows, queries that once ran in milliseconds started creeping into the seconds. Here's what we learned fixing them.
Index everything you filter on — but not blindly
The obvious fix is adding indexes, but too many slow down writes. We audited pg_stat_user_indexes to find indexes with zero scans and dropped them.
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY schemaname, tablename;
Connection pooling matters more than you think
Without PgBouncer, each Rails process held an open connection. At peak load, we were opening 400+ connections — PostgreSQL's default max_connections is 100. We hit this wall hard.
Adding PgBouncer in transaction mode dropped our connection count from ~400 to ~20 with no latency regression.
EXPLAIN ANALYZE is your best friend
Before any schema change, we ran:
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <your query>;
Look for Seq Scan on large tables — that's always a red flag. Bitmap Heap Scan with high Rows Removed by Filter means your index selectivity is poor.
Partial indexes for common filters
We had a query that always filtered WHERE status = 'pending'. A partial index cut query time by 80%:
CREATE INDEX idx_orders_pending
ON orders (created_at)
WHERE status = 'pending';
Key takeaways
- Audit unused indexes regularly
- Add a connection pooler before you need it
- Partial indexes are underused and very effective
- Never guess — always
EXPLAIN ANALYZE