PostgreSQL architecture

See the whole system move.

A client request becomes a backend process, a query tree, an iterator plan, shared pages, visible row versions, and durable WAL. Follow that journey once; every subsystem gets a permanent place in your mental model.

Follow a requestOpen the machine
ProcessBackend owns the session
DecisionPlanner chooses; executor pulls
StateShared memory coordinates
RecoveryWAL makes change replayable

01 / Follow the work

Watch PostgreSQL become a system.

Choose a workload. Each beat lights the process, memory structure, or durable artifact that owns the next decision.

Read journey

Follow one SELECT

A read becomes a typed query tree, a costed plan, an iterator run, and finally a snapshot-approved row.

Process

A backend owns the request

The postmaster accepts the connection and a dedicated backend handles that session. Shared structures coordinate it with every other backend.

Keep this: The planner chooses. The executor pulls. The snapshot judges.

02 / Open the machine

One engine. Several systems.

Peel the X-ray by system, hover a boundary for why it exists, then pin it for evidence. PostgreSQL is not a pile of boxes, it is concurrent systems sharing one engine body.

PostgreSQL engine · X-ray

Architectural cutaway

One engine body. Peel a system, hover a boundary, or replay an incident path.

POSTGRESQLsingle engine · multiple concurrent systemsQuery pathMemory · durability · coordinationStorage · maintenance · replicaSQLBackendsParserPlannerStatsExecutorLocksRow lockMVCCBuffersWALCheckpointReplicaStoragenbtreeFSM·VMVacuumxmin

Hover a boundary for the short card · click to pin selection and open evidence below · peel a layer to see one system through the same engine.

Frame + crew

Postmaster & backends

One postmaster process supervises; each client session is typically its own backend process doing work for that connection.

ExampleIn psql: SELECT pid, usename, state, wait_event_type, wait_event, query_start FROM pg_stat_activity ORDER BY query_start NULLS LAST; Find idle-in-transaction.

Sessionspg_stat_activity
Waitswait_event
I/O pathpg_stat_io
Durabilitypg_stat_wal

Process & memory / Frame + crew

Postmaster & backends

One postmaster process supervises; each client session is typically its own backend process doing work for that connection.

Failure signature

Connection storms, stuck sessions, or “too many connections”, the crew is oversized or idle workers hold resources.

First measurement

In psql: SELECT pid, usename, state, wait_event_type, wait_event, query_start FROM pg_stat_activity ORDER BY query_start NULLS LAST; Find idle-in-transaction.

Source boundary

Process model, postmaster forks backends (see official docs: Architecture / Connections).

Learn this component next

03 / Read the instruments

A system you can diagnose.

Start with the symptom, then ask the smallest view that can locate the responsible architecture layer. No wall of charts; no tuning by folklore.

Symptom selected

Table file grows, row count flat

Versions retained; cleanup blocked or lagging, not “disk magic.”
  1. 01

    Table churn

    pg_stat_user_tablesn_dead_tup, vacuum timestamps, and update/HOT activity identify where versions accumulate.
  2. 02

    Cleanup horizon

    pg_stat_activity.backend_xminAn old backend snapshot can prevent otherwise-dead versions from becoming removable.
  3. 03

    Active vacuum

    pg_stat_progress_vacuumPhase and block progress separate active cleanup from a scheduler or horizon problem.
Decision rule

If backend_xmin is old, fix the pin first. If not, compare churn with autovacuum cadence and progress.

Check n_dead_tup, long xacts / idle-in-transaction, then vacuum lag, before VACUUM FULL.
MVCC row versionsAutovacuum internalsConcept: autovacuum thresholds
PostgreSQL 17 source trailTrace the real call boundaries
  1. 01

    Session process

    PostmasterMainpostmaster.cSupervises the server and starts the processes that serve client connections.
  2. 02

    Query pipeline

    exec_simple_querypostgres.cDrives parse, analyze/rewrite, plan, and execute for a simple-query message.
  3. 03

    Iterator execution

    ExecProcNodeFirstexecProcnode.cShows the PlanState node boundary behind the executor's pull model.
  4. 04

    Buffer path

    ReadBuffer_commonbufmgr.cFinds or reads a relation block through the shared buffer manager.
  5. 05

    WAL insertion

    XLogInsertRecordxlog.cPlaces a constructed redo record into PostgreSQL's WAL stream.
  6. 06

    Commit durability

    XLogFlushxlog.cAdvances durable WAL to the requested record position.
  7. 07

    Recovery points

    CheckpointerMaincheckpointer.cCoordinates checkpoints; this role is distinct from commit-time WAL flush.
  8. 08

    Maintenance

    AutoVacLauncherMainautovacuum.cRuns the launcher that selects databases and starts autovacuum workers.
  9. 09

    Heavyweight locks

    LockAcquireExtendedlock.cAcquires transaction-facing object locks through PostgreSQL's heavyweight lock manager.
  10. 10

    Shared-memory protection

    LWLockAcquirelwlock.cProtects short internal shared-memory critical sections, not SQL objects for a transaction.

Guardrails that keep the model honest

  • The planner produces a forecasted plan. Executor nodes perform the runtime work.
  • Shared buffers hold copies of data pages. WAL is a redo stream, not the table itself.
  • The required WAL flush makes a normal commit durable; the background writer is not the commit gate.
  • MVCC visibility, heavyweight locks, tuple row locks, and LWLocks solve different coordination problems.
  • Ordinary VACUUM makes relation space reusable. It normally does not return the file's space to the operating system.
  • A standby replays WAL. Replication extends the durability path; it does not replace the primary's query pipeline.

04 / Keep the picture

Deepen one boundary at a time.

These lessons reuse the same architecture grammar. You are not starting over; you are opening one subsystem and proving how it behaves.

  1. 01
    MVCC & access methods

    MVCC row versions

    Rows are physical lifetimes selected by snapshots.

    Open free lesson
  2. 02
    Query path

    Reading EXPLAIN plans

    Plans are estimates; execution turns them into evidence.

    Open free lesson
  3. 03
    MVCC & access methods

    B-tree index internals

    Ordered leaves find candidates; heap visibility still decides.

    Open free lesson
  4. 04
    Vacuum & freeze

    Autovacuum internals

    Cleanup reuses space and freezing preserves XID safety.

    Open free lesson
  5. 05
    Locks & waits

    Lock manager internals

    Waits make sense only after you name the locking layer.

    Open free lesson
Continue through the machine