Start practising

SYSTEM DESIGN · TECHNOLOGIES · 4 OF 12

Cassandra: Write-Heavy, Always-On, Query-First Data Modelling

How Apache Cassandra spreads data over a token ring with no leader, partition keys and clustering columns, the commit log, memtable and SSTable write path, bloom filters and compaction on reads, tunable consistency with LOCAL_QUORUM, tombstones and repair, modelling one table per query, where it hurts and how to justify it in an interview.

  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
1 / 16

Press play to hear the narration, or step through at your own pace.

Notes

What it is

Cassandra is a distributed wide-column database. It combines the Dynamo idea of a leaderless ring with Bigtable's storage format. Every node is equal: any node can accept any read or write, and nodes learn about each other by gossip. There is no primary to fail over, which is the whole appeal. ScyllaDB is a fast rewrite with the same protocol.

Cassandra in one line
  • distributed wide-column DB
  • from the Dynamo + Bigtable
  • ideas
  • no leader: any node takes
  • any request
  • nodes find each other by
  • gossip
  • compatible: ScyllaDB (C++),
  • Amazon Keyspaces, Astra DB

Data model

The data model is the key idea. A primary key has two parts. The partition key, here chat id, is hashed to decide which nodes store the row, and all rows with that key live together. Clustering columns, here sent at, sort rows inside the partition on disk. So fetching the latest fifty messages of a chat reads one partition, already in order. That is the only kind of query Cassandra is truly fast at.

One table per query
CREATE TABLE messages_by_chat (
  chat_id   uuid,
  sent_at   timeuuid,
  sender_id uuid,
  body      text,
  PRIMARY KEY ((chat_id), sent_at)
) WITH CLUSTERING ORDER BY
  (sent_at DESC);

-- latest 50 messages of a chat:
-- one partition, already sorted

Token ring

Here is placement. The partition key is hashed to a token, and each node owns ranges of tokens. With a replication factor of three, the owner and the next two nodes around the ring keep copies. A token-aware driver sends the request straight to a replica, which acts as coordinator. Adding a node only takes ranges from its neighbours. This is consistent hashing, Concepts episode 22.

Placement
  • token = Murmur3(partition key)
  • each node owns token ranges
  • (many vnodes per node)
  • replication factor RF = 3:
  • owner + next 2 on the ring
  • add a node: it takes ranges
  • from neighbours, no reshuffle
  • of everything (Concepts 22)

Write path

Writes are cheap by design. A replica appends the write to its commit log, a sequential disk write, inserts it into an in-memory sorted table, and acknowledges. That is it. No reading the old row, no updating in place. When the memtable fills, it is flushed to a new immutable file called an SSTable. This is a log-structured merge tree, covered in Concepts episode 23.

Why writes are cheap
  • 1. append to the commit log
  • (sequential disk write)
  • 2. insert into the memtable
  • 3. ack
  • memtable full → flush to a
  • new immutable SSTable
  • no read before write, no
  • in-place update: an LSM tree
  • (Concepts episode 23)

Read path

Reads do more work. Pieces of one row may sit in the memtable and in several SSTables. For each file, a bloom filter answers definitely not here, so most files are skipped, and an index points straight to the partition. The pieces are merged and the newest timestamp wins. Background compaction merges SSTables, so reads touch fewer files. Pick the compaction strategy to match the workload; time-window compaction suits time series.

Why reads cost more
  • a row may be spread across
  • the memtable + many SSTables
  • per SSTable: bloom filter
  • → "definitely not here" skips
  • → partition index → offset
  • merge versions, newest wins
  • compaction merges SSTables
  • so reads touch fewer files

Consistency

Consistency is chosen per query. With three replicas, ONE waits for a single answer, QUORUM for two, and ALL for three. Most multi-region setups use LOCAL QUORUM, two of three in the local region, so a slow far region never adds latency. If both reads and writes use quorum, R plus W is greater than N and reads see the latest write. Concepts episode 27 explains why.

Tunable consistency
  • RF = 3
  • ONE: 1 replica answers
  • QUORUM: 2 of 3
  • LOCAL_QUORUM: 2 of 3 in this
  • region (the usual choice)
  • ALL: every replica
  • write QUORUM + read QUORUM
  • → R + W > N, reads see the
  • latest write (Concepts 27)

Multi-region

Cassandra was built for multiple regions. You set a replication factor per data centre, say three in each. Clients write locally with local quorum, and the other region catches up in the background. Every region accepts writes, so it stays up when a whole region is lost. The trade-off: concurrent updates to the same column are resolved by last write wins, using timestamps.

Multi-region, active-active
  • NetworkTopologyStrategy:
  • RF 3 in region 1,
  • RF 3 in region 2
  • clients write locally with
  • LOCAL_QUORUM; other regions
  • catch up asynchronously
  • conflicts: last write wins
  • by timestamp, per column

Tombstones + repair

Deletes are surprising. A delete is actually a write of a marker called a tombstone, because files are immutable. Reads must step over tombstones, so a queue-like table with constant deletes gets slow. Tombstones are purged after a grace period, ten days by default. Repair, which syncs replicas, must run more often than that, or a replica that missed the delete can bring the data back to life.

Deletes and repair
  • delete = write a tombstone
  • reads must skip tombstones;
  • thousands in one partition
  • = slow or failed reads
  • tombstones purged after
  • gc_grace_seconds (10 days)
  • run repair (anti-entropy)
  • more often than that, or
  • deleted data can come back

