How PostgreSQL Works: MVCC, the Query Planner, and the Storage Engine Behind the World's Most Advanced Open-Source Database

PostgreSQL has been the default relational database for serious engineering teams for years, not because of marketing but because it earns the trust. ACID guarantees, a rich type system, solid replication, a mature extension ecosystem. But most engineers using Postgres daily have only a surface-level model of what happens inside when a query runs. That gap matters when you are diagnosing a slow query, tuning autovacuum, or deciding whether to add an index.

This article covers Postgres internals in the depth needed to make confident decisions: the process model, heap storage layout, MVCC mechanics, WAL, the cost-based planner, all major index types, the buffer manager, and vacuum. The goal is not completeness but accurate mental models.

The Process Model

Postgres follows a process-per-connection model, not a thread model. The postmaster is the supervisor process that listens on the TCP socket. When a client connects, the postmaster forks a backend process dedicated to that connection. Each backend runs a complete copy of the Postgres code in its own address space.

This design has clear consequences. Memory is not shared between backends (except through shared memory segments like the buffer pool and WAL buffers). A bug in one backend cannot corrupt another. Killing a backend does not threaten the postmaster. The cost is that each connection carries real overhead: 5-10 MB of resident memory per backend, plus the fork cost. At 500+ connections, this overhead becomes visible in both memory pressure and context-switching cost, which is why connection pooling is not optional for high-concurrency deployments.

Beyond client backends, Postgres runs several background workers:

• WAL writer: flushes WAL buffers to disk on a configurable schedule, reducing the number of backends that must do it inline on commit.
• Checkpointer: writes dirty shared buffer pages to data files at checkpoint intervals, bounding recovery time after a crash.
• Background writer (bgwriter): proactively writes dirty buffers to disk ahead of the checkpointer, smoothing I/O.
• Autovacuum launcher and workers: monitors tables for dead tuple buildup and transaction ID wraparound risk, triggering vacuum operations as needed.
• WAL sender processes: one per standby, streaming WAL records to physical or logical replication targets.
• Logical replication workers: apply decoded change events on the subscriber side.

The shared memory area allocated at startup holds the buffer pool (shared_buffers), WAL buffers, lock tables, and process metadata. All backends and background workers see the same buffer pool.

Heap Storage: Pages, Tuples, and TOAST

Postgres stores table data in heap files. A heap file is a sequence of 8 KB pages (configurable at compile time, but 8 KB is the default and almost universal). Each page has a header, an item pointer array at the front, free space in the middle, and tuple data growing from the back toward the front.

A tuple consists of a header followed by the actual column values. The tuple header contains:

• t_xmin: the transaction ID that inserted this tuple.
• t_xmax: the transaction ID that deleted or updated this tuple (0 if the tuple is live).
• t_ctid: the physical location of this tuple (or a newer version of it, for updated tuples).
• Null bitmap and attribute offsets for variable-length columns.
• t_infomask and t_infomask2: flags encoding visibility and lock status.

This layout is the foundation of MVCC. No separate version store: every version of a row lives in the heap as a distinct tuple.

Variable-length values (text, bytea, arrays, jsonb) live inline in the tuple if they fit. When a value exceeds roughly 2 KB, Postgres uses TOAST (The Oversized-Attribute Storage Technique). TOAST compresses the value (using LZ4 or pglz), and if it still exceeds the page-size threshold, stores it in a separate TOAST table in 2 KB chunks. The main table tuple holds a pointer. This is transparent to queries but has performance implications: accessing a TOAST-stored column requires an extra heap lookup per row. If your query frequently accesses wide JSONB or text columns and performance matters, understanding TOAST storage thresholds is worth the time.

MVCC: Readers Never Block Writers

Postgres implements multiversion concurrency control through the xmin/xmax fields in every tuple. The rule is simple: a tuple is visible to a transaction if xmin committed before the transaction’s snapshot was taken and xmax either did not commit or committed after the snapshot.

When Postgres takes a snapshot at transaction start (with the default READ COMMITTED isolation level, it re-takes the snapshot at the start of each statement), it records:

• xmin: the oldest transaction ID still running. Anything committed before this is definitely visible.
• xmax: the current transaction ID. Anything with this ID or higher is not visible.
• xip: the list of in-progress transaction IDs at snapshot time.

A tuple is visible if its t_xmin committed before the snapshot and is not in xip, and its t_xmax is either 0 (not deleted) or did not commit before the snapshot.

The result: reads and writes operate on different tuple versions simultaneously without locking. A long-running read does not block concurrent inserts or updates. An update does not block reads. This is fundamentally different from the locking model in some other databases.

The tradeoff is dead tuples. When a row is updated, the old version remains in the heap. When deleted, the tuple is marked with an xmax but its space is not immediately reclaimed. These dead tuples accumulate until vacuum cleans them up.

