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 & Hashes| PRIV[work_mem & MemoryContext]:::engine
        BE <-->|Locks & 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 & 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) -> Grants AccessShareLock (< 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 -> Load 8KB block
        OS-->>SB: 8KB page loaded into buffer slot
    end
    BE->>SB: Pin Page (refcount++) & Acquire LWLock(Shared)
    BE->>SB: Traverse ItemId line pointer -> Evaluate HeapTupleSatisfiesMVCC()
    Note over BE,SB: Tuple xmin committed and visible in snapshot!
    BE->>SB: Release LWLock & 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 & 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 & 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 > (50 + 0.2 * Total_Rows)
    AL->>AW: Postmaster forks dedicated Autovacuum Worker
    AW->>Heap: Scans relation & acquires BufferCleanupLock on page
    AW->>Heap: HOT Pruning: Reclaims dead space (pd_lower / pd_upper expand)
    AW->>Idx: Sweeps B-Tree indexes & 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;`)¶