How PostgreSQL Works: Storage Engine, MVCC, and the Query Planner Behind Every Production Database

Most engineers use PostgreSQL every day without understanding what happens between the SQL statement and the result set. That gap costs teams when queries suddenly slow down, when bloat eats disk space, or when a misconfigured checkpoint interval causes write spikes. This article walks through how Postgres actually works: storage, MVCC, WAL, VACUUM, the planner, and the buffer pool. Not a tutorial — an internals tour for engineers who want to reason about what the database is doing.

Storage Layer: Heap Files, Pages, and Tuples

Every table in Postgres is a heap file. A heap file is a collection of 8 KB pages stored on disk in $PGDATA/base/<db_oid>/<table_oid>. There is no clustering by default. Rows are appended wherever space exists.

Each 8 KB page has a fixed layout:

[ PageHeader (24 bytes) ]
[ ItemIdData array (4 bytes per slot) ]
[ Free space (middle) ]
[ Tuples (growing from end toward middle) ]
[ Special area (optional, used by index pages) ]

ItemIdData is an array of (offset, length, flags) entries called line pointers. Each tuple has its own header (HeapTupleHeaderData) that includes visibility metadata:

• t_xmin: the transaction ID that inserted this tuple
• t_xmax: the transaction ID that deleted or updated this tuple (0 if live)
• t_ctid: physical location (page, slot) of the latest version of this tuple
• t_infomask: bitmask encoding commit/abort status, null bitmap presence, etc.

An UPDATE in Postgres does not modify a tuple in place. It writes a new tuple version at a new physical location and sets t_xmax on the old version. This is the foundation of MVCC.

TOAST

Values larger than roughly 2 KB are automatically compressed or moved out-of-line to a TOAST (The Oversized-Attribute Storage Technique) table. Each table with variable-length columns gets a companion pg_toast_<oid> table. TOAST chunks are 2 KB by default. Accessing a large jsonb column or a text field over the threshold adds a TOAST fetch to the read path, which matters for performance when doing wide scans.

-- See TOAST table for a given relation
SELECT reltoastrelid::regclass
FROM pg_class
WHERE relname = 'events';

MVCC: How Postgres Handles Concurrent Transactions

Postgres implements multi-version concurrency control by keeping multiple physical versions of each row. Readers never block writers and writers never block readers because each transaction sees a consistent snapshot of the data.

Transaction IDs and Snapshots

Every transaction gets a transaction ID (XID), a monotonically increasing 32-bit integer. A snapshot is a struct that captures:

• xmin: the oldest active transaction at snapshot time (tuples with t_xmin < xmin and committed are visible)
• xmax: the next XID to be assigned (tuples with t_xmin >= xmax are invisible)
• xip[]: the list of in-progress transaction IDs at snapshot time

Tuple visibility is determined by evaluating these rules against the tuple’s t_xmin and t_xmax headers.

-- Check your current transaction's XID
SELECT pg_current_xact_id();

-- See the snapshot a transaction would use
SELECT * FROM pg_snapshot_xip(pg_current_snapshot());

Isolation Levels

Level	Dirty Read	Non-repeatable Read	Phantom Read	How Postgres Implements It
Read Committed	No	Yes	Yes	New snapshot per statement
Repeatable Read	No	No	No	Snapshot taken at first statement of transaction
Serializable	No	No	No	SSI with predicate locks (tracks read-write conflicts)

Postgres does not implement Read Uncommitted differently from Read Committed. The default isolation level is Read Committed, which is often not serializable enough for financial or inventory workloads. Serializable Snapshot Isolation (SSI) in Postgres is genuine — it detects and aborts transactions that would produce non-serializable schedules without requiring row-level locks.

The Visibility Map

The visibility map is a fork of each heap file with one bit per page. If the bit is set, every tuple on that page is known to be visible to all current and future transactions. The vacuum process sets these bits. Index-only scans use the visibility map: if the page is all-visible, Postgres can return index data without fetching the heap tuple, which is a significant I/O reduction for read-heavy workloads.

Write-Ahead Log: Crash Recovery and Replication

Every change in Postgres is first written to the WAL before the corresponding heap page is modified in shared memory. This is the “write-ahead” guarantee: if the database crashes, the WAL is replayed to reconstruct any changes that did not make it to the heap files on disk.

