NoSQLcassandrapartition keyclustering keyneo4jtraversal

Wide-Column, Graph, Search and Time-Series

Four specialised models, each built around one query shape: partition-local time-ordered reads at huge write rates, multi-hop traversal, relevance-ranked text, and range aggregates over time.

Interview question See how this works internally →

Progress

Wide-column: design the table for the query

Cassandra and its relatives store rows under a partition key that decides which node holds them, sorted within the partition by a clustering key. A query must supply the partition key; it may range over the clustering key. That is the whole query language, and it is why writes scale linearly across nodes: every write goes to the partition’s owners with no coordination. Secondary indexes exist and are slow; joins do not exist; the rule is one table per query, denormalising freely because storage is cheap and joins are not available.

Right for: messages per conversation, events per device, readings per sensor — high write volume, reads that always know the entity and want a time range. Wrong for: anything that needs to ask across partitions, or to update a row in place under contention (last-writer-wins by timestamp is the only conflict rule).

CQL: the partition is the query

1CREATE TABLE messages_by_conversation (
2  conversation_id uuid,
3  created_at      timeuuid,
4  sender_id       uuid,
5  body            text,
6  PRIMARY KEY ((conversation_id), created_at)
7) WITH CLUSTERING ORDER BY (created_at DESC);
8 
9-- the only shape of query this table answers, and it is very fast:
10SELECT * FROM messages_by_conversation WHERE conversation_id = ? LIMIT 50;

Graph: when the query is a path

A graph database stores nodes and typed edges and makes traversal the primitive: from this node, follow these edges, k hops, with these filters. In a relational database each hop is a self-join on the edge table, and the planner’s cost grows with every hop; by the third or fourth hop the query is unrunnable. A graph engine follows pointers from node to adjacent node in constant time per hop — the Adjacency List made physical.

Right for: recommendations ("people who bought this also bought"), fraud detection (rings of accounts sharing devices), dependency analysis, access control with inheritance, knowledge graphs. Wrong for: aggregates over the whole graph, bulk scans, anything tabular. Most systems with a graph-shaped question also have relational data; the graph store is fed from the system of record.

Search: relevance is not a WHERE clause

An inverted index maps each term to the documents containing it, with positions and frequencies, which is what makes "documents containing *index* near *scan*, ranked by relevance, with typo tolerance and per-field boosts, faceted by category" answerable in milliseconds across millions of documents. Elasticsearch and OpenSearch are that index with a distributed query engine and a JSON API. They are eventually consistent, refresh on an interval, and are not a system of record: documents are indexed *from* the database, and a lost index is rebuilt from it.

PostgreSQL full-text search covers the basic case — see JSONB, Full-Text Search and Extensions. Reach for a search engine when relevance tuning, facets, fuzzy matching or scale are the product.

Time-series and columnar

Metrics, logs and sensor data are written once, in time order, at high volume, and read as aggregates over time ranges: average per minute, p99 per hour, count per day. A row store reads every column of every row to answer that; a columnar store reads only the columns involved, compresses each column by 10–100× because adjacent values are similar, and vectorises the aggregate. Time-series databases add automatic time partitioning, retention by dropping old chunks, downsampling, and time-aware functions. TimescaleDB does this as a PostgreSQL extension; ClickHouse and InfluxDB are standalone. The tell that you need one: a Postgres table of events that is growing past hundreds of millions of rows and is only ever read by date_trunc(...) GROUP BY.

Key points

Wide-column: partition key decides the node, clustering key the order; one table per query; linear write scaling.
Graph: traversal in constant time per hop; use when the query is a path of more than two hops.
Search: an inverted index for relevance, facets and fuzzy text; never the source of truth.
Time-series / columnar: read only the columns you aggregate, compress by column, partition and drop by time.

When to use — and when not

Use it when

The workload is one query shape at a scale a general-purpose database handles poorly.

Avoid it when

As the first database. All four are usually fed from a relational system of record.

Failure modes

A Cassandra table designed like a relational one, with queries that need ALLOW FILTERING.
Six-hop relational self-joins where a graph store was needed.
Elasticsearch as the only copy of the data.