# Chapter 6: Resource Management & Processes ## 6.0 - Memory & Disk (The Hierarchy of Inertia) <img src="assets/arch_resource_hunger.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> Every piece of data in your database has weight, and moving that weight between physical layers is the central problem of database performance. We call this the **Hierarchy of Inertia**. Postgres cannot make RAM faster or disk cheaper; it can only decide—aggressively and constantly—how to budget its limited energy to keep the most relevant data as close to the CPU as possible. Performance is not about making things "fast"; it is about minimizing the distance data must travel. ### What You'll Learn - Why Postgres uses a **Process-Per-Connection** model and the fault isolation trade-off - How the **Latency Stack** (L1 cache → RAM → SSD → HDD) governs every performance decision - How `shared_buffers`, `work_mem`, and `maintenance_work_mem` control memory allocation - Why **Autovacuum** is critical for preventing tuple bloat and XID wraparound To understand these decisions, you need a clear picture of the **Resource Hierarchy**: the physical stack of processing cores, memory, and storage that every query touches. ### The Latency Stack To understand these decisions, you must understand the physics of the machine. Hardware latency is not linear. When a worker needs data, the physical distances are enormous, and each step down the hierarchy increases the inertia by orders of magnitude: | Layer | Typical Latency | Notes | | :--------------------- | :-------------- | :----------------------------------------- | | **CPU L1 Cache** | ~1.5 ns | On-chip; 32–64 KB per core | | **CPU L2 Cache** | ~7 ns | Still on-chip; 256 KB – 1 MB per core | | **Main Memory (RAM)** | ~100 ns | DIMM; fast, but 100× slower than L1 | | **NVMe SSD** | ~100 µs | ~1,000× slower than RAM | | **SATA SSD** | ~500 µs | ~5,000× slower than RAM | | **Spinning HDD** | ~10 ms | ~100,000× slower than RAM; rotational seek | The numbers matter more than any analogy. A random HDD read is five orders of magnitude slower than fetching from RAM. The gap between "data is cached" and "data is not cached" isn't a minor inconvenience—it is a change in the physical state of the query. This is why the engine's entire design is a fight against inertia: **keep the working set in RAM**. But to use that RAM, the engine must coordinate how independent processes access it. ### The Process Model (One Backend Per Connection) Unlike many modern runtimes that use lightweight threads, PostgreSQL normally uses a **process-per-connection** model. Separate address spaces protect one backend's private memory from direct access by another. Backends also modify shared memory, however, so PostgreSQL treats an abnormal child death as a possible shared-state failure: the postmaster normally terminates sibling server processes, reinitializes shared state, performs recovery as needed, and then accepts work again. When you connect, PostgreSQL creates a dedicated backend process. A non-parallel plan is executed by that process, which can be scheduled on one CPU core at a time. A parallel plan can recruit workers, and the operating system can move processes among cores, so “one connection equals one permanent core” is not the model. If you have 100 active connections, you have 100 independent processes, each consuming OS memory and competing for CPU scheduler time. > [!NOTE] Every Connection Has Process Weight > **Concept**: A database connection is a physical operating system process, not a lightweight thread. > **Payoff**: Separate backend processes provide private-memory isolation at the cost of process and per-session overhead. A query uses multiple CPU cores only when PostgreSQL chooses and obtains parallel workers; more server cores primarily increase concurrency unless the plan can exploit them. ### Parallel Query (Multi-Core Execution) In PostgreSQL core, one query uses multiple PostgreSQL processes when its plan contains eligible **parallel query** nodes and workers are actually obtained. Extensions and external services may add other execution models. When the Planner estimates that a table is large enough to justify the overhead, it requests **Background Workers** from the OS to divide the scan across multiple cores. These workers process their portions of the table simultaneously. Workers pass tuples back through `Gather` or `Gather Merge` paths using inter-process communication. Additional workers can help until setup, tuple transfer, memory bandwidth, leader work, I/O, or skew outweighs the saved work; the crossover is workload-specific. > [!TIP] > **The limits**: The maximum number of parallel workers per query is controlled by `max_parallel_workers_per_gather` (default: 2). The total number of background workers across the entire database is capped by `max_worker_processes` (default: 8). ### What Postgres Controls Postgres can't change the hardware. What it controls is how it allocates and uses the memory it's given: - **`shared_buffers`**: PostgreSQL's shared page cache. Backends read and write through this pool. A miss must be read through the operating system, but that read may be served from the OS page cache rather than physical storage. - **`work_mem`**: Per-operation scratch space for sorts, hash tables, and bitmap builds. When an operation exceeds this budget, it **spills** sorted runs to temporary files on disk. - **`maintenance_work_mem`**: A separate, higher budget reserved for `VACUUM`, `CREATE INDEX`, and `CLUSTER`. These bulk operations benefit from larger allocations to avoid repeated disk passes. Operations that support external algorithms can spill when their effective memory limit is exceeded; other allocations and operations have different behavior and can still fail for lack of memory. A spill adds temporary-file I/O and may or may not dominate the query. --- ## 6.1 - Process Family (The Process Family) <img src="assets/arch_process_family.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> When you start Postgres, you aren't launching a single program. You're launching a **supervisor** that immediately spawns a small family of specialized worker processes. Each one has a narrow, well-defined job. Understanding who they are—and what they do between your queries—is essential to understanding every chapter that follows. You can see the family yourself: ```sql SELECT pid, backend_type FROM pg_stat_activity WHERE backend_type != 'client backend'; ``` ``` pid | backend_type -----+------------------------------ 68 | autovacuum launcher 65 | background writer 64 | checkpointer 69 | logical replication launcher 67 | walwriter ``` These five processes (plus the **Postmaster** itself, which doesn't appear in `pg_stat_activity`) are running at all times, even when no client is connected. They are the engine's autonomic nervous system. **Process family** (who reports to whom): - **Postmaster** (parent; not listed in `pg_stat_activity`) - **Client backends** — one OS process per open connection; forked on connect, exit on disconnect - **Checkpointer** — checkpoint dirty pages, advance recovery horizon - **Background writer** — proactive dirty-page flush between checkpoints - **WAL writer** — flush WAL buffers to disk - **Autovacuum launcher** — spawns workers when tables need vacuum - **Logical replication launcher** — manages logical replication workers ### The Postmaster The **Postmaster** is the parent of every other Postgres process. It is the first process that starts and the last to die. Its responsibilities are simple and strict: 1. **Listen** for incoming connections on the configured port. 2. **Fork** a new **backend process** for each accepted connection. 3. **Supervise** child processes. If a server child dies abnormally, the Postmaster normally terminates the other server processes, reinitializes shared state, performs crash recovery as needed, and starts a new process family. The Postmaster itself never executes queries. It never touches your data. It exists solely to manage the lifecycle of the processes that do. > [!IMPORTANT] > **Why this matters**: One backend cannot directly overwrite another backend's private address space. But abnormal backend death is not ignored: because all backends use shared memory, PostgreSQL takes the process family through a controlled crash-recovery cycle before resuming service. A normal session termination via `pg_terminate_backend()` is different from an abnormal crash. ### Backend Processes (One Per Connection) When you connect via `psql` or your application's connection pool, the Postmaster forks a dedicated **backend process** for your session. This process: - Parses your SQL - Plans the query - Executes it against **[[Manuscript/06 - Resource Management & Processes/6.2 - Shared Buffers (The Page Cache)|Shared Buffers]]** - Returns results to your client Each backend is an independent OS process with its own memory space (for `work_mem` sorts, hash tables, etc.) but sharing the global `shared_buffers` pool. When you disconnect, the backend exits. ### The Background Workers The remaining processes run continuously in the background, performing maintenance that keeps the engine healthy between—and during—your queries. ### Checkpointer Periodically flushes all **dirty pages** from Shared Buffers to disk and writes a **Checkpoint Record** to the WAL. After a checkpoint, Postgres knows it can recover from that point forward without replaying older WAL segments. Controlled by `checkpoint_timeout` (default: 5 minutes) and `max_wal_size`. → *Introduced in [[Manuscript/05 - Durability & Transactions/5.1 - WAL & fsync (The Durable Ledger)|5.1 The Durable Ledger]]* ### Background Writer Proactively scans Shared Buffers for dirty pages and flushes small batches to disk in quiet intervals. Its goal is to ensure that when a backend needs a free buffer, one is already available—preventing the backend from stalling to perform an expensive synchronous write. Controlled by `bgwriter_delay` (default: 200ms). → *Introduced in [[Manuscript/05 - Durability & Transactions/5.1 - WAL & fsync (The Durable Ledger)|5.1 The Durable Ledger]]* ### WAL Writer Periodically writes and flushes WAL buffers, which bounds the loss window for asynchronous commits. Backends can also perform WAL writes/flushes on demand, and concurrent committers can share a flush through group commit. `wal_writer_delay` controls the writer's normal sleep interval. → *Introduced in [[Manuscript/05 - Durability & Transactions/5.1.1 - Commit Tuning (The Loose Handshake)|5.1.1 The Loose Handshake]]* ### Autovacuum Launcher The launcher is a scheduler. It monitors table statistics and, when a table accumulates enough dead tuples, spawns **Autovacuum Worker** processes to reclaim space and update visibility maps. The number of concurrent workers is limited by `autovacuum_max_workers` (default: 3). → *Covered in depth in [[Manuscript/06 - Resource Management & Processes/6.4 - Vacuum & Freezing (The Housekeepers)|6.4 The Housekeepers]]* ### Logical Replication Launcher If logical replication is configured, this process manages the lifecycle of **Logical Replication Workers**—the processes responsible for applying changes received from a publisher to the local database. → *Referenced in [[Manuscript/05 - Durability & Transactions/5.3 - Logical Replication (The Relay Chain)|5.3 The Town Crier]]* ### Startup Process The first child the Postmaster spawns on boot. It replays WAL records to bring the database to a consistent state after an unclean shutdown. On a **replica**, the startup process runs continuously, applying WAL streamed from the primary. → *Referenced in [[Manuscript/05 - Durability & Transactions/5.2 - Crash Recovery (The Recovery Parade)|5.2 The Recovery Parade]]* ### Cumulative Statistics (Shared Memory) PostgreSQL 18 has no dedicated Stats Collector process. Backends and auxiliary processes maintain cumulative statistics in shared memory, exposed through the `pg_stat_*` and `pg_statio_*` views. Planner statistics such as value distributions are produced by `ANALYZE` and stored in catalogs; cumulative runtime counters and planner statistics are related inputs, not one collector's single dataset. ### Archiver Active when WAL archiving is configured. After a WAL segment is completed (or switched), the archiver invokes `archive_command` or an archive module and records success/failure. A valid base backup plus a continuous required WAL archive supports point-in-time recovery to reachable transaction/time/LSN targets; the destination and guarantees belong to the configured archive system. ### Logger Active when `logging_collector = on`. It captures `stderr` output from server processes and redirects it into configured log files/CSV/JSON destinations with rotation settings. Without the collector, `stderr` goes wherever the service manager or launching environment routes it; supported destinations such as syslog have their own path. ### WAL Sender / WAL Receiver Used in **streaming replication**. The **WAL Sender** runs on the primary and streams WAL records over the network to replicas. The **WAL Receiver** runs on the replica and feeds the received records to the **Startup Process** for replay. → *Referenced in [[Manuscript/08 - Scaling & Architectural Coordination/8.0 - The Three Coordination Problems|Chapter 8 - Distributed Scaling & Clouds]]* ### The Full Picture ``` postmaster (PID 1) ├── startup (crash recovery / WAL replay) ├── checkpointer ├── background writer ├── walwriter ├── autovacuum launcher │ └── autovacuum worker (spawned on demand) ├── logical replication launcher │ └── logical replication worker (spawned on demand) ├── archiver (if archive_mode = on) ├── logger (if logging_collector = on) ├── wal sender (if replicas are connected) ├── backend (connection 1) ├── backend (connection 2) └── backend (connection N) ``` Relevant server processes attach to shared memory, including the shared buffer pool; only WAL-producing work writes WAL. The Postmaster watches the family, and an abnormal server-process failure normally triggers termination of sibling processes and reconstruction of shared state rather than allowing them to continue against possibly inconsistent memory. --- ## 6.1.1 - Connection Mechanics (Long running connections and sessions) <img src="assets/arch_protocol_handshake.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> Now that the Postmaster has `fork()`ed a dedicated backend process for you (as we saw in the [[Manuscript/06 - Resource Management & Processes/6.1 - Process Family (The Process Family)|Process Family]]), how does your application actually talk to it? If you are coming from the world of web development, you are used to the **Drive-Thru** model of communication (HTTP). You drive up, shout your order into a plastic box, pay at the window, and drive away. If you want a second burger ten minutes later, you start the entire process from the beginning. The restaurant doesn't remember who you are, and it certainly doesn't keep a table reserved for you. Postgres does not work this way. Postgres is built on the **Table Service** model. When you connect to Postgres via TCP/IP or a Unix domain socket, you aren't just sending a request; you are establishing a **Session**. You sit down, the waiter (your dedicated backend process) arrives at your table, and they stay with you for the duration of the meal. They remember what you ordered first, they know if you are currently in the middle of a transaction, and they maintain your specific preferences until you decide to leave. ### The Anatomy of a Message The Postgres Wire Protocol (Version 3.0) is a stateful, message-based protocol. With the exception of the very first startup packet, every message in the protocol follows a strict, predictable physical layout: 1. **Type Byte**: A single ASCII character indicating what the message is (e.g., `Q` for Simple Query, `D` for DataRow, `C` for CommandComplete). 2. **Length**: A 32-bit integer specifying the total length of the message in bytes. 3. **Payload**: The actual data (the SQL string, the row values, or the error message). > [!NOTE] > **The Universal Translator** > The Postgres wire protocol is so stable and well-documented that it has become a de facto industry standard for database communication. Open-source projects like **`pgwire`** (a Rust library) allow developers to easily build custom databases, proxies, or analytical engines that "speak Postgres." If your custom system implements the protocol, any standard Postgres driver (`psycopg2`, `pgx`, `JDBC`) can connect to it natively. ### The Connection Lifecycle Because the protocol is stateful, the initial connection is significantly more expensive than opening a stateless HTTP connection: 1. **The Startup Packet**: The client initiates the TCP connection and sends a `StartupMessage` containing the protocol version, the target database name, and the user name. 2. **The Authentication Challenge**: The server responds with an `AuthenticationRequest` (e.g., asking for an MD5 or SCRAM-SHA-256 password hash). The client responds with the hashed password. 3. **The Backend Setup**: Once authenticated, the backend establishes session state and participates in PostgreSQL's shared process/transaction structures. It does **not** reserve `work_mem` at login; execution nodes allocate working memory on demand, and one query can have several such nodes. 4. **Parameter Status**: The backend sends down a flurry of `ParameterStatus` messages (telling the client the server's timezone, character encoding, and version). 5. **ReadyForQuery**: Finally, the backend sends the `ReadyForQuery` message. Only now is the engine ready to accept your first SQL command. > [!IMPORTANT] > **The Cost of Connection Setup**: Because setting up a session requires OS-level process forking and cryptographic handshakes, Postgres is optimized for **connection reuse**. Opening a fresh connection for every single query in a high-traffic web environment is the fastest way to paralyze your database. This is why we use **[[Manuscript/08 - Scaling & Architectural Coordination/8.4 - Scaling Connections (Connection Pooling)|Connection Pooling]]** to keep the "tables" occupied even when the clients change. ### The Simple vs. Extended Protocol Once the connection setup is complete, you can send SQL to the backend in one of two ways. #### The Simple Query Protocol (The Waiter's Pad) You send a single `Query` message containing a raw SQL string (e.g., `SELECT * FROM animals`). The backend parses the string, plans it, executes it, sends the `DataRow` messages back, and finishes with a `CommandComplete` message. This is great for one-off commands, but if you send the exact same query 1,000 times, the backend wastes CPU cycles re-parsing and re-planning the exact same text every single time. #### The Extended Query Protocol (The Pre-Printed Menu) To avoid this redundant work, modern drivers use the Extended Protocol, which breaks the process into distinct steps: - **Parse**: You send a query with placeholders (`SELECT * FROM animals WHERE species_id = $1`). The backend parses and semantically analyzes it, creating a **Prepared Statement** object. It is not yet a parameter-complete executable portal. - **Bind**: You send the parameter values (e.g., `$1 = 4`). PostgreSQL creates a **Portal** and plans as needed. Parameter values can support a custom plan; after repeated executions PostgreSQL may choose a reusable generic plan when its cost trade-off is favorable. - **Execute**: You tell the backend to execute the Portal and return the rows. The architectural payoff is that repeated executions avoid repeated parsing and semantic analysis, and a generic plan can also avoid repeated planning. PostgreSQL does not promise to skip the planner on every later execution: it can use custom plans, switch to a generic plan, or replan after relevant DDL, statistics, or `search_path` changes. ```text Parse (text + parameter types) → prepared statement Bind (values + result formats) → planning as needed → portal Execute (portal) → rows / command result ``` > [!NOTE] Where the menu metaphor stops > The pre-printed menu saves PostgreSQL from rereading and reanalyzing the wording. It does not guarantee one permanently frozen kitchen plan: the parameter values and plan-cache decision can still change how the order is prepared. ### The Power of Session State In the "Table Service" model, the connection carries **State**. This persistent state is what enables the core features of a relational database: - **Transactions**: You can start a transaction in one message (`BEGIN`), send five more messages to modify data, and then commit in a final message (`COMMIT`). The server keeps track of your "In-Progress" status. - **Session Settings (GUCs)**: You can change your `work_mem` or `timezone` for just your current session (`SET statement_timeout = '5s'`) without affecting anyone else. - **Temporary Tables**: You can create tables that exist only for the lifetime of your session and vanish the moment you disconnect. By treating the connection as a long-running conversation rather than a series of isolated shouts, Postgres provides a level of contextual consistency that stateless protocols simply cannot match. --- ## 6.2 - Shared Buffers (The Page Cache) <img src="assets/arch_shared_buffers_rack.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> > [!NOTE] Production Story: Friday at 4:55 PM (The Ice-Cold Buffers) > "We upgraded our primary database, but an analytical scan remained I/O-bound. `EXPLAIN (ANALYZE, BUFFERS)` showed many pages entering PostgreSQL's buffer pool as reads, and operating-system metrics confirmed that those reads were reaching storage rather than being satisfied by the host page cache. > > The fix was not “put all RAM in `shared_buffers`.” We measured the working set, PostgreSQL buffer residency, OS cache, and competing memory demand, then tuned the cache and query together. PostgreSQL deliberately relies on both `shared_buffers` and the operating-system page cache; a larger shared buffer pool can help some workloads, but the supported starting point for a dedicated server is commonly around 25% of RAM and values above roughly 40% often do not improve performance." > > Below is the plan operator trace of the cold `SELECT count(*)` on `orders` (captured after dropping the OS page cache), showing exactly where the wall clock vanished: > >  > > `EXPLAIN (ANALYZE, BUFFERS)` tells you *how many pages* missed the buffer pool. The plan trace proves that 99.7% of query time was spent sleeping on storage block fetches (`IO:DataFileRead`) under the `Seq Scan` operator. To minimize disk I/O, Postgres reserves a shared region of RAM at boot called **Shared Buffers**. This is a global cache that holds the most frequently accessed 8KB **[[Manuscript/02 - Physical Storage & MVCC/2.3 - The Page (The Shipping Container)|Pages]]**. Shared Buffers serves as a high-speed staging area between persistent storage and active queries. Because Postgres uses a process-per-connection architecture, this memory is allocated as shared memory (using System V or POSIX IPC). It is mapped into every backend process's address space, allowing workers to share data without duplication. When a query needs a page, Postgres first performs a high-speed lookup in the **Buffer Mapping Hash Table**: - **Buffer Hit**: The mapping table points to a slot already present in PostgreSQL shared memory. - **Buffer Miss**: PostgreSQL must read the page through the operating system into a buffer slot. The OS may satisfy that read from its page cache; a PostgreSQL miss is not proof of physical device I/O. > [!NOTE] > **In PostgreSQL Terms** > * **Shared Buffers**: The globally shared memory pool caching 8KB pages. > * **Buffer Hit**: Finding the requested page already in memory. > * **Buffer Read**: Bringing the page into `shared_buffers` through the OS. Corroborate with OS and `pg_stat_io` evidence before calling it a physical disk read. ### The Partitioned Mapping Table To avoid lock contention, the Buffer Mapping Hash Table is divided into separate partitions (typically 128). Each partition is protected by its own **Lightweight Lock (LWLock)**. This allows hundreds of backends to perform concurrent lookups without bottlenecking on a single global lock. ### Buffer Pinning When a worker finds a page, it **Pins** the buffer—an atomic reference count. While pinned, a page is protected from eviction and structural relocation. If you observe a **`BufferPin`** wait event, an exclusive operation (like `VACUUM`) is waiting for current readers to finish. ### The Clock Sweep (Eviction) Because buffers are finite, PostgreSQL must select candidates for reuse. Instead of maintaining an exact global LRU list, it uses a clock-sweep strategy with atomic counters and synchronization around buffer state and mapping. "Lock-free" is too broad; the important point is that it avoids moving every hit through one exact LRU queue. Imagine a circular array of all buffer page descriptors. A sweeping clock hand points to a single descriptor and marches around: 1. **usage_count > 0**: If a page has been read recently, the sweep hand decrements its usage count by 1 and moves to the next descriptor. 2. **usage_count == 0**: If the page has not been accessed recently (usage count is 0) and is currently unpinned, the page is selected as the eviction candidate. 3. **The Sweep Heat**: Every time a query accesses a page, it increments the page's `usage_count` (up to a maximum of 5). This ensures "hot" pages build up "heat" and require up to 5 complete clock sweeps before they can be evicted, while "cold" pages are rapidly recycled. If a selected buffer is dirty, its page must be written before that buffer slot can be reused, with WAL flushed far enough first. Dirty does not mean "uncommitted": committed and uncommitted changes can share a page. Foreground reuse can therefore add write latency when background cleaning has not supplied enough reusable buffers. ```mermaid stateDiagram-v2 [*] --> Clean : Buffer Miss (Read) Clean --> Pinned : Access Pinned --> Clean : Release Pin Clean --> Dirty : UPDATE / INSERT Dirty --> Flushing : Checkpoint / BgWriter Flushing --> Clean : Disk Sync (fsync) Clean --> Candidate : usage_count == 0 Candidate --> [*] : Evicted Dirty --> Candidate : usage_count == 0 Candidate --> Flushing : Must Flush Before Evict ``` > [!NOTE] > **Access-strategy rings**: Some bulk operations, including large sequential scans and vacuum, use access-strategy rings to limit their footprint in shared buffers. Ring size and behavior depend on operation and PostgreSQL version; the descriptors remain in the shared pool rather than becoming a private cache. You can inspect the size of the buffer pool using the `SHOW` command: ```sql SHOW shared_buffers; ``` The **`pg_buffercache`** extension provides visibility into the buffer pool, allowing you to see exactly which pages are currently resident. ### 🧪 Find the Table Filling the Buffer Cache — Lab **Identify the Buffer-Pool Hog**: "The database is slow, and we suspect a single massive table is evicting everything else from RAM. Prove which table is hogging the buffer pool." #### The Investigation To see the internal state of the Shared Buffers, we use the `pg_buffercache` extension. This provides a detailed view of the buffer pool's contents. ```sql -- Creating the thermal camera (if not already present) CREATE EXTENSION IF NOT EXISTS pg_buffercache; -- Which tables are taking up the most space in Shared Buffers? SELECT c.relname, count(*) AS buffers, round(count(*) * 8192 / 1024 / 1024, 2) AS size_mb FROM pg_buffercache b JOIN pg_class c ON c.relkind IN ('r', 'i') AND b.relfilenode = pg_relation_filenode(c.oid) GROUP BY 1 ORDER BY 2 DESC LIMIT 10; ``` ```text relname | buffers | size_mb ----------------------+---------+--------- supply_deliveries | 16384 | 128.00 order_items | 1626 | 12.00 order_items_pkey | 1541 | 12.00 orders | 740 | 5.00 orders_pkey | 276 | 2.00 pg_attribute | 180 | 1.00 idx_orders_animal_id | 140 | 1.00 pg_proc | 110 | 0.00 pg_description | 100 | 0.00 pg_statistic | 79 | 0.00 (10 rows) ``` #### The Diagnosis The snapshot shows `supply_deliveries` occupying many buffers at that instant. It does not by itself prove harmful eviction or physical I/O: compare repeated residency, other relations' hit/read behavior, `pg_stat_io`, query plans, and OS cache/device metrics. #### The Lazy Fix Identify the responsible workload and test the least costly repair: a more selective query or access path, partition pruning, scheduling, a corrected working set, or memory tuning that preserves room for the OS and per-query work. Do not add an index or enlarge `shared_buffers` from this snapshot alone. ```sql -- Checking the current size of the buffer pool SHOW shared_buffers; ``` #### More Cache Room for Hot Data By reducing the I/O footprint of the large table, you preserve space in memory for frequently accessed data. > [!TIP] > **Double Buffering**: Postgres relies on the **OS Kernel Cache** to handle writes and read-ahead. This means data often exists in both `shared_buffers` and the OS cache. While this seems redundant, it allows Postgres to manage "hot" data explicitly while letting the kernel handle broader I/O optimizations. ### 🧪 Observation Lab: Shared Buffer Cache Hit Ratios We can measure the efficiency of `shared_buffers` by executing a query and observing how page reads shift from disk (reads) to memory cache (hits) on subsequent executions. #### The Task 1. Run a query targeting a specific record, and request physical buffer usage statistics using `EXPLAIN (ANALYZE, BUFFERS)`: ```sql EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM animals WHERE id = 1234; ``` #### Warm Pages Register as Hits, Cold Pages as Reads  Examine the buffer usage in the execution plan: Here, the engine returned **`shared hit=3`**, meaning all 3 pages (the index root, the index leaf, and the heap tuple page) were already warm in the `shared_buffers` cache. No disk reads occurred. 2. To observe an empty PostgreSQL buffer pool, restart the disposable database container: ```bash # Clears shared_buffers in this disposable lab; it does not clear the host OS page cache. docker restart elephant_cafe_db ``` Now, run the query again immediately: ```sql EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM animals WHERE id = 1234; ```  If the line changes to **`shared read=3`**, PostgreSQL brought three blocks into `shared_buffers`. Those reads may have been served from the operating-system cache. Use `track_io_timing`, `pg_stat_io`, wait events, and host storage metrics to decide whether the device was involved. A second execution will commonly report hits because the pages now reside in `shared_buffers`. #### Buffer Counts Reveal Whether the Working Set Fits Buffer counts are useful evidence, but a global hit ratio is not a memory-sizing verdict. Frequent reads for latency-sensitive point lookups deserve investigation; combine them with per-query plans, `pg_stat_io`, wait events, OS page-cache behavior, and device metrics. Sequential analytical reads and one-time scans can be healthy even when they lower a ratio. --- ## 6.3 - Work Mem (Private Working Memory) <img src="assets/arch_private_desk_spill.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> While **Shared Buffers** are communal, many operations require private, high-speed scratch space. When a backend process performs a **[[Operations/ResultSet/Sort|Sort]]** or builds a **[[Operations/ResultSet/HashJoin|Hash Table]]**, it cannot use the shared buffer pool without displacing resident data and incurring high lock contention. Instead, memory-intensive operations such as sorts and hashes allocate private working memory using **`work_mem`** as a base limit. Not every plan node receives a full allocation, and hash operations can use `work_mem × hash_mem_multiplier`. ### The Tuplesort Mechanic When Postgres executes an `ORDER BY`, it initiates a **Tuplesort**. This operation attempts to organize the data entirely within the bounds of `work_mem`. - **In-Memory Sort**: If the dataset fits within `work_mem`, Postgres performs an in-memory Quicksort. - **The Disk Spill**: If the data exceeds `work_mem`, the Tuplesort transitions to an **External Merge Sort**. The engine breaks the data into chunks, writes them to temporary disk files in `base/pgsql_tmp/`, and merges them back together. Spilling to disk is a performance bottleneck. It forces Postgres to move from nanosecond-scale RAM access to microsecond-scale SSD access. A small spill can turn a sub-second query into a multi-second operation. ### The Hash Join Batches Joins also rely on this private memory. When executing a Hash Join, the engine reads the smaller table and builds an **In-Memory Hash Table** for O(1) lookups. If the hash table cannot fit its effective memory budget, the executor can split it into **Batches** and use temporary files. The extra passes add I/O, although the performance effect depends on cache, storage, row width, and batch count. ### The Multiplication Hazard Unlike `shared_buffers`, which is pre-allocated at startup, `work_mem` is allocated **per operation.** > [!CAUTION] > **The Memory Multiplication Hazard** > A single query plan can contain multiple **Sort** or **Hash Join** nodes, each allowed to consume the full `work_mem` allocation. A query with three such nodes and 100 concurrent connections could demand 300 times the `work_mem` value. Setting `work_mem` to 64MB under high concurrency could easily exhaust available RAM and trigger the **OOM Killer**. Administrative tasks like `CREATE INDEX` or `VACUUM` use a separate pool called **`maintenance_work_mem`**. Because maintenance operations usually have a different concurrency envelope, `maintenance_work_mem` is often set higher than `work_mem`. More memory can reduce the number of index-cleanup passes for vacuum and improve some index builds, but it does not make heap/index writes disappear; autovacuum workers also interact with `autovacuum_work_mem` and worker concurrency. If a query is slow due to a large sort, you can temporarily increase the memory allocation for that specific session: ```sql SET work_mem = '64MB'; EXPLAIN (ANALYZE, BUFFERS) SELECT ...; ``` If you see **`Sort Method: external merge`** or **`Hash Batches: > 1`** in the output, the allocation was too small and the engine was forced to spill to disk. ### 🧪 Eliminate an External Sort Spill — Lab **Explain the Nightly Report Slowdown**: "Our nightly supply report has suddenly become three orders of magnitude slower. We haven't changed the SQL." #### The Investigation Check the plan with `BUFFERS` to see where the data is being processed: ```sql -- Force a small work_mem to simulate the bottleneck SET work_mem = '64kB'; EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM supply_deliveries ORDER BY quantity_kg; ```  #### The Diagnosis The `work_mem` allocation was too small to hold the dataset. The engine was forced to "spill" to temporary files. The `temp read` and `temp write` lines in the output are physical evidence of this I/O. `EXPLAIN` shows *that* a spill happened. A plan trace shows *where* the wall clock went — under the `Sort` node:  Almost all wall clock is temp-file I/O under `Sort`—writing run tapes (73.8ms `IO:BufFileWrite`) and merging sorted runs (42.2ms `IO:BufFileRead`) in `base/pgsql_tmp/`. This is physical proof of why expanding `work_mem` for sorting sessions eliminates external merge thrashing. **In-memory path (`work_mem = 16MB` for the larger audit fixture):**  Same operator shape; the `Sort` is pure `CPU` (2,664.0ms). No `BufFile*` disk waits—the sorted runs stayed entirely in RAM. > [!TIP] > The trace is a normalized, illustrative fixture from the optional pg_wait_tracer track; the runnable PostgreSQL 18 proof is the literal `EXPLAIN (ANALYZE, BUFFERS)` output and its temporary-block/sort-method fields. Treat fixture timings as shape, not a benchmark promise. #### The Lazy Fix Increase the `work_mem` for this specific session to ensure the sort stays in memory. ```sql SET work_mem = '16MB'; EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM supply_deliveries ORDER BY quantity_kg; ``` #### The Sort Stays in Memory  For this controlled fixture, temporary blocks disappear and the sort method changes to `quicksort`. The elapsed-time improvement is machine- and cache-dependent; the transferable observation is that this plan no longer reports a temp-file spill. > [!NOTE] > `16MB` is a controlled, query-local setting for this 150,000-row teaching fixture—not a universal recommendation. A smaller trace fixture can fit at `4MB`; production row width, cardinality, and concurrency change the threshold. Increase memory only until the literal plan reports `Sort Method: quicksort` with no temporary buffers. > [!WARNING] > **Do not treat a 1GB global `work_mem` as a spill fix.** It creates severe aggregate-memory risk. For a targeted experiment, prefer `SET LOCAL` inside a transaction or a carefully scoped role/session setting, then size the fleet-wide default from concurrency evidence. --- ## 6.4 - Vacuum & Freezing (The Housekeepers) <img src="assets/arch_maintenance_crew.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> In Postgres, data is not immediately deleted. Every `UPDATE` and `DELETE` leaves dead row versions that remain in the heap, occupying space until they are reclaimed. If left unmanaged, they accumulate into **[[Manuscript/06 - Resource Management & Processes/6.5 - Tuple Bloat (Garbage Collection)|Bloat]]**. This forces the engine to scan more pages to retrieve live data. To manage this, Postgres uses **Autovacuum**: a background worker responsible for reclaiming MVCC "dead space" and freezing old rows before the 32-bit transaction counter reaches its limit. > [!NOTE] > While other workers like the **Background Writer** and **Checkpointer** manage the flow of data between RAM and Disk, **Autovacuum** is the only process that physically reclaims the space inside those files. > [!NOTE] > **In PostgreSQL Terms** > * **Vacuum**: The background process that reclaims physical space occupied by dead tuples. > * **Free Space Map (FSM)**: The data structure tracking available space inside pages. > * **Visibility Map (VM)**: The data structure tracking which pages contain only live tuples. ### The Cleanup Crew: Autovacuum Autovacuum relies on two physical maps to optimize its sweep: - **Visibility Map (VM)**: A bitset indicating page status using two bits: - **Bit 0 (All-Visible)**: Every tuple is visible to everyone; enables **Index-Only Scans**. - **Bit 1 (All-Frozen)**: Every tuple is permanent history; allows vacuum to skip the page entirely during anti-wraparound runs. - **Free Space Map (FSM)**: A record of exactly how much reusable space remains on each page, helping future inserts find a home. #### Shared Lock Coordination (`pg_multixact`) A tuple header has limited space for transaction metadata. When multiple transactions place shared locks (`SELECT FOR SHARE`) on the same row, Postgres cannot store all their IDs in the tuple header. Instead, the engine uses **`pg_multixact`**. The tuple's `xmax` field is updated with a "MultiXact ID" that points to a list of lockers in an external file. > [!WARNING] > **The Maintenance Tax**: While this enables high-concurrency shared locking, it adds a maintenance burden. Autovacuum must also clean up old `pg_multixact` segments. Heavy shared-row locking can significantly increase autovacuum overhead. ### The Two-Phase Vacuum Vacuuming a table is a two-phase mechanical operation: 1. **Phase 1: Heap Scan**. Autovacuum scans the heap and collects the physical addresses (**TIDs**) of dead tuples into a buffer. 2. **Phase 2: Index Cleanup**. The engine visits every index and removes pointers referencing the collected TIDs. Only after the indexes are cleared can the heap space be marked as reusable in the Free Space Map (FSM). > [!NOTE] Vacuum Reuses Space; It Does Not Shrink Files > **Concept**: Vacuum does not shrink table files. It just sweeps them. > **Payoff**: When `VACUUM` runs, it does not release disk blocks back to the operating system (which would require expensive OS locks and file truncations). It simply marks the slots of dead tuples as "empty" in the Free Space Map (FSM). The next time you run `INSERT`, Postgres checks the FSM first and writes the new data directly into those empty slots, preventing the file from growing. **The rule of thumb:** Vacuum clears the heap first to find dead tuples, then sweeps the indexes to remove pointers to them, and finally updates the Free Space Map so new inserts can reuse the space. > [!IMPORTANT] > **The Maintenance Budget**: The size of the TID buffer is controlled by `maintenance_work_mem`. If the buffer is too small, autovacuum must perform multiple index passes, which increases the I/O cost and duration of the vacuum. ```sql -- Is autovacuum keeping up? SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10; ``` A high `n_dead_tup` count means autovacuum is falling behind. By default, it triggers when dead tuples exceed **20%** of live tuples (`autovacuum_vacuum_scale_factor = 0.20`). For high-write tables you typically want to lower that threshold: ```sql -- Make autovacuum more aggressive on a busy table ALTER TABLE orders SET ( autovacuum_vacuum_scale_factor = 0.01, -- trigger at 1% dead tuples autovacuum_vacuum_cost_limit = 1000 -- and give it a bigger I/O budget ); ``` ### From Cleanup to Conservation: The Historical Horizon Reclaiming dead tuples is only part of autovacuum's responsibility. While "cleaning up waste" keeps the table lean, the engine has a more profound duty: ensuring that old facts remain visible even as the system's transaction counter resets. This is where maintenance transitions from housekeeping to **historical integrity**. ### Transaction ID Wraparound (The Freeze Ritual) Imagine a database cluster running on a server with terabytes of free disk space, minimal CPU load, and gigabytes of spare RAM. Suddenly, it goes completely read-only. It refuses to accept new write transactions, rejects client connections, and throws urgent, scary error messages. The database has run out of nothing physical. No disks are full, and no hardware has failed. Yet the database has entered an emergency lockdown. What invisible resource has the engine run out of, and why does it force a complete database shutdown? > [!IMPORTANT] Predict Why Transaction IDs Cannot Simply Restart > Why can't a Postgres database simply loop its transaction counter back to 1 when it runs out of numbers? What would happen to historical data if the counter rolled over? Pause and formulate a guess. You might expect that transaction IDs are just temporary labels that can be recycled once the transaction finishes, or that they can roll over back to 1 like an odometer. If transaction IDs rolled over, then old transactions stamped with ID 100 would suddenly appear to have been written *after* the new transaction 2,000,000,000. Under MVCC visibility rules, ancient rows would suddenly vanish or become visible in the wrong temporal snapshots, corrupting your data. Instead, Postgres resolves this by managing a strictly bounded timeline. Transaction IDs (XIDs) are stored in tuple headers as 32-bit unsigned integers, meaning there are exactly $2^{32}$ ($4.2$ billion) possible IDs. Under modular arithmetic, Postgres divides this timeline in half: 1. At any moment, the current transaction is in the middle. 2. The $2$ billion IDs *before* it are considered the **Past**. 3. The $2$ billion IDs *after* it are considered the **Future**. As new transactions are created, the timeline moves forward. If the counter reaches $2$ billion active transactions without resetting, the past wraps around and becomes the future. Ancient rows would suddenly seem to have been created in the future, making them completely invisible to the current transaction. To prevent this existential disaster, Postgres performs a **Freeze** operation via Autovacuum: - **Freezing**: Autovacuum scans older pages (using the Visibility Map's `All-Frozen` bits to skip clean pages) and finds tuples older than a threshold (`vacuum_freeze_min_age`). - **The Frozen Stamp**: It stamps their header with a special flag (`FrozenTransactionId` or `2`). In modular math, this specific ID is defined as being infinitely in the past, older than any active transaction. - **Odometry Reset**: Once all ancient tuples on a page are frozen, the engine can safely roll over the active transaction counter without risking history. If the database's write rate outpaces the Freeze Ritual, the age of the oldest unfrozen transaction (`age(relfrozenxid)`) approaches $2$ billion. At that point, Postgres enters emergency lockdown to prevent data corruption. This Freeze process is the ultimate trade-off of MVCC. In exchange for non-blocking read/write performance, the database must pay a constant background maintenance tax. Autovacuum must systematically read and rewrite old pages to freeze tuples, causing write amplification even on tables that are completely static. ```sql -- How close are our tables to the XID wraparound danger zone? SELECT relname, age(relfrozenxid) AS xid_age, relpages FROM pg_class WHERE relkind = 'r' ORDER BY age(relfrozenxid) DESC LIMIT 10; ``` The older the `xid_age`, the more urgently the table needs a vacuum pass to freeze its rows. The danger threshold is ~**2 billion transactions**. You can also force a full freeze on any table at any time: ```sql -- Manual emergency freeze for a specific table VACUUM FREEZE orders; ``` > [!WARNING] > Never disable `autovacuum`. It is a critical system process. Disabling it leads to XID wraparound panics, uncontrolled table growth, and eventual cluster shutdown. ### Throttling and I/O Spikes To prevent maintenance from overwhelming the system, autovacuum is throttled by a cost-based credit system (`autovacuum_vacuum_cost_limit`). This ensures background cleanup does not starve user queries for I/O. However, if the Checkpointer is too aggressive, it can flood the I/O subsystem with dirty pages. Spread these writes using `checkpoint_completion_target` (typically 0.9) to minimize latency spikes during checkpoints. Without these background processes, Postgres would eventually succumb to bloat and XID exhaustion. ### 🧪 Reclaim Deleted Space for Reuse — Lab **Prove Vacuum Recycled the Deleted Space**: "We just deleted 100,000 old records, but the table size on disk didn't shrink. Prove that autovacuum is doing its job." #### The Investigation Check the current state of dead tuples on the table: ```sql SELECT n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables WHERE relname = 'supply_deliveries'; ``` **Result**: ```text n_live_tup | n_dead_tup | last_autovacuum -------------+------------+----------------- 500,000 | 100,000 | 2024-03-25... ``` #### The Diagnosis The `n_dead_tup` counter confirms that 100,000 versions are physically present but logically dead. Postgres hasn't reclaimed the space yet. If you insert new data now, the engine will prefer to reuse these "dead" slots rather than extending the file. #### The Lazy Fix You can wait for **Autovacuum** to trigger automatically (based on the `scale_factor`), or you can manually summon it for an immediate sweep: ```sql VACUUM ANALYZE supply_deliveries; ``` #### Dead Tuples Become Reusable Space Check the stats again: ```text n_live_tup | n_dead_tup | last_autovacuum -------------+------------+----------------- 500,000 | 0 | 2024-03-25... ``` The dead tuples are gone. The space is now marked as "Free" in the **Free Space Map (FSM)**, and future inserts will be lightning-fast because they won't trigger expensive disk-allocation syscalls. > [!IMPORTANT] > **The Disk Myth**: `VACUUM` does NOT return space to the operating system. It marks the space as reusable *within* the existing file. If you need to shrink the physical file size, you need `VACUUM FULL` (which locks the table exclusively). --- ## 6.5 - Tuple Bloat (Garbage Collection) <img src="assets/arch_vacuum_bloat.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> When updates and deletes happen faster than they can be cleaned, the database enters a state of **Bloat**. Even though your "live" data remains the same size, the physical data file on disk begins to swell. This is because the "dead" records (the ghost tuples left by MVCC) are taking up space that hasn't been recycled yet. > [!IMPORTANT] > **The Survival Clause** > Garbage Collection is not an "optimization" you can turn off to save CPU. In an MVCC engine like Postgres, space is not automatically reclaimed when a row is deleted. If you disable the cleanup processes, your storage will grow until the disk is consumed, and your indexes will swell until every query stalls. In Postgres, maintenance is a requirement for existence. --- ### The Mechanics: Why Bloat Happens Vacuum makes space from removable tuple versions reusable and updates related metadata. Whether future writes reuse that space fast enough to stabilize relation size depends on free-space location, tuple size, HOT opportunities, fillfactor, and workload. However, two things can break this cycle: 1. **Old horizons**: Long-running transactions, prepared transactions, replication slots, and standby feedback can hold back cleanup horizons. The exact tuples retained depend on database, snapshot, and transaction state; "everything deleted after BEGIN" is only a rough metaphor. 2. **High-Frequency Updates**: If you update 10,000 rows a second, but your vacuum is only tuned to clean once every minute, the "dead" data will accumulate faster than it can be removed. --- ### Table Bloat vs. Index Bloat Bloat doesn't just happen in your tables; it happens in your indexes, too. - **Table Bloat**: Is generally manageable. Once space is marked free, it will be reused for new rows. - **Index Bloat**: B-trees delete/recycle pages and use mechanisms such as bottom-up deletion and deduplication, but some workloads still leave inefficient space distribution. More pages can increase cache and scan costs; the effect must be measured. > [!WARNING] > **The Vacuum Trap**: Standard `VACUUM` primarily makes space reusable inside PostgreSQL. It can truncate completely empty pages from the physical end of a relation when conditions permit, but it does not compact scattered live tuples throughout the file. A large predictable shrink usually requires a rewrite/rebuild strategy. --- ### Reclaiming the Space If bloat becomes catastrophic (e.g., your disk is 95% full), you have three options to physically shrink the files: | Method | Impact | Best For... | | :--- | :--- | :--- | | **`REINDEX [CONCURRENTLY]`** | Rebuilds an index with different locking/space trade-offs. | A measured index-rebuild need. | | **`VACUUM FULL`** | Rewrites the table and takes an AccessExclusive lock. | Planned cases that can tolerate blocking and temporary disk needs. | | **`pg_repack`** (extension) | Rebuilds with reduced blocking but operational prerequisites and extra disk. | Version-tested environments that cannot accept `VACUUM FULL` locking. | --- ### Diagnostics: The Waste Audit To see if your cleanup crew is falling behind, you can check the ratio of live to dead tuples: ```sql -- Check the 'dead tuple' count for a specific table SELECT relname, n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables WHERE relname = 'supply_deliveries'; ``` `n_dead_tup` is an estimate. A sustained high value relative to churn, vacuum history, relation size, and cleanup horizons is evidence to investigate—not proof by itself that autovacuum is mis-tuned. --- ### Tune the Cleanup Thresholds The vacuum trigger includes a fixed threshold plus a scale-factor term (by default `50 + 0.2 × reltuples`, subject to other insert/freeze/emergency triggers and configuration). On a very large, high-churn table that scale term can permit more dead tuples than the workload tolerates. Tune per table from observed churn and vacuum duration: ```sql -- Trigger vacuum at 1% dead rows instead of 20% ALTER TABLE supply_deliveries SET ( autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_cost_limit = 1000 ); ``` > [!TIP] > **The Sleeping Janitor**: If you see the wait event `Timeout:VacuumDelay` in your tracer, it means Autovacuum is intentionally sleeping to avoid consuming too much I/O. If bloat is rising, you need to increase the `cost_limit` to let it work longer before taking a break. --- ### Recap: Managing the Overflow > [!NOTE] > - **Bloat is caused by MVCC history** that hasn't been recycled. > - **Old cleanup horizons** are a common cause of stuck bloat; identify the actual holder. > - **Index bloat** is harder to fix than table bloat. > - **Tune per-table** for busy workloads; don't wait for the 20% default. --- ### 🧪 Manipulation Lab: Disabling Autovacuum & Bloat Growth To see how MVCC dead tuples physically bloat a table when automated garbage collection is disabled, we will create a table, turn off autovacuum, run high-frequency updates, measure table growth, and trigger manual cleanup. #### The Setup Connect to the database and create a table with autovacuum disabled: ```sql CREATE TABLE bloat_test ( id INT PRIMARY KEY, counter INT ); ALTER TABLE bloat_test SET (autovacuum_enabled = false); -- Insert 10,000 records INSERT INTO bloat_test SELECT i, 0 FROM generate_series(1, 10000) i; ``` #### The Task 1. Measure the table's initial size and verify the dead tuple count: ```sql SELECT pg_size_pretty(pg_relation_size('bloat_test')) AS table_size; SELECT pg_stat_force_next_flush(); SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'bloat_test'; ``` Output: - Table size: **344 kB** - `n_live_tup`: `10000`, `n_dead_tup`: `0` 2. Run a loop that updates the counter 5 times (generating 50,000 dead tuples), then query sizes and stats: ```sql -- Generate updates UPDATE bloat_test SET counter = counter + 1; UPDATE bloat_test SET counter = counter + 1; UPDATE bloat_test SET counter = counter + 1; UPDATE bloat_test SET counter = counter + 1; UPDATE bloat_test SET counter = counter + 1; SELECT pg_stat_force_next_flush(); SELECT pg_size_pretty(pg_relation_size('bloat_test')) AS bloated_size; SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'bloat_test'; ``` Output: - Table size: **2008 kB** (nearly 6x growth) - `n_live_tup`: `10000`, `n_dead_tup`: `50000` Notice that the physical table size grew from 344 kB to over 2 MB. This is because every `UPDATE` wrote a new version of the row, and since autovacuum was disabled, the old versions remained as garbage. 3. Run a manual `VACUUM` to clean the dead tuples and inspect stats: ```sql VACUUM bloat_test; SELECT pg_stat_force_next_flush(); SELECT pg_size_pretty(pg_relation_size('bloat_test')) AS size_after_vacuum; SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'bloat_test'; ``` Output: - Table size: **2008 kB** (size did not shrink!) - `n_live_tup`: `10000`, `n_dead_tup`: `0` #### VACUUM Removes Dead Tuples but Leaves the File Look at the results: - `n_dead_tup` dropped to `0`, meaning the dead tuples were removed. - However, the table size on disk **remained 2008 kB**. In this recorded lab fixture, standard `VACUUM` made the dead space reusable but did not shrink the file. PostgreSQL can sometimes truncate empty tail pages, so treat "never shrinks" as too absolute; scattered free space normally remains allocated to the relation. 4. Run `VACUUM FULL` to rebuild the table and recover the space: ```sql VACUUM FULL bloat_test; SELECT pg_size_pretty(pg_relation_size('bloat_test')) AS final_size; ``` Output: - Table size: **344 kB** `VACUUM FULL` rewrote the active rows into a brand-new file, shrinking the size back to 344 kB. However, it required an exclusive lock on the table. ```sql -- Clean up DROP TABLE bloat_test; ``` --- ## 6.6 - RAM, CPU & Disk (The Physical Machine) <img src="assets/arch_hardware_evolution.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> The previous sections described how Postgres *manages* its memory budgets. This section describes what is actually happening in the hardware and OS kernel when those budgets are exercised — because the decisions Postgres makes only make sense once you can picture what is happening below the abstraction. ### RAM: Two Caches, Not One There is a common misconception about `shared_buffers`: that it *is* the Postgres memory, and that everything else goes to disk. The reality is more layered. When Postgres reads a page from disk, it calls `pread()` — a standard POSIX syscall. The kernel intercepts this call and routes it through the **Linux Page Cache** (also called the **Buffer Cache**), a kernel-managed pool of recently accessed file pages. The kernel doesn't know anything about Postgres tables or 8KB pages; it just sees file offsets. So when a page arrives in Postgres, it has actually passed through *two* independent caches: 1. **The OS Page Cache**: Kernel-managed. Contains file pages as the kernel understands them. Lives in ordinary kernel address space. 2. **`shared_buffers`**: Postgres-managed. Contains those same pages mapped into Postgres's shared memory segment, indexed by the Buffer Mapping Hash Table. This is the reason `shared_buffers` is capped at 25% of RAM. The OS Page Cache is not idle; it is actively caching the same files below Postgres's sight line. Giving Postgres 90% of RAM doesn't eliminate that second cache — it just starves it. On workloads with large sequential scans or heavy checkpoint I/O, the OS Page Cache does meaningful, non-redundant work. **Read path when a page is missing from `shared_buffers`:** 1. Backend asks the buffer manager for the page. 2. **Hit in `shared_buffers`** → return immediately (microseconds). 3. **Miss** → backend issues `pread()` on the heap file. - **Hit in OS page cache** → kernel returns the page without disk I/O. - **Miss in OS cache** → kernel reads from NVMe/SSD (milliseconds; shows up as **`DataFileRead`** wait events in Chapter 7). 4. Backend copies the page into `shared_buffers` for subsequent queries. > [!NOTE] > **The `O_DIRECT` exception**: Some systems configure Postgres to use `O_DIRECT` for WAL writes, bypassing the OS Page Cache entirely for those files. This avoids double-buffering the WAL. It is not the default. ### How `shared_buffers` is Allocated At startup, Postgres allocates `shared_buffers` as a single large **shared memory segment** using `shmget()` or `mmap()` (depending on the OS and configuration). All backend processes attach to this same segment. This means every backend reading or writing a page in the buffer pool is accessing the exact same physical memory — no copying, no per-process duplication. This also means that when you set `shared_buffers = 8GB`, Postgres reserves 8GB of **physical DRAM** on startup, not virtual address space. The OS cannot page it out. ### CPU: When I/O Is Not the Bottleneck Once a page is in `shared_buffers`, the query engine starts doing CPU work: - **Tuple evaluation**: Walking the rows in a page and evaluating WHERE clause predicates. - **Hash computation**: Building hash tables for Hash Joins and Hash Aggregations. - **Sort passes**: Quicksort in-memory for Sort nodes. - **Expression evaluation**: Running functions, type coercions, and operator calls. A query whose working set fits entirely in `shared_buffers` is CPU-bound, not I/O-bound. Throwing more RAM at a CPU-bound query won't help. Adding an appropriate index to eliminate the tuple evaluation loop will. This distinction — **I/O-bound vs. CPU-bound** — is the central diagnostic axis of Chapter 7. Wait events tell you which world your query is living in. ### The Same Operator, Two Cache States `EXPLAIN (ANALYZE, BUFFERS)` reports page hits and reads. **`pg_wait_tracer`** plan traces report *wait time under each plan node* — a preview of the Chapter 7 workflow. Warm the heap in-session, then run an expensive filter: ```sql SELECT count(*) FROM orders WHERE EXTRACT(year FROM order_time) = 2024; ``` Below is the plan operator trace for this CPU-intensive filter:  Because pages are resident, per-row `EXTRACT` math dominates execution under `Seq Scan` (87.9% CPU compute). Drop the OS page cache and scan a cold heap: ```sql SELECT sum(quantity_kg) FROM supply_deliveries; ``` Below is the plan operator trace for the cold heap scan:  Same `Seq Scan` shape — completely opposite wait signature. The first query needs a better predicate or index; the second needs RAM or fewer blocks read. Chapter 7 names these patterns precisely; here the point is that **cache state**, not plan shape, decides the resource mix. > [!TIP] > Regenerate these figures with `./tests/docker_live.sh manuscript` in the `pg_wait_tracer` repo. Timings are measured, not hand-authored. On modern multi-core hardware, Postgres can execute a single query using **parallel workers** (see `max_parallel_workers_per_gather`); they help on CPU-bound aggregations and sorts but not when a single disk read dominates. ### Disk: A History of I/O Assumptions When a page is not in either cache, the database engine must fetch it from the block device. The kernel translates the `pread()` call into an I/O request submitted to the device driver. What happens next has changed dramatically over the history of database infrastructure — and will continue to change. Postgres's defaults encode a specific set of assumptions about what "storage" costs. Understanding those assumptions, and when they were made, is the key to tuning correctly for hardware you are actually running. ### Era 1: Spinning HDD (The Physical Constraint) A spinning hard drive reads data by moving a physical read head across a rotating magnetic platter. The mechanical latency is real and unavoidable: a **rotational seek** takes 4–10 ms, bounded by the physics of moving steel. The consequences for a database are severe. Reading 10,000 random 8KB pages from an HDD: ``` 10,000 seeks × 8 ms average = 80 seconds ``` Sequential reads are dramatically cheaper — the head doesn't need to move far. This asymmetry between random and sequential I/O is the foundational assumption baked into Postgres's query planner. `random_page_cost` defaults to **4.0** while `seq_page_cost` defaults to **1.0**. Together they tell the planner: "a random page is four times as expensive as a sequential page." On spinning disk, even that can be conservative—the physical latency ratio is often much larger. ### Era 2: SATA SSD (The First Inversion) SATA SSDs have no moving parts. Random access latency drops to ~500 µs — roughly 10–20× faster than a seeking HDD. But SATA SSDs still use the SATA interface, which was designed for spinning disks. The interface itself is a bottleneck: SATA caps at ~550 MB/s throughput and can't issue many parallel I/O operations concurrently. On a SATA SSD, the planning assumptions begin to break down. The `random_page_cost = 4.0` default can overstate the penalty for random reads. Some workloads benefit from lowering it toward `3.0`, after measurement. ### Era 3: NVMe (The Interface Disappears) NVMe SSDs connect over PCIe lanes directly to the CPU. They have no mechanical seek, no SATA bottleneck, and can service tens of thousands of I/O operations in parallel. Random access latency is ~100 µs or lower. At this point, the distinction between "random" and "sequential" reads has narrowed dramatically at the hardware level. The `random_page_cost = 4.0` default can be too conservative for a well-cached NVMe workload. ```sql -- For NVMe: close the planning gap between random and sequential ALTER SYSTEM SET random_page_cost = 2.1; SELECT pg_reload_conf(); ``` > [!WARNING] > Do not copy `2.1` blindly into production. Even NVMe has a real cost relative to CPU cache, and the planner still needs a signal that page retrieval is not free. Benchmark the storage and validate representative plans. A more aggressive value may be used temporarily with `SET LOCAL` in a disclosed planner experiment, but that is operator demonstration—not hardware calibration. ### Era 4 and Beyond: Persistent Memory (The Category Blurs) **Persistent Memory (PMEM / Optane)** sits in DIMM slots alongside DRAM, but survives power loss. Latency is ~300 ns — faster than NVMe by 300×, and only 3× slower than DRAM. This completely dissolves the traditional RAM/disk boundary. Storage-class memory architectures like this have already influenced database design. **Neon** and **Amazon Aurora** separate compute from storage entirely, replacing the local disk with network-attached page stores. WAL is shipped over the network instead of written to local disk. The lesson is not a specific set of numbers to memorize. It is this: > **Postgres's defaults are calibrated to an era. The hardware has already changed twice since those defaults were written. It will change again.** Every time you deploy Postgres on a new infrastructure — a new instance type, a new cloud region, a new disk tier — the correct behavior is to benchmark actual I/O latency and re-calibrate `random_page_cost`, `seq_page_cost`, and `effective_cache_size` accordingly. The database engine has no way to detect that it's running on NVMe instead of a 2003-era SCSI disk. That judgment is yours. ```sql -- effective_cache_size tells the planner how much OS Page Cache is available. -- It does not allocate memory; it only influences index-vs-seqscan decisions. SHOW effective_cache_size; -- A reasonable starting point: about 75% of total system RAM ALTER SYSTEM SET effective_cache_size = '24GB'; -- for a 32GB server SELECT pg_reload_conf(); ``` > [!NOTE] > **The Analogy Limit**: A shared buffer pool metaphor accurately describes the *role* of `shared_buffers`, but it implies a static, fixed capacity. Real storage hardware is not static. The speed of retrieval and the cost of physical I/O are parameters of the hardware generation you happen to be running — not constants of the universe. ### Observing Hardware Pressure: `pg_stat_io` Postgres 16 introduced `pg_stat_io`, a view that exposes per-backend, per-context I/O counters. This is the most direct window into whether your hardware is actually being exercised: ```sql -- Which backend types are hitting disk the most? SELECT backend_type, object, context, reads, hits, writes, evictions, ROUND(hits::numeric / NULLIF(reads + hits, 0) * 100, 1) AS hit_rate_pct FROM pg_stat_io WHERE reads + hits > 0 ORDER BY reads DESC; ``` A `hit_rate_pct` near 100% means shared buffer pool is doing its job and reads are being served from RAM. As `hit_rate_pct` drops below ~95% on a busy OLTP system, the buffer pool is too small for the working set and physical disk I/O is becoming a significant cost. ```sql -- Reset to get a fresh baseline after config changes SELECT pg_stat_reset_shared('io'); ``` --- ## 6.7 - Summary: The Machine Has a Budget ### Chapter 6 Capstone: Why is My Table 10x Larger Than Expected? Your storage monitoring alert triggers: disk usage is growing exponentially, but your business metrics show no increase in customer registrations or orders. You inspect the database and discover that the `orders` table is physically occupying 10GB of storage on disk, despite only holding 1GB of actual, live data records. Below are three operational diagnostics. Determine **what** is causing the runaway bloat and **how** to resolve it. --- #### Case A: The Forgotten Transaction * **Symptoms**: You run `VACUUM orders;` but the dead tuple statistics do not drop. You check the active processes: ```sql SELECT pid, age(backend_xmin), query, state FROM pg_stat_activity WHERE state != 'idle' ORDER BY age DESC LIMIT 1; ``` This reveals a transaction that has been in the `idle in transaction` state for 12 hours. * **The Cause**: **Uncommitted transaction bounds**. Postgres cannot vacuum any tuples that were deleted/updated after the oldest active transaction's `xmin` boundary was established, as it must keep those dead tuples visible in case that transaction requests them. * **Release the Stale Snapshot**: Terminate the stale connection using `SELECT pg_terminate_backend(pid);` and run `VACUUM` again to allow page space reclamation. --- #### Case B: The Autovacuum Lockout * **Symptoms**: Dead tuples are rising rapidly on the `orders` table. You check the autovacuum logs and notice that autovacuum starts cleaning the table but aborts after a few seconds without reclaiming space. * **The Cause**: **Lock conflict is one hypothesis.** Ordinary vacuum takes `SHARE UPDATE EXCLUSIVE`, which is compatible with normal reads and writes but conflicts with several maintenance and DDL lock modes. PostgreSQL may cancel an autovacuum worker that blocks a waiting conflicting lock. Logs and `pg_locks` should establish whether cancellation, blocking, throttling, or another failure occurred. * **Clear the Maintenance Conflict**: Identify the conflicting command, shorten or reschedule exclusive maintenance, and make migrations lock-timeout aware. Cost-delay tuning addresses vacuum throughput, not a lock conflict; tune it only after evidence shows throttling is the limiting factor. --- #### Case C: The High-Frequency Update Loop * **Symptoms**: Autovacuum is running continuously on the `orders` table, consuming 100% disk write bandwidth, but table bloat continues to grow. * **The Cause**: **Scale-based triggering can be too permissive for this churn.** The normal update/delete trigger includes `autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples` (defaults commonly 50 and 0.2), alongside other insert/freeze/emergency triggers and caps. On a 10-million-row estimate, that term is roughly two million tuple events; it does not translate directly into exactly 20% physical bloat. * **Vacuum This Table More Aggressively**: Modify the table thresholds to be more aggressive: ```sql ALTER TABLE orders SET ( autovacuum_vacuum_scale_factor = 0.02, autovacuum_vacuum_cost_limit = 1000 ); ``` --- ### 📝 Summary: The Machine Has a Budget At the start of this chapter, performance may have looked like a query problem. Now you can see the budget underneath: backend processes, shared buffers, work memory, disk latency, vacuum capacity, and hardware assumptions. That changes the way you diagnose trouble. A table that is 10x larger than its live data is no longer just “big.” It is evidence. Maybe a transaction is pinning old tuples. Maybe autovacuum is yielding to locks. Maybe the update pattern is producing dead rows faster than cleanup can reclaim them. Maybe the planner still believes in hardware assumptions that no longer match the disk beneath it. The practical achievement is resource attribution. You can now ask: is this system short on memory, blocked on disk, starved for cleanup, overloaded by connections, or mispriced by planner settings? > [!NOTE] Physical Work Is Deferred, Not Erased > **Concept**: Postgres cannot make physical work disappear. It can only decide when to pay for it. ### Sources & Further Reading - [PostgreSQL 18: Resource Consumption](https://www.postgresql.org/docs/18/runtime-config-resource.html) - [PostgreSQL 18: Server Processes](https://www.postgresql.org/docs/18/connect-estab.html) - [PostgreSQL 18: Cumulative Statistics](https://www.postgresql.org/docs/18/monitoring-stats.html) - [PostgreSQL 18: Routine Vacuuming](https://www.postgresql.org/docs/18/routine-vacuuming.html) - Source trail: `src/backend/postmaster/`, `src/backend/storage/buffer/`, and `src/backend/commands/vacuum.c`. <div style="page-break-after: always;"></div>