PostgreSQL

Part I: The mental model

psql / your app
   │  libpq wire protocol (TCP or unix socket)
   ▼
postmaster ── fork() ──▶ one backend process per connection
                              │  tcop/postgres.c  (the traffic cop)
                              ▼
                         parser      (gram.y, analyze.c)
                              ▼
                         rewriter    (views, rules)
                              ▼
                         planner     (paths + costs → plan tree)
                              ▼
                         executor    (pull tuples through plan nodes)
                              ▼
                         heap + indexes   (row versions, never in place)
                              ▼
                shared buffers (cache) ──▶ WAL (durability first)
                              ▼
                         data files on disk

The one-sentence identity: PostgreSQL is a process-per-connection server that compiles each query through a parse, rewrite, plan, execute pipeline, stores rows as immutable versions that vacuum later reclaims, and makes everything durable by logging it to the WAL before it ever touches a data file.

Three commitments define the system. First, the pipeline: every statement, from SELECT 1 to a twelve-way join, passes through the same four stages, and the source tree's directories map onto them almost one to one. Second, MVCC by versioning: an UPDATE never overwrites a row; it writes a new version and marks the old one expired, so readers and writers never block each other, and a background vacuum process pays the bill. Third, WAL first: no change reaches a table or index page on disk before a log record describing it is safely down, and that single log turns out to also be the replication stream and the point-in-time recovery mechanism.

Hold those three and everything operational about Postgres, connection pooling, bloat, autovacuum tuning, replicas, becomes a corollary rather than folklore. For the requirements-first view of when to reach for a server database at all, see my databases note; for the storage-engine spectrum this sits on, the key-value store design write-up and the RocksDB page are the B-tree-versus-LSM companion reads.

Part II: Using it

Install and first session

The quickest zero-install route is the official container (PostgreSQL 18 is current as of this writing; 14 through 17 remain supported):

docker run -d -e POSTGRES_PASSWORD=secret -p 5432:5432 postgres:18
docker exec -it $(docker ps -lq) psql -U postgres

On a real machine the package manager sets up a cluster and a postgres system user (Debian and Ubuntu ship an older major by default; the PGDG apt repository carries current releases):

sudo apt install postgresql        # or: brew install postgresql@18
sudo -u postgres createdb demo
sudo -u postgres psql demo

demo=# CREATE TABLE accounts(id bigint PRIMARY KEY, balance bigint NOT NULL);
demo=# INSERT INTO accounts SELECT g, 1000 FROM generate_series(1, 100000) g;
demo=# UPDATE accounts SET balance = balance - 100 WHERE id = 42;
UPDATE 1
demo=# EXPLAIN ANALYZE SELECT * FROM accounts WHERE id = 42;

That last command is the one to make a habit: EXPLAIN ANALYZE runs the query and prints the plan with estimated and actual costs side by side, and reading it is the daily interface to everything in Parts IV and V.

From code

The natural client library for this chapter is psycopg, the standard PostgreSQL adapter for Python, which speaks the libpq protocol:

pip install "psycopg[binary]"

import psycopg

with psycopg.connect("host=localhost dbname=demo user=postgres password=secret") as conn:
    with conn.cursor() as cur:
        cur.execute(
            "UPDATE accounts SET balance = balance - %s WHERE id = %s",
            (100, 42),
        )
        cur.execute("SELECT balance FROM accounts WHERE id = %s", (42,))
        print(cur.fetchone())
    # the with-block commits on clean exit, rolls back on exception

The mistakes everyone makes first

Mistake one: leaving transactions open. Client libraries commonly start a transaction implicitly on the first statement. A session that runs a query and then sits idle holds its snapshot open, shows up as idle in transaction in pg_stat_activity, blocks vacuum from reclaiming anything newer than its snapshot, and can hold locks. Commit or roll back promptly; set idle_in_transaction_session_timeout as a backstop.

Mistake two: assuming count(*) is cheap. Because visibility is per row version, Postgres generally has to scan to count; there is no maintained row counter. For monitoring, pg_stat_user_tables or an estimate from pg_class.reltuples is usually what you actually want.

