Secondary Indexes Across Shards

Harbor Point routes decisive reservation changes by allocation_bucket_id. That is deliberate: one shard owns a scarce bucket and can decide whether a hold, release, or confirmation is allowed. Then compliance asks a different question: “show every open reservation for issuer CA-MUNI, across all desks and allocation buckets.”

The obvious answer is “add an index on issuer_id.” That helps inside one allocation-bucket shard, but the router still does not know which bucket owners contain CA-MUNI. It must ask them all. The lookup has become many quick local searches plus one slow cluster-wide fan-out.

The stronger model is that an alternate-key lookup is a second distributed structure. A global secondary index has its own partition key, write path, lag, hotspots, and recovery story. It can make issuer queries targeted, but only when its relationship to the authoritative base row is designed explicitly.

Core Insight

The base reservation and its index entry answer different questions:

base row, partitioned by allocation_bucket_id
  “May this bucket confirm or release reservation R-88421?”

global index, partitioned by issuer_id and status
  “Which reservations might be open for CA-MUNI?”

The first path owns the business decision. The second is a derived access path. Treating the index as independent truth creates two authorities; treating it as a local schema ornament hides a second distributed write. A good design keeps the base row authoritative while stating exactly how current the index is allowed to be.

From a Local Index to a Routed Lookup

Suppose the base table has many logical allocation buckets. Each shard can maintain a local index on issuer_id, but an issuer query still looks like this:

router -> bucket shard A: open CA-MUNI rows?
router -> bucket shard B: open CA-MUNI rows?
router -> bucket shard C: open CA-MUNI rows?
... wait for the useful answers and merge them

This is called scatter-gather. It may be acceptable for an occasional internal report. It becomes costly when the query sits on a market-open request path: fan-out spends network work on every shard and exposes the query's tail latency to the slowest needed shard.

A global index changes the routing shape. Harbor Point materializes open_by_issuer with an index key based on (issuer_id, status) and an entry for each matching reservation.

index key:
  (issuer_id, status, reservation_id)

index payload:
  allocation_bucket_id, reservation_id, base_version,
  owner_epoch, desk_id, notional

index partition:
  hash(issuer_id, status)

Now the read begins at one index partition rather than every base partition:

GET /issuers/CA-MUNI/open-reservations
  -> index partition for (CA-MUNI, open)
  -> entries for matching reservation IDs
  -> optionally fetch or validate selected base rows

The optional second hop exposes a design choice. A pointer index stores only enough to find the base row. It is compact and cheap to update, but a result needs a base-row read. A covering index copies the fields the endpoint needs, such as desk_id and notional. It can answer in one hop, but every copied byte widens the index and every changed copied field becomes index work.

The Second Write Path

Consider reservation R-88421, created in allocation bucket 173 for issuer CA-MUNI. The base owner writes the authoritative row. The index must also gain an entry, perhaps on index partition I3.

before create
  base bucket 173: no R-88421
  index I3:         no (CA-MUNI, open, R-88421)

after create
  base bucket 173: R-88421, status=open, version=v41
  index I3:         (CA-MUNI, open, R-88421) -> bucket 173, v41, epoch 105

When the reservation becomes released, the base row changes first. The old index membership must disappear too:

base mutation: status open -> released, version v41 -> v42
index mutation: delete (CA-MUNI, open, R-88421) at or after v42

The update must be derived from the before and after images, not merely from an SQL verb. A change of issuer needs a delete from the old issuer partition plus an insert at the new one. A change of status can remove or add index membership. A bucket move needs the index pointer's owner epoch updated or safely re-resolved.

derive_index_mutations(before, after):
  remove the prior entry if before matched the index predicate
  add the new entry if after matches the index predicate
  carry reservation ID, base version, and routing epoch

Stable identity makes retries safe. The logical key (issuer_id, status, reservation_id) should not gain another entry when a worker replays the same message. A version-aware put may overwrite the same logical entry; a delete must be idempotent. Otherwise, ordinary at-least-once delivery produces duplicate or orphaned search results.

Choose the Alignment Contract

The hard design choice is when the index transition becomes visible relative to the base commit.

Synchronous alignment

A distributed transaction or equivalent coordinated protocol commits the base row and its index entry together. Then a query with the relevant consistency level can make the stronger statement: a committed base change is reflected by the index according to the transaction's isolation semantics.

This is a good fit when the issuer lookup participates directly in a safety decision or a uniqueness rule. The trade-off is higher write latency, more cross-shard coordination, and a larger failure surface. It is not free merely because the index looks like a familiar database feature.

