PostgreSQL Internals: Under the Hood of MVCC, WAL, and VACUUM

PostgreSQL is celebrated for its rock-solid reliability, extensibility, and transactional compliance. However, when database systems hit high write volumes—thousands of updates and deletes per second—superficial knowledge of SQL syntax is no longer enough. Performance cliffs, write amplification, and runaway table bloat emerge from the physical mechanics of the PostgreSQL storage engine.

To design high-throughput systems, diagnose latency spikes, and prevent catastrophic downtime, database engineers and architects must understand what happens beneath the SQL interface: inside the memory buffers, on the physical 8KB disk pages, and across transaction boundaries.

This architectural guide unpacks the foundational internal engines of PostgreSQL:

1. The Physical Page and Tuple Layout (8KB page anatomy, Line Pointers, HeapTuple headers).
2. Multi-Version Concurrency Control (MVCC) (Visibility algorithms, transaction snapshots, and pg_xact).
3. The Write-Ahead Log (WAL) and Buffer Manager (Dirty buffers, checkpoints, and ARIES-style crash recovery).
4. Table Bloat and the VACUUM Engine (Two-phase vacuuming, Free Space Maps, Visibility Maps, and Heap-Only Tuple optimization).
5. The Transaction ID Wraparound Catastrophe (32-bit transaction math and aggressive freezing).
6. Forensic Inspection and Production Tuning (Using pageinspect to read raw disk pages and calibrating autovacuum for high write throughput).

---

1. Physical Storage Architecture: The 8KB Page Anatomy

PostgreSQL stores table and index data on disk in units of fixed-size blocks known as pages, with a default compile-time size of 8,192 bytes (8KB). A table is physically partitioned into files (called segments, capped at 1GB each) stored inside the database cluster’s base directory ($PGDATA/base/<database_oid>/<table_filenode>).

Every 8KB page is structured to grow from both ends toward the middle, maximizing storage efficiency and avoiding fragmentation.

+----------------------------------------------------------------+
| PageHeaderData (24 bytes)                                      |
|  - pd_lsn (8B)       : Log Sequence Number of last WAL update  |
|  - pd_checksum (2B)  : Page validation checksum                |
|  - pd_flags (2B)     : Page state flags                        |
|  - pd_lower (2B)     : Byte offset to end of line pointers     |
|  - pd_upper (2B)     : Byte offset to start of newest tuple    |
|  - pd_special (2B)   : Byte offset to special space (e.g. B-Tree)|
|  - pd_pagesize_version: Page layout version and page size      |
+----------------------------------------------------------------+
| Line Pointers / ItemIds (4 bytes each: offset, flags, length)  |
|  [ ItemId 1 ] -> points to Tuple 1 at byte offset 8100         |
|  [ ItemId 2 ] -> points to Tuple 2 at byte offset 8010         |
|  [ ItemId 3 ] (allocated downward -> grows toward middle)      |
+----------------------------------------------------------------+
|                   <--- UNALLOCATED FREE SPACE --->             |
|                       (pd_lower to pd_upper)                   |
+----------------------------------------------------------------+
| Actual Heap Tuples / Rows (allocated upward from bottom)       |
|  [ Tuple 3 data (variable length) ]                            |
|  [ Tuple 2 data (variable length) ]                            |
|  [ Tuple 1 data (variable length) ]                            |
+----------------------------------------------------------------+
| Special Space (Index-specific metadata; 0 bytes for heap pages)|
+----------------------------------------------------------------+

Key Page Header Pointers

• pd_lower: Points to the byte offset immediately following the last allocated line pointer (ItemId). Line pointers are written top-to-bottom.
• pd_upper: Points to the byte offset of the beginning of the most recently inserted tuple data. Tuple payloads are written bottom-to-top.
• Free Space Gap: The contiguous available byte count in the page is calculated directly as: $$\text{Free Space} = \text{pd_upper} - \text{pd_lower}$$ When pd_lower == pd_upper, the page has zero contiguous unallocated free space.

The ItemId (Line Pointer) Indirection

Indexes in PostgreSQL do not store direct byte offsets of table rows. Instead, indexes store a 6-byte Tuple Identifier (TID / ctid): $$\text{ctid} = (\text{BlockNumber}, \text{OffsetNumber})$$