Mistake three: string interpolation instead of parameters.

# wrong: injection plus no plan reuse
cur.execute(f"SELECT * FROM accounts WHERE id = {user_input}")
# right
cur.execute("SELECT * FROM accounts WHERE id = %s", (user_input,))

Mistake four: opening a connection per request. Every connection is a forked server process with real setup cost and memory. Web applications need a pool (client-side or PgBouncer); Part III's warning is this mistake at architecture scale.

Mistake five: adding an index and assuming it will be used. The planner is cost-based: on a hundred-row table a sequential scan is genuinely cheaper than an index scan, and on a filter matching half the table it usually is too. The habit that fixes the confusion is checking EXPLAIN ANALYZE before and after, which Lab 2 makes concrete.

Part III: When it is the right tool

PostgreSQL is the right default for a networked application's system of record: multiple services or machines sharing data, sustained concurrent writes, strong constraints and transactions, and workloads that benefit from its unusually deep feature set (rich types, partial and expression indexes, window functions, logical replication, and an extension ecosystem from PostGIS to vector search). Its habit of absorbing adjacent workloads, queue, search, analytics, document store, is often the correct boring choice at small and medium scale.

The named alternatives, and when they win: SQLite when the database and the application share one machine and one writer at a time is acceptable; an embedded file beats a server on operational simplicity every time it is sufficient. MySQL when your organization already runs it well; its clustered primary-key storage (InnoDB) and mature replication tooling remain real strengths, though the technical gap has narrowed to taste in most new-project decisions. DuckDB or a warehouse when the workload is analytical scans over columns rather than transactional point reads and writes; row-store Postgres can be made to do OLAP, but columnar engines are built for it.

The architecture-shaped warning: do not point an elastic fleet of clients directly at Postgres. One connection is one forked process holding real memory, and a serverless platform or large microservice fleet that opens connections freely will exhaust max_connections or thrash the server with process overhead long before the hardware is busy. The safe shape puts a pooler in front, so thousands of client connections multiplex onto tens of backend processes.

SAFE                                     DANGEROUS
1000s of clients                         1000s of lambdas/pods
      │                                        │ │ │ │ │
      ▼                                        ▼ ▼ ▼ ▼ ▼
  PgBouncer (pools)                        postgres: one forked
      │  ~20-50 server conns               process PER connection
      ▼                                    → max_connections hit,
  postgres backends                          memory pressure, collapse

Part IV: The full life of one UPDATE

The canonical operation for this chapter is a write, because a write exercises every mechanism a read does plus versioning, locking, and the WAL:

UPDATE accounts SET balance = balance - 100 WHERE id = 42;

Every file named below was verified to exist in the current master branch of the repository; the behavior described is stable across recent releases and was checked against the PostgreSQL 18 documentation.

Stage 1: libpq and the wire protocol

psql and psycopg both sit on libpq (src/interfaces/libpq/, with statement dispatch in fe-exec.c). A plain PQexec sends the SQL as a single simple-protocol message; parameterized calls use the extended protocol's Parse, Bind, and Execute messages, which is what keeps your parameters out of the SQL text entirely, separating code from data at the protocol level. Either way, bytes arrive at a server socket.

Stage 2: the postmaster and the fork

The listening process is the postmaster (src/backend/postmaster/postmaster.c). At connection time, not query time, it forked a dedicated backend process for this session after authentication; by the time our UPDATE arrives, a private process is already waiting on the socket. Postgres has no threads for query execution: one connection is one process, cooperating with its siblings only through shared memory (buffer cache, lock tables) and signals. Background siblings, the checkpointer, WAL writer, autovacuum launcher and workers, and WAL senders for replicas, were forked by the postmaster at startup.

Stage 3: the traffic cop, tcop/postgres.c

The backend's main loop lives in src/backend/tcop/postgres.c, and for a simple-protocol message the spine of the whole system is one function: exec_simple_query(). It is short enough to read in a sitting and calls the next four stages in order, which makes it the single best entry point into the codebase.

Stage 4: parser and analyzer