Here is a concrete illustration of what Postgres does when you run an UPDATE:

-- Transaction A: updates a row
BEGIN;
UPDATE orders SET status = 'shipped' WHERE id = 42;
COMMIT;

Internally:

1. Postgres finds the current tuple for id = 42.
2. It writes a new tuple with the updated status, setting t_xmin to the current transaction ID.
3. It sets t_xmax on the old tuple to the current transaction ID, marking it dead for future snapshots.
4. The old tuple remains in the heap. It is invisible to transactions that started after the commit, but visible to snapshots taken before it.

A SELECT running concurrently in another transaction sees the version consistent with its snapshot, not the updated version, without needing a lock.

The Write-Ahead Log

Every change in Postgres generates a WAL record before the change is applied to shared buffers. The WAL is an append-only log of physical changes: which page was modified, the before and after state. The WAL writer flushes these records to disk periodically; on commit, if synchronous_commit = on, Postgres calls fsync before acknowledging the commit to the client.

WAL provides two things: crash recovery (replay the WAL from the last checkpoint to reconstruct any changes not yet in the data files) and replication (ship WAL records to standbys that replay them against their own data files).

The interaction between WAL and checkpointing matters for production sizing. A checkpoint writes all dirty shared buffer pages to disk and records its position in the WAL. After the checkpoint, WAL records before that position are no longer needed for crash recovery. The checkpointer runs on a schedule (checkpoint_timeout, default 5 minutes) or when WAL accumulation exceeds max_wal_size (default 1 GB). Infrequent checkpoints let WAL grow large and extend crash recovery time. Frequent checkpoints generate bursty I/O. checkpoint_completion_target = 0.9 (default) spreads checkpoint writes over 90% of the interval to avoid I/O spikes.

WAL also grows when replication slots hold back log cleanup for lagging standbys. An unmonitored replication slot on a lagging standby can fill the disk. Monitor WAL directory size and replication lag together.

The Cost-Based Query Planner

When you submit a query, Postgres parses it into a parse tree, rewrites it (applying rules and views), then hands it to the planner. The planner generates a set of candidate execution plans and estimates the cost of each using statistics, then returns the cheapest.

The statistics live in pg_statistic, populated by ANALYZE (and autovacuum’s analyze phase). For each column, Postgres stores: number of distinct values, most common values and their frequencies, a histogram of the value distribution, and correlation (how well the physical row order matches the logical value order).

The cost model assigns each plan node two numbers: startup cost (work before producing the first row) and total cost (work to produce all rows). These are abstract units proportional to disk page reads and CPU operations, not wall-clock time. The planner picks the plan with the lowest total cost (or lowest startup cost for queries with LIMIT).

The main scan choices:

• Sequential scan: read every page in the table. Cost scales with table size. Preferred when selectivity is low (you need most of the rows anyway) or when the table fits in cache.
• Index scan: walk the B-tree to find matching tuple pointers, then fetch tuples from the heap. Each heap fetch is a random I/O. Preferred for high-selectivity queries on large tables.
• Bitmap index scan: collect all matching tuple pointers from the index, sort them by physical location, then fetch heap pages in order. Better than index scan when selectivity is medium (many matches, but not most rows).
• Index-only scan: if all needed columns are in the index, skip the heap entirely. Requires the visibility map to confirm pages are all-visible (fully vacuumed).

For joins, the planner chooses among:

• Nested loop: for each row in the outer relation, probe the inner relation. Efficient when the inner side can be index-scanned and the outer side is small.
• Hash join: build a hash table from the smaller relation, probe it with the larger. Efficient for large unsorted joins when work_mem is sufficient to hold the hash table in memory.
• Merge join: sort both sides on the join key, then merge. Efficient when both sides are already sorted or have covering indexes.

Parallel query distributes table scanning across multiple worker processes, controlled by max_parallel_workers_per_gather and the table-level parallel_workers setting. The coordinator gathers partial results from workers and combines them. Useful for large sequential scans and aggregate queries on large tables.

The planner can be wrong. Its estimates rely on statistics that may be stale or imprecise for non-uniform distributions. Use EXPLAIN (ANALYZE, BUFFERS) to see actual row counts versus estimates and identify where estimates diverge. Large divergences between estimated and actual rows usually explain bad plan choices.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.status, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > now() - interval '7 days'
  AND o.status = 'pending';

Look at the Rows column: estimate on the left, actual on the right. If the index scan estimated 10 rows but returned 50,000, the statistics are stale or the distribution is skewed. Run ANALYZE orders; or increase default_statistics_target for that column.

Index Types

Postgres ships with several index types, each suited to different access patterns:

B-tree (the default): balanced tree supporting equality, range queries, prefix matches, and sorting. Covers =, <, <=, >, >=, BETWEEN, IN, IS NULL, LIKE 'prefix%'. This is the right choice for 90% of cases.

