Two ways to store an order
NoSQL, often read as “not only SQL”, is a loose name for databases not built on relational tables. The four main families are key-value, document, wide-column and graph stores. We will store one shop order two ways.
In PostgreSQL, order 42 is split over two tables. orders holds one row: who bought it. order_items holds one row per item, a mug and a pen, and each points back with order_id 42.
To read the order, the app sends a JOIN. PostgreSQL pairs each order_items row whose order_id is 42 with the orders row for 42, and returns 2 rows, one per item.
A document store keeps the same order as one document: JSON text with the items nested inside. It is saved under one key, order:42. Nothing is split, so there is nothing to join.
Documents at work
A document lives whole under its key, so the store can spread documents over many machines, called nodes. A hash of the key picks the node: order:42 goes to node A, orders 41 and 43 to node B.
To read the order, the app asks for key order:42. Only node A can hold it, and it sends back the whole document in one read. A key-value store works the same way, but never looks inside the value.
Ana adds a tea. The app adds {"item":"tea","qty":1} to the items list and saves the document under the same key. In the tables, the same change is one new order_items row.
No table says which fields an order must have. Ana marks this order as a gift, so it gains a gift field the others lack. In PostgreSQL, a new column first needs ALTER TABLE.
The trade-off
Now ask: which orders contain a mug? The key cannot help. Without an index on that field, every node must open every document and look inside. Here 1 of 3 orders match: order:42.
PostgreSQL can store the same documents in a jsonb column, find every order containing a mug with the @> operator, and speed that up with a GIN index. So choose by how the data is read, not by fashion.
What happens
;; step 1/10 · nosql
NoSQL: databases not built on relational tables
key-value a value stored under a key; get and put by key
e.g. Redis; DynamoDB (also document)
document a JSON-like document under a key; the store can look inside
e.g. MongoDB, Couchbase
wide-column rows of many columns, grouped by a partition key
e.g. Apache Cassandra, HBase
graph nodes joined by relationships, for following links
e.g. Neo4j
the example: order 42 by Ana
in PostgreSQL tables (left)
in a document store (right, 2 nodes)
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. 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.