The grammar in src/backend/parser/gram.y (bison, one of the largest grammars in open source) produces a raw parse tree; semantic analysis in src/backend/parser/analyze.c then looks up accounts in the catalogs, resolves id and balance to typed references, and resolves the operators, producing a Query node. The split matters: raw parsing needs no database access at all, analysis is all catalog lookups.

Stage 5: the rewriter

src/backend/rewrite/rewriteHandler.c applies the rule system: views are expanded into their defining queries here, and row-level security policies inject their predicates here. Our UPDATE targets a plain table, so it passes through unchanged, but the stage is why a view behaves exactly like its definition; it literally becomes it before planning.

Stage 6: the planner

src/backend/optimizer/plan/planner.c drives planning. Postgres separates paths, cheap descriptions of possible strategies built in src/backend/optimizer/path/allpaths.c, from the final plan tree built only for the winner. Each path is priced by the cost model in src/backend/optimizer/path/costsize.c using statistics that ANALYZE gathered into pg_statistic. For id = 42 on a primary key, the index path wins trivially, and the plan is an index scan feeding a ModifyTable node. EXPLAIN would show:

Update on accounts
  ->  Index Scan using accounts_pkey on accounts
        Index Cond: (id = 42)

Stage 7: the executor

src/backend/executor/execMain.c runs the plan tree as a pull pipeline: each node's function asks its child for the next tuple (the classic Volcano model, one file per node type in executor/, names matching EXPLAIN output almost exactly). The index scan node descends accounts_pkey, finds the heap location for id = 42, checks that the row version is visible to our snapshot, and hands it to src/backend/executor/nodeModifyTable.c, which computes the new tuple with balance - 100 and asks the table's access method to perform the update.

Stage 8: the heap update, a new row version

Here is the load-bearing storage moment, in src/backend/access/heap/heapam.c (heap_update()). Postgres does not modify the row. It writes a complete new tuple whose xmin is our transaction ID, sets xmax on the old tuple to the same ID, and links old to new through the ctid chain. Both versions now coexist in the table's 8 kB pages, in shared buffers (src/backend/storage/buffer/bufmgr.c). If no indexed column changed and the old tuple's page has room, this is a HOT update (heap-only tuple, see src/backend/access/heap/README.HOT) and no index entry is written; otherwise every index on the table gets a new entry pointing at the new version. Our UPDATE changes only balance, which is not indexed, so HOT applies.

Stage 9: WAL record emission

Before the modified buffer page can ever be written to disk, the change is described in a WAL record assembled by src/backend/access/transam/xloginsert.c and copied into WAL buffers by the machinery in src/backend/access/transam/xlog.c. Each record gets a log sequence number (LSN), a byte position in the log, and the page is stamped with it; the buffer manager enforces the write-ahead rule by refusing to evict a data page until the WAL up to that page's LSN is flushed. If this is the first touch of the page since the last checkpoint, the record carries a full page image to defend against torn writes.

Stage 10: commit

Our implicit transaction commits in src/backend/access/transam/xact.c: a commit record is appended to the WAL, and XLogFlush forces the log to durable storage up through it (with synchronous_commit = on, the default, the client does not get success before this fsync). The transaction is then marked committed in pg_xact (src/backend/access/transam/clog.c), locks are released, and the backend sends UPDATE 1 and ReadyForQuery back down the wire. Note what did not happen: the 8 kB data page with our two row versions is still only in shared buffers, dirty. The checkpointer or background writer will get it to disk eventually; if power fails first, crash recovery replays the WAL and reconstructs it. Durability lives in the log, not the data files.

Part V: Internals deep dives

Deep dive 1: MVCC, vacuum, and wraparound

Every heap tuple header carries xmin (the transaction that created it) and xmax (the transaction that deleted or superseded it, 0 if none). A snapshot is essentially "every transaction ID below this horizon, minus this list of still-in-progress ones", taken per statement in READ COMMITTED and per transaction in REPEATABLE READ (snapshot code in src/backend/utils/time/snapmgr.c). A tuple is visible if its xmin committed before the snapshot and its xmax did not. That is the whole trick: readers never block writers because old versions remain readable, and writers never block readers because new versions are invisible until commit.