GIN (Generalized Inverted Index): maps values to lists of row locations. Ideal for multi-valued columns like arrays, tsvector (full-text search), and JSONB. A GIN index on a JSONB column lets you search inside the document without scanning all rows.

-- GIN index for JSONB document search
CREATE INDEX idx_events_payload ON events USING gin(payload);

-- Uses the GIN index: finds rows where payload contains the key
SELECT * FROM events WHERE payload @> '{"event_type": "checkout"}';

GiST (Generalized Search Tree): a framework for custom index strategies. Used by PostGIS for spatial data (nearest-neighbor, bounding-box overlap), by range types, and by trigram indexes (pg_trgm) for substring and similarity search.

-- GiST index for geographic queries with PostGIS
CREATE INDEX idx_locations_geom ON locations USING gist(geom);

-- Uses the GiST index: finds locations within 5km of a point
SELECT name FROM locations
WHERE ST_DWithin(geom, ST_MakePoint(-73.99, 40.73)::geography, 5000);

BRIN (Block Range Index): stores the min and max values for each range of consecutive heap pages. Tiny in size, extremely fast to build, but only useful when physical storage order correlates with the indexed column. Time-series tables where rows are inserted in timestamp order are the canonical use case: a BRIN index on created_at lets the planner skip entire page ranges without reading them.

-- BRIN index for naturally time-ordered append tables
CREATE INDEX idx_events_created_at ON events USING brin(created_at);

Hash: maps each value to a 32-bit hash bucket. Supports only equality lookups (=). Smaller than B-tree for equality-only workloads, but B-tree handles range queries too. In most cases, B-tree wins unless the index is on a column used exclusively in equality comparisons on a very large table where size matters.

Partial indexes (a WHERE clause on the index) let you index only the rows that matter:

-- Index only pending orders: much smaller, faster scans
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

Covering indexes (INCLUDE) add non-key columns to the index so index-only scans can serve the query without touching the heap:

-- B-tree on customer_id; INCLUDE lets status be returned without heap access
CREATE INDEX idx_orders_customer ON orders(customer_id) INCLUDE (status, created_at);

The Buffer Manager and Shared Buffers

shared_buffers is the in-memory page cache managed by Postgres itself. When a backend needs a page, it first checks the buffer pool. If the page is present (a cache hit), it reads from memory. If not, it reads from disk into the pool, evicting another page if the pool is full.

Eviction uses a clock-sweep algorithm (a simplified approximation of LRU). Each buffer has a usage count. On access, the count increments up to a small ceiling. The clock hand sweeps through buffers; when it visits a buffer with a positive usage count, it decrements the count. When it finds a buffer with a zero count, that buffer is a candidate for eviction. Dirty buffers must be written to disk before eviction (or the bgwriter has already done it proactively).

shared_buffers should be sized to hold your hot working set. A common starting point is 25% of system RAM, but this depends heavily on your workload. If your hot dataset is 40 GB and your server has 64 GB, sizing shared_buffers to 16 GB and relying on the OS page cache for the rest can outperform a larger shared_buffers setting, because Postgres also benefits from the OS page cache. The effective interaction between shared_buffers and the OS page cache is the reason effective_cache_size exists: it is not a memory allocation but a hint to the planner about how much memory is effectively available for caching, which affects index-vs-sequential-scan cost estimates.

Vacuum and Autovacuum

Dead tuples from MVCC accumulate in the heap. Vacuum reclaims that space, making it available for new tuples within the same pages. Vacuum also updates the visibility map (which pages are all-visible and eligible for index-only scans) and updates free space map entries.

Autovacuum triggers a vacuum worker when a table accumulates enough dead tuples, governed by:

threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * n_live_tuples

Default values: autovacuum_vacuum_threshold = 50, autovacuum_vacuum_scale_factor = 0.2. A table with 1 million rows triggers autovacuum after 200,050 dead tuples. For large tables, the scale factor means autovacuum runs infrequently, which can allow significant bloat to accumulate. Lowering autovacuum_vacuum_scale_factor (to 0.01 or 0.02) on large, high-write tables improves this.

Transaction ID wraparound is the other critical role autovacuum plays. Postgres uses 32-bit transaction IDs. After 2 billion transactions, IDs wrap around. Tuples with xmin values older than the current horizon become invisible, which is catastrophic. Autovacuum runs an aggressive vacuum (regardless of table size) when a table’s oldest unfrozen transaction ID approaches the autovacuum_freeze_max_age limit (default 200 million transactions). Freezing marks tuples as permanently visible to all future transactions, removing their xmin from the wraparound danger zone.

