A step-by-step approach for adding indexes with confidence and validating impact under production load.
The first time I "fixed" a slow query, I added an index, watched the page load faster on my laptop, and shipped it feeling like a hero. Two weeks later that same table had ground to a halt under write load, and the index I was so proud of was a big part of why. I'd optimized the read I could see and ignored the writes I couldn't.
That's the thing about indexing in Postgres. It feels like free speed, and it never is. Every index is a trade — you're spending write performance and disk to buy faster reads. The job isn't to add indexes. It's to make that trade on purpose, with numbers in front of you. Here's the playbook I use now.
Measure before indexing
Capture baseline latency and query plans before adding any index. Without a baseline, every improvement is just a guess — and worse, a guess you'll defend because you're emotionally invested in it.
Before I touch anything, I run EXPLAIN (ANALYZE, BUFFERS) on the actual query, with realistic parameters, against data that looks like production. That last part matters more than people expect. A query plan on 500 seed rows tells you almost nothing about how Postgres will behave on 50 million; the planner makes completely different choices as tables grow, and the sequential scan that's instant on your laptop is the thing paging your team at 2am.
What I'm looking for in the plan is simple: where is the time actually going? A sequential scan over a huge table, a sort that spills to disk, a nested loop doing far more work than the row counts suggest. Write the numbers down — total time, rows scanned, buffers read. That baseline is the only way you'll know later whether you actually helped or just felt like you did.
Index for access patterns
Indexes should mirror the WHERE, JOIN, and ORDER BY clauses in your real traffic — not the schema you imagine, but the queries that actually run. Pull them from pg_stat_statements if you're not sure. The truth is usually a little embarrassing and very useful.
The single most valuable habit here is reaching for composite indexes — indexes on more than one column — instead of scattering single-column ones everywhere. If you frequently filter by tenant_id and then sort by created_at, one index on (tenant_id, created_at) will almost always beat two separate indexes, because Postgres can satisfy both the filter and the ordering in a single pass. Column order matters: put the columns you filter on equality first, then ranges, then the sort.
There's a quiet superpower hiding in here too — the covering index. If your index includes every column a query needs, Postgres can answer straight from the index and never touch the table at all. Postgres calls this an index-only scan, and on a hot read path it's the difference between fast and startlingly fast. You add the extra columns with an INCLUDE clause so they ride along without becoming part of the sort key.
Watch write amplification
Every index speeds reads and slows writes. There's no way around it — when you insert a row, Postgres has to update every index on that table, and on a high-ingest table that cost adds up in a hurry. This is the bill that came due on my hero index years ago.
So for tables that take a lot of writes, I keep the index count deliberately lean and treat each one as something I have to justify. I'd rather have three well-chosen indexes that earn their keep than ten that each shave a few milliseconds off some query nobody runs. And I watch for bloat — as rows churn, indexes accumulate dead space that quietly inflates their size and slows them down. pg_stat_user_indexes will even show you which indexes are never used at all; those are pure cost, and dropping them is the easiest win in the whole playbook.
One more practical note: when you do add an index to a live table, use CREATE INDEX CONCURRENTLY. The plain version takes a lock that blocks writes for the entire build, which on a large table can mean a very bad few minutes for everyone. The concurrent version is slower and a little fussier, but it lets traffic keep flowing while it works. On a production database, that's not a nicety — it's the whole game.
That's really the loop: measure, index for the queries you actually run, then keep an eye on what those indexes cost you over time. Nothing flashy. But it's the difference between an index that makes the system better and one that just makes one graph better while quietly making everything else worse.
If you take one thing from this, let it be the baseline. Measure first. Future-you, staring at a mysterious slowdown, will be so grateful you have the numbers to compare against. I promise.
