How PostgreSQL Works Under the Hood

PostgreSQL has been the default answer to "what database should I use?" since before most of us were writing production code. That longevity isn't accidental — Postgres makes specific, principled design choices that favor correctness over raw speed. Once you understand those choices, behavior that seems mysterious (why does UPDATE slow down after millions of rows? why does an idle table bloat?) becomes obvious.

Every constant, default and process name below is against PostgreSQL 18. That matters more than it sounds — several things everyone "knows" about Postgres internals stopped being true a few majors ago, and I've flagged those where they come up.

The Mental Model

Here's how a query moves through Postgres end-to-end:

Every query takes this path. The interesting behavior — the stuff that actually matters for performance and reliability — lives in the storage layer and the concurrency model.

The Process Model

Postgres uses a process-per-connection model, not threads. When a client connects, the postmaster forks a new OS process. That process has its own memory space and handles exactly one client session.

Unfashionable next to epoll-based servers or goroutines, but a crashing backend can't corrupt shared state for anyone else. That isolation is the whole trade. It's also why you put PgBouncer in front once you want thousands of connections: each one is a process, with a process's memory and scheduling cost.

Alongside backend processes, the postmaster runs several background workers permanently:

• Checkpointer — flushes dirty pages from shared_buffers to disk at intervals
• WAL writer — flushes WAL buffer to disk, reducing per-commit I/O pressure
• Autovacuum launcher + workers — triggers vacuum on tables that accumulate dead tuples
• Background writer — pre-emptively writes dirty pages before checkpoints need to

If you learned this list a few years ago it had a fifth entry that no longer exists. There is no statistics collector process. PostgreSQL 15's release notes: "Previously this data was sent to a statistics collector process via UDP packets, and could only be read by sessions after transferring it via the file system. There is no longer a separate statistics collector process." Backends now accumulate stats locally and flush to shared memory on going idle, no more than once a second — which is why pg_stat_* lags real activity slightly while pg_stat_activity is always current.

Storage: Heap Files and Pages

On disk, every table is a heap file — a flat sequence of 8KB pages. No fancy tree structure for the main table data, just pages laid out sequentially inside $PGDATA/base/<dbOid>/.

One naming detail that bites anyone writing tooling: the file is not named after the table's OID but after its filenode, pg_class.relfilenode. They start out equal and then diverge — VACUUM FULL, TRUNCATE, REINDEX and CLUSTER rewrite the relation into a new filenode while the OID stays put. Alongside the main fork you get a _fsm file (free space map) and a _vm file (visibility map); both matter later.

Each 8KB page has five parts:

+----------------------+
| PageHeaderData (24B) |  pd_lsn, checksum, flags, free space offsets
+----------------------+
| ItemIdData           |  4 bytes each — (offset, length) per item
+----------------------+
|     (free space)     |
+----------------------+
| Items                |  the tuples themselves
+----------------------+
| Special space        |  index-AM specific; empty in ordinary tables
+----------------------+

Both numbers are exact: the docs specify PageHeaderData as "24 bytes long" and item identifiers as "each requiring four bytes." Item identifiers are allocated from the start of the free space, tuples from the end, growing toward each other. When they meet, the page is full.

Every tuple carries a fixed header — 23 bytes on most machines — then an optional null bitmap, then the user columns at the offset in t_hoff. The fields worth reasoning about:

• t_xmin — XID of the transaction that inserted this tuple
• t_xmax — XID of the transaction that deleted or updated it (0 = never deleted)
• t_ctid — an ItemPointerData; the docs define it as the "current TID of this or newer row version." Not necessarily the newest: on a chain of updates you follow it hop by hop.
• t_infomask / t_infomask2 — flag bits (transaction status, whether the tuple has nulls at all, attribute count). The null bitmap itself is not in here; it sits after the header.

These are the foundation of MVCC. You can inspect the interesting ones as system columns:

SELECT xmin, xmax, ctid, * FROM orders WHERE id = 42;

MVCC: Readers Don't Block Writers

Postgres still takes row-level write locks — two transactions updating the same row serialise, same as anywhere. What it doesn't do is make readers wait, because it keeps multiple versions of every row in the heap simultaneously rather than in a separate undo log.

When you run UPDATE orders SET status = 'shipped' WHERE id = 42:

