A key-value store like Azure Table Storage or DynamoDB lets you look up an item fast only by its primary key (partition key + sort key). Your application also needs to find a customer's orders by their email address, which is not part of that key. Name a simple technique for making that lookup fast without switching databases, and say what it costs you.

## One access path, and only one Most key-value and wide-column stores — Azure Table Storage, DynamoDB before generalized secondary indexes matured, Cassandra's early versions, Riak, and similar systems — are built around a single primary access path: give the store a partition key (and often a sort/row key), and it returns the matching item in roughly constant time, because the engine can jump straight to the right partition and offset. Anything you query by that isn't part of that key requires the engine to walk every partition and every row, filtering as it goes — a full scan. For a table with a few hundred rows this is a minor inefficiency; for a table with millions of rows spread across many physical partitions, it's a query that times out or costs a fortune in read capacity. ## What the index table is The **index table pattern** solves this by manually building the secondary index the store doesn't give you for free. You create one additional table per attribute (or attribute combination) you need to query by. Its key is the attribute you want to search on — say, customer email — and its value is the primary key of the corresponding row(s) in the base table (or, in some variants, a copy of the fields you need to avoid a second hop entirely, though that reintroduces duplication problems). A query by email now becomes: 1. Look up the email in the index table (fast, indexed) to get back an order ID or list of order IDs. 2. Then look up those IDs in the base table (also fast, indexed). Two O(1)-ish lookups replace one O(n) scan. If a single email can map to many orders, the index table typically uses the email as the partition key and the order ID as the sort key, so a range query against that partition returns all matches in one request. ## Why the store makes you build it The reason this pattern exists at all is that the underlying store deliberately trades query flexibility for predictable, horizontally scalable performance — it won't build and maintain arbitrary indexes for you the way a relational database's `CREATE INDEX` does. The application takes on that responsibility instead, in exchange for keeping the system able to scale linearly by adding partitions. ## What it costs That trade shows up as real, ongoing costs. 1. **First is write amplification.** Every write to the base entity that touches an indexed attribute must also produce a write to the index table — and if the indexed attribute changes value (a customer updates their email), that write is actually two operations on the index table: delete the old key, insert the new one. With one index table this roughly doubles write cost; a catalog with four or five index tables (by category, by brand, by price tier, by warehouse) multiplies it accordingly, and every one of those writes consumes capacity units, network round trips, and latency budget on the hot write path. 2. **Second is storage cost.** Each index table duplicates at minimum the primary key of every indexed row, and often more. 3. **Third, and most operationally dangerous, is the consistency gap between the two tables.** Most of these stores do not offer multi-table ACID transactions (or offer them with caveats — DynamoDB's `TransactWriteItems` and Azure Table Storage's entity group transactions being partial exceptions, each with their own scope limits), so a naive "write base row, then write index row" sequence has a window where a process crash, network partition, or throttling response leaves the two tables disagreeing. The failure modes are concrete: - an index entry pointing at a base row that no longer exists — a **stale pointer** that returns a 404 downstream and has to be handled defensively; or - a base row that's simply invisible to anyone querying by the indexed attribute (an **orphaned write**) because its index entry was never created. Under sustained load, these small windows compound; a system doing thousands of writes a second without idempotent retries or a repair mechanism will accumulate drift. ## Where it shows up A widely cited concrete usage of this exact shape is Azure Table Storage applications that need a secondary lookup — for example, storing user profiles keyed by UserId in the base table, and maintaining a second table keyed by Email (or Username) whose row points back to the UserId, so a login-by-email flow doesn't have to scan every user. The pattern is documented explicitly under this name in Microsoft's Azure Architecture Center cloud design patterns catalog, precisely because Table Storage offers no native secondary indexing and this was, for years, the standard workaround. The same shape recurs informally anywhere an engineer bolts a lookup table onto DynamoDB before reaching for a Global Secondary Index, or onto a sharded MySQL/Cassandra deployment where cross-shard secondary lookups aren't otherwise possible.