New series: what assistive tech really hears on your checkout page. Start with part one →
Shipwright
Home
Databases Sep 11, 2026 2 min read 122 views

Postgres Indexes You Do Not Need (and the One You Do)

Priya Raman

Priya Raman

Database and performance engineer. Priya reads…

Share

How we dropped eleven indexes and made writes 30 percent faster. Databases are usually the last place teams look when an application slows down, and the first place the problem turns out to be. This post is a practical walk through how we find and fix those problems.

Cover illustration for the article Postgres Indexes You Do Not Need (and the One You Do)
Figure 1. How we dropped eleven indexes and made writes 30 percent faster.

Look at the plan first

Every slow query investigation starts with EXPLAIN ANALYZE. It shows what the planner expected, what actually happened, and where the two diverged, which is almost always where the fix is.

  • Read the query plan before adding an index.
  • Backfill in batches, never in one transaction.
  • Drop indexes nobody uses.
  • Test migrations against a copy of production data.

The migration we ran

Schema changes on a busy table need care. We add the new column, backfill in batches, switch reads, switch writes, and only then drop the old column, with a deploy between each step.

Premature optimization is the root of all evil.

Donald Knuth, Structured Programming with go to Statements, 1974

Quick takeaway

The query plan is the source of truth.

Keeping it fast

🔎

Explain first

Read the plan before changing anything.

🪜

Expand and contract

Migrate in small, reversible steps.

🧹

Prune indexes

Remove the ones nobody reads.

Indexes are not free. Each one slows writes and takes memory, so we review unused indexes every quarter and drop the ones the statistics say nobody reads.

Wrapping up

We will revisit this in six months with fresh numbers. If the results change, we will update the post and note what changed at the end.

Priya Raman

Priya Raman

Author

Database and performance engineer. Priya reads query plans for fun and believes every slow page has a story worth measuring.

Related Posts

Comments 8

Ravi Shankar

Great post. Did you measure the impact before and after, or is this mostly from experience?

Oskar Nilsson

I would push back a little on the second section. In our monorepo the trade-off went the other way.

Liam O’Brien

Would love a follow-up on how you test this in CI.

Chloe Martin

It should work without shell access, you just lose the command-line shortcuts.

Priya Raman Author

How does this hold up on older Safari versions? That is where we always get burned.

Chloe Martin

Our accessibility audit flagged exactly this last month. I wish I had read this first.

Kofi Mensah

Glad it helped!

Sofia Rossi

We run this in production at a fairly large scale and can confirm the caveats are real.

Leave a comment

Your email will not be published. Sign in for a faster experience.

Supports **bold**, *italic*, and `code`.