Wide-column databases

Why it matters

When a system writes hundreds of thousands of events per second and must keep working when a data centre goes dark, the relational model's single primary is the problem. Wide-column stores were built for that regime: every node accepts writes, data is placed by a key you choose, and there is no coordinator to lose. The price is that you must know your queries before you design your tables, which is a discipline most engineers learn by getting it wrong once.

The idea

A wide-column store organises data into tables of rows identified by a primary key with two parts. The partition key is hashed to decide which nodes hold the row; all rows sharing a partition key live together, sorted by the clustering key. Columns are sparse: each row may have different columns, and a partition can hold millions of rows. Think of it as a distributed, sorted map of maps rather than a spreadsheet.

Query-first design

In a relational database you normalise the data and write any query later. In a wide-column store you start from the queries and build one table per query pattern, duplicating data as needed. "Readings for a device over a time range" is a table partitioned by device with time as clustering key. "Latest reading per device in a region" is a different table partitioned by region. Writes go to both (the application or a materialised view feature does the fan-out). This feels wrong to relational instincts and is correct here: storage is cheap, cross-partition queries are not.

Partition design rules

1. 1

   Every query must name the partition key

   Queries without it scan every node and are either forbidden or ruinously slow.
2. 2

   Keep partitions bounded

   A partition that grows forever (all events for a popular device) becomes a hot spot and a huge object to compact. Bucket it: partition by (device_id, day) so each partition has a bounded size.
3. 3

   Spread writes evenly

   A partition key with few values or one dominant value concentrates load on one node. High cardinality with even distribution is the goal; add a bucket component if needed.
4. 4

   Order by how you read

   Clustering keys set the sort; choose descending time if you mostly want the latest.

Tunable consistency and replication

Each partition is replicated to N nodes (typically three) with no primary: any replica accepts writes. Per operation you choose a consistency level: how many replicas must acknowledge a write and how many must answer a read. With QUORUM on both (two of three), every read overlaps with the latest write and you get strong consistency for that key; with ONE you get lowest latency and eventual consistency. Conflicts between replicas resolve by last-write-wins timestamps, which means concurrent updates to the same column can silently lose one, so model data as immutable appends where possible. Multi-data-centre replication is built in, which is the headline operational advantage.

Storage engine and operations

Underneath is an LSM tree per node: writes are appends, reads merge SSTables, compaction runs continuously, deletes are tombstones. Everything from the LSM lesson applies, including watching compaction backlog and avoiding delete-heavy patterns. Repairs reconcile replicas that drifted while a node was down.