UPDATE accounts SET balance = 900 WHERE id = 42;   (txn 748)

page: [ (0,1) xmin=701 xmax=748  id=42 balance=1000 ]  old version
      [ (0,2) xmin=748 xmax=0    id=42 balance=900  ]  new version
                 │
   txn 730 (snapshot before 748): sees (0,1)
   txn 749 (after 748 commits):   sees (0,2)

The bill: superseded versions, dead tuples, stay in the file as bloat until VACUUM (src/backend/access/heap/vacuumlazy.c, entry point in src/backend/commands/vacuum.c) removes them, which it may only do once no snapshot can still see them; this is precisely why long-lived transactions cause bloat. Autovacuum triggers per table once dead tuples exceed autovacuum_vacuum_threshold (default 50) plus autovacuum_vacuum_scale_factor (default 0.2, twenty percent) of the table. And the correction worth engraving: plain VACUUM makes dead space reusable but almost never shrinks the file; only VACUUM FULL, which rewrites the table under an exclusive lock, returns space to the operating system.

Vacuum's second, non-negotiable duty is wraparound defense. Transaction IDs are 32-bit and compared circularly, so a given ID can only "see backward" about two billion transactions; a tuple whose xmin falls off that horizon would abruptly look like it came from the future. Vacuum therefore freezes old tuples, marking them visible to everyone, and autovacuum_freeze_max_age (default 200 million) forces an anti-wraparound vacuum on any table that lets its oldest unfrozen ID get that stale. Neglect this on an append-only or vacuum-starved table and Postgres will eventually refuse writes to protect itself; monitoring age(relfrozenxid) is the standard defense.

Deep dive 2: the WAL, one log wearing three hats

The WAL is a stream of records in 16 MB segment files under pg_wal/, addressed by LSN. Its first hat is crash recovery: since every page change is logged before the page can be evicted, replaying from the last checkpoint reconstructs all committed work, and checkpoints (default every five minutes, tunable with checkpoint_timeout and max_wal_size) bound the replay window by forcing dirty buffers to disk. The second hat is replication: a standby is just a server in permanent recovery, replaying WAL shipped to it by a WAL sender process (src/backend/replication/walsender.c), and synchronous replication merely means waiting for the standby's acknowledgment before reporting commit. The third hat is point-in-time recovery: a base backup plus archived WAL replayed to a chosen LSN or timestamp. Crash recovery, replicas, and PITR are not three subsystems; they are one mechanism, the log, consumed three ways. Logical decoding reads the same stream and emits row-level changes, which is how change-data-capture tools ride the WAL. The trap to correct: replication is not backup. A replica faithfully replays your accidental DROP TABLE within milliseconds; only base backups plus WAL archives let you rewind.

Deep dive 3: the planner and reading EXPLAIN ANALYZE

The planner's currency is cost, an abstract unit anchored by seq_page_cost = 1.0. The defaults in costsize.c that matter: random_page_cost = 4.0 (deliberately far below the true random-versus-sequential ratio of magnetic disks because most random reads are assumed cached; on SSDs many operators lower it toward 1.1), cpu_tuple_cost = 0.01, and cpu_index_tuple_cost = 0.005. Selectivity estimates come from ANALYZE's statistics, histograms and most-common-value lists at default_statistics_target = 100 resolution (estimation functions in src/backend/utils/adt/selfuncs.c).

The reading skill, on real output shape:

Index Scan using accounts_pkey on accounts
    (cost=0.29..8.31 rows=1 width=16)
    (actual time=0.041..0.043 rows=1 loops=1)
  Index Cond: (id = 42)
Planning Time: 0.140 ms
Execution Time: 0.070 ms

Read it in this order. First, rows estimated versus rows actual: a large mismatch means stale or insufficient statistics and is the root cause of most bad plans; run ANALYZE, then look again. Second, where the time actually went, remembering that actual time is per loop and multiplies by loops (the classic misread on nested loops). Third, the node types against your expectation: Seq Scan where you expected an index is not a bug until the row estimate says it should have been selective. Costs are unitless planning currency, not milliseconds; only actual time is time. Add EXPLAIN (ANALYZE, BUFFERS) to see shared buffer hits versus disk reads per node.

