Search Knowledge

© 2026 LIBREUNI PROJECT

Database Systems/Data at scale and synthesis
Page 17 of 20

Replication and Partitioning

Design replica, partition, failover, and migration boundaries from workload and failure evidence.

Lesson briefData at scale and synthesis · 7 h · 4 outcomes

Required knowledge

  • Indexes, query plans, transactions, recovery, and distributed-data consistency contracts
  • Hashing, range order, workload skew, and basic availability terminology
  • Tenant ownership and relational key design

Learning outcomes

  • Compare synchronous and asynchronous replication by acknowledged-write durability, read freshness, and failover loss risk.
  • Explain why failover needs a fencing/epoch rule rather than merely a new primary process.
  • Choose a partition key from query, maintenance, skew, and cross-partition-constraint evidence.
  • Trace a safe tenant or range migration with dual-write/redirect or cutover boundaries and reconciliation evidence.

Assessment target

Given a multi-tenant workload and a failover/migration incident, select replication and partition boundaries, identify a split-brain or pruning counterexample, and specify the evidence required before cutover.

Scope boundary

  • Consensus implementation, automatic rebalancer algorithms, global secondary-index internals, and cloud-service administration are outside scope.
  • Partition pruning is demonstrated as a query-planning property, not as a substitute for distributed sharding design.

A replica that is one minute behind can be an excellent reporting target and a dangerous authorization target. A partitioned table can make retention cheap and still leave a tenant-hotspot unsolved. Replication and partitioning change where data lives and when it becomes visible; neither silently preserves every single-node guarantee.

Replication changes the meaning of acknowledgement

ModeClient acknowledgementRead behaviorPrimary risk
asynchronouslocal durable primary commitreplicas can lagacknowledged tail can be lost on failover
synchronous to one or more standbysrequired standby acknowledgement as configuredsome reads may still be stalehigher latency / reduced write availability
logical/event projectionsource commit before consumer applies eventexplicit lag and replay boundaryduplicate or delayed consumer work

A replica should report both replication position and lag relative to a named source. “Healthy” cannot mean only that its process is running. A read-after-write API must either route to a sufficiently caught-up replica, route to the writer, wait with a bound, or return a clearly stale contract.

Prediction — a false zero-loss claim

A primary acknowledges a write locally, then crashes before its asynchronous replica receives it. The replica is promoted. What happened to the client-observed write?

Worked solutionExpand

It can be lost despite the former primary’s acknowledgement. The deployment promised local durable commit, not durable replication to the promoted node. Calling this “zero data loss” confuses storage durability with a failover durability contract. Synchronous acknowledgement, durable client retry records, or a revised product guarantee changes the claim and its latency/availability cost.

Promotion needs fencing against stale writers

Code
participant "client" as C
participant "old primary, epoch 17" as O
participant "new primary, epoch 18" as N
participant "routing control" as R
R -> N : promote with epoch 18
R -> C : route writes to epoch 18
C -> O : delayed write with epoch 17
O -> O : reject / fenced
C -> N : retry with epoch 18
note right of N
Promotion without fencing can
permit two write authorities.
end note
clientold primary. epoch 17new primary. epoch 18routing controlclientclientold primary, epoch 17old primary, epoch 17new primary, epoch 18new primary, epoch 18routing controlrouting controlpromote with epoch 18route writes to epoch 18delayed write withepoch 17reject / fencedretry with epoch 18Promotion without fencing canpermit two write authorities.

Failover is not finished when a process starts accepting traffic. Fencing prevents an isolated former primary from continuing to accept writes that later conflict with the new authority. The precise technique may be leases, terms, a strongly consistent configuration store, or a database-specific mechanism; the required property is refusal of stale authority.

Application — read your own write

A client creates an order, then reads it from an asynchronous replica. Give two correct API behaviors.

Worked solutionExpand

Route the immediate read to the primary/source of truth, or attach a required log position/version and wait until the chosen replica confirms it has replayed that position within a timeout. Returning “not found” from a lagging replica as if the order never existed violates read-your-writes. The API can instead return an explicitly stale result if the product contract permits it.

