IDs, Cross-Shard Queries and Transactions

Scenario

Example company: the sharded collaboration SaaS from the previous lesson. Each shard has its own SERIAL id sequence, and support staff need a global "search document by id" tool; finance needs revenue across all workspaces.

What you see in production

• Two documents on different shards both have id 1,284,551 — links and caches confuse them.
• The support tool queries all 32 databases sequentially (6 s per lookup).
• A feature that moves a document between workspaces (different shards) sometimes leaves it on both or neither shard after failures.

The naive code

CREATE TABLE documents (id bigserial PRIMARY KEY, workspace_id uuid NOT NULL, ...);   -- per-shard counter

def move_document(doc_id, from_ws, to_ws):
    doc = shard(from_ws).fetch(doc_id)
    shard(to_ws).insert(doc)           # commits on shard B
    shard(from_ws).delete(doc_id)      # may fail → document exists twice

Reproduce it

A Snowflake-style 64-bit ID: time, shard id and a per-millisecond sequence — unique across shards, roughly time-ordered, and it tells you which shard owns the row:

EPOCH_MS = 1_704_067_200_000            # custom epoch: 2024-01-01T00:00:00Z
SHARD_BITS, SEQ_BITS = 13, 10

def make_id(now_ms, shard, seq):
    return ((now_ms - EPOCH_MS) << (SHARD_BITS + SEQ_BITS)) | (shard << SEQ_BITS) | seq

def decode(id_):
    return {"ms_since_epoch": id_ >> (SHARD_BITS + SEQ_BITS),
            "shard": (id_ >> SEQ_BITS) & ((1 << SHARD_BITS) - 1),
            "seq": id_ & ((1 << SEQ_BITS) - 1)}

now = 1_791_590_400_000                 # 2026-10-10T00:00:00Z
a = make_id(now, shard=17, seq=0)
b = make_id(now, shard=4, seq=0)
c = make_id(now + 1, shard=4, seq=0)
print(a, decode(a))
print(b, decode(b))
print("unique across shards:", a != b, "| ordered by time:", b < c)

734197815705617408 {'ms_since_epoch': 87523200000, 'shard': 17, 'seq': 0}
734197815705604096 {'ms_since_epoch': 87523200000, 'shard': 4, 'seq': 0}
unique across shards: True | ordered by time: True

Root cause

Per-shard sequences are only unique within a shard. Lookups by id without the shard key have to ask every shard. And a transaction that spans two shards is a distributed transaction: without a coordinator, a failure between the two commits leaves them inconsistent.

The fix

• IDs: generate globally unique ids — Snowflake-style (time + shard/worker + sequence), UUIDv7 (time-ordered UUIDs), or prefix ids with the shard. Encoding the shard lets you route by id alone. Twitter's original Snowflake popularised the time + worker + sequence layout (twitter-archive/snowflake).
• Cross-shard reads: design them out (co-locate by shard key), or run scatter-gather in parallel with timeouts, or serve them from a separate analytics store fed by CDC.
• Cross-shard writes: keep transactions single-shard; for the rare multi-shard operation use a saga with idempotent steps and compensation (see Distributed Systems).

def move_document(doc_id, from_ws, to_ws):
    move_id = start_move(doc_id, from_ws, to_ws)                      # recorded on the source shard
    shard(to_ws).insert_if_absent(doc_id, payload, move_id=move_id)  # idempotent step 1
    shard(from_ws).mark_moved(doc_id, move_id=move_id)               # idempotent step 2 (tombstone)
    finish_move(move_id)
# a recovery job re-runs unfinished moves from the source shard's move log

-- Postgres 18 has uuidv7(); earlier versions: generate in the app (time-ordered keeps B-tree inserts local)
CREATE TABLE documents (id uuid PRIMARY KEY DEFAULT uuidv7(), workspace_id uuid NOT NULL, ...);

List<CompletableFuture<Long>> parts = shards.stream()
    .map(s -> CompletableFuture.supplyAsync(() -> s.queryForLong("SELECT count(*) FROM documents"), pool)
                               .orTimeout(2, TimeUnit.SECONDS))
    .toList();
long total = parts.stream().mapToLong(CompletableFuture::join).sum();

Trade-offs

• Snowflake ids need unique worker/shard numbers and roughly synchronized clocks (a clock moving backwards can create duplicates — refuse to issue ids until it catches up).
• Random UUIDv4 keys scatter inserts across the index (worse cache locality); prefer time-ordered ids.
• Scatter-gather is as slow as the slowest shard and multiplies load; keep it out of hot paths.
• Sagas give eventual, not atomic, consistency; users may briefly see intermediate states.

Detect it in production

• Duplicate-id checks when merging data from shards (e.g. in the analytics pipeline).
• Count and time fan-out queries; unfinished saga instances older than N minutes.

Key points

• Use globally unique, time-ordered ids — ideally ones that encode the shard.
• Avoid cross-shard queries and transactions by design; use parallel scatter-gather or analytics stores when needed.
• Use sagas with idempotent steps for rare multi-shard writes.