Deep dive 4: the process model and shared buffers

Postgres runs queries in processes, not threads, a Berkeley-era decision retained for the isolation it buys: a crashing backend takes out one connection while the postmaster resets shared state, and the codebase avoids whole classes of threading bugs. The prices are connection weight (hence pooling) and the constraint that everything shared must live in explicit shared memory. The centerpiece of that shared memory is the buffer cache in src/backend/storage/buffer/bufmgr.c: all table and index pages are read and modified through it, dirty pages are written back lazily by the background writer and at checkpoints, and eviction runs a clock-sweep approximation of LRU. Its size, shared_buffers, defaults to a deliberately tiny 128 MB; the standard starting point on a dedicated machine is around a quarter of RAM, not more, because Postgres reads files through the OS page cache too and the two caches double-buffer. This layered-cache effect is why "cold" query timings are so hard to reason about casually, and why BUFFERS output distinguishes hits from reads: a "read" may still be served from the OS cache without touching the disk.

Part VI: Reading the repository

Clone the mirror and live in src/backend/. The tree's README files are genuinely excellent and every path below was verified against current master.

Stage 0, orientation without code. Read src/backend/optimizer/README (the standout), src/backend/access/transam/README (transactions and WAL), and src/backend/storage/buffer/README. Questions you should be able to answer: what are the four pipeline stages and in which directories do they live? What is the difference between a path and a plan? What rule makes a log "write-ahead"?

Stage 1, the spine. Read src/backend/postmaster/postmaster.c (skim the fork logic), then src/backend/tcop/postgres.c, focusing on exec_simple_query(). Questions: at what moment does a connection get its process? Which functions correspond to parse, rewrite, plan, execute? Where would a second query in the same session re-enter?

Stage 2, front half of the pipeline. Read src/backend/parser/README, skim gram.y for one statement type, read parser/analyze.c, then rewrite/rewriteHandler.c. Questions: why is parsing split from analysis? How does a view become its definition? Where would row-level security predicates attach?

Stage 3, planner. Read optimizer/plan/planner.c top-down, then optimizer/path/allpaths.c and optimizer/path/costsize.c. Questions: where do sequential and index paths get created for a base table? Which function turns the cheapest path into a plan? Which GUCs appear as variables in costsize.c?

Stage 4, executor. Read src/backend/executor/README, then execMain.c, then one scan node and nodeModifyTable.c. Questions: how does the Volcano pull model appear in code? Where does an UPDATE actually call into the heap? How do EXPLAIN node names map to files?

Stage 5, storage and transactions. Read access/heap/heapam.c (find heap_update()), access/heap/README.HOT, storage/buffer/bufmgr.c, access/transam/xact.c, and access/transam/xlog.c last, it is the deep end. Questions: what exactly happens to xmin, xmax, and ctid on update? When is a HOT update possible? What sequence of events constitutes commit?

Where not to start: gram.y in full (tens of thousands of grammar lines with no architecture in them), xlog.c cold (it is among the hardest files in the tree; earn it via the transam README), and the src/include/ headers as a reading path, useful as reference, aimless as a tour.

Part VII: Hands-on labs

All labs run in stock psql against the demo database from Part II. Exact IDs, timings, and sizes will differ on your machine; the shapes will not.

Lab 1: watch row versions with xmin and xmax

-- session A
BEGIN;
UPDATE accounts SET balance = 900 WHERE id = 42;

-- session B (a second psql)
SELECT xmin, xmax, ctid, * FROM accounts WHERE id = 42;
--  xmin | xmax | ctid  | id | balance
--   748 |  751 | (0,42)| 42 |    1000     <- old version, xmax set by A

-- session A
COMMIT;

-- session B again
SELECT xmin, xmax, ctid, * FROM accounts WHERE id = 42;
--   751 |    0 | (0,442)| 42 |     900    <- new version, new ctid

