How to choose a database

In short: Choose a database by your data and the questions you ask of it: a relational database holds the truth, and another kind is added next to it only where it is weak.

Problem

Many kinds of databases on the table

The module showed key-value, documents, event streams, graphs, columns and time series, text search, vectors and clusters. Each of these databases is excellent at something. The shop's new project asks: which one do we choose?

The wrong way is to start from the database ("we use MongoDB because it is modern"). The right way is to start from the data and the questions you will ask about it, and to add a new database only when there is a question the main one does not answer well.

The idea

Five questions

1. What shape is the data? Rows with the same columns, whole documents with nested parts, connections between things, records over time, free text.
2. What do you ask? One thing by key, a report over everything, "who is connected to whom", "which words", "what is similar".
3. How important is consistency? Money and stock need transactions and reading the latest write. A like counter can lag by a second.
4. How big are the data and the load? Thousands or billions of rows, ten or a hundred thousand queries a second. Most projects are much smaller than they think.
5. Who will look after it? Every database needs copies, monitoring, upgrades and someone who knows what to do when it goes down.

The question	Kind of database
Money, orders, stock, everything that has to be right	relational (PostgreSQL, MySQL)
The same thing by key thousands of times, sessions, carts	key-value (Redis)
Whole objects with different structures	document (MongoDB) or JSONB
Many systems learn about the same event	event stream (Kafka)
Recommendations, networks, fraud across several steps	graph (Neo4j)
Reports over millions of rows, sensors, metrics	columnar, time series (ClickHouse, TimescaleDB)
A search box, words in text	full-text search (Elasticsearch)
"Similar in meaning", a chatbot over documents	vector (pgvector, Qdrant)

The idea

Start with PostgreSQL

For most new projects the sensible start is one relational database. PostgreSQL alone covers a large part of the module: JSONB columns for documents, tsvector for text search, pgvector for vectors, TimescaleDB for time series, read replicas and partitioning tables by time. It is not the best at each of them, but it is good enough, and one database is much easier to look after than five.

A separate database pays off when there is a concrete question the main one answers slowly or with difficulty, and you have measured it: the cache, because the same query runs 2000 times a minute; the search engine, because search is the main job of the site; the data warehouse, because the reports slow down the till.

More precisely, if you are curious

This is called polyglot persistence: different databases for different parts of the system. It works well when one of them is clearly the source of truth and the others are copies that can be rebuilt. It works badly when two databases hold different parts of the same truth and nobody knows which one is right.

See it

The shop when it grows

This is what the shop might look like after a few years, with each part added when it was needed:

• PostgreSQL keeps the customers, the products, the orders and the payments. This is the truth. It has one synchronous and one asynchronous replica.
• Redis keeps the catalogue cache, the sessions and the carts.
• Kafka carries the events "order placed", "paid", "shipped" to the warehouse, the invoices and the e-mail.
• Elasticsearch holds a copy of the products and reviews for the search box. It is filled from the events.
• ClickHouse is the data warehouse for the reports. It is filled every night or from the stream.
• pgvector in the same PostgreSQL gives "similar products".

All the copies come from PostgreSQL. If the index in Elasticsearch breaks, it is rebuilt from the database. The reverse is not true.

Problem

The two writes

The order is written to PostgreSQL, and then the program sends an event to Kafka. If the program crashes between the two, the order is in the database, but the event is missing: the warehouse never packs the parcel. If it is the other way round (first the event, then the database), the warehouse may pack a parcel for an order that later failed.

The two systems have no common transaction. The solution is an outbox table: the event is written to a table in the same database, in the same transaction as the order. Either both are written, or neither.

SQLdatabase: shop

BEGIN;
INSERT INTO orders (id, customer_id, ordered_at, status) VALUES (61, 7, '2026-10-25', 'new');
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (61, 9, 2, 3.2);
INSERT INTO outbox (topic, key, payload)
VALUES ('orders', 'order-61', '{"order":61,"event":"placed","total":6.40}');
COMMIT;

SELECT id, topic, key, payload, published_at FROM outbox;

BEGIN; INSERT INTO orders (id, customer_id, ordered_at, status) VALUES (61, 7, '2026-10-25', 'new'); INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (61, 9, 2, 3.2); INSERT INTO outbox (topic, key, payload) VALUES ('orders', 'order-61', '{"order":61,"event":"placed","total":6.40}'); COMMIT; SELECT id, topic, key, payload, published_at FROM outbox;

