How PostgreSQL Works Internally: Storage, MVCC, Query Planning, and the WAL

PostgreSQL has been in continuous development since 1986. It underpins systems that handle trillions of dollars in transactions, petabytes of event data, and some of the most demanding read workloads in production. Yet most engineers interact with it entirely through SQL, treating the internals as a black box.

Understanding what PostgreSQL does below the SQL layer changes how you design schemas, write queries, and size infrastructure. This article covers the four systems that matter most: the heap-based storage engine, MVCC with its tuple versioning model, the query planner’s cost-based optimizer, and the write-ahead log that makes durability possible without sacrificing throughput.

The Heap Storage Engine

PostgreSQL stores table data in files called heap files. Each table lives in its own file (or set of files if it grows beyond 1 GB) under $PGDATA/base/<database_oid>/. The file is divided into 8 KB pages. Everything about how PostgreSQL reads, writes, and caches data flows from this fixed-size page structure.

Page Layout

Each 8 KB page has a fixed layout:

+------------------+  <- offset 0
| PageHeader (24B) |
+------------------+
| ItemId array     |  <- grows downward
|  (4B per slot)   |
+------------------+
|                  |
|  Free space      |
|                  |
+------------------+
| Tuples           |  <- grows upward from end
+------------------+
| Special space    |  <- 0 bytes for heap pages
+------------------+  <- offset 8192

The PageHeader stores the page’s LSN (Log Sequence Number from the WAL), free space information, and flags. The ItemId array is a roster of (offset, length) pairs pointing to each tuple stored in the page. Tuples are packed from the end of the page toward the middle.

This layout means a tuple’s physical address is a (page_number, slot_number) pair called a ctid. You can see it directly:

SELECT ctid, id, username
FROM users
LIMIT 5;

-- ctid    | id | username
-- (0,1)   | 1  | alice
-- (0,2)   | 2  | bob
-- (1,1)   | 3  | charlie

(0,1) means page 0, slot 1. This address is physical and can change when a row is updated or a table is vacuumed and compacted.

TOAST: Handling Large Values

PostgreSQL has a hard limit: a single tuple must fit on a single page (approximately 8 KB minus overhead). Values larger than roughly 2 KB trigger TOAST: The Oversized-Attribute Storage Technique.

When PostgreSQL writes a large value, it first tries compression (using pglz or lz4 depending on server version). If the compressed result still exceeds the threshold, the value is chunked into 2 KB pieces and written to a per-table TOAST table in the same schema. The main row stores a pointer (a varattrib_4b indirect reference) to the TOAST table.

The practical implication: fetching a row with a large text column that has been TOASTed is more expensive than it looks. The planner cannot always account for TOAST decompression cost, and sequential scans over tables with many large columns are slower than the row count suggests. For large JSON documents or binary blobs, jsonb compression behavior and TOAST storage policy (set with ALTER TABLE ... ALTER COLUMN ... SET STORAGE) are worth examining in production.

MVCC: Multi-Version Concurrency Control

PostgreSQL uses MVCC to give each transaction a consistent snapshot of the database without using read locks. The design is different from MySQL’s InnoDB, which uses an undo log to reconstruct older versions. PostgreSQL writes all versions directly into the heap. Old versions accumulate in place and are cleaned up later by vacuum.

Tuple Headers and Visibility

Every heap tuple has a header that includes:

• t_xmin: the transaction ID (XID) that inserted this tuple
• t_xmax: the transaction ID that deleted or updated this tuple (0 if the row is live)
• t_ctid: the ctid of the newest version of this tuple (self-referential if current)
• t_infomask: flags that encode commit/abort status and lock information

When a transaction reads a row, PostgreSQL evaluates visibility rules against its snapshot. The snapshot captures the XID of the current transaction and the set of in-progress transactions at the time BEGIN was issued. A tuple is visible if t_xmin committed before the snapshot was taken and t_xmax is either zero, aborted, or committed after the snapshot.

This is the “snapshot isolation” guarantee. Two concurrent transactions see independent, consistent views of the data, and neither blocks the other for reads.

