Relational vs Document vs Key-Value

Three ways to organise your closet

Relational is a dresser with labelled drawers: socks here, shirts there, every drawer cross-referenced so you can answer "which shirts go with which trousers." Document is a row of boxes, each holding one complete outfit (shirt, trousers, socks together) — fast to grab, slow to answer "show me every red shirt anyone owns." Key-Value is a row of hooks: each hook has a label, one item, and zero structure beyond that.

When each model wins

Relational wins when relationships matter and you want the database to enforce them. Document wins when the shape of each record genuinely varies and you query by document, not by relationship. Key-Value wins when you need raw speed for "give me X by exact key" and nothing else.

The PostgreSQL twist

PostgreSQL is the only major database that does all three credibly. Relational is the default. JSONB columns give you document-style storage with proper indexing. hstore gives you key-value. The pragmatic answer to "do I need MongoDB?" is almost always "no — add a JSONB column."

Code

Relational: cross-referenced tables·sql

SELECT customers.name, orders.total
FROM   customers
JOIN   orders ON orders.customer_id = customers.id
WHERE  orders.total > 100;

Document: same data inside Postgres JSONB·sql

CREATE TABLE customer_orders (
    id        INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer  TEXT NOT NULL,
    payload   JSONB NOT NULL
);

INSERT INTO customer_orders (customer, payload) VALUES
('Alice', '{"orders":[{"product":"Widget","total":150},
                     {"product":"Gadget","total":45}]}');

-- Query inside the JSON document
SELECT customer,
       jsonb_array_elements(payload->'orders') ->> 'product' AS product
FROM   customer_orders;

Key-value: Redis-style lookup·text

SET   user:42:session  "abc123"
GET   user:42:session
"abc123"

Relational vs Document vs Key-Value

Three ways to organise your closet

When each model wins

The PostgreSQL twist

Code

External links

Exercise

Progress

Comments 0