Deep Thought

NoSQL_Overview

6 min read

SQL vs. NoSQL

In Transactions_CAP_Scaling we saw: once a database is spread over many machines, ACID and JOINs get expensive, and we have to choose between consistency and availability. NoSQL databases are the answer to that problem. This scriptum answers three questions:

  1. Why were NoSQL databases invented?
  2. Which kinds of NoSQL databases exist?
  3. How does the same data look in each of them?

1. Why NoSQL?

Around 2005–2010 companies like Google, Amazon and Facebook had problems no single server could handle: billions of users, petabytes of data, millions of writes per second.

Three ideas came out of that:

IdeaMeaning
Scale outMany cheap machines instead of one huge one.
AggregatesStore data that is used together together (one order incl. its items), so a request touches only one machine — no JOIN across machines.
Flexible schemaData can be stored without first defining a table structure.

NoSQL = “Not only SQL”. It does not mean “SQL is bad”. It means: the relational model is one option, not the only one.

2. SQL vs. NoSQL at a glance

SQL (relational)NoSQL
Data modeltables with rows and columnsdocuments, key-value pairs, graphs, wide columns
Schemafixed, defined before inserting (schema on write)flexible, often checked by the application (schema on read)
Relationshipsforeign keys + JOINsembedded, duplicated, or (graph) stored directly
Query languageSQL — one standard for alldifferent per product (MongoDB queries, Cypher, CQL, …)
TransactionsACIDoften BASE, ACID only within one aggregate
Scalingmostly verticalbuilt for horizontal
Typical systemsMySQL, PostgreSQL, SQL Server, OracleMongoDB, Valkey/Redis, Neo4j, Cassandra

It is not black and white any more: PostgreSQL stores JSON documents, MongoDB has ACID transactions, and distributed SQL databases (CockroachDB, Google Spanner) scale out while keeping SQL. The table shows the typical behaviour.

3. The four NoSQL families

3.1 Document stores

  • Data = documents (JSON/BSON), grouped in collections.
  • A document can contain nested objects and arrays → a tree (hierarchy).
  • Documents in one collection may have different fields.
  • You can query inside documents ({ "customer.city": "Krems" }).
  • Examples: MongoDB, CouchDB, Firestore

3.2 Key-value stores

  • The simplest model: a giant hash table. Every value is found by its key.
  • The database does not care what is inside the value.
  • Extremely fast (often completely in RAM).
  • Examples: Valkey, Redis, Amazon DynamoDB, etcd

3.3 Graph databases

  • Data = nodes (things) and relationships (connections between things). Both can have properties.
  • Relationships are stored, not calculated by a JOIN → following them is very fast.
  • Ideal when the connections are the question: friends of friends, recommendations, routes, fraud rings.
  • Examples: Neo4j, Memgraph, Amazon Neptune

3.4 Column-oriented databases

“Column-oriented” means two different things. Don’t mix them up:

Wide-column store (NoSQL)Columnar database (analytics)
Idearows identified by a key, each row may have different, very many columnsa normal table, but stored column by column on disk
Good forhuge write volume, data always read by its key (sensor data, logs, messages)aggregations over millions of rows (SUM, AVG of one column)
QueryCQL (looks like SQL, but no JOINs)normal SQL
ExamplesCassandra, ScyllaDB, HBase, Google BigtableClickHouse, DuckDB, Amazon Redshift, Parquet files

Why store by column? A query like SELECT AVG(price) FROM sales only needs one column. A row store reads every complete row from disk; a column store reads only the price values — often 10–100× less data, and similar values compress very well.

Row store: [1, Anna, Krems, 40] 
           [2, Ben, Tulln, 25] 
           [3, Clara, Krems, 80]
Column store: id: [1, 2, 3]   
              name: [Anna, Ben, Clara]
              city: [Krems, Tulln, Krems]   
              price: [40, 25, 80]

4. One order — five shapes

The same business fact, stored in each model:

What you should notice:

  • Relational splits the order into three tables. Flexible for any question, but every answer needs JOINs.
  • Document keeps the whole order in one piece. Perfect for “show order 1001”, worse for “which customers bought a Mouse?“.
  • Key-value only knows order:1001. Fastest possible lookup — but you cannot ask anything else.
  • Graph stores the connections themselves. Great for “what else did people who bought a Keyboard buy?“.
  • Wide-column puts all of Anna’s orders into one partition. Great for “all orders of Anna, newest first”, millions of times per second.

There is no best model. You choose the model that fits the questions you will ask most often.

5. Polyglot persistence

Real applications often use several databases at once, each for what it is good at:

Webshop
 ├─ MySQL      → customers, orders, stock      (ACID needed)
 ├─ Valkey     → sessions, shopping carts      (fast, may expire)
 ├─ Neo4j      → "customers also bought …"     (relationships)
 └─ ClickHouse → sales statistics              (analytics)

The price: more systems to run, and data has to be kept in sync between them.

6. Summary

  • NoSQL databases were built for horizontal scaling, flexible schemas and aggregates.
  • Four families: document, key-value, graph, column-oriented (wide-column).
  • “Column-oriented” also names columnar analytics databases — normal SQL, stored by column.
  • The same data can be modelled in every family — the questions you ask decide which one fits.
  • Many systems combine several databases (polyglot persistence).

7. Self-check questions

  1. What does “NoSQL” stand for, and what does it not mean?
  2. Explain schema on write vs. schema on read.
  3. Why do NoSQL systems like to store “aggregates”? What does this have to do with scaling out?
  4. Name the four NoSQL families with one product each.
  5. A key-value store holds cart:anna → {…}. Why can’t it answer “Which carts contain a Mouse?”
  6. What is the difference between a wide-column store and a columnar database?
  7. Why is SELECT AVG(price) FROM sales faster in a columnar database?
  8. Which family would you pick for: (a) a friend-recommendation feature, (b) storing user sessions, (c) a product catalogue where every product category has different attributes?
  9. What is polyglot persistence? Name one advantage and one disadvantage.

Short answers

  1. “Not only SQL”. It does not mean “no SQL at all” or “SQL is outdated”.
  2. Schema on write: structure is defined and checked before data is stored (CREATE TABLE). Schema on read: data is stored as it comes; the reading application interprets it.
  3. An aggregate is read and written as one unit, so it can live on one node. Requests then never need a JOIN or a transaction across several machines.
  4. Document – MongoDB, key-value – Valkey, graph – Neo4j, wide-column – Cassandra.
  5. The value is a black box for the store. It can only look things up by key; it would have to read every cart.
  6. Wide-column: NoSQL, rows by key with flexible columns, no JOINs (Cassandra). Columnar: relational table stored column by column for analytics, normal SQL (ClickHouse).
  7. Only the price column is read from disk instead of every complete row, and a column of similar values compresses well.
  8. (a) graph, (b) key-value, (c) document.
  9. Using several databases, each for what it does best. + every part gets the best tool. – more operations work, data must be synchronised.

Graph View

Backlinks