The consistency boundary, local and global indexes
Where may an index disagree with its table, and should a distributed index live with the rows or with the key?
The consistency boundary
An index is derived state. When a row changes, the index must change too, and for a moment the two disagree. The question is where you allow that moment to be visible.
| Placement | How | Who pays | Visible symptom |
|---|---|---|---|
| Synchronous maintenance | the index update is part of the same transaction as the row update | the writer pays latency (and locks) | slower writes; none stale |
| Asynchronous maintenance | the index is updated later, through a log or replication | the reader pays with stale answers | "I created it and cannot find it"; replication lag |
It is worth its own name because it is what makes secondary indexes in distributed systems hard rather than merely tedious. You can move the boundary (inside the transaction, or into replication lag). You cannot make it vanish.
The same idea at smaller scale. Search engines refresh their inverted index periodically ("near-real-time" refresh): a new document is searchable after a delay, because new data is written as a small immutable segment that must first be opened. Vector indexes tombstone deleted vectors and filter them out until the next rebuild. In both cases the delay or the filter is the consistency boundary.
Local versus global secondary indexes
In a partitioned system the biggest indexing decision is where the index lives relative to the table's partitions.
- Local index (index per partition). Each partition indexes only its own rows. Writes are cheap: one row changes in one partition, so one index changes, in the same place. Reads on the indexed column must be scatter-gather: send the query to every partition and merge the answers.
- Global index (partitioned by the index key). The index is itself partitioned by the indexed column, so all entries for one email live in one place. A read on that column goes to one partition. A write now touches two places (the row's partition and the index entry's partition), which needs either a distributed transaction (slow, complex) or eventual consistency (stale reads).
| Local | Global | |
|---|---|---|
| Write cost | cheap (one partition) | cross-partition |
| Read by indexed column | scatter-gather to all partitions | single partition |
| Consistency | consistent with the row | distributed transaction or eventually consistent |
| Tail latency on reads | grows with the number of partitions | independent of partition count |
Real systems show the same fork under different names. DynamoDB's global secondary indexes are global and asynchronous. Cassandra's built-in secondary indexes are local only. Elasticsearch's routing option decides whether a query can be limited to one shard or must visit all.