For example, (42, 3) refers to Block 42, Line Pointer Index 3. The 3rd line pointer in Block 42 provides the actual physical byte offset within that page. This indirection allows PostgreSQL to defragment, shuffle, or compact rows inside an 8KB page without updating every index pointing to those rows.

---

2. The Heap Tuple Header Anatomy

Every row stored in a table page starts with a mandatory HeapTupleHeaderData struct, consuming a minimum of 23 bytes before any user column data:

+-----------------------------------------------------------------------+
| t_xmin (4 bytes)       : 32-bit Transaction ID that inserted the row  |
+-----------------------------------------------------------------------+
| t_xmax (4 bytes)       : 32-bit Transaction ID that deleted or updated |
+-----------------------------------------------------------------------+
| t_cid / t_xvac (4 bytes): Command identifier within the transaction   |
+-----------------------------------------------------------------------+
| t_ctid (6 bytes)       : (BlockNumber, OffsetNumber) pointing to self |
|                          or to a newer version if updated             |
+-----------------------------------------------------------------------+
| t_infomask2 (2 bytes)  : Attribute count + HOT update flag bits       |
+-----------------------------------------------------------------------+
| t_infomask (2 bytes)   : Status flags (committed, aborted, frozen)    |
+-----------------------------------------------------------------------+
| t_hoff (1 byte)        : Total header byte offset to data payload     |
+-----------------------------------------------------------------------+
| [Null Bitmap]          : Present if any nullable column contains NULL  |
+-----------------------------------------------------------------------+
| User Column Data       : Binary serialized column values              |
+-----------------------------------------------------------------------+

Critical Header Fields

• t_xmin: The transaction ID (XID) that created this row version.
• t_xmax: The transaction ID that deleted this row version or created an updated replacement version. If the row is currently alive and untouched, t_xmax is 0.
• t_ctid: If the row has not been updated, t_ctid points to itself (e.g., (10, 2) points to Block 10, Item 2). If updated, t_ctid is rewritten to point to the new tuple version’s location (e.g., (10, 4) or (11, 1)), establishing a forward version chain.
• t_infomask: Bitwise flags that cache transaction status directly on the tuple:
  • HEAP_XMIN_COMMITTED (0x0100): The creating transaction is known to have committed.
  • HEAP_XMIN_INVALID (0x0200): The creating transaction aborted; this tuple was never committed.
  • HEAP_XMAX_COMMITTED (0x0400): The deleting/updating transaction committed.
  • HEAP_XMAX_INVALID (0x0800): The deleting transaction aborted, or t_xmax is unused.
  • HEAP_XMIN_FROZEN (0x0300): The tuple’s t_xmin is older than the freezing horizon and is permanently visible to all transactions.

---

3. How MVCC Works: Visibility and Snapshot Isolation

Unlike database engines that implement concurrency control via undo-logs (such as MySQL InnoDB or Oracle), PostgreSQL implements Multi-Version Concurrency Control (MVCC) directly in the table heap.

In PostgreSQL:

• An INSERT creates a new tuple with t_xmin = current_xid and t_xmax = 0.
• A DELETE does not erase data from disk. It sets t_xmax = current_xid on the target tuple.
• An UPDATE performs an atomic two-step sequence: it marks the existing tuple as expired (t_xmax = current_xid) and inserts a brand new tuple with t_xmin = current_xid.

The Snapshot Engine

When a query executes, the transaction acquires a SnapshotData structure:

typedef struct SnapshotData {
    TransactionId xmin;        /* All XIDs < xmin are already committed & visible */
    TransactionId xmax;        /* All XIDs >= xmax are in the future & invisible */
    uint32        xcnt;        /* Number of active XIDs in xip array */
    TransactionId *xip;        /* List of active (in-flight) XIDs at snapshot time */
} SnapshotData;

           Snapshot Boundary
  ------------+------------------------+-------------> Transaction IDs
              |                        |
     XID < xmin:                   XID >= xmax:
  Guaranteed COMMITTED          In the FUTURE
   (Visible to query)         (Invisible to query)

              | <--- In-flight Window ---> |
                 If XID in xip array: IN-PROGRESS (Invisible)
                 If XID not in xip array: COMMITTED (Visible)

The Tuple Visibility Determination Algorithm

For a standard SELECT query under READ COMMITTED or REPEATABLE READ, visibility for any given tuple evaluates as follows:

