Pointer Walk

SQL or NoSQL

A short set of questions about your data that leads to an answer. Most paths end at the boring choice.

DatabasesBeginner8 min read

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:

sql
customers                 orders
id | name                 id | customer_id | item
 1 | Ana                  10 |           1 | book
                          11 |           1 | pen

In a document database it is one self-contained record:

json
{
  "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

KindStoresVery good atGives up
DocumentNested records, each with its own fieldsReading and writing a whole item at onceQueries that cut across items
Key-valueA value under a keyThe fastest possible get and setAny query except "by key"
Wide-columnRows spread over many machines by keyHuge write volumesJoins, and queries you did not plan for
GraphThings and the links between themFollowing links many steps deepSimple 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

SQL or NoSQL?Interactive

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.

Your progress

Check your progress

0%

  1. Read the page50% of this page

    When you have gone through the page and its animations, tick it off.

  2. Take the quiz50% of this page

    7 questions, from easy to hard. Take it as often as you like: the questions change, and only your best score counts.

Saved on this device only. See all your progress