PostgreSQL Master Memory Map: The Connected Neural Architecture¶

This document is your single-glance visual memory map and revision blueprint. Every component, memory block, background daemon, disk file, and lock structure is mapped as a connected graph so you can trace how every dot connects to every other dot in the PostgreSQL organism.

1. The Master Visual Memory Maps (Modular Deep-Zoom System)¶

All diagrams follow a uniform, top-to-bottom (graph TD) layout with consistent color-coded nodes for effortless visual parsing.

1.1 The Executive Macro Ecosystem (The 30,000-Foot View)¶

graph TD
    classDef ingress fill:#e1f5fe,stroke:#0288d1,color:#01579b;
    classDef engine fill:#ede7f6,stroke:#512da8,color:#311b92;
    classDef storage fill:#fbe9e7,stroke:#d84315,color:#bf360c;
    classDef rep fill:#e0f2f1,stroke:#00796b,color:#004d40;

    subgraph INGRESS ["1. Client & Ingress Tier"]
        C["Client Applications"]:::ingress -->|"TCP 5432"| PB["PgBouncer Pooler"]:::ingress
        PB -->|"Multiplexed Sessions"| PM["Postmaster Daemon PID 1"]:::ingress
    end

    subgraph ENGINE ["2. Core Processing Engine Tier"]
        PM -->|"Forks Session"| BE["Backend Worker Process"]:::engine
        BE <-->|"Private Sorts and Hashes"| PRIV["work_mem and MemoryContext"]:::engine
        BE <-->|"Locks and Buffer Pool"| SHM[("Global Shared Memory IPC")]:::engine
    end

    subgraph STORAGE ["3. Operating System & Persistence Tier"]
        SHM <-->|"Double Buffering"| KPC["OS Page Cache"]:::storage
        KPC <-->|"8KB Physical Blocks"| DISK[("$PGDATA Table Data Files")]:::storage
        SHM -->|"Synchronous fsync"| WAL[("$PGDATA/pg_wal Write-Ahead Log")]:::storage
    end

    subgraph HA ["4. High Availability & Replication Tier"]
        WAL -->|"TCP Network Streaming"| REP["Standby Replicas and CDC Pipelines"]:::rep
    end

1.2 Regional Map 1: Process & Shared Memory Hub¶

graph TD
    classDef proc fill:#ede7f6,stroke:#512da8,color:#311b92;
    classDef mem fill:#e8f5e9,stroke:#388e3c,color:#1b5e20;
    classDef priv fill:#fff3e0,stroke:#f57c00,color:#e65100;

    PM["Postmaster Process"]:::proc -->|"Forks on Connect"| BE["Backend Worker"]:::proc
    PM -->|"Spawns on Boot"| CP["Checkpointer Process"]:::proc
    PM -->|"Spawns on Boot"| BGW["Background Writer"]:::proc
    PM -->|"Spawns on Boot"| WALW["WAL Writer Process"]:::proc
    PM -->|"Spawns on Boot"| AVL["Autovacuum Launcher"]:::proc
    AVL -->|"Forks on Dead Rows"| AVW["Autovacuum Worker"]:::proc

    BE --- WM["work_mem: Sort / Hash Joins"]:::priv
    BE --- TB["temp_buffers: Session Temp Tables"]:::priv
    BE --- MC["MemoryContext: Top / ExprContext"]:::priv
    BE --- FP["Fast-Path: fpLockBits"]:::priv

    BE <-->|"1. Checks Heavy Locks"| LM[("Lock Manager: LOCK / PROCLOCK")]:::mem
    BE <-->|"2. Captures Snapshot"| PA[("ProcArray: xmin / xmax / xip")]:::mem
    BE <-->|"3. Reads/Pins 8KB Pages"| SB[("shared_buffers 8KB Pool")]:::mem
    BE -->|"4. Writes Redo Deltas"| WB[("wal_buffers Cache")]:::mem

    SB --- CS["Clock Sweep: StrategyGetBuffer Eviction"]:::mem
    SB --- BHT["Buffer Hash Table: BufferTag to BufferDesc"]:::mem
    LM --- WQ["waitProcs: Sleeping Queue"]:::mem

