Types of databases

On the way to the index, QUERY pauses by a darkened section of the vault. Behind the glass is a registry of old-Earth archives that didn’t survive the Great Severance: the cache nodes went dark first, the document stores arrived as ragged fragments, the analytical clusters sit silent across whole sectors. The relational Kotomarket snapshot, though, survived in full — every link intact.

Not only relational

For Kotomarket, relational databases suit us especially well: buyers, orders, and products fall naturally into tables with links between them. That’s what this course is about. But across the industry — in 2024 just as today — you’ll meet other approaches too:

• key-value (Redis) — a blazingly fast "key → value" format for when you need to grab a simple record instantly;
• document-oriented (MongoDB) — stores flexible JSON documents whose structure can differ from record to record;
• columnar (ClickHouse) — strong at analytics, when you need to crunch metrics over enormous volumes of data fast.

Most of the popular DBMSs for product development stay relational: PostgreSQL, MySQL, Oracle, MS SQL. Master SQL here and you’ll be able to read data confidently at very different companies.

QUERY: Each archive has its own job: one prizes speed, another flexibility, a third analytics. You and I got lucky: we inherited the one where everything is connected to everything.

The registry of families: sorted out

The registry behind the glass is a good excuse to dig into the database families for real: "what kinds of databases are there, and when do you pick which" is one of the most common interview questions. We’ll go through five families in a row, each laid out the same way: storage, strengths and weaknesses, products, and when to pick it.

Relational databases: a strict schema and links

Storage: rows in tables with a schema declared up front — every column has a type, links between tables run through keys, and the DBMS guards integrity itself: you can’t record an order for a buyer who doesn’t exist. Changes are executed as transactions with ACID guarantees — "create the order + decrement stock" are either both written or neither is.

• Strengths: expressive SQL (filters, joins, aggregates), integrity enforced at the database level, transactions.
• Weaknesses: the schema has to be thought through in advance and migrated when things change; scaling horizontally across many servers is harder than for the NoSQL families.
• Products: PostgreSQL, MySQL/MariaDB, Microsoft SQL Server, Oracle, SQLite.
• When to pick it: users, orders, money, inventory — anywhere the data is connected and a bad write is unacceptable. For a new product, this is the default choice.

Key-value databases: a dictionary at top speed

Storage: one giant "key → value" dictionary, most often held in RAM. No tables, no links: put it in by key, pull it back out by key in a fraction of a millisecond.

-- This isn’t SQL, these are Redis commands
SET session:8f3a '{"user_id": 42}'
GET session:8f3a

• Strengths: speed, simplicity, easy scaling.
• Weaknesses: you can only query by an exact key — there’s no way to ask this format "find all sessions for buyers in Moscow"; the inside of a value is opaque to the database.
• Products: Redis, Memcached, Amazon DynamoDB.
• When to pick it: caches, sessions, counters, queues, leaderboards — hot data sitting alongside your main database, not instead of it.

Document databases: flexible JSON

Storage: records are JSON documents grouped into collections. The schema isn’t declared up front: a smartwatch has a "battery life" field, a hoodie has a "size chart," and both documents sit happily in the same collection. Nested structures live right inside the document, with no separate tables.

• Strengths: flexible structure, a document is read in full in a single fetch, handy for catalogs with heterogeneous attributes and for quick prototypes.
• Weaknesses: links between documents are the application’s job, and there’s almost no real JOIN; without discipline a collection turns into a zoo of incompatible formats.
• Products: MongoDB, Couchbase, Firestore.
• When to pick it: content, catalogs with wildly varying fields, profiles, and settings.

The line between families is blurry, by the way: PostgreSQL can store documents in a jsonb column and build indexes over them — that often covers a "document-shaped" need without a second database:

-- An example outside our snapshot: attrs is a jsonb column
SELECT name, attrs->>'color' AS color
FROM catalog
WHERE attrs @> '{"brand": "Kotomarket"}';

Columnar databases: analytics over billions of rows

Storage: values are laid out by column rather than by row: all the prices together, all the dates together. A "average order value by month" query reads just two columns off disk instead of the whole table, and values of the same kind compress beautifully. Hence the speed on aggregates.

-- The columnar database’s favorite genre (ClickHouse dialect: toYYYYMM is its function)
SELECT toYYYYMM(created_at) AS month, avg(total_amount) AS avg_check
FROM orders
GROUP BY month;

• Strengths: aggregates and scans over billions of rows, heavy compression.
• Weaknesses: pinpoint updates and deletes are expensive; reading a single full row is slower than in a row store; transactional load is simply not what they’re built for.
• Products: ClickHouse, BigQuery, Snowflake, Amazon Redshift.
• When to pick it: analytics, reports, events, logs, metrics — usually alongside the "live" relational database.

While we’re here, two terms interviewers love: OLTP (online transaction processing — lots of short writes and reads, the world of PostgreSQL) and OLAP (online analytical processing — heavy analytical reads over the entire history, the world of ClickHouse). The product writes to the OLTP database, while analytics reads from an OLAP copy.

And one last family: graph databases

When the links matter more than the records themselves — "friends of friends," delivery routes, recommendation chains — people reach for graph databases (Neo4j, Memgraph). The data in them is nodes (the records) and edges (the links between records); traversing links of any depth is a native operation. In a relational database, that same traversal is a staircase of joins that grows heavier with every level.

A mini-guide: how to choose a database for a project

1. Start with the questions, not the data. What queries will the product ask: pinpoint "by key," relational "who bought what," analytical "how much this month"?
2. Default to relational. Connected data plus a high cost of error (orders, payments, stock) means PostgreSQL. The safest start for almost any product.
3. Specialized databases are for a specific job, not "just in case." Hot reads are straining you — add a Redis cache. Analytics has grown — replicate events into ClickHouse. Wildly variable fields — first jsonb in PostgreSQL, and only when that isn’t enough, a document database.
4. Count the cost of a second database. Every new DBMS means data synchronization, backups, monitoring, and one more technology for the team to maintain.