Adding an index is the cheapest-feeling change in a database. One line, no migration risk to speak of, and a query goes from slow to instant. Nobody asks what it costs, because the cost is paid by a different team on a different day, spread evenly across every write the table will ever take.
I measured it. Eight indexes on a table cut insert throughput by 70%, and an index on a column that gets updated took heap-only tuple updates from 89,555 out of 200,000 down to exactly zero — which is a second, larger cost that almost nobody counts.
What each index costs on writes
The benchmark is deliberately plain: a table with eight integer columns and a text payload, insert 200,000 rows, vary how many of those columns are indexed. synchronous_commit off, so the measurement is index maintenance rather than disk flushes.
The spread deserves a word. Across three rounds the rates varied by up to 27%, which is a lot — it is a laptop, with a container, and other things running. But the trend is a 3.3× difference from end to end, far outside that noise, and it is monotonic. So the ratios are the finding and the absolute numbers are not; run it on your own hardware if you want the second kind.
The shape is worth noting too. The first index is nearly free at about 10%. The marginal cost then grows: going from four indexes to eight costs more than going from zero to four. Indexes compete for buffer cache and WAL bandwidth as well as for CPU, so they get worse together rather than adding up independently.
The cost that does not appear in any write benchmark
Here is the one that surprised me, and it is much more consequential than the insert rate.
Postgres has an optimisation called heap-only tuples. When you update a row, it normally has to write a new row version and a new entry in every index, because every index points at a physical location that just changed. HOT lets it skip all of that, under one condition the documentation states exactly: "The update does not modify any columns referenced by the table's indexes, not including summarizing indexes."
So an index on a column changes the cost of updating that column, permanently, for every writer. And the benefit HOT provides is not only the skipped index writes: "When a row is updated multiple times, row versions other than the oldest and the newest can be completely removed during normal operation, including SELECTs, instead of requiring periodic vacuum operations."
I want to be precise about what I am claiming. The throughput difference between updating an indexed and an unindexed column did not reproduce across runs — one run had the unindexed case 38% faster, another had it 5% slower — so I am not claiming a speedup. The HOT counts reproduced exactly: 89,555 and 0. What that buys you is fewer index writes and less vacuum work, which shows up as bloat and autovacuum load over weeks rather than as throughput in a two-second benchmark.
The other half of that figure is also worth keeping: even with no index in the way, only 45% of updates got the HOT path. HOT also requires "sufficient free space on the page containing the old row for the updated row", and a freshly loaded table at the default fill factor has none. That is what fillfactor is for, and on a heavily updated table it is often a bigger win than anything you will do to the indexes.
And what it buys
None of this is an argument against indexes. It is an argument against the ones nobody checked.
Fourteen times, on a table small enough to fit entirely in memory. On a table that does not fit, the difference is larger by orders of magnitude, because the sequential scan becomes an IO problem rather than a CPU one.
So the question was never whether the index helps. It is whether it helps often enough.
The ledger
calculator · index cost
Does this index pay for itself?
measured insert throughput by index count, 200,000 rows on Postgres 17.11
the ledger for this one index, per day
The percentage slowdown is measured; the milliseconds are yours. Using the benchmark's own insert rate as a cost per write would be wrong by orders of magnitude, because a bulk load with synchronous_commit off is not what an application does — so the tool applies the measured percentage to the write latency you supply. The curve: 200,000 rows, best of 3 rounds, on Postgres 17.11 with synchronous_commit off so the disk is not the bottleneck. The spread across rounds was 7% to 27%, so treat the ratios as the finding and the absolute rates as machine-specific. Two costs are not in this ledger: the index blocks heap-only tuple updates on its column, which in the same benchmark took HOT updates from 89,555 to zero, and it has to be rebuilt or maintained through every restore, failover and major-version upgrade.
The default is a moderately busy table: four indexes already, half a million writes a day, two million queries of which 2% would use the new index. Move the "share this index helps" slider and watch the answer flip — that single number, which nobody knows and everybody guesses, decides the whole thing.
Two experiments:
Set the share to 0.1%. A query that runs a couple of thousand times a day. The index still helps every one of those queries, and it still loses, because the write tax is paid five hundred thousand times.
Set existing indexes to 8. The marginal write cost rises, so the same index that paid for itself on a table with four indexes does not on a table with eight. Index decisions are not independent of each other, which is why "just add one more" gets progressively worse advice.
How to find the ones that are not earning
Postgres tracks this and almost nobody looks. pg_stat_user_indexes records how many times each index has been scanned since the last statistics reset:
SELECT s.relname AS table, s.indexrelname AS index,
s.idx_scan AS scans,
pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE NOT i.indisunique
ORDER BY s.idx_scan, pg_relation_size(s.indexrelid) DESC;Zero scans and a large size is the obvious candidate. Two cautions before dropping anything: statistics reset on pg_stat_reset() and are not replicated, so check the counters on the replica that serves your reads as well as on the primary, and an index that has not been scanned in a month may still be the one that makes the quarterly report finish.
The safe sequence is DROP INDEX CONCURRENTLY after recording the definition, or — better on a production table you are unsure about — mark it invisible first. Postgres has no ALTER INDEX ... INVISIBLE, so the usual approach is to drop it concurrently during a low-traffic window with the CREATE INDEX CONCURRENTLY statement already written and ready to paste back.
| Signal | What it means | Action |
|---|---|---|
idx_scan = 0 on primary and replicas, index is large | Nobody has used it since stats reset | Drop it, keep the definition |
idx_scan low, column is frequently updated | Costing both write throughput and HOT | Drop it unless the query is critical |
| Duplicate leading columns with another index | The narrower one is usually redundant | Drop the narrower, keep the composite |
High idx_scan, high idx_tup_read per scan | It is being used and is not selective | Consider a better index, not no index |
quick check
You add an index on status, a column that changes on most rows several times a day. What is the largest cost?
HOT only applies when the update touches no indexed column. Indexing a frequently updated column means every one of those updates now writes new index entries and leaves a dead row for vacuum, instead of being cleaned up in place. In the benchmark this took HOT updates from 89,555 out of 200,000 to zero.
What this benchmark does not settle
Your hardware is not this hardware. Round-to-round spread was up to 27% on a laptop with synchronous_commit off. On real storage with durable commits, the disk becomes a larger share of the cost and the relative index penalty falls. The ratios are directionally right; the rates are not yours.
Index type matters and only one was tested. These are default B-tree indexes on integers. A GIN index on a JSON column costs far more to maintain, a BRIN index costs almost nothing and is a summarising index that does not block HOT at all. The documentation's HOT condition explicitly excludes summarising indexes, which is one of the strongest arguments for BRIN on append-mostly tables.
Partial and covering indexes change the arithmetic. A partial index on a predicate matching 1% of rows costs roughly 1% of the write penalty, which often turns a losing index into a winning one. If the calculator says no, a WHERE clause on the index is the next thing to try, not a shrug.
Bloat is not in the ledger. Every non-HOT update leaves a dead row version for autovacuum, and indexes bloat independently of the table. A table that looks fine on insert throughput can still be spending its afternoons vacuuming, which is the same class of invisible cost as an oversized connection pool — the resource looks adequate and the contention is elsewhere.
Write amplification compounds with everything else on the table. A table that is both heavily indexed and heavily read is competing with itself for the same buffer cache, which is the shape of problem that also shows up as a queue that stops draining once the write path slows down enough.
Reads and writes are not the same currency. The calculator adds milliseconds on both sides, which assumes an engineer-second of query latency is worth an engineer-second of insert time. For a user-facing read and a background batch write, the read is worth much more; for an ingestion pipeline that is already behind, the reverse. Weight them yourself before believing the sign.
questions people ask
How much does an index slow down inserts in Postgres?
In these measurements on 200,000 rows, one index cost about 10% of insert throughput, two cost 30%, four cost 51% and eight cost 70%. The marginal cost grows rather than staying constant, because indexes compete for buffer cache and WAL bandwidth as well as CPU.
Does an index slow down updates too?
Yes, and in a way that is easy to miss. An index on a column blocks the heap-only tuple optimisation for updates to that column, so every update writes new index entries and leaves a dead row for vacuum. In this benchmark, HOT updates went from 89,555 out of 200,000 to zero.
How do I find unused indexes in Postgres?
Query pg_stat_user_indexes for idx_scan, excluding unique indexes. Check the replicas as well as the primary, since read traffic often goes elsewhere and the statistics are not replicated, and remember that a monthly report can make an index look unused for four weeks out of five.
Is it safe to drop an index on a live table?
DROP INDEX CONCURRENTLY avoids taking a lock that blocks writes. Record the full definition first so it can be recreated with CREATE INDEX CONCURRENTLY, and prefer a low-traffic window so that a wrong guess is recoverable before anyone notices.
When is an index definitely worth it?
When it is a unique constraint, which is correctness rather than performance, and on foreign key columns, where its absence turns deletes on the parent into sequential scans of the child. Beyond those, it is arithmetic: the read time saved has to exceed the write time added.
Does fillfactor matter?
On heavily updated tables, a great deal. HOT needs free space on the page as well as an unindexed column, and a freshly loaded table at the default fill factor has none — even with no index in the way, only 45% of updates got the HOT path in this benchmark.
The short version
Indexes are not free and the price is not the disk. Four indexes halved insert throughput in my measurements and eight took 70% of it, and those numbers are paid on every write for the life of the table.
The larger cost is the one that does not appear in a write benchmark: an index on a column you update blocks heap-only tuples for that column, so each update writes new index entries and leaves work for vacuum. That showed up as 89,555 HOT updates becoming zero.
Against that, an index on the right query is one of the best trades in the database — 14× here, more on a table that does not fit in memory. So the question is arithmetic rather than taste: how many queries a day does it actually serve, against how many writes a day pay for it. Postgres already records the first half in pg_stat_user_indexes, and almost nobody looks.