Wide-column stores: Cassandra and the write-heavy case

You have now met three families in depth: relational (PostgreSQL), document (MongoDB) and key-value (Redis). This module surveys the rest — wide-column, graph, search, time-series and vector — not to make you an expert in each, but so you can recognise the shape of problem each one exists for and know when to reach past PostgreSQL. Recognition is the skill; you can learn the specific engine when a real project calls for it.

We start with wide-column, of which Apache Cassandra is the canonical example (with ScyllaDB as a faster-C++ reimplementation and Google Bigtable / HBase as relatives).

The problem it exists for

Imagine writes arriving faster than any single machine can absorb — millions per second — and a requirement that the system never stop accepting them, even when a data-centre goes offline. Think sensor telemetry from millions of devices, a global messaging platform's message log, or activity events from a billion users.

PostgreSQL, with a single primary that all writes funnel through, hits a ceiling here. You can read-scale it with replicas, but write throughput is bounded by that one primary, and if it fails, writes pause during failover. Wide-column databases are built for exactly the case where that ceiling is the problem.

The model: a distributed, write-optimised table

A wide-column store looks superficially like a table — rows and columns — but the model is different in ways that matter:

Every row is found by a partition key, and the partition key decides which machine the row lives on. Data is spread ("sharded") across many nodes automatically by hashing that key.
Rows in the same partition are stored together and sorted by a clustering key, so reading a range within a partition is fast.
Columns can vary per row — hence "wide-column"; a row is really a map of column-name to value, and different rows can have different columns.

Cassandra's query language, CQL, is deliberately SQL-like to look familiar:

CREATE TABLE messages (
  conversation_id uuid,
  sent_at timestamp,
  sender_id uuid,
  body text,
  PRIMARY KEY (conversation_id, sent_at)   -- partition key, then clustering key
);

SELECT * FROM messages
WHERE conversation_id = ?          -- MUST filter on the partition key
ORDER BY sent_at DESC LIMIT 50;

That looks like SQL, and that familiarity is a trap, because the rules underneath are the opposite of relational.

What you give up — and it is a lot

The write scalability is bought with sacrifices that will feel like going backwards if you expect a relational database:

You must query by the partition key. There is no efficient "find all messages from this sender across every conversation" unless you designed a table for it. A query without the partition key scans the whole cluster and is either forbidden or catastrophically slow.

No joins. At all. You denormalise aggressively and model one table per query. The same data is duplicated across several tables, each shaped for a specific read. This is not a mistake to avoid — it is the intended design, and it inverts everything module 8 taught about normalisation.

Tuneable consistency, not ACID. This is the CAP theorem from module 1 made concrete. You choose per operation how many replicas must acknowledge: a write with consistency ONE is fast but a later read might not see it; QUORUM (a majority) reads and writes give you consistency at higher latency. You dial the trade-off; you do not get ACID for free. Cassandra is AP by default — it stays available during a partition and lets consistency be eventual.

Limited or no transactions. Cassandra has lightweight transactions (a compare-and-set via Paxos) for narrow cases, but nothing like PostgreSQL's multi-row BEGIN/COMMIT.

Design is query-first and rigid. Because tables are shaped per query, a new query pattern often means a new table and a data migration. You must know your access patterns before you design — even more strictly than with MongoDB.

Why the writes are so fast

The mechanism is worth knowing because it explains the trade-offs. Cassandra uses a log-structured merge tree (LSM tree): a write is appended to a commit log and an in-memory table, and acknowledged immediately — no in-place update, no seeking to modify a B-tree page. Data is later flushed and merged into sorted files in the background.

The result: writes are nearly as cheap as appends, and there is no single primary to bottleneck them because any node can accept a write for any partition it is responsible for. The cost lands on reads, which may have to merge several files, and on the background compaction that keeps those files tidy. Wide-column stores trade read simplicity and consistency for write throughput and availability — the mirror image of PostgreSQL's priorities.

How to recognise the need

Reach for a wide-column store when all of these hold:

Write volume exceeds what a single primary can take — genuinely, measured, not imagined.
Your access patterns are few and known in advance, so query-first table design is workable.
You can tolerate eventual consistency for most operations.
Continuous availability across data-centres matters more than strong consistency.
The data is naturally partitionable by some key (per conversation, per device, per user).

Real fits: time-series telemetry at massive scale, messaging and chat history, activity feeds and event logging for very large user bases, and any write-mostly workload spread across regions. Companies known to run Cassandra at scale include Netflix, Apple and Discord.

And the warning, which is this whole module's refrain: do not choose Cassandra for scale you do not have. It is operationally complex, the query restrictions are severe, and a single well-indexed PostgreSQL instance handles far more write volume than most teams will ever reach. The wide-column model is a specialist tool for a specialist problem; using it for an ordinary app because it sounds web-scale is the exact mistake the MongoDB lesson warned about, one family over.

Check your work

The problem wide-column stores exist for. Write volume and continuous availability beyond what a single-primary database can provide.

The canonical example. Apache Cassandra (ScyllaDB, Bigtable, HBase are relatives).

What the partition key decides. Which machine a row lives on — how data is sharded.

What the clustering key does. Sorts rows within a partition, so range reads there are fast.

Why CQL looking like SQL is a trap. The rules underneath (query by partition key, no joins, tuneable consistency) are the opposite of relational.

The one-table-per-query rule. You denormalise and duplicate data across tables shaped per read; this is intended, not a mistake.

What "tuneable consistency" means. You choose per operation how many replicas must acknowledge (ONE, QUORUM); you dial the CAP trade-off rather than getting ACID.

Cassandra's default CAP stance. AP — available during a partition, eventually consistent.

Why writes are so fast. An LSM tree appends writes and acknowledges immediately; any node accepts writes, so there is no single-primary bottleneck.

Where the cost lands. On reads (merging files) and background compaction.

The five conditions that justify it. Write volume beyond one primary, few known access patterns, tolerance for eventual consistency, cross-DC availability over consistency, and naturally partitionable data.

The warning. Do not adopt it for scale you do not have; one PostgreSQL instance handles more than most teams reach.

Practice

Describe an application whose write volume would genuinely exceed a single PostgreSQL primary. Estimate the writes per second.
For a messaging app, design the Cassandra table for "the last 50 messages in a conversation". Note the partition and clustering keys.
Now add the query "all messages a user sent across all conversations". Explain why it needs a second table, and design it.
Take a normalised relational schema and describe how you would denormalise it into one-table- per-query form. Count the duplications.
Explain, in terms of CAP, what consistency = ONE versus QUORUM buys and costs.
Explain why an LSM tree makes writes cheap and what it costs reads.
Find a real system that uses Cassandra and identify which of the five conditions applied.
Argue whether an application you know should use Cassandra. Most should not — say why.

Official documentation

Apache Cassandra — Documentation — The data model, CQL, and the architecture.
Cassandra — Data modeling — Query-first design and one-table-per-query.
Cassandra — Tunable consistency — Consistency levels and the CAP trade-off in practice.
ScyllaDB — Cassandra alternative — The high-performance reimplementation, for context.
Google — Bigtable overview — The wide-column ancestor, and where the model came from.

Next: graph databases, for when the relationships are the data.