Indexes are not free

The write path gets slower first, and not by enough to look like an incident. A few extra milliseconds on inserts. A little more CPU during ingestion. A migration that used to finish quickly now sits there long enough for someone to start watching it. Then someone opens the table definition and finds fifteen indexes.

Each one made sense at the time. A slow dashboard query. A filter in an admin screen. A report someone needed before a meeting. No single index looks reckless. Together, they changed the cost of every write.

That’s the part teams miss. An index is easy to reason about when a read is slow and you can feel the payoff. It’s much harder to remember once the query goes away and the database quietly keeps paying for it.

An index is a promise your database keeps on every write

When you write a row, the database doesn’t just append it to the table. It also updates every index that covers that row. That’s the deal. An index isn’t a free read optimization, it’s a contract: for every write, the database will also update this side structure so that reads can skip a full scan.

One index on a ten-million-row table with low write volume is a cheap contract. Twelve indexes on an orders table taking five thousand inserts a second is a different story. Now every write is also twelve separate b-tree updates, and you’ve traded write throughput for read speed without necessarily noticing you made the trade.

The write cost is invisible until it is not

Indexes don’t show up in slow query logs when they’re the thing hurting you. You see the slow writes, the lock wait times, the CPU climbing during ingestion. What you don’t always see is the line connecting those symptoms back to the eight indexes you added to support an analytics query someone runs twice a week.

That’s the trap. Indexes get added one at a time, for good reasons, by different people, at different points in the system’s life. No single one is wrong. The aggregate is.

CREATE INDEX ON orders (status);
CREATE INDEX ON orders (customer_id);
CREATE INDEX ON orders (created_at);
CREATE INDEX ON orders (status, customer_id);
CREATE INDEX ON orders (customer_id, created_at);
CREATE INDEX ON orders (status, created_at, customer_id);

Six indexes on one table. Each one had a query that needed it. Together, they mean every insert into orders is doing six b-tree updates, and any bulk load or high-frequency write path is now paying for queries that might not even be in the hot path anymore.

Partial indexes exist and most teams underuse them

A full index on status when ninety-eight percent of rows are completed is a bad deal. You’re indexing almost everything to find almost nothing.

A partial index changes the contract:

CREATE INDEX ON orders (customer_id)
WHERE status = 'pending';

Now the index only covers pending orders. It’s smaller, faster to update, and more useful for the specific query pattern that actually needs it, and the storage and write cost fall right along with it.

This isn’t an advanced feature. It’s a basic one that teams skip because the syntax is unfamiliar, or because the habit of full-column indexes is already set.

Unused indexes are not neutral

An index that no query uses still costs you on every write. PostgreSQL tracks index usage in pg_stat_user_indexes, where you can see scan counts and how long it’s been since a given index was touched. What you tend to find are indexes from two years ago, added for a query that no longer exists or got rewritten to take a different path.

SELECT
  indexrelid::regclass AS index_name,
  relid::regclass AS table_name,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

This returns indexes with zero scans, and that list is never empty on a system that’s been running for more than a year. Unused indexes cost you on writes and give you nothing back. Drop them.

Index maintenance during migrations is where things get slow

Migrations slow down because of indexes, not because of the data move itself. Adding a column to a large table can be near-instant in modern Postgres. But adding a column and then building an index on it turns a seconds-long operation into a minutes-long one that holds locks the whole time.

The lock matters more than the duration. A migration that holds an exclusive lock for three seconds on a high-traffic table is a visible outage, even if three seconds sounds harmless.

Concurrent index builds exist for exactly this reason:

CREATE INDEX CONCURRENTLY idx_orders_new_field ON orders (new_field);

Concurrent builds take longer overall but don’t hold exclusive locks. They can also fail and leave an invalid index behind, so they’re not magic. They’re just the right tool for production schema changes on hot tables.

The question is never whether to index

Every index is a bet: that the reads it enables will be more frequent and more valuable than the writes it slows down. High-read, low-write workloads can afford more of them. Append-only event logs behave differently than transactional order tables, and a reporting table rebuilt nightly from scratch has different constraints than a live table written to continuously.

So the questions worth asking are about your table, not the index in the abstract. What’s the write volume? Which queries are actually in the hot path, and which ones get run by three people twice a month? Those answers decide what’s worth keeping. Not the query planner’s suggestion, not the fact that a column shows up in a WHERE clause somewhere, and definitely not that a given index made a slow query fast in a dev environment with ten thousand rows.

Treat indexes as costs, not features

Note which query each index serves when you add it, and review what’s already on a table before you add more. The query above is fast to run, so make it routine.

If a table has high insert volume, a new index has to justify itself against that baseline. A 5ms read improvement that adds 2ms to every insert on a table doing ten thousand inserts a minute is a completely different calculation than the same trade on a table that sees fifty inserts an hour.

The database keeps the contract

Most teams add indexes when queries are slow. Very few go back and revisit them when the query goes away. Every index you add is a standing commitment: the database will honor it on every write, forever, until you explicitly drop it. If nobody reviews that commitment later, the table slowly collects obligations nobody remembers agreeing to.

The write cost never shows up on a dashboard. It doesn’t trigger an alert. It just accumulates, quietly, until one day you’re debugging write latency on a table with fifteen indexes, wondering where the time went.