Is tuple visible to current transaction T?
  1. Has t_xmin committed?
     NO  -> Is t_xmin == current_tx?
            YES -> Visible if created by an earlier command in this tx (check t_cid).
            NO  -> INVISIBLE.
     YES -> Continue to check t_xmax.

  2. Is t_xmin in current transaction's snapshot?
     If t_xmin >= snapshot.xmax -> INVISIBLE (born in the future).
     If t_xmin in snapshot.xip  -> INVISIBLE (in-flight when query started).
     If t_xmin < snapshot.xmin  -> Continue to step 3.

  3. Has t_xmax been set?
     NO (t_xmax == 0) -> VISIBLE (row is alive).
     YES -> Did t_xmax commit?
            NO  -> Is t_xmax == current_tx?
                   YES -> INVISIBLE (deleted by current tx).
                   NO  -> VISIBLE (deleting tx aborted or still in-flight).
            YES -> Was t_xmax committed before snapshot was taken?
                   YES -> INVISIBLE (row was dead before query started).
                   NO  -> VISIBLE (deletion committed after snapshot taken).

Why Commit Hints Matter

Evaluating whether an arbitrary transaction ID committed or aborted requires reading the Commit Log (pg_xact), an on-disk ring buffer tracking 2 bits per transaction (COMMITTED, ABORTED, IN_PROGRESS, SUB_COMMITTED).

Checking pg_xact on every row scan would create severe I/O and lock contention on the commit log. To avoid this, PostgreSQL uses Commit Hint Bits: the very first query that inspects a tuple and looks up its XID in pg_xact writes the result directly back to the tuple’s t_infomask (setting HEAP_XMIN_COMMITTED or HEAP_XMIN_INVALID). Subsequent readers evaluate t_infomask instantly without touching pg_xact.

---

4. The Write-Ahead Log (WAL) and Shared Buffers

PostgreSQL guarantees durability and ACID transactions through write-ahead logging following the ARIES protocol: no database page modified in memory may be flushed to disk until the corresponding log entry describing the change has been flushed to persistent storage.

[ Client App ]
      │  SQL INSERT / UPDATE
      ▼
[ Backend Process ]
      │
      ├── 1. Acquire Buffer Lock on target 8KB page in Shared Buffers
      ├── 2. Construct WAL record in WAL Buffers
      ├── 3. Modify 8KB Page in memory (Marks page DIRTY in Shared Buffers)
      ├── 4. On COMMIT: Issue XLogFlush() -> WAL flushed to disk ($PGDATA/pg_wal)
      │
      ▼
 [ Disk: pg_wal/000000010000... ]  <-- DURABILITY ACHIEVED (Fast sequential I/O)

-----------------------------------------------------------------------------------
Asynchronous Background Persistence:
 [ Shared Buffers (Dirty Pages) ]
      │
      ▼
 [ Checkpointer / Background Writer ]
      │
      ▼
 [ Disk: Table Heap Data Files ($PGDATA/base/...) ] (Random I/O)

Shared Buffers and Dirty Pages

1. PostgreSQL allocates a shared memory region called shared_buffers (typically tuned to 25% to 40% of system RAM).
2. When a query needs to read or write a row, it checks shared_buffers for the page. If missing, it issues an OS read() call to bring the 8KB page from disk into shared_buffers.
3. An UPDATE or INSERT modifies the page in shared_buffers and flags the buffer header as DIRTY.
4. The page is not immediately written back to the table file on disk. Doing so would force high-latency random I/O on every commit.

The Checkpoint Engine

A Checkpoint is a recurring synchronization point where all dirty shared buffers are flushed to disk, and a checkpoint record is written to WAL.

• Checkpoints are triggered when checkpoint_timeout expires (default: 5 minutes) or when max_wal_size is reached (default: 1GB).
• Smooth Checkpointing: The checkpointer spreads dirty buffer writes across checkpoint_completion_target * checkpoint_timeout (e.g., $0.9 \times 30\text{m} = 27\text{m}$) to eliminate I/O throughput spikes.
• Full Page Writes (FPW): Following a checkpoint, the very first write to any 8KB page writes the entire 8KB page image into WAL. This guarantees that if the server suffers an abrupt power loss while writing an 8KB block (a torn page write), PostgreSQL recovery restores the full page image from WAL rather than attempting to apply a delta to a corrupted block.

---

5. Table Bloat and the VACUUM Engine

