Index Table

The Index Table pattern creates additional tables that index data by fields commonly used in query criteria but that are not the primary key. These index tables let the application look up records quickly by alternate keys, avoiding slow full scans.

The problem appears in data stores, especially key-value and document NoSQL stores, that retrieve efficiently only by primary key. Queries on other fields force a full scan, which is slow and costly as data grows.

How It Works

For each frequently queried non-key field, the application maintains a separate index table keyed by that field, whose values point to the primary records (or contain a copy of needed fields). When data changes, the application updates both the main store and the index tables.

Two common shapes exist. An index table can map the indexed value to a list of primary keys, requiring a second lookup to fetch full records. Alternatively, it can denormalize and store the needed columns directly, trading storage and consistency effort for single-lookup reads. Some systems provide global secondary indexes (for example DynamoDB) that automate this; the pattern is the manual equivalent where they are absent or insufficient.

When to Use It

Use it when an application frequently queries a data store by non-primary-key fields and the store lacks adequate secondary indexing, and when read performance for those queries matters more than the extra write and storage cost.

Avoid it for fields rarely queried, for small datasets where scans are cheap, or where the store already provides efficient secondary indexes.

Trade-offs

Maintaining index tables adds write overhead and storage, and introduces a consistency challenge: index and base data must be kept in sync, often eventually rather than atomically, so reads may briefly see stale indexes. More indexes mean more to update on every write.

The benefit is fast lookups on alternate keys without expensive scans, which can be decisive at scale.

Related Patterns

It is closely related to Materialized View, which precomputes query-shaped results; index tables are a focused form of that. Cache-Aside can cache index lookups. Sharding partitions both base and index data for scale.

Example

An order store keyed by orderId also needs lookups by customerId. The application maintains an index table keyed by customerId whose value is the list of that customer's order IDs. When an order is created, it writes the order and appends its ID to the customer's index entry. A query for a customer's orders reads the index in one lookup, then fetches each order by primary key, avoiding a full scan of the order table.