7.5.2 — Wide-Column, Graph, Time-Series, and Choosing

Three more families, each shaped by one workload that relational databases handle badly. Then the part that matters more than any of them: a procedure for deciding, so the choice is made by the access pattern rather than by whichever system was in the last conference talk.

1. Wide-column stores

Cassandra, ScyllaDB and HBase descend from Google's Bigtable paper. The name is a poor description — a better one is a distributed sorted map of maps.

partition key      →  clustering key(s)  →  columns
"sensor:9"            2026-08-02T10:00      { temp: 21.4, humidity: 55 }
"sensor:9"            2026-08-02T10:01      { temp: 21.5, humidity: 55 }
"sensor:9"            2026-08-02T10:02      { temp: 21.6, humidity: 54 }

The partition key decides which machine holds the data. Everything with the same partition key lives together on the same nodes. The clustering key decides the sort order within that partition. Rows in a partition are stored physically sorted, so a range query inside a partition is a sequential read.

The whole model follows from that. You query by partition key, optionally with a range on the clustering key, and that is essentially all you can do. No joins. No arbitrary WHERE on a non-key column without a secondary index, and secondary indexes in Cassandra are weak enough that the standard advice is to avoid them and duplicate the data into a second table instead.

-- Cassandra Query Language: looks like SQL, is not SQL
CREATE TABLE readings (
  sensor_id text,
  ts        timestamp,
  temp      double,
  PRIMARY KEY ((sensor_id), ts)          -- (1)
) WITH CLUSTERING ORDER BY (ts DESC);    -- (2)

SELECT * FROM readings
WHERE sensor_id = 'sensor:9'             -- (3)
  AND ts > '2026-08-02T09:00';

(1) The double parentheses mark the partition key; ts is the clustering key. (2) Newest first on disk, so "the latest readings" is the front of the partition. (3) The partition key is mandatory. A query without it either fails or requires ALLOW FILTERING, which scans every node and is a production hazard rather than a feature.

What you get for that discipline:

Linear write scalability. Writes go to an LSM tree (Chapter 7.3.3) on whichever node owns the partition, with no coordination. Adding nodes adds throughput close to linearly, and Cassandra genuinely handles very large write volumes on commodity machines.

No single point of failure. Every node is equal — there is no leader. Replication is by a factor you choose, and reads and writes use tunable consistency: you say how many replicas must respond. ONE is fast and may be stale; QUORUM on both reads and writes gives read-your-writes, because the read and write sets must overlap. Chapter 10.5 develops the quorum arithmetic.

Multi-datacentre replication as a first-class feature rather than a bolt-on.

The failure modes are specific and worth knowing:

A hot partition is a hot machine. Partition by date and every write for today goes to one node. Partition keys must spread — often by bucketing, sensor:9:2026-08 rather than sensor:9, which also bounds partition size.

Unbounded partitions get slow. A partition holding years of data on one node eventually costs too much to read or repair. Bucket by time.

Tombstones accumulate. Deletes write markers (Chapter 7.3.3), and a query that scans a range full of them reads all of them to return nothing. A queue-like table in Cassandra — insert, read, delete — is the classic anti-pattern, because it produces exactly the tombstone-heavy range scans the design is worst at.

Last-write-wins resolves conflicts by timestamp, which means a clock skew between nodes can silently discard the newer write. Chapter 10.3 covers why wall clocks are not a reliable ordering.

Use it when you have very high write volume, a known key-based access pattern, and a genuine need for multi-region availability. Do not use it as a general-purpose database, and be suspicious of any design where you cannot name the partition key of every query in advance.

2. Graph databases

Some questions are about relationships, and relational databases answer them by joining a table to itself repeatedly. "Who are my friends' friends who work at companies my company sells to" is four self-joins, and each one multiplies the intermediate result.

A graph database stores relationships as first-class objects. A node holds a pointer to its relationships, and a relationship holds pointers to both nodes. Traversing from one node to its neighbours is following pointers — the cost does not depend on how large the graph is. Neo4j calls this index-free adjacency, and it is the actual reason graph databases are fast at what they are fast at.

// Neo4j's Cypher: friends-of-friends who are not already friends
MATCH (me:Person {id: 42})-[:FRIEND]->(f)-[:FRIEND]->(fof)   // (1)
WHERE NOT (me)-[:FRIEND]->(fof) AND fof <> me                // (2)
RETURN fof.name, count(*) AS mutual                          // (3)
ORDER BY mutual DESC LIMIT 10;

(1) The arrow notation is the query. Read it as a path: me, to a friend, to their friend. (2) Exclude people I already know and myself. (3) Count how many paths reach each person — the number of mutual friends — and rank by it. The same query in SQL is two self-joins with a NOT EXISTS, and it is genuinely harder to read and usually slower at depth.

