Database Performance Tuning and Scaling Questions

Assumptions: 100TB total, critical shards require RTO <2min.

Client routing & versioning:

• Use versioned routing keys and feature-flagged client SDKs aware of routing table with TTL. Clients consult a routing service cache (with short TTL) to find shard locations.

Background data copy:

• Start live copy from source to target shard using parallel workers reading sstable/streaming replication (CDC) ensuring consistent snapshot at start.
• Track per-key high-watermark LSN/sequence and stream deltas through CDC until cutover.

Write handling during migration:

• Two-phase writes: writes go to source and are mirrored to target (dual-write) or written to source + durable change log that target consumes. Ensure idempotent, ordered application on target.

Cutover steps:

1. Ensure target fully caught up (delta queue empty), verify checksums on sample ranges.
2. Quiesce brief window: redirect writes by toggling routing version; allow up to 2-minute maintenance window if needed.
3. Switch client routing atomically (update routing service + invalidate caches); monitor metrics.
4. Serve reads from target; keep source read-only for fallback.

Validation & rollback:

• Validation: compare sampled key checksums, counters, and success rate; run application smoke tests.
• Rollback: if errors, revert routing to source (cached) within TTL and replay missing deltas.

Corner cases:

• Hot keys: migrate with shadow traffic or sticky routing for hot users.
• Partial failure: perform per-shard progressive cutover, not all at once for RTO.

Operational steps outline:

• Preflight: capacity checks, dry-run on subset.
• Copy: parallel streaming + continuous CDC.
• Validation: checksums, QA tests.
• Cutover: flip routing, monitor.
• Post: decommission or re-balance.

This process minimizes movement, supports strict RTO by using staged per-shard cutovers, dual-write or CDC mirroring, and clear rollback paths.

Requirements:

• Primary-per-shard cluster across 3 DCs; robust leader election, avoid split-brain, handle DC loss/partition.

Approach (Raft preferred):

• Run a Raft group per shard with one replica in each DC when possible.
• Configure quorum: majority quorum (e.g., 3 nodes => quorum=2). For 3 DCs with replicas per DC, ensure total node count odd (3 or 5) and quorum remains majority.

Avoiding split-brain:

• Use strict majority for leader election. Do not allow minority partitions to elect leaders.
• Use leader leases (short lease < election timeout) so leader assumes authority for lease duration; followers refuse to vote if they believe lease still valid.

Handling DC loss / partition:

• DC loss: if surviving nodes form a majority, continue serving; otherwise read-only or degraded mode until majority restored.
• Network partition: partitions lacking majority must step down and reject writes. Optionally expose read-only replicas.

Leader leases and clock skew:

• Leases require bounded clock skew; prefer logical timeouts in Raft (no reliance on synchronized clocks). If using leases, synchronize clocks with NTP and set conservative lease durations > maximum clock skew + network RTT.

Safety steps:

• Election timeouts randomized and larger than typical RTT to reduce split campaigns.
• Snapshotting and log compaction to bound log size; ensure leader transfers to preferred DC gracefully.

Trade-offs:

• Raft simplifies implementation and availability reasoning; Paxos is more flexible but complex.
• For cross-DC latency, tune heartbeat/election timeout to avoid undue flapping.

Operational:

• Monitor quorum membership, election frequency, and leader tenure; automate alerts when majority shifts.

This design ensures single-leader safety under majority rule, predictable behavior during DC loss, and minimized split-brain risk with leases and conservative timeouts.

Choice: Python implementation.

Approach:

• Represent ring as sorted list of (hash, vnode_id, node).
• Given target node list, compute desired vnode distribution (assign each vnode hash to nearest node clockwise).
• Compute mapping changes by comparing current owner vs desired owner and produce list of ranges to move.

Implementation (unit-testable):

from bisect import bisect_right

# ring: list of (hash, node). target_nodes: list of node ids
# returns list of (start_hash, end_hash, from_node, to_node)
def rebalance_ring(current_ring, vnode_hashes, target_nodes):
    """Compute minimal movement mapping without data transfer."""
    # desired owner for each vnode hash: hash % len(target_nodes) -> deterministic
    desired = {}
    for h in vnode_hashes:
        owner = target_nodes[h % len(target_nodes)]
        desired[h] = owner

    moves = []
    for i, (h, cur_node) in enumerate(current_ring):
        to_node = desired.get(h)
        if to_node and to_node != cur_node:
            # compute range from h to next vnode hash
            next_h = current_ring[(i+1) % len(current_ring)][0]
            moves.append((h, next_h, cur_node, to_node))
    return moves

Key points:

• Use deterministic vnode-to-node mapping to minimize movement.
• Tests: small ring, add/remove node, assert total moved vnode count minimal.

Complexity: O(V + R) where V=vnode_hashes, R=current_ring length.

This produces a clear mapping plan for minimal key movement; actual data transfer follows these ranges.

**Describe backup types

Full/snapshot backups:** capture entire dataset at a moment. Fastest to restore, large storage and longer backup windows. Good for nightly snapshots.

Incremental backups: store changes since last full or incremental. Smaller storage and quicker backups; restores require applying chain (longer restore time).

WAL-based PITR: continuous logging of write-ahead logs to allow restoring to any timestamp. Enables point-in-time recovery and fine-grained RPO.

Recommendation for RPO=1h, RTO=15m

• Take hourly full or snapshot backups (or nightly full + hourly snapshots stored as fast cloud snapshots).
• Stream WAL to durable object store (S3) continuously for PITR (ensures <1h RPO).
• Maintain hot standby replica(s) using asynchronous replication for reads and rapid failover; use automated failover (Patroni/replica promotion) to meet 15m RTO.

Why: WAL streaming + hourly snapshots gives granular recovery points and small backup window. Hot standby enables near-instant failover and meets tight RTO; snapshots + WAL cover data safety against corruption.

Patterns for atomic multi-shard behavior: 2PC, distributed locks, sagas

Two-Phase Commit (2PC):

• How: coordinator prepares each shard; if all prepared, commit; else abort.
• Latency: high (round trips to all participants twice).
• Complexity: moderate; requires durable prepare logs, coordinator reliability.
• Failure modes: coordinator crash can block participants (use 3PC or timeouts); performance bottleneck.
• When to pick: when strict ACID across shards required (financial ledger updates).

Distributed Locks:

• How: acquire locks on involved shard keys (e.g., via Redis/etcd), perform operations, release.
• Latency: lower than 2PC if operations local, but locking order must prevent deadlocks.
• Complexity: moderate; need lease/fencing to prevent stale lock holders.
• Failure modes: stuck locks if client dies; risk of single lock service becoming bottleneck.
• When to pick: short critical sections where global serialization suffices and conflict surface is small.

Sagas (compensating transactions):

• How: break transaction into steps across shards; if step fails, run compensating actions to undo prior steps.
• Latency: higher overall because of asynchronous steps but each step is local and can be parallelized; eventual consistency.
• Complexity: high design-wise (compensations, idempotency, ordering) but operationally scalable.
• Failure modes: incomplete compensation, complex error handling, eventual consistency windows.
• When to pick: order processing where eventual consistency acceptable (reserve inventory, charge payment, confirm order), and business rules allow compensations.

Recommendation for order processing: prefer saga-based workflow with careful orchestrator for user-facing flows (reserve inventory first, authorize payment, create order); use idempotent operations, outbox pattern for reliable messaging, and compensations (refund, restock). Use 2PC only for strict monetary transfers where atomicity cannot be compromised; use distributed locks for localized coordination and short-lived critical sections.