▶ RunRestore the queryFresh database

published_at is NULL: the event has not been sent yet. A separate program (a relay) reads the unsent ones in order, sends them to the stream and only then marks them:

SQLdatabase: shop

-- The relay program: takes what has not been sent, in order
SELECT id, topic, key, payload FROM outbox WHERE published_at IS NULL ORDER BY id;
-- … sends it to the stream and only then marks it
UPDATE outbox SET published_at = '2026-10-25 10:00:02' WHERE id = 1;
SELECT id, published_at FROM outbox;

-- The relay program: takes what has not been sent, in order SELECT id, topic, key, payload FROM outbox WHERE published_at IS NULL ORDER BY id; -- … sends it to the stream and only then marks it UPDATE outbox SET published_at = '2026-10-25 10:00:02' WHERE id = 1; SELECT id, published_at FROM outbox;

▶ RunRestore the queryFresh database

And if something fails before COMMIT, there is neither a change nor an event:

SQLdatabase: shop

BEGIN;
UPDATE orders SET status = 'cancelled' WHERE id = 61;
INSERT INTO outbox (topic, key, payload) VALUES ('orders', 'order-61', '{"order":61,"event":"cancelled"}');
-- Something fails before COMMIT
ROLLBACK;

SELECT (SELECT status FROM orders WHERE id = 61) AS status,
       (SELECT COUNT(*) FROM outbox) AS events;

BEGIN; UPDATE orders SET status = 'cancelled' WHERE id = 61; INSERT INTO outbox (topic, key, payload) VALUES ('orders', 'order-61', '{"order":61,"event":"cancelled"}'); -- Something fails before COMMIT ROLLBACK; SELECT (SELECT status FROM orders WHERE id = 61) AS status, (SELECT COUNT(*) FROM outbox) AS events;

▶ RunRestore the queryFresh database

More precisely, if you are curious

The relay can crash after it has sent the event but before it marks it. Then on restart it sends it again. That is why the consumers of the event have to be idempotent, like the invoices in the lesson on streams. Instead of a program that reads the table, Debezium is often used: it reads PostgreSQL's change log (the WAL) and sends every new row of the outbox to Kafka. This is called change data capture (CDC).

Try it

Cancelling an order

The customer cancels order 42. In one transaction change its status to cancelled and write an event to the outbox for the topic orders with the key order-42 and the content {"order":42,"event":"cancelled"}.

SQLdatabase: shop

SELECT id, status FROM orders WHERE id = 42;

SELECT id, status FROM orders WHERE id = 42;

Check▶ Run

Exercise

Five projects

For each project: which database is the foundation, or what do you add next to it?

1. A bank keeps accounts and the transfers between them. The sum of all accounts must never be off. What is the foundation?

A key-value database, to be fastA relational database with transactions and constraintsA document database, to be flexible

2. A delivery app shows where the courier is. Every courier sends their coordinates every 5 seconds, and the chart shows the last hour. What fits this data best?

Time series (or a columnar database)A graph databaseFull-text search

3. A classifieds site wants "similar ads" by text and photos, not by equal words. What do you add next to the main database?

An index on the titleVectors (pgvector or a vector database)Another replica

4. A learning platform keeps courses with different structures: chapters, lessons, videos, tests, all nested and different for each course. A whole course is read and written at once. What fits most naturally?

A columnar databaseA document database (or a JSONB column in PostgreSQL)A key-value database

5. A shop with 300 orders a day, one programmer and PostgreSQL. The boss has read that the big ones use sharding. What do you do?

Move to a graph databaseSharding right awayNone of that: indexes, then a read replica if it is ever needed

Check

Test

Check yourself

1. The program writes the order to PostgreSQL and then sends an event to Kafka. What happens if it crashes between the two?

The database rolls the order backNothing, Kafka will find outThe order is in the database, but the event is missing: the warehouse never finds out

2. Which is true about every new database in a project?

It brings work: copies, monitoring, upgrades, people who know it, and data that has to be kept in stepIt is free if it is open sourceIt makes the others faster

3. The cache in Redis, the index in Elasticsearch and the data warehouse have a copy of the orders. Where is the truth?

Where it is newerIn all of them equallyIn the main database. The copies are filled from it and can be rebuilt

Check

Start the lesson over

Related lessons

• Builds on: Columnar and time series databases · Searching text · Searching by meaning · Replication, sharding and CAP
• Tasks on this topic: Kinds of databases