WAL Record Structure

A WAL record contains:

• The LSN (Log Sequence Number): a byte offset into the WAL stream, used for ordering and progress tracking
• The resource manager ID: which subsystem owns this record (heap, index, transaction, etc.)
• The full-page image (FPI) for the first modification to a page since the last checkpoint (protects against partial page writes)
• The delta: the actual change (tuple header mutation, index entry, etc.)

-- Examine recent WAL activity
SELECT * FROM pg_walinspect(pg_current_wal_lsn() - '1MB'::pg_lsn, pg_current_wal_lsn());

-- Check replication lag
SELECT client_addr, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       sent_lsn - replay_lsn AS lag_bytes
FROM pg_stat_replication;

Checkpoints

A checkpoint forces all dirty shared buffer pages to disk and writes a checkpoint record to the WAL. On crash recovery, Postgres replays WAL only from the most recent checkpoint forward. The key parameters:

• checkpoint_timeout: maximum time between automatic checkpoints (default 5 minutes)
• max_wal_size: triggers a checkpoint if WAL grows beyond this size (default 1 GB)
• checkpoint_completion_target: fraction of the checkpoint interval over which dirty writes are spread (default 0.9, meaning 90% of the interval — smooths I/O)

A too-aggressive checkpoint interval (short checkpoint_timeout or small max_wal_size) causes frequent I/O spikes. Too relaxed and crash recovery time grows. The checkpoint_completion_target value of 0.9 is almost always the right call in production.

VACUUM: Dead Tuple Reclamation and XID Wraparound

Because MVCC leaves old tuple versions in place, deleted and updated rows accumulate as dead tuples. VACUUM reclaims that space.

What VACUUM Does

1. Scans heap pages for dead tuples (where t_xmax is a committed transaction and no snapshot can see the old version)
2. Marks those line pointer slots as reusable
3. Updates the free space map so INSERT knows where to put new rows
4. Sets the all-visible bit in the visibility map for pages with no dead tuples
5. Advances pg_class.relfrozenxid, the oldest XID that the table still needs to track

Autovacuum

Autovacuum runs in the background and triggers based on dead tuple thresholds:

autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2

A table triggers autovacuum when: dead_tuples > threshold + (scale_factor * live_tuples)

For a table with 10 million rows, that is 2,000,050 dead tuples before autovacuum fires. That is often too late for high-churn tables. For write-heavy tables, reduce the scale factor:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 1000
);

XID Wraparound

The XID is a 32-bit integer. After roughly 2.1 billion transactions, it wraps. Postgres uses a modular arithmetic model where every transaction is “in the future” or “in the past” relative to the current XID, and tuples with XIDs that are “too old” are treated as frozen (visible to all). If a table’s relfrozenxid falls more than autovacuum_freeze_max_age (default 200 million) transactions behind the current XID, autovacuum runs aggressively on it. If wraparound is imminent (within a safety margin), Postgres will refuse all writes until VACUUM runs. This is a production emergency. Monitor it:

SELECT relname,
       age(relfrozenxid) AS xid_age,
       2000000000 - age(relfrozenxid) AS transactions_until_wraparound
FROM pg_class
WHERE relkind = 'r'
ORDER BY xid_age DESC
LIMIT 10;

Any table approaching 1.5 billion transactions of age needs immediate attention.

Query Planner and Optimizer

The Postgres query planner is a cost-based optimizer. It enumerates candidate execution plans, estimates the cost of each, and picks the cheapest one.

Statistics

The planner depends on column statistics collected by ANALYZE. Statistics live in pg_statistic and are summarized in pg_stats. They include:

• n_distinct: estimated number of distinct values
• most_common_vals / most_common_freqs: histograms for skewed distributions
• correlation: physical ordering correlation between column values and heap position (1.0 means perfectly sorted, relevant for index vs. sequential scan decisions)
• null_frac: fraction of nulls

Statistics are sampled, not exact. The default sample target is 100 values per column. For columns with high cardinality or unusual distributions, increase it:

ALTER TABLE events ALTER COLUMN user_id SET STATISTICS 500;
ANALYZE events;

Cost Model

The planner assigns costs in units where a sequential page read costs 1.0. Important parameters:

