Indexes are not free. They eat RAM. They slow down writes. Everyone adds a B-Tree index to every column and calls it a day. That’s a rookie mistake.
Strategy 1: Partial Indexes
We had a table orders with 100M rows. We frequently queried for “pending” orders. But only 1% of orders are pending.
Instead of indexing the whole table: CREATE INDEX idx_status ON orders(status)
We did this: CREATE INDEX idx_pending ON orders(created_at) WHERE status = ‘pending’
Result: The index size dropped from 2GB to 20MB. It fits entirely in RAM. Queries are instant.
Strategy 2: BRIN Indexes for Time-Series
For our log tables (append-only, sorted by time), B-Trees are wasteful. We switched to BRIN (Block Range INdexes).
BRIN stores the min/max value for each physical disk block. It is tiny.
- B-Tree Size: 45GB
- BRIN Size: 40KB
Yes, Kilobytes. For “Give me all logs from yesterday”, it is just as fast.
Strategy 3: The “Fillfactor” Trick
We had high write contention on a counter table. Updates were blocked by locking. We lowered the fillfactor to 70% to leave empty space on each page for updates (HOT updates). This reduced IOPS by 50%.
Conclusion
Postgres is an incredible piece of engineering. Before you shard or switch to NoSQL, learn to use the tool you already have.