What Happens on UPDATE

An UPDATE in PostgreSQL is not an in-place modification. It is a delete-plus-insert:

1. The existing tuple gets t_xmax set to the updating transaction’s XID.
2. A new tuple is written with the modified column values and a fresh t_xmin.
3. The old tuple’s t_ctid is updated to point to the new version.
4. When the update commits, both tuples exist in the heap simultaneously.

This means UPDATE-heavy workloads generate dead tuples continuously. A table that receives 10,000 updates per second is also accumulating 10,000 dead tuples per second. Without vacuum, the heap grows without bound, query performance degrades as pages fill with dead rows, and eventually you hit transaction ID wraparound.

Vacuum and VACUUM FREEZE

The autovacuum daemon runs background workers that scan tables and reclaim space from dead tuples. It marks the dead tuple’s ItemId as unused, making the space available for new inserts. It does not immediately return the space to the OS; for that you need VACUUM FULL, which rewrites the entire table (and takes an exclusive lock).

Beyond space reclamation, vacuum has a second critical job: transaction ID wraparound prevention. PostgreSQL uses 32-bit XIDs. After approximately 2 billion transactions, XIDs wrap around. PostgreSQL prevents this by running VACUUM FREEZE, which replaces old t_xmin values with a special frozen XID that is always considered visible. Failure to run freeze on a busy database will eventually trigger a hard shutdown with the message “database is not accepting commands to avoid wraparound data loss.”

Monitoring autovacuum lag is production-critical:

SELECT relname,
       n_dead_tup,
       n_live_tup,
       last_autovacuum,
       last_autoanalyze,
       age(relfrozenxid) AS xid_age
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

xid_age above 150 million warrants investigation. Above 200 million, increase autovacuum_freeze_max_age urgency or run manual VACUUM FREEZE.

The Query Planner

PostgreSQL’s query planner is a cost-based optimizer. It generates a set of candidate execution plans for each query, estimates the cost of each plan in abstract units (loosely, sequential disk I/O units), and picks the plan with the lowest estimated total cost. The accuracy of cost estimation depends entirely on the quality of the statistics PostgreSQL maintains about each table and column.

Statistics and the pg_statistic Catalog

ANALYZE collects statistics about column data: null fraction, distinct values, most common values (MCV), and a histogram of value distribution. These are stored in pg_statistic and exposed through pg_stats.

SELECT tablename, attname, n_distinct, most_common_vals, correlation
FROM pg_stats
WHERE tablename = 'orders'
  AND attname IN ('status', 'customer_id', 'created_at');

correlation is the coefficient between physical sort order and logical sort order. A value near 1.0 means rows are physically sorted by this column, making range scans much cheaper because they read sequential pages. A value near 0 means rows are scattered across pages, making each index lookup potentially fetch a separate page.

Sequential Scans vs. Index Scans

The planner chooses between scan types based on estimated cost:

• Sequential scan: reads every page in the table, one by one, in physical order. Cost scales linearly with table size. PostgreSQL uses prefetching and benefits from OS read-ahead. For low-selectivity queries (returning more than roughly 5-20% of rows), sequential scans are often faster than index scans.

• Index scan: traverses the B-tree index to find matching ctid values, then fetches each heap page. For high-selectivity queries, this avoids reading most of the table. For low-selectivity queries, the random I/O from fetching scattered heap pages becomes more expensive than a sequential scan.

• Index-only scan: if all columns the query needs are present in the index, PostgreSQL can return results directly from the index without touching the heap at all. The visibility map (a per-table file tracking which pages have all-visible tuples) is consulted to verify visibility without a heap fetch. This is why covering indexes matter.

• Bitmap scan: a hybrid strategy. PostgreSQL scans the index to collect all matching ctid values into a bitmap in memory, then sorts them by physical page order, then fetches heap pages sequentially. Bitmap scans are effective for moderate selectivity, where an index scan would involve too much random I/O but a sequential scan would read too many pages.

You can inspect what the planner chose and why:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders
WHERE customer_id = 42
  AND status = 'pending'
  AND created_at > now() - interval '30 days';