1.3 Regional Map 2: Storage, Page & Tuple Anatomy¶

graph TD
    classDef mem fill:#e8f5e9,stroke:#388e3c,color:#1b5e20;
    classDef disk fill:#fbe9e7,stroke:#d84315,color:#bf360c;

    SB[("shared_buffers Cache")]:::mem <-->|"Miss fetches block"| KPC["OS Page Cache"]:::mem
    KPC <-->|"Physical File I/O"| HEAP[("$PGDATA/base Table Files")]:::disk

    HEAP --- PAGE["8KB Physical Block"]:::disk
    PAGE --- PH["PageHeaderData: pd_lsn, pd_lower, pd_upper"]:::disk
    PAGE --- LP["ItemIdData: Line Pointers lp_flags"]:::disk
    PAGE --- TUP["HeapTupleHeaderData: xmin, xmax, t_ctid"]:::disk

    TUP -.->|"If row exceeds 2KB"| TOAST["pg_toast: 2KB Chunk Table"]:::disk
    HEAP --- FSM["Free Space Map: .fsm Space Lookup"]:::disk
    HEAP --- VM["Visibility Map: .vm All-Visible / Frozen"]:::disk
    TUP <-->|"Checks Commit Status"| CLOG["pg_xact: 2-bit Status Committed / Aborted"]:::disk

1.4 Regional Map 3: WAL, Durability & Replication Pipeline¶

graph TD
    classDef proc fill:#ede7f6,stroke:#512da8,color:#311b92;
    classDef mem fill:#e8f5e9,stroke:#388e3c,color:#1b5e20;
    classDef disk fill:#fbe9e7,stroke:#d84315,color:#bf360c;
    classDef net fill:#e0f2f1,stroke:#00796b,color:#004d40;

    BE["Backend Process"]:::proc -->|"Appends XLogRecord"| WB[("wal_buffers Ring Buffer")]:::mem
    WB -->|"Synchronous fsync at COMMIT"| WALF[("$PGDATA/pg_wal 16MB Segments")]:::disk
    WALW["WAL Writer Process"]:::proc -.->|"Periodic Background Flush"| WALF

    CP["Checkpointer Process"]:::proc -->|"Flushes Dirty 8KB Pages"| HEAP[("Table Data Files")]:::disk
    CP -->|"Updates Last Checkpoint LSN"| CTRL["pg_control Global State"]:::disk

    WALF -->|"Streams WAL Bytes"| WALS["walsender Process"]:::proc
    WALS ==>|"TCP Network Stream"| WALR["walreceiver on Standby"]:::net
    WALR -->|"Replays to Memory"| SREP[("Standby shared_buffers")]:::mem
    SREP -->|"Serves READ-ONLY Queries"| RO["Hot Standby Clients"]:::net
    WALS -->|"Logical Decoding"| CDC["Kafka / Debezium Stream"]:::net

2. The 3 Primary Pathways in Motion (Interactive Visual Traces)¶

Pathway A: Trace of a Read Query (`SELECT * FROM orders WHERE id = 42;`)¶