Observe: while A is uncommitted, B still sees the old version but with a nonzero xmax, A's transaction ID staked on it. After commit, B sees the new version with xmin equal to that same ID and a different ctid: the row moved, because it was never updated in place. This is Deep dive 1 with your own eyes.

Lab 2: EXPLAIN ANALYZE before and after an index

CREATE TABLE events(id bigserial, kind text);
INSERT INTO events(kind)
  SELECT (ARRAY['signup','login','click'])[1 + g % 3]
  FROM generate_series(1, 500000) g;

EXPLAIN ANALYZE SELECT count(*) FROM events WHERE kind = 'signup';
-- Seq Scan on events ... rows≈166000 ... Execution Time: tens of ms

CREATE INDEX ON events(kind);
ANALYZE events;
EXPLAIN ANALYZE SELECT count(*) FROM events WHERE kind = 'signup';

Observe: with a third of the table matching, the planner may justifiably stick with a scan or choose an index-only scan; change the predicate to a rare value (insert one 'rare' row and query it) and watch the plan flip decisively to the index with a row estimate near 1. The lesson is Deep dive 3's: plans follow selectivity estimates, not the existence of indexes.

Lab 3: create bloat, then watch vacuum work

CREATE TABLE bloat_demo AS SELECT g AS id, 0 AS n FROM generate_series(1,100000) g;
SELECT pg_size_pretty(pg_relation_size('bloat_demo'));   -- e.g. 3544 kB
UPDATE bloat_demo SET n = 1;                             -- 100k dead tuples
SELECT pg_size_pretty(pg_relation_size('bloat_demo'));   -- roughly doubled
SELECT n_dead_tup, n_live_tup FROM pg_stat_user_tables
 WHERE relname = 'bloat_demo';
VACUUM VERBOSE bloat_demo;
SELECT pg_size_pretty(pg_relation_size('bloat_demo'));   -- barely shrinks!
VACUUM FULL bloat_demo;
SELECT pg_size_pretty(pg_relation_size('bloat_demo'));   -- back to original

Observe three facts from Deep dive 1: the update doubled the heap because every row got a second version; plain VACUUM reported removing dead tuples but the file stayed big (space is reusable, not returned); VACUUM FULL rewrote the table and gave the space back, at the price of an exclusive lock you would not take casually in production.

Lab 4: watch the WAL advance

SELECT pg_current_wal_lsn();                       -- e.g. 0/1A2B3C40
INSERT INTO accounts VALUES (999999, 0);
SELECT pg_current_wal_lsn();                       -- a bit further
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), '0/1A2B3C40');  -- bytes logged

