Build Your Own PostgreSQL
The Whole Machine
What You Will Learn
- Trace one request from protocol state to a durable result
- Name the invariant preserved at each architectural handoff
- Diagnose failures by locating which layer's promise was violated
The path, not the parts list
A bookstore sends one order update and receives success. That compact exchange is credible only because a chain of layers preserves progressively stronger facts: who made the request, what its SQL means, which execution route is equivalent, which versions it may observe, what physical changes occurred, and which durable history can recover them. The architecture is best understood as those handoffs, not as a parts inventory.
Start with a client sending SQL over an authenticated session. The protocol layer knows who is speaking, which database is selected, what transaction state exists, and whether the server is ready for another message. This context matters before any parsing happens. The same text can mean different things under different users, search paths, settings, and transaction states.
The handoff from text to work
The parser turns text into a tree. It understands grammar, not the catalog. The analyzer binds that tree to schemas, tables, columns, functions, operators, types, and privileges. After analysis, the statement is no longer just valid SQL. It is a request against specific database objects under a specific identity. That is when planning can begin.
The planner translates the analyzed query into relational operations, explores legal alternatives, estimates cardinality, assigns costs, and chooses physical paths. It may decide to scan an index, hash a join input, sort rows, aggregate groups, or prune partitions. The chosen plan is the database's best guess about how to produce the right result cheaply enough.
End-to-end path
Trace one order update
An authenticated session sends an update inside a transaction. Raw parsing fixes grammatical shape; analysis binds the target relation, column, operator, type, and privilege. Planning selects an equivalent physical route from estimates. Executor nodes request candidate tuples, and the transaction snapshot plus heap metadata determine which version may be changed. Locks preserve logical claims while short internal primitives protect shared structures.
The update creates a heap version and required index entries in shared buffers while WAL records their effects. Commit waits for the configured WAL durability boundary, not for every data page. A later checkpoint bounds future redo, vacuum eventually proves obsolete versions reclaimable, and replication may carry the ordered history to a standby. The client-facing success is the composition of all those narrower invariants.
The handoff from work to storage
Execution turns the plan into motion. Plan nodes pull tuples from scans, joins, filters, sorts, aggregates, and modification nodes. Expression contexts evaluate predicates and computed values. Snapshots decide which tuple versions are visible. Locks and latches coordinate access to logical objects and shared structures. The executor is where abstract choices meet memory, pages, and concurrent transactions.
Storage holds those tuples. Heap pages hold row versions with transaction metadata. Indexes provide ordered routes to heap tuple identifiers. The buffer pool mediates between disk and memory, pinning pages while they are used and marking them dirty when changed. A write changes shared buffers first, but the durability story is not complete until WAL records describe the change.
The write-ahead log records changes in order. Before a dirty data page can safely reach disk, the WAL records that describe its changes must be durable. At commit, the transaction's commit record must be flushed according to the configured policy. This is how the database can acknowledge success before every final heap and index page is written. If the server crashes, recovery starts from a checkpoint and replays WAL to restore committed effects.
How visibility, metadata, and scale fit in
MVCC lets the machine hold multiple truths for different readers without lying. A tuple inserted by a new transaction may be visible to one snapshot and invisible to another. A deleted tuple may remain physically present because an old reader still needs it. Vacuum later removes versions that no valid snapshot can see and freezes old transaction metadata before wraparound becomes dangerous. The database builds its present from managed history.
Catalogs make the whole system self-describing. They tell the analyzer what names mean, the planner what indexes and statistics exist, the executor which functions to call, and security checks what privileges apply. Extensions register new types, operators, functions, aggregates, and access behavior through those catalogs. The database can grow new capabilities because its core layers communicate through metadata rather than hard-coded special cases.
Replication and partitioning widen the shape. WAL can stream to standbys, giving another server the primary's history. Logical change streams can feed other systems. Partitioning can split a logical table into physical pieces that planning, execution, and maintenance handle selectively. These features reuse the same commitments to ordered change, metadata, planning, and visibility.
The architectural takeaway
A storage library can read and write records. PostgreSQL manages conversations, meaning, cost, concurrency, history, recovery, and change propagation. Each subsystem exists because a user-facing promise would otherwise break under ordinary pressure. The useful diagnostic question is therefore not merely 'which component ran?' but 'which invariant should this handoff have preserved?'
When building a small version from scratch, the order matters. Start with the promises, not the features. Define the session boundary. Represent SQL as trees. Bind names to catalogs. Build logical and physical plans. Execute through iterator nodes. Store tuple versions in pages. Add a buffer pool before performance collapses. Add WAL before durability claims become fiction. Add transactions and MVCC before concurrency becomes guesswork. Add indexes, vacuum, recovery, catalogs, and replication as consequences of the same design.
The whole machine is less mysterious when each layer has a reason to exist. A client sees rows and command tags; inside, PostgreSQL stages a careful translation from text to durable state. Building MiniPG does not require cloning every detail. It requires reconstructing the chain of contracts that lets a relational system keep its promises under concurrency and failure.