Index Table

A cookbook is organized by chapter — soups, mains, desserts. That's great if you know you want dessert. But if you've got a bag of mushrooms and want every recipe that uses them, you'd have to read the whole book page by page. That's why good cookbooks add an ingredient index in the back: look up "mushrooms" and jump straight to the right pages.

Index Table is exactly that back-of-the-book index, but for a data store that can only find things by its primary key. You build and maintain a second table keyed by the field you actually search on.

Many data stores — especially key-value and partitioned cloud stores — are blazing fast when you look up a record by its primary key, and miserably slow at everything else. Picture a users table sharded across partitions by user ID. "Get user 7" is one hop, because the ID tells you which partition holds it. But a login form has an email, not an ID, and nothing about an email says where its user lives. The store has no choice but to ask every partition and check every row.

In a relational database you'd just add a secondary index and move on. But the simpler stores at cloud scale often don't offer secondary indexes, or limit them severely. Without one, your common-but-non-key queries get slower and pricier as the data grows, until the full scan becomes unworkable. Step through both lookups below, and predict how much work the email search costs before you watch it run.

You create a second table whose key is the field you want to query by. To find users by email, you build an index table keyed on email, where each entry holds the matching user's ID. Now a login is two direct lookups: email → ID in the index, then ID → user in the main table. The same trick handles one-to-many lookups too: an index keyed by customer ID can list every order ID that customer has placed.

There are two flavors. A lean index table stores just the key and a pointer — the primary keys of the matching records — so you then fetch the full rows from the main table. A fatter one duplicates whole records (or the columns a query needs) into the index so the lookup returns everything in one hop, trading storage and write cost for read speed. Either way, your application is responsible for writing to the index whenever the source data changes.

Step through a lookup below, then follow what happens when Ada changes her email. Predict what a search finds while the index update is still queued, then flip to Same transaction to compare.

There are two ways to keep an index table current, and the scene showed both. If your store can write several items in one transaction, update the row and its index entries together: the index is never stale, but every write gets slower, and many partitioned stores can only do this within a single partition. Otherwise, update the index asynchronously from a change feed or a queue. Writes stay fast, but the index trails the data by a moment, and the app has to live with that window.

Async indexes need a few guards. Apply updates idempotently and in order — a version number on each row lets you ignore an old update that arrives late. Treat the index as a hint, not the truth: after following an entry, check the fetched row really matches what you searched for. And run a periodic repair job that compares the two and fixes drift, because sooner or later an update will go missing.

Use an index table when you frequently query a store on a non-key field and the store itself can't index it for you — the typical situation with large key-value or sharded NoSQL stores. It's a great fit alongside CQRS, where the read side is free to maintain whatever purpose-built lookup structures make queries fast.

Skip it when your database already supports the secondary indexes you need — let the engine do the work and keep the consistency guarantees. And weigh the write penalty: if the field is rarely queried but constantly updated, the extra writes and sync risk may cost more than the occasional scan you're trying to avoid.