Modelling rules

The modelling rules are the opposite of SQL. List the queries first, then create one table per query, duplicating data freely. Messages by chat and messages by user are two tables, and the app writes to both. Keep partitions bounded, for example by bucketing a sensor's readings by day, aiming under about a hundred megabytes each. And do not expect joins or filtering on columns that are not in the key.

Query-first modelling
  • 1. list the queries first
  • 2. one table per query
  • (messages_by_chat,
  • messages_by_user)
  • 3. write to all of them
  • (batch or async)
  • 4. bound partitions: bucket
  • by day, ~100 MB max
  • 5. no joins, no ad-hoc
  • WHERE on other columns

Great at

Cassandra shines at enormous write volume, and at time-ordered data like chat messages, sensor readings, activity logs and feed timelines. Throughput grows roughly linearly as you add nodes. With no leader and replicas in several regions, it keeps serving through node and even region failures. Row-level time to live expires old data automatically.

Where Cassandra shines
  • • huge write throughput
  • • time-ordered data: chat,
  • events, IoT, activity feeds
  • • linear scale: 2x nodes ≈
  • 2x throughput
  • • no single point of failure
  • • multi-region active-active
  • • TTL on rows for expiry

Where it hurts

Where it hurts. No joins and no flexible queries: every new access pattern means a new table and a backfill. Compare-and-set, called lightweight transactions, runs Paxos and costs several round trips, so it is for rare cases like claiming a username. Last write wins can silently drop concurrent updates. Operating it means tuning compaction, repair and garbage collection. And one celebrity partition still overloads its three replicas.

Pain points
  • • no joins, no ad-hoc queries
  • • new query = new table
  • • lightweight transactions
  • (Paxos) are ~4 round trips
  • • last write wins can lose
  • concurrent updates
  • • tombstones, repair, JVM GC
  • • hot partition = hot nodes
  • • counting and aggregates

Use it when

Use Cassandra when writes are huge, data is time-ordered, you need multiple regions with no downtime, and you can list the queries up front. Avoid it for relational data with joins, for ad-hoc analytics, for flows that need strong transactions like payments, and honestly for small data sets, where a single Postgres is simpler. If you want this model without running it, DynamoDB is the managed cousin.

Use it when / avoid it when
  • USE when
  • writes ≫ reads, or both huge
  • time-series and event data
  • multi-region, always on
  • queries known up front
  • AVOID when
  • joins, ad-hoc analytics
  • strong transactions (money)
  • small data (just use SQL)

Numbers

Numbers to have ready. A replication factor of three per region is standard. A node commonly handles tens of thousands of writes a second with local write latency of a few milliseconds. Keep partitions under roughly a hundred megabytes and expect one to two terabytes per node. The tombstone grace period is ten days. A lightweight transaction costs about four round trips.

Numbers worth knowing
typical RF                 3 per region
writes per node            10k-50k+/s
p99 local write latency    a few ms
partition size target      under ~100 MB
data per node              1-2 TB typical
gc_grace_seconds           10 days
LWT (Paxos) cost           ~4 round trips
ScyllaDB                   more per node

Say it like this

How to say it. Chat messages are written constantly and read per chat, newest first, so Cassandra. Partition by chat id plus a day bucket so partitions stay bounded, cluster by sent at descending. Three replicas per region, reads and writes at local quorum. And a second table keyed by user for the inbox view, written alongside. That shows you understand query-first modelling.

In the interview
  • "Chat messages are append-
  • heavy and read by chat, so
  • Cassandra: partition key
  • (chat_id, day bucket),
  • clustering sent_at DESC.
  • RF 3 per region, writes and
  • reads at LOCAL_QUORUM. A
  • second table keyed by user
  • for their inbox."

Interview questions

What are the partition key and clustering columns?

The partition key is hashed to decide which nodes store a row and groups all rows with that key together. Clustering columns sort rows within the partition on disk, which makes ordered range reads inside one partition fast.

Why are writes in Cassandra so fast?

A write is a sequential append to the commit log plus an insert into the in-memory memtable, with no read of the old value and no in-place update. Memtables are later flushed to immutable SSTables.

Why can reads be slower than writes?

A row may be spread across the memtable and several SSTables, so a read must check bloom filters, read matching files and merge versions. Compaction and a good compaction strategy keep the number of files per read low.

What consistency level would you use in a multi-region deployment?

Usually LOCAL_QUORUM for both reads and writes, which gives read-after-write consistency within a region without waiting on a remote region; other regions catch up asynchronously.

What are tombstones and why do they matter?

A delete writes a tombstone marker instead of removing data. Too many tombstones in a partition slow or fail reads, and repair must run within gc_grace_seconds or deleted data can reappear.

How do you model data in Cassandra?

Start from the queries, create one denormalised table per query with a partition key that the query filters on, write to every table that needs the data, and bound partition size with time buckets.

How does Cassandra resolve conflicting writes?

By last write wins per column, using the write timestamp. Concurrent updates to the same column can therefore lose one of them, which is why clock sync and idempotent designs matter.

When would you not use Cassandra?

For relational data needing joins or ad-hoc queries, for strongly consistent transactions such as payments, or for small data sets where a single relational database is simpler to run.

What is a lightweight transaction?

A compare-and-set operation such as INSERT IF NOT EXISTS that uses Paxos among replicas. It is linearizable for that partition but costs several round trips, so use it sparingly.