Because MVCC leaves expired row versions in the table heap, high-update tables inevitably accumulate dead tuples.

Page Block 0 (Before Vacuum)
+-------------------------------------------------------------------+
| ItemId 1 [Valid] | ItemId 2 [Dead] | ItemId 3 [Dead] | ItemId 4   |
+-------------------------------------------------------------------+
| ... free space ...                                                |
+-------------------------------------------------------------------+
| Tuple 4 [Live]   | Tuple 3 [Dead]  | Tuple 2 [Dead]  | Tuple 1    |
+-------------------------------------------------------------------+

If dead tuples are never removed:

1. Tables and indexes swell in disk size (table bloat and index bloat).
2. Sequential scans and index traversals must read exponentially more 8KB pages from disk.
3. Cache hit ratios plummet because dead tuples occupy space in shared_buffers.

The Two-Phase VACUUM Process

A standard lazy VACUUM (non-blocking) operates in two distinct phases:

Phase 1: Heap Scan
  Iterate through heap pages.
  Collect physical TIDs of dead tuples into memory (limited by maintenance_work_mem).

Phase 2: Index Scan
  For EACH index on the table:
    Scan the index structure.
    Remove all index entries pointing to the dead TIDs collected in Phase 1.

Phase 3: Heap Cleanup & Free Space Recording
  Re-visit heap pages.
  Mark dead Line Pointers as UNUSED.
  Defragment contiguous free space in the page (adjust pd_lower and pd_upper).
  Update Visibility Map (VM) and Free Space Map (FSM).

The Auxiliary Maps

1. Free Space Map (FSM):
   • Stored in files ending with _fsm.
   • Organizes free space per page into a binary tree structure.
   • When a new INSERT needs a page with $\ge 200$ free bytes, PostgreSQL checks the FSM in memory to find an existing candidate block rather than extending the table file on disk.
2. Visibility Map (VM):
   • Stored in files ending with _vm.
   • Tracks 2 bits per 8KB heap page:
     • All-Visible Bit: Set to 1 if every tuple on the page is committed and visible to all current and future transactions. This enables Index-Only Scans (the executor satisfies queries entirely from the index without reading the heap page).
     • All-Frozen Bit: Set to 1 if every tuple on the page has been frozen by vacuum, skipping the page in future aggressive freeze scans.

Heap-Only Tuples (HOT) Optimization

Index maintenance is the most expensive part of an UPDATE. If a table has 6 indexes, a single UPDATE typically writes 1 new heap tuple and 6 new index entries.

PostgreSQL solves this with HOT (Heap-Only Tuples):

• Condition 1: The columns modified by the UPDATE are not referenced by any index on the table.
• Condition 2: The 8KB page containing the original tuple has enough free space to accommodate the new tuple version.

Without HOT:
  Index Entry 1 -> points to (Block 0, Item 1) [Old Tuple]
  Index Entry 2 -> points to (Block 0, Item 2) [New Tuple]  <-- Index Bloat!

With HOT Optimization:
  Index Entry   -> points to (Block 0, Item 1)
                                      │
  Block 0 Heap Page                   ▼
  [ ItemId 1 ] ----------------> [ Old Tuple ]
        │ (marked HOT_UPDATED)        │ t_ctid
        │                             ▼
  [ ItemId 2 ] (no index entry) -> [ New Tuple (HOT) ]

When HOT executes:

• No index entries are written.
• The old tuple’s line pointer is linked directly to the new tuple’s line pointer inside the same page.
• Subsequent SELECT queries traversing the index hit Item 1 and follow the internal page chain to Item 2.
• A lightweight in-page pruning operation (lazy_check_prune) recycles the old tuple space during regular read operations without waiting for VACUUM.

---

6. The Transaction ID (XID) Wraparound Catastrophe

PostgreSQL uses a 32-bit integer for transaction IDs, yielding $2^{32} \approx 4,294,967,296$ unique IDs.

Because transactions run continuously in 24/7 production systems, an active database can easily execute 4 billion transactions over months or years.

The Modular Math & The 2-Billion Limit

To allow transaction IDs to wrap around safely, PostgreSQL treats transaction numbers modularly:

• For any active transaction ID $T$, the preceding $2^{31} - 1$ IDs (2.1 billion) are considered to have happened in the past.
• The subsequent $2^{31}$ IDs are considered to be in the future.

                       XID Space (Modulo 2^32)
                     Past <--- Current XID ---> Future
                   [ 2.1 Billion ]   (T)   [ 2.1 Billion ]

