RDBMS or NoSQL? The cow explains, and tells you when to switch

Every few months someone at work asks the question with the confidence of a person who has just watched a conference talk: “Should we use a relational database or NoSQL?”

On one side, the relational databases (RDBMS), the ones you talk to in SQL: PostgreSQL, MySQL and friends. On the other, the non-relational ones, which go under the name NoSQL: MongoDB, Redis, Cassandra, Neo4j and many more. Let’s see what really separates them, and when it’s worth crossing over.

This is a whole-milk post: there is code, there are acronyms, there is a theorem. Every example in it was actually run, on PostgreSQL 16, MongoDB 8.3, Redis 7.4, Cassandra 5.0 and Neo4j 5.26, and the outputs are the real ones.

A bit of history

In 1970 Edgar F. Codd, at IBM’s research laboratory in San Jose, published “A Relational Model of Data for Large Shared Data Banks”. His idea was that users shouldn’t need to know how data is physically organised inside the machine: data lives in relations, tables made of rows and columns, and you ask questions about it in a high-level language. It worked so well that it earned him the Turing Award in 1981, and it still runs a good part of the world.

The word “NoSQL” has a funnier story. In 1998 Carlo Strozzi used it as the name of his own database, which was, of all things, relational: it just didn’t use SQL. In 2009 Johan Oskarsson, then at Last.fm, revived the term for a meetup about open-source, distributed, non-relational databases, and that time it stuck. Today it’s usually read as “not only SQL”.

Same data, two models

Let’s take something concrete: an order. A customer, some products, quantities, prices. In a relational database you split it into tables, each with its own responsibility, connected by keys:

