Index Table

NoSQL stores are optimized for primary-key access: get(user_id) → user record. But real applications have many access patterns:

• get by email — login flow looks up users by email.
• get by username — profile pages.
• get by phone — password reset.
• get by tenant_id — admin views all users in a tenant.
• get by created_at range — ‘users who signed up this week’.

Without secondary indexes, each of these requires a full table scan — which on a 100M-row table is unacceptable. The Index Table pattern solves this: for each access pattern, maintain a separate table that maps the secondary attribute to the primary key.

Example: a DynamoDB users table keyed by user_id. To support get by email:

users table:
  PK: user_id=42
  data: { email: 'alice@example.com', name: 'Alice', ... }

users_by_email index table:
  PK: email='alice@example.com'
  SK: user_id=42

To look up a user by email: query the index table by email → get user_id → query the users table by user_id. Two O(1) lookups instead of a full scan.

The index table can store just the primary key (a sparse index) or a denormalized copy of the full record (a covering index that avoids the second lookup). The trade-off is storage cost vs read latency.

Maintenance: every write to the primary table must also update every index table. If the user changes their email, you must delete the old email-index row and write the new one. This is where it gets tricky in NoSQL — there's usually no transaction across tables, so the updates are eventually consistent.

The hard problem with index tables is consistency. Most NoSQL stores don't support multi-table transactions, so updating the primary table and the index tables in one atomic operation is impossible. Options:

1. Best-effort dual writes — the application writes to the primary table, then to each index table. If any write fails, the index is out of sync. Recovery: a reconciliation job that scans and repairs. Simple but unreliable.

2. Change data capture (CDC) — write only to the primary table; a CDC stream (DynamoDB Streams, Kafka, Debezium) emits the change; consumers update the index tables. Eventually consistent, decoupled from the write path, more reliable.

3. Native secondary indexes — some stores (DynamoDB Global Secondary Indexes, Cassandra materialized views) provide built-in secondary indexes. You pay for them (storage, eventual consistency, update lag), but the store maintains them — no application logic. Often the right answer if your store supports it.

4. Synchronous in-transaction update — if the store supports multi-row transactions (e.g., DynamoDB transactions, FoundationDB), update the primary and index tables in one transaction. Strongly consistent, but slower writes and limited transaction throughput.

The consistency model matters for correctness: if a user updates their email and then immediately logs in with the new email, will the index find them? With eventual consistency (CDC, GSI), the answer is ‘eventually but not immediately.’ For login flows, this usually means falling back to a strongly consistent scan or accepting brief login failure after email change.

A common pattern: use the primary table as the source of truth for writes; use index tables for lookups; accept eventual consistency for most queries; fall back to the primary table for read-your-writes scenarios.

Design considerations for index tables:

• Which attributes to index? Index attributes that you actually query by. Each index costs storage and write amplification. Don't index attributes you never query.
• Sparse vs covering index? Sparse: just the secondary key → primary key mapping. Cheaper storage, but requires a second lookup to get the full record. Covering: denormalize the full record into the index. More storage, but one lookup serves the read.
• Sort key / clustering? For range queries (users created this week in a tenant), use a composite key: PK=tenant_id, SK=created_at. This allows range scans within a partition.
• Cardinality matters. Indexing a low-cardinality attribute (e.g., gender) gives huge partitions and limited benefit. Indexing a high-cardinality attribute (e.g., email) gives precise lookups.
• Hot keys. If one index key maps to many records (e.g., one tenant with millions of users), that partition becomes a hotspot. Consider sharding the sort key.
• TTL / cleanup. Index tables can accumulate stale rows. Use TTLs or scheduled cleanup to remove index rows whose primary record no longer exists.
• Rebuilding. If an index drifts (bug, failed writes), you need a way to rebuild it from scratch — typically a batch job that scans the primary table and writes the index.

The discipline is the same as for relational indexes: index for your actual query patterns, not for theoretical completeness. Every index has a cost; only pay it where it earns its keep.

Mental model. Think of Index Table as a deliberate boundary in a system. The boundary exists because something becomes harder to manage when everything is done in one place: latency, scale, failure isolation, consistency, cost, or team ownership. The useful question is not “what does Index Table mean?” but “what pressure makes this boundary worth introducing, and what new failure mode does it create?”

Numerical lens: write down traffic, payload size, read/write ratio, peak multiplier and durability target before choosing a component. The numbers should justify the architecture.

Answer this without notes: When would you choose Index Table, and when would you intentionally avoid it? Mention at least one bottleneck it addresses, one failure mode it introduces, and one alternative. Then quantify the workload you are designing for.

Numerical sanity check

A rough capacity check: required write throughput ≈ peak writes/s × average record size. At 5,000 writes/s and 2 KB average payloads, the raw incoming data stream is about 10 MB/s before indexes, replication and overhead.