Skip to content

PostgreSQL — How It Works

Process model, shared memory, MVCC, WAL, query execution, and v18 async I/O.

Process Architecture

flowchart TB
    subgraph Postmaster["Postmaster (main process)"]
        PM["Process Manager\n(forks backends)"]
    end

    subgraph Backends["Backend Processes"]
        B1["Backend 1\n(client connection)"]
        B2["Backend 2"]
        BN["Backend N"]
    end

    subgraph Background["Background Processes"]
        WAL_W["WAL Writer"]
        Checkpointer["Checkpointer"]
        Autovac["Autovacuum"]
        BGWriter["Background Writer"]
        StatsCol["Stats Collector"]
    end

    subgraph SharedMem["Shared Memory"]
        SharedBuf["Shared Buffers\n(page cache)"]
        WAL_Buf["WAL Buffers"]
        CLOG["CLOG\n(transaction status)"]
    end

    PM --> Backends
    PM --> Background
    Backends --> SharedMem
    Background --> SharedMem

    style SharedMem fill:#1565c0,color:#fff

MVCC (Multi-Version Concurrency Control)

Each row has hidden xmin (created by) and xmax (deleted by) transaction IDs. Readers never block writers.

sequenceDiagram
    participant TX1 as Transaction 1 (xid=100)
    participant Heap as Table Heap
    participant TX2 as Transaction 2 (xid=101)

    TX1->>Heap: UPDATE row → creates new version (xmin=100)
    Note over Heap: Old version: xmax=100<br/>New version: xmin=100, xmax=∞
    TX2->>Heap: SELECT → sees old version (100 not committed yet)
    TX1->>TX1: COMMIT
    TX2->>Heap: SELECT → now sees new version

Async I/O (v18)

flowchart LR
    subgraph Old["v17 (Synchronous I/O)"]
        Req1_O["Read page 1"] --> Wait1["⏳ Wait"] --> Req2_O["Read page 2"] --> Wait2["⏳ Wait"]
    end

    subgraph New["v18 (Async I/O)"]
        Req1_N["Read page 1"]
        Req2_N["Read page 2"]
        Req3_N["Read page 3"]
        Req1_N --> Batch["io_uring batch\n(all in parallel)"]
        Req2_N --> Batch
        Req3_N --> Batch
        Batch --> Done["All pages ready\n(2-3× faster)"]
    end

    style Old fill:#c62828,color:#fff
    style New fill:#2e7d32,color:#fff

Sources