Relational and Non-Relational Databases: The Definitive Guide

"Should I use SQL or NoSQL?" is the wrong question. The right one is: what shape does my data have, and what guarantees do I need about it? Answer that and the choice of database shows up on its own.

This guide puts the two worlds side by side — same application, different modelling — with the commands for each.

Contents

• 1 · What a relational database is
• 2 · What a non-relational database is
• 3 · The five families of NoSQL
• 4 · The same system modelled both ways
• 5 · ACID, BASE and the CAP theorem
• 6 · How you query in each one
• 7 · Scale: vertical, horizontal and sharding
• 8 · When to use each one
• 9 · Common mistakes on both sides
• 10 · Polyglot persistence

1. What a relational database is

A relational database keeps data in tables — rows and columns with defined types — and the relationships between them are declared and enforced by the database itself.

Three ideas hold everything up:

1. A defined schema: you declare up front which columns exist and what type they are. The database refuses whatever does not fit.
2. Normalisation: each fact is stored exactly once. The customer's address lives in the customers table, not repeated on every order.
3. Referential integrity: a foreign key prevents an order existing for a customer who does not.

CREATE TABLE clientes (
  id BIGSERIAL PRIMARY KEY,
  nome VARCHAR(120) NOT NULL,
  email VARCHAR(200) UNIQUE NOT NULL
);

