NoSQL databases

A NoSQL store drops the relational table-and-join model in exchange for one access pattern it can serve across many machines. The name ("Not Only SQL") is a poor one, because the four families grouped under it have less in common with each other than any of them has with Postgres. What unites them is that each picks a data model, a partitioning scheme and a consistency default that let it spread over commodity hardware without a human deciding where each row lives.

That trade only pays at a load one machine cannot carry, so do the back-of-the-envelope arithmetic before choosing. SQL vs NoSQL covers the choice against a relational database; this page covers choosing between the families, and the mechanism underneath them.

The four data models

Key-value stores are a distributed hash map: get and put by key. Lookups are fast and partitioning is trivial, because the key is the partition key. Querying by value is unsupported or a full scan, and there are no relationships. Used for session state, profiles by ID, rate-limit counters, and caching. Redis, Memcached, DynamoDB, Riak KV.

Document stores hold records as JSON or BSON documents with nested fields, queryable on content rather than only on the key, with a flexible schema and a natural mapping to objects in code. Denormalising to avoid joins duplicates data, so one logical update becomes several writes, and joins across collections are slow or absent. Used for content management, product catalogues, entities whose shape varies. MongoDB, Couchbase, ArangoDB.

Wide-column (columnar) stores identify a row by key inside a column family and store the columns of that row together, sorted, so reading a slice of one partition is near-sequential and write throughput is high. The model is dictated by the query rather than the entity, and the partition key is fixed at schema time. Used for time-series, event logs, message history, feeds. Cassandra, Bigtable, HBase.

Graph databases store nodes and edges with properties on both, indexed for traversal, so multi-hop questions ("friends of friends who bought X") stay cheap where the SQL equivalent is a self-join per hop. They are weak on scans and aggregates, and hard to partition: an edge that crosses machines turns a pointer hop into a network round trip. Used for social graphs, recommendations, fraud rings. Neo4j, Neptune, ArangoDB.

How the write path scales

In a Dynamo-style store (DynamoDB, Cassandra, Riak) no single leader takes every write. The request lands on any node, which acts as coordinator. The partition key is hashed to a token, the token maps to a position on a ring, and the N nodes following it hold the replicas. The coordinator writes to all N and returns as soon as W acknowledge.

A quorum write in a leaderless store: N = 3 replicas, W = 2 acknowledgements100%−+FitSVGEsc

Scroll to zoom · drag to pan · 0 fits · Esc closes

With N=3, W=2, R=2 the read and write sets overlap on at least one replica, so a read sees the newest acknowledged write. At W=1, R=1 both return at the speed of the fastest replica with no such guarantee: a user renames their profile, the next read lands on a replica that has not received it, and they watch their own edit disappear. Read-your-writes breaks first, and it breaks visibly. See data consistency for the models these two knobs select between.

The throughput itself comes from the storage engine. A relational primary applies each insert to a B-tree — find the leaf page, split it if full, repeat for every secondary index — which is scattered random I/O. Cassandra and the Bigtable family append to a commit log and an in-memory memtable, then flush sorted files sequentially. Sequential writes, with no single node ordering them, is the whole trick; the cost is deferred to reads, which may reassemble a row from several files, and to background compaction.

Sizing the decision

An activity feed for 50M DAU, each generating 40 events a day:

• 50M × 40 = 2 billion writes/day
• 2,000,000,000 ÷ 86,400 ≈ 23,000 QPS average, and ~70,000 QPS at a 3× peak
• A commodity Postgres box handles roughly 5,000 simple QPS, so 70,000 ÷ 5,000 = 14 shards before any headroom — call it 25 in practice, each with its own failover and backups
• Storage: 2 billion × 1 KB = 2 TB/day, so 180 TB at 90 days retention

That is the case for a store that partitions by default. Change one input and it evaporates: 1 million requests/day is about 12 QPS, a fraction of a percent of one box.

Latency is not the argument. A single-replica read is one datacenter round trip (0.5 ms) plus an SSD random read (100 µs), around 0.6 ms — an indexed Postgres lookup on the same hardware is in the same range. A quorum read of 2 of 3 waits for the second-fastest replica, so its p99 is drawn from the tail of three machines instead of one; see tail latency.

The failure mode: the unbounded partition

A chat service on Cassandra, partition key room_id. This looks right — a room's whole history is one sequential read on one replica set. Then a busy room takes 10,000 messages a day at 1 KB each: 10 MB/day, and after a year ~3.6 GB in a single partition.