sequenceDiagram
    autonumber
    actor Client as "Client App"
    participant PB as "PgBouncer"
    participant BE as "Backend Worker"
    participant LM as "Lock Manager"
    participant PA as "ProcArray (Snapshot)"
    participant SB as "shared_buffers"
    participant OS as "OS Page Cache / Disk"

    Client->>PB: SELECT * FROM orders WHERE id = 42
    PB->>BE: Assigns connection from pool
    BE->>LM: Check Fast-Path (fpLockBits) to grant AccessShareLock (under 80ns)
    BE->>PA: Capture SnapshotData (xmin=100, xmax=105, xip=102, 104)
    BE->>SB: Lookup page in Buffer Hash Table
    alt Cache Miss
        SB->>OS: StrategyGetBuffer() clock sweep to load 8KB block
        OS-->>SB: 8KB page loaded into buffer slot
    end
    BE->>SB: Pin Page (refcount++) and acquire LWLock(Shared)
    BE->>SB: Traverse ItemId line pointer to evaluate HeapTupleSatisfiesMVCC()
    Note over BE,SB: Tuple xmin committed and visible in snapshot!
    BE->>SB: Release LWLock and unpin Page (refcount--)
    BE->>PB: Return formatted TupleTableSlot result
    PB-->>Client: Result returned to application

Mechanical Summary:¶

Network: Ingress arrives through PgBouncer connection pool.
Locking: Backend checks Fast-Path lock bits in local memory; acquires AccessShareLock without touching global shared hash tables.
Snapshot: Consults ProcArray to freeze the active transaction horizon.
Buffer Pool: Evaluates buffer hash. If cached, increments refcount (PIN). If missed, triggers Clock Sweep eviction to load from OS/disk.
Visibility: Inspects ItemIdData offset, reads HeapTupleHeaderData, executes HeapTupleSatisfiesMVCC(), and returns row.
Release: Decrements refcount (UNPIN), increments usage_count, and sends network response.

Pathway B: Trace of a Write Query (`UPDATE accounts SET balance = balance - 100 WHERE id = 1;`)¶

sequenceDiagram
    autonumber
    actor Client as "Client App"
    participant BE as "Backend Worker"
    participant LM as "Lock Manager"
    participant SB as "shared_buffers (RAM)"
    participant WB as "wal_buffers (RAM)"
    participant WALDisk as "$PGDATA/pg_wal (Disk)"
    participant CLOG as "pg_xact (CLOG Disk)"
    participant CP as "Checkpointer"

    Client->>BE: UPDATE accounts SET balance = balance - 100 WHERE id = 1
    BE->>LM: Acquire RowExclusiveLock on table and Exclusive Lock on tuple
    BE->>SB: Pin Block in shared_buffers
    BE->>SB: Set old tuple xmax = Current_TXID (Marks old row as dead)
    BE->>SB: Insert new tuple (xmin = Current_TXID, balance = 400)
    BE->>SB: Link old tuple t_ctid to new tuple (HOT Chain)
    BE->>WB: Append XLogRecord (HEAP_UPDATE delta)
    BE->>SB: Update page pd_lsn = New_LSN and Mark BM_DIRTY = 1
    Client->>BE: COMMIT
    BE->>WALDisk: Flush wal_buffers to disk via synchronous fsync()
    BE->>CLOG: Write 2-bit commit status (01)
    BE-->>Client: Transaction Committed OK!
    Note over SB,CP: Dirty 8KB page remains in RAM until Checkpoint flushes it!
    CP->>SB: Checkpoint flushes dirty 8KB page to table data file on disk

Mechanical Summary:¶

Locking: Traverses B-Tree to find target CTID; claims RowExclusiveLock on table and tuple lock.
MVCC Duplication: Old tuple is marked with xmax = current_txid. A brand new tuple is created on the page. Old tuple's t_ctid points to the new tuple.
WAL Logging: Appends binary redo delta into wal_buffers, sets page pd_lsn, and marks buffer as BM_DIRTY.
The COMMIT Barrier: WAL is flushed to physical disk (fsync), 2-bit status is recorded in pg_xact, and success is returned. The dirty data page remains in memory until the Checkpointer flushes it minutes later.

Pathway C: Trace of Background Hygiene (Autovacuum & Space Reclamation)¶

