Local and Global Secondary Indexes Under Partitioning

A primary key is a routing decision, so it is chosen for how it distributes data. Every other attribute is a query target without a routing decision, and an index over one has to invent one. The invention is not neutral: it decides whether the write path pays for keeping the index consistent or the read path pays for searching it. The two standard answers are known as local and global indexes, and the names describe where the index entries live relative to the rows they point at.

Local indexes: partitioned with the data

A local index stores, inside each partition, entries for the rows that partition holds. The index is a private structure of the partition, rebuilt when the partition is rebuilt, and moved when the partition is moved. There is no cross-partition coordination.

A write that inserts a row updates the row and its index entries in the same partition, in the same local transaction or the same atomic batch. The write cost is one partition’s worth of work plus the index write amplification, which for a single secondary attribute is roughly double. The consistency between row and index is local, so it is achievable with the storage engine’s own atomicity rather than with a distributed protocol.

A read by the indexed attribute has no idea which partitions hold matching rows, because the index is not organized by the attribute across the cluster. It must ask every partition. Each partition searches its local index, returns matching keys, and the coordinator merges.

-- local index: the query cannot be routed, only broadcast
SELECT id, title FROM articles WHERE author = 'x' AND published > '2025-01-01';
-- executed as: for each partition p in 1..P: SELECT ... WHERE ... (local index on author)

The read cost is the fan-out problem. A query that matches 20 rows across a million-row table still touches every partition, and the coordinator must merge P result sets to produce 20 rows. The number of rows scanned is small because each partition uses its index; the number of round trips is P, and the latency is the maximum over P, as described in Scatter-Gather Fan-Out and Tail Latency Multiplication.

The advantage of local indexes is that they never require a cross-partition write, so they never fail independently of the row write. A partition that is unavailable makes its own rows and its own index entries unavailable together, which is a consistent failure. There is no window in which the index says a row exists in a partition that cannot serve it.

This is why local indexes are the default in stores that prioritize write availability and partition independence. The cost is paid at query time, and it is paid by every query on a non-key attribute.

Global indexes: partitioned by the indexed value

A global index is partitioned by the indexed attribute rather than by the row’s primary key. An entry for (author, id) lives in the index partition that owns author, regardless of which data partition owns id. The index partition is determined by hashing or ranging the indexed value, exactly as a primary key would be. When it is ranged, the index inherits the split behaviour of Range Partition Boundaries and the Split Key Contract, including the need to keep its own boundary list consistent with the data’s.

A read by the indexed attribute now has a routing decision. The query computes the index partition for the value, asks that one partition, receives the primary keys of matching rows, and then fetches those rows from their data partitions. The index lookup is a single-partition operation; the fetch is a fan-out over the number of distinct data partitions the matching rows happen to occupy.

A write is where the cost moves. Inserting a row requires writing the row to its data partition and writing an entry to the index partition for the attribute value. Those are two different partitions, possibly on different machines, and they cannot be updated in one local transaction. The write becomes a distributed write with two failure modes: the row is written and the index entry is not, or the index entry is written and the row is not.

def write_row(row, put_data, put_index, repair):
    put_data(row)                        # durable in the partition that owns the key
    try:
        put_index(row.author, row.id)    # a second partition, a second write
    except Transient:
        repair(row)                      # row is durable, its index entry is pending
        raise

The usual resolution is to make the index eventually consistent and to repair it. The write path either (a) writes the row first and the index second, accepting a window where the row is unindexed, or (b) writes both with a retry queue that guarantees the index entry eventually appears. Both accept that a reader may see a stale index, and both need a repair process to reconcile. The alternative, a distributed transaction across the two partitions, is available in systems that have one, and it converts the availability problem into a latency and coordination problem.

The index must also be maintained on delete and on update of the indexed attribute. An update that changes author from A to B is a delete from A’s index partition and an insert into B’s, plus the row update: three writes across up to three partitions. An update that does not touch the indexed attribute touches only the row, which the index maintenance path must know; otherwise every update rewrites index entries for no reason.

The comparison as an axis

The two designs are mirror images, and the axis is write amplification versus read fan-out.

Property	Local index	Global index
Write partitions touched	1	2 or more
Write atomicity	local	cross-partition
Read by indexed value	all partitions	1 index partition
Read latency	maximum over P	1 hop, then fetch fan-out
Index availability	tied to data partition	independent
Rebuild cost	per partition	whole index
Affected by rebalancing	moves with partition	re-partitioned independently