Where the difference becomes decisive is depth. A two-hop join in SQL is fine. Four hops multiplies intermediate results and the planner struggles. Six hops with a variable path length — "any route between these two accounts, up to six steps" — is straightforward in a graph query and painful in SQL, even with the recursive CTE from Chapter 7.2.2.

Real uses that justify one: fraud rings (accounts connected by shared devices, addresses or cards), recommendation by traversal, network and infrastructure dependency mapping, identity resolution, and knowledge graphs.

The honest limits. Graph databases are usually worse at bulk aggregation across all nodes, harder to shard (a graph does not partition cleanly — that is what makes it a graph), and represent an extra system to operate. If your deepest question is two hops, a relational schema with the right indexes is the better answer, and most systems that adopted a graph database did not need one.

PostgreSQL can do a lot of this: recursive CTEs handle bounded traversal, and the SQL:2023 property-graph syntax is arriving in engines. Reach for a dedicated graph database when traversal depth and path queries are the core of the product, not a feature of it.

3. Time-series databases

Time-series data — metrics, sensor readings, prices, events — has a shape so specific that specialised engines beat general ones by an order of magnitude.

What makes it special:

• Writes are almost always appends at the current time, never updates.
• Reads are almost always a range of time, often aggregated: average per minute over the last day.
• Old data becomes less interesting at a predictable rate.
• Adjacent values are very similar, which makes them compress extremely well.

What the engines do about it:

Columnar storage per time chunk (Chapter 7.8.1 explains why this matters), so reading one metric does not read the others.

Delta and delta-of-delta encoding. Store timestamps as the difference from the previous one, then the difference of those differences — for a regular one-second interval, that is a stream of zeros. Values get the same treatment plus XOR encoding for floats. Facebook's Gorilla paper reported compression to around 1.4 bytes per point from 16. That is the main reason a time-series database holds ten times more data on the same disk.

Automatic retention policies and downsampling. Keep raw data for 7 days, one-minute averages for 90 days, one-hour averages for two years, and delete the rest — expressed as configuration rather than a cron job that someone must maintain.

Continuous aggregates: pre-computed rollups updated as data arrives, so a dashboard query reads a small summary rather than a billion points.

The main systems, honestly. Prometheus is a metrics system with its own store, pull-based, and is the default for infrastructure monitoring (Chapter 10.10). InfluxDB and TimescaleDB are general time-series stores — TimescaleDB is a PostgreSQL extension, which means you keep SQL, joins and your existing tooling, and is often the pragmatic choice. ClickHouse is a columnar analytics engine that is extremely good at time-series-shaped queries. Chapter 11.19 designs a metrics system end to end.

The trap to avoid: storing high-volume metrics in your transactional database. A metrics table in PostgreSQL grows to hundreds of millions of rows, bloats, competes for the buffer pool with the data your users are waiting on, and makes vacuum a permanent background problem. Metrics belong somewhere else — that separation is worth more than any index you could add.

4. Choosing, as a procedure

Do not start from the database. Start from four questions, in this order.

1. What are the access patterns? Write down every query the system will run, with rough frequencies. If you cannot list them, you need a relational database, because it is the only family that answers questions you have not thought of.

2. What is the actual scale? Rows, bytes, writes per second, reads per second. Then check against a real number: a well-indexed PostgreSQL instance on modern hardware handles low tens of thousands of simple transactions per second and terabytes of data. Most systems that "outgrew" relational never measured.

3. What consistency does each operation need? Not the system — each operation. A payment needs strict correctness; a view counter does not. If the answer is "everything must be strictly correct", distributed alternatives get much less attractive.

4. What is the shape of the data? Deeply relational, self-contained aggregates, pure key lookups, relationship traversal, or a stream of timestamped points.

Then:

If	Choose
Anything unclear or evolving	Relational
Key lookups, extreme throughput	Key-value
Self-contained aggregates, varying shape	Document, or jsonb in PostgreSQL
Huge write volume, known key access, multi-region	Wide-column
Deep traversal is the product	Graph
Timestamped appends, range reads	Time-series
Aggregation over billions of rows	Columnar (Chapter 7.8.1)

Two rules that outrank the table.

Start relational unless you have a specific reason not to. The reason must be a number or a named access pattern, not "scale". You can always move a specific workload out later, and moving one table out of a relational database is far easier than discovering three years in that you cannot answer a new question.

Polyglot persistence is the normal end state, and it is not free. Real systems end up with PostgreSQL for core data, Redis for cache and sessions, Elasticsearch for search, and a time-series store for metrics — because each is genuinely better at its job. But every additional store is another thing to operate, back up, secure, monitor, and keep in agreement. Two stores holding the same fact will disagree eventually, and reconciling them becomes real work. Add a store when the pain of not having it exceeds that cost, and not before.