Observe: every write moves the LSN, and the diff function tells you exactly how many bytes of WAL your operation generated. Try the same insert immediately after a CHECKPOINT; command and see the count jump, because the first touch of a page after a checkpoint logs a full page image (Deep dive 2's torn-write defense, measurable from SQL).

Lab 5: read the system's pulse in pg_stat views

SELECT pid, state, wait_event_type, wait_event, query
  FROM pg_stat_activity WHERE state <> 'idle';

SELECT relname, seq_scan, idx_scan, n_dead_tup, last_autovacuum
  FROM pg_stat_user_tables ORDER BY seq_scan DESC LIMIT 5;

SELECT * FROM pg_stat_database WHERE datname = current_database();

Observe: pg_stat_activity shows every backend process (Deep dive 4 as a table: each row is literally a forked process; look for idle in transaction), pg_stat_user_tables shows which tables are being sequentially scanned (missing indexes announce themselves in seq_scan) and how autovacuum is keeping up with n_dead_tup, and pg_stat_database's blks_hit versus blks_read is your shared-buffers hit rate.

Part VIII: Questions and model answers

Understanding checks; try each before reading the answer.

1. What happens, stage by stage, between typing an UPDATE and seeing UPDATE 1? libpq sends the text to a backend process the postmaster forked at connect time; exec_simple_query runs it through parser and analyzer (parse tree to Query), the rewriter (views, rules), the planner (paths costed, cheapest becomes a plan), and the executor, which finds the row, writes a new version, logs the change to WAL, and at commit fsyncs the WAL before reporting success.

2. What do xmin and xmax mean on a tuple? The transaction IDs that created and (if any) deleted or superseded that version. Visibility is computed per snapshot from them: a version is visible if its creator committed before your snapshot and its deleter, if any, did not.

3. Why do readers never block writers in Postgres? Because an update creates a new version instead of overwriting; readers keep using the old version their snapshot can see while the writer works on the new one. The two only meet when two writers target the same row, which does lock.

4. Why does vacuum exist, in two sentences? Versioning leaves superseded tuples in the heap, and something must reclaim them once no snapshot can see them, or tables bloat without bound. Vacuum also freezes old tuples so the 32-bit transaction counter can wrap without making old data vanish.

5. What is transaction ID wraparound and what prevents it? XIDs are 32-bit and circular, so comparisons only work within a two-billion-transaction horizon; an unfrozen tuple older than that would flip from "past" to "future" and disappear from view. Anti-wraparound vacuums, forced by autovacuum_freeze_max_age (default 200 million), freeze old tuples first, and Postgres will stop accepting writes rather than let the horizon be crossed.

6. What is a HOT update and why does it matter? When an update changes no indexed column and the new version fits on the same page, Postgres links it as a heap-only tuple and writes no new index entries. On hot tables with wide indexing this is the difference between an update costing one page write and costing one per index.

7. Why is "the WAL" the answer to three different questions? Crash recovery replays it, a standby is a server permanently replaying a streamed copy of it, and PITR is a base backup plus archived WAL replayed to a chosen point. One append-only log of page changes happens to be exactly the interface all three need.

8. Why is replication not a backup strategy? Because replicas replay everything, including your mistakes, in near real time; a dropped table is dropped on the standby moments later. Only base backups plus WAL archives (or delayed replicas as a partial mitigation) let you return to a moment before the error.

9. EXPLAIN says cost=0.29..8.31. Milliseconds? No: cost is a unitless currency anchored at seq_page_cost = 1.0, used only to rank candidate plans. Time appears only in EXPLAIN ANALYZE's actual time, and even that is per loop and must be multiplied by loops.

10. The planner ignores your new index. Name the three likeliest reasons. The predicate matches too large a fraction of the table for an index scan to beat a sequential scan; the statistics are stale so the row estimate is wrong (run ANALYZE and compare estimated versus actual rows); or the expression in the query does not match the indexed expression (functions, casts, collations).

11. A table's file barely shrinks after VACUUM. Is that a bug? No. Plain VACUUM marks dead tuple space reusable inside the file and only truncates trailing empty pages; it deliberately avoids the exclusive lock a rewrite needs. VACUUM FULL (or a tool like pg_repack) rewrites the table and returns space to the OS.

12. What does "idle in transaction" cost you? The session holds its snapshot and possibly locks, so vacuum cannot reclaim any tuple version its snapshot might still see, bloat accumulates cluster-wide, and lock waits can pile up behind it. It is the classic silent Postgres incident, and pg_stat_activity is where you catch it.

13. Why does Postgres want a connection pooler when MySQL historically shrugged? A Postgres connection is a forked OS process with meaningful memory and setup cost, while MySQL used a thread per connection. Process-per-connection buys fault isolation but makes connection churn and high connection counts expensive, so poolers multiplex many clients onto few backends.

14. When is SQLite the better choice than Postgres? When the application and data share one machine, writers are few, and operational simplicity dominates: an in-process library with a single-file database eliminates the server, the network, and the credential surface entirely. The crossover is the moment a second machine needs the data.

15. Why can't Postgres answer count(*) from a counter? Because the count depends on the observer: each snapshot sees a different subset of row versions, so no single stored number is correct for everyone. The scan is the price of MVCC; estimates live in pg_class.reltuples when approximate answers suffice.

16. How would you debug a query that is fast in staging and slow in production? Compare EXPLAIN (ANALYZE, BUFFERS) from both: look first for estimated-versus-actual row divergence (statistics differ with data), then buffers hit versus read (cache state differs), then plan shape changes driven by table size crossing a cost threshold. Fixes flow from which of the three diverged: ANALYZE or higher statistics targets, memory or prewarming, or query and index changes.

Part IX: Design lessons

Lesson 1: one durable log can power everything. Postgres derives crash recovery, replication, and time travel from a single WAL rather than three mechanisms. The same consolidation is Kafka's entire identity, RocksDB's WAL plus replication-by-log-shipping, and every event-sourced architecture: when in doubt, make the log the source of truth and everything else a replay.

Lesson 2: decide with an explicit cost model, not heuristics. The planner reduces "which strategy" to arithmetic over a handful of published constants and measured statistics, which makes its decisions inspectable (EXPLAIN) and tunable (random_page_cost) instead of magical. Query optimizers everywhere work this way, but so do good schedulers and load balancers: expose the model, and operators can reason about the system instead of appeasing it.

Lesson 3: buy isolation with processes when correctness is the product. Process-per-connection looks archaic next to thread pools and async runtimes, but it converts memory corruption in one session into a bounded incident instead of a silent cross-session data hazard. Chrome made the same trade with process-per-site, and the pattern generalizes: the more catastrophic shared-state corruption would be, the more an address-space boundary is worth its overhead.

Lesson 4: never pay costs at write time that you can defer to a janitor, but then take the janitor seriously. MVCC moves conflict cost off the hot path and onto vacuum, a deliberate asynchronous debt with a dedicated collector, tunable and observable. Garbage-collected runtimes, LSM compaction in RocksDB, and log-structured filesystems all make the same wager, and all teach the same operational corollary: the deferred work needs first-class monitoring, because it is load-bearing.

Lesson 5: store the system's own definition as data. Types, functions, operators, and index methods are rows in catalogs, which is why CREATE EXTENSION can teach a thirty-year-old server geospatial indexing or vector search without patching it. Plugin registries, Kubernetes CRDs, and LLVM's target registration are the same idea: extensibility is a data-modeling problem before it is an API problem.

Part X: Memorization framework

The one-sentence summary: PostgreSQL forks a process per connection, runs every statement through parse, rewrite, plan, and execute, writes new row versions instead of overwriting (vacuum reclaims, freeze defends the XID horizon), and secures every change in the WAL before data pages ever reach disk, with that same WAL doubling as the replication and recovery stream.

libpq → postmaster → parse → rewrite → plan → execute → heap → WAL → commit

The chain mapped to source files (all under src/):

libpq       interfaces/libpq/fe-exec.c
postmaster  backend/postmaster/postmaster.c
traffic cop backend/tcop/postgres.c        (exec_simple_query)
parse       backend/parser/gram.y, analyze.c
rewrite     backend/rewrite/rewriteHandler.c
plan        backend/optimizer/plan/planner.c,
            path/allpaths.c, path/costsize.c
execute     backend/executor/execMain.c, nodeModifyTable.c
heap        backend/access/heap/heapam.c   (xmin/xmax/ctid, HOT)
WAL         backend/access/transam/xloginsert.c, xlog.c
commit      backend/access/transam/xact.c  (flush WAL, mark pg_xact)

Memorize these blocks:

MVCC: every tuple carries xmin and xmax; update equals new version plus xmax on the old; visibility is snapshot arithmetic; vacuum reclaims dead versions and freezes old ones; wraparound horizon is about two billion XIDs, with forced vacuums at 200 million by default.

WAL: log before page, always; commit equals WAL flushed through the commit record; 16 MB segments in pg_wal; checkpoints bound replay; one log serves recovery, replication, and PITR; replication is not backup.

Planner: paths costed, cheapest planned; seq_page_cost 1.0, random_page_cost 4.0, statistics from ANALYZE at target 100; read EXPLAIN ANALYZE by comparing estimated to actual rows first; costs are not milliseconds.

Processes and memory: postmaster forks one backend per connection; shared_buffers (default 128 MB, tune to about a quarter of RAM) plus the OS page cache double-buffer; checkpointer, background writer, autovacuum, and WAL senders are sibling processes; pool your connections.