A database is not a library your program calls. It is a separate server with its own processes, memory and files, and most surprises come from forgetting that. It reads whole pages, not rows. It keeps old copies of rows you deleted. It guesses how many rows a query returns, and a wrong guess means a wrong plan. This guide covers those mechanisms — pages, indexes, plans, transactions, versions, replicas — because they decide whether a system is fast or only fast on a laptop. Claims come from PostgreSQL's public documentation; another engine may differ, and that difference is usually what breaks on the move.
Each chapter opens with the short version. Tap one to read the detail.
The server, the page, and the memory it owns
~2 min
A database server is a separate program that owns the files. It reads fixed-size pages rather than rows and already caches them — so most "we need a cache" problems are really "the working set stopped fitting" ones.
What an index actually costs
~2 min
An index trades write cost and space for read speed, so one nobody queries is pure cost. The folk rules about when an index can be used are close but not exact.
The planner is guessing — read the guess
~2 min
The engine estimates how many rows each step returns and picks the cheapest plan for that estimate. Estimates come from a sample and are approximate even when fresh, so a bad plan is usually a bad guess.
Transactions, and what isolation does not give you
~2 min
Every statement runs inside a transaction whether you asked or not. The default isolation level lets two reads in one transaction legitimately disagree — and the level above it still permits real corruption.
Old row versions, and the cleanup nobody budgets for
~2 min
Readers never block writers because each statement works from a snapshot — so an update writes a new row version and leaves the old behind. Dead versions are cleaned up later, and DELETE alone frees no space.
When everything is waiting
~2 min
Reads and writes coexist without blocking, but locks still exist and one mode blocks plain SELECTs. In an incident the slow query you see is usually a victim, not the cause.
Durability: the log is the database
~2 min
Changes reach a sequential log before the data pages they describe, so a crash is repaired by replaying it. That same log turns a file copy into a restorable backup.
Replicas, lag, and what the user sees
~2 min
A standby copies the primary and can serve read-only queries. Asynchronous replication is nearly free and admits stale reads and lost writes on failover; synchronous removes both and charges every commit.
Partitioning is only as good as your predicate
~2 min
Splitting a table into partitions helps exactly when queries filter on the partition key, because the planner can then skip whole partitions unread. Queries omitting it get the overhead and none of the benefit.
Design so two copies cannot disagree — then change it safely
~2 min
Normalisation is not about elegance; it is about storing each fact once so nothing can hold two contradictory answers. Changing that shape later is where the locks bite.
When the relational shape is the wrong shape
~2 min
A document column buys flexibility by moving validation out of the engine and into every reader. Sometimes that is right — but the schema does not disappear, and a large document becomes one unit of update contention.
Connections are processes, not sockets
~2 min
Each connection is a server process with its own memory, and the ceiling is fixed at server start and sizes shared memory. Raising it makes every connection dearer; the remedy is to keep *active* connections few.
Written by Keentune. We are not affiliated with or endorsed by the organizations whose documentation informs this guide, and any linked sources belong to their respective owners.
All exam, test, and product names and trademarks are the property of their respective owners and are used here for identification and reference only. Keentune is independent study practice — not affiliated with, authorized, or endorsed by any of these organizations.