The Catastrophic Failure Mode

If a database runs past 2.1 billion transactions without freezing old tuples, modular arithmetic flips: transactions from the distant past suddenly appear to be in the future, rendering all historical data completely invisible.

To prevent silent data corruption, PostgreSQL implements a hard stop:

1. At 200 million transactions before wraparound (autovacuum_freeze_max_age), PostgreSQL triggers an aggressive, un-cancellable autovacuum across all tables.
2. At 3 million transactions remaining, PostgreSQL issues high-priority warnings to the logs.
3. At 1 million transactions remaining, PostgreSQL shuts down entirely and refuses to start, accepting only single-user recovery mode commands to force a vacuum freeze.

How VACUUM FREEZE Solves It

During a freezing vacuum scan, PostgreSQL inspects tuples whose t_xmin is older than the freeze horizon (vacuum_freeze_min_age). It sets the HEAP_XMIN_FROZEN bit in t_infomask.

A frozen tuple is treated as permanently older than any conceivable transaction ID, immunizing it from modular wraparound comparison forever.

---

7. Forensic Diagnostics with pageinspect

To see physical storage internals in action, PostgreSQL provides the standard contrib module pageinspect.

Step 1: Install Extension and Create Test Data

CREATE EXTENSION IF NOT EXISTS pageinspect;

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    name TEXT,
    balance NUMERIC
) WITH (fillfactor = 70);

INSERT INTO accounts (name, balance) VALUES ('Alice', 1000.00);

Step 2: Inspect the Page Header

SELECT 
    lsn,
    checksum,
    flags,
    lower,
    upper,
    special,
    pagesize
FROM page_header(get_raw_page('accounts', 0));

Expected Result:

      lsn      | checksum | flags | lower | upper | special | pagesize 
---------------+----------+-------+-------+-------+---------+----------
 0/01A4B820    |        0 |     0 |    28 |  8128 |    8192 |     8192

Notice: lower is at byte 28 (24 bytes for PageHeaderData + 4 bytes for ItemId 1). upper is at 8128 (8192 minus the 64-byte row payload).

Step 3: Inspect the Heap Tuple Header Fields

SELECT 
    lp AS item_offset,
    t_xmin,
    t_xmax,
    t_field3 AS t_cid,
    t_ctid,
    to_hex(t_infomask::integer) AS infomask_hex,
    to_hex(t_infomask2::integer) AS infomask2_hex
FROM heap_page_items(get_raw_page('accounts', 0));

Result:

 item_offset | t_xmin | t_xmax | t_cid | t_ctid | infomask_hex | infomask2_hex 
-------------+--------+--------+-------+--------+--------------+---------------
           1 |    742 |      0 |     0 | (0,1)  | 0902         | 0003

Interpretation:

• t_xmin = 742: Created by transaction 742.
• t_xmax = 0: Active, alive, un-deleted.
• t_ctid = (0,1): Points to Block 0, Item 1 (self).
• infomask2 = 0003: 3 user columns (id, name, balance).

Step 4: Execute an UPDATE and Observe HOT Chaining

-- Update Alice without touching indexed column 'id'
UPDATE accounts SET balance = 1250.00 WHERE id = 1;

SELECT 
    lp AS item_offset,
    t_xmin,
    t_xmax,
    t_ctid,
    to_hex(t_infomask::integer) AS infomask_hex,
    to_hex(t_infomask2::integer) AS infomask2_hex
FROM heap_page_items(get_raw_page('accounts', 0));

Result:

 item_offset | t_xmin | t_xmax | t_ctid | infomask_hex | infomask2_hex 
-------------+--------+--------+--------+--------------+---------------
           1 |    742 |    743 | (0,2)  | 2002         | 4003
           2 |    743 |      0 | (0,2)  | 2002         | 8003

Physical Proof of HOT:

1. Tuple 1 now has t_xmax = 743 and its t_ctid points forward to (0, 2).
2. Tuple 1’s infomask2 has the HEAP_HOT_UPDATED bit set (0x4000).
3. Tuple 2 was created by t_xmin = 743, has t_xmax = 0, and its infomask2 has the HEAP_ONLY_TUPLE bit set (0x8000).
4. No index changes occurred. The B-Tree index on accounts_pkey still points only to (0, 1).