CREATE TABLE customers (
  id    serial PRIMARY KEY,
  name  text NOT NULL,
  email text UNIQUE NOT NULL
);
CREATE TABLE products (
  id    serial PRIMARY KEY,
  name  text NOT NULL,
  price numeric(10,2) NOT NULL,
  stock int NOT NULL CHECK (stock >= 0)
);
CREATE TABLE orders (
  id          serial PRIMARY KEY,
  customer_id int NOT NULL REFERENCES customers(id),
  created_at  timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE order_lines (
  order_id   int REFERENCES orders(id),
  product_id int REFERENCES products(id),
  qty        int NOT NULL CHECK (qty > 0),
  unit_price numeric(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

The database itself guarantees the rules: an order can’t point to a customer that doesn’t exist, a quantity can’t be zero, stock can’t go negative. To see an order with its total, you put the pieces back together with a JOIN:

SELECT o.id, c.name, sum(l.qty * l.unit_price) AS total
FROM orders o
JOIN customers c   ON c.id = o.customer_id
JOIN order_lines l ON l.order_id = o.id
GROUP BY o.id, c.name;

 id | name | total
----+------+-------
  1 | Pina | 21.00

In a document database like MongoDB, the same order is a single document, shaped like what you’d show on screen:

db.orders.insertOne({
  _id: 1,
  customer: { name: "Pina", email: "pina@example.com" },
  lines: [
    { product: "Hay bale",    qty: 2, unit_price: 4.5 },
    { product: "Milk bucket", qty: 1, unit_price: 12 }
  ],
  created_at: new Date("2025-10-07T09:00:00Z")
})

db.orders.aggregate([
  { $project: {
      customer: "$customer.name",
      total: { $sum: { $map: { input: "$lines", as: "l",
                               in: { $multiply: ["$$l.qty", "$$l.unit_price"] } } } }
  } }
])

[ { _id: 1, customer: 'Pina', total: 21 } ]

One read, everything is there, no JOIN. That’s the strength of the document model. The price shows up the day Pina changes her surname: her name is copied inside every order she has ever placed, so you have to update all of them:

db.orders.updateMany(
  { "customer.email": "pina@example.com" },
  { $set: { "customer.name": "Pina Bianchi" } }
)

In the relational model you’d change one row in customers. In the document model you change every copy, and if you forget one, nobody will tell you.

The four NoSQL families

“NoSQL” isn’t one thing: it’s four very different families, each designed around a different way of reading data.

1. Documents (MongoDB)

You’ve just seen it: JSON-like documents, nested, read and written as a whole. Good when the data has a natural “unit” (an order, a product sheet, a user profile) that you nearly always read in one piece.

2. Key–value (Redis)

The simplest model there is: a key, a value. No queries on the content: you know the key, you get the value, very fast. Redis keeps data in memory and adds useful extras, like automatic expiry and atomic counters:

> SET session:42 "{\"user\":\"pina\"}" EX 1800
OK
> GET session:42
{"user":"pina"}
> TTL session:42
1800
> INCR views:home
1
> INCR views:home
2

Sessions that expire on their own after 30 minutes, view counters, caches: that’s home ground for key–value stores.

3. Wide columns (Cassandra)

Cassandra was developed inside Facebook by Avinash Lakshman and Prashant Malik, to power the search in the Facebook inbox, and was released as open source in 2008. It combines ideas from Amazon’s Dynamo (how to distribute and replicate data across many machines) and Google’s Bigtable (how to store it). You still write something that looks like SQL, but the rules are different. Here are the milk readings for each cow, partitioned by cow and day:

CREATE TABLE farm.milk_readings (
  cow_id text,
  day    date,
  ts     timestamp,
  litres double,
  PRIMARY KEY ((cow_id, day), ts)
) WITH CLUSTERING ORDER BY (ts DESC);

CONSISTENCY QUORUM;
SELECT ts, litres FROM farm.milk_readings
WHERE cow_id = 'ercolina' AND day = '2025-10-07';

 ts                              | litres
---------------------------------+--------
 2025-10-07 18:00:00.000000+0000 |   12.9
 2025-10-07 06:00:00.000000+0000 |   14.2

Now try asking a question the table wasn’t designed for:

SELECT * FROM farm.milk_readings WHERE litres > 13;

InvalidRequest: Error from server: code=2200 [Invalid query]
message="Cannot execute this query as it might involve data filtering and thus
may have unpredictable performance. If you want to execute this query despite
the performance unpredictability, use ALLOW FILTERING"

Cassandra refuses, politely. That’s the central lesson: in a relational database you design the data and then ask any question you like; in Cassandra you design the tables around the questions you’ll ask. In exchange you get writes spread over many nodes and data replicated across data centres.

4. Graphs (Neo4j)

When the interesting part is the relationships themselves, a graph database stores nodes and connections directly. Who are the friends of Ercolina’s friends?

CREATE (e:Cow {name: 'Ercolina'}), (c:Cow {name: 'Carolina'}),
       (b:Cow {name: 'Bianca'}),   (r:Cow {name: 'Rosina'})
CREATE (e)-[:FRIEND_OF]->(c), (c)-[:FRIEND_OF]->(b), (c)-[:FRIEND_OF]->(r);

MATCH (:Cow {name: 'Ercolina'})-[:FRIEND_OF*2]->(fof:Cow)
RETURN fof.name AS friend_of_a_friend ORDER BY friend_of_a_friend;

friend_of_a_friend
"Bianca"
"Rosina"

In SQL you can do it too, with joins or recursive queries, but at the third or fourth hop it gets painful to write and to run. In a graph database it’s one line.

ACID: the promises of a transaction

Relational databases made four promises, which Theo Härder and Andreas Reuter packed into an acronym in 1983:

• Atomicity: a transaction happens entirely or not at all.
• Consistency: it only moves the database between states that respect all the rules.
• Isolation: concurrent transactions behave as if they ran one after the other.
• Durability: once confirmed, the data survives crashes and power cuts.

Here’s atomicity at work. Pina wants five buckets of milk, but there are only two left. The order is inserted, the line is inserted, then the stock update breaks the CHECK rule:

BEGIN;
INSERT INTO orders (customer_id) VALUES (1);
INSERT INTO order_lines VALUES (2, 2, 5, 12.00);      -- 5 milk buckets
UPDATE products SET stock = stock - 5 WHERE id = 2;   -- only 2 in stock
ERROR:  new row for relation "products" violates check constraint "products_stock_check"
COMMIT;
ROLLBACK

SELECT count(*) AS orders FROM orders;
 orders
--------
      1

The whole transaction is cancelled, including the order that had already been inserted. There’s still only one order in the database, and no half-finished one to clean up by hand.

NoSQL doesn’t automatically mean “no transactions”: MongoDB, for example, added multi-document ACID transactions in version 4.0 in 2018 (first on replica sets, then on sharded clusters in 4.2). But how strong the guarantees are, and at what cost, varies a lot from one product to another. Read the documentation of your database, not the one you remember.

CAP: pick two? Not quite

As soon as data lives on several machines, a theorem comes into play. Eric Brewer presented it as a conjecture in 2000, and Seth Gilbert and Nancy Lynch proved it in 2002: a distributed system can’t guarantee all three of Consistency (everyone sees the same data), Availability (every request gets an answer) and Partition tolerance (it keeps working when the network splits the nodes into groups that can’t talk to each other).

It’s often drawn as a triangle where you “pick two”. In 2012 Brewer himself explained that this is misleading: network partitions will happen whether you like it or not, so the real choice only arises during a partition, and it’s between consistency and availability. When the network is fine, you can have both. Daniel Abadi’s PACELC (2010) adds the other half: even without partitions, you trade consistency against latency.

That’s why Cassandra lets you choose the consistency level for every operation, like the CONSISTENCY QUORUM above: a majority of replicas must answer, which costs a little more time but gives you more certainty.

Eventual consistency

Many distributed systems choose availability, and accept being eventually consistent: if nobody updates a piece of data any more, sooner or later every read will return the latest value. Sooner or later. Meanwhile, two people can see two different versions. The opposite of ACID even has its own acronym, BASE: basically available, soft state, eventually consistent.

The most famous example comes from Amazon’s Dynamo paper (2007), and it’s the shopping cart. Customers must be able to add items to the cart “even if disks are failing, network routes are flapping, or data centers are being destroyed by tornados”. So when different versions of a cart exist, Dynamo merges them. The paper is honest about the consequence: “an ‘add to cart’ operation is never lost. However, deleted items can resurface.”

For a shopping cart, an item that comes back from the dead is a small annoyance, and much better than a cart that refuses to work. For other data it would be a disaster.

So, when should you use NoSQL?

Here’s the cow’s practical guide.

Start relational when you don’t have a specific reason to do otherwise. PostgreSQL, MySQL and friends handle relationships, constraints, transactions and ad-hoc questions you haven’t thought of yet. And if you need flexible fields, PostgreSQL has had jsonb, a column type for JSON documents, since version 9.4 in 2014.

Reach for NoSQL when a specific way of using the data calls for it:

• Key–value (Redis): sessions, caches, counters, anything you read by key, fast, and that can expire.
• Documents (MongoDB): data with a natural unit that you read and write whole, with few relationships between units and a structure that varies from one item to the next.
• Wide columns (Cassandra): huge volumes of writes, distributed over many nodes or data centres, read through queries you know in advance (time series, events, logs per device).
• Graphs (Neo4j): when the question is about the connections themselves, many hops deep (social networks, recommendations, fraud rings, dependencies).

And very often the answer is “both”: a relational database for orders and money, a key–value store next to it for sessions and cache.

Moo. 🐄