Why promotion is not enough

0 / 4 answered

A standby replica is promoted to primary after network failure and accepts writes. The former primary remains online and receives delayed requests. Evaluate which mechanisms prevent split-brain data corruption:

Classify each option as correct or incorrect, or leave uncertain options unanswered (“I don't know”) and verify at any time.

1
The promoted replica storage volume absorbs delayed client writes naturally without requiring routing protocol changes.
2
Configuring read-your-writes client session guarantees revokes the old primary authority to commit concurrent writes.
3
Lease expiration guarantees that an isolated former primary cannot commit writes once its allocated time lease elapses.
4
Fencing tokens or epoch terms reject writes from the demoted primary by checking monotonically increasing leadership numbers.

A partition key is a workload and operations decision

Worked example. Time partitions make dropping expired events cheap and can prune a query with a time predicate. They do not make a tenant-only query prune partitions, and they can concentrate today’s write hotspot in one partition.

Candidate keyServesBreaks or complicatesEvidence to collect
time rangeretention, time-bounded scanscurrent-write hotspot; tenant-only querydate filters, retention cadence, ingest skew
tenant hashtenant isolation and balanced ownershipcross-tenant reporting; tenant movestenant-size distribution, joins, reporting scope
geographyresidency / latency boundaryglobal users and moving customerslegal domain, traffic origin, disaster plan
product/categorylocalized catalog accessuneven popularity and changing classificationhot-key distribution, reclassification rate

A partition is not automatically a shard. Local table partitions can remain on one database node; a shard adds independent placement, failure, and coordination boundaries. Global uniqueness and foreign keys become harder when the key omits the partition owner. Put tenant identity into keys where tenant isolation is a requirement, rather than assuming an application filter is always present.

Code
rectangle "orders_2026_08\nrange: August" as A
rectangle "orders_2026_09\nrange: September" as B
rectangle "orders_2026_10\nrange: October" as C
rectangle "query: created_at in September" as Q
Q --> B : prune to one partition
note bottom of A
A query only on tenant_id
cannot infer this range.
end note
orders_2026_08range: Augustorders_2026_09range: Septemberorders_2026_10range: Octoberquery: created_at in SeptemberA query only on tenant_idcannot infer this range.prune to one partition

Prediction — pruning counterexample

A table is partitioned monthly. A query filters only tenant_id = 42 and lacks a time predicate. How many partitions can the planner discard from partition bounds alone?

Worked solutionExpand

None merely from month bounds: tenant identity gives no constraint on the partition key. An index can still help each partition, but the query may need to inspect all of them. Add an appropriate time predicate, choose a tenant-aligned partition/shard design when the workload warrants it, or maintain a separate routing structure with a documented correctness boundary.

Migration is a state transition, not a copy command

PhaseRequired evidence
preparedestination schema/version accepts the tenant; ownership routing is versioned
copysnapshot position and checksum/count evidence are recorded
catch upordered changes after the snapshot replay idempotently
cut overwrites are fenced or redirected at one durable ownership epoch
verifykey counts, sampled hashes, invariants, and lag converge
retireold copy is retained until rollback window and audit evidence expire

Transfer — choose the cutover test

Tenant 42 is moving to a new shard while orders continue to arrive. What single test is insufficient, and what must replace it?

HintExpand

A matching row count cannot prove every key/version and in-flight write moved exactly once.

Worked solutionExpand

A final row count alone is insufficient: it misses mismatched values, duplicate outbox events, and writes after the snapshot. Record a migration epoch, fence or route writes through that epoch, replay changes after a known position idempotently, then verify key sets, checksums or sampled hashes, and domain invariants before retirement. Keep a rollback decision rule rather than deleting the old copy on optimism.

Section Exercises

Deep Practice

Solve these problems to test and consolidate your understanding. Complete worked solutions and diagnostic hints are available for each problem.

Exercise 5.2.1: Split-Brain Prevention and Epoch-Based Leader Fencing

Verificationapplication

In a high-availability primary-replica setup, a network partition isolates the active primary from the cluster monitor. The monitor promotes a standby to primary at epoch 2. Verify how a client request carrying epoch 1 sent to the old primary is safely rejected to prevent split-brain writes.

HintExpand

Examine monotonically increasing epoch tokens and lease validation.

Worked solutionExpand

Each mutation request carries the coordinator’s current leadership epoch. If a request with epoch 1 arrives at the old primary, it either encounters an expired time-bounded leader lease or, upon attempting to append to persistent storage / consensus log, is rejected by storage fencing mechanisms that demand epoch ≥2\ge 2 Because the cluster consensus quorum has already advanced to epoch 2, the stale primary cannot commit the transaction.

Exercise 5.2.2: Read-Your-Writes Consistency under Asynchronous Replication Lag

Descriptionrecall

Describe why a user who creates an account and is immediately redirected to a read replica may receive an HTTP 404 (Not Found) error under asynchronous replication, and propose two routing architectures that guarantee read-your-writes consistency.

HintExpand

Asynchronous replication decouples primary commit from replica log replay.

Worked solutionExpand

Under asynchronous replication, the primary confirms commits to the client before the WAL stream has been replayed on standby read replicas. If the client’s subsequent read query routes to a replica lagging behind the commit LSN, the new account tuple is not yet visible. Architectural solutions:

  1. Primary read routing: Route read requests immediately following user mutations to the primary database for a bounded duration (e.g. 5 seconds).
  2. Session-level LSN wait: Record the primary’s commit LSN in the client’s session cookie; the read router forwards queries only to replicas that have confirmed replay past that recorded LSN.

Exercise 5.2.3: Workload-Driven Partition Key Selection: Time Range vs. Tenant Hash

Constructionapplication

A SaaS platform with 50,000 tenants processes 50,000,000 order records per month. Contrast the operational trade-offs of range partitioning by created_at (monthly) versus hash partitioning by tenant_id (32 partitions) across query pruning and data retention workflows.

HintExpand

Analyze WHERE clause pruning and DROP TABLE efficiency.

Worked solutionExpand
  1. Range by created_at: Ideal for data lifecycle retention; dropping an expired month is an O(1)O(1) metadata operation (DROP TABLE orders_2025_01) with zero vacuum overhead. However, queries filtering only by tenant_id must scan all partitions, and all current-day writes concentrate into a single active partition.
  2. Hash by tenant_id: Perfect for tenant isolation and query pruning on tenant-specific lookups; writes distribute evenly across all 32 partitions. However, dropping expired historical data requires expensive row-level DELETE scans across all 32 partitions.

Exercise 5.2.4: Zero-Downtime Live Shard Migration and Cutover Protocol

Generalisationtransfer

Design a zero-downtime shard migration protocol to transfer a high-throughput tenant from an overloaded database shard AA to a newly provisioned shard BB Detail the six sequential phases and evidence required before retiring shard AA

HintExpand

Follow prepare, copy, catch up, cut over, verify, and retire phases with epoch fencing.

Worked solutionExpand
  1. Prepare: Verify destination shard BB schema matches AA and initialize tenant routing configuration with epoch 1.
  2. Initial copy: Take a consistent point-in-time snapshot of tenant rows on shard AA at LSN L0L_0 and copy data to shard BB
  3. Catch up: Enable CDC (change data capture) or logical replication to stream and apply all mutation records from L0L_0 forward onto shard BB
  4. Cutover: Set tenant routing to read-only or brief pause; advance routing epoch to 2 pointing to shard BB Drain in-flight writes on AA
  5. Verify: Execute reconciliation queries comparing row counts, cryptographic column checksums, and key sets between AA and BB
  6. Retire: Route all traffic to shard BB under epoch 2. Retain frozen tenant data on shard AA in read-only mode for 7 days before issuing drop.

References & Further Reading