Apache Cassandra & Wide-Column at Scale
Cassandra ও wide-column DB
1. The Database Built for "Always-On Writes"
Apache Cassandra began at Facebook in 2008, was open-sourced through Apache, and is today the database behind Discord's message store (trillions of messages), Netflix's viewing history, Instagram's direct messages and Apple's iCloud telemetry. What unites these workloads? Astronomical write volume, geographic distribution, and zero tolerance for downtime.
Cassandra is "SQL-shaped on the surface, completely different underneath." Its query language CQL looks
like SQL — SELECT, INSERT, WHERE — but Cassandra has no joins,
no subqueries, and forces you to know your queries before you design your schema.
That feels restrictive until you understand why: every choice in Cassandra exists to keep writes
cheap, distributed, and linearly scalable.
2. The Ring — No Primary, No Single Point of Failure
Cassandra has no leader and no follower. Every node is a peer. Each node owns a range of tokens
on a logical ring from −2⁶³ to 2⁶³−1. To find where a row
lives, Cassandra hashes its partition key with Murmur3 to a token, then walks the ring
clockwise to the first node whose range covers it.
Replication factor (RF) controls how many copies of each row exist. With RF = 3, the row is written to the natural owner plus the next two nodes clockwise. Lose any one node and reads still work; lose two and you can still read with consistency level ONE.
3. Primary Key = Partition Key + Clustering Key
In Cassandra a primary key has two parts. The first part(s) — the partition key — decide which node the row lives on. The remaining parts — the clustering keys — decide how rows are sorted within that partition. Partition keys give you scale; clustering keys give you efficient range queries.
-- Discord-style direct messages: all messages of a chat live in one partition,
-- newest first → "load last 50 messages of this chat" hits one node, one disk seek.
CREATE TABLE messages (
chat_id uuid,
sent_at timestamp,
message_id timeuuid,
sender_id uuid,
body text,
PRIMARY KEY ((chat_id), sent_at, message_id)
) WITH CLUSTERING ORDER BY (sent_at DESC, message_id DESC);
-- chat_id is the partition key (the parentheses).
-- (sent_at, message_id) are clustering keys, sorted DESC.SELECT * FROM messages WHERE chat_id = ? LIMIT 50; hits exactly the node(s) holding that
partition, reads sequential bytes off SSD, returns. This is why Discord can serve trillions of messages
from a small fleet — the access pattern was baked into the schema.
Composite partition key
You can make the partition key itself a tuple. Useful when one column alone has too many rows in a single partition.
CREATE TABLE sensor_events (
sensor_id uuid,
day date, -- "bucket" — keeps partitions bounded
ts timestamp,
temp_c decimal,
PRIMARY KEY ((sensor_id, day), ts)
);
The composite (sensor_id, day) ensures one partition per sensor per day — never unbounded.
A query for "all readings for sensor X on 2025-08-12" still hits exactly one partition.
4. CQL — Looks Like SQL, Behaves Differently
CQL is intentionally familiar. CREATE TABLE, INSERT, UPDATE,
DELETE, SELECT all exist. But four SQL features are missing — and the
absence is intentional, not lazy:
| Missing in CQL | Why |
|---|---|
JOIN | Joins need data co-located on one node. Cassandra never co-locates by default. |
| Subqueries | Same reason — would force a coordinator to fan out and aggregate, killing scalability. |
Arbitrary WHERE | Filtering on non-indexed, non-key columns triggers full-cluster scan; CQL forbids it unless you say ALLOW FILTERING. |
| Foreign keys / referential integrity | Would require cross-partition transactions — too expensive at scale. |
-- INSERT — same row, same partition, no surprises
INSERT INTO messages (chat_id, sent_at, message_id, sender_id, body)
VALUES (7821, '2025-08-12 09:11:33', now(), 42, 'হ্যালো!');
-- READ — last 50 messages of one chat. ONE partition, very fast.
SELECT sender_id, body, sent_at
FROM messages
WHERE chat_id = 7821
LIMIT 50;
-- DANGEROUS — scans every node in the cluster:
SELECT * FROM messages WHERE sender_id = 42 ALLOW FILTERING;
-- Don't. Build a second table partitioned by sender_id instead.ALLOW FILTERING is a code smell
It means "scan the whole cluster, please." Acceptable for one-off DBA work; never in application code.
If a query needs it, the schema is wrong — design a second table that supports it natively.
5. Tunable Consistency — You Decide the Trade-off
Cassandra is famous for letting you set the consistency level on every single query. With
replication factor RF = 3, a write at QUORUM waits for 2 of 3 replicas to acknowledge;
a read at QUORUM waits for 2 of 3 replicas to respond. Because R + W >
RF, the two read replicas always overlap with the two write replicas — so the latest write is
guaranteed to be among the read responses (quorum overlap).
R + W > RF হলে strong consistency নিশ্চিত — এই formula-টিই Cassandra-র মূল sleight of hand।
সাধারণ default: read=QUORUM, write=QUORUM, RF=3।
| Level | Write waits for | Read waits for | Use when |
|---|---|---|---|
ONE | 1 replica | 1 replica | Lowest latency; ok if app tolerates stale reads. |
QUORUM | ⌈RF/2⌉+1 (2 of 3) | ⌈RF/2⌉+1 | Default. R+W>RF → strong consistency. |
LOCAL_QUORUM | QUORUM in local DC only | same | Multi-region. Avoids cross-Atlantic round trips. |
ALL | All RF replicas | All RF | Critical correctness; loses any one replica → unavailable. |
-- Set consistency for the next statements
CONSISTENCY LOCAL_QUORUM;
UPDATE accounts SET balance = 1000 WHERE acct_id = 42;
SELECT balance FROM accounts WHERE acct_id = 42;
-- Both at LOCAL_QUORUM, RF=3 → R+W>RF → guaranteed to read latest write.6. Hinted Handoff, Read Repair and Anti-Entropy
What happens when a replica is down at write time? Cassandra never refuses the write — instead it uses three healing mechanisms to bring the laggard back into sync:
- Hinted handoff: the coordinator stashes the write as a "hint" for the offline node and replays it within a short window (default 3h) when the node returns.
- Read repair: on a read at QUORUM, replicas may return different values (some stale). The coordinator detects mismatches via timestamps, returns the newest, and writes the correction back to the stale replicas in the background.
- Anti-entropy /
nodetool repair: a scheduled, full Merkle-tree comparison across replicas that fixes any drift hints + read-repair missed. Run weekly on production.
7. Storage Internals — LSM Trees, Memtables, SSTables, Compaction
Cassandra's write path is a deliberate inversion of the B-tree path used by SQL engines. Instead of updating a row in place, every write is appended. This is the Log-Structured Merge Tree (LSM) — the same family used by RocksDB, LevelDB and HBase.
The write path
- Write is appended to commit log (sequential disk IO — durable on a single fsync).
- Write is inserted into the memtable (sorted in-memory structure).
- Coordinator returns success. The actual SSTable flush happens later in the background.
- When the memtable fills, it is flushed atomically to disk as an immutable SSTable (sorted string table).
- SSTables are never modified after flush — only merged via compaction.
Compaction strategies
| Strategy | Best for | Trade-off |
|---|---|---|
| STCS — Size Tiered | Write-heavy, append-mostly tables. | Read amplification can be high. |
| LCS — Leveled | Read-heavy or update-heavy tables. | More disk IO during compaction. |
| TWCS — Time Window | Time-series with TTL — sensor data, logs. | Bad if updates span time windows. |
gc_grace_seconds (default 10 days) — long
enough that any down node can come back and learn about the delete via repair. Heavy delete workloads
on the same partition cause "tombstone hell" — read latency degrades because the engine reads many
ghosts to find a few live rows.
8. Query-First Modeling — Duplicate Data on Purpose
In SQL you start with 3rd normal form: every fact lives in exactly one place. In Cassandra, you start with queries: list every read your application will perform, then design one table per query pattern. Each row of source data is written into multiple tables — because a Cassandra read should hit one partition on one node, full stop.
-- Read pattern A: "all messages of a chat, newest first"
CREATE TABLE messages_by_chat (
chat_id uuid,
sent_at timestamp,
message_id timeuuid,
sender_id uuid, body text,
PRIMARY KEY ((chat_id), sent_at, message_id)
) WITH CLUSTERING ORDER BY (sent_at DESC, message_id DESC);
-- Read pattern B: "all messages a user sent, newest first" — different partition key!
CREATE TABLE messages_by_sender (
sender_id uuid,
sent_at timestamp,
message_id timeuuid,
chat_id uuid, body text,
PRIMARY KEY ((sender_id), sent_at, message_id)
) WITH CLUSTERING ORDER BY (sent_at DESC);
-- App writes the same message to BOTH tables — usually in a single batch.
BEGIN BATCH
INSERT INTO messages_by_chat ... ;
INSERT INTO messages_by_sender ... ;
APPLY BATCH;ALLOW FILTERING. Both options have a
real cost. So spend time on the query catalogue before writing any DDL.
9. CAP Placement and "When Cassandra over Postgres?"
On the CAP triangle, Cassandra is AP-leaning: it always accepts writes, even during a network partition. Postgres is CP-leaning: it refuses writes if it cannot guarantee consistency. Neither is "better" — they answer different questions.
✅ Pick Cassandra when
- Write rate > one node can absorb (sustained 100k+/s).
- Data must live in multiple regions with low write latency.
- Access patterns are known and stable (chat, time series, IoT, audit log).
- Eventual consistency is acceptable for the business.
⚠️ Pick Postgres / SQL when
- Multi-row, multi-table ACID transactions are core (banking ledger, inventory).
- Workload mixes ad-hoc analytics with OLTP.
- Data fits comfortably on one or a small number of machines.
- Joins and complex constraints are essential.
| Cassandra | PostgreSQL | |
|---|---|---|
| Topology | Masterless ring of peers | Single primary + read replicas |
| Write scaling | Linear — add nodes, get throughput | Vertical — bigger box |
| Joins | None — duplicate per query | Yes, optimiser-driven |
| Transactions | Single-partition only (LWT) | Full ACID multi-row |
| CAP lean | AP | CP |
| Sweet spot | 10s of TB, write-heavy, multi-DC | Up to a few TB, mixed workload |
10. Practice Problems
These problems are conceptual — Cassandra does not run inside this in-browser SQLite engine. Try designs on paper or in a free Astra DB tier, then expand the answer.
-
Design a Cassandra table for IoT temperature readings such that "give me all readings of sensor X between 9 AM and 10 AM today" hits one partition.এক ঘণ্টার range query এক partition-এ হিট করার মতো schema লিখুন।
Show Answer
ans1.cqlCREATE TABLE readings ( sensor_id uuid, day date, ts timestamp, temp_c decimal, PRIMARY KEY ((sensor_id, day), ts) );Composite partition key keeps each (sensor, day) bounded; clustering on
tsturns range queries into a sequential SSTable scan. -
Why does Cassandra forbid
WHERE created_at > ?on a non-key column withoutALLOW FILTERING?non-key column-এWHEREকেন reject হয়?Show Answer
Because there is no index path — every node would have to read every row. Allowing it silently would let one bad query knock the cluster over.
ALLOW FILTERINGexists as a deliberate "yes, I know" knob for offline jobs.কোনো index নেই — সব node-কে full scan করতে হতো; এটি নীরবে অনুমতি দিলে cluster crash হতে পারত।
-
RF = 3, write at QUORUM, read at QUORUM. The latest write succeeded on 2 of 3 replicas. The 3rd has stale data. Will a subsequent read see the latest value?QUORUM read — সর্বশেষ write কি দেখা যাবে?
Show Answer
Yes.
R + W = 2 + 2 = 4 > RF = 3, so the read set must overlap the write set in at least one replica. The coordinator sees the newer timestamp from the up-to-date replica and returns it (and triggers read repair to fix the stale node).R+W>RF — তাই overlap নিশ্চিত; latest write দেখা যাবে এবং stale replica-কে background-এ ঠিক করা হবে।
-
Same setup, but writes at ONE and reads at ONE. What can go wrong?দুই দিকেই ONE — কী সমস্যা?
Show Answer
R + W = 1 + 1 = 2 ≤ RF = 3 — no overlap guarantee. A read can land on a replica that did not yet receive the latest write and return stale data. Acceptable for view counters and "presence dot" indicators; unacceptable for money.
-
Why does deleting heavily from one Cassandra partition slow reads on that same partition over time?একই partition থেকে অনেক delete করলে read কেন ধীর হয়ে যায়?
Show Answer
Each delete writes a tombstone. Until
gc_grace_secondselapses and compaction runs, the engine must read every tombstone alongside the few remaining live rows just to skip them. This is "tombstone hell" — fix by changing the data model (e.g., bucket by day with TTL) so old data ages out instead of being deleted.প্রতিটি delete একটি tombstone যোগ করে। এদের পড়েই বাদ দিতে হয় — তাই latency বাড়ে। Bucket + TTL ব্যবহার করুন।
-
A team wants Cassandra to do "report top-5 customers by spend across the last 30 days". Why is this a poor fit?এই ad-hoc analytical query Cassandra-র জন্য কেন বেমানান?
Show Answer
It needs a global GROUP BY + ORDER BY across every partition — the antithesis of "one query, one partition". Cassandra has no global secondary index for ranking. Either pre-aggregate in the application as writes happen, or pipe data into a column store like ClickHouse / BigQuery for analytics.
পুরো cluster-জুড়ে aggregate দরকার — Cassandra-র design-এর বিপরীত। বিকল্প: write-time pre-aggregation অথবা ClickHouse/BigQuery।
-
Explain why Discord can run trillions of messages on Cassandra with surprisingly few nodes.Discord কেন Cassandra-তে ভালো চলে?
Show Answer
Two reasons. (1) Their access pattern — "load the recent messages of a single channel" — is exactly what a partition key + clustering key is built for: one node, one disk seek. (2) Writes vastly outnumber reads in a chat app, and Cassandra's LSM write path is sequential append. Both pillars of Cassandra map perfectly to chat.
-
Why must
nodetool repairbe run on a regular schedule even though hinted handoff and read repair exist?hinted handoff ও read repair থাকা সত্ত্বেওnodetool repairকেন দরকার?Show Answer
Hints expire (default 3h) and read repair only fixes data the application actually reads. Cold rows that are written when one replica is down — and never read again before the hint expires — would remain inconsistent. Anti-entropy repair sweeps the entire keyspace via Merkle trees and fixes the cold corners.
Hint expire হয়; read repair শুধু পড়া row ঠিক করে। কখনো পড়া হয় না এমন row-এর জন্য full repair দরকার।
-
Why is "current timestamp" a bad partition key for an event log?event log-এ "current timestamp" partition key কেন খারাপ?
Show Answer
All events at "now" hash to one partition → one node carries every write while the rest are idle (a hot partition). Bucket by hour or day and combine with another high-cardinality field — e.g.
PRIMARY KEY ((tenant_id, day), ts). -
Define "wide partition" and "wide row" in Cassandra. Why does the modern data model usually keep partitions under ~100 MB?wide partition কী, এবং কেন ১০০ MB-র নিচে রাখা হয়?
Show Answer
A "wide row" (CQL row) is one logical row identified by a full primary key. A "wide partition" is the collection of all rows sharing the same partition key — internally one storage row that may contain millions of CQL rows. Partitions over a few hundred MB cause GC pressure on the JVM heap, slow read latency, and harm streaming during repair. Bucket the partition key (e.g., add
day) to bound it. -
A startup has 50 GB of well-structured order data, needs ad-hoc analytical reports, and one DBA. Cassandra or Postgres?৫০ GB structured data + ad-hoc report + এক জন DBA — কোনটি বাছবেন?
Show Answer
Postgres, easily. The data fits on one machine, ad-hoc analytics demand joins and a query optimiser, and Cassandra would force the team to predict every report in advance. Choose Cassandra only when scale, write throughput or geographic distribution forces your hand.
Postgres — ৫০ GB এক মেশিনে আসে, ad-hoc report-এ JOIN ও optimiser দরকার, Cassandra বানানো overkill ও বিপজ্জনক।
-
In two sentences: why is Cassandra called "AP" on the CAP triangle, and what does that mean for a banking ledger?AP মানে কী, এবং banking ledger-এর জন্য কেন বেমানান?
Show Answer
AP = it stays Available during a network Partition by accepting writes on either side and reconciling later. A banking ledger must guarantee that two simultaneous "withdraw all" operations cannot both succeed — that requires C (linearisability), not A. Use a CP system (Postgres, Spanner, CockroachDB) for money.
AP মানে — partition হলেও available থাকে, পরে reconcile করে। কিন্তু একই account থেকে দু'বার withdraw আটকাতে linearisability দরকার, যা CP system দেয়।
Summary — Module 46
Cassandra is a masterless ring of peers. Each row's partition key picks the node;
the clustering keys sort rows within that partition for cheap range scans. CQL looks
like SQL but forbids joins, subqueries and arbitrary filters — by design, to keep every read on one
partition on one node. Tunable consistency (ONE / QUORUM / LOCAL_QUORUM / ALL) lets you
dial latency vs correctness per query; the formula R + W > RF guarantees strong reads.
Storage is an LSM tree — commit log + memtable + immutable SSTables + compaction —
which makes writes a sequential append and explains the linear scaling. The mental shift from SQL is:
list your queries first, build one table per query, and accept duplication. CAP-wise Cassandra is
AP-leaning: pick it when write volume, multi-region presence and zero downtime trump
ad-hoc analytics and multi-row ACID — pick Postgres when they don't.