sequenceDiagram
    autonumber
    participant Stats as "Stats Collector"
    participant AL as "Autovacuum Launcher"
    participant AW as "Autovacuum Worker"
    participant Heap as "8KB Heap Pages"
    participant Idx as "B-Tree Indexes"
    participant FSM as "Free Space Map (.fsm)"
    participant VM as "Visibility Map (.vm)"

    Stats->>AL: Reports dead tuple count exceeds threshold formula
    AL->>AW: Postmaster forks dedicated Autovacuum Worker
    AW->>Heap: Scans relation and acquires BufferCleanupLock on page
    AW->>Heap: HOT Pruning reclaims dead space (pd_lower / pd_upper expand)
    AW->>Idx: Sweeps B-Tree indexes and erases pointers to dead line pointers
    AW->>FSM: Updates Free Space Map with reusable byte offsets
    AW->>VM: Sets ALL_VISIBLE bit (Enables Index-Only Scans)
    AW->>Heap: Freezes old xmin with FrozenTransactionId (Wraparound Defense)

Mechanical Summary:¶

Telemetry: Stats collector tracks mutation counts; Launcher monitors thresholds every minute.
Worker Fork: Launcher signals Postmaster to spawn an un-throttled or cost-throttled worker.
Heap Pruning: Worker scans pages, reclaims dead tuple payload space, and expands the free space gap (pd_upper - pd_lower).
Index Sweep: Erases index leaf pointers referencing dead line pointers.
Catalog & Map Persistence: Updates .fsm for future INSERT targeting, sets .vm bits to enable Index-Only Scans, and freezes old tuples to neutralize the 2.14B XID Wraparound disaster.

3. Quick Revision Flashcard Matrix ("Connect the Cause to the Effect")¶

If this "Dot" changes / breaks...	Which connected "Dot" immediately suffers?	Why? (The Mechanical Link)
`shared_buffers` is sized $> 40\%$ of RAM	Linux Page Cache (`KPC`)	Double-buffering competition: Postgres and Linux cache identical 8KB blocks, starving private `work_mem` and dirty writeback buffers.
An application leaves a transaction `idle in transaction`	`Autovacuum Worker` & `$PGDATA/base` (Disk)	The idle transaction pins the global `xmin` horizon in `ProcArray`. Autovacuum cannot reclaim dead tuples or freeze old rows, causing runaway table bloat and XID wraparound.
An `ALTER TABLE` runs without `lock_timeout`	All web application `SELECT` queries	`ALTER TABLE` queues behind an active read; the Lock Manager puts all subsequent `SELECT`s to sleep behind the `ALTER TABLE` to prevent write starvation. Connection pool collapses.
A table column order has `BOOLEAN` before `BIGINT`	Page capacity (`pd_upper - pd_lower`)	CPU 8-byte word alignment forces Postgres to pad 7 empty zero bytes into the tuple header, wasting up to 20% of disk space and RAM cache capacity.
A query runs `ORDER BY` on data exceeding `work_mem`	Disk I/O (`$PGDATA/base/pgsql_tmp`)	Quicksort in RAM fails; backend spills sorted runs to temporary disk files and switches to External Merge Sort, degrading latency by 100x.
A replication standby crashes with an active `Replication Slot`	Primary server disk (`$PGDATA/pg_wal`)	The replication slot forbids the primary from deleting WAL. 16MB WAL segments accumulate indefinitely until the primary disk is 100% full, crashing the database.
*An application ORM runs `SELECT ` on a table with 50KB JSON**	Backend process CPU & Memory	Forces the executor to read the out-of-line `pg_toast` table, decompress LZ4/pglz chunks in memory, and allocate megabytes of RAM per query session.
`random_page_cost` remains at `4.0` on an NVMe SSD drive	Query Planner Optimizer	The planner falsely assumes non-sequential reads are 4x slower than sequential reads, foolishly choosing slow Sequential Scans over valid B-Tree Index Scans.
`full_page_writes = off` during a sudden server power failure	Database Recovery Engine (`xlog.c`)	Filesystems write in 4KB blocks; Postgres writes in 8KB pages. A torn page leaves half-written data on disk that cannot be recovered from incremental WAL deltas. Data corruption occurs.