5. NewSQL and distributed SQL

The families above were designed when the choice was "SQL on one machine" or "scale without SQL". That is no longer the choice.

Distributed SQL systems — Google Spanner, CockroachDB, YugabyteDB, TiDB — shard data across machines while keeping SQL, joins and ACID transactions, using consensus (Chapter 10.7.2) for replication and either atomic clocks or careful timestamp ordering for cross-shard transactions.

Vitess and Citus take the other route: shard MySQL or PostgreSQL itself, keeping the engine and adding a coordination layer.

The honest cost of all of them: a cross-shard transaction requires coordination, so it is much slower than a local one, and the latency floor is set by the distance between replicas. You get SQL and transactions at scale, and you pay in write latency and operational complexity. They are a real option for a system that genuinely needs both, and over-engineering for most.

What the interviewer will push on

"When would you use Cassandra?" Very high write volume with a known partition-key access pattern and a real multi-region requirement. Then show you know the discipline it demands: every query must name a partition key, secondary indexes are weak so you duplicate into a second table, and partitions must be bucketed to stay bounded and to avoid a hot node.

"What is a partition key versus a clustering key?" The partition key decides which machine holds the data; the clustering key decides the sort order within the partition, which is what makes a range read sequential. The tell is drawing the consequence — a poorly chosen partition key concentrates all of today's writes on one node.

"Why is a graph database faster for friends-of-friends?" Index-free adjacency: a node holds direct pointers to its relationships, so traversal is pointer-following rather than an index lookup per hop, and the cost does not grow with total graph size. Then be honest — at two hops SQL is fine, and the difference only becomes decisive at depth or with variable-length paths.

"Why not store metrics in PostgreSQL?" Volume and shape. Hundreds of millions of append-only rows bloat the table, compete for the buffer pool with user-facing data, and make vacuum a permanent problem — while a time-series engine compresses the same data by ten times using delta-of-delta encoding and downsamples it automatically. The separation is worth more than any index.

"How do you choose a database?" Access patterns first, then measured scale, then per-operation consistency, then data shape. Say the sentence that matters: if you cannot list the access patterns, you need relational, because it is the only one that answers questions you have not thought of yet. "We might need to scale" is not a reason.

"What is the cost of polyglot persistence?" Each store is another thing to operate, back up, secure and monitor, and two stores holding the same fact will disagree. It is the normal end state and it should be reached deliberately, one store at a time, when the pain of not having it exceeds that cost.

One thing to volunteer: mention that a queue implemented in Cassandra is a known anti-pattern — insert, read, delete produces tombstone-heavy range scans, which is precisely the access pattern an LSM store is worst at. It shows you reason from the storage engine to the workload rather than from marketing material.

Recall

• Wide-column is a distributed sorted map of maps: the partition key picks the machine, the clustering key sorts within it. Every query must name the partition key; ALLOW FILTERING is a hazard, not a feature.
• Cassandra buys linear write scaling, no leader, tunable consistency (quorum reads plus quorum writes give read-your-writes) and multi-region replication. It costs hot partitions, unbounded partitions, tombstones, and last-write-wins by timestamp.
• A queue in Cassandra is the classic anti-pattern — insert/read/delete makes tombstone-heavy range scans, the worst case for an LSM store.
• Graph databases use index-free adjacency: nodes point directly at their relationships, so traversal cost does not grow with graph size. Decisive at depth and for variable-length paths; unnecessary at two hops.
• Time-series engines win on shape: append-only writes, range reads, delta-of-delta and XOR compression taking points from ~16 bytes to ~1.4, plus retention policies, downsampling and continuous aggregates. Keep metrics out of your transactional database.
• Choosing: access patterns → measured scale → per-operation consistency → data shape. If you cannot list the access patterns, you need relational.
• Start relational. The reason to leave must be a number or a named access pattern. Moving one workload out later is far easier than losing the ability to ask new questions.
• Polyglot persistence is normal and not free — every store is another thing to operate and another copy that can disagree. Distributed SQL (Spanner, CockroachDB, Vitess, Citus) keeps SQL and transactions at scale, paying in write latency and operational complexity.

Self-test: What decides which Cassandra node holds a row, and what decides its order there? · Why does bucketing a partition key by month matter? · What is index-free adjacency and when does it stop mattering? · Why does time-series data compress to about a tenth? · What is the first question when choosing a database? · What does every extra datastore cost you?

Next: 7.6 covers the store almost every system ends up adding first — Redis — its data structures, its persistence models, and the failure modes of putting a cache in front of a database.