Back to Notes

Postgres Internals

MVCC and isolation levels are covered in [[SQL Transactions & ACID]]; EXPLAIN/indexing in [[SQL Indexes & Query Performance]]. This page is Postgres-specific physical internals — how MVCC is actually stored on disk, WAL, replication, and connection pooling.

Tuple Storage — xmin/xmax, Not In-Place Updates

Every row (tuple) carries hidden system columns xmin (the transaction ID that created it) and xmax (the transaction ID that deleted/superseded it, or null if still live). An UPDATE in Postgres never modifies a row in place — it writes an entirely new tuple version and marks the old one's xmax, leaving both physically present until cleanup.

SELECT xmin, xmax, * FROM accounts WHERE id = 1;   -- system columns are queryable directly

This is the concrete mechanism behind the MVCC snapshot-isolation behavior described in [[SQL Transactions & ACID]] — a transaction's snapshot simply means "show me tuples whose xmin committed before my start and whose xmax either doesn't exist or hasn't committed yet."

Why This Requires VACUUM

Because UPDATE/DELETE leave old tuple versions physically on disk (not immediately reclaimed), a table can accumulate dead tuples — rows no transaction can see anymore, but which still occupy space and slow down sequential scans.

VACUUM accounts;             -- reclaims dead tuple space for reuse (doesn't shrink the file back to the OS)
VACUUM FULL accounts;        -- actually shrinks the file, but takes an exclusive lock — rarely safe in production
VACUUM ANALYZE accounts;     -- reclaim + refresh planner statistics in one pass

autovacuum runs this automatically in the background based on dead-tuple thresholds — a misconfigured or disabled autovacuum on a write-heavy table is a real, common production incident (table bloat): the table file grows far larger than its live data would require, every sequential scan gets slower, and eventually even index lookups degrade as dead tuples pile up between live ones.

TOAST — Large Values

Postgres stores rows in fixed-size pages (typically 8KB); a value larger than roughly 2KB (a large TEXT/JSONB/BYTEA column) is automatically moved to a separate TOAST table, with the main row holding only a pointer. This is transparent to queries — SELECT a toasted column and Postgres fetches it automatically — but explains why a table with a few huge JSONB columns can have surprising I/O characteristics compared to its apparent row count.

Write-Ahead Log (WAL)

Every change is written to the WAL before being applied to the actual data files — this is what gives Durability (the D in ACID, see [[SQL Transactions & ACID]]): a crash after a committed transaction's WAL record is flushed to disk is recoverable by replaying the WAL on restart, even if the corresponding data-file page hadn't been written yet.

SHOW wal_level;    -- 'replica' or 'logical' needed for replication (below)

Replication

  • Streaming (physical) replication: a replica continuously receives and replays the WAL stream from the primary — the replica is a byte-for-byte copy, used for read scaling and failover, but cannot have a different schema or run different queries against different structure than the primary.
  • Logical replication: replicates at the level of row changes (not raw WAL bytes) via publish/subscribe — a subscriber can be a different Postgres version, replicate only specific tables, or even feed into a different system entirely (e.g., streaming changes into Kafka via a CDC connector, the same [[Message Queues & Kafka]] pattern applied to database change capture).

Streaming replication lag is the concrete instance of the replication-lag discussion in [[Database Replication & Sharding]] — a replica can serve reads slightly stale relative to the primary, and monitoring pg_stat_replication's lag metrics is the real-world way to catch this before it becomes a user-visible correctness issue.

Connection Pooling — Why It's Not Optional at Scale

Each Postgres connection is a full OS process (not a lightweight thread) — this makes raw per-request connections expensive and caps realistic concurrent-connection counts far lower than an application's request concurrency might assume.

PgBouncer modes:
  session   — one client connection = one server connection, released at client disconnect
  transaction — server connection returned to the pool after each transaction (most common, most efficient)
  statement — released after each statement (rare, breaks multi-statement transactions)

Transaction-mode pooling is the default choice for most web application workloads — it lets far more concurrent client connections share a much smaller pool of actual Postgres backend processes, since most connections spend most of their time idle between queries. This is the same idea as [[Caching & Redis]]'s connection-pool reasoning, applied to Postgres's process-per-connection model specifically.

Interview Drills

  1. Why does an UPDATE in Postgres not modify a row in place, and why does that matter operationally? — MVCC requires old tuple versions to remain visible to transactions whose snapshot predates the update; Postgres implements this by writing a new tuple and marking the old one's xmax rather than overwriting in place — the operational consequence is that dead tuples accumulate and must be reclaimed by VACUUM, and a table with autovacuum disabled or falling behind will bloat.
  2. A table's row count hasn't grown, but its on-disk size keeps increasing and queries are getting slower. What's the likely cause? — Table bloat from accumulated dead tuples that autovacuum isn't keeping up with (perhaps due to long-running transactions holding back the oldest visible snapshot, or autovacuum settings tuned too conservatively for the write rate) — VACUUM (or, if urgent and downtime is acceptable, VACUUM FULL) reclaims the space.
  3. Why is transaction-mode connection pooling generally preferred over session-mode for a typical web API? — Most application connections spend the majority of their time idle between queries; transaction-mode pooling returns the underlying Postgres backend process to the pool as soon as each transaction commits, letting a small pool of actual server processes serve a much larger number of concurrent application-level connections, which matters because each Postgres connection is a full OS process, not a cheap lightweight thread.

Cross-links

[[SQL Transactions & ACID]] · [[Database Replication & Sharding]] · [[Message Queues & Kafka]] · [[Caching & Redis]]