Secondary Index Write Saturation

When a table carries many secondary indexes, each INSERT or UPDATE must maintain all indexes, amplifying the write I/O by the number of indexes. At high write throughput (10,000+ inserts/second), index maintenance saturates WAL throughput, storage I/O bandwidth, or shared_buffers write capacity, causing write latency to spike from sub-millisecond to tens of milliseconds. The amplification grows linearly with index count and write rate, making this failure predictable but frequently discovered only after a traffic spike.

Every secondary B-tree index on a PostgreSQL or MySQL table requires a separate index page write for every INSERT, and potentially for every UPDATE that modifies an indexed column. A table with 10 indexes amplifies write I/O by approximately 10x relative to a heap-only write. For a workload producing 10,000 inserts/second, maintaining 10 indexes generates 100,000 index writes/second. Each index write is a random write to the index B-tree (as opposed to sequential heap appends), making it particularly expensive on rotational storage and moderately expensive on SSDs.

WAL is the primary bottleneck in PostgreSQL. Every heap write and every index write generates WAL records. A table with 10 indexes generates 11 WAL records per INSERT (1 heap + 10 index). At 10,000 inserts/second with 200 bytes per WAL record, total WAL write throughput is 22 MB/s: manageable on most storage. But complex indexes on wide columns (JSONB, TEXT arrays, full-text GIN indexes) generate much larger WAL records. A GIN index update for a JSONB document may generate 2–10 KB of WAL per document, driving total WAL throughput to 200+ MB/s on insert-heavy workloads, saturating even NVMe storage at sustained rates.

The saturation manifests progressively. At moderate write rates, index maintenance adds 5–15% latency overhead. As write throughput approaches the I/O ceiling, WAL writes begin queueing. The WAL fsync becomes a bottleneck: PostgreSQL must fsync WAL to durable storage before acknowledging commits. When fsync queues accumulate, commit latency increases from <1ms to 10–50ms. Client applications see their INSERT response time increase linearly with write throughput, then spike discontinuously when WAL I/O saturates.

Index bloat compounds the problem over time (see index_fragmentation). Fragmented indexes have more pages to update, increasing the I/O cost per write. A table that was within I/O budget on initial deployment can exceed it 12 months later due to the combined effect of write rate growth and index fragmentation.

The maintenance cost above is not uniform across engines, because it depends on how the primary key relates to physical storage. PostgreSQL's default heap table stores rows in an unordered heap; even the index backing the primary key is, physically, just another B-tree index, one that points at heap tuples via a TID (tuple identifier: block number and offset). Because the primary key index carries no privileged storage role, its per-write maintenance cost is roughly the same as any other secondary index on the table, which is why counting total indexes is a reasonable proxy for write amplification in PostgreSQL. InnoDB is a different machine. Its primary key is a clustered index: the table's row data is physically stored in primary-key order inside the index's own leaf pages, so there is no separate "primary key index" write in PostgreSQL's sense, the row write and its primary-key-ordered placement are the same operation. But every other, secondary, index on an InnoDB table stores the indexed column values plus the primary key value rather than a physical row pointer, and a query that needs a column outside the secondary index must take that stored primary key value and perform a second lookup into the clustered index to fetch the full row. This is the mechanism InnoDB engineers call secondary index indirection, and it is a cost that does not exist in PostgreSQL's model in the same form, PostgreSQL's heap TID is already a direct physical pointer, not a second index traversal. The indirection cost is avoidable per query with a covering index, one that includes every column the query needs so the engine never touches the clustered index, but a query that reads any column outside the secondary index pays the extra lookup on every matched row, not just on the write path. Cassandra's native secondary indexes are a narrower tool again: they are local to each node's own data rather than global, so a query filtering only on a secondary-index column that is not part of the partition key must fan out to every node in the cluster instead of being served by a single partition lookup, a well-known operational pitfall rather than a drop-in substitute for a global index.