1. The current tuple gets xmax stamped with your transaction ID
2. A new tuple version is written to the heap with xmin = your XID
3. The old tuple's t_ctid is updated to point to the new version

When a transaction reads a row, Postgres computes a snapshot — a record of which transaction IDs were active or committed at that point. A tuple is visible if:

• xmin is a committed XID visible in the snapshot
• xmax is zero, or belongs to a transaction that aborted, or is still in progress / invisible to the snapshot

The result: readers never block writers and writers never block readers. A long-running SELECT sees the database as it was when its snapshot was taken, regardless of concurrent updates.

The cost: dead tuple accumulation. Every UPDATE leaves an old version behind, every DELETE marks a tuple dead without removing it, and both sit in the heap until VACUUM reclaims them.

WAL: How Postgres Survives a Crash

Every mutation goes through the Write-Ahead Log before touching heap pages. The guarantee: a WAL record describing a change must be durably on disk before the change is considered committed.

On COMMIT, Postgres flushes WAL to disk before reporting success. That's why commits are I/O-bound by default — you're waiting for the disk to confirm a write. The actual heap data pages might still be sitting in shared_buffers, marked dirty. That's fine, because the WAL has everything needed to reconstruct them.

That "by default" is doing real work. The knob is synchronous_commit, default on, and the documented local behaviour of every non-off mode is "to wait for local flush of WAL to disk." Set it off and commits stop waiting: you keep crash consistency but you can lose the last few transactions, bounded by three times wal_writer_delay. Biggest write-throughput lever in Postgres, and the one most likely to get you fired.

WAL positions are tracked via LSN (Log Sequence Number) — internally, per the docs, "a 64-bit integer, representing a byte position in the write-ahead log stream," surfaced to SQL as the pg_lsn type. Every heap page stores the LSN of the last WAL record that modified it (pd_lsn). On startup after a crash, Postgres finds the latest checkpoint record and replays WAL forward from there.

There is no undo phase, but not for the reason usually given. Uncommitted changes do have WAL records, and recovery replays them onto the heap like anything else. MVCC is what makes undo unnecessary: those replayed tuples carry an xmin whose transaction never recorded a commit, so no later snapshot can see them, and VACUUM sweeps them up like any other dead tuple. Recovery is redo-only because visibility decides what counts, not the log.

WAL Segment: 000000010000000000000001
  ├── record: INSERT into orders (xid=5001, lsn=0/1234AB)
  ├── record: UPDATE pg_class (xid=5002, lsn=0/1234CC)
  └── record: COMMIT (xid=5001, lsn=0/1234F0)  ← fsync() happens here

Vacuum: The Mandatory Janitor

VACUUM is what prevents Postgres from slowly dying from dead tuple bloat. It's not optional — it's a core part of the storage model.

When autovacuum runs on a table:

1. It scans the heap for dead tuples — those where xmax is a committed XID no longer visible to any open transaction
2. It marks those slots free in the Free Space Map (FSM) so new tuples can reuse the space
3. It updates the Visibility Map (VM) — a per-page bitmap tracking pages that contain only tuples visible to all transactions

Note what is not on that list: VACUUM does not run ANALYZE. The docs describe statistics as gathered "by the ANALYZE command, which can be invoked by itself or as an optional step in VACUUM" — optional, i.e. VACUUM ANALYZE. Plain VACUUM skips it. Autovacuum does issue ANALYZE, but as a separate decision on separate thresholds, "strictly as a function of the number of rows inserted or updated." So a table can be vacuumed diligently and still be planned against stale statistics.

The Visibility Map earns its keep twice. Vacuum skips all-visible pages on its next run, and — more importantly — it's what makes index-only scans possible. Postgres indexes carry no visibility information, so a normal index scan must fetch the heap tuple just to check whether this transaction should see it. An index-only scan checks the VM first and skips that fetch when the page is known all-visible.

There's a harder problem VACUUM also handles: transaction ID wraparound. XIDs are 32 bits, compared modulo 2^32, so for any given XID there are "two billion XIDs that are older and two billion that are newer." A row version older than two billion transactions starts reading as being from the future — invisible. Hence the docs' hard requirement: vacuum every table in every database at least once every two billion transactions. VACUUM discharges it by marking old tuples frozen — certain to be visible to all current and future transactions, no XID comparison needed.

The Query Planner

Before execution, your SQL goes through a 4-stage pipeline:

