Choosing a database: relational, document or key-value

There is no best database, only a good fit for a given job. Most teams are well served by one general-purpose database plus, sometimes, a specialized one next to it. This article compares the three families you meet most often and ends with a short decision list.

Relational (SQL): PostgreSQL, MySQL, MariaDB, SQL Server

Data lives in tables with defined columns and types. Relationships are expressed with foreign keys and combined at query time with JOIN. Changes run inside transactions: several steps succeed or fail as one.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';
COMMIT;   -- both changes, or neither

• Strengths: strong consistency and constraints (unique, foreign key, check) enforced by the database itself; flexible ad hoc queries and reporting with SQL; decades of tooling, skills and documentation.
• Costs: schema changes need migrations; deeply nested or highly variable records become many tables; scaling writes beyond one server takes more planning (partitioning, sharding or a distributed SQL product).
• Typical fit: orders, billing, inventory, user accounts, anything where money or correctness is involved and data is related in many ways.

Document: MongoDB and similar

Data lives in JSON-like documents grouped in collections. A document can contain arrays and nested objects, so one read can return a complete record without joins. The schema is flexible: documents in one collection may differ.

db.orders.insertOne({
  customer: { id: 42, name: "Ada" },
  lines: [ { sku: "LAMP-1", qty: 2 }, { sku: "BULB-9", qty: 4 } ],
  status: "paid"
})

• Strengths: natural fit for nested, record-shaped data; fast development when the shape still changes; replica sets for availability and built-in sharding for horizontal scale.
• Costs: you must design around your access patterns up front, because data that is read together should be stored together; duplicated (embedded) data must be kept in sync by your code; reporting across many collections is harder than SQL joins; a flexible schema means the application must enforce the rules unless you add schema validation.
• Typical fit: product catalogs, content management, user profiles and settings, event or device data with varying attributes.

Key-value and data structures: Redis, Valkey

Every value is reached by its key. Redis adds rich value types (hashes, lists, sets, sorted sets, streams) and keeps the working set in memory, so typical operations take well under a millisecond.

SET session:abc123 "{...}" EX 1800
INCR rate:10.0.0.7:2026-01-15T10:05
ZADD leaderboard 1810 "cy"

• Strengths: very low latency; simple, atomic operations on counters, queues, sets and rankings; built-in expiry for caches and sessions.
• Costs: the dataset (or at least its hot part) must fit in RAM, which is more expensive than disk; querying by anything other than the key needs extra structures or modules; durability depends on your persistence settings, and replication is asynchronous, so recent writes can be lost in a failover.
• Typical fit: caching, sessions, rate limiting, leaderboards, job queues and pub/sub next to a main database.

How to decide

1. Start from the queries. Write down the five most important reads and writes. If they are “fetch one thing by ID with its details”, a document or key-value store fits; if they are “combine and filter many related things”, SQL fits.
2. Ask what a lost or wrong write costs. Money, stock levels and permissions want transactions and constraints. A cached page does not.
3. Check the size and shape. If the data must live in RAM, price that. If records vary wildly, a rigid schema may fight you.
4. Count the people who will run it. Every additional database is another thing to patch, back up, monitor and secure. A team that knows PostgreSQL well may get further with it, including its JSON columns, than with a second system.
5. Default to boring. When unsure, pick a relational database as the system of record and add Redis as a cache when measurements show you need one.

Common mistakes

• Using Redis as the only copy of important data without persistence, backups and replication planned.
• Choosing a document database to avoid schema design, then discovering the schema problems later in application code.
• Normalizing a MongoDB model like SQL tables and then emulating joins with many round trips.
• Choosing a database for scale you do not have yet, at the cost of features you need today.

Official documentation

• PostgreSQL documentation: Transactions
• MongoDB Manual: Data modeling
• Redis docs: Data types