Replication and sharding

Key points

• Replication copies the same data to more nodes. It buys read capacity and survives a node loss. It does not help write throughput.
• Leader-follower is the common shape: writes go to the leader, reads may go to followers, and failover promotes a follower.
• Replica lag is real. Someone who writes then reads from a follower sees old data - route their reads to the leader for a few seconds.
• Sharding splits different rows across nodes. It is the answer when one machine cannot hold the data or absorb the writes.
• The shard key decides everything. Every query must carry it, or you fan out to all shards and lose the benefit.
• Bad keys create hot shards: sharding by country puts half your traffic on one node. Cross-shard joins and transactions get expensive fast.

Example

-- Shard key: chat_id. Every message for one chat lives on one shard.
-- Good: routes to a single shard.
SELECT id, body, created_at
  FROM messages
 WHERE chat_id = 91823
   AND created_at < '2026-07-01'
 ORDER BY created_at DESC
 LIMIT 50;

-- Bad: no shard key, so the router asks all 32 shards and merges the results.
SELECT id FROM messages WHERE sender_id = 7 ORDER BY created_at DESC LIMIT 50;

-- Fix it with a second table keyed by the other access pattern.
CREATE TABLE messages_by_sender (
  sender_id  BIGINT NOT NULL,     -- shard key here
  created_at TIMESTAMPTZ NOT NULL,
  message_id BIGINT NOT NULL,
  chat_id    BIGINT NOT NULL,
  PRIMARY KEY (sender_id, created_at, message_id)
);

Replicate to survive and to read more; shard to write more. Pick the shard key before anything else.