# Chapter 10: Summary & Epilogue ## Graduation: Seeing the Whole Machine <img src="assets/arch_cloud_nirvana.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> You started with SQL. You end with a model of the machine beneath it. SQL is no longer text that disappears into a black box. You can follow a request through the machine and explain how its logical promise becomes physical work: what PostgreSQL reads, what it changes, what must become durable, what each snapshot may see, and what evidence remains when the request misbehaves. The book's central lens—PostgreSQL often avoids, batches, or defers work—is useful because it helps you predict unfamiliar behavior. It is not a law that outranks correctness. PostgreSQL also pays work for concurrency, durability, compatibility, security, and implementation history. The mature question is therefore not simply, *"How is PostgreSQL being lazy?"* It is: > **Which work is PostgreSQL avoiding, which work must it pay now, and which work is it safely leaving for later?** This chapter is your field map. It joins the nine systems you learned into one machine, gives you compact operating checklists, and ends with an incident that requires the whole model. --- ## The Entire PostgreSQL Machine on One Spread ```mermaid flowchart LR A[Application request] --> B[Connection or pool] B --> C[Backend process] C --> D[Parse and analyze] D --> E[Rewrite and RLS] E --> F[Planner estimates and costs] F --> G[Executor tree] G --> H[Scans and joins] H --> I[Shared buffers] I --> J[Heap pages and indexes] J --> K[OS page cache and storage] G --> L[Tuple versions] L --> M[Snapshot visibility] M --> N[Rows returned] N --> A G --> O[Write path] O --> P[Constraints, triggers, indexes] P --> Q[WAL records] Q --> R[Commit acknowledgement] R --> A Q --> S[Checkpointer and background writer] S --> J Q --> T[Standby or logical subscriber] L --> U[Dead tuple versions] U --> V[Vacuum and freezing] V --> J C --> W[Roles, privileges, policies] W --> E C --> X[Activity, waits, locks, I/O stats] X --> Y[Working, waiting, or blocking diagnosis] ``` Read the diagram in two directions. From left to right, it is the life of a request. From bottom to top, it is the evidence available during an incident. Catalogs tell you what objects and permissions exist. `EXPLAIN` tells you what work the planner chose. Activity and wait views tell you what backends report now. WAL and replication positions tell you how durability and copies are progressing. Tuple metadata and vacuum statistics tell you how much physical history remains. No single metric describes the whole machine. A high buffer-hit ratio does not prove fast queries. A NULL wait event does not prove CPU saturation. A small plan cost is not milliseconds. A committed row is not visible to every existing snapshot. A replica that is caught up does not automatically possess write authority. A policy that filters an ordinary role does not constrain a superuser, a `BYPASSRLS` role, or normally the table owner. The power is in joining the evidence. <div style="page-break-after: always;"></div> ## Two Paths Through the Machine The diagram is the topology. To carry the machine in your head, follow two routes through it. A **read** follows a question until PostgreSQL can return the right visible rows. A **write** follows a promise until PostgreSQL can acknowledge a change that will survive the failures covered by its durability policy. Almost every mechanism in this book lives somewhere along those two paths. ### The Read Path: From Question to Visible Rows ```text Client → connection or pool → backend → parse, analyze, rewrite, and authorize → planner → executor tree → access paths → buffers and storage → snapshot-visible tuples → result protocol → application ``` **Understand the question.** A client reaches a PostgreSQL backend directly or through a pool. The backend parses the statement, resolves names and types, applies rewrite rules and row-security qualifications, and checks whether the role may perform the requested work. PostgreSQL now understands the question; it has not yet proved the answer cheap. **Choose the work.** The planner uses statistics, costs, available indexes, ordering, partitioning, parallelism, and the shapes of joins and aggregates to choose an executor tree. Its costs compare plausible strategies; they are not milliseconds and they are not a promise that the estimates are right. **Find visible rows.** The executor pulls tuples through that tree. Access paths reach heap and index pages through shared buffers, the operating-system cache, and storage. Filters discard candidates, joins combine them, and sorts and aggregates reshape the stream. Physical presence is not enough: the statement's snapshot decides which tuple versions count as visible. **Cross the last boundary.** PostgreSQL returns rows through the client protocol. The executor may finish quickly while the user still waits for pool acquisition, network transfer, result consumption, or surrounding application work. The database clock is evidence, not automatically the whole request clock. > [!NOTE] Plans Find; Snapshots Decide > **The plan decides how PostgreSQL searches. The snapshot decides which tuple versions that search may return.** ### The Write Path: From New Version to Durable Promise ```text Client → backend → parse, analyze, rewrite, authorize, and plan → produce or locate tuples and coordinate with concurrent work → constraints and triggers → heap and index changes → dirty buffers and WAL → commit outcome → durability acknowledgement → later page flushing, replication, and vacuum ``` **Produce, find, and coordinate.** The executor produces an `INSERT` row or locates visible target versions for `UPDATE` or `DELETE`, then coordinates with concurrent work. A conflicting transaction may make the backend wait before it is allowed to change the next version. **Create the change.** An `INSERT` creates a heap tuple, an ordinary heap `UPDATE` creates a new version rather than overwriting the old user payload, and a `DELETE` retires a version without immediately erasing it. PostgreSQL evaluates applicable constraints and triggers, maintains affected indexes, and dirties heap and index buffers. For ordinary logged tables, it also generates WAL. When indexed values are unchanged and the page has room, HOT may let an update avoid new index entries. Older snapshots may continue to see a retired version. **Make the promise durable.** The write-ahead rule requires the WAL needed to recover a page change to reach the appropriate durability boundary before that dirty page may be written. COMMIT records the transaction's outcome as a unit; it does not revisit and stamp every changed tuple. The acknowledgement boundary depends on the durability and synchronous-replication policy actually configured, while snapshots still decide when the committed versions become visible to each transaction. **Finish safely later.** Dirty data pages may be written after acknowledgement. Physical standbys can receive and replay WAL; logical subscribers can decode and apply selected changes. After no relevant snapshot can need the retired versions, vacuum can reclaim their space. Following a crash, recovery replays durable WAL whose effects had not reached the data files; uncommitted tuple versions remain invisible and become later cleanup work. > [!NOTE] WAL First; Pages Later > **At a durable commit boundary, WAL makes the change recoverable before dirty data pages must be flushed. Replication and physical cleanup may finish later.** Those are the two routes. During an incident, you do not need to narrate every arrow. You need to locate where the work stalled, identify which promise actually applied, and find the evidence that can prove it. <div style="page-break-after: always;"></div> ## The Working, Waiting, or Blocking Decision Tree ```mermaid flowchart TD A[The system is slow] --> B[Define the slow request and time window] B --> C[Is it queued before PostgreSQL?] C -->|Yes| D[Inspect app queue, pool, network, routing] C -->|No or unknown| E[Inspect pg_stat_activity and historical query stats] E --> F{Reported database state} F -->|Named wait| G{Wait class} F -->|No named wait| H[Corroborate running vs runnable/off-CPU] G -->|Lock| I[Follow pg_blocking_pids and transaction age] G -->|IO| J[Identify exact I/O operation and cache/storage path] G -->|LWLock| K[Identify contended shared structure and concurrency] G -->|Client| L[Check result transfer or idle transaction] G -->|IPC| M[Inspect workers, parallel plan, process role] G -->|Activity| N[Often normal for background role; check progress/lag] H -->|Sustained CPU| O[Inspect plan, row volume, expressions, algorithms] H -->|Runnable but unscheduled| P[Reduce host/process concurrency] H -->|Unknown| Q[Collect OS profile or optional high-resolution trace] I --> R[Fix root transaction, ordering, timeout, or DDL] J --> S[Fix access path, memory residency, spill, or storage] K --> T[Fix demonstrated hot structure or concurrency source] O --> U[Repair plan/query/schema and re-measure] ``` ### The first five questions during an incident 1. **What exactly is slow?** Name the endpoint, query fingerprint, database, role, and time interval. 2. **Where is the request spending time?** Separate application queue, pool acquisition, network, server execution, commit, and result consumption. 3. **Is the backend working, waiting, or making others wait?** A NULL wait event is not enough to call it CPU. 4. **What changed?** Plan, statistics, data distribution, schema, workload concurrency, configuration, storage behavior, deployment topology, or permissions. 5. **What evidence will falsify the leading hypothesis?** Choose the next observation before choosing the fix. ### The order of intervention Contain user harm first when authorized: stop an unsafe deployment, apply a narrowly scoped timeout, cancel a proven runaway query, or terminate a proven root blocker with awareness that its transaction will roll back. Preserve evidence. Then repair the cause and repeat the same measurement. Do not tune by noun. "It is I/O" is not a remedy. `DataFileRead`, `WALSync`, temporary-file writes, control-file activity, and relation extension describe different mechanisms. "It is locks" is not a remedy. A row-version conflict, AccessExclusive DDL, advisory lock, and internal lightweight lock have different owners and fixes. <div style="page-break-after: always;"></div> ## Index Choice Cheat Sheet | Shape of the question | First candidate | Why it can fit | What to challenge | | :--- | :--- | :--- | :--- | | Equality, range, ordering, prefix | B-tree | Ordered, general-purpose search path | Selectivity, write tax, expression/collation match | | Many searchable elements inside one value | GIN | Inverted mapping from element to rows | Update cost, pending list, supported operators | | Overlap, containment, nearest-neighbor families | GiST | Extensible tree of predicates/bounds | Lossy rechecks, opclass semantics, data distribution | | Huge physically correlated table | BRIN | Summarizes page ranges compactly | Correlation, range size, false positives, maintenance | | Approximate vector neighbors | HNSW or IVFFlat through pgvector | Trades recall/build/write/storage for faster search | Exact operator/opclass pairing, recall, filters, extension version | | A stable subset of rows | Partial index | Avoids indexing irrelevant rows | Query predicate must imply index predicate | | Query needs extra output columns | Covering B-tree with `INCLUDE` | Can avoid heap visits on all-visible pages | Visibility map state, tuple size, write amplification | | Search wraps the column in an expression | Expression index or predicate rewrite | Matches the actual searchable expression | Immutability, exact expression match, simpler range alternative | | Parent delete/update searches child FK values | Index on child foreign-key column | Avoids searching the child relation broadly | Not automatic; write/read workload may not need every FK indexed | The table is a hypothesis generator, not a vending machine. Validate with the real operator family, workload, data distribution, write rate, and `EXPLAIN (ANALYZE, BUFFERS)`. Small tables and unselective predicates often make a sequential scan the honest choice. ### Before adding any index - Name the query and predicate it serves. - Record the current plan and runtime evidence. - Estimate selectivity and expected index size. - Confirm operator, collation, data type, and opclass semantics. - Account for INSERT, UPDATE, DELETE, WAL, vacuum, backup, and cache costs. - Decide how you will detect that the index is unused or redundant. - Re-measure after creation under representative reads **and** writes. <div style="page-break-after: always;"></div> ## Production Health Checklist This is not a universal dashboard. It is a set of questions that prevents one metric from pretending to be the system. ### Workload and plans - Are the highest-total-time, highest-mean-time, and highest-call-count query fingerprints known? - Do plan estimates diverge materially from actual rows at an early node? - Are statistics fresh for rapidly changing or skewed columns? - Are temporary spills, parallel-worker shortages, or repeated loops visible? - Are query plans compared with settings, schema, and data distribution from the same environment? ### Transactions and concurrency - Are there old transactions, old snapshots, or idle-in-transaction sessions? - Which sessions are direct blockers, and what business operation owns them? - Are lock and statement timeouts intentional per workload rather than globally copied? - Do application transactions avoid network calls and unrelated work while holding locks? - Is server concurrency bounded, with pool queueing and saturation observable? ### Storage and maintenance - Are shared-buffer misses interpreted alongside OS cache and physical I/O evidence? - Are table/index growth, live rows, dead tuples, free space, and churn considered together? - Is autovacuum keeping up per table, and are workers being blocked or canceled? - Are freeze ages and wraparound protection monitored? - Are WAL generation, full-page images, checkpoints, writes, syncs, and archive/slot retention understood as a pipeline? ### Replication and availability - Are send, receive, write, flush, and replay LSNs monitored separately? - Are recovery conflicts classified through `pg_stat_database_conflicts`? - Is hot-standby feedback's bloat trade-off monitored where enabled? - Are synchronous standby names, quorum/priority, and `synchronous_commit` tested together? - Has failover been rehearsed for clean crash, DCS loss, partial partition, stale routing, failed demotion, and client reconnect? - Are recovery point and recovery time measured from fault injection rather than promised by architecture diagrams? ### Security and audit - Does the application login lack superuser, `BYPASSRLS`, unnecessary role memberships, and table ownership? - Are membership `INHERIT`, `SET`, and `ADMIN` options intentional? - Are default privileges tested from the actual object-creating role? - Are RLS policies tested for SELECT, INSERT, UPDATE, DELETE, owner behavior, and trusted tenant context? - Do `SECURITY DEFINER` functions pin a safe `search_path`, schema-qualify objects, and restrict EXECUTE? - Are audit records sent to storage whose administration, retention, and integrity are independent of the actors being audited? ### Reproducibility - Can a new reader or engineer start the supported PostgreSQL version from a clean checkout? - Are extension and vendor versions pinned or date-qualified? - Can the core lab environment be reset to a known state? - Can destructive, privileged, restart, kernel, vendor, and optional-tool exercises be distinguished before execution? <div style="page-break-after: always;"></div> ## What This Book Deliberately Did Not Cover Understanding a boundary is part of understanding the system. This book gives you a physical and operational mental model, not exhaustive mastery of every PostgreSQL subsystem. It did not attempt comprehensive treatment of: - PostgreSQL source development, extension ABI details, or the contribution workflow; - every planner transformation, statistics subsystem, index access method, or operator-class implementation; - serializable retry design, two-phase commit, distributed coordinators, or every logical-replication conflict; - backup catalogs, point-in-time recovery operations, and disaster-recovery governance; - encryption keys, host and network hardening, secrets lifecycle, or full compliance design; - every managed service, ORM, driver, pooler, proxy, hardware platform, or workload-capacity model. That is intentional. A strong mental model tells you where the next boundary is and what source to consult, without pretending the map is the territory. ## Where to Go Next Choose the next subject based on the work you actually do: - **Performance engineering:** deepen statistics, extended statistics, plan caching, JIT, parallel query, `pg_stat_io`, OS profiling, and representative workload design. - **Storage and maintenance:** study heap/index page source, HOT chains, free-space and visibility maps, vacuum scheduling, freezing, WAL resource managers, and recovery tests. - **Reliability:** build and restore base backups, rehearse point-in-time recovery, measure replication data loss windows, and inject HA faults. - **Security:** model roles and ownership, test RLS as the real login role, review function execution contexts, and design independently retained audit evidence. - **PostgreSQL development:** build PostgreSQL, read the source directories cited at each chapter end, run regression tests, and trace one SQL statement through parser, planner, executor, WAL, and cleanup code. - **Teaching:** reuse the book's strongest loop—Predict → Observe → Explain → Repair → Generalize—and require learners to state where each metaphor stops. The companion repository lets you reopen the Cafe, reset the labs, and test the book's claims for yourself. <div style="page-break-after: always;"></div> ## Final Incident: The Missing Order, the Slow Report, and the Growing Primary It is 09:05 on Monday. Three alerts arrive together: 1. Customers occasionally see **Order Not Found** immediately after checkout. 2. The reporting replica cancels long dashboard queries with **conflict with recovery**. 3. The primary's `orders` table and indexes are growing much faster than live order count. The tempting explanation is "replication is broken." That is too vague to be useful. Checkout cannot be taken offline. Confirmation reads must stop lying to customers. Reports may be fifteen minutes stale, and the primary has 12% disk headroom. ### Evidence from the Machine On the primary: ```sql SELECT application_name, state, sync_state, sent_lsn, write_lsn, flush_lsn, replay_lsn FROM pg_stat_replication; SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count FROM pg_stat_user_tables WHERE relname = 'orders'; ``` On the standby: ```sql SELECT datname, confl_lock, confl_snapshot, confl_bufferpin, confl_deadlock FROM pg_stat_database_conflicts; SHOW max_standby_streaming_delay; SHOW hot_standby_feedback; ``` Application traces show that checkout writes to the primary, then the redirected confirmation request reads from a randomly selected asynchronous replica. The reporting replica has `hot_standby_feedback = on`. One dashboard holds a snapshot for forty minutes. Conflict counters show snapshot conflicts before feedback was enabled and occasional lock conflicts afterward. Primary bloat grows during long reports. Two cautions belong in your notebook. `pg_stat_database_conflicts` is cumulative since its statistics reset, so its counters do not by themselves timestamp this morning's cancellations. The replication positions are a current sample, not the exact coordinates of every missing-order request. ### Write the Graduation Incident Plan 1. **What is known?** Separate facts directly established by traces, positions, counters, and settings. 2. **What is inferred?** Build the smallest causal model that explains all three alerts without pretending they must share one root cause. 3. **What is not yet known?** Name the missing evidence that could change the response. 4. **What do you observe next?** Choose the next measurement with the highest power to distinguish competing explanations. 5. **What do you do now?** Protect checkout, reporting, and disk headroom without promising impossible consistency. 6. **What do you change for good?** Assign explicit freshness, recovery, query-duration, and cleanup policies to each workload. 7. **How do you roll it out?** Define verification, failure signals, and rollback for every routing or database change. > [!IMPORTANT] Make the Call Before the Last Click > State which evidence is fact, which is inference, and what would prove your explanation wrong. The Cafe can survive uncertainty; it cannot survive certainty borrowed from one counter. <div style="page-break-after: always;"></div> ### Graduation Debrief: Three Promises Were Sharing One Replica #### What the Evidence Establishes - Checkout writes to the primary and immediately reads from a randomly selected asynchronous replica. - At least one reporting query holds a standby snapshot for forty minutes. - `hot_standby_feedback` is enabled on that reporting replica. - Conflict counters contain snapshot conflicts from before feedback and lock conflicts from afterward. - Primary physical growth correlates with the long reporting periods. These facts establish an unsafe freshness policy, a long snapshot, multiple recovery-conflict classes over the observed statistics lifetime, and a cleanup-risk mechanism. They do not yet prove the exact conflict behind every cancellation or quantify how much of the primary's growth is reclaimable bloat. #### The Leading Causal Model Three mechanisms explain the alerts without squeezing them into one magic failure: - **Missing order:** the confirmation path has no read-your-writes policy. The chosen asynchronous replica sometimes has not replayed the checkout commit. - **Canceled reports:** hot-standby feedback can reduce cleanup/snapshot conflicts, but it cannot eliminate AccessExclusive lock, buffer-pin, tablespace, database, or deadlock conflicts. Each cancellation must be matched to its class and time window. - **Growing primary:** the long standby snapshot can advance a feedback horizon slowly enough to delay primary cleanup, allowing dead versions and associated index churn to accumulate. One knob cannot maximize freshness, long-query survival, low primary bloat, minimal write latency, and fast failover simultaneously. #### What Remains Unknown Correlate each missing-order trace with its commit WAL position and the selected replica's replay position. Capture conflict-counter deltas around current cancellations, inspect logs, and identify the conflicting WAL operation. Measure cleanup horizons, dead tuples, relation and index growth, vacuum progress, and slot or feedback retention before assigning every byte to the reporting snapshot. The most discriminating freshness observation is whether the selected replica had replayed the specific checkout commit when it returned `Order Not Found`. The most discriminating cancellation observation is the conflict class for that cancellation—not the largest cumulative counter. #### Protect the Cafe Now Keep checkout confirmation reads on the primary for a bounded window, or carry a conservative post-commit WAL token and route or wait only on a replica that has replayed at least that position. Stop assigning long reports to the HA standby if they threaten its recovery objective. Bound or pause the forty-minute report while disk headroom is assessed, then vacuum only after the horizon-holding cause is removed. Do not enable, disable, or enlarge every standby delay globally from one morning's evidence. Do not rebuild a growing table while the cause still pins its history. Do not claim zero-loss failover from an asynchronous freshness workaround. #### Give Each Workload Its Own Promise 1. **Checkout** gets an explicit read-your-writes route and a tested behavior when no replica has reached the required position. 2. **The HA standby** gets a short replay-delay budget and prioritizes recovery readiness. 3. **Long reports** move to a dedicated reporting copy with a stated freshness objective, bounded query duration, and monitored feedback/bloat trade-off—or to a logical or analytical system whose retention behavior fits the workload. 4. **DDL deployment** uses scheduling and timeouts appropriate for standby lock conflicts. 5. **Primary cleanup** is monitored against snapshot, slot, and feedback horizons; physical rewrites occur only when reusable space is insufficient and the lock, WAL, disk, and replication costs are acceptable. #### Roll Out, Verify, and Retreat Deliberately Roll out checkout routing by a small traffic cohort. Inject replay lag and verify that confirmation never reports an acknowledged order missing: it should use the primary, wait within budget, or return an honest retry state. Roll back if the policy overloads the primary, exceeds latency budgets, or cannot preserve the intended commit semantics. Move reports one workload at a time. Verify replay lag, recovery-conflict deltas, primary dead-tuple and relation growth, report freshness, and failover readiness. Roll back or shorten the reporting policy if cleanup horizons or recovery objectives deteriorate. After the horizon clears, confirm that vacuum creates reusable space before scheduling a rewrite merely to make a filesystem graph prettier. ### Generalize The incident spans nearly the whole book: - Chapter 2 explains why old tuple versions exist and why a snapshot can need them. - Chapter 5 explains the commit and WAL positions. - Chapter 6 explains cleanup capacity and physical growth. - Chapter 7 separates the working query, the recovery wait, and the blocker. - Chapter 8 explains asynchronous freshness, recovery conflict classes, and feedback trade-offs. - Chapter 9 asks whether the routing and reporting roles see only what they should. That is graduation: not memorizing a magic parameter, but connecting physical storage, transactions, resources, waits, architecture, and identity into one causal explanation. --- ## The Last Click You are no longer treating PostgreSQL as a black box that accepts queries and returns rows. You can ask better questions: - What fact is the schema trying to represent once? - Which tuple version is visible, and why? - Which access path matches the shape of the question? - What did the planner believe, and where was it wrong? - Which work must finish before acknowledgement, and which can safely happen later? - Is this backend working, waiting, or making others wait? - Which coordination boundary is the architecture trying to control? - Which role, ownership edge, or policy decides access? - What observation would prove this explanation wrong? Those questions travel well. They survive new hardware, new data distributions, new PostgreSQL minor releases, and new vendors because they are questions about mechanisms and evidence. %% ### Final References - [PostgreSQL 18 Documentation](https://www.postgresql.org/docs/18/) - [PostgreSQL Source Code](https://git.postgresql.org/gitweb/?p=postgresql.git) - The chapter-end **Sources & Further Reading** sections provide subsystem-specific exits into documentation, source directories, extension references, and versioned vendor material. %% %% --- | ← Previous | ↑ Table of Contents | Next → | | :--- | :---: | ---: | | [[Manuscript/09 - Identity & Access Control/9.6 - Summary (Identity & Access Control)|9.6 Summary (Identity & Access Control)]] | [[Manuscript/00 - Introduction/Index|Home]] | | %%