Design Sharding & Replication for a Relational Database

Data, Storage & ConsistencyHarddesign-database-sharding

Design Sharding & Replication for a Relational Database

Design how a single Postgres primary that has run out of headroom becomes a sharded fleet — including the migration, the cross-shard queries, and the failover story.

Horizontal ShardingRead ReplicasFailoverOnline Resharding

Traffic & Capacity Estimates:

20 TB · 150k writes/second · 800k reads/second · zero-downtime resharding

Functional Requirements

•Route every query to the correct shard using a stable shard key, via a routing tier or a smart client.
•Serve reads from replicas while keeping writes on the primary of each shard.
•Promote a replica automatically when a primary fails, without losing acknowledged writes.
•Split and move a shard online while it continues serving traffic.

Non-Functional Requirements

•Write p99 under 20ms; read p99 under 10ms from replicas.
•Failover completes within 30 seconds with no split-brain.
•Resharding must be resumable and must never require a maintenance window.

Back-of-the-Envelope Math

20 TB across 16 shards = 1.25 TB per shard, each with 2 replicas = 48 database instances.
150k writes/s / 16 shards = ~9.4k writes/s per primary — comfortable for a tuned Postgres node.

Key Architectural Trade-offs

Shard key choice is the whole design: user_id keeps a user's data together and creates hot tenants; hash-of-id spreads evenly and makes every range query a scatter-gather.
Synchronous replication protects acknowledged writes and ties your write latency to the slowest replica; asynchronous is fast and loses the tail on failover.
Cross-shard joins and transactions: denormalize and duplicate, or accept a two-phase commit whose availability is the product of every participant's.

Click or drag a component onto the canvas, then connect the handles to draw the data flow.

3 nodes · 2 edges

Components · 35

Client & Edge4

Compute & Gateway7

Storage & Caching11

Messaging & Streaming6

Coordination & Ops5

Intelligence2

React Flow