Monitor pg_stat_user_tables.n_dead_tup and pg_database.datfrozenxid (distance from wraparound). If wraparound prevention vacuum starts running, it will prioritize that work over normal autovacuum and the impact on I/O can be significant if you have not been keeping up with regular vacuuming.

Tradeoffs vs. Alternatives

Factor	PostgreSQL	MySQL (InnoDB)	CockroachDB	AlloyDB	Aurora PostgreSQL
Write throughput	High single-node	High single-node	Lower (distributed consensus overhead)	High (disaggregated storage)	High (log-structured storage)
Horizontal write scaling	Manual sharding or Citus	Manual sharding or ProxySQL	Native distributed	Managed Citus-compatible	Limited, Aurora Limitless in preview
Operational complexity	Medium (self-managed)	Medium	High (distributed systems ops)	Low (managed)	Low (managed)
JSON/document support	Excellent (JSONB with GIN indexes)	Good (JSON, limited indexing)	Good (JSONB)	Excellent (same as PG)	Excellent (same as PG)
Extension ecosystem	Very rich (PostGIS, pgvector, TimescaleDB, Citus)	Limited	Limited	Good subset of PG extensions	Good subset of PG extensions
Managed options	RDS, Aurora, Cloud SQL, Supabase, Neon	RDS MySQL, PlanetScale, Cloud SQL	Cockroach Cloud	Google Cloud (AlloyDB)	AWS (Aurora)
Licensing	PostgreSQL License (permissive)	GPL / commercial	BSL (source-available for 4 years)	Proprietary	Proprietary
MVCC implementation	Heap-based versioning	Undo log (InnoDB)	Hybrid-logical clocks, distributed MVCC	Same as PG	Modified PG with Aurora storage

CockroachDB is worth calling out separately: it provides serializable isolation and horizontal scaling natively, which are hard problems. The cost is latency on every write (Raft consensus round-trip) and a distributed query planner that is less mature than Postgres’s. For workloads that genuinely need global distribution and strong consistency, it is a serious option. For most teams, single-region Postgres with a read replica is cheaper and simpler.

Production Considerations

shared_buffers: Start at 25% of RAM, measure cache hit ratio in pg_statio_user_tables (heap_blks_hit / (heap_blks_hit + heap_blks_read)). If the hit ratio is below 99% on hot tables, the working set is not fitting in the buffer pool. Do not allocate more than 40% of RAM to shared_buffers; the OS page cache handles the rest and oversizing starves it.

work_mem: Memory allocated per sort or hash operation, per node in the query plan. A query with three sorts can use 3x work_mem. The default (4 MB) is conservative. For analytical queries doing large sorts or hash joins, increasing work_mem session-locally is better than raising the global default: SET work_mem = '256MB'; for the session, not in postgresql.conf. Watch for swap activity when raising this on concurrent workloads.

effective_cache_size: Set to the sum of shared_buffers plus available OS page cache (typically 75% of total RAM). This is a planner hint only, not a memory allocation. Underestimating it biases the planner toward sequential scans over index scans.

Connection pooling: Do not connect applications directly to Postgres at high concurrency. Run PgBouncer in front. Transaction pooling mode (the most efficient) allows a connection pool smaller than your application concurrency to serve all requests, since most transactions are short. Configure pool_size to match the number of backend processes you want Postgres to run (often 100-200, depending on hardware), not the number of application threads.

# PgBouncer minimal config for transaction pooling
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 100
server_idle_timeout = 600

pg_stat_statements: Install this extension. It tracks query text, call count, total and mean execution time, and I/O statistics per query fingerprint. This is the first place to look when investigating performance: SELECT query, calls, mean_exec_time, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;. Queries at the top by total time, not mean time, are your actual bottlenecks.

Autovacuum tuning for large tables: Lower autovacuum_vacuum_scale_factor to 0.01 for tables above 50 million rows. Set autovacuum_vacuum_cost_delay = 2ms (or lower) if autovacuum is falling behind. Increase autovacuum_max_workers if multiple large tables need concurrent vacuuming.

Index bloat: Postgres B-tree indexes accumulate bloat over time as pages split and old entries are not removed until vacuum catches up. Monitor bloat with pgstattuple or the pg_bloat_check query. REINDEX CONCURRENTLY rebuilds the index without a table lock, useful for reclaiming space.

Closing

The behaviors that seem like quirks in Postgres are usually direct consequences of the storage model. Dead tuples are the price of MVCC’s lock-free reads. Index-only scans require a clean visibility map, which requires vacuum to have run. The planner’s plan changes after ANALYZE because the statistics changed. Bad performance on large hash joins means work_mem is too small and the hash table is spilling to disk. Connection count matters because each backend is a real OS process.

Understanding the internals does not mean rewriting the database. It means reading the right output from EXPLAIN ANALYZE, knowing which knobs matter for a given workload, and not being surprised when autovacuum affects I/O at 3am.