What Do Indexes Cost on Writes?

Three things. Storage, work, and unpredictability.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders (customer_id, amount) VALUES
  (1, 120.00), (1, 340.50), (2,  75.25);

SELECT 'table only' AS stage,
       (SELECT COUNT(*) FROM orders) AS rows_stored,
       (SELECT * FROM pragma_page_count) AS pages_used;

CREATE INDEX idx_orders_customer ON orders (customer_id);

SELECT 'with index' AS stage,
       (SELECT COUNT(*) FROM orders) AS rows_stored,
       (SELECT * FROM pragma_page_count) AS pages_used;

-- The index is maintained on every write, so it is correct immediately.
SELECT COUNT(*) AS rows_for_customer_1
FROM orders
WHERE customer_id = 1;

The page counts show the storage cost directly: the table occupies some pages on its own, and the index adds pages on top of it. The row count stays at three — an index stores no rows, it stores entries pointing at them — and the final query returns 2, confirming the index is correct straight after the write.

The work is the second cost. Every INSERT has to add an entry to every index on the table, and every UPDATE that changes an indexed column has to update or delete the old entry and add a new one. That is a B-tree insert per index, and it has to be durable, because the index has to be consistent with the table after a crash. On a write-heavy table, five indexes can turn a fast insert into a slow one.

The third cost is unpredictability. A random primary key means the index entries are written in random order, so every insert touches a random page and may have to split one, leaving half-empty pages behind. The same insert against a sequential key appends to the right-hand edge, touches the same hot pages and never splits. This is why the choice of primary key affects write performance so much, and why the page count of a badly-indexed table can be far larger than the data warrants.

Start from the queries, not from the columns. An index is worth keeping only if it serves a query that matters, and the only reliable way to know which queries matter is to look at what the application actually runs and how often.

Then check whether it is earning its keep. Every engine gives you the usage statistics for its indexes: PostgreSQL's pg_stat_user_indexes shows how many times each index has been used, and MySQL's performance_schema shows the same. An index with zero reads since the statistics were last reset is a candidate for removal. But treat zero as a warning rather than a verdict — an index that exists to enforce a UNIQUE constraint is used by the write path even if no query reads it, and removing it removes the constraint.

After that, weigh the write cost against the read benefit. A table that is written far more often than it is read — a session table, an audit log, an event stream — should have as few indexes as possible, because every write pays for all of them. A table that is read constantly and written rarely is the opposite case, and can afford many indexes.

Then check for redundancy. If you have an index on (a) and another on (a, b), the first is usually redundant: the composite index can serve any query the single-column one could, because of the leftmost-prefix rule. The exception is index size — if the composite index is very wide, a narrow single-column index may still be worth keeping because it is more likely to fit in the buffer cache.

And check for the worst case: an index on a column with very few distinct values. If a column has three possible values and a million rows, the index is huge and the planner will ignore it for most queries anyway, because a scan of a third of the table is cheaper than a seek. That index is pure write overhead.

Finally, remember that removing an index is itself a schema change with a cost — it is usually fast, but it can lock the table briefly, and it changes the plans of queries that were using it, which can make some of them slower. Do it deliberately, in the same careful way you would add one.