Asynchronous derivation

The base owner commits first, then an ordered log or change-data-capture consumer applies the index mutation. This often isolates the index pipeline and keeps the decisive reservation write fast. It also creates a window:

10:00:00.010  base bucket 173 releases R-88421 at v42
10:00:00.012  base commit is acknowledged
10:00:00.040  index consumer applies delete on I3

Between those events, the global index can still list a released reservation. In the other direction, it can temporarily omit a newly opened one. That is not corruption if the contract says “eventually updated index”; it is corruption if the API claims a current, complete compliance answer.

For an asynchronous index, publish the evidence of its limit: an index watermark, the source-log position processed, or a freshness age. A session-sensitive endpoint can wait for the index to reach a commit token, read the authoritative base path, or return a clear bounded-staleness result. Lesson 010's token rule applies to the index too; a fast derived read does not escape the need for proof.

Hotspots, Moves, and Repair

The index has a new partition key, so it creates a new distribution problem. Base buckets may be balanced while one issuer dominates both queries and updates. If CA-MUNI is unusually active, hashing only on issuer_id can make one index partition hot.

Possible responses depend on the query contract:

• Split a hot issuer's index by a bounded dimension such as day or a small hash suffix, then query those few buckets.
• Keep a range key that supports the intended time or status scan.
• Refuse to use a global index for a pathological access pattern and supply an asynchronous report instead.

The trade-off is again explicit: bucketing spreads write and read load, but reintroduces bounded fan-out. It is usually better to query four known index buckets than every base shard, but the number must be capped and measured.

Row movement is another boundary. After bucket 173 migrates from shard B to shard F, a pointer that says “read B” can be obsolete. Store an owner epoch or route version in the index entry. On a mismatch, resolve the current base owner through the partition map instead of treating the old pointer as authority. The index helps locate data; it must not override the current ownership record.

Finally, plan repair before the first outage. The base table is the source of truth, so a reconciliation job can scan a base-key range, derive the expected index entries, and compare or rebuild the index range. This detects missing entries, stale membership, old pointers, and retry defects. A rebuildable index is valuable, but rebuilding can be expensive; recovery objectives should name whether an endpoint can wait for it.

Check Your Understanding

Check: Harbor Point adds an issuer_id index to every allocation-bucket shard. Does GET /issuers/CA-MUNI/open-reservations become a one-shard lookup?

Answer: No. The router still does not know which allocation buckets contain matching rows. It must fan out unless another directory or global index maps the issuer query to a bounded candidate set.

Check: An asynchronous index lists R-88421 as open, but the base row says released at a later version. Which source decides the reservation's authoritative status?

Answer: The base row on its current owner. The index entry is stale derived state. The service must either validate the base row, wait for index catch-up, or disclose the stale-index contract.

Practice: Defend the Lookup Contract

Compliance wants interactive issuer searches, but a reservation approval must not depend on a stale alternate-key result. Propose one index design for the search endpoint. State whether it is pointer or covering, synchronous or asynchronous, what it may return while behind, and how a bucket move is handled.

A strong answer may choose an asynchronous pointer index for interactive search: it routes to the issuer partition, includes an index watermark, and validates selected sensitive rows against the current base owner. It should state that approval reads stay on the authoritative bucket path, and that an owner_epoch mismatch triggers a fresh partition-map lookup. Another answer can choose synchronous maintenance only if it explains why the lookup's invariant justifies the extra coordination.

Connections

• Rebalancing Partitions Under Live Traffic explains why index pointers require owner epochs and fresh routing after a base-row move.
• Clocks, Leases, and Safe Reads asks the next version of the same question: what evidence earns a fast read from a changing authority?

Resources

• [DOC] Amazon DynamoDB: Global Secondary Indexes — Focus: Compare alternate keys, projections, asynchronous propagation, capacity effects, and index-key changes.
• [DOC] Google Cloud Spanner: Secondary Indexes — Focus: Contrast an index maintained inside a distributed transaction with an asynchronously derived access path.
• [DOC] Vitess: Lookup Vindexes — Focus: Inspect an alternate-key-to-shard mapping as an explicit distributed lookup structure.

Key Takeaways

• A global secondary index is a separately partitioned access path, not a local index copied across shards.
• Each relevant base-row transition produces index membership work, so retries, versions, and deletes must be designed as carefully as inserts.
• Synchronous alignment buys stronger index semantics; asynchronous derivation buys a faster write path but requires an explicit freshness contract.
• Index hotspots, stale pointers, and divergence need their own routing, metrics, reconciliation, and recovery plan.