---

8. Production Autovacuum Calibration Architecture

Default PostgreSQL configurations for autovacuum were designed decades ago for modest hardware. In production databases handling sustained write traffic, uncalibrated defaults cause autovacuum to fall hopelessly behind write rates, leading to bloat runaway and I/O thrashing.

The Cost-Based Delay Engine

Autovacuum workers throttle themselves using a token bucket cost mechanism:

• Reading a page in shared_buffers: cost = 1.
• Reading a page from OS cache/disk: cost = 2.
• Writing a dirty page to disk: cost = 20.

When the worker’s accumulated cost hits autovacuum_vacuum_cost_limit, it sleeps for autovacuum_vacuum_cost_delay.

High-Throughput Production Tuning Recipe

Apply these settings in postgresql.conf for write-heavy OLTP databases (16+ cores, NVMe storage):

# =====================================================================
# PostgreSQL Production Autovacuum & Memory Tuning (64GB RAM, NVMe)
# =====================================================================

# Memory Sizing
shared_buffers = 16GB                  # 25% of system RAM
maintenance_work_mem = 2GB             # Memory per vacuum worker for dead TIDs

# Autovacuum Workers & Cost Limits
autovacuum = on
autovacuum_max_workers = 5             # Scale workers to avoid single-table bottleneck
autovacuum_vacuum_cost_limit = 2000    # Default 200 is 10x too slow for modern SSDs!
autovacuum_vacuum_cost_delay = 2ms     # Minimal sleep (default was 20ms in older versions)

# Trigger Thresholds (Scale factor + Flat threshold)
# Vacuum triggers when dead tuples > (autovacuum_vacuum_scale_factor * table_tuples) + threshold
autovacuum_vacuum_scale_factor = 0.05  # Trigger at 5% dead tuples instead of default 20%
autovacuum_vacuum_threshold = 1000     # Minimum 1,000 dead rows before triggering
autovacuum_analyze_scale_factor = 0.02 # Keep planner statistics fresh at 2% drift

# Checkpoint Tuning to eliminate I/O spikes
max_wal_size = 32GB                    # Prevent frequent checkpointing under write bursts
min_wal_size = 4GB
checkpoint_timeout = 30min             # Smooth checkpoint pacing
checkpoint_completion_target = 0.9     # Spread writes across 90% of duration (27 minutes)

Table-Specific Override for Ultra-Hot Tables

For tables processing over 10 million rows with high update frequencies (e.g., job queues, financial balances), configure table-level parameters to prevent global queue starvation:

ALTER TABLE accounts SET (
    autovacuum_vacuum_scale_factor = 0.01,  -- Trigger at 1% dead tuples
    autovacuum_vacuum_threshold = 500,
    autovacuum_vacuum_cost_limit = 5000,   -- Dedicated high-speed vacuuming
    fillfactor = 80                        -- Leave 20% page room for HOT updates
);

Essential Monitoring Query: Diagnosing Table Bloat and Dead Tuples

Run this query in production to identify tables in urgent need of vacuuming:

SELECT 
    schemaname,
    relname AS table_name,
    n_live_tup AS live_rows,
    n_dead_tup AS dead_rows,
    ROUND((n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0)::numeric) * 100, 2) AS dead_tuple_pct,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_rows DESC
LIMIT 10;

---

Architectural Summary Matrix

Engine Subsystem	Physical Mechanism	Primary Failure Mode	Architectural Best Practice
Storage Pages	8KB blocks, top-down Line Pointers, bottom-up Tuples	Contiguous space exhaustion	Set fillfactor < 100 on frequently updated tables to leverage HOT updates
MVCC Engine	Multi-versioning via t_xmin / t_xmax in heap headers	Dead tuple accumulation	Maintain short transaction durations; avoid long-running idle transactions
WAL & Buffers	Append-only sequential log before memory page flushes	Torn pages, checkpoint I/O spikes	Keep checkpoint_completion_target = 0.9 and tune max_wal_size
VACUUM Engine	2-phase cleanup using Visibility and Free Space Maps	Table and index bloat runaway	Raise autovacuum_vacuum_cost_limit to 2000+ on NVMe storage
Transaction ID	32-bit modular transaction counter	Database freeze at 2B transactions	Monitor pg_database.datfrozenxid and verify autovacuum freeze workers run