If this "Dot" changes / breaks...	Which connected "Dot" immediately suffers?	Why? (The Mechanical Link)
`shared_buffers` is sized \(> 40\%\) of RAM	Linux Page Cache (`KPC`)	Double-buffering competition: Postgres and Linux cache identical 8KB blocks, starving private `work_mem` and dirty writeback buffers.
An application leaves a transaction `idle in transaction`	`Autovacuum Worker` & `$PGDATA/base` (Disk)	The idle transaction pins the global `xmin` horizon in `ProcArray`. Autovacuum cannot reclaim dead tuples or freeze old rows, causing runaway table bloat and XID wraparound.
An `ALTER TABLE` runs without `lock_timeout`	All web application `SELECT` queries	`ALTER TABLE` queues behind an active read; the Lock Manager puts all subsequent `SELECT`s to sleep behind the `ALTER TABLE` to prevent write starvation. Connection pool collapses.
A table column order has `BOOLEAN` before `BIGINT`	Page capacity (`pd_upper - pd_lower`)	CPU 8-byte word alignment forces Postgres to pad 7 empty zero bytes into the tuple header, wasting up to 20% of disk space and RAM cache capacity.
A query runs `ORDER BY` on data exceeding `work_mem`	Disk I/O (`$PGDATA/base/pgsql_tmp`)	Quicksort in RAM fails; backend spills sorted runs to temporary disk files and switches to External Merge Sort, degrading latency by 100x.
A replication standby crashes with an active `Replication Slot`	Primary server disk (`$PGDATA/pg_wal`)	The replication slot forbids the primary from deleting WAL. 16MB WAL segments accumulate indefinitely until the primary disk is 100% full, crashing the database.
*An application ORM runs `SELECT ` on a table with 50KB JSON**	Backend process CPU & Memory	Forces the executor to read the out-of-line `pg_toast` table, decompress LZ4/pglz chunks in memory, and allocate megabytes of RAM per query session.
`random_page_cost` remains at `4.0` on an NVMe SSD drive	Query Planner Optimizer	The planner falsely assumes non-sequential reads are 4x slower than sequential reads, foolishly choosing slow Sequential Scans over valid B-Tree Index Scans.
`full_page_writes = off` during a sudden server power failure	Database Recovery Engine (`xlog.c`)	Filesystems write in 4KB blocks; Postgres writes in 8KB pages. A torn page leaves half-written data on disk that cannot be recovered from incremental WAL deltas. Data corruption occurs.

PostgreSQL Master Memory Map: The Connected Neural Architecture¶

1. The Master Visual Memory Maps (Modular Deep-Zoom System)¶

1.1 The Executive Macro Ecosystem (The 30,000-Foot View)¶

1.2 Regional Map 1: Process & Shared Memory Hub¶

1.3 Regional Map 2: Storage, Page & Tuple Anatomy¶

1.4 Regional Map 3: WAL, Durability & Replication Pipeline¶

2. The 3 Primary Pathways in Motion (Interactive Visual Traces)¶

Pathway A: Trace of a Read Query (SELECT * FROM orders WHERE id = 42;)¶

Mechanical Summary:¶

Pathway B: Trace of a Write Query (UPDATE accounts SET balance = balance - 100 WHERE id = 1;)¶

Mechanical Summary:¶

Pathway C: Trace of Background Hygiene (Autovacuum & Space Reclamation)¶

Mechanical Summary:¶

3. Quick Revision Flashcard Matrix ("Connect the Cause to the Effect")¶

Pathway A: Trace of a Read Query (`SELECT * FROM orders WHERE id = 42;`)¶

Pathway B: Trace of a Write Query (`UPDATE accounts SET balance = balance - 100 WHERE id = 1;`)¶