CREATE TABLE pedidos (
  id BIGSERIAL PRIMARY KEY,
  cliente_id BIGINT NOT NULL REFERENCES clientes (id),
  total DECIMAL(12,2) NOT NULL CHECK (total >= 0),
  criado_em TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Notice what that snippet already guarantees, without a single line of application code: the email cannot repeat, the total is never negative, and no order points at a customer who does not exist. That is the value proposition of the relational model: the rule lives in the data, not in whoever writes to it.

The main names: PostgreSQL, MySQL, SQL Server, Oracle, Firebird, SQLite, MariaDB, DB2.

2. What a non-relational database is

"NoSQL" never meant "no SQL" — these days it reads as Not Only SQL. It is an umbrella for databases that gave up part of the relational model to gain something else: horizontal scale, schema flexibility, or performance on one specific access pattern.

What usually changes:

• Flexible schema: each document can have different fields. Good for heterogeneous data, bad for consistency without discipline.
• Deliberate denormalisation: data that is read together is stored together. One read, no JOIN.
• Integrity in the application: the database does not guarantee that cliente_id exists — your code does.

// MongoDB: the order carries what it needs along with it
db.pedidos.insertOne({
  _id: ObjectId(),
  cliente: { id: 42, nome: "Ana Souza", email: "ana@email.com" },
  itens: [
    { produto: "Teclado", qtd: 1, preco: 199.9 },
    { produto: "Mouse",   qtd: 2, preco:  89.9 }
  ],
  total: 379.7,
  criadoEm: new Date()
});

One read brings back the whole order, ready for the screen. The price of that: if Ana changes her email, this document still holds the old one — and you have to decide whether that is a bug or exactly what you wanted (a historical snapshot of the order).

3. The five families of NoSQL

Treating "NoSQL" as a single category is the most common mistake. They are families with very different purposes.

Document

db.produtos.find({ categoria: "games", preco: { $lt: 500 } })
          .sort({ preco: -1 })
          .limit(10);

Key-value

SET sessao:abc123 '{"userId":42}' EX 3600   # expires in 1 hour
GET sessao:abc123
INCR contador:visitas:2026-08-10

Columnar

-- Cassandra (CQL): the modelling comes from the query, not from the domain
CREATE TABLE eventos_por_usuario (
  usuario_id UUID,
  quando TIMESTAMP,
  tipo TEXT,
  PRIMARY KEY (usuario_id, quando)
) WITH CLUSTERING ORDER BY (quando DESC);

Graph

// Neo4j (Cypher): "friends of friends who like what I like"
MATCH (eu:Pessoa {id: 42})-[:AMIGO*2]-(sugestao:Pessoa)
WHERE NOT (eu)-[:AMIGO]-(sugestao)
RETURN sugestao.nome, COUNT(*) AS forca
ORDER BY forca DESC LIMIT 10;

This is the case where NoSQL wins beyond argument: the same question in SQL would need recursive JOINs and would get much slower as the depth grows.

Vector

-- pgvector: semantic search inside PostgreSQL itself
SELECT titulo, embedding <-> $1 AS distancia
FROM documentos ORDER BY distancia LIMIT 5;

4. The same system modelled both ways

A blog with posts, authors and comments.

Relational — three tables, each fact once:

CREATE TABLE autores (
  id BIGSERIAL PRIMARY KEY,
  nome VARCHAR(120) NOT NULL,
  email VARCHAR(200) UNIQUE NOT NULL
);

CREATE TABLE posts (
  id BIGSERIAL PRIMARY KEY,
  autor_id BIGINT NOT NULL REFERENCES autores (id),
  titulo VARCHAR(200) NOT NULL,
  corpo TEXT NOT NULL,
  publicado_em TIMESTAMP
);

CREATE TABLE comentarios (
  id BIGSERIAL PRIMARY KEY,
  post_id BIGINT NOT NULL REFERENCES posts (id) ON DELETE CASCADE,
  autor_nome VARCHAR(120) NOT NULL,
  texto TEXT NOT NULL,
  criado_em TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- The post screen: one JOIN
SELECT p.titulo, p.corpo, a.nome AS autor
FROM posts p JOIN autores a ON a.id = p.autor_id
WHERE p.id = 1;

Document — the post carries what the screen needs:

db.posts.insertOne({
  _id: 1,
  titulo: "Bancos relacionais e não relacionais",
  corpo: "...",
  autor: { id: 7, nome: "Jhonatan Pinheiro" },   // denormalised
  comentarios: [                                  // nested
    { autorNome: "Ana", texto: "Ótimo post!", criadoEm: new Date() }
  ],
  publicadoEm: new Date()
});

// The post screen: one read, no JOIN
db.posts.findOne({ _id: 1 });

The trade-off shows up when something changes: renaming the author is an UPDATE on one row in the relational model, and an updateMany across all of their documents in NoSQL. Reading the page, on the other hand, is a single lookup on the document — and a JOIN in the relational one.

Nesting comments inside the post works well right up until the viral post with 50 thousand comments. A document has a size limit (16 MB on MongoDB) and grows with every write. A separate collection becomes the answer again.

5. ACID, BASE and the CAP theorem

ACID is the contract of relational databases:

• Atomicity — all or nothing.
• Consistency — constraints always valid.
• Isolation — concurrent transactions do not get in each other's way.
• Durability — once committed, it is written.

BEGIN;
UPDATE contas SET saldo = saldo - 100 WHERE id = 1;
UPDATE contas SET saldo = saldo + 100 WHERE id = 2;
COMMIT;   -- or nothing happens

BASE is the stance of much of distributed NoSQL: Basically Available, Soft state, Eventually consistent. The system accepts being temporarily inconsistent across replicas in exchange for availability and scale.

The CAP theorem explains why: a distributed system subject to a network partition has to choose between consistency and availability. It is not a philosophical choice — it is what happens when the cable between two data centres goes down.

Two common myths are worth correcting: NoSQL is not a synonym for "no transactions" (MongoDB has had multi-document transactions since 4.0) and relational is not a synonym for "does not scale" (there is PostgreSQL running dozens of terabytes in production).

6. How you query in each one

The same question — "the 10 most expensive games products under 500" — in each language:

-- SQL
SELECT nome, preco FROM produtos
WHERE categoria = 'games' AND preco < 500
ORDER BY preco DESC LIMIT 10;

// MongoDB
db.produtos.find(
  { categoria: "games", preco: { $lt: 500 } },
  { nome: 1, preco: 1 }
).sort({ preco: -1 }).limit(10);

-- Cassandra (CQL): it only filters by what is in the key; the rest needs another table
SELECT nome, preco FROM produtos_por_categoria
WHERE categoria = 'games' LIMIT 10;

// Neo4j
MATCH (p:Produto {categoria: 'games'})
WHERE p.preco < 500
RETURN p.nome, p.preco ORDER BY p.preco DESC LIMIT 10;

And the aggregation — revenue by month:

-- SQL
SELECT DATE_TRUNC('month', criado_em) AS mes, SUM(total) AS faturamento
FROM pedidos GROUP BY 1 ORDER BY 1;

// MongoDB — aggregation pipeline
db.pedidos.aggregate([
  { $group: {
      _id: { $dateTrunc: { date: "$criadoEm", unit: "month" } },
      faturamento: { $sum: "$total" }
  } },
  { $sort: { _id: 1 } }
]);

The underlying difference: SQL is declarative and standardised — you learn it once and take it to any relational database. Each NoSQL has its own language, and switching databases usually means rewriting the data layer.

7. Scale: vertical, horizontal and sharding

• Vertical (grow the machine): simple, gets you much further than people say, and one day runs out.
• Horizontal (add machines): scales indefinitely, and brings distributed consistency, rebalancing and more complex operations.

Relational databases scale reads naturally through replicas:

-- PostgreSQL: the read replica takes the reports
SELECT pg_is_in_recovery();   -- true = it is a replica

Writes are the hard part: only one primary accepts writes. The ways out are partitioning, sharding in the application, or extensions such as Citus.

-- Native partitioning: one partition per month
CREATE TABLE eventos (id BIGSERIAL, criado_em DATE NOT NULL, dados JSONB)
PARTITION BY RANGE (criado_em);

CREATE TABLE eventos_2026_08 PARTITION OF eventos
FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

In distributed databases, sharding is a design premise:

// MongoDB: the shard key defines the distribution — and is nearly impossible to change later
sh.shardCollection("loja.pedidos", { clienteId: "hashed" });

Choosing the shard key is the most expensive decision in a distributed database. A bad key creates a hot partition: one node with 90% of the traffic and the rest of the cluster idle.

8. When to use each one

Use relational when (and this is the default for most systems):

• The data has relationships and you will query it in ways you cannot yet imagine.
• There is money, stock, enrolment or anything else that cannot diverge.
• You need ad hoc reporting and the data team speaks SQL.
• Strong consistency is a requirement, not a wish.

Use document when:

• The shape legitimately varies per record (a catalogue with different attributes per category).
• The aggregate is read and written whole, almost always by the same key.
• The schema changes very often in the early life of the product.

Use key-value when:

• You need millisecond latency per key: cache, session, rate limit, queue, leaderboard.

Use columnar when:

• Writes are massive and continuous, queried by time range: telemetry, IoT, logs, events.

Use graph when:

• The question is about the path between entities: social network, recommendation, fraud, dependencies.

Use vector when:

• Search is by meaning, not by equality: RAG, semantic similarity, content-based recommendation.

A practical summary:

9. Common mistakes on both sides

On the NoSQL side:

1. Choosing by fashion. "It is more modern" is not a technical requirement. The question is which problem in your system the relational model fails to solve.
2. Thinking "schemaless" means "no modelling". The schema did not disappear: it left the database and went into the code, scattered across several places — often inconsistently.
3. Using documents for highly relational data. If you are simulating a JOIN in the application, the wrong database was chosen.
4. Ignoring the access key. On Cassandra and DynamoDB, whoever does not model from the query finds out far too late that the query they need is impossible.
5. Trusting eventual consistency without understanding the effect. "I wrote it and it did not show up on screen" becomes a support ticket every day.

On the relational side:

1. Normalising to the point of paralysis. Seven JOINs to build a simple screen is a sign that a materialised view — or a little deliberate denormalisation — was missing.
2. Storing everything in JSONB and calling it flexible. If you filter by that field in every report, it deserves to be a column with an index.
3. Not indexing a foreign key. The database creates an index on the PK, not always on the FK — and the JOIN pays the price.
4. Using the database as a queue. It works up to a certain volume; after that, locks and bloat show up. There are dedicated tools for that.
5. Assuming it does not scale without trying. Good modelling, the right index and one replica get you further than most projects will ever need.

10. Polyglot persistence

Mature systems rarely use a single database. A common e-commerce architecture:

The cost of that design is real: more pieces to operate, monitor, version and keep in sync. Start with one well-modelled relational database and add a specialised piece when a concrete bottleneck appears — never in anticipation.

Conclusion

Relational and non-relational do not compete: they solve different problems.

• The relational model remains the right default for most systems. It errs on the side of correctness: if the data does not add up, it refuses.
• NoSQL is the right answer when the access pattern is known and specific, or when horizontal scale is a real requirement.
• Modern PostgreSQL does JSONB, text search, vectors and time series. Before bringing a new database into the architecture, it is worth checking whether the one you already run solves it.

The final question is never "which is the best database". It is "which guarantees do I need, and what price am I willing to pay for them".