Relational, key-value and document

Relational

Data lives in tables of rows and columns, with a fixed schema saying which columns exist and what type each holds. Tables link through keys: each order row holds a customer_id that points at a row in customers.

You ask questions in SQL, and the database can join tables to answer them: "every order from customers in Lagos". It enforces the rules you set, such as "every order needs a customer", and runs transactions. PostgreSQL, MySQL and SQLite are relational.

Key-value

A key-value store is a giant lookup table: give it a key like session:7f3a, get back its value. There are no queries across values, and no joins.

What you get in return is speed. Many key-value stores keep data in memory and answer in well under a millisecond, which suits caches, login sessions, rate-limit counters and job queues. Redis and Valkey are key-value stores.

Document

A document database stores records as JSON-like documents, and two documents in the same collection can have different fields. You can query by any field, and a document can nest lists and objects, so one read can return a whole order with its items.

The flexibility cuts both ways: the database no longer checks that every record has the fields your code expects, so your code must. MongoDB is a document database.

Choosing

• Relational when data has clear relationships and must stay consistent: users, orders, payments. The cost: changing the schema takes a migration.
• Key-value when you always look things up by one key and speed matters. The cost: no queries by value, and in-memory stores may lose recent writes in a crash.
• Document when records vary in shape and you read each one whole. The cost: records can drift apart, and combining them is harder than a join.

Many apps use two: a relational database as the source of truth, and a key-value store as a cache or session store in front of it. PostgreSQL can also store JSON in a column, so at the start one relational database often covers document-shaped data too.