The last row is the one that bites in practice. A local index moves with its partition: when the partition migrates, the index goes with it, and the migration protocol handles both because they are one unit. A global index has its own partitioning, and a change to the data partitioning does not move index entries, but a change to the index partitioning does. Two independent partitionings means two independent rebalancing problems, and the index rebalance must preserve the mapping to rows that may be moving at the same time.

There is also a storage-cost asymmetry. A local index duplicates the indexed attribute in every partition’s index, which is the same total bytes as a global index. But a global index must carry the primary key of each entry so the row can be fetched, and if the primary key is large the index grows. A local index can store a row identifier local to the partition, which is typically smaller. For wide primary keys this difference is measurable in the index’s total size.

Consistency windows and what readers observe

The consistency a reader gets from a global index is a function of how the write path handles the two partitions. Three designs are in use.

Synchronous dual write. The write is acknowledged only after both the row and the index entry are durable. A reader that sees the row sees the index entry. The cost is that a failure of either partition fails the write, so the write’s availability is the product of two partitions’ availabilities. With each at 99.9 per cent, the product is 99.8 per cent, which is a doubling of unavailability for a single-attribute index. Adding a second attribute triples the exposure.

Asynchronous index update. The row write is acknowledged, and the index entry is applied by a background process. The reader may see a row that the index does not yet contain. The window is bounded by the background lag, which under normal operation is small and under a backlog is unbounded. A query that must see its own writes cannot use the index; it must fall back to a scan or to a read of the primary key.

Read repair on the fetch path. The index is treated as a hint. A query reads the index, fetches the rows, and verifies that each row still matches the predicate. Rows that no longer match are filtered out, and rows that the index missed are simply absent. This makes the index safe against false positives (stale entries pointing at deleted or updated rows) but not against false negatives (rows missing from the index). Since a stale index entry is far more common than a missing one, since an update that changes the attribute leaves the old entry until it is cleaned, filtering handles the common case.

The asymmetry between false positives and false negatives is the key design fact. A global index that is allowed to lag produces false negatives, which silently drop results. A global index that is allowed to accumulate stale entries produces false positives, which are filtered by a recheck. The second is much easier to live with, which is why index maintenance usually leans toward “write the new entry promptly, delete the old entry lazily”.

Composite and covering indexes

A secondary index over a single attribute answers one shape of query. Most real queries constrain several attributes, and the index design must decide which combinations to materialize.

A composite index over (a, b) is ordered by a then b, so it serves queries that constrain a and optionally b, but not queries that constrain only b. That is the prefix rule of B-tree indexes, and it applies unchanged in a partitioned setting. The partitioning decision is which attribute leads: leading with a means the index is partitioned by a, so a query on b alone must fan out.

A covering index includes the columns a query needs, so the query is answered from the index without fetching rows. That removes the second hop of a global index, which is often the dominant cost. The price is index size, since the covered columns are duplicated, and the price is write amplification, since every update to a covered column rewrites the index entry.

The covering case is where the global index’s advantage is largest. A query answered entirely from one index partition is a single-partition request with no fan-out, which has the single-node latency distribution rather than the maximum-over-P distribution. That is a qualitative improvement, not a marginal one, and it is the reason covering indexes are worth their storage cost when the query pattern is known.

Choosing, and the cost of getting it wrong

The choice follows from the write-to-read ratio and the tolerance for stale results.

A write-heavy table with occasional analytical queries should use local indexes: writes stay single-partition, and the occasional query pays the fan-out. The fan-out cost is proportional to the partition count, so the table’s partition count becomes a query-latency parameter, not just a storage parameter.

A read-heavy table with a known query pattern should use global indexes, ideally covering, so that queries resolve to one partition. The write path then carries the cross-partition cost, and the write availability is reduced by the number of indexed attributes.

A table that must support ad-hoc queries on arbitrary attributes cannot index all of them globally; the write amplification would be the product of the attribute count. It uses local indexes and accepts the fan-out, or it maintains a separate analytical copy with a different partitioning, which is a data-duplication strategy rather than an indexing one.

The failure to avoid is an index whose partitioning is chosen by accident. A global index partitioned by the same hash as the data is a local index with extra steps: the entries for a given attribute value land wherever the hash sends them, so a query on that value still fans out, and the write still touches a second partition. That combination pays both costs and buys neither benefit.