1. Parser — converts SQL text to an AST
2. Analyzer — resolves table/column names, validates types
3. Rewriter — expands view definitions and applies rule rewrites
4. Planner — enumerates possible execution plans, estimates cost, picks the cheapest

The planner is cost-based. It uses statistics in pg_statistic (written by ANALYZE) to estimate row counts and selectivity, then computes the total cost of each candidate plan. Costs are arbitrary units anchored to one thing, and getting the anchor right matters because a lot of tuning advice has it backwards: the unit is a sequential page fetch. seq_page_cost defaults to 1.0 and, per the docs, "is conventionally set to 1.0 and the other cost variables are set with reference to that." A random fetch is random_page_cost, default 4.0. Per-row CPU work is cpu_tuple_cost, default 0.01 — so out of the box the planner assumes one random page costs about what processing 400 rows costs.

For joins, it evaluates three algorithms:

• Nested Loop — iterate outer rows, probe inner for each. Good when inner is small or indexed.
• Hash Join — build a hash table from the smaller side, probe with the larger. Great for equi-joins on unsorted data.
• Merge Join — requires both sides sorted (or an index providing the sort order). Excellent when data arrives pre-sorted.

-- See exactly what the planner decided and what actually happened
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, u.email
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at > '2025-01-01';

The BUFFERS option shows cache hits vs. disk reads; high shared read counts mean you're missing the buffer pool. A large Rows Removed by Filter means the node read far more rows than it returned — sometimes a missing index, sometimes bad estimates, so compare the plan's estimated rows against actual before you reach for ANALYZE.

Indexes: More Than B-Trees

Postgres ships with six built-in index types (plus bloom, which is a contrib extension rather than core), each suited to different access patterns:

GIN surprises people most. Index a jsonb column with the default jsonb_ops and writes get noticeably slower, because — in the docs' words — that operator class "creates independent index items for each key and value in the data." Every key. Every value. The payoff is fast @> containment. If containment is all you need, jsonb_path_ops indexes hashed paths instead and is "usually much smaller," giving up the key-exists operators (?, ?|, ?&) in exchange. gin_pending_list_limit and a partial index are the other two levers.

BRIN is the underused one. It stores min/max per block range rather than an entry per row, so its size scales with block ranges, not rows — which is why BRIN on an append-only created_at is tiny next to the equivalent B-tree, and why it only works when physical order tracks the indexed value. Insert out of order and the ranges overlap into uselessness.

What This Means in Practice

Table growing despite deletes: Dead tuples. Run SELECT n_dead_tup, last_autovacuum FROM pg_stat_user_tables WHERE relname = 'your_table'. If autovacuum isn't keeping up, tune autovacuum_vacuum_scale_factor down or increase autovacuum_max_workers.

Slow UPDATEs at scale: Every UPDATE writes a new tuple at a new TID — so it needs a new entry in every index on the table, not just the indexes covering the column you changed. That's the cost people underestimate. HOT (Heap-Only Tuple) is the escape hatch, and the docs give exactly two conditions: the update touches no column referenced by any of the table's indexes (BRIN excepted, as summarizing indexes don't participate), and there's free space on the page holding the old row. Meet both and no index entries are written at all. Which is why lowering fillfactor on a hot table is real tuning rather than folklore — heap fillfactor defaults to 100, and HOT needs somewhere to put the new version.

Planner choosing sequential scan over index: Check random_page_cost. The docs are candid about where 4.0 comes from — random access "is normally much more expensive than four times sequential access. However, a lower default is used (4.0) because the majority of random accesses to storage, such as indexed reads, are assumed to be in cache." On fast local SSDs the ratio is lower still, and reducing it relative to seq_page_cost is the documented way to make index scans look cheaper. Values near 1.x are common practice on SSD-backed instances — a starting point to measure from, not a magic number.

Long-running transactions causing bloat: Autovacuum can't reclaim dead tuples still visible to any open snapshot. A transaction that ran a query and then went idle pins the xmin horizon where it was, and bloat accumulates across every table written to since. (A bare BEGIN is harmless — the snapshot is taken at the first query, not at BEGIN. It's idle in transaction after work that hurts.)

The core trade-off: Postgres spends storage to buy concurrency. Dead tuples, WAL overhead and vacuum all exist because MVCC chose not to make readers wait. Understand that and most tuning decisions stop being cargo cult.