What Are the Cost and Consistency Tradeoffs of Secondary Indexes?

Secondary indexes enable queries on non primary key columns, but they come with significant cost and consistency implications that vary by system architecture. Every write to the base table must propagate to all secondary indexes, multiplying write amplification. In Amazon DynamoDB, a table with 2 Global Secondary Indexes (GSIs) writing at 10K items per second generates 30K effective write operations: 10K to the base table and 10K to each GSI. Since DynamoDB charges per Write Capacity Unit (WCU) and storage, this triples both write throughput costs and storage Input/Output (I/O) costs.

Consistency models create a fundamental tradeoff between write latency and read correctness. Google Cloud Spanner implements strongly consistent secondary indexes as independent, distributed replication groups. Every index update participates in the same two phase commit transaction as the base table write, guaranteeing readers always see consistent state. The cost is higher write latency (typically tens of milliseconds intra region for p50, with p99 dominated by cross shard coordination) and reduced write availability during network partitions or node failures.

Amazon DynamoDB GSIs use eventual consistency: updates propagate asynchronously via change streams, allowing base table writes to complete quickly without waiting for index updates. This maximizes write throughput and availability, achieving single digit millisecond p50 latencies. However, under sustained high write rates (50K to 100K writes per second), index lag can reach seconds, meaning queries against the GSI may miss recently written items or return stale values. Applications must tolerate read your writes gaps or pay for strongly consistent reads at higher latency and cost.

Index maintenance creates operational complexity. Building a new index on a large existing table requires scanning billions of rows while concurrently capturing ongoing writes. Google Cloud Spanner backfills indexes online at tens to hundreds of megabytes per second per node, using change capture to dual write new updates during the scan. Microsoft SQL Server online index builds similarly throttle backfill to protect foreground query latency, but on multi terabyte tables the process can take hours, during which time the partially built index consumes storage and write capacity without serving reads.

💡 Key Takeaways

•Write amplification multiplies by number of indexes: 2 secondary indexes on table with 10K writes per second generates 30K total write operations tripling cost and storage I/O

•Strongly consistent indexes (Spanner) add 10 to 30 milliseconds to write latency and reduce availability during partitions but guarantee readers always see current state

•Eventually consistent indexes (DynamoDB GSI) achieve single digit millisecond writes but lag can reach seconds at 50K to 100K writes per second causing read your writes inconsistency

•Online index builds on terabyte scale tables backfill at 50 to 200 megabytes per second per node, taking hours while consuming write capacity and storage for partially built structures

•Each Global Secondary Index in DynamoDB is independently provisioned for throughput and scales separately, requiring capacity planning and monitoring per index to avoid throttling

📌 Examples

Amazon DynamoDB: Table with 3 GSIs writing at 20K items/sec provisions 80K WCU total (20K base + 60K indexes); at $1.25 per million writes, this is $100/million base writes versus $400/million with indexes

Google Cloud Spanner: Creating a secondary index on a 10TB table with 100 nodes backfills at 10 to 20GB per minute; full build takes 8 to 16 hours with concurrent dual write capturing new updates via change streams

Microsoft Azure SQL: Index rebuild on 500M row table in online mode scans 500K rows per second with 10 percent throttle limit to keep foreground query p95 under 50 milliseconds; total time 16 minutes versus 5 minutes offline

← Back to Indexing Strategies Overview