• seq_page_cost = 1.0: baseline
• random_page_cost = 4.0 (default, tune down to 1.1 for SSDs or cloud storage)
• cpu_tuple_cost = 0.01
• cpu_index_tuple_cost = 0.005
• effective_cache_size: the planner’s estimate of how much OS page cache is available (does not allocate memory, purely a hint for cost estimation)

Setting random_page_cost = 1.1 on SSD-backed storage is one of the most impactful single-line changes you can make. It causes the planner to favor index scans over sequential scans for mid-cardinality queries where it would otherwise pick a seq scan.

Scan Strategies

Strategy	When Used	I/O Pattern
Sequential scan	Low selectivity, small tables, no useful index	Linear heap read
Index scan	High selectivity, small result set	Random heap + index page reads
Index-only scan	Result fits in index, page is all-visible	Index pages only
Bitmap heap scan	Medium selectivity	Index → bitmap → sorted heap fetch

The bitmap heap scan is worth understanding. It builds an in-memory bitmap of matching heap page numbers using the index, sorts them, then fetches heap pages in order. This amortizes random I/O into a semi-sequential access pattern. It shows up in EXPLAIN as Bitmap Index Scan feeding a Bitmap Heap Scan.

Join Strategies

Strategy	Complexity	Best When
Nested loop	O(outer * inner)	Small inner side, indexed join column
Hash join	O(outer + inner)	Larger tables, no index on join column
Merge join	O(N log N) pre-sort	Both sides already sorted, large equijoins

-- Force a plan for debugging (never leave in production)
SET enable_hashjoin = off;
SET enable_nestloop = off;
EXPLAIN ANALYZE SELECT ...;
RESET enable_hashjoin;
RESET enable_nestloop;

Plan Caching with Prepared Statements

Prepared statements are cached after five executions in PostgreSQL. On the sixth execution, the planner generates a generic plan without parameter values and compares its cost to the average of the five specific plans. If the generic plan is cheaper (or within a threshold), it uses that going forward.

This matters for parameterized queries with skewed data distributions. A generic plan cannot use column statistics for parameter values, so it may pick a bad plan for a parameter value that represents a large fraction of the table.

-- See cached plan types (pg_prepared_statements is per-session)
SELECT name, generic_plans, custom_plans
FROM pg_prepared_statements;

-- Force custom plans always (useful when distribution is highly skewed)
-- Set in postgresql.conf or per-session:
SET plan_cache_mode = force_custom_plan;

Buffer Pool: shared_buffers and Page Eviction

shared_buffers is the in-memory cache for heap and index pages. Every page read goes through this cache. A common starting point is 25% of system RAM, but on dedicated database servers, 40% is reasonable. Above 8 GB or so, the returns diminish because the OS page cache also buffers reads.

Clock-Sweep Eviction

Postgres uses a clock-sweep algorithm (an approximation of LRU) to evict pages when the buffer pool is full. Each buffer has a usage count (0-5). When a buffer is accessed, its count increments. The clock hand sweeps around decrementing counts and evicting buffers whose count reaches zero. Heavily accessed pages accumulate high counts and resist eviction.

bgwriter and Checkpointer

Two background processes write dirty pages:

• bgwriter: proactively writes dirty pages to reduce the work the backend processes (your query sessions) must do at eviction time. Tuned with bgwriter_lru_maxpages and bgwriter_delay.
• checkpointer: handles checkpoint writes, spreading dirty page writes over checkpoint_completion_target * checkpoint_timeout.

If your backend processes are doing significant bgwriter work themselves (check pg_stat_bgwriter.buffers_backend), it means bgwriter is not keeping up. Increase bgwriter_lru_maxpages.

Connection Handling: The Postmaster Fork Model

Postgres uses a process-per-connection model. The postmaster is the supervisor process. When a client connects, postmaster forks a new backend process. Each process gets its own memory (query working memory determined by work_mem) and holds open file descriptors to the data files.

This model has well-known overhead. Each connection consumes roughly 5-10 MB of memory and a process slot. At 500 connections, that is 2.5-5 GB just in process overhead, before any query data. At 1000 connections, the context-switching and shared-memory contention become measurable.

Why PgBouncer is Not Optional at Scale

PgBouncer is a connection pooler that maintains a small pool of actual Postgres connections and multiplexes many client connections onto them. In transaction-mode pooling, a Postgres connection is leased to a client only for the duration of a transaction, then returned to the pool.

