> ## Documentation Index
> Fetch the complete documentation index at: https://miliracle.online/llms.txt
> Use this file to discover all available pages before exploring further.
# Comparing Database Types
> Flat files to NewSQL: a visual guide to how each kind of database shapes data, and which questions each one makes cheap.
export const QUIZ = [{
q: "Which older model let a record have only one parent?",
a: ["Network", "Hierarchical", "Flat file"],
c: 1,
why: "In a hierarchy every record hangs under exactly one parent, like folders on a disk. The network model added more than one owner, so MINH could sit in both the CS set and the HANOI set."
}, {
q: "You embed the branch name inside every student document, then rename branch CS. What must happen?",
a: ["One update, like in SQL", "Every student document that holds CS is rewritten", "Nothing; documents update themselves"],
c: 1,
why: "Each document has its own copy of the name. MINH's and LAN's documents both need a write. Keeping those copies in step is the application's job."
}, {
q: "You need the average AGE of 100 million students. Which layout reads less data?",
a: ["Row store", "Column store", "They read the same"],
c: 1,
why: "A column store keeps all AGE values together, so it reads only that column. A row store reads every whole row to pick out one value from each."
}, {
q: "Cassandra and HBase are called wide-column databases. How do they store data?",
a: ["Column by column, like an analytics store", "Row by row, grouped by a row key; each row can have different columns"],
c: 1,
why: "The name misleads. Wide-column (column-family) stores keep each row together under its row key. Column-oriented stores such as ClickHouse are the ones that store by column."
}, {
q: "During a network split, a CP system gets a read on the cut-off node. What does it do?",
a: ["Returns the last value it has", "Returns an error", "Waits until the split heals, however long"],
c: 1,
why: "C cannot prove its copy is current, so a consistency-first system refuses. An AP system would answer with the old value instead."
}, {
q: "Which type makes 'friends of friends of MINH' a one-line query, whatever the depth?",
a: ["Key-value", "Graph", "Time series"],
c: 1,
why: "A graph database stores the links themselves, so the query names a pattern and a depth. In SQL each extra hop adds a join, or you write a recursive query."
}];
export const STACK_JOBS = [{
key: "orders",
label: "Store users and orders",
type: "Relational",
db: "PostgreSQL",
why: "Every order must point at a real user, and totals must add up. Foreign keys and transactions enforce that.",
pg: "This is what it is built for."
}, {
key: "sessions",
label: "Keep login sessions",
type: "Key-value",
db: "Redis",
why: "Every request looks up one session by its ID. Redis answers from memory and expires old sessions on its own (TTL).",
pg: "A sessions table is fine until lookups run at thousands per second."
}, {
key: "catalog",
label: "Store products with different fields",
type: "Document",
db: "MongoDB",
why: "A shirt has sizes, a laptop has RAM. Each product is one record with its own fields.",
pg: "A jsonb column holds the varied fields and can be indexed."
}, {
key: "friends",
label: "Suggest people you may know",
type: "Graph",
db: "Neo4j",
why: "Friends of friends is a walk two or three links out. A graph follows the links instead of joining a table to itself.",
pg: "A recursive query handles two hops on a modest network."
}, {
key: "metrics",
label: "Chart response times and errors",
type: "Time series",
db: "Prometheus",
why: "A value every few seconds per server, read as charts over time windows and deleted after weeks.",
pg: "The TimescaleDB extension adds time windows and retention."
}, {
key: "reports",
label: "Report sales over years of orders",
type: "Column-oriented",
db: "ClickHouse",
why: "Sums over hundreds of millions of rows read only the columns they need, and do not slow the live app.",
pg: "Fine until reports start slowing checkout. Then copy the data out."
}, {
key: "events",
label: "Log billions of device events",
type: "Wide-column",
db: "Cassandra",
why: "Writes spread over many machines with no single leader, and each device's events are read back by its ID.",
pg: null
}, {
key: "regions",
label: "Serve one SQL database in many regions",
type: "Distributed SQL",
db: "CockroachDB",
why: "Tables and transactions, with copies in each region that survive a region going down.",
pg: null
}];
export const SplitSide = ({side, step}) => {
const st = SPLIT_STEPS[step];
const v = st[side.key];
const seen = SPLIT_STEPS.slice(0, step + 1).map(x => x[side.key]);
const score = [["errors", seen.reduce((a, x) => a + (x.err || 0), 0)], ["stale reads", seen.reduce((a, x) => a + (x.stale || 0), 0)], ["lost writes", seen.reduce((a, x) => a + (x.lost || 0), 0)]];
const tone = {
ok: "is-hit",
warn: "is-visit",
bad: "is-bad"
};
const edges = [["A", "B"], ["A", "C"], ["B", "C"]];
return
{side.name}
{side.rule}
{v.reply ? <>{v.reply}. {v.why} : "\u00a0"}
{score.map(([label, n]) => {n} {label})}
;
};
export const SPLIT_POS = {
A: [70, 62],
B: [70, 158],
C: [238, 110]
};
export const SPLIT_STEPS = [{
t: "Three copies of MINH's AGE. All agree on 18.",
cut: false,
cp: {
A: 18,
B: 18,
C: 18
},
ap: {
A: 18,
B: 18,
C: 18
}
}, {
t: "The network splits. A and B still reach each other. C is alone.",
cut: true,
cp: {
A: 18,
B: 18,
C: 18
},
ap: {
A: 18,
B: 18,
C: 18
}
}, {
t: "Client 1 writes AGE = 19 on A.",
cut: true,
cp: {
A: 19,
B: 19,
C: 18,
at: "A",
tone: "ok",
reply: "Saved",
why: "A and B are 2 of 3, a majority, so the write commits."
},
ap: {
A: 19,
B: 19,
C: 18,
at: "A",
tone: "ok",
reply: "Saved",
why: "A copies it to B. C cannot hear about it."
}
}, {
t: "Client 2 reads AGE on C.",
cut: true,
cp: {
A: 19,
B: 19,
C: 18,
at: "C",
tone: "bad",
reply: "Error",
why: "C cannot reach a majority, so it cannot promise 18 is current.",
err: 1
},
ap: {
A: 19,
B: 19,
C: 18,
at: "C",
tone: "warn",
reply: "18",
why: "An answer, but a stale one. The latest value is 19.",
stale: 1
}
}, {
t: "Client 2 writes AGE = 20 on C.",
cut: true,
cp: {
A: 19,
B: 19,
C: 18,
at: "C",
tone: "bad",
reply: "Refused",
why: "Same reason: no majority, no write.",
err: 1
},
ap: {
A: 19,
B: 19,
C: 20,
at: "C",
tone: "warn",
reply: "Saved",
why: "Now the two sides disagree: 19 and 20."
}
}, {
t: "The network heals.",
cut: false,
cp: {
A: 19,
B: 19,
C: 19,
at: "C",
tone: "ok",
reply: "C copies 19",
why: "Every copy agrees, and no write was lost."
},
ap: {
A: 20,
B: 20,
C: 20,
at: "A",
tone: "bad",
reply: "Last write wins: 20",
why: "Client 1's 19 is silently dropped.",
lost: 1
}
}];
export const SPLIT_SIDES = [{
key: "cp",
name: "CP",
rule: "Refuse when unsure"
}, {
key: "ap",
name: "AP",
rule: "Answer anyway"
}];
export const minute = i => `10:${String(i).padStart(2, "0")}`;
export const DOWN = [{
from: 0,
to: 3,
v: 5
}, {
from: 3,
to: 6,
v: 9.7
}];
export const LOGINS = [4, 6, 5, 9, 12, 8, 7, 11, 14, 10, 6, 5];
export const WC_JOBS = [{
key: "get",
label: "Get student:1",
code: "get 'students', 'student:1'",
steps: [{
node: "A",
rows: ["student:1"]
}],
need: () => true,
note: "The key says which machine holds the row. One machine, one read, every column together."
}, {
key: "add",
label: "Add a login for MINH",
code: "put 'students', 'student:1', 'login:10-05', '…'",
steps: [{
node: "A",
rows: ["student:1"],
write: true
}],
need: () => false,
note: "One machine, one write. The row just grows a new column. No other row changes, and no schema change."
}, {
key: "sum",
label: "Sum every student's age",
code: "scan 'students' # then add up every age: 74",
steps: [{
node: "A",
rows: ["student:1", "student:4"]
}, {
node: "B",
rows: ["student:2"]
}, {
node: "C",
rows: ["student:3"]
}],
need: c => c.startsWith("age="),
note: "No row key, so every machine reads every row to find 4 ages. This is a job for a columnar warehouse."
}];
export const WC_ROWS = [{
key: "student:1",
node: "A",
cols: ["name=MINH", "city=HANOI", "age=18", "login:10-01", "login:10-03"],
extra: "login:10-05"
}, {
key: "student:4",
node: "A",
cols: ["name=HOA", "city=HANOI", "age=18", "login:10-02"]
}, {
key: "student:2",
node: "B",
cols: ["name=LAN", "age=18", "phone=0912345678"]
}, {
key: "student:3",
node: "C",
cols: ["name=TUAN", "age=20"]
}];
export const WC_NODES = ["A", "B", "C"];
export const RC_QUERIES = [{
key: "sum",
label: "Run SUM(AGE)",
sql: "SELECT SUM(AGE) FROM STUDENT; -- 74",
need: (r, c) => c === 3,
row: "Reads all 4 blocks (16 values) to use 4 of them.",
col: "Reads 1 block: just the AGE values."
}, {
key: "one",
label: "Fetch MINH's whole record",
sql: "SELECT * FROM STUDENT WHERE ROLL_NO = 1;",
need: r => r === 0,
row: "Reads 1 block. The whole record sits together.",
col: "Reads 4 blocks, one per column, to rebuild one row."
}, {
key: "ins",
label: "Insert student 5",
sql: "INSERT INTO STUDENT VALUES (5, 'KHOA', 'CANTHO', 19);",
need: r => r === 4,
insert: true,
row: "Writes 1 new block on the end.",
col: "Adds one value to each of the 4 blocks."
}];
export const RC_NEW = [5, "KHOA", "CANTHO", 19];
export const RC_ROWS = [[1, "MINH", "HANOI", 18], [2, "LAN", "HUE", 18], [3, "TUAN", "DANANG", 20], [4, "HOA", "HANOI", 18]];
export const RC_COLS = ["ROLL_NO", "NAME", "CITY", "AGE"];
export const hopSql = n => {
const lines = [`SELECT f${n}.b FROM FRIEND f1`];
for (let i = 2; i <= n; i++) lines.push(`JOIN FRIEND f${i} ON f${i}.a = f${i - 1}.b`);
const where = ["f1.a = 'MINH'"];
for (let i = 2; i <= n; i++) where.push(`f${i}.b <> f${i - 1}.a`);
lines.push(`WHERE ${where.join("\n AND ")};`);
return lines.join("\n");
};
export const hopCypher = n => `MATCH (:Student {name: 'MINH'})-[:FRIEND*${n}]-(f)\nRETURN DISTINCT f.name;`;
export const HOP_LABELS = ["Find MINH's friends (1 hop)", "Find friends of friends (2 hops)", "Find people 3 hops away"];
export const HOP_LEVELS = [["MINH"], ["LAN", "HOA"], ["TUAN", "KHOA"], ["LINH"]];
export const FRIENDS = {
nodes: [{
id: "MINH",
label: "MINH",
x: 60,
y: 120
}, {
id: "LAN",
label: "LAN",
x: 190,
y: 60
}, {
id: "HOA",
label: "HOA",
x: 190,
y: 180
}, {
id: "TUAN",
label: "TUAN",
x: 330,
y: 60
}, {
id: "KHOA",
label: "KHOA",
x: 330,
y: 180
}, {
id: "LINH",
label: "LINH",
x: 460,
y: 60
}],
edges: [["MINH", "LAN"], ["MINH", "HOA"], ["LAN", "TUAN"], ["HOA", "KHOA"], ["TUAN", "LINH"]]
};
export const laneCount = (r, w) => [r && `${r} read${r === 1 ? "" : "s"}`, w && `${w} write${w === 1 ? "" : "s"}`].filter(Boolean).join(" · ") || "–";
export const LANE_TASKS = [{
key: "read",
label: "Read MINH's profile page",
steps: {
rel: [{
read: ["S1"]
}, {
read: ["BCS"]
}],
doc: [{
read: ["d1"]
}],
kv: [{
read: ["k1"]
}, {
read: ["kCS"]
}]
},
note: {
rel: "Two tables, joined by the database.",
doc: "The branch is already inside MINH's document.",
kv: "Your app splits the string and combines the two."
}
}, {
key: "find",
label: "Find every student in CS",
steps: {
rel: [{
read: ["S1", "S2"]
}],
doc: [{
read: ["d1", "d2"]
}],
kv: [{
read: ["k1", "k2"]
}, {
read: ["k3", "k4"]
}]
},
note: {
rel: "An index on the branch finds the two rows.",
doc: "An index on branch.code finds the two documents.",
kv: "The store can't look inside values, so it reads them all."
}
}, {
key: "rename",
label: "Rename branch CS to COMPUTING",
steps: {
rel: [{
write: ["BCS"],
set: {
BCS: "COMPUTING"
}
}],
doc: [{
write: ["d1"],
set: {
d1: "CS · COMPUTING"
}
}, {
write: ["d2"],
set: {
d2: "CS · COMPUTING"
}
}],
kv: [{
write: ["kCS"],
set: {
kCS: "COMPUTING"
}
}]
},
note: {
rel: "The name lives in one row.",
doc: "The name is copied into every CS student. Miss one and they disagree.",
kv: "Students store only the code, so one key changes."
}
}];
export const LANES = [{
key: "rel",
name: "Relational",
recs: [["S1", "MINH", "CS"], ["S2", "LAN", "CS"], ["S3", "TUAN", "ECE"], ["S4", "HOA", "IT"], ["BCS", "BRANCH CS", "COMPUTER SCIENCE", true]]
}, {
key: "doc",
name: "Document",
recs: [["d1", "MINH", "CS · COMPUTER SCIENCE"], ["d2", "LAN", "CS · COMPUTER SCIENCE"], ["d3", "TUAN", "ECE · …"], ["d4", "HOA", "IT · …"]]
}, {
key: "kv",
name: "Key-value",
recs: [["k1", "student:1", "1|MINH|HANOI|18|CS"], ["k2", "student:2", "2|LAN|HUE|18|CS"], ["k3", "student:3", "3|TUAN|DANANG|20|ECE"], ["k4", "student:4", "4|HOA|HANOI|18|IT"], ["kCS", "branch:CS", "COMPUTER SCIENCE", true]]
}];
export const NET = {
nodes: [{
id: "CS",
label: "CS",
x: 140,
y: 28,
own: true
}, {
id: "IT",
label: "IT",
x: 320,
y: 28,
own: true
}, {
id: "ECE",
label: "ECE",
x: 440,
y: 28,
own: true
}, {
id: "s2",
label: "LAN",
sub: "age 18",
x: 80,
y: 120
}, {
id: "s1",
label: "MINH",
sub: "age 18",
x: 200,
y: 120
}, {
id: "s4",
label: "HOA",
sub: "age 18",
x: 320,
y: 120
}, {
id: "s3",
label: "TUAN",
sub: "age 20",
x: 440,
y: 120
}, {
id: "HUE",
label: "HUE",
x: 80,
y: 212,
own: true
}, {
id: "HANOI",
label: "HANOI",
x: 260,
y: 212,
own: true
}, {
id: "DANANG",
label: "DANANG",
x: 440,
y: 212,
own: true
}],
edges: [["CS", "s2"], ["CS", "s1"], ["IT", "s4"], ["ECE", "s3"], ["HUE", "s2"], ["HANOI", "s1"], ["HANOI", "s4"], ["DANANG", "s3"]]
};
export const TREE = {
nodes: [{
id: "root",
label: "SCHOOL",
x: 260,
y: 28,
own: true
}, {
id: "CS",
label: "CS",
x: 125,
y: 110,
own: true
}, {
id: "ECE",
label: "ECE",
x: 315,
y: 110,
own: true
}, {
id: "IT",
label: "IT",
x: 450,
y: 110,
own: true
}, {
id: "s1",
label: "MINH",
sub: "HANOI · 18",
x: 65,
y: 200
}, {
id: "s2",
label: "LAN",
sub: "HUE · 18",
x: 185,
y: 200
}, {
id: "s3",
label: "TUAN",
sub: "DANANG · 20",
x: 315,
y: 200
}, {
id: "s4",
label: "HOA",
sub: "HANOI · 18",
x: 450,
y: 200
}],
edges: [["root", "CS"], ["root", "ECE"], ["root", "IT"], ["CS", "s1"], ["CS", "s2"], ["ECE", "s3"], ["IT", "s4"]]
};
export const LEGACY_NOTES = {
file: {
cs: "A flat file has no structure to skip ahead with. Every question reads every line and splits it at the colons.",
hanoi: "Same cost as before: read all four lines. The file does not care what you ask.",
age: "Same again: all four lines. A flat file is fine for a config file, slow for a million students."
},
tree: {
cs: "The tree is built by branch, so this is a short walk: root, CS, then CS's children.",
hanoi: "City is not part of the tree. To find HANOI you must visit every branch and every student.",
age: "Age is not part of the tree either, so this is another full walk."
},
net: {
cs: "Follow the CS set: start at the owner record, then step to each member.",
hanoi: "Students also belong to a city set, so HANOI has its own path. A record can have more than one owner.",
age: "Nobody built an AGE path. The program walks every branch set to reach every student."
}
};
export const LEGACY_PATHS = {
file: {
cs: ["s1", "s2", "s3", "s4"],
hanoi: ["s1", "s2", "s3", "s4"],
age: ["s1", "s2", "s3", "s4"]
},
tree: {
cs: ["root", "CS", "s1", "s2"],
hanoi: ["root", "CS", "s1", "s2", "ECE", "s3", "IT", "s4"],
age: ["root", "CS", "s1", "s2", "ECE", "s3", "IT", "s4"]
},
net: {
cs: ["CS", "s1", "s2"],
hanoi: ["HANOI", "s1", "s4"],
age: ["CS", "s1", "s2", "IT", "s4", "ECE", "s3"]
}
};
export const LEGACY_QS = [{
key: "cs",
label: "Find students in branch CS",
test: s => s.branch === "CS",
sql: "SELECT NAME FROM STUDENT WHERE BRANCH_CODE = 'CS';"
}, {
key: "hanoi",
label: "Find students in HANOI",
test: s => s.city === "HANOI",
sql: "SELECT NAME FROM STUDENT WHERE CITY = 'HANOI';"
}, {
key: "age",
label: "Find students aged 20",
test: s => s.age === 20,
sql: "SELECT NAME FROM STUDENT WHERE AGE = 20;"
}];
export const LEGACY_MODELS = [{
key: "file",
label: "Use a flat file"
}, {
key: "tree",
label: "Use a hierarchy"
}, {
key: "net",
label: "Use a network"
}];
export const ShapeView = ({k}) => {
if (k === "table") return
;
return NodeGraph({
nodes: HERO_GRAPH.nodes,
edges: HERO_GRAPH.edges,
h: 195,
label: "MINH linked to branch CS, city HANOI and friend LAN"
});
};
export const HERO_DOC = `{
"_id": 1,
"name": "MINH",
"city": "HANOI",
"age": 18,
"branch": {
"code": "CS",
"name": "COMPUTER SCIENCE"
}
}`;
export const HERO_GRAPH = {
nodes: [{
id: "MINH",
label: "MINH",
x: 260,
y: 95
}, {
id: "CS",
label: "CS",
x: 80,
y: 35,
own: true
}, {
id: "HANOI",
label: "HANOI",
x: 80,
y: 160,
own: true
}, {
id: "LAN",
label: "LAN",
x: 445,
y: 95
}],
edges: [["MINH", "CS", "IN_BRANCH"], ["MINH", "HANOI", "LIVES_IN"], ["MINH", "LAN", "FRIEND"]]
};
export const SHAPES = [{
key: "table",
label: "Show as tables",
family: "Relational",
cheap: "Fixed columns, linked by keys. Cheap: joining tables and checking rules on every write."
}, {
key: "kv",
label: "Show as key-value",
family: "Key-value",
cheap: "One key, one opaque value. Cheap: fetching by key. The store cannot look inside the value."
}, {
key: "doc",
label: "Show as a document",
family: "Document",
cheap: "One nested record, branch included. Cheap: reading or writing the whole record at once."
}, {
key: "graph",
label: "Show as a graph",
family: "Graph",
cheap: "Things and the links between them. Cheap: following links from one thing to the next."
}];
export const NodeGraph = ({nodes, edges, w = 520, h = 240, cls = () => "", ecls = () => "", label}) => {
const at = {};
nodes.forEach(n => at[n.id] = n);
return ;
};
export const nodeW = n => Math.max(n.label.length, (n.sub || "").length) * 9.4 + 24;
export const MiniTable = ({name, cols, rows}) =>
{name}
{cols.map(h =>
{h}
)}
{rows.map(r =>
{r.map((v, j) =>
{v}
)}
)}
;
export const CodeLine = ({text}) => text.split(new RegExp(CODE_KW.source, "g")).map((part, i) => i % 2 ? {part} : part);
export const CODE_KW = /\b(SELECT|DISTINCT|FROM|WHERE|JOIN|ON|AND|SUM|UPDATE|SET|INSERT|INTO|VALUES|MATCH|RETURN|GET)\b/;
export const studentOf = id => STUDENTS.find(s => `s${s.id}` === id);
export const STUDENTS = [{
id: 1,
name: "MINH",
city: "HANOI",
age: 18,
branch: "CS"
}, {
id: 2,
name: "LAN",
city: "HUE",
age: 18,
branch: "CS"
}, {
id: 3,
name: "TUAN",
city: "DANANG",
age: 20,
branch: "ECE"
}, {
id: 4,
name: "HOA",
city: "HANOI",
age: 18,
branch: "IT"
}];
export const pause = (reduce, ms) => new Promise(r => setTimeout(r, reduce ? 0 : ms));
export const useOnceVisible = (ref, onEnter) => {
const cb = useRef(onEnter);
cb.current = onEnter;
useEffect(() => {
const el = ref.current;
if (!el) return;
if (typeof IntersectionObserver === "undefined") {
cb.current();
return;
}
const io = new IntersectionObserver(entries => {
if (entries.some(e => e.isIntersecting)) {
io.disconnect();
cb.current();
}
}, {
threshold: 0.35
});
io.observe(el);
return () => io.disconnect();
}, [ref]);
};
export const useReducedMotion = () => {
const [reduce, setReduce] = useState(false);
useEffect(() => {
const mq = window.matchMedia("(prefers-reduced-motion: reduce)");
setReduce(mq.matches);
const on = e => setReduce(e.matches);
mq.addEventListener("change", on);
return () => mq.removeEventListener("change", on);
}, []);
return reduce;
};
> This is the Markdown version of an interactive page. Every animated figure on the web page is replaced here by a table or code block with the same facts. Source: adapted from Prisma's Data Guide, ["Comparing database types"](https://www.prisma.io/dataguide/intro/comparing-database-types), with corrections.
Here is a student named MINH. MINH is 18, lives in HANOI, studies in branch CS and is friends with LAN. That is four facts. Every database can store them. But each kind of database stores them in a different **shape**, and the shape decides which questions are fast and which are slow.
A **database type** (also called a *data model* or *database family*) is a pattern for organizing data. Relational databases use tables. Key-value stores use keys and opaque values. Document stores use nested records, and graph databases use things and the links between them. This post walks through the main types in roughly the order they appeared. Every figure uses the same small set of students, so you can see exactly what changes.
**In short**
* The shape of the data decides which questions are cheap. No type is fastest at everything.
* Early databases (hierarchical, network) made programs walk fixed paths. Relational databases let you say *what* you want and work out the path for you.
* NoSQL types each make one access pattern cheap: key-value (look up by key), document (whole records), graph (follow links), wide-column (huge numbers of rows spread across machines), time series (timestamped appends).
* Wide-column and column-oriented are different things. Only column-oriented stores keep each column together on disk.
* NewSQL (distributed SQL) spreads relational data across machines. When the network splits, every such system must choose: refuse requests, or risk stale and lost data (the CAP theorem).
* Most real systems use more than one type.
## Why the shape matters
Ask three questions about the students:
1. Show MINH's profile page.
2. Find every student in branch CS.
3. Find friends of MINH's friends.
A document store answers the first in one read, because MINH's whole profile sits in one record. A relational database answers the second with one indexed query. A graph database answers the third by following links. Each type made a bet about which questions matter most, and stored the data to win that bet.
So the useful question is never "which database is best?". It is "which questions will I ask most, and which shape makes them cheap?".
## Before relational: files, trees and networks
The earliest "databases" were plain **flat files**: one record per line, with fields split by a character such as a colon. Unix still keeps its user list in `/etc/passwd` this way, and a CSV file is the same idea. Flat files need no special software, and you can read them by eye. But they have no structure to search with, so every question reads the whole file.
In the 1960s, **hierarchical databases** such as IBM's IMS arranged records in a tree. Each record has exactly one parent: the school has branches, and each branch has its students. Walking down the tree is fast. But a question the tree was not built for means visiting everything. Your computer's folders, DNS names and LDAP directories still follow this shape.
**Network databases** (the CODASYL standard, late 1960s, products such as IDMS) let a record have more than one owner. MINH could belong to both the CS set and the HANOI set, so both questions had a fast path. But the program still had to walk those paths step by step, and someone had to build each path in advance.
The data, as the flat file `students.txt`:
```text theme={null}
1:MINH:HANOI:18:CS
2:LAN:HUE:18:CS
3:TUAN:DANANG:20:ECE
4:HOA:HANOI:18:IT
```
The hierarchy is `SCHOOL → {CS, ECE, IT}`, with MINH and LAN under CS, TUAN under ECE and HOA under IT. The network adds city owners: HANOI owns MINH and HOA, HUE owns LAN and DANANG owns TUAN.
Records visited to answer each question:
| Question | Flat file | Hierarchy | Network | Relational SQL |
| - | - | - | - | - |
| Students in CS (MINH, LAN) | 4 (every line) | 4 (SCHOOL → CS → 2 students) | 3 (CS set → 2 members) | `SELECT NAME FROM STUDENT WHERE BRANCH_CODE = 'CS';` |
| Students in HANOI (MINH, HOA) | 4 | 8 (city is not in the tree: visit everything) | 3 (HANOI set → 2 members) | `SELECT NAME FROM STUDENT WHERE CITY = 'HANOI';` |
| Students aged 20 (TUAN) | 4 | 8 | 7 (no AGE path: walk every branch set) | `SELECT NAME FROM STUDENT WHERE AGE = 20;` |
All three older models share one problem. The **program** decides how to reach the data. If the paths change, every program that walks them has to change too.
## Relational: say what, not how
Edgar F. Codd proposed the [relational model](/writing/database/relational-model) in 1970. Data lives in tables, and tables link through shared keys such as `BRANCH_CODE`. In SQL you describe the rows you want, and the database picks the path. The SQL at the bottom of Figure 1 never says how to walk anything.
* **Good at:** checking rules on every write, and joining any tables, even in ways nobody planned.
* **Cost:** data must fit the schema. Changing the shape later needs a migration.
* **Examples:** PostgreSQL, MySQL, SQLite, Oracle, SQL Server.
## NoSQL: one shape per job
In the mid-2000s, big web companies needed to spread data across many machines and change its shape often. Joins across machines are slow, and fixed schemas slowed teams down. New databases each dropped some relational features to make one job fast. Together they are called **NoSQL** ("not only SQL"), a very mixed family.
### Key-value and document stores
A **key-value store** is a giant dictionary: `SET` a value under a key, `GET` it back. The store never looks inside the value, so key lookups are very fast, but you cannot ask "which values contain CS?". Used for sessions, caches and config. Examples: Redis, Memcached, DynamoDB.
A **document store** also keeps one record per key, but the record is JSON the database can read, index and query. Documents nest, so MINH's branch can live *inside* MINH's record. Adding a field needs no migration. Examples: MongoDB, Couchbase, Firestore.
Nesting is the big trade-off. It makes reads fast and writes harder:
The same students in three stores:
* **Relational:** `STUDENT` rows 1–4 (with `BRANCH_CODE`) and `BRANCH` rows CS, ECE, IT.
* **Document:** `students/1` = `{"name": "MINH", "branch": {"code": "CS", "name": "COMPUTER SCIENCE"}, …}`, and the same for students 2–4. The branch name is copied into every document.
* **Key-value:** `student:1` = `"1|MINH|HANOI|18|CS"` … `student:4`, plus `branch:CS` = `"COMPUTER SCIENCE"`.
| Job | Relational | Document | Key-value |
| - | - | - | - |
| Read MINH's profile page | 2 reads (STUDENT 1, BRANCH CS) and a join | 1 read (`students/1`) | 2 reads (`student:1`, `branch:CS`); the app parses and combines |
| Find every student in CS | 2 reads through the `BRANCH_CODE` index | 2 reads through an index on `branch.code` | 4 reads: the store cannot search values, so the app GETs every key and filters |
| Rename branch CS to COMPUTING | 1 write (the BRANCH row) | 2 reads and 2 writes (`students/1` and `students/2`); missing one leaves the copies out of step | 1 write (`branch:CS`) |
The document store wins the profile page, and pays when shared data changes. Keeping the copies in step becomes the application's job, not the database's. A useful rule: embed data that belongs to one record and changes with it, such as a student's phone numbers. Reference data that many records share, such as a branch.
### Graph databases
A **graph database** stores *nodes* (things, such as students), *edges* (named links between them, such as `FRIEND`) and *properties* on both. The links are stored as data, so following one is a direct step from one node to the next. Queries describe a pattern of nodes and links, and the database finds every match.
Friendships: MINH–LAN, MINH–HOA, LAN–TUAN, HOA–KHOA, TUAN–LINH. In SQL they are stored in a `FRIEND(a, b)` table with both directions of each pair.
| Distance from MINH | People | Graph query (Cypher) | SQL self-joins |
| - | - | - | - |
| 1 hop | LAN, HOA | `MATCH (:Student {name: 'MINH'})-[:FRIEND*1]-(f) RETURN DISTINCT f.name;` | 0 |
| 2 hops | TUAN, KHOA | `…-[:FRIEND*2]-(f)…` | 1 |
| 3 hops | LINH | `…-[:FRIEND*3]-(f)…` | 2 |
SQL for 3 hops:
```sql theme={null}
SELECT f3.b FROM FRIEND f1
JOIN FRIEND f2 ON f2.a = f1.b
JOIN FRIEND f3 ON f3.a = f2.b
WHERE f1.a = 'MINH'
AND f2.b <> f1.a
AND f3.b <> f2.a;
```
The `<>` filters stop the walk from stepping straight back. That is enough here because this graph has no loops. Real data also needs to skip people already seen.
To be fair to SQL, a recursive query (`WITH RECURSIVE`) handles any depth, and for two or three hops on indexed tables a relational database is often fast enough. Graph databases are worth it when the questions are *mostly* about paths: social networks, fraud rings, recommendations, network maps. Examples: Neo4j, Amazon Neptune, JanusGraph, Dgraph.
### Wide-column and column-oriented: not the same thing
These two names sound alike and mean different things.
A **column-oriented** (columnar) database stores each *column* together on disk. All the ages sit in one place, all the names in another. A query that adds up one column reads only that column. Rebuilding one whole row means visiting every column. This is the engine behind analytics warehouses. Examples: ClickHouse, Amazon Redshift, Google BigQuery, Snowflake, DuckDB.
`STUDENT(ROLL_NO, NAME, CITY, AGE)` with rows `(1, MINH, HANOI, 18)`, `(2, LAN, HUE, 18)`, `(3, TUAN, DANANG, 20)`, `(4, HOA, HANOI, 18)`. In a row store, each disk block holds one row. In a column store, each block holds one column.
| Job | Row store | Column store |
| - | - | - |
| `SELECT SUM(AGE) FROM STUDENT;` (= 74) | Reads 4 blocks, 16 values, to use 4 | Reads 1 block (AGE) |
| `SELECT * FROM STUDENT WHERE ROLL_NO = 1;` | Reads 1 block | Reads 4 blocks, one per column |
| `INSERT INTO STUDENT VALUES (5, 'KHOA', 'CANTHO', 19);` | Writes 1 new block | Appends a value to each of 4 blocks |
A **wide-column** (or *column-family*) database, such as Apache Cassandra, HBase or ScyllaDB, is a different idea. Despite the name, it keeps each **row** together, under a **row key**. Each row can have its own set of columns, and a row can have thousands of them. Rows are spread across machines by their key, so these systems scale to huge write loads:
Rows are hashed to machines by row key. Each row has its own columns:
| Machine | Row key | Columns in this row |
| - | - | - |
| A | `student:1` | `name=MINH`, `city=HANOI`, `age=18`, `login:10-01`, `login:10-03` |
| A | `student:4` | `name=HOA`, `city=HANOI`, `age=18`, `login:10-02` |
| B | `student:2` | `name=LAN`, `age=18`, `phone=0912345678` |
| C | `student:3` | `name=TUAN`, `age=20` |
| Job | Machines touched | Rows touched |
| - | - | - |
| Get `student:1` | 1 (A) | 1 read |
| Add column `login:10-05` to `student:1` | 1 (A) | 1 written; no schema change |
| `SUM(age)` over all students (= 74) | 3 (all) | 4 read: no row key, so a full scan |
You design the tables around the questions you will ask, because joins are not supported and queries should name a row key. In Cassandra, a sum over all rows means scanning the whole cluster. That is the opposite of what a columnar warehouse is built for.
### Time series databases
A **time series** is a list of timestamped values: CPU load every ten seconds, temperatures from a sensor, logins per minute. This data has a clear pattern. New points arrive all the time, always at the end. Old points are almost never changed. Recent data is read often and old data rarely. Time series databases are built for exactly this:
Logins per minute (illustrative numbers):
| Minute | 10:00 | 10:01 | 10:02 | 10:03 | 10:04 | 10:05 | 10:06 | 10:07 | 10:08 | 10:09 | 10:10 | 10:11 |
| - | - | - | - | - | - | - | - | - | - | - | - | - |
| Logins | 4 | 6 | 5 | 9 | 12 | 8 | 7 | 11 | 14 | 10 | 6 | 5 |
Each point is appended once and never updated. Downsampling 10:00–10:05 into 3-minute averages replaces six points with two: 10:00–10:02 = 5, 10:03–10:05 = 9.7. Storage goes from 12 points to 8. The minute-by-minute detail for that period is gone.
They compress long runs of similar numbers, aggregate by time window, downsample old data and drop it after a retention period. Examples: Prometheus, InfluxDB, TimescaleDB (a PostgreSQL extension), OpenTSDB.
## NewSQL and the CAP theorem
NoSQL systems scaled out by giving things up: joins, schemas, and often strong guarantees about what a read returns. **NewSQL**, now usually called *distributed SQL*, tries to get the scale back without giving those up. These databases keep tables, SQL and transactions, and spread the data across many machines. They split the data into pieces (*shards*), keep copies of each piece, and use a consensus protocol so the copies agree on each write. Examples: Google Spanner, CockroachDB, YugabyteDB, TiDB, and in-memory engines such as VoltDB and SingleStore (formerly MemSQL).
Copies on many machines raise a hard question. What happens when the network between them breaks? The **CAP theorem** answers it. When a network *partition* splits the machines, a system cannot be both fully **consistent** (every read sees the latest write) and fully **available** (every request gets a non-error answer). It must pick one until the network heals.
Three nodes, A, B and C, each hold MINH's `AGE = 18`. The links to C break, so A and B form a majority and C is alone.
| Step | CP: refuse when unsure | AP: answer anyway |
| - | - | - |
| 1. Links to C break | A=18, B=18, C=18 | A=18, B=18, C=18 |
| 2. Client 1 writes AGE = 19 on A | Commits on A and B (majority). C still 18 | A and B = 19. C still 18 |
| 3. Client 2 reads AGE on C | Error: C cannot confirm it is up to date | Returns 18, which is stale |
| 4. Client 2 writes AGE = 20 on C | Refused | Accepted: C = 20, the two sides disagree |
| 5. Network heals | C copies 19. No write lost; C's clients saw errors | Last-write-wins keeps 20 and drops 19. Every request was answered; one write was lost |
CAP is often misquoted as "pick two of three". The choice only applies **during** a partition. When the network is healthy, a well-built system gives you both. Distributed SQL databases choose consistency (CP): the cut-off side refuses requests rather than answer wrongly. Many NoSQL stores, such as Cassandra and DynamoDB, lean towards availability (AP) by default, though both let you ask for stronger consistency per request. Even with no partition, there is a second trade-off. Waiting for copies to agree adds delay to every write. The PACELC model gives that trade-off a name. [The CAP Theorem](/writing/database/cap-theorem) covers both in depth.
## Multi-model, and using more than one
A **multi-model** database supports several of these shapes in one system: for example documents, graphs and key-value data, queried with one language. You run one system instead of three. The catch is that each model may be less complete than in a database built only for it, so check the parts you need. Examples: ArangoDB, OrientDB, Couchbase. The lines are blurring anyway. PostgreSQL stores and indexes JSON documents, and TimescaleDB adds time series features to it.
### Start with one, add with a reason
Say you are building a small shop. You need users, orders, login sessions and a dashboard of response times. That looks like four jobs and maybe four databases. Usually it is one. PostgreSQL stores the tables, keeps sessions in a plain table, and holds varied product fields in a `jsonb` column. A recursive query walks a short chain of links, and the TimescaleDB extension handles time series. One relational database covers most young products.
Add a second database only when you can name the job the first one does badly, and you have measured it. Good reasons look like this:
* Session lookups run thousands of times per second and add load to the main database. Add a key-value store such as Redis.
* Reports over years of orders slow down checkout. Copy the orders to a column store such as ClickHouse.
* Writes arrive faster than one machine can take them. Add a wide-column store, or a distributed SQL database if you still need transactions.
"Everyone uses MongoDB" or "graphs are cool" are not reasons. Every extra database costs something: one more system to back up, monitor, upgrade and secure, and one more place where data can go out of step. Writing to two databases in one request is **not** one transaction. If the second write fails, the first one stays. The usual fix is to treat one database as the source of truth and copy changes out of it, for example from an *outbox* table or with *change data capture* (reading the database's change log).
Jobs a product might need, the database type built for each, and whether PostgreSQL alone can cover it for now:
| Job | Type | Example | Why that type | PostgreSQL alone? |
| - | - | - | - | - |
| Store users and orders | Relational | PostgreSQL | Orders must point at real users and totals must add up; foreign keys and transactions enforce that | Yes, this is what it is built for |
| Keep login sessions | Key-value | Redis | One lookup by session ID per request, answered from memory, with automatic expiry (TTL) | Yes, a sessions table, until lookups run at thousands per second |
| Store products with different fields | Document | MongoDB | Each product is one record with its own fields | Yes, a `jsonb` column, which can be indexed |
| Suggest people you may know | Graph | Neo4j | Friends of friends is a walk two or three links out | Yes, a recursive query, for two hops on a modest network |
| Chart response times and errors | Time series | Prometheus | A value every few seconds per server, read over time windows, deleted after weeks | Yes, with the TimescaleDB extension |
| Report sales over years of orders | Column-oriented | ClickHouse | Sums over hundreds of millions of rows read only the needed columns | Until reports slow the live app; then copy the data out |
| Log billions of device events | Wide-column | Cassandra | Writes spread over many machines with no single leader, read back by device ID | No |
| Serve one SQL database in many regions | Distributed SQL | CockroachDB | Tables and transactions, with copies that survive a region going down | No |
As a quick reference, here is the main question each type answers best:
| If your main question is… | Reach for | Examples |
| - | - | - |
| Many related records, rules that must hold, questions you cannot predict | Relational | PostgreSQL, MySQL, SQLite |
| Get or set one value by its key, very fast | Key-value | Redis, Memcached, etcd |
| Read and write whole self-contained records whose shape changes | Document | MongoDB, Couchbase |
| How are these things connected, several steps out? | Graph | Neo4j, Neptune |
| Huge write volume spread over many machines, looked up by key | Wide-column | Cassandra, ScyllaDB, HBase |
| Sums and averages over billions of rows | Column-oriented | ClickHouse, BigQuery, Redshift |
| Metrics and events over time | Time series | Prometheus, InfluxDB, TimescaleDB |
| Relational data at a scale one machine cannot hold | Distributed SQL | Spanner, CockroachDB, YugabyteDB |
A typical web app might keep users and orders in PostgreSQL, sessions in Redis, and metrics in Prometheus.
## Other types you will meet
* **RDF triple stores** (semantic graphs) store facts as subject–predicate–object triples, such as `MINH – studiesIn – CS`, using the W3C's RDF standard and the SPARQL query language. They suit linked open data and shared vocabularies. *Property graphs* like Neo4j are usually easier for application data, because their nodes and edges can carry properties.
* **Object-oriented databases** store objects exactly as a program defines them, classes and all. They never went mainstream: there was no common standard, and the data was tied to one application's code. Their main idea lives on in ORMs, which map objects onto relational tables.
## Check yourself
Questions this document answers:
* What is a database type? A pattern for organizing data, also called a data model. It decides which questions are cheap and which are expensive.
* How did hierarchical and network databases differ? In a hierarchy each record has exactly one parent, so MINH can be under CS but not also under HANOI. In a network a record can have several owners, so MINH sits in both the CS set and the HANOI set. In both, the program walks the paths itself.
* What did the relational model change? You describe the rows you want (`WHERE BRANCH_CODE = 'CS'`) and the database picks the path. Programs no longer depend on how the data is stored.
* What is the difference between a key-value store and a document store? A key-value store sees each value as opaque bytes and finds it only by key. A document store understands the JSON inside, so it can index fields and query by them.
* What happens when you rename a branch whose name is embedded in student documents? Every document holding a copy must be rewritten: `students/1` and `students/2` for CS. Keeping copies in step is the application's job.
* When is a graph database worth it? When most questions are about paths, such as friends of friends. In SQL each extra hop adds a self-join, or you write a recursive query. For two or three hops on indexed tables, SQL is often fine.
* Are wide-column and column-oriented databases the same? No. Column-oriented stores (ClickHouse, Redshift) keep each column together and are good at sums over many rows. Wide-column stores (Cassandra, HBase) keep each row together under a row key, with flexible columns per row.
* Why does a column store read less for `SUM(AGE)`? All ages are stored in one block, so it reads 1 block instead of every row's block.
* What makes time series data special? Points arrive in time order and are appended, almost never updated. Old data is downsampled (for example, six 1-minute counts become two 3-minute averages) and later deleted.
* What does the CAP theorem say? During a network partition, a distributed system must choose between consistency (refuse requests it cannot answer correctly) and availability (answer anyway, possibly with stale data). With no partition, it can have both.
* What is NewSQL? Relational databases with SQL and transactions that spread data across many machines, such as Spanner, CockroachDB and YugabyteDB. They choose consistency during partitions.
* Should a project use one database type? Often not. A common setup is relational for core records, a key-value store for sessions and caches, and a time series database for metrics.
## Glossary
| Term | Meaning |
| - | - |
| Data model / database type | A pattern for organizing data: tables, documents, graphs and so on. |
| Flat file | Plain text with one record per line and fields split by a delimiter. |
| Hierarchical database | Records in a tree; each record has exactly one parent. |
| Network database | Records linked in owner–member sets; a record can have several owners. |
| Relational database | Tables of rows and columns, linked by keys and queried with SQL. |
| NoSQL | Umbrella name for non-relational databases; usually read as "not only SQL". |
| Key-value store | Stores opaque values under unique keys; looks up by key only. |
| Document store | Stores self-describing documents (usually JSON) it can index and query. |
| Embedding | Putting related data inside a document instead of referencing another record. |
| Graph database | Stores nodes, edges and their properties; queries follow edges. |
| Column-oriented database | Stores each column together on disk; fast for aggregates over many rows. |
| Wide-column database | Stores rows by row key, each with its own flexible set of columns. |
| Time series database | Optimized for appending and querying timestamped values. |
| Downsampling | Replacing detailed old data points with coarser aggregates. |
| Shard | One piece of a dataset that has been split across machines. |
| Partition (network) | A failure that splits machines into groups that cannot reach each other. |
| CAP theorem | During a partition, a distributed system must choose consistency or availability. |
| NewSQL / distributed SQL | Relational, transactional databases that scale across many machines. |
| Multi-model database | One system that supports several data models. |
| Source of truth | The one database whose copy of the data wins; other databases get copies from it. |
| Change data capture (CDC) | Reading a database's change log to copy each change to other systems. |
## For AI agents
You are reading the Markdown export of `/writing/database/comparing-database-types`. The web page has the same text plus interactive figures; nothing factual is missing here. The site index for agents is at `/llms.txt`. Related pages: `/writing/database/what-is-a-database` (databases in general) `/writing/database/relational-model` (tables, keys and constraints in depth) and `/writing/database/cap-theorem` (CAP and PACELC in depth).
***
Adapted from Prisma's Data Guide, ["Comparing database types"](https://www.prisma.io/dataguide/intro/comparing-database-types), with corrections. This page explains CAP as a choice made only during a partition. It notes that MemSQL is now SingleStore and that only some NewSQL engines are in-memory. It drops FaunaDB, whose service shut down in 2025, and Calvin, a research protocol rather than a product. It adds the recursive-SQL caveat for graph queries. Product promotion and the unsourced claim that relational databases are the majority have been left out. Figures are simplified teaching models; the time series numbers are illustrative.
This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.