fiveninesGuided learning track
PostgreSQL-inspired relational database built from sessions, SQL parsing, binding, planning, execution, heap storage, buffers, WAL, transactions, MVCC, indexes, vacuum, recovery, catalogs, extensibility, replication, and partitioning.
Promises And Server Shape · Tutorial 1 of 25
Start from database promises, then introduce server processes, shared memory, protocol sessions, and session lifecycle boundaries.
A database earns trust by defending three contracts under pressure: acknowledged changes remain recoverable, concurrent state stays coherent, and declarative questions preserve their meaning as execution routes change.
PostgreSQL presents one database through many backend processes: each connection owns private working memory, while backends and background processes coordinate through shared memory and durable files.
A PostgreSQL connection is a state machine: startup establishes identity, normal messages advance explicit query and transaction states, and errors, cancellation, and termination each have defined recovery consequences.
Turn SQL text into relational meaning through relations, parsing, binding, names, types, schemas, and privileges.
SQL fixes the desired relation while leaving PostgreSQL free to choose its execution order, provided every transformation preserves duplicates, null semantics, types, and observable ordering.
Raw parsing establishes grammatical structure, not database meaning: tokens and precedence become a faithful tree while object identity, types, operators, and privileges remain unresolved.
Binding freezes SQL meaning for a statement: visible names become catalog identities, expressions receive concrete types and operators, and privileges are checked before strategy begins.
Explain how analyzed queries become logical alternatives, costed physical plans, and iterator-style executor tuple flow.
Logical planning may rearrange an analyzed query only across proven semantic equivalences; predicates, joins, projections, and subqueries are opportunities until SQL or security boundaries make their order observable.
Physical planning is a decision under uncertainty: PostgreSQL prices semantically valid paths from estimated cardinalities, so correct results are guaranteed while good performance depends on statistical evidence.
Execution is demand-driven composition: plan nodes share a lifecycle and tuple interface, allowing streaming pipelines, blocking operators, visibility checks, and cleanup to cooperate without exposing their internals.
Move from tuples and pages to buffer pools, write-ahead logging, and transaction commit as a durable storage promise.
Heap storage gives each physical tuple version a page-and-item address; line pointers keep that address stable within a page, while logical row identity and version lifetime remain separate concerns.
Shared buffers maintain one coherent in-memory identity per cached page: pins protect frame lifetime, short synchronization protects contents, and the WAL rule constrains when dirty pages may cross to disk.
WAL decouples commit from data-page placement through two orderings: durable commit waits for required log records, and a dirty page may reach disk only after WAL that explains its state.
Transactions make many physical traces carry one outcome: tuple metadata and transaction status determine whether work is committed or aborted, while WAL and later cleanup operate on separate schedules.
Show how MVCC, locks, indexes, index maintenance, and vacuum coordinate concurrent access while carrying historical versions.
MVCC preserves a snapshot invariant: each reader combines tuple-version metadata with transaction outcomes to see one coherent past, while writers create future versions and vacuum later reclaims unreachable history.
Coordination primitives must match resource and lifetime: heavyweight locks defend logical claims, while LWLocks, spinlocks, and buffer content locks protect shared internals only for short critical sections.
A B-tree preserves ordered routes from keys to physical tuple candidates; it can narrow work or supply order, but heap and transaction state remain authoritative for MVCC visibility.
Index maintenance must never omit a candidate for a visible tuple; PostgreSQL may retain obsolete candidates for MVCC rechecks, use HOT chains when keys stay fixed, and remove dead routes later.
Vacuum reclaims history only after a visibility-horizon proof shows no relevant snapshot can need it, then reconciles heap space, index routes, visibility metadata, and transaction-ID age.
Connect checkpoints, crash recovery, system catalogs, functions, operators, and extensions into a self-describing database core.
A checkpoint bounds where redo must begin; recovery then compares ordered WAL with each page's state, reapplies missing effects, and restores committed history without treating the checkpoint as the commit guarantee.
PostgreSQL describes itself with transactional catalog relations: stable identities and dependencies preserve structural meaning, while locks, visibility, caching, and invalidation make live DDL coherent.
PostgreSQL extensibility is a metadata contract: implementations join typed catalog interfaces, while declared semantic properties constrain every optimization and storage structure that trusts them.
Relate isolation levels, replication streams, partitioning, and distributed table shape to ordered change and query routing.
Isolation levels define which concurrent histories applications may observe; PostgreSQL strengthens the contract from statement snapshots to stable snapshots to serializable histories enforced through possible abort and retry.
Streaming replication exports ordered history, but receipt, write, durable flush, and replay are distinct progress claims; availability, commit safety, and read freshness depend on which position the contract names.
Partitioning preserves one logical relation by proving which local children can contain a read and routing each write to one bound; indexing, replication, and sharding solve different problems.
Assemble the full PostgreSQL-like machine from client messages through durable state, visibility, metadata, and scale.
PostgreSQL is a chain of handoff invariants: session context becomes bound meaning, equivalent plans become visible tuple work, and ordered physical changes become recoverable committed history.