Distributed Databases & the CAP Theorem
Distributed database ও CAP theorem
1. Why Go Distributed?
One server has hard limits. A single PostgreSQL box on top-tier hardware peaks somewhere around ~50,000 transactions per second and a few terabytes of working data. The moment a Bangladeshi fintech grows past Dhaka — adding Chattogram, Sylhet and Khulna with low-latency requirements, or surviving a fibre cut in one data centre — a single machine stops being enough. We must replicate data to many machines for durability and read scaling, and partition it across many machines for write scaling and storage capacity. The minute we do, the rules change. This module is the rule-book.
2. Replication Strategies
Replication means keeping the same data on multiple nodes. There are three common shapes.
2.1 Leader–Follower (Primary–Replica)
One node — the leader — accepts all writes. It streams a replication log to one or more followers, who apply the same writes in order. Reads can hit either, but if you read from a follower you may see slightly stale data. PostgreSQL streaming replication, MySQL binlog replication, MongoDB replica sets, and Amazon RDS read replicas all follow this pattern.
2.2 Multi-Leader
Multiple nodes accept writes — useful for multi-region setups (a "Dhaka leader" and a "Singapore leader" so users in each region get low-latency writes). The hard part is conflict resolution: what if both leaders simultaneously update the same balance? Last-write-wins, vector-clock merges, or CRDTs are common approaches. CockroachDB, Cassandra (in some modes), and Yugabyte support this.
2.3 Leaderless
No node is special — clients write to several nodes simultaneously and read from several, using quorums to make the result consistent. Dynamo, Cassandra, Riak, and DynamoDB are leaderless or quorum-based.
3. Sharding / Partitioning — Splitting One Table Across Many Machines
Replication makes copies. Sharding makes splits. A 4-billion-row order table can be cut into 16 shards of ~250 million rows each, distributed across 16 machines. Three classical strategies:
| Strategy | How it works | Pros | Cons |
|---|---|---|---|
| Range | Shard 1 = ids 1–10M, shard 2 = 10M–20M … | Excellent for range scans | Hot shards if traffic skews to recent ids |
| Hash | shard = hash(key) mod N | Even load, no hotspots | Range scans now hit every shard |
| Directory | A separate lookup service: "key X → shard 7" | Total flexibility, easy rebalance | Lookup service is a new SPOF and extra hop |
Consistent hashing is a refined hash strategy that lets you add or remove shards while moving only ~1/N of the keys (instead of all of them). Cassandra, DynamoDB, Riak, and most modern key-value stores use it.
-- We can't actually shard inside a single SQLite, but we can SIMULATE
-- shard routing: which shard does each key live on for each strategy?
CREATE TABLE orders(id INTEGER PRIMARY KEY, user_id INTEGER, amount INTEGER);
INSERT INTO orders VALUES
(1,101,500),(2,102,300),(3,103,800),
(4,104,120),(5,105,990),(6,106,450),
(7,107,270),(8,108,600);
-- Range sharding by id: shards of width 3
SELECT id, user_id,
(id-1)/3 AS range_shard,
user_id % 4 AS hash_shard
FROM orders
ORDER BY id;4. The CAP Theorem — One Sentence That Shapes Every Distributed DB
Eric Brewer's CAP theorem (2000, formalized by Gilbert & Lynch in 2002) states: in the presence of a network partition, a distributed system can preserve at most one of Consistency (every read sees the latest write) or Availability (every request gets a non-error response). Partition tolerance (P) is not optional — networks fail — so the real choice on a partition is CP or AP.
Worked scenario — bKash transfer during a fibre cut
Imagine bKash runs two replicas: one in Dhaka, one in Chattogram. A fibre cut between them creates a partition. A user in Sylhet sends 500 BDT.
- CP choice: The Chattogram replica refuses writes until the partition heals — the user sees "transfer failed, please try again". No money is lost, but service is partially unavailable.
- AP choice: Both replicas accept writes independently. The user sees "transfer successful". When the partition heals, the system must reconcile: what if both sides spent the same 500 BDT? Now you have a double-spend.
Money systems almost always pick CP. Social-feed systems and shopping carts often pick AP — a like that doesn't show up immediately is harmless; refusing every like during a partition is unacceptable.
5. PACELC — The Honest Extension of CAP
CAP only describes what happens during a partition. But partitions are rare. Daniel Abadi's PACELC (2010) extends it: if Partition (P), choose between Availability (A) and Consistency (C); Else (E), in normal operation, choose between Latency (L) and Consistency (C). Most real production trade-offs are about latency, not partitions.
| System | During partition | Normal operation | PACELC class |
|---|---|---|---|
| PostgreSQL (sync replication) | Refuses writes (CP) | Higher write latency for safety | PC/EC |
| Cassandra (default) | Available, possibly stale (AP) | Tunable, often favours latency | PA/EL |
| MongoDB (default) | Primary side keeps serving (CP-ish) | Reads can hit secondaries (eventual) | PC/EL |
| Google Spanner | CP (linearizable) | Pays latency for global consistency | PC/EC |
6. Consistency Models — A Spectrum, Not a Binary
"Consistent" is not a single thing. There is a whole hierarchy from "the system is one big database" all the way down to "writes propagate eventually". From strongest to weakest:
- Linearizability / Strong consistency: every operation appears to occur instantaneously at some point between its invocation and response. From the client's perspective the system looks like a single machine. Implemented by Spanner, etcd, ZooKeeper, single-leader RDBMSes with sync replicas.
- Sequential consistency: all clients see operations in the same global order, but that order is not necessarily real time.
- Causal consistency: if write A causally precedes write B, every client sees A before B — but unrelated writes may be reordered. CockroachDB, MongoDB causal sessions, COPS.
- Read-your-writes: a single client always sees its own writes, even if others see older values. The minimum acceptable for "user-friendly" UX.
- Monotonic reads: a client never goes "backward in time" — once you saw the new value, you never again see the old one.
- Eventual consistency: if writes stop, all replicas eventually converge. No timing guarantee. Cassandra, DNS, S3 (historically).
7. Quorums — Tunable Consistency in Leaderless Systems
In a leaderless system with N replicas, a write goes to W nodes, a read to R nodes. The crucial inequality is W + R > N: when satisfied, every read overlaps with the latest write on at least one node, so the freshest value is always visible.
| Setting | N=3 | Effect |
|---|---|---|
| W=1, R=1 | 1+1=2 ≤ 3 | Fastest, weakest. May read stale data. (eventual) |
| W=2, R=2 | 2+2=4 > 3 | Quorum reads/writes. Strong consistency in normal ops. |
| W=3, R=1 | 3+1=4 > 3 | Slow writes, fast reads. Good for read-heavy workloads. |
| W=1, R=3 | 1+3=4 > 3 | Fast writes, slow reads. Good for write-heavy workloads. |
Cassandra exposes this exact knob via consistency levels — ONE, QUORUM,
ALL, LOCAL_QUORUM (only nodes in the local data centre count), etc.
ONE, QUORUM, ALL।
-- Cassandra CQL: tunable consistency per query (NOT runnable in SQLite)
CONSISTENCY QUORUM;
INSERT INTO payments(user_id, amount, ts)
VALUES (10234, 500, toTimestamp(now()));
CONSISTENCY ONE; -- speed over freshness
SELECT * FROM payments WHERE user_id = 10234;
CONSISTENCY LOCAL_QUORUM; -- safe within one data centre, fast across DCs
SELECT * FROM payments WHERE user_id = 10234;8. Consensus — Paxos and Raft, in 5 Minutes
How does a leader-based replicated system actually elect its leader, or agree on the next entry in the log when the network is unreliable? That problem is called distributed consensus, and it has exactly two famous algorithmic answers: Paxos (Lamport, 1989, infamously hard to read) and Raft (Ongaro & Ousterhout, 2014, deliberately designed to be easy).
Raft in seven lines
- Each node is in one of three states: follower, candidate, or leader.
- Time is divided into numbered terms; at most one leader per term.
- If a follower hears nothing from a leader for an election timeout, it becomes a candidate, increments its term, and votes for itself.
- It asks every other node for a vote. Each node grants at most one vote per term.
- The candidate becomes leader if it gets a majority. Otherwise, term times out and someone else tries.
- Once leader, it sends heartbeats and replicates log entries; an entry is committed when a majority of nodes have stored it.
- If a leader is partitioned away, a new leader will be elected on the majority side; the old leader cannot commit anything because it lacks majority votes — safety is preserved.
Production users: etcd (Kubernetes' brain), Consul, CockroachDB, TiKV, RethinkDB, MongoDB's replica election (Raft-derived), and many in-house replication layers.
9. Choosing for Real Workloads
✅ Strong consistency wins when…
- Money is involved (bKash, banks, brokerage)
- Inventory must not oversell (Daraz Black-Friday)
- Identity / auth (login service)
- Locks & sequencing (printing job tickets)
⚠️ Eventual consistency wins when…
- Social feeds, likes, comments
- Shopping carts (re-pick at checkout)
- Analytics dashboards (lag is fine)
- Logs, telemetry, metrics
Strong consistency is expensive — choose it where the cost of being wrong is high. Eventual consistency is cheap — choose it where being briefly stale is fine.
ভুল হলে যদি টাকা/মানুষ/আইন জড়িয়ে যায় — strong consistency নিন (PostgreSQL, Spanner, MongoDB primary)। সামান্য পুরোনো data দেখানো গ্রহণযোগ্য হলে — eventual / AP নিন (Cassandra, DynamoDB)।
10. Practice Problems
Most of these are conceptual — distributed systems can't run inside a single SQLite — but each answer includes either a worked walk-through or runnable code.
-
A 5-node cluster is configured with
N=5, W=3, R=3. Is it strongly consistent? Justify.৫-node cluster-এN=5, W=3, R=3— কি strongly consistent? কেন?✨ Show Answer
Answer: Yes (within a single data centre, ignoring partitions). W+R = 6 > N = 5, so any read quorum and write quorum share at least one node — meaning every successful read sees the latest write. (Note: this guarantees quorum consistency, not full linearizability — for that you also need read repair / leader-style ordering.)
-
Compare range sharding vs hash sharding for a table of WhatsApp-like messages keyed on
(user_id, ts).WhatsApp-জাতীয় message-এর(user_id, ts)-keyed table-এ range vs hash sharding — কোনটা ভালো এবং কেন?✨ Show Answer
Answer: Hash by
user_id: it spreads load evenly because no single user is "hottest", and the typical query "messages for user X newer than ts T" still hits one shard. Range bytsalone would put all today's traffic on one shard — a textbook hot-shard problem. Range by(user_id, ts)with composite is fine but harder to rebalance. -
Write a SQL simulation that assigns 1 million synthetic order ids to 8 hash shards and shows the maximum and minimum shard count. Are loads balanced?১০ লাখ synthetic order id-কে ৮টি hash shard-এ ভাগ করে max ও min shard count দেখান। load কি balanced?
✨ Show Answer
ans3.sqlWITH RECURSIVE g(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM g WHERE i < 100000 ) SELECT i % 8 AS shard, COUNT(*) AS n FROM g GROUP BY shard ORDER BY shard;Each shard receives 12,500 ± a tiny number — because
i % 8is a perfect hash for sequential ids. With real (non-sequential) ids you would see <1% imbalance for any decent hash function. -
In CAP terms, classify: PostgreSQL with synchronous replication, Cassandra default, Redis Sentinel, Spanner.CAP অনুসারে classify করুন: PostgreSQL synchronous replication, Cassandra default, Redis Sentinel, Spanner।
✨ Show Answer
- PostgreSQL sync replication → CP — refuses writes if a sync replica is unreachable.
- Cassandra default → AP — every node accepts; conflicts resolved by last-write-wins timestamps.
- Redis Sentinel → CP-leaning in practice; failover may briefly lose unreplicated writes.
- Spanner → CP, achieves linearizability globally via TrueTime + Paxos.
-
Explain in your own words why "CA without P" is a misleading category for any distributed system."CA without P" কেন distributed system-এর জন্য একটি বিভ্রান্তিকর category — নিজের ভাষায় ব্যাখ্যা করুন।
✨ Show Answer
Answer: Networks fail. A "CA without P" system can only exist on a single machine — once nodes communicate over a network, partitions are inevitable, and the system will face the C-vs-A choice. So in practice every distributed system is either CP or AP; CA is just a single-node DB.
-
Define read-your-writes consistency and give an example where violating it would confuse users.Read-your-writes consistency কী? এটি না থাকলে user কেমন বিভ্রান্ত হবে?
✨ Show Answer
Answer: A client always sees the effects of its own previous writes. Without it: a user updates her bKash profile photo, refreshes, and the old photo is back — because the read landed on a not-yet-replicated follower. Solution: pin reads to the same primary or use a session token (e.g., MongoDB causal sessions).
-
Sketch how a Raft cluster of 5 nodes survives the loss of any 2 nodes but cannot survive losing 3.৫-node Raft cluster যেকোনো ২ node হারিয়ে কীভাবে টিকে থাকে এবং ৩ হারালে কেন থামে — ব্যাখ্যা করুন।
✨ Show Answer
Answer: Raft commits an entry only when a majority (⌈N/2⌉+1 = 3 of 5) acknowledges it. Lose 2 → 3 healthy → still a majority → keeps committing. Lose 3 → only 2 healthy → no majority → no leader can be elected and no entry can be committed. The cluster pauses (preserving safety) until a third node returns.
-
Given a 4-billion row order log that is queried mostly by
WHERE order_id = ?, design a sharding scheme. How many shards, what key, and why?৪০০ কোটি row-এর order log, query mostlyorder_idদিয়ে — sharding scheme design করুন।✨ Show Answer
Answer: Hash-shard by
order_idacross (say) 32 shards: ~125M rows each, perfectly balanced, every point lookup hits exactly one shard. Use consistent hashing so adding a 33rd shard moves only ~3% of keys. Add an asynchronous secondary index (or column-store sink) for analytics queries that aren't keyed on order id. -
PACELC: classify a system you use every day (e.g., DNS, Gmail, BillDesk).প্রতিদিন ব্যবহার করেন এমন একটি system (DNS, Gmail) PACELC-এ classify করুন।
✨ Show Answer
Answer (DNS): PA/EL — partition tolerant, available even when name servers can't reach each other (resolvers cache stale entries), and even in normal operation it favours latency over consistency (TTL-based caching, propagation delay). That is exactly the right trade-off for "what IP is google.com?" but the wrong trade-off for "did my payment go through?"
-
In a leaderless system with N=3, can you tune R and W such that writes are very fast but reads are still consistent? Explain trade-off.N=3 leaderless system-এ এমনভাবে R, W set করতে পারবেন যাতে write খুব দ্রুত কিন্তু read consistent থাকে?
✨ Show Answer
Answer: Yes — set
W=1, R=3. Writes touch only 1 node so they are fast; reads touch all 3 and pick the most recent value, satisfyingW+R > N. The cost: reads are now slow and require all replicas to be up. Useful for write-heavy logging where fresh reads are rare. -
Why can a multi-leader topology cause double-spends, and how do real systems mitigate it?Multi-leader topology-এ double-spend কেন ঘটতে পারে? real system কীভাবে এটি ঠেকায়?
✨ Show Answer
Answer: Each leader can independently approve a withdrawal because it doesn't see the other's pending writes during a partition. Mitigations: (a) shard the user's account so only ONE leader ever owns that account (eliminates the conflict by construction); (b) use a global consensus layer (Spanner, CockroachDB) that turns the "multi-leader" into per-key Raft groups; (c) accept conflicts and use CRDTs for the small set of operations that commute (counters, sets).
-
A startup wants strong consistency and global low latency. Is that possible? What is the cost?কোনো startup চায় global low-latency এবং strong consistency — সম্ভব কি? খরচ কী?
✨ Show Answer
Answer: Approximately, yes — Google Spanner does it via TrueTime (atomic clocks + GPS) and Paxos per shard. The cost is real money: hardware (atomic clocks in every data centre), engineering complexity, and a hard floor of ~5–10 ms commit latency from the speed of light alone. For most companies the realistic answer is "strong consistency regionally, eventual across regions" — exactly what Aurora Global, Yugabyte, and CockroachDB offer in cheaper deployments.
Summary — Module 37
Going distributed earns scale and durability but introduces choices that no single-machine engineer ever has to make. Replication (leader-follower, multi-leader, leaderless) gives durability and read-scaling. Sharding (range, hash, directory, consistent-hash) gives write-scaling. The CAP theorem — extended by PACELC — frames the inevitable trade-off during partitions and even during normal operation. Within that frame lives a whole spectrum of consistency models, tunable in leaderless systems via the quorum inequality W + R > N. And whenever multiple nodes must agree on a value or a leader, we lean on consensus protocols — Paxos, or its modern, friendlier cousin Raft.