The BUFFERS option shows shared buffer hits versus disk reads. A query that reports “Buffers: shared hit=1200 read=0” is fully served from shared memory. One that reports “read=800” is doing disk I/O on every execution.

Join Strategies

For multi-table queries, the planner chooses from three join algorithms:

Nested loop join: for each row in the outer relation, scan the inner relation for matching rows. Cost is O(outer * inner) without an index, or O(outer * log(inner)) with an index on the join column. Best for small outer sets with selective inner lookups.

Hash join: build an in-memory hash table from the smaller relation, then probe it with each row from the larger relation. Requires enough work_mem to hold the hash table. Best for large joins with good cardinality estimates.

Merge join: sort both relations on the join key, then merge them in a single pass. Cost is dominated by the sort. If both relations already arrive sorted (from an index or earlier sort), merge join can be very efficient with minimal memory.

The planner’s join strategy decision depends on estimated cardinalities. When row count estimates are wrong by orders of magnitude (common with correlated predicates or complex expressions), the planner picks the wrong strategy. EXPLAIN ANALYZE shows the estimated vs. actual rows for each node:

Hash Join  (cost=1234.00..5678.00 rows=500 width=64)
           (actual time=23.4..89.1 rows=42000 loops=1)

Here the planner expected 500 rows but got 42,000. This kind of mismatch means a hash join chosen for a “small” table is actually processing a large one, potentially spilling to disk. Collecting statistics more aggressively (ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500) or creating extended statistics for correlated columns (CREATE STATISTICS ... ON status, customer_id FROM orders) often fixes bad estimates.

The Write-Ahead Log

PostgreSQL guarantees durability through the WAL. Before any change is written to a heap or index page in shared buffers, a WAL record describing that change is written to the WAL buffer and flushed to disk. If PostgreSQL crashes mid-operation, the WAL records that were flushed but whose corresponding heap changes had not yet been written to disk are replayed on startup. This is called redo recovery.

The WAL is a sequential append-only log stored in $PGDATA/pg_wal/ as 16 MB segment files by default. Sequential writes to a spinning disk are an order of magnitude faster than random writes to heap pages. The WAL converts the random-write workload of modifying arbitrary heap pages into a sequential-write workload. Committed transactions only wait for the WAL flush, not for heap pages to be written.

WAL and Replication

The same WAL that enables crash recovery also drives streaming replication. Standby servers connect to the primary and receive WAL records as they are written. They apply those records to their own copy of the heap, maintaining a near-real-time replica.

The wal_level setting controls how much information is included in WAL records:

• minimal: enough for crash recovery only
• replica: enough for streaming replication and base backups
• logical: adds information needed for logical replication and change data capture

Logical replication decodes WAL records at the row level, making the WAL a viable stream for CDC pipelines. Tools like pgoutput (PostgreSQL’s built-in logical decoding plugin) and Debezium consume this stream to replicate changes to Kafka, Elasticsearch, or other systems.

synchronous_commit and the Durability Tradeoff

By default, synchronous_commit = on, which means a COMMIT waits for the WAL record to be flushed to disk before returning to the client. This guarantees no data loss for committed transactions.

Setting synchronous_commit = off allows commits to return immediately, with the WAL flush happening asynchronously. The tradeoff: in a crash scenario, up to wal_writer_delay (default 200ms) of recently committed transactions may be lost. The transactions won’t be partially applied (no corruption), but they may silently disappear. This mode is appropriate for workloads where you can tolerate losing the last few hundred milliseconds of writes, such as session data or analytics events, in exchange for significantly higher write throughput.

Tradeoffs: PostgreSQL vs. MySQL vs. CockroachDB vs. SQLite

