Fundamentalsdbmstablesrowscolumnsschema

What a Database Actually Is

A database is a program that owns your data’s durability, concurrency and access paths so that your application does not have to — and every feature it has is there to make one of those three cheaper or safer.

▶ Interactive Interview question See how this works internally →

Progress

Three jobs, not one

Strip the marketing away and a database management system does three things a file cannot. It survives failures: a write that has been acknowledged is still there after the power goes. It serves many clients at once without them corrupting each other’s work. And it finds rows fast — a specific row among a billion in a handful of disk reads, not a scan.

Everything else — SQL, transactions, indexes, replication, the planner — is machinery for one of those three. When you evaluate any database feature, or any database, ask which of the three it improves and what it costs the other two. That framing will carry you through this whole domain.

A table is a set of rows with the same shape. A row is one record. A column is one attribute with a declared type. A schema is the set of tables, their columns, and the rules that relate them. None of these are physical: the engine decides how rows are laid out on disk, and it is allowed to change its mind.

Durability is paid for with the write-ahead log and an fsync per commit — see Transactions and ACID.
Concurrency is paid for with locks and row versions — see MVCC: Multi-Version Concurrency Control and Locks and Deadlocks.
Access paths are paid for with indexes, which cost storage and write speed — see Why Is This Query Slow? Indexes.

What happens to your query

A SELECT arrives as text over a connection. The parser turns it into a tree and resolves names against the catalog. The rewriter expands views and *. The planner enumerates ways to execute it, estimates each one’s cost from statistics, and picks the cheapest. The executor runs that plan by pulling rows through a tree of operators. Pages come from the buffer cache; a miss reads from storage.

The planner never runs anything and the executor never chooses anything. That separation is why EXPLAIN exists: it shows you the plan without executing it, and EXPLAIN ANALYZE executes it and shows you both the estimates and the reality. Learning to read that output is the single most valuable skill in this domain — see Reading EXPLAIN ANALYZE.

The path of a query

UserLLMAgentToolDataDecisionHumanGuardrail

Keys, constraints and what they buy you

A primary key identifies a row. A foreign key says "this value must exist as a primary key over there", and the database refuses writes that would break it. A unique constraint says no two rows share this value. A check constraint is an arbitrary rule on a row. NOT NULL is the most useful constraint of all, because a column that can be NULL has three states and every query over it has to reason about the third.

Constraints look like bureaucracy and are the opposite: they are the cheapest tests you will ever write, run on every write, forever, by a system that never forgets. An application bug can produce an order for a customer that does not exist for as long as it takes someone to notice; a foreign key produces an error in the request that caused it.

Every constraint that needs to be checked fast is backed by an index. PRIMARY KEY and UNIQUE create one automatically. FOREIGN KEY does not index the referencing column — a fact that is behind a remarkable number of slow queries. See Index Types: B-tree, Hash, Partial, Expression, Covering, Full-Text.

A table that states its rules instead of hoping the application follows them

1CREATE TABLE orders (
2  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
3  user_id     bigint NOT NULL REFERENCES users(id),
4  status      text   NOT NULL CHECK (status IN ('pending','paid','shipped','cancelled','refunded')),
5  total       numeric(12,2) NOT NULL CHECK (total >= 0),
6  created_at  timestamptz NOT NULL DEFAULT now()
7);
8-- the FK column is NOT indexed by this DDL; add it yourself:
9CREATE INDEX orders_user_id_idx ON orders (user_id);

Where the bytes live

Rows are stored in fixed-size pages — 8 kB in PostgreSQL. A page holds as many rows as fit, so a narrow table packs hundreds per page and a wide one packs a handful. The engine reads and caches whole pages, never single rows, which is why row width matters for performance: every extra column means fewer rows per page and more pages per query.

The buffer cache holds recently used pages in memory. A working set that fits in the cache runs from RAM; one that does not runs from disk, and the difference is two orders of magnitude. "Add more RAM" is a legitimate database optimisation exactly because of this, and "why did it get slow when the table grew past X" is often the working set outgrowing the cache.

8 kB page, ~100–300 rows per page for a typical table, ~200 index entries per index page.
Sequential reads of adjacent pages are cheap; random reads of scattered pages are expensive. The planner’s cost model encodes this as 1.0 vs 4.0.
A SELECT * on a 40-column table reads all 40 columns off the page even if you use three — row storage has no way to skip them. Columnar stores exist for exactly this reason; see SQL vs NoSQL: Choosing a Data Model.

Key points

A DBMS exists to provide durability, concurrency and fast access paths; every feature serves one of the three at a cost to another.
Parser → rewriter → planner → executor. The planner chooses from statistics; the executor runs the choice. EXPLAIN shows the choice.
Constraints are tests that run on every write forever. Use them.
Foreign keys are not automatically indexed on the referencing side.
Data lives in pages; the buffer cache decides whether a query runs from memory or disk.

What the engine does with one query

One query, six stages

SELECT * FROM users WHERE email = '…' — step through what the engine does with it.

Where a query goes

1/6Connection

The client sends the query text over an existing connection. It is text — the server has no idea yet whether `users` exists.

A PostgreSQL connection is a separate OS process with its own memory. Opening one costs a few milliseconds and a few megabytes, which is exactly why a connection pool exists and why "too many connections" is a database outage, not an app hiccup.

What is on the wire

SELECT * FROM users WHERE email = 'jonas.olsen7@example.com';

1/6 · Connection

When to use — and when not

Use it when

You need data to survive process and machine failures.
More than one client reads and writes the same data.
You need to find specific rows among many quickly and repeatedly.

Avoid it when

A single-process tool with a config file’s worth of state.
Append-only logs that are only ever read sequentially — a file or an object store is simpler and cheaper.
Ephemeral state that can be lost on restart without consequence.

Failure modes

Treating the database as a dumb store and re-implementing constraints, locking or indexing in the application, badly.
Ignoring row width and page count until the working set stops fitting in memory.
Reading EXPLAIN without ANALYZE and trusting estimates that are wrong.