# Chapter 7: Wait Events & Concurrency ## 7.0 - Why Slow Queries Lie (The Waiting Game) <img src="assets/chap_6_modular_wait.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> > [!NOTE] Production Story: Friday at 4:55 PM (The CPU is Sleeping) > "We deployed a fast new feature. CPU utilization was flat at 2%, but the database connection pool was saturated, and user response times spiked to 5 seconds. The database was dying, yet the CPU was asleep. Where were the seconds going? > > The application was updating the cafe's daily menu. It opened a transaction, updated a row, and then performed a slow external API request before committing. During that round-trip, the uncommitted tuple version and transaction remained in place. Dozens of concurrent backends attempting to modify the same row waited on the transaction to finish while doing no useful query work. The queries were lying: they weren't executing slowly, they were waiting." Postgres is a finely tuned engine of movement, but most of its life is spent in a curious state of **Synchronized Waiting**. Imagine a query that, according to **[[Operations/_Operations|EXPLAIN ANALYZE]]**, only took 1 second of actual processing time. Yet, the user's stopwatch reported a 5-second hang. Where did the other 4 seconds go? The missing time was lost to environmental wait time. We call this the **Waiting Game**. To master the engine, you must stop looking solely at what it is *doing* and start measuring what it is *waiting for*. ### Choose Your Instruments The core toolkit is already inside PostgreSQL 18: `pg_stat_activity`, `pg_blocking_pids()`, `pg_locks`, `pg_stat_statements`, `pg_stat_io`, and `EXPLAIN (ANALYZE, BUFFERS)`. Every reader can investigate the Waiting Game with those instruments in the companion lab. The optional `pg_wait_tracer` track adds a microscope: higher-frequency samples, plan-node correlation, replay files, and W3C trace context. Those are tool features rather than PostgreSQL core. Chapter 7.10 states the Linux and privilege requirements, but you do not need the tracer to learn the method. Below is a normalized plan-operator fixture showing the distinction. Its event names are literal PostgreSQL names; its durations are intentionally simplified for teaching rather than presented as a raw capture:  Notice the stark diagnosis: In this fixture, the query spends **700.0ms** in `IO:DataFileRead` under the `Seq Scan` operator and only 0.3ms in derived active-execution intervals. The plan shape alone cannot reveal that split; the runtime trace does. > [!TIP] > Tracer views such as `time_model` and `active` can help when a wait lives outside a user plan—WAL commit flushes, replication senders, idle-in-transaction `ClientRead`, or latch storms. With stock PostgreSQL, follow the process role through activity, I/O, replication, and lock views. For ordinary SQL, start with `EXPLAIN (ANALYZE, BUFFERS)`. #### The Two Classes of Locks: LWLocks vs. Heavyweight Locks When analyzing concurrency blockages, Postgres divides waits into two distinct locking systems: - **Lightweight Locks (LWLocks)**: These are short-lived, internal locks managed automatically in shared memory. They protect physical structures like page buffers, commit log buffers, and hash table buckets. Contention on events such as `LWLock:WALWrite` or `LWLock:BufferMapping` points to pressure on a shared internal structure. - **Heavyweight Locks**: These are transactional locks that protect user-level relations (tables), rows, or transactions. They are held for the duration of a transaction. Contention here (e.g., waiting on `transactionid` or `relation` lock events) points to application logic conflicts, where concurrent processes are attempting to modify the same rows. The rest of this chapter explores the **Anatomy of a Slowdown**. We will move layer by layer—from single queries to cluster-wide congestion—treating each failure not as a list of abstract metrics, but as a forensic investigation. --- ## 7.1 - The Diagnostic Views (Sweat vs. Sigh) <img src="assets/arch_wait_events.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> To observe the engine's behavior, we must move beyond the "Service Receipt" of `EXPLAIN ANALYZE` and look at the live heartbeat of the system. Before diving into specific events, establish the first diagnostic split: at a sampled instant, PostgreSQL either reports a named wait event or it does not. ### 1. The Sweat (No PostgreSQL Wait Reported) In `pg_stat_activity`, `wait_event IS NULL` means PostgreSQL is not reporting an instrumented wait. The backend may be executing, runnable but not scheduled, preempted, or in uninstrumented work. Correlate this state with OS scheduler data or `pg_wait_tracer` running samples before calling it CPU time. When scheduler or tracer evidence confirms sustained running time, inspect logical complexity, row volume, and plan shape. A NULL wait sample by itself is only the beginning of that diagnosis. ### 2. The Sigh (Named PostgreSQL Wait) The backend reports the resource or coordination point delaying it—a disk read, a network socket, a row lock, or an internal latch. When a process is sighing, the exact event identifies the subsystem to investigate. The remedy may be an access-path change, a shorter transaction, a faster resource, or simply recognizing a normal process wait. Understanding wait events is simply the art of measuring exactly what caused Postgres to Sigh. ### The Diagnostic Trio When a query is slow, we use three distinct lenses to triangulate the cause: 1. **Activity (`pg_stat_activity`)**: Is the process currently doing anything? 2. **Waiting (`wait_event`)**: If it's active but slow, what resource is it waiting for? 3. **Locking (`pg_blocking_pids()` plus `pg_locks`)**: If it is waiting for a lock, which session is blocking it and which lock modes conflict? The rest of this chapter classifies the specific failure modes this framework reveals. --- ### 🧪 Observation Lab: Wait Event Safari To see how Postgres reports active workloads, we will run queries that trigger active wait states and query `pg_stat_activity` from another session. #### The Task 1. Open a session to your database and execute a query that does CPU-heavy calculations in a loop to simulate **Active CPU** (sweating): ```sql -- Run a heavy calculation loop (will take a few seconds) SELECT count(*) FROM generate_series(1, 20000000) i WHERE (i % 3) = 0; ``` 2. While that query is executing in the background, open a second session immediately and check the state and wait events: ```sql SELECT pid, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state = 'active' AND query NOT LIKE '%pg_stat_activity%'; ``` #### NULL Means No Reported Wait; a Named Event Identifies the Wait Examine the active process columns: ``` pid | state | wait_event_type | wait_event | query ------+--------+-----------------+------------+------------------------------- 8951 | active | | | SELECT count(*) FROM gener... ``` The `wait_event_type` and `wait_event` columns are empty (`NULL`). For this deliberately CPU-heavy query, OS or tracer samples should also show running time—but the NULL columns alone do not prove that the backend occupied a core at that instant. 3. To see **The Sigh** (Waiting), run a query that is forced to wait for client input. If a connection is idle, wait states appear: ```sql SELECT pid, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state = 'idle'; ``` Output: ``` pid | state | wait_event_type | wait_event | query ------+-------+-----------------+------------+------------------------------- 8952 | idle | Client | ClientRead | SELECT * FROM animals; ``` Here, the wait event is **`ClientRead`** of type **`Client`**. For an idle connection this is normally expected: the backend is waiting for the client to send its next protocol message. #### One Column Separates Reported Waits from Everything Else Checking `wait_event` immediately identifies instrumented waits such as `Client:ClientRead`, `IO:*`, or `Lock:*`. When it is NULL, investigate plan work and scheduler/on-CPU evidence instead of automatically declaring CPU saturation. --- ## 7.2 - The Investigative Workflow <img src="assets/arch_kitchen_chaos_v2.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> When the database slows down, the reader's instinct is often to dive into advanced subsystems or guess at missing indexes. A systems investigator does not guess; they measure. We start with four stock-PostgreSQL tools, then add an optional high-resolution layer when the environment supports it. **Investigative workflow** (in order): 1. **`pg_stat_statements`** — which queries hurt the fleet over time? 2. **`EXPLAIN (ANALYZE, BUFFERS)`** — what did this query actually do (rows, buffers, node times)? 3. **`pg_stat_activity`** — what is running *right now* (state, wait event, query text)? 4. **`pg_blocking_pids()` + `pg_locks`** — who is blocking whom, and on what? 5. **Optional plan trace (`pg_wait_tracer`)** — where did sampled time occur inside the plan tree? ### 1. The Historical Ledger: `pg_stat_statements` If you want to know which normalized statements have consumed the most total resources since these statistics were reset, query `pg_stat_statements`. The extension aggregates counters for tracked statement fingerprints; entries can be evicted, and the view does not define a 24-hour window. For a true 24-hour comparison, snapshot the counters at both boundaries or collect them continuously in a monitoring system. ```sql -- Which queries have consumed the most cumulative execution time? SELECT query, calls, total_exec_time / 1000 AS total_seconds, mean_exec_time AS avg_ms, (shared_blks_hit + shared_blks_read) AS total_buffers FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; ``` If a query has a massive `total_seconds` but a tiny `avg_ms`, it is a high-frequency **OLTP (Diner)** query: a thousand papercuts. If it has a massive `avg_ms` but only one call, it is a heavy **OLAP (Soup Factory)** query: a single heavy boulder causing massive I/O. ### 2. The Physical Reality: `EXPLAIN ANALYZE` A standard `EXPLAIN` shows the query planner's cost estimates. To see the physical reality of a workload—what the query actually did and how many disk blocks it hit—you must use `EXPLAIN (ANALYZE, BUFFERS)`. ```sql -- The fundamental diagnostic command EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM ingredients WHERE category = 'Spice'; ``` If `EXPLAIN ANALYZE` shows 10ms but the application logs 5000ms, first establish where the clocks begin and end. The missing time may be in a connection pool, application queue, network transfer, transaction wrapper, result consumption, or a different execution—not necessarily inside PostgreSQL. `EXPLAIN ANALYZE` reports **work**: node times, rows, buffers. It does not attribute **wait events** to plan operators. When the missing seconds are environmental, you need a plan-shaped wait trace. ### Optional Advanced Layer: Wait Events Inside Operators A plan trace can correlate observed wait samples with the active query and plan node. This normalized fixture shows the intended reading:  With the optional tracer installed, a plan trace answers a question neither `EXPLAIN ANALYZE` nor a single `pg_stat_activity` poll can: **which operator coincided with which sampled wait event?** Here, essentially all wall clock is `IO:DataFileRead` under `Seq Scan`—not under the `HashAggregate`. For a literal capture with the same signature, investigate the scan's cache residency and storage path before tuning the aggregate or buying CPU. > [!TIP] > Fleet views (`time_model`, `system_event`, `active`) tell you *how much* time a wait class consumes. The plan trace tells you *where in the plan* that time lives. Use both. ### 3. The Real-Time State: `pg_stat_activity` If you want to see exactly which processes are currently stuck and why, you query the live tracking view. This is your primary tool for investigating live incidents. ```sql -- Who is currently blocked, and what are they waiting for? SELECT pid, usename, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state != 'idle'; ``` If you see **`wait_event_type: IO`**, the specific backend process is stalled at the storage layer. If you see **`wait_event_type: Lock`**, the process is blocked by another query holding a lock. ### 4. The Coordination Truth: `pg_locks` When `pg_stat_activity` reports a lock wait, start with `pg_blocking_pids(waiter_pid)` to identify direct blockers, then join `pg_locks` when you need the exact lock modes and objects. A relation-only query can miss transaction-ID blockers. ```sql -- A simplified check for granted vs ungranted locks SELECT locktype, relation::regclass, mode, granted, pid FROM pg_locks WHERE relation = 'orders'::regclass; ``` --- ### 🧪 Incident: The Stuck Frog (Lock Contention) Let's put this workflow into practice. **The Symptom**: "Simple `UPDATE` calls are taking 30+ seconds. The API is queuing up and timing out." #### The Investigation First, we query `pg_stat_activity` to find the blockage: ```sql SELECT pid, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE wait_event_type = 'Lock'; ``` **Result**: ```text pid | state | wait_event_type | wait_event | query --------+---------+-----------------+-------------------+----------------------- 8551 | active | Lock | transactionid | UPDATE orders SET... ``` PID 8551 is waiting for a row lock held by another Transaction ID. Hardware is useless here; the query is blocked by another transaction. #### Finding the Blocker Next, we use lock tracking to find the root blocker (the "Stuck Frog") that is holding the lock but not doing anything. ```sql -- Find the session that is blocking others SELECT pid, state, query, xact_start FROM pg_stat_activity WHERE pid = (SELECT (pg_blocking_pids(8551))[1]); ``` #### Clearing the Jam You identify that a developer left a `BEGIN` session open in their terminal without a `COMMIT`. By calling `SELECT pg_terminate_backend(blocker_pid);`, you clear the traffic jam and the API immediately resumes its flow. > [!NOTE] > **Recap**: `Lock:transactionid` usually means another transaction owns a conflicting tuple version. `lock_timeout` contains queueing damage; the root fix is to shorten or reorder the conflicting transactions. Now that we have the tools, we will look at the specific failure modes. --- ## 7.3 - CPU Saturation (Active Execution) <img src="assets/arch_cpu_sweat.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> ### 1. CPU Is Pinned, but Nothing Is Waiting "Our nightly report is taking forever. The server's CPU is pinned at 100%, but `pg_stat_activity` shows that the query isn't waiting for any locks or disk I/O." ### 2. The Physical Reality Sometimes nothing is blocked. The query is simply expensive. A backend with no reported PostgreSQL wait may be hashing, sorting, evaluating expressions, scanning memory, runnable, or briefly off-CPU. Confirm CPU saturation with scheduler or tracer evidence. This represents a bottleneck of **logical complexity**. ### 3. The Wait Signature In `pg_stat_activity`, active execution usually presents as a NULL wait event. In these diagrams, `CPU` means a measured or derived running interval from the tracer—not a PostgreSQL `wait_event` value. ### 4. The Investigation The normalized fixture below gives the report query a mixed I/O-and-running signature so the two phases remain easy to compare: ```sql -- Forcing CPU to calculate math on every row SELECT count(*) FROM orders WHERE EXTRACT(year FROM order_time) = 2024; ``` Below is the corresponding teaching trace:  Notice the distinct profile: * **The Initial Disk Retrieval (646.3ms, `IO:DataFileRead`)**: Non-resident table pages are read into `shared_buffers`. * **The CPU Compute Core (1,675.5ms, `CPU`)**: 72.2% of the query's total life is spent actively executing mathematical function logic (`EXTRACT(year)`) on millions of tuples. The bottleneck is **Sweat-led**, not storage-led. The engine is doing real work on the CPU, but it is working inefficiently due to an unindexed expression. ### 5. The Root Cause This is a **Complexity Failure**. Because the `EXTRACT` function is applied to the column, the engine cannot use a B-Tree index to perform a fast lookup. Instead, it must perform a Sequential Scan, loading every single row into memory and executing the `EXTRACT` mathematical function on the CPU for every row. ### 6. The Strategic Fix Unwrap the function to allow the engine to use its high-speed indexing machinery (a Sargable predicate). ```sql -- Fast: Logical range check allows index usage SELECT count(*) FROM orders WHERE order_time >= '2024-01-01' AND order_time < '2025-01-01'; ``` By removing the computational burden from the CPU, you allow the engine to find the rows instantly via the index. The query time drops from seconds to milliseconds. ### 7. The Same Scan, Two Bottlenecks A `Seq Scan` is not inherently a CPU problem or an I/O problem. It is a loop: fetch a page, process its tuples, repeat. The plan trace reports **which phase won the wall clock**. "Warm" is not a single switch. A page can miss **`shared_buffers`** (Postgres's page cache) and still be served from the **OS page cache** when Postgres calls `pread()`. Only a miss in *both* layers is a true storage stall. Chapter 6 covers that layering in full ([[Manuscript/06 - Resource Management & Processes/6.6 - RAM, CPU & Disk (The Physical Machine)|6.6 RAM, CPU & Disk]]); here it explains why two scans with the same plan shape can disagree on wait events. Compare the CPU-led EXTRACT fixture above with the deliberately I/O-led fixture below: **1. Warm path, expensive filter — Sweat-led under `Seq Scan`:**  Most of the wall clock is `CPU` (1,675.5ms) under `Seq Scan` (per-row `EXTRACT` math). Residual `IO:DataFileRead` is pages that still missed cache—not the main story. **2. Cold path — Sigh under `Seq Scan`:**  In the cold-path fixture, 700.0ms is `IO:DataFileRead`. Same operator shape; completely opposite runtime signature. * The first is a **Complexity Failure** (fix the predicate). * The second is a **Throughput Failure** (read fewer blocks, or keep them in RAM)—the subject of the next section. > [!NOTE] > **Random access shifts the mix.** Sequential scans benefit from kernel read-ahead: the OS page cache often holds the "warm but not hot" stretch of a large table, while `shared_buffers` keeps the truly hot pages. Point lookups and index-driven random I/O get little read-ahead; they compete for `shared_buffers` slots one page at a time. A random-heavy workload therefore tends to pin its hot set in `shared_buffers` (CPU under the leaf/heap access when it hits) and pay full `IO:DataFileRead` on cold random misses—whereas a sequential scan of the same byte volume may ride the OS cache and show a milder wait mix. Access pattern, not only table size, decides how the two caches share the working set. See [[Manuscript/06 - Resource Management & Processes/6.6 - RAM, CPU & Disk (The Physical Machine)|6.6]] for the allocation rationale (why `shared_buffers` is often ~25% of RAM). > [!TIP] > In a lab, run a large `SELECT count(*)` twice. The second pass, with pages in `shared_buffers`, shifts toward `CPU`. Restarting Postgres clears `shared_buffers` but **not** the OS page cache—so you may still avoid a heavy `IO:DataFileRead` mix. For a true cold path, drop the OS cache (or use a fresh VM) as well. The plan node does not change; the wait mix does. --- ### The Active Execution Bestiary When your OS monitoring shows 100% CPU utilization, you must verify what kind of active execution is occurring. #### Pure Computation These are active-execution signatures, not official PostgreSQL wait-event names. When tracer running samples and host CPU utilization agree, they help explain what the backend is computing. | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_ipc_system_hub.png" width="75" alt="CPU"> | [[Workloads/CPU/CPU\|CPU]] | Actively executing logic on the CPU. | | <img class="bestiary-thumb" src="assets/wl_cpu_tupleprocessing.png" width="75" alt="TupleProcessing"> | [[Workloads/CPU/TupleProcessing\|TupleProcessing]] | High CPU utilization during core tuple manipulation. | | <img class="bestiary-thumb" src="assets/wl_cpu_expressionevaluation.png" width="75" alt="ExpressionEvaluation"> | [[Workloads/CPU/ExpressionEvaluation\|ExpressionEvaluation]] | High CPU utilization during SQL expression evaluation. | #### Internal Algorithms Derived categories for complex internal work such as sorting, hashing, or memory-context management. | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_cpu_memoryalgorithms.png" width="75" alt="MemoryAlgorithms"> | [[Workloads/CPU/MemoryAlgorithms\|MemoryAlgorithms]] | High CPU utilization during in-memory sorting or hashing. | #### Micro-Friction: Spinlocks A **spinlock** protects very short critical sections by briefly polling rather than sleeping. Isolated spin time is normal; sustained aggregate spinning can consume CPU while making little application progress and needs source/OS-level corroboration. | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_cpu_spinning.png" width="75" alt="Spinning"> | [[Workloads/CPU/Spinning\|Spinning]] | High CPU utilization while waiting for lightweight locks. | Spinlock pressure can masquerade as computation because PostgreSQL exposes no ordinary `pg_stat_activity` wait event while a process spins. Confirm it with an OS profiler or scheduler evidence. It is not naturally attributed to one plan operator. **Optional tracer track:** the tested tracer line derives a fleet-level `Spinning` category: ```bash # Spinlock storms are cluster-wide, not plan-local sudo ./pg_wait_tracer --view system_event --interval 5 --count 1 ``` ```text Wait Class Wait Event AAS % DB Time ────────── ────────── ────── ──────── CPU Spinning 14.20 89.0% CPU — 1.80 11.0% ``` The remedy depends on the contended structure and workload. Bounding concurrency with a pooler may help a connection-driven storm; a hot buffer, extension, kernel behavior, or PostgreSQL defect requires a different fix. Profile first, change one cause, and re-measure. --- ## 7.4 - Storage Latency (Physical IO Stalls) <img src="assets/arch_io_friction_beaver.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> ### 1. Latency Spikes While the CPU Sits Idle "Queries are taking seconds instead of milliseconds. `top` shows CPU usage is low, but database response times are consistently sluggish." ### 2. The Physical Reality When PostgreSQL needs a page that is absent from `shared_buffers`, it issues a read request to the operating system. `IO:DataFileRead` records time waiting in that read call. The operating system may satisfy it from its page cache or may reach storage; the wait event alone cannot distinguish those paths. We call this **The Friction of Retrieval**. If your working set exceeds your RAM, your throughput collapses. ### 3. The Wait Signature In `pg_stat_activity`, this state presents as an `IO` wait class. The most common event is **`DataFileRead`**, which means the backend is waiting for a block of user data. In PostgreSQL 18, **`BuffileWrite`** and **`BuffileRead`** indicate that the engine has run out of `work_mem` and is "spilling" a sort or hash operation to temporary disk files. Earlier PostgreSQL releases and some tracing tools render the same buffered-file family as `BufFileWrite` and `BufFileRead`. > [!TIP] Relation read or executor spill? > `DataFileRead` can be normal on a cold pass. When it dominates repeated executions alongside physical buffer reads and low useful CPU, the query is touching too many relation blocks. > > `BuffileRead` or `BuffileWrite` alongside temporary blocks means a sort, hash, or materialized working set has spilled. Reduce its input first; adjust `work_mem` only after accounting for concurrency. ### 4. The Investigation In [[Manuscript/07 - Wait Events & Concurrency/7.3 - CPU Saturation (Active Execution)|7.3]] we saw a `Seq Scan` that was almost pure `CPU`. Here the same operator shape is almost pure I/O—because the pages are not in memory. We look at the specific query, a large `SELECT` on `supply_deliveries`, and export a plan trace: ```sql SELECT sum(quantity_kg) FROM supply_deliveries; ``` Below is the normalized I/O-led fixture introduced earlier:  The fixture attributes 700.0ms of `IO:DataFileRead` to the `Seq Scan`. A literal trace with this signature proves that the backend waited in relation-file reads under that operator; buffer statistics and host storage telemetry determine how much reached physical media. ### 5. The Root Cause Because there is no index (or the query must visit the whole table), Postgres performs a Sequential Scan. It is a rapid sequence of "Sighs" (I/O) and "Sweats" (CPU) on that operator alone. ### 6. The Strategic Fix Start by reducing the blocks touched: improve the predicate or access path, partition appropriately, or avoid repeated full scans. Then measure cache residency and storage latency before changing `shared_buffers` or buying faster media. If the wait event is `BuffileWrite` (a temporary file spill during a sort), the fix may be to increase `work_mem` for the session after accounting for the number of concurrent operations that can receive the same budget. ```sql -- Giving this session more room may avoid IO:BuffileWrite for this operation SET work_mem = '64MB'; SELECT count(*) FROM ingredients GROUP BY category; ``` --- <!-- I/O FIELD GUIDE START: generated --> ### The I/O Bestiary These **19** representative `IO` leaves cover relation data, temporary spills, control state, asynchronous operations, and physical backups. Each row pairs its visual mnemonic and event name with a quick operational definition. WAL I/O continues in 7.5 and replication-file I/O in 7.7; the complete card catalog remains available for the future cladogram. #### Relation Data Files The everyday storage path: fetching, writing, extending, and synchronizing table and index files. | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_io_datafileextend.png" width="75" alt="DataFileExtend"> | [[Workloads/IO/DataFile/DataFileExtend\|DataFileExtend]] | Waiting for a relation data file to be extended | | <img class="bestiary-thumb" src="assets/wl_io_datafileprefetch.png" width="75" alt="DataFilePrefetch"> | [[Workloads/IO/DataFile/DataFilePrefetch\|DataFilePrefetch]] | Waiting for an asynchronous prefetch from a relation data file | | <img class="bestiary-thumb" src="assets/wl_io_datafileread.png" width="75" alt="DataFileRead"> | [[Workloads/IO/DataFile/DataFileRead\|DataFileRead]] | Waiting for a read from a relation data file | | <img class="bestiary-thumb" src="assets/wl_io_datafilesync.png" width="75" alt="DataFileSync"> | [[Workloads/IO/DataFile/DataFileSync\|DataFileSync]] | Waiting for changes to a relation data file to reach durable storage | | <img class="bestiary-thumb" src="assets/wl_io_datafilewrite.png" width="75" alt="DataFileWrite"> | [[Workloads/IO/DataFile/DataFileWrite\|DataFileWrite]] | Waiting for a write to a relation data file | #### Temporary Work Files Sorts, hashes, and materialized intermediates spill here when their working set exceeds memory. | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_io_buffileread.png" width="75" alt="BuffileRead"> | [[Workloads/IO/BufFile/BufFileRead\|BuffileRead]] | Waiting for a read from a buffered file | | <img class="bestiary-thumb" src="assets/wl_io_buffiletruncate.png" width="75" alt="BuffileTruncate"> | [[Workloads/IO/BufFile/BufFileTruncate\|BuffileTruncate]] | Waiting for a buffered file to be truncated | | <img class="bestiary-thumb" src="assets/wl_io_buffilewrite.png" width="75" alt="BuffileWrite"> | [[Workloads/IO/BufFile/BufFileWrite\|BuffileWrite]] | Waiting for a write to a buffered file | #### Asynchronous I/O PostgreSQL can overlap requests until a backend must submit more work or wait for a result. | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_io_aio_iocompletion.png" width="75" alt="AioIoCompletion"> | [[Workloads/IO/AIO/AioIoCompletion\|AioIoCompletion]] | Waiting for another process to complete IO | | <img class="bestiary-thumb" src="assets/wl_io_aio_iouringexecution.png" width="75" alt="AioIoUringExecution"> | [[Workloads/IO/AIO/AioIoUringExecution\|AioIoUringExecution]] | Waiting for IO execution via io_uring | | <img class="bestiary-thumb" src="assets/wl_io_aio_iouringsubmit.png" width="75" alt="AioIoUringSubmit"> | [[Workloads/IO/AIO/AioIoUringSubmit\|AioIoUringSubmit]] | Waiting for IO submission via io_uring | #### Control and Shared Metadata Small files can still gate the cluster when control state or shared transaction metadata must become durable. | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_io_controlfileread.png" width="75" alt="ControlFileRead"> | [[Workloads/IO/ControlFile/ControlFileRead\|ControlFileRead]] | Waiting for a read from the pg_control file | | <img class="bestiary-thumb" src="assets/wl_io_controlfilesync.png" width="75" alt="ControlFileSync"> | [[Workloads/IO/ControlFile/ControlFileSync\|ControlFileSync]] | Waiting for the pg_control file to reach durable storage | | <img class="bestiary-thumb" src="assets/wl_io_controlfilewrite.png" width="75" alt="ControlFileWrite"> | [[Workloads/IO/ControlFile/ControlFileWrite\|ControlFileWrite]] | Waiting for a write to the pg_control file | | <img class="bestiary-thumb" src="assets/wl_io_slruread.png" width="75" alt="SlruRead"> | [[Workloads/IO/SLRU/SLRURead\|SlruRead]] | Waiting for a read of an SLRU page | | <img class="bestiary-thumb" src="assets/wl_io_slruwrite.png" width="75" alt="SlruWrite"> | [[Workloads/IO/SLRU/SLRUWrite\|SlruWrite]] | Waiting for a write of an SLRU page | #### Physical Backups Base backups expose the read, write, and durability cost of copying a complete physical cluster image. | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_io_basebackupread.png" width="75" alt="BasebackupRead"> | [[Workloads/IO/BaseBackup/BaseBackupRead\|BasebackupRead]] | Waiting for base backup to read from a file | | <img class="bestiary-thumb" src="assets/wl_io_basebackupsync.png" width="75" alt="BasebackupSync"> | [[Workloads/IO/BaseBackup/BaseBackupSync\|BasebackupSync]] | Waiting for data written by a base backup to reach durable storage | | <img class="bestiary-thumb" src="assets/wl_io_basebackupwrite.png" width="75" alt="BasebackupWrite"> | [[Workloads/IO/BaseBackup/BaseBackupWrite\|BasebackupWrite]] | Waiting for base backup to write to a file | <!-- I/O FIELD GUIDE END: generated --> --- ## 7.5 - WAL Pressure (Durability Bottlenecks) <img src="assets/arch_pocket_diary.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> ### 1. Simple Updates Stall Behind WAL "Our bulk update is crawling. `pg_stat_activity` shows dozens of sessions, but they're all just 'active' and taking 200ms per simple update." ### 2. The Physical Reality Many commit-time write waits are tied to Postgres's durability guarantees. With synchronous commit enabled, a transaction cannot acknowledge success until the required **[[Architecture/WAL|Write-Ahead Log (WAL)]]** position has been durably synchronized. Because the WAL is a single, sequential stream, it presents two distinct bottlenecks: **I/O Latency** (how fast we can write to disk) and **Lock Contention** (how fast we can coordinate which process gets to write next). ### 3. The Wait Signature In `pg_stat_activity`, this state presents as a combination of `LWLock` and `IO` events: - **`LWLock:WALWrite`**: The process is queued, waiting for another process to finish flushing the shared WAL buffer to disk. - **`IO:WalSync`**: The process is waiting for the operating system's WAL synchronization call to complete. - **`LWLock:WALInsert`**: In high-concurrency workloads, backends may fight for this lock just to reserve their byte offset in the sequential stream. ### 4. The Investigation WAL waits often sit at **commit boundaries**—flushing the log after a statement finishes—not inside a clean plan-node attribution. Start on the core track by grouping current activity and comparing it with `pg_stat_wal` over a measured interval: ```sql SELECT wait_event_type, wait_event, count(*) AS sessions FROM pg_stat_activity WHERE state = 'active' GROUP BY wait_event_type, wait_event ORDER BY sessions DESC; SELECT wal_records, wal_fpi, wal_bytes, wal_buffers_full, wal_write, wal_sync, wal_write_time, wal_sync_time, stats_reset FROM pg_stat_wal; ``` The cumulative counters require two observations or a monitoring system that computes rates. Timing columns also depend on the relevant I/O timing configuration. **Optional tracer track:** a plan trace of `UPDATE orders ...` may show work under `ModifyTable` while the commit wait follows. The tracer's fleet view can sample that boundary: ```bash # WAL pressure is cluster-wide commit friction, not a plan operator sudo ./pg_wait_tracer --view system_event --interval 5 --count 1 ``` ```text Wait Class Wait Event AAS % DB Time ────────── ────────────── ────── ──────── LWLock WALWrite 8.42 80.0% IO WalSync 1.05 10.0% CPU — 1.00 9.5% ``` The tracer reveals that **80% of cluster time** is spent in `WALWrite`. ### 5. The Root Cause A bulk update script is running hundreds of independent UPDATE statements without grouping them into a single transaction: ```sql -- Naive: Updating 100 rows, one commit at a time UPDATE orders SET status = 'Pending' WHERE id = 1; UPDATE orders SET status = 'Pending' WHERE id = 2; -- ... repeat 98 more times ... ``` Because each UPDATE is its own transaction, the engine must flush the WAL buffer to disk for every single row. A single 100-row update generates ~830KB of WAL records, but forcing 100 separate physical flushes turns a bandwidth problem into a latency problem. ### 6. The Strategic Fix Wrap the updates into a single atomic transaction. This allows the engine to buffer all changes and perform a single, efficient flush at the end. ```sql -- Fast: Atomic batching BEGIN; UPDATE orders SET status = 'Pending' WHERE id <= 100; COMMIT; ``` Moving from 100 commits toward one commit can sharply reduce `IO:WalWrite`, `IO:WalSync`, and `LWLock:WALWrite` pressure. Measure again to discover the remaining bottleneck. --- ### The WAL Bestiary When your system is write-bound, the following wait events help you identify exactly where the sequential write stream is bottlenecked. #### Physical WAL Durability & Throughput Synchronous commits are gated by the configured durability boundary. These events record PostgreSQL waiting in WAL write and synchronization calls; they do not reveal the storage controller's internal behavior. | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_io_walwrite.png" width="75" alt="WalWrite"> | [[Workloads/IO/WAL/WALWrite\|WalWrite]] | Waiting for WAL file write. | | <img class="bestiary-thumb" src="assets/wl_io_walsync.png" width="75" alt="WalSync"> | [[Workloads/IO/WAL/WALSync\|WalSync]] | Waiting for WAL file sync. | #### High-Concurrency Hotspots (LWLocks) Before the physical disk is even accessed, backends must acquire memory-level locks to reserve their position in the sequential WAL stream. | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_lwlock_wal_walinsert.png" width="75" alt="WALInsert"> | [[Workloads/LWLock/WAL/WALInsert\|WALInsert]] | Waiting to insert a WAL record into a buffer. | | <img class="bestiary-thumb" src="assets/wl_lwlock_wal_walwrite.png" width="75" alt="WALWrite"> | [[Workloads/LWLock/WAL/WALWrite\|WALWrite]] | Waiting for a write to the WAL. | | <img class="bestiary-thumb" src="assets/wl_lwlock_wal_buffer.png" width="75" alt="WALBufMapping"> | [[Workloads/LWLock/WAL/WALBufMapping\|WALBufMapping]] | Waiting to replace a page in the WAL buffers. | `WALInsert` contention means too many backends are fighting to *reserve their slot* in the sequential stream simultaneously — the fix is to reduce write concurrency (batch transactions, connection pooling). `WALWrite` contention means backends are queued to *flush* the buffer — the fix is faster disk or a dedicated WAL volume. #### Background Maintenance & Initialization Postgres initializes WAL segment files (`IO:WalInitWrite` and `IO:WalInitSync`) as needed. Material time here points to WAL segment initialization work and its storage path. ##### WAL Provisioning | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_io_walinitsync.png" width="75" alt="WalInitSync"> | [[Workloads/IO/WAL/WALInitSync\|WalInitSync]] | Waiting for a newly initialized WAL file to be synchronized. | | <img class="bestiary-thumb" src="assets/wl_io_walinitwrite.png" width="75" alt="WalInitWrite"> | [[Workloads/IO/WAL/WALInitWrite\|WalInitWrite]] | Waiting for a write during WAL file initialization. | ##### Background Synchronization | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_activity_walwritermain.png" width="75" alt="WalWriterMain"> | [[Workloads/Activity/Replication/WalWriterMain\|WalWriterMain]] | Waiting in main loop of WAL writer process. | | <img class="bestiary-thumb" src="assets/wl_ipc_checkpointstart.png" width="75" alt="CheckpointStart"> | [[Workloads/IPC/Checkpoint/CheckpointStart\|CheckpointStart]] | Waiting for a checkpoint cycle to begin. | #### WAL Reading & Historical Access Under normal conditions, the WAL is a write-only stream. However, during recovery, standby synchronization, or incremental backups, the engine must read the log to reconstruct state or generate change summaries. | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_io_walread.png" width="75" alt="WalRead"> | [[Workloads/IO/WAL/WALRead\|WalRead]] | Waiting for a read from a WAL file. | | <img class="bestiary-thumb" src="assets/wl_io_walsummaryread.png" width="75" alt="WalSummaryRead"> | [[Workloads/IO/WAL/WalSummaryRead\|WalSummaryRead]] | Waiting for a read from a WAL summary file. | | <img class="bestiary-thumb" src="assets/wl_io_walsendertimelinehistoryread.png" width="75" alt="WalsenderTimelineHistoryRead"> | [[Workloads/IO/WAL/WALSenderTimelineHistoryRead\|WalsenderTimelineHistoryRead]] | Waiting to read timeline history during WAL sending. | --- ## 7.6 - Lock Contention (Concurrency & Blocking) <img src="assets/arch_heavyweight_locks.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> ### 1. One DDL Lock Stops the Application "I'm just trying to add a single column to the `ingredients` table. It's a metadata change, but suddenly the entire application has stopped being able to read from that table!" ### 2. The Physical Reality While LWLocks protect shared-memory structures, **Heavyweight Locks** ensure the logical consistency of data. They coordinate transactional access to tables, rows, and other database objects. When a transaction holds a lock on a resource, concurrent processes requiring a conflicting lock mode must wait until the holder completes. Unlike LWLocks, many heavyweight relation and transaction locks are **transactional**: once acquired, they remain until `COMMIT` or `ROLLBACK`. Conflicting requests queue, while compatible lock modes can proceed according to the lock table below. ### Postgres Rocks, Except When It Blocks To coordinate concurrent transactions, the engine compares requested lock modes against a compatibility matrix. If the requested mode conflicts with any lock held by another transaction, the requesting transaction must block and wait. | Requested Mode | Access Share (`SELECT`) | Row Share (`FOR UPDATE`) | Row Exclusive (`UPDATE`) | Share Update Excl. (`VACUUM`) | Share (`CREATE INDEX`) | Share Row Exclusive | Exclusive | Access Exclusive (`ALTER`) | | :--- | :---: | :---: | :---: | :---: | :---: | :---: | :---: | :---: | | **Access Share** | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | **BLOCK** | | **Row Share** | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | **BLOCK** | **BLOCK** | | **Row Exclusive** | ✓ | ✓ | ✓ | ✓ | **BLOCK** | **BLOCK** | **BLOCK** | **BLOCK** | | **Share Update Excl.** | ✓ | ✓ | ✓ | **BLOCK** | **BLOCK** | **BLOCK** | **BLOCK** | **BLOCK** | | **Share** | ✓ | ✓ | **BLOCK** | **BLOCK** | ✓ | **BLOCK** | **BLOCK** | **BLOCK** | | **Share Row Excl.** | ✓ | ✓ | **BLOCK** | **BLOCK** | **BLOCK** | **BLOCK** | **BLOCK** | **BLOCK** | | **Exclusive** | ✓ | **BLOCK** | **BLOCK** | **BLOCK** | **BLOCK** | **BLOCK** | **BLOCK** | **BLOCK** | | **Access Exclusive** | **BLOCK** | **BLOCK** | **BLOCK** | **BLOCK** | **BLOCK** | **BLOCK** | **BLOCK** | **BLOCK** | ### 3. The Wait Signature In `pg_stat_activity`, this state presents as a `Lock` wait class: - **`Lock:relation`**: The process is waiting for a table-level lock (often caused by DDL or `VACUUM FULL`). - **`Lock:transactionid`**: The process is waiting for a row-level lock held by another transaction (often caused by concurrent `UPDATE`s). ### 4. The Investigation: The Causal Lock Trace Below is a normalized multi-process fixture correlating the long reader, the DDL waiter, and a later reader caught behind the queued DDL:  Notice how the causal dependency unfolds on the timeline: * **The Long Reader (PID 10500, `SELECT *`)**: Runs on-CPU for 12,000ms holding an `AccessShareLock` on the table. * **The DDL Waiter (PID 10555, `ALTER TABLE`)**: Attempts to acquire an `AccessExclusiveLock` at $t=2.8\text{s}$, but is immediately suspended on **`Lock:relation`** (9,200ms), waiting for PID 10500 to finish. * **The Queued Reader (PID 10501, `SELECT`)**: Arrives after the DDL request and waits on **`Lock:relation`** behind it. The diagram distinguishes lock ownership from waiting: PID 10500 holds a granted lock but does not report a `Lock` wait. The statement is not "slow DDL"—it was completely blocked waiting for an exclusive table lock. ### 5. The Root Cause PID 10500 is the **Root Blocker**. The `ALTER TABLE` command requires an **AccessExclusiveLock**, so it must wait for all existing readers to finish. While it waits in the queue, it blocks *all new* readers behind it. You have accidentally closed the Narrow Gate for the entire application by running a DDL migration on a busy table without setting a timeout. ```mermaid graph TD Resource[(Table: ingredients)] subgraph "The Lock Queue (FIFO)" A[PID 10500: SELECT] -->|GRANTED| B{AccessShareLock} C[PID 10555: ALTER] -.->|"WAITING: #1"| D{AccessExclusiveLock} E[PID 10501: SELECT] -.->|"WAITING: #2"| F{AccessShareLock} G[PID 10502: SELECT] -.->|"WAITING: #3"| H{AccessShareLock} end B --- Resource D --- Resource F -.->|Blocked by 10555| D H -.->|Blocked by 10555| D style A fill:#e8f5e9,stroke:#2e7d32 style C fill:#ffebee,stroke:#c62828 style E fill:#fff3e0,stroke:#e65100 style G fill:#fff3e0,stroke:#e65100 ``` ### 6. The Strategic Fix Never run DDL on a busy table without a **`lock_timeout`**. This ensures the DDL script fails gracefully rather than bringing down the app. ```sql -- Setting a 1-second patience limit SET lock_timeout = '1s'; ALTER TABLE ingredients ADD COLUMN scent_intensity INT; ``` The DDL command will now either succeed instantly or fail and let the traffic continue. You can then retry the migration during a lower-traffic window. ### ⚠️ Pathology: The Lock Convoy (Row Contention & Cascading Queues) Row-level lock contention is insidious because it is completely invisible in execution plan cost estimates. An `UPDATE` that the planner expects to finish in 0.2ms will silently stall for tens of seconds if another transaction modified the same row and has not committed. Below is a multi-lane execution trace showing a cascading **Lock Convoy** across three concurrent sessions:  Notice how the deliberately staged queue unfolds across time: 1. **Transaction A (PID 6001, The Blocker)**: Updates Row 42 in 3.0ms, then remains idle in the transaction while its client performs an external call. PostgreSQL reports **`Client:ClientRead`** for those 32.0ms while the transaction retains the row lock. 2. **Transaction B (PID 6002, Victim 1)**: Arrives at $t=4.0\text{ms}$ to update Row 42. As the first updater in line, it waits on A's transaction outcome in **`Lock:transactionid`** for 34.0ms. 3. **Transaction C (PID 6003, Victim 2)**: Arrives at $t=10.0\text{ms}$ and queues behind the already-waiting updater in **`Lock:tuple`** for 32.0ms. 4. **The Convoy Release ($t=38\text{ms}$)**: When Transaction A finally commits, the lock queue drains sequentially—Transaction B runs, commits, and only then is Transaction C unblocked. --- ### The Lock Bestiary When queries are stuck waiting on `Lock` events, the bottleneck is logical coordination, not hardware. #### Tables & Rows Heavyweight locks restrict access based on conflicting modes. If Process A attempts to `UPDATE` a row that Process B has modified but not yet committed, Process A will block on **`Lock:transactionid`** until Process B's transaction resolves. | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_lock_relation.png" width="75" alt="relation"> | [[Workloads/Lock/relation\|relation]] | Waiting for a relation-level lock. | | <img class="bestiary-thumb" src="assets/wl_lock_transactionid.png" width="75" alt="transactionid"> | [[Workloads/Lock/transactionid\|transactionid]] | Waiting for a transaction to finish. | | <img class="bestiary-thumb" src="assets/wl_lock_tuple.png" width="75" alt="tuple"> | [[Workloads/Lock/tuple\|tuple]] | Waiting for a lock on a specific row (tuple). | #### Database Object Locks Beyond tables and rows, Postgres must occasionally lock internal physical structures. For example, when a table fills up, a backend must ask the OS to allocate new 8KB blocks. To prevent two backends from concurrently trying to extend the same file, the engine acquires a `Lock:extend`. | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_lock_page.png" width="75" alt="page"> | [[Workloads/Lock/page\|page]] | Waiting for a lock on a relation page. | | <img class="bestiary-thumb" src="assets/wl_lock_extend.png" width="75" alt="extend"> | [[Workloads/Lock/extend\|extend]] | Waiting to extend a relation file. | | <img class="bestiary-thumb" src="assets/wl_lock_object.png" width="75" alt="object"> | [[Workloads/Lock/object\|object]] | Waiting for a lock on a database object. | During bulk loads, `Lock:extend` becomes a significant bottleneck. The fix for high-throughput bulk loads is to partition the table so workers write to different files. #### Advisory Locks Postgres allows developers to define their own logical locks using `pg_advisory_lock()`. Think of it as an application reserving an arbitrary name that does not correspond to a physical database object, but is still enforced globally by the engine's Lock Manager. | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_lock_advisory.png" width="75" alt="advisory"> | [[Workloads/Lock/advisory\|advisory]] | Waiting for a user-defined advisory lock. | | <img class="bestiary-thumb" src="assets/wl_lock_userlock.png" width="75" alt="userlock"> | [[Workloads/Lock/userlock\|userlock]] | Waiting for a user-defined lock. | #### Virtual Transaction Identifiers The most critical event in this group is `Lock:frozenid` — the diagnostic signal for **Anti-Wraparound Vacuum**. An `autovacuum worker` blocked on `Lock:frozenid` means a long-running user query is preventing the engine from advancing its internal transaction horizons. You must terminate the blocker to prevent eventual database lockdown. | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_lock_virtualxid.png" width="75" alt="virtualxid"> | [[Workloads/Lock/virtualxid\|virtualxid]] | Waiting for a virtual transaction ID to finish. | #### Lock Manager Mechanics The **Lock Manager** is the shared memory subsystem that tracks heavyweight locks. High table counts can exhaust the **Fast Path** local array and force backends to bottleneck on the global manager partitions. | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_lw_lockmanager.png" width="75" alt="LockManager"> | [[Workloads/LWLock/LockManager/LockManager\|LockManager]] | Waiting for access to the lock manager. | | <img class="bestiary-thumb" src="assets/wl_lw_lockfastpath.png" width="75" alt="LockFastPath"> | [[Workloads/LWLock/LockManager/LockFastPath\|LockFastPath]] | Waiting for access to the fast-path lock info. | --- ## 7.7 - Cluster Pressure (Background Workers & Replication) <img src="assets/arch_background_workers.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> ### 1. The Replica Falls Thirty Seconds Behind "Our read-replica is falling behind by over 30 seconds. The application is reading stale data. Is the network dropping packets or is the replica disk too slow?" ### 2. The Physical Reality In a distributed environment, the primary server must coordinate with standby replicas to ensure data is mirrored correctly. This introduces **The Distributed Sigh**—the delay of information traveling across the network to find a safe harbor on a remote disk. Even when no user is connected, the engine is not truly silent. It has an **Internal Rhythm**—a heartbeat of maintenance workers (Autovacuum, Checkpointer) that must coordinate with active queries. When these workers wait, or when replication falls behind, the entire cluster experiences pressure. ### 3. The Wait Signature `Client:WalSenderWriteData` on the primary can expose backpressure while sending WAL. On the standby, inspect the WAL receiver separately from the startup process: the receiver handles the network stream, while the startup process replays WAL and may encounter WAL or relation-file I/O. ### 4. The Investigation You need to determine if this is a *sending* bottleneck or an *applying* bottleneck. Replication and maintenance backends have **no user plan tree**. On the core track, use `pg_stat_replication` on the primary and `pg_stat_wal_receiver` plus replay functions on the standby to compare send, write, flush, and replay positions. ```sql -- Primary SELECT application_name, state, sync_state, sent_lsn, write_lsn, flush_lsn, replay_lsn, write_lag, flush_lag, replay_lag FROM pg_stat_replication; -- Standby SELECT status, written_lsn, flushed_lsn, latest_end_lsn FROM pg_stat_wal_receiver; SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(); ``` **Optional tracer track:** use the active-process view to sample process states that the cumulative replication views do not time directly: ```bash # On the PRIMARY: Check the WAL sender (not a user plan) sudo ./pg_wait_tracer --view active --count 1 ``` ```text PID State Wait Event Wait (ms) Backend Type ────── ───────── ───────────────── ────────── ──────────── 1234 active Client:WalSenderWriteData 450.2 walsender 5678 on cpu — — client ``` Sustained `Client:WalSenderWriteData` means the sender cannot drain data promptly; investigate the network and the receiver. `Activity:WalSenderMain` means that sender is in its main-loop wait, but it does not by itself prove where accumulated replay lag lives. Compare send, receive, flush, and replay LSNs. ### 5. The Root Cause If the LSN pipeline points to replay rather than receive, the optional tracer can provide another observation on the **standby** server: ```bash # On the STANDBY: Check the replay worker's throughput sudo ./pg_wait_tracer --view active --count 1 ``` ```text PID State Wait Event Wait (ms) Backend Type ────── ───────── ────────────── ────────── ──────────── 1050 active Client:LibpqwalreceiverReceive 12.0 walreceiver 1051 active IO:DataFileRead 7500.0 startup ``` The receiver and replayer are different processes. Here the receiver is waiting for more bytes from the primary while the startup process spends a long interval in `IO:DataFileRead` while applying changes. Combined with a growing receive-to-replay LSN gap, that points to replay-side storage pressure. This is an **Applying Bottleneck**. The primary is sending data just fine, but the standby's storage is too slow to replay the changes at the rate they are arriving. ### 6. The Strategic Fix You cannot fix this by tweaking network settings. You must either upgrade the IOPS of the replica's storage volume or investigate if the primary is running massive, unoptimized bulk updates that cause a "WAL explosion." --- ### The Cluster Bestiary When the cluster itself is under pressure, you will see wait events in the background workers and replication processes rather than in user queries. #### Background System Activity These events represent the "internal life" of the database—the background workers responsible for maintenance, logging, and durability. High waits here typically indicate that background processes are competing with user backends for shared resources. | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_activity_autovacuummain.png" width="75" alt="AutovacuumMain"> | [[Workloads/Activity/Maintenance/AutoVacuumMain\|AutovacuumMain]] | Waiting in main loop of autovacuum launcher process. | | <img class="bestiary-thumb" src="assets/wl_activity_checkpointermain.png" width="75" alt="CheckpointerMain"> | [[Workloads/Activity/Maintenance/CheckpointerMain\|CheckpointerMain]] | Waiting in main loop of checkpointer process. | | <img class="bestiary-thumb" src="assets/wl_activity_logicalapplymain.png" width="75" alt="LogicalApplyMain"> | [[Workloads/Activity/Logical/LogicalApplyMain\|LogicalApplyMain]] | Waiting in the main loop of a logical replication apply worker. | | <img class="bestiary-thumb" src="assets/wl_activity_bgwritermain.png" width="75" alt="BgwriterMain"> | [[Workloads/Activity/Maintenance/BgWriterMain\|BgwriterMain]] | Waiting in main loop of background writer process. | When the background rhythm breaks, the tracer reveals the starvation. For example, a long `BufferPin` wait in an autovacuum worker means another backend retains a conflicting buffer pin; identify the responsible backend before assuming it is a user query. #### Replication Coordination (IPC) The "Distributed Sigh" of a cluster. These Inter-Process Communication (IPC) signals measure the efficiency of data transfer and synchronization between nodes. ##### 1. Streaming Replication Control Signals related to the core physical replication protocol and the synchronization of WAL records to standby replicas. | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_activity_walsendermain.png" width="75" alt="WalSenderMain"> | [[Workloads/Activity/Replication/WalSenderMain\|WalSenderMain]] | Waiting in main loop of WAL sender process. | | <img class="bestiary-thumb" src="assets/wl_lwlock_syncrep.png" width="75" alt="SyncRep"> | [[Workloads/LWLock/Replication/SyncRep\|SyncRep]] | Waiting to update information about the state of synchronous replication. | | <img class="bestiary-thumb" src="assets/wl_activity_walreceivermain.png" width="75" alt="WalReceiverMain"> | [[Workloads/Activity/Replication/WalReceiverMain\|WalReceiverMain]] | Waiting in main loop of WAL receiver process. | ##### 2. Logical Replication & Worker State Coordination between the primary server and the specialized workers that handle logical decoding, table synchronization, and parallel apply operations. | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_ipc_logicalapplysenddata.png" width="75" alt="LogicalApplySendData"> | [[Workloads/IPC/LogicalRep/LogicalApplySendData\|LogicalApplySendData]] | Waiting to send data to a parallel apply worker. | | <img class="bestiary-thumb" src="assets/wl_ipc_logicalsyncdata.png" width="75" alt="LogicalSyncData"> | [[Workloads/IPC/LogicalRep/LogicalSyncData\|LogicalSyncData]] | Waiting for data during logical replication synchronization. | | <img class="bestiary-thumb" src="assets/wl_activity_logicalparallelapplymain.png" width="75" alt="LogicalParallelApplyMain"> | [[Workloads/Activity/Logical/LogicalParallelApplyMain\|LogicalParallelApplyMain]] | Waiting in the main loop of a logical replication parallel apply worker. | ##### 3. Slot & Origin Management The persistence of replication progress. **Slots** ensure that the primary doesn't delete WAL files before replicas have seen them. | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_ipc_replicationslotdrop.png" width="75" alt="ReplicationSlotDrop"> | [[Workloads/IPC/LogicalRep/ReplicationSlotDrop\|ReplicationSlotDrop]] | Waiting for a replication slot to be dropped. | | <img class="bestiary-thumb" src="assets/wl_io_replicationslotsync.png" width="75" alt="ReplicationSlotSync"> | [[Workloads/IO/Replication/ReplicationSlotSync\|ReplicationSlotSync]] | Waiting for a replication slot file to be synchronized. | | <img class="bestiary-thumb" src="assets/wl_io_replicationslotwrite.png" width="75" alt="ReplicationSlotWrite"> | [[Workloads/IO/Replication/ReplicationSlotWrite\|ReplicationSlotWrite]] | Waiting to write a replication slot status file. | ##### 4. Replication History & Timelines Signals related to the complex "history" of a cluster, specifically when a replica must catch up or reconcile its branch of the transaction log with the primary (e.g., during `pg_rewind`). | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_io_timelinehistoryread.png" width="75" alt="TimelineHistoryRead"> | [[Workloads/IO/Replication/TimelineHistoryRead\|TimelineHistoryRead]] | Waiting to read a timeline history file. | | <img class="bestiary-thumb" src="assets/wl_io_timelinehistorywrite.png" width="75" alt="TimelineHistoryWrite"> | [[Workloads/IO/Replication/TimelineHistoryWrite\|TimelineHistoryWrite]] | Waiting to write a new timeline history file. | #### Distribution & Recovery Coordination Signals related to the complex state transitions of cluster recovery, node promotion, and the persistence of "Prepared Transactions" for Two-Phase Commit (2PC). | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_ipc_archivecommand.png" width="75" alt="ArchiveCommand"> | [[Workloads/IPC/Recovery/ArchiveCommand\|ArchiveCommand]] | Waiting for the WAL archive command to complete. | | <img class="bestiary-thumb" src="assets/wl_ipc_backupwaitwalarchive.png" width="75" alt="BackupWaitWalArchive"> | [[Workloads/IPC/Recovery/BackupWaitWalArchive\|BackupWaitWalArchive]] | Waiting for required WAL to be archived during a backup. | | <img class="bestiary-thumb" src="assets/wl_ipc_promote.png" width="75" alt="Promote"> | [[Workloads/IPC/Recovery/Promote\|Promote]] | Waiting for standby promotion to complete. | | <img class="bestiary-thumb" src="assets/wl_ipc_recoverypause.png" width="75" alt="RecoveryPause"> | [[Workloads/IPC/Recovery/RecoveryPause\|RecoveryPause]] | Waiting while recovery is paused. | #### Client Protocol & Replication Handshakes Wait points occurring at the edge of the engine, where Postgres is negotiating secure connections or waiting for a replica to acknowledge data receipt. | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_client_walsenderwritedata.png" width="75" alt="WalSenderWriteData"> | [[Workloads/Client/WalSenderWriteData\|WalSenderWriteData]] | Waiting for replication data to be sent over the network. | | <img class="bestiary-thumb" src="assets/wl_client_waitforstandbyconfirmation.png" width="75" alt="WaitForStandbyConfirmation"> | [[Workloads/Client/WaitForStandbyConfirmation\|WaitForStandbyConfirmation]] | Waiting for a physical standby to confirm receipt of WAL. | | <img class="bestiary-thumb" src="assets/wl_client_libpqwalreceiverreceive.png" width="75" alt="LibpqwalreceiverReceive"> | [[Workloads/Client/LibPQWalReceiverReceive\|LibpqwalreceiverReceive]] | Waiting to receive WAL data from the primary. | #### Maintenance & System Health Wait points related to the internal "metabolism" of the database. These events coordinate the background workers that keep the cluster healthy without direct user intervention. | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_lw_autovacuum.png" width="75" alt="Autovacuum"> | [[Workloads/LWLock/Autovacuum/Autovacuum\|Autovacuum]] | Waiting for access to the autovacuum shared memory. | | <img class="bestiary-thumb" src="assets/wl_lw_wraplimitsvacuum.png" width="75" alt="WrapLimitsVacuum"> | [[Workloads/LWLock/Autovacuum/WrapLimitsVacuum\|WrapLimitsVacuum]] | Waiting to update transaction ID or MultiXact ID limits. | | <img class="bestiary-thumb" src="assets/wl_ipc_checkpointercomm.png" width="75" alt="CheckpointerComm"> | [[Workloads/LWLock/Checkpointer/CheckpointerComm\|CheckpointerComm]] | Waiting to communicate with the checkpointer. | --- ## 7.8 - Internal Coordination (Parallelism & Shared Memory) <img src="assets/arch_master_keys.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> ### 1. More Cores Cannot Clear Shared-Memory Queues "During our Black Friday sale, CPU usage hit 100% and queries slowed down from 1ms to 20ms. We upgraded to a 64-core machine, but it didn't help. We're not seeing any disk I/O or row locks." ### 2. The Physical Reality Deep within the engine, even memory access must be coordinated. This is **The Bureaucratic Friction**—the microscopic LWLocks and IPC signals that ensure two processes don't try to write to the same memory slot or read the same snapshot simultaneously. These represent the "Master Keys" of the database. When hundreds of parallel workers or connections attempt to read from the same memory block or update the same global array, they must queue at the microsecond level. ### 3. The Wait Signature In `pg_stat_activity`, this state presents as an `IPC` or `LWLock` wait class: - **`LWLock:ProcArray`**: The process is waiting to read or update the global array of active transactions. - **`LWLock:BufferContent`**: The process is waiting for access to a buffer's page contents. `BufferPin` is a separate wait type. - **`IPC:ExecuteGather`**: The leader of a parallel query is waiting for its workers to finish. ### 4. The Investigation `LWLock:ProcArray` is shared coordination across backends. It is not owned by a single plan operator. On the core track, repeatedly sample `pg_stat_activity` (or let a monitoring system do so) and corroborate runnable-process pressure at the OS level: ```sql SELECT wait_event_type, wait_event, count(*) AS sessions FROM pg_stat_activity WHERE backend_type = 'client backend' AND state = 'active' GROUP BY wait_event_type, wait_event ORDER BY sessions DESC; ``` **Optional tracer track:** use a fleet view for higher-frequency attribution: ```bash # ProcArray contention is cluster-wide, not plan-local sudo ./pg_wait_tracer --view system_event --interval 5 --count 1 ``` ```text Wait Class Wait Event AAS % DB Time Wait (ms) ────────── ────────── ────── ──────── ──────── LWLock ProcArray 28.50 95.0% 142500 CPU — 1.50 5.0% 7500 ``` 95% of cluster time is spent in `LWLock:ProcArray`. Only 5% is productive computation. No amount of CPU upgrade will help—the latch is the ceiling. When the wait *is* inside a parallel plan (`IPC` under `Gather`, `IO` under a worker `Seq Scan`), use a plan-trace timeline instead—see **Parallel Query Coordination** below. ### 5. The Root Cause You are suffering from a **ProcArray Stall**. To ensure MVCC (Multi-Version Concurrency Control), every query must ask, "Which other transactions are currently running?" To answer this, the engine scans the `ProcArray`—a shared memory structure tracking all active connections. When you have 500 active connections, scanning the array takes longer. Worse, while one backend is updating the array (e.g., to commit), it takes an exclusive `LWLock` on it, forcing the other 499 backends to wait. Upgrading CPU cores often makes this *worse*, as more cores means more aggressive lock contention. ### 6. The Strategic Fix The database has too many direct connections. You must implement a **Connection Pooler** (like PgBouncer) to multiplex thousands of application connections down to a small, fixed number of database connections (e.g., 50). > **The Takeaway**: While heavyweight locks coordinate logical database objects like tables and rows across transactions, lightweight locks (LWLocks) and latches coordinate physical access to shared memory pages and engine data structures. --- ### The Coordination Bestiary When the bottleneck is deep inside the engine's memory or execution fabric, you will see the following LWLocks and IPC signals. #### Visibility & Snapshot Coordination These events represent the engine's "Atomic Clock." They manage the global process array and subtransaction logs that determine which rows are visible to which transactions. | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_lw_procarray.png" width="75" alt="ProcArray"> | [[Workloads/LWLock/ProcArray/ProcArray\|ProcArray]] | Waiting for access to the process array. | | <img class="bestiary-thumb" src="assets/wl_ipc_safesnapshot.png" width="75" alt="SafeSnapshot"> | [[Workloads/IPC/System/SafeSnapshot\|SafeSnapshot]] | Waiting for a safe snapshot in a serializable transaction. | | <img class="bestiary-thumb" src="assets/wl_ipc_procarraygroupupdate.png" width="75" alt="ProcarrayGroupUpdate"> | [[Workloads/IPC/Transaction/ProcArrayGroupUpdate\|ProcarrayGroupUpdate]] | Waiting for a group process array update. | | <img class="bestiary-thumb" src="assets/wl_lwlock_multixact_member.png" width="75" alt="MultiXactGen"> | [[Workloads/LWLock/MultiXact/MultiXactGen\|MultiXactGen]] | | ### ⚠️ Pathology: The 64-Savepoint Cliff (Subtransaction SLRU Cache Overflow) Postgres caches up to **64 active subtransactions** (nested `SAVEPOINT`s) per transaction in fast backend CPU memory. When an application framework or ORM creates more than 64 savepoints inside a single transaction (a common pattern in nested test suites or batch loops with `try/catch`), the 64-entry cache overflows. Once the cached subtransaction IDs overflow, relevant snapshot checks may need to consult `pg_subtrans` to resolve parent transaction IDs. That can introduce `LWLock:SubtransBuffer` contention and `IO:SlruRead` calls when the required SLRU page is not already available. Below is a normalized pathology fixture showing the signature to look for. A publishable claim about a live system still comes from its recorded samples:  Notice the severe execution breakdown: 1. **Query Parse & Index Scan (1.0ms, `CPU`)**: Finding the tuple on the page is instant. 2. **SLRU Buffer Contention (29.0ms, `LWLock:SubtransBuffer`)**: The fixture models repeated access to the subtransaction SLRU buffers. 3. **SLRU Reads (34.0ms, `IO:SlruRead`)**: PostgreSQL waits in an SLRU read call. The event does not prove whether the operating system served the bytes from its page cache or physical media. The fixture contrasts a 1.0ms parse-and-index phase with 63.0ms of modeled SLRU access and contention. A live capture determines whether a particular workload actually has this shape. #### The Memory Floor: Buffers & Caches Low-level memory locks coordinate access to `shared_buffers` and other shared structures. Sustained `LWLock:BufferContent` means backends contend for access to the contents of the same buffer—a hot-page signature, not a buffer-pin wait. | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_lwlock_buffers_content.png" width="75" alt="BufferContent"> | [[Workloads/LWLock/Buffers/BufferContent\|BufferContent]] | Waiting for access to a data page buffer. | | <img class="bestiary-thumb" src="assets/wl_lwlock_buffers_hamster.png" width="75" alt="BufferMapping"> | [[Workloads/LWLock/Buffers/BufferMapping\|BufferMapping]] | Waiting to find a page in the shared buffer cache. | | <img class="bestiary-thumb" src="assets/wl_ipc_bufferio.png" width="75" alt="BufferIo"> | [[Workloads/IPC/Storage/BufferIO\|BufferIo]] | Waiting for buffer I/O to complete. | | <img class="bestiary-thumb" src="assets/wl_bufferpin_bufferpin.png" width="75" alt="BufferPin"> | [[Workloads/BufferPin/BufferPin\|BufferPin]] | Waiting to acquire an exclusive pin on a buffer. | | <img class="bestiary-thumb" src="assets/wl_lw_sinvalread.png" width="75" alt="RelCacheInit"> | [[Workloads/LWLock/Catalog/RelCacheInit\|RelCacheInit]] | | #### Parallel Query Coordination Wait events related to the active execution of a parallel plan. `ExecuteGather` indicates the leader is waiting for workers. If this is high, you have **Parallel Skew**—one worker is stuck holding up the team. A multi-process plan trace makes that coordination visible across PIDs:  Wall-clock query time is not the sum of worker CPU. In this normalized fixture, workers overlap on I/O while the leader reports `IPC:BgworkerStartup` during startup and `IPC:ExecuteGather` while consuming worker output. Finishing coordination may instead appear as events such as `IPC:ParallelFinish`. | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_ipc_executegather.png" width="75" alt="ExecuteGather"> | [[Workloads/IPC/Parallel/ExecuteGather\|ExecuteGather]] | Waiting for parallel workers to produce results. | | <img class="bestiary-thumb" src="assets/wl_ipc_parallelbitmapscan.png" width="75" alt="ParallelBitmapScan"> | [[Workloads/IPC/Parallel/ParallelBitmapScan\|ParallelBitmapScan]] | Waiting for parallel bitmap scan to become available. | | <img class="bestiary-thumb" src="assets/wl_lwlock_parallel_hash_join.png" width="75" alt="ParallelHashJoin"> | [[Workloads/LWLock/Parallel/ParallelHashJoin\|ParallelHashJoin]] | Waiting for access to a parallel hash join state. | #### Worker Lifecycle & Synchronization Signals related to the startup, phase changes, and shutdown of parallel workers. `ParallelFinish` spikes when a query completes so quickly that the time spent managing workers exceeds the time spent processing rows. | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_ipc_parallelfinish.png" width="75" alt="ParallelFinish"> | [[Workloads/IPC/Parallel/ParallelFinish\|ParallelFinish]] | Waiting for parallel workers to finish. | | <img class="bestiary-thumb" src="assets/wl_lwlock_parallel_coordination.png" width="75" alt="ParallelAppend"> | [[Workloads/LWLock/Parallel/ParallelAppend\|ParallelAppend]] | Waiting for access to a shared parallel append state. | #### Shared Memory Management (DSM & DSA) Parallel workers communicate through Dynamic Shared Memory (DSM) segments. | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_ipc_xactgroupupdate.png" width="75" alt="XactGroupUpdate"> | [[Workloads/IPC/Transaction/XactGroupUpdate\|XactGroupUpdate]] | Waiting for a group transaction status update. | | <img class="bestiary-thumb" src="assets/wl_lw_parallelquerydsa.png" width="75" alt="ParallelQueryDSA"> | [[Workloads/LWLock/Parallel/ParallelQueryDSA\|ParallelQueryDSA]] | Waiting for access to parallel query dynamic shared memory state. | | <img class="bestiary-thumb" src="assets/wl_lwlock_dsm_registry.png" width="75" alt="DSMRegistry"> | [[Workloads/LWLock/Parallel/DSMRegistry\|DSMRegistry]] | Waiting for access to the dynamic shared memory registry. | #### Parallel Infrastructure: Hashing & Messaging Wait points for the "Interconnect" of the parallel engine. | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_ipc_hashbatchallocate.png" width="75" alt="HashBatchAllocate"> | [[Workloads/IPC/Hash/HashBatchAllocate\|HashBatchAllocate]] | Waiting for parallel hash join batch allocation. | | <img class="bestiary-thumb" src="assets/wl_ipc_hashbuildelect.png" width="75" alt="HashBuildElect"> | [[Workloads/IPC/Hash/HashBuildElect\|HashBuildElect]] | Waiting for parallel hash join build election. | | <img class="bestiary-thumb" src="assets/wl_ipc_messagequeuereceive.png" width="75" alt="MessageQueueReceive"> | [[Workloads/IPC/MessageQueue/MessageQueueReceive\|MessageQueueReceive]] | Waiting to receive data via a message queue. | | <img class="bestiary-thumb" src="assets/wl_ipc_messagequeuesend.png" width="75" alt="MessageQueueSend"> | [[Workloads/IPC/MessageQueue/MessageQueueSend\|MessageQueueSend]] | Waiting to send data via a message queue. | #### Transaction Identity & Serializability Wait events related to the generation of Transaction IDs and the enforcement of Serializable Snapshot Isolation (SSI). | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_ipc_xidgen.png" width="75" alt="XidGen"> | [[Workloads/LWLock/Xact/XidGen\|XidGen]] | Waiting to allocate a new transaction ID. | | <img class="bestiary-thumb" src="assets/wl_lw_predicatelockmanager.png" width="75" alt="PredicateLockManager"> | [[Workloads/LWLock/SSI/PredicateLockManager\|PredicateLockManager]] | Waiting to access predicate lock information. | | <img class="bestiary-thumb" src="assets/wl_lw_serializablexacthash.png" width="75" alt="SerializableXactHash"> | [[Workloads/LWLock/SSI/SerializableXactHash\|SerializableXactHash]] | Waiting to access the hash table of serializable transactions. | | <img class="bestiary-thumb" src="assets/wl_lw_serialslru.png" width="75" alt="SerialSLRU"> | [[Workloads/LWLock/Serial/SerialSLRU\|SerialSLRU]] | Waiting to access the serial log SLRU lock. | --- ## 7.9 - Human-Caused Stalls (Administrative Blocking) <img src="assets/arch_starvation.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> ### 1. The Idle Client Is Blocking Everyone Else "We have a critical lock contention on the `animals` table, but the blocker PID 5432 shows a `wait_event` of `ClientRead`. How can a process be 'waiting for the client' while also blocking others?" ### 2. The Physical Reality Not every stall is caused by the database itself. Sometimes Postgres is completely healthy, but the application cannot feed it work fast enough. This is **The Starvation**—the database engine sitting idle, waiting for the user or the application to send the next command. When this idle state occurs *inside* an open transaction, the client effectively holds database resources hostage while it performs external logic (like an API call). ### 3. The Wait Signature In `pg_stat_activity`, this state presents as a `Client` wait class: - **`Client:ClientRead`**: Postgres has finished its work and is waiting for the application to send the next query. - **`Client:ClientWrite`**: Postgres is waiting for the network buffer to clear so it can send more data back to the client. ### 4. The Investigation `Client:ClientRead` while **idle in transaction** happens *between* statements—after an `UPDATE` has finished and before the client sends `COMMIT`. There is no active plan node to attribute. The core PostgreSQL view shows the state and direct blocker chain: ```sql SELECT a.pid, a.state, a.wait_event_type, a.wait_event, now() - a.xact_start AS xact_age, pg_blocking_pids(a.pid) AS blocked_by, a.query FROM pg_stat_activity AS a WHERE a.state = 'idle in transaction' OR cardinality(pg_blocking_pids(a.pid)) > 0; ``` **Optional tracer track:** use its active view for a sampled fleet presentation, and a victim plan trace if operator context is needed: ```bash # Blocker is between statements — not inside a plan sudo ./pg_wait_tracer --view active --count 1 ``` ```text PID State Wait Event Wait (ms) Backend Type ────── ────────────────── ─────────────────── ────────── ──────────── 5432 idle in transaction Client:ClientRead — client 5501 waiting Lock:transactionid 14200.0 client 5502 waiting Lock:transactionid 12800.0 client 5503 waiting Lock:transactionid 11500.0 client ``` Below is a normalized two-process timeline showing one blocker and one representative waiter from this pattern:  The single forgotten `BEGIN` (PID 5432, resting in `Client:ClientRead`) is the **Root Blocker**; the representative victim is not a "slow UPDATE"—it is suspended in `Lock:transactionid` under `ModifyTable`. ### 5. The Root Cause The state of PID 5432 is **`idle in transaction`**. The application opened a transaction, issued an `UPDATE` (which acquired a lock), and then "went away" to do something else without committing. Postgres is now starving—waiting for the client to finish the transaction while faithfully holding the exclusive lock required by the uncommitted UPDATE. The downstream clients are blocked on `Lock:transactionid`. ### 6. The Strategic Fix Set an **`idle_in_transaction_session_timeout`** at the database level. This ensures that any session that "forgets" to commit will be automatically terminated after a short window. ```sql -- Terminate any session that stays idle in a transaction for > 1 minute ALTER SYSTEM SET idle_in_transaction_session_timeout = '1min'; SELECT pg_reload_conf(); ``` The database protects itself from "Human-Caused Stalls," ensuring that a single developer's forgotten `BEGIN` block cannot take down the production cluster. > [!NOTE] > **Recap**: Brief `Client:ClientRead` intervals can be normal. Pair the event with `state`, transaction age, retained locks, and snapshot age; a long `idle in transaction` interval is the dangerous signature. --- ### 🧪 Manipulation Lab: Idle in Transaction Disaster To see how an uncommitted write locks rows and blocks autovacuum cleanup, we will simulate an application transaction leak and query the transaction boundaries. #### The Task 1. Open Session A and simulate an application opening a transaction to modify a record without committing: ```sql BEGIN; UPDATE animals SET name = 'Babu the Great' WHERE id = 1; ``` Notice that Session A is now in the **`idle in transaction`** state. It holds an exclusive lock on the row with `id = 1`. 2. Open Session B and attempt to modify the same row: ```sql UPDATE animals SET name = 'Sir Babu' WHERE id = 1; ``` #### The ClientRead Backend Still Holds the Lock Look at Session B. The session hangs completely. It is waiting for the lock. 3. Open Session C and query the active sessions: ```sql SELECT pid, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state IN ('active', 'idle in transaction'); ``` Output: ``` pid | state | wait_event_type | wait_event | query ------+---------------------+-----------------+---------------+------------------------------- 9051 | idle in transaction | Client | ClientRead | UPDATE animals SET name = ... 9052 | active | Lock | transactionid | UPDATE animals SET name = ... ``` Notice that Session A (PID 9051) has a status of `idle in transaction` and is waiting on `ClientRead` (waiting for the client to send `COMMIT` or `ROLLBACK`). Session B (PID 9052) is `active` but blocked on `Lock:transactionid`. 4. Terminate the blocker from Session C to resolve the hang: ```sql SELECT pg_terminate_backend(9051); -- Replace with Session A's PID ``` Session A receives `FATAL: terminating connection due to administrator command`, and its transaction rolls back. Session B was not terminated: after the lock is released, its waiting `UPDATE` proceeds and can commit normally. #### A Timeout Contains the Abandoned Transaction A single uncommitted transaction that remains idle in transaction can easily queue up your entire application connection pool. Setting an `idle_in_transaction_session_timeout` is an essential safety boundary for production. --- ### The Client Bestiary When wait events are primarily in the `Client` or `Timeout` class, the bottleneck is external to the database engine. #### Client Interactions The primary wait points for the communication protocol between the client and the server. High AAS in the `Client` class usually points to application-side performance issues ("Think Time") or network bandwidth bottlenecks. | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_client_clientread.png" width="75" alt="ClientRead"> | [[Workloads/Client/ClientRead\|ClientRead]] | Waiting for data from the client. | | <img class="bestiary-thumb" src="assets/wl_client_clientwrite.png" width="75" alt="ClientWrite"> | [[Workloads/Client/ClientWrite\|ClientWrite]] | Waiting to send data to the client. | #### Intentional Throttling (Timeouts) Wait points where the database intentionally pauses a process. This can be at the user's request (e.g., calling `pg_sleep`) or due to background throttling to prevent resource exhaustion during administrative maintenance (like Vacuum or Base Backups). | Image | Event | What it records | | :---: | :--- | :--- | | <img class="bestiary-thumb" src="assets/wl_timeout_pgsleep.png" width="75" alt="PgSleep"> | [[Workloads/Timeout/PgSleep\|PgSleep]] | Waiting during a pg_sleep call. | | <img class="bestiary-thumb" src="assets/wl_timeout_basebackupthrottle.png" width="75" alt="BaseBackupThrottle"> | [[Workloads/Timeout/BaseBackupThrottle\|BaseBackupThrottle]] | Waiting during base backup throttling. | | <img class="bestiary-thumb" src="assets/wl_timeout_recoveryapplydelay.png" width="75" alt="RecoveryApplyDelay"> | [[Workloads/Timeout/RecoveryApplyDelay\|RecoveryApplyDelay]] | Waiting during recovery apply delay. | | <img class="bestiary-thumb" src="assets/wl_timeout_vacuumdelay.png" width="75" alt="VacuumDelay"> | [[Workloads/Timeout/VacuumDelay\|VacuumDelay]] | Waiting in a cost-based vacuum delay point. | --- ## 7.10 - High-Precision Lab (pg_wait_tracer) <img src="assets/arch_performance_audit.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> This entire section is an **optional third-party tooling track**. The core PostgreSQL workflow from Sections 7.1–7.9 remains sufficient for classifying work, waits, and blockers. Here we add higher-frequency capture and plan correlation to study failures that rarely travel alone: I/O can extend a transaction, the longer transaction can retain row locks, and the resulting queue can saturate a connection pool. > [!IMPORTANT] What You Need for Live Tracing (checked August 2026) > - **Tested with:** the `pg_wait_tracer` **v0.13 development line** and PostgreSQL 18. > - **Live capture:** Linux on the database host, normally kernel 5.8+ with BTF (or supported EL8 backports), plus root or the documented BPF, performance, and ptrace capabilities. > - **macOS and Windows:** study the normalized fixtures or use the replay and web clients; live capture is unavailable on those hosts. > - **Install:** build from the [pg_wait_tracer repository](https://github.com/DmitryNFomin/pg_wait_tracer) and pin the commit so the lab does not move beneath you. > [!CAUTION] > Tracing adds overhead and peers deeply into database processes. Measure that overhead away from production first. Trace files can contain sensitive query text and identifiers, so restrict access and treat attaching an elevated tracer to production as a production change. This lab is the **Capstone Synthesis**. We will use high-precision diagnostics not just to find a bottleneck, but to untangle a **Cascading Failure** and execute a post-mortem replay. --- ### 🧪 Synthesis Challenge: The Cascading Collapse **Find the First Cause in the Collapse**: "The whole site is slow. CPU is at 80%, Disk I/O is high, and there are hundreds of waiting connections. We don't know where to start." #### The Naive Approach Looking at a single signal. The "Sweat" (CPU) is high, so the developer assumes they need more cores. But the "Sigh" (I/O) is also high. #### The Evidence: The Causal Collision Trace Below is a normalized causal fixture. It models a transaction that acquires a row lock, then runs a slow report before committing; a concurrent update of that row must wait for the transaction to finish.  Notice how the collision aligns on the timeline: * **The Root Blocker (PID 1331426)**: Updates `orders.id = 42`, acquiring the row lock, then spends 700.0ms in `IO:DataFileRead` while running a report inside the same transaction. * **The Lock Victim (PID 1331500, `UPDATE`)**: Reaches the same row and waits in `Lock:transactionid` for PID 1331426 to finish. When the blocker commits, the transaction-ID wait ends and the victim can update the row. #### The Interpretation The plan traces expose the **Chain of Causality**: 1. **Cause**: The blocker modified the row before beginning a slow scan in the same transaction. 2. **Evidence (I/O)**: The scan extends the transaction lifetime with `IO:DataFileRead`. 3. **Evidence (Locking)**: The earlier update retains its row lock until transaction end; the I/O does not acquire the lock, but it prolongs its ownership. 4. **Evidence (Contention)**: The waiter's `UPDATE` spends its time in `Lock:transactionid`, not in useful work. The lock queue is downstream of an unnecessarily wide transaction. You can shorten it by committing the update before unrelated report work, moving the report outside the transaction, reading fewer blocks, or improving the report's access path. > [!TIP] > Use `--view active` and blocker relationships to discover the causal PIDs. Export plan traces for statements that are executing; an idle-in-transaction blocker sits between statements and therefore has no current plan node to attribute. #### The Strategic Fix Narrow the transaction boundary first, then optimize the report query if its I/O remains material. #### A Shorter Transaction Collapses the Wait Chain Once the row-changing transaction commits before the report begins, the report can still be slow without retaining the row lock. Indexing or cache improvements then address its I/O independently. --- ### 🔍 Post-Mortem: Replaying the Crash If the tracer was already recording in daemon mode during an incident, its stored trace files can be analyzed afterward. This is not PostgreSQL crash recovery; it is replay of previously captured observability data. **The Trigger**: The database crashed 10 minutes ago, and you need to prove what happened. ```bash # Replay the last hour from the configured trace directory ./pg_wait_tracer --replay -T /var/lib/pgwt/traces --from 1h ``` **The Interpretation**: By replaying the trace, you can see the AAS climb and compare the first sustained signature with later queues. Ordering suggests hypotheses; blocker relationships, query IDs, and exact wait samples establish causality. `CPU` is tracer running time, while names such as `Lock:relation` and `IO:DataFileRead` are PostgreSQL wait events. ### 🌐 The Distributed Span: Correlating Application HTTP to Postgres Wait Events (W3C Traceparent) Modern systems don't run in isolation. When an end-user experiences a 500ms API response time, the API gateway logs an HTTP trace, the ORM logs a database query span, and Postgres logs wait events. Historically, these systems were completely disconnected—database wait events lived in isolation from distributed tracing. When the application or driver includes a valid W3C `traceparent` in a SQL comment, this tested tool line can parse that context from captured query text and associate its exported trace data with the application trace. PostgreSQL itself does not inherit or interpret OpenTelemetry context. Below is a normalized cross-system correlation fixture. The application lanes are elapsed request envelopes, not PostgreSQL wait events; only the PostgreSQL lanes use wait-event classes.  Notice the seamless correlation across process tiers: 1. **API Gateway (PID 100)**: Receives `POST /api/v1/checkout` ($t=0\text{ms}$). 2. **Application ORM (PID 101)**: Opens the checkout transaction and propagates `traceparent: 00-4bf92f3577b34da6a3ce929d0e0e4736-...` via SQL comments. 3. **Postgres Leader Backend (PID 9001)**: Extracts the trace ID, runs parse/plan, reports `IPC:BgworkerStartup` during worker startup, and reports `IPC:ExecuteGather` while consuming worker output. 4. **Parallel Workers (PIDs 9002 & 9003)**: Execute partition scans concurrently (`IO:DataFileRead`, 14–17ms) and stream partial aggregations back to the leader. 5. **Checkout Write and Durability**: The leader finalizes the checkout writes and the committing backend reports `IO:WalSync` while synchronizing WAL. This unified timeline eliminates finger-pointing between application developers and database administrators. --- ### The Final Triage Protocol When the system collapses, use this definitive sequence to restore order: 1. **Quantify the AAS**: Total AAS measures concurrent running and waiting work. Compare the running/CPU component—not total AAS—to CPU core count when testing for CPU saturation. 2. **Identify the Class**: * **Running/CPU** → Inspect query complexity and scheduler saturation. * **IO** → Inspect the exact file operation, cache path, and storage latency. * **Lock** → Follow blockers and transaction boundaries. * **LWLock** → Identify the specific shared structure under contention. 3. **Find the Root**: Use `--view active` to identify the PID at the head of the chain. 4. **Attribute the Wait**: Export a plan trace for that PID so you know which operator owns the dominant wait event. 5. **Apply the Fix**: Fix the demonstrated cause, then capture the same view again. Event classes narrow the search; the exact event, query, process role, and blocker chain determine the remedy. > [!TIP] > **The Golden Rule of Diagnostics**: Follow causality, not whichever bar is largest. A CPU-heavy blocker can create lock waits; an I/O wait can prolong a transaction; and many `Client` or `Activity` waits are normal for their process role. --- ## 7.11 - Summary: Working, Waiting, or Blocking ### Chapter 7 Capstone: Production Incident Simulation Your API latency has exploded from 2ms to 30 seconds while CPU utilization is near 0%. That strongly suggests waiting or queueing, but the wait columns and blocker graph supply the diagnosis. The blocker is **idle in transaction** on `ClientRead`—between statements, so there is no plan node to attribute. Victims *do* have plan traces: ```text pid | state | wait_event_type | wait_event | query ------+---------------------+-----------------+---------------+------------------------------- 5432 | idle in transaction | Client | ClientRead | UPDATE orders SET status = ... 8501 | active | Lock | transactionid | UPDATE orders SET status = ... ```  Use your understanding of wait events and lock coordination to answer the troubleshooting questions. --- #### 1. What is the root cause PID of the latency spike? * **The Physical Reality**: **PID 5432**. It is `idle in transaction`. It has already modified the target row and is now waiting in `Client:ClientRead` for the client to send another command. Its uncommitted tuple version and transaction remain in place while it waits. #### 2. Why is PID 8501 blocked? * **The Physical Reality**: Its update reached a tuple version owned by the still-open transaction and waits on **`Lock:transactionid`** until PID 5432 commits or rolls back. #### 3. How do you resolve this incident immediately? * **The Physical Reality**: Terminate the root blocker PID 5432: ```sql SELECT pg_terminate_backend(5432); ``` This rolls back PID 5432's uncommitted transaction and releases its locks. PID 8501 can then resume; whether it commits depends on the rest of its transaction and error handling. #### 4. How do you prevent this incident from taking down production in the future? * **The Physical Reality**: Implement timeouts at the server configuration level: - **`idle_in_transaction_session_timeout = '1min'`**: Automatically terminates any transaction left open and idle for longer than 60 seconds. - **`lock_timeout = '5s'`**: Prevents application connections from hanging indefinitely in a lock queue, failing fast to preserve connection pool capacity. --- ### 📝 Summary: Working, Waiting, or Blocking Before this chapter, a slow query may have looked like a query that needed rewriting. Now you know latency has three faces. Sometimes the query is working: burning CPU, scanning rows, sorting, hashing, or joining. Sometimes it is waiting: stalled on disk, WAL, a lock, a latch, a client, or another backend. Sometimes it is the thing everyone else is waiting for. That distinction changes production debugging. You can now look at wait events and classify the incident before reaching for a fix. CPU pressure asks for plan and query work. I/O pressure asks about memory, access paths, and storage. Lock pressure asks who is holding the transaction open. LWLock pressure asks whether the architecture is forcing too many backends to fight over shared structures. > [!NOTE] Working, Waiting, or Making Others Wait > **Concept**: A query is not slow in the abstract. It is working, waiting, or making others wait. ### Sources & Further Reading - [PostgreSQL 18: Monitoring Database Activity](https://www.postgresql.org/docs/18/monitoring-stats.html) - [PostgreSQL 18: Wait Event Tables](https://www.postgresql.org/docs/18/monitoring-stats.html#WAIT-EVENT-TABLE) - [PostgreSQL 18: Explicit Locking](https://www.postgresql.org/docs/18/explicit-locking.html) - [PostgreSQL 18: `pg_stat_io`](https://www.postgresql.org/docs/18/monitoring-stats.html#MONITORING-PG-STAT-IO-VIEW) - Optional advanced track: [pg_wait_tracer repository](https://github.com/DmitryNFomin/pg_wait_tracer). <div style="page-break-after: always;"></div>