In the index table pattern, a write to a base entity needs to also update a separate index table keyed by a non-key attribute of that entity. Describe two different strategies for keeping the two tables consistent with each other, and what each one trades off in terms of latency and consistency guarantees.

## The gap the store leaves Keeping an index table faithful to its base table is the central operational challenge of this pattern, because the store that forces you to build the index table in the first place is usually also a store that doesn't give you a native way to update two tables atomically as a single unit — that's exactly the feature ('do this write and this other write together, or neither') a relational database gives for free via multi-statement transactions and secondary indexes give for free via engine-internal index maintenance. Two broad strategies fill that gap, and the choice between them is a genuine trade-off, not a matter of one being categorically better. ## Strategy one — the transactional dual write The **first strategy** is a transactional or batched dual write, used wherever the underlying store offers some scoped multi-item atomicity. - DynamoDB's `TransactWriteItems` lets you submit up to 100 item writes across multiple tables as a single all-or-nothing operation. - Azure Table Storage's entity group transactions let you batch up to 100 operations against entities that share the same partition key within one table. If your index table entry and base table entity can be expressed as operations inside that scope, you get true atomicity: either the base row and the index row both land, or neither does, and there is no window where an external reader can observe one without the other. The cost is real, though. Transactional writes typically: - cost more — DynamoDB bills transactional writes at roughly double the capacity units of a plain write; - carry higher latency, because the store has to coordinate the commit across the involved items; and - are, **critically**, usually scoped narrowly. Azure's entity-group transaction requires everything in the batch to share a partition key, which frequently doesn't align with the index table's natural partition key (the indexed attribute) versus the base table's partition key (the entity's own ID); you often can't get both tables into the same transaction scope at all, which is why in Azure's own documented guidance for this pattern, true single-transaction consistency between a base table and an index table is often not achievable, and the pattern is explicitly described as offering eventual consistency in the general case. ## Strategy two — asynchronous propagation The **second strategy** is asynchronous propagation: the base table write happens first, alone, as the single durable source of truth, and the index table update is derived from it afterward by a separate process. That derivation can be driven: - by a **change feed** or change-data-capture stream native to the store (Cosmos DB's change feed, DynamoDB Streams); - by an application-level **outbox pattern**, where the base write also writes a small 'pending index update' record in the same transaction as the base write and a background worker drains that outbox; or - simply by a **message queue** the writer publishes to after the base write succeeds. This decouples the two writes completely: the base write path stays fast and simple, retries and failures in the index-update path don't block or fail the caller's primary operation, and the propagation mechanism can batch, dedupe, and back off under load far more gracefully than a synchronous transaction could. The cost is that the index table becomes eventually consistent. There's a real, measurable window — milliseconds under DynamoDB Streams in the common case, potentially much longer under load, backpressure, or an outage of the propagation worker — during which a caller who writes an entity and immediately queries for it via the index table will get a miss. Any code that reads its own writes through the index path has to be written with that window in mind (read-after-write consistency isn't guaranteed), and the propagation pipeline itself becomes a new component that needs monitoring, dead-letter handling, and a repair/reconciliation job for the cases where an event is dropped entirely. ## Combining the two in production In production, the two strategies are frequently combined by scope: use transactional dual writes wherever the store's transaction scope happens to cover both tables cheaply (small, same-partition updates), and fall back to async propagation, with an explicit staleness SLA communicated to consumers, for everything else. A common real-world instance of this hybrid is a DynamoDB table pattern where a single-item update uses a conditional/transactional write to update both the item and a co-located attribute atomically, while a separate DynamoDB Streams-triggered Lambda repairs any cross-table index tables that couldn't be included in that transaction, reconciling drift on a lag measured in seconds. The engineering judgment call is picking, per index, whether the query paths that read through it can tolerate that lag. | Index | Tolerance | |---|---| | a login-by-email index | probably can't tolerate more than sub-second staleness | | a 'products in this price tier' browsing index | usually can |