What Are the Cost and Consistency Tradeoffs of Secondary Indexes?

Write Amplification

Secondary indexes enable queries on non-primary-key columns, but every write to the base table must also update all secondary indexes. A table with 2 secondary indexes writing 10K items/sec generates 30K effective writes: 10K to the base table and 10K to each index. This "write amplification" multiplies both write throughput and storage costs.

Consistency Models

Secondary index updates can be synchronous (index updated in the same transaction as base data) or asynchronous (index updated later). Synchronous updates guarantee consistency but add latency: every write waits for index updates. Asynchronous updates are faster but queries may temporarily see stale or missing data. The delay is typically milliseconds to seconds depending on system load.

Local vs Global Secondary Indexes

In distributed databases, local secondary indexes are co-located with the data they index (same partition). Queries using the partition key plus local index are fast. Global secondary indexes are stored separately and can query across partitions, but writes must update a remote partition, adding latency. Global indexes also create hot partitions (a single partition receiving disproportionate traffic) if the index key has skewed distribution.

When to Use

Add secondary indexes when read patterns justify the write cost. A query running 1000x/day saving 100ms each justifies an index. A query running 10x/day may not justify doubling write costs.