App servers (500 connections) -> PgBouncer (10-20 Postgres connections) -> Postgres

Without a pooler, a Node.js service with 50 workers each holding 10 idle connections will consume 500 Postgres backends. With PgBouncer, those 500 virtual connections share 20 actual backends. The difference in memory, lock contention, and scheduler overhead is substantial.

max_connections in Postgres should be set conservatively (100-200 for most deployments). Let PgBouncer or pgpool handle the fan-in.

Tradeoffs: PostgreSQL vs MySQL vs CockroachDB vs AlloyDB

Dimension	PostgreSQL	MySQL	CockroachDB	AlloyDB (Google)
MVCC model	Heap versioning, dead tuple cleanup via VACUUM	Undo log (InnoDB), no VACUUM needed	MVCC with distributed timestamp oracle	PostgreSQL-compatible, columnar extension
Replication	Streaming WAL (physical), logical replication	Binlog-based (row/statement/mixed)	Raft-based distributed consensus	Managed HA with synchronous replication
Horizontal write scaling	Not native, requires partitioning + Citus	Not native	Native distributed writes across nodes	Not native (read replicas only)
JSON support	Native jsonb with GIN indexing	JSON type, limited indexing	jsonb (PostgreSQL-compatible)	Full PostgreSQL jsonb support
Extension ecosystem	Rich (PostGIS, pgvector, TimescaleDB, Citus)	Limited	Limited (CockroachDB-specific)	PostgreSQL-compatible, managed environment limits
Managed offerings	RDS, Aurora, Cloud SQL, Supabase, Neon	RDS, Aurora, Cloud SQL	CockroachDB Serverless/Dedicated	AlloyDB Omni, AlloyDB Serverless
Cross-row transactions	Full ACID, SSI	Full ACID, no SSI equivalent	Full ACID, serializable isolation	Full ACID (PostgreSQL-compatible)
Operational complexity	High (VACUUM, bloat, XID wraparound)	Lower (InnoDB handles row versions automatically)	Higher (distributed consensus, clock skew)	Low (fully managed)

Production Considerations

Monitor pg_stat_statements. It is the single most useful view in a production Postgres instance. It tracks cumulative execution counts, total and mean execution time, rows returned, and buffer hits vs. disk reads for every distinct query fingerprint.

SELECT query,
       calls,
       mean_exec_time,
       total_exec_time,
       rows / calls AS avg_rows,
       shared_blks_hit::float / nullif(shared_blks_hit + shared_blks_read, 0) AS cache_hit_ratio
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Track index bloat. Index pages accumulate dead entries that are not reclaimed as aggressively as heap pages. Index bloat shows up as growing index sizes, slower scans, and higher cache miss rates. REINDEX CONCURRENTLY rebuilds an index without blocking reads or writes.

-- Rough index bloat estimate
SELECT schemaname, tablename, indexname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

Respect max_connections. Use PgBouncer in transaction mode for web application workloads. Set max_connections to what Postgres can actually handle (usually under 200) and let the pooler absorb the fan-in.

Tune work_mem carefully. work_mem is per sort or hash operation per query, not per connection. A query with three hash joins can use 3x work_mem. At 200 connections each running a multi-join query, the memory pressure is 200 * 3 * work_mem. Start at 4-8 MB and increase for specific sessions or queries that need it, not globally.

Checkpoint tuning. If you see periodic I/O spikes every 5 minutes, increase max_wal_size to 4-8 GB and keep checkpoint_completion_target = 0.9. The WAL growth is acceptable; the smoothed I/O pattern is worth it. Monitor pg_stat_bgwriter.checkpoints_timed vs. checkpoints_req: if most checkpoints are checkpoints_req, your max_wal_size is too small.

Set random_page_cost = 1.1 on SSDs. The default of 4.0 was tuned for spinning disks. On NVMe or cloud block storage, random reads are nearly as fast as sequential reads. Leaving it at 4.0 causes the planner to avoid index scans it should be using.

Understanding the storage engine is not an academic exercise. Dead tuple accumulation, bloat, checkpoint storms, and plan cache mismatches are real production failure modes. The engineers who can diagnose them are the ones who understand what is happening between the query string and the disk.