Two families
"SQL or NoSQL?" sounds like a choice between two products. It is really a choice between two different ways of organising data.
SQL databases, also called relational databases, keep data in tables with fixed columns. Each fact is stored once, tables refer to each other by id, and you combine them with joins when you ask a question. PostgreSQL and MySQL are examples.
NoSQL is a loose name for everything else. It covers several quite different kinds of database, each built around one particular shape of data.
The same data, two shapes
A customer, with two orders.
In a relational database it is spread over tables:
customers orders
id | name id | customer_id | item
1 | Ana 10 | 1 | book
11 | 1 | penIn a document database it is one self-contained record:
{
"id": 1,
"name": "Ana",
"orders": [
{ "id": 10, "item": "book" },
{ "id": 11, "item": "pen" }
]
}Example
What each shape makes easy
"Show Ana's page with her orders." The document wins: one read, no join.
"Which customers bought a pen?" The tables win: one query over orders. In the document store, orders are buried inside customers, so you search every customer.
"Rename the item 'pen' to 'biro'." The tables win again if items are stored once. In documents, the word may be copied into thousands of records.
Neither shape is better. Each is fast for the questions it was arranged around and awkward for the others. The relational shape is the more flexible of the two: it does not assume in advance which questions you will ask.
The kinds of NoSQL
| Kind | Stores | Very good at | Gives up |
|---|---|---|---|
| Document | Nested records, each with its own fields | Reading and writing a whole item at once | Queries that cut across items |
| Key-value | A value under a key | The fastest possible get and set | Any query except "by key" |
| Wide-column | Rows spread over many machines by key | Huge write volumes | Joins, and queries you did not plan for |
| Graph | Things and the links between them | Following links many steps deep | Simple bulk reporting |
What you usually trade
Good at
- SQL: any question can be asked later, with joins
- SQL: transactions keep several related changes consistent
- SQL: the database enforces the structure and rules of your data
Where it bites
- SQL: spreading writes over many machines is hard
- SQL: changing the structure of a very large table takes care
- SQL: data that does not fit rows and columns is awkward
Many NoSQL systems turn those lists around: they spread across machines easily and accept any shape, and in exchange they limit joins, limit transactions, and leave more of the checking to your application.
The line has blurred in both directions. SQL databases store and query JSON documents. Several NoSQL databases have added transactions.
Follow the questions
Will you ask questions that connect different kinds of things?
For example: which customers ordered this product, and what else did they buy?
Try a few paths. Most of them end at a relational database, and that is not an accident.
It is also normal to use more than one: a relational database as the source of truth, a key-value store in front as a cache, and a search engine for text search.
Key takeaways
- SQL databases store data in related tables and combine them with joins.
- NoSQL is several different kinds of database: document, key-value, wide-column and graph.
- Each shape makes some questions cheap and others expensive.
- SQL gives flexible queries and transactions. NoSQL systems usually trade some of that for scale or flexibility of shape.
- Unless you have a specific, measured need, a relational database is the safe default.