The Bill for Every Index

Last timeReading What the Database Tells You

An index makes some reads fast and makes every write to its table slower, takes space, competes for memory and has to be maintained forever. The bill is real and usually unexamined.

Indexes are discussed as though they were free, as a thing you add when a query

is slow. Every one of them is a second copy of some of your data, maintained

synchronously, forever, by every write that touches the table.

FIG 1What one insert has to do on a table with three indexes

Nothing in this sequence can be skipped or deferred in a system that keeps its indexes consistent with its table. The whole of the write cost of an index is here, and it is paid on every insert and delete whether or not any query ever uses the index.

The write bill

An insert into a table with no indexes modifies one page. On a table with six

indexes it modifies seven, in seven different places, and all seven have to be

made durable.

Updates are more subtle. An update only has to touch the indexes that contain a

column it changed, which means a table with many indexes can still have cheap

updates if the frequently changed columns are not indexed. That is a design

lever worth knowing about: keeping the hot column out of every index keeps the

hot path cheap.

FIG 2What one write costs as indexes are added

: the total page modifications one write causes
: the pages the table row itself touches, normally one
: the number of indexes that have to be updated
: the pages one index update touches, normally one, occasionally more when a page has to be split
: the index bill, which grows in a straight line with the number of indexes and has no ceiling

The term that matters grows linearly with the index count and is paid on every write forever, while the benefit of each index is enjoyed only by the queries that use it. That asymmetry is the whole argument for auditing indexes rather than accumulating them.

FIG 3Insert throughput as indexes are added to a table

measured insert ratethe rate with no indexes

The fall is steepest at the start, because the first index roughly doubles the number of pages a write touches. Past four or five the marginal damage is smaller in proportion, which is unfortunately why tables accumulate indexes once they already have a few.

The memory bill

Indexes live in the same memory as the table data. A large index that is being

read and written keeps its pages resident, and those pages displace table pages

that other queries were depending on.

This is the index cost that is almost never attributed correctly. A query that

does not use the new index gets slower, because the pages it used to find in

memory now come from storage. Nothing about that query changed, nothing in its

plan changed, and the cause is three steps away.

The lesson stops here

4 more paragraphs to go

You have read the opening. The rest of the argument, the problems that check whether it landed, and the lines worth keeping at the end all come with a plan.

The first lesson of every course in the library reads the whole way through, free, so you can see exactly what the rest of them are.

See the plan The contents

This is the reading half

Starting the course gives you your own copy of it. Every idea on every page has problems standing under it, marked with a reason rather than a tick, and any sentence you do not believe can be opened and argued with. None of that can happen on a page nobody owns.

The contents

The Bill for Every Index

The write bill

The memory bill

4 more paragraphs to go

This is the reading half

Read alongside