Lesson 20 · At scale

NoSQL databases

NoSQL is a loose name for databases that are not built on relational tables and SQL: key-value, document, wide-column and graph stores. Follow one shop order stored two ways, as PostgreSQL tables joined back together and as one JSON document read by its key, and see what the document model buys (locality, easy spreading over nodes, flexible fields) and what it costs (searching inside the documents).

12 min10 steps5 questionsAdvanced

Fig 20 · Interactive

Loading figure…

or ← →

Check yourself

5 quick questions

Pick an answer to see why. Your score saves with your progress.

01Which of these is NOT one of the four usual NoSQL families?

The four usual families are key-value, document, wide-column (column-family) and graph stores. A spreadsheet is not a database family at all.

02In PostgreSQL, order 42 is one orders row and two order_items rows. What does the app send to read the whole order back?

The order is split over two tables, so reading it means joining them again. The JOIN returns 2 rows, one per item, each carrying the customer's name.

03A document store hashes the key to pick a node, and order:42 lands on node A. How many nodes does get("order:42") need to ask?

The same key always hashes to the same node, so the app (or the store's router) goes straight to it, and the whole document comes back in one read.

04Which question is the slowest for a document store with no secondary index?

The key says nothing about what is inside a document, so every node must open every document and look. An index on items.item fixes this, at the cost of updating the index on every write.

05What does PostgreSQL's @> operator do on a jsonb column?

body @> '{"items":[{"item":"mug"}]}' is true for every order whose items list contains a mug. A GIN index (jsonb_path_ops) can answer it without opening every row.

Key terms

Glossary

NoSQL: A loose name, often read as “not only SQL”, for databases that are not built on relational tables and SQL.
Key-value store: A store that keeps a value under a key and offers get and put by key; it treats the value as opaque bytes.
Document store: A store that keeps each record as one document, such as a JSON object, under a key, and can look inside it.
JSON: JavaScript Object Notation: text made of {objects}, [lists], strings, numbers, true, false and null.
Node: One machine in a database that is spread over many machines.
Hash partitioning: Placing each record on a node chosen by a hash of its key, so the same key always lands on the same node.
Secondary index: An index on a field other than the key, so a search on that field does not have to open every record.
jsonb: PostgreSQL's binary JSON column type; it supports containment (@>) and GIN indexes.

Fine print

Notes and sources

“NoSQL” is a label, not a standard: it covers very different systems, and several of them now offer SQL-like query languages (Cassandra has CQL).
The figure adds up the key’s character codes so you can check the placement by hand. Real stores use a strong hash: Cassandra’s default partitioner uses Murmur3, and DynamoDB feeds the partition key to an internal hash function.
Many document stores can change part of a document in place: MongoDB’s $push operator appends to a list without the app sending the whole document. A plain key-value store replaces the whole value.
Schemaless is optional: MongoDB can validate documents against a JSON Schema, and PostgreSQL can add CHECK constraints on a jsonb column.
Document stores are weak at joins, not always without them: MongoDB has a $lookup stage. Joining across many nodes is still slow, because the data must travel between machines.
The Python example keeps JSON texts in a dict, the simplest key-value store. Run it and it prints ['order:42'].

Quiz Unlocks after the last step.