Part 6 of 8 · 3 min read
What indexes cost
The extra writes and space behind every index, with a count for a busy table.
Think of a library that keeps a card drawer by author, another by title and another by subject. Every new book means three new cards. A library with ten drawers is slow to receive books.
The same happens in a database. Each insert, delete and update of an indexed column must also change every index that includes it.
A worked example. An orders table has the primary key plus 4 secondary indexes. The system inserts 2,000 orders per second at the peak.
- One insert writes the row (or the clustered index entry) plus 4 index entries: 5 writes.
- At 2,000 inserts a second: 2,000 × 5 = 10,000 write operations a second.
- With a single index (primary only): 2,000 write operations a second.
- Five times more writes for the same business. If the disks handle 8,000 write operations a second, the table cannot keep up.
Updates matter too. If a column is updated and indexed, the old index entry is removed and a new one added: 2 index operations. An update to a column with no index touches only the row.
Space is the other cost. Suppose each index entry is 24 bytes (an 8-byte key, an 8-byte row id, some overhead; made up).
- One index on 10 million rows: 10,000,000 × 24 = 240,000,000 bytes = 240 MB.
- Four secondary indexes: 960 MB, nearly 1 GB.
- Indexes should fit in memory where possible. A 1 GB set of indexes is easy to keep in a server with 16 GB. At 100 times the rows, it is 100 GB, and no longer fits, so lookups start to read from disk.
The book gives the underlying reason. For writes, nothing beats appending to a file, and any index slows that down. Log-structured storage (module 4) reduces the cost by making index writes sequential, but does not remove it.
Practical rules:
- Index for the real queries. Write down the queries from the access patterns (module 2), and for each one decide the index.
- Remove unused indexes. Databases can show index usage counts. An index that is never read is pure cost.
- Avoid indexing columns that change constantly, such as a "last seen" time stamp updated on every request. Every update then rewrites an index entry.
- Be careful with very wide indexes, such as a long text column. They are large and slow.
- Add indexes online on big tables. Building an index on 10 million rows can lock a table for minutes unless the database supports building it in the background.
| Table type | Typical number of indexes |
|---|---|
| Write-heavy log or events | 0 to 2 |
| Typical transactional table | 2 to 5 |
| Read-heavy reporting table | More, as the queries need |
For the curious For the curious
Bulk loads are often faster if the indexes are dropped first, the data is loaded, and the indexes are rebuilt at the end. One sorted build of an index is cheaper than millions of small insertions.
Check yourself:
- A table has 6 indexes including the primary. How many write operations does one insert cause?
- Why might a "last seen" column be a bad candidate for an index?