Dimension	PostgreSQL	MySQL (InnoDB)	CockroachDB	SQLite
Storage model	Heap files with MVCC via tuple versioning	B+Tree clustered by PK, MVCC via undo log	Distributed RocksDB key-value, Raft consensus	Single-file B-tree
MVCC implementation	Old versions in heap, vacuumed later	Old versions reconstructed from undo log	MVCC over distributed Raft log	Write lock per table, no MVCC
Write amplification	Medium: WAL + deferred heap writes	Medium: WAL (redo log) + undo log + clustered B+tree updates	High: Raft log + RocksDB LSM compaction	Low: single file, simple B-tree
Vacuum / maintenance	Required; autovacuum manages it; can lag	No dead tuple accumulation; undo purge thread	Automatic MVCC GC via distributed consensus	Not needed; overwrite in place
Horizontal scaling	Vertical + read replicas natively; sharding requires Citus or app-level logic	Similar; Aurora adds auto-scaling replicas	Native horizontal sharding; multi-region built in	Single-writer; not designed for distribution
SQL completeness	Best-in-class: window functions, CTEs, lateral joins, JSON, full-text search	Good; gaps in some window function edge cases	Good; PostgreSQL-compatible dialect	Minimal; limited DDL, no window functions in older versions
Ecosystem maturity	Very high; 38 years of development	Very high; 30 years; massive web ecosystem	Moderate; actively developed but younger	Very high; embedded use cases; limited operational tooling
Best fit	General-purpose OLTP, complex queries, JSON workloads, analytics at moderate scale	Web applications with simple read-heavy access patterns	Multi-region OLTP requiring distributed transactions without external coordination	Embedded, mobile, edge, single-writer local storage

Production Considerations

Bloat monitoring. Heap bloat from MVCC dead tuples is the leading cause of unexpected PostgreSQL performance degradation. Tables with high update rates need more aggressive autovacuum settings (autovacuum_vacuum_scale_factor, autovacuum_vacuum_cost_delay) or manual vacuum scheduling during low-traffic windows.

work_mem and sort spills. The default work_mem = 4MB is almost always too low for complex queries. Hash joins and sorts that exceed work_mem spill to disk. Set it per-session for analytical queries rather than globally, since the value applies per-sort-node and a query with five sort nodes will use up to 5 * work_mem.

Index bloat. B-tree indexes accumulate dead entries too. Unlike heap vacuum, which reclaims space for reuse, index pages are not reclaimed until a page is entirely empty. REINDEX CONCURRENTLY rebuilds an index without blocking reads or writes and should be part of routine maintenance on high-churn tables.

Connection overhead. PostgreSQL forks a new OS process per client connection. Each process starts at approximately 5-10 MB of memory. At 500 connections, that is 2.5-5 GB just for process overhead. PgBouncer in transaction-mode pooling is not optional at scale; it is a prerequisite. This is the single most common PostgreSQL production misconfiguration.

pg_stat_statements. Enable this extension. It aggregates query statistics across executions, grouped by query fingerprint. It is the fastest path to identifying slow queries, high-variance execution times, and plans that regress after a PostgreSQL upgrade or data distribution change.

SELECT query,
       calls,
       mean_exec_time,
       stddev_exec_time,
       total_exec_time,
       rows / calls AS avg_rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Checkpoint tuning. PostgreSQL periodically flushes all dirty pages from shared buffers to disk in a checkpoint. The default checkpoint_completion_target = 0.9 spreads the writes over 90% of the checkpoint interval. For write-heavy systems, increasing max_wal_size delays checkpoints and reduces I/O pressure during peaks, at the cost of longer crash recovery time.

Where PostgreSQL Shines and Where It Does Not

PostgreSQL is the right default for most OLTP workloads: it handles mixed read-write traffic, complex queries, JSON, full-text search, geospatial data (PostGIS), and time-series at moderate scale. Its correctness guarantees are best-in-class.

It is the wrong tool when you need transparent horizontal sharding across dozens of nodes under write-heavy load, or when you need columnar storage and vectorized execution for analytical queries over billions of rows (see ClickHouse or DuckDB for that case). MVCC dead tuple accumulation is a real operational burden on tables with extremely high update rates, requiring tuned autovacuum and monitored bloat.

Understanding the internals described here, particularly heap layout, MVCC visibility rules, planner cost estimation, and WAL mechanics, gives you the foundation to debug the problems that appear when PostgreSQL behaves differently than expected in production.