Nothing splits it. A partition is the unit of replication, compaction and repair, so that one room drives long compactions, slow repairs and read timeouts while every cluster-level dashboard looks healthy. Adding nodes does not help, because a partition never moves apart. Cassandra guidance keeps partitions near 100 MB and 100,000 rows for exactly this reason.

The fix is a composite partition key, (room_id, day): about 10 MB per partition, spread across the ring, and the common query — the newest page — hits one of them. The cost is fan-out, since reading a month becomes 30 parallel partition reads. Bucket width trades partition size against read fan-out and you pick it from the measured write rate. In Postgres you can add an index next quarter; here the partition key is fixed at schema time, and changing it means rewriting the table.

What you give up

Many of these systems lean on BASE rather than strict ACID: basically available (the system answers), soft state (state changes without input as replicas converge), eventually consistent (replicas agree once writes stop). BASE is a different set of promises, not a diluted ACID — and ACID's C and CAP's C are unrelated, sharing a letter by accident. ACID-C means a committed transaction preserves the invariants you declared; CAP-C means every read sees the latest committed write.

CAP is a partition-time choice. When a partition splits the cluster, a Cassandra ring at W=1 keeps accepting writes on both sides and reconciles later; the same ring at quorum refuses writes on the minority side. PACELC covers the rest of the time: else, with no partition, the choice is latency or consistency, and W=R=1 versus quorum is precisely that dial. Since the dial is per-query here, "Cassandra is AP" is too coarse to be a useful answer.

Two things that were true a decade ago are not now: several of these stores have transactions (MongoDB multi-document, DynamoDB TransactWriteItems) and offer strongly consistent reads on request. What holds is the absence of cross-partition joins and of a free global secondary index. Most systems end up running both engines, and a write that must land in each usually goes through the outbox pattern rather than two independent writes.

In an interview

Know the four types, their data models, pros, cons and typical use cases, and be ready to say why you would pick one over another or over a relational database. What is being tested underneath is whether the choice comes from the access pattern and the load, or from familiarity.

State the query before the product: "the read is always the newest 50 messages in one room by room ID, writes peak near 70,000 QPS, and nothing queries across rooms — so a wide-column store partitioned on (room_id, day) fits. A relational primary would need 14 or more shards for the same write rate, and I would be hand-building what the ring already does."

Three mistakes cost points. Naming a product first ("I would use Mongo") and reverse-engineering the requirements to fit it. Claiming NoSQL "is faster" — per operation it is not, as the 0.6 ms above applies to both; what scales is write throughput by adding nodes rather than by buying a bigger node. And reciting CAP as picking two of three, when what the interviewer wants is what happens to reads and writes on each side of a partition.

Check yourself

1. 20M DAU each write 5 events a day at 2 KB, kept for a year. Does the write rate force a NoSQL store?

20M × 5 = 100M writes/day ÷ 86,400 ≈ 1,200 QPS average, about 3,600 QPS at a 3× peak — under the ~5,000 QPS one commodity Postgres box handles, so throughput alone does not force the move. Storage does: 100M × 2 KB = 200 GB/day, roughly 73 TB a year. The pressure is retention, so the first move is time-partitioning and tiering old data to object storage, not a different engine.

2. A notification service on Cassandra partitions on user_id. One power user receives 2,000 notifications a day at 1 KB, kept two years. What breaks, and what do you change?

2,000 × 1 KB = 2 MB/day, so about 1.5 GB in one partition after two years, far past the ~100 MB working guidance. Compaction and repair treat that partition as a unit, so this user's reads degrade and time out while the cluster looks healthy, and new nodes do not help because a partition never splits. Change the key to (user_id, month): about 60 MB per partition, 24 partitions across the retention window, and the newest page reads one.

3. A profile store runs N=3, W=1, R=1 for lowest latency. A user edits their display name and reloads immediately. What do they see, and what is the cheapest fix?

The read can land on either replica that has not received the write, so the old name comes back on roughly two reloads in three. Cheapest first: pin that session's reads to the node that coordinated the write. Otherwise move to W+R greater than N (W=2, R=2), paying the second-fastest replica on every read instead of the fastest. Raising W to 3 is the wrong knob — it pays the slowest replica on every write to fix a read problem, and loses availability whenever one replica is down.