How PostgreSQL MVCC Actually Works

People describe PostgreSQL MVCC as if it were a feature you switch on to make readers and writers stop blocking each other. That is true in the same shallow way that saying a jet engine is a machine that makes an aeroplane go forward is true. It names the outcome and skips the mechanism.

The mechanism is much more specific. PostgreSQL does not keep one mutable row and attach a lock to it. It stores row versions on heap pages, stamps them with transaction identity, builds snapshots from the set of transactions that were in flight at a moment in time, and makes every reader prove which tuple version is visible. Vacuum, pruning, HOT updates, the visibility map, and even index-only scans all exist downstream of that decision.

That is why MVCC shows up far outside concurrency discussions. It affects why an UPDATE becomes table bloat later. It affects why an index-only scan sometimes still has to visit the heap. It affects why a forgotten reporting transaction in Frankfurt can keep dead tuples alive for hours. It affects why autovacuum is not optional housekeeping but part of the storage engine.

This article stays close to PostgreSQL's actual model in the PostgreSQL 18 manuals and in the current code paths such as src/include/access/htup_details.h, src/backend/storage/ipc/procarray.c, and src/backend/access/heap/heapam_visibility.c. The goal is simple: if you watch one row get inserted, updated, read under different isolation levels, and eventually cleaned up, you should be able to explain exactly why PostgreSQL behaved that way.

MVCC Starts On Heap Pages, Not In SQL

At the SQL layer, MVCC sounds abstract. At the storage layer, it is concrete.

A PostgreSQL table is a heap relation made of fixed-size pages, usually 8 kB each. Each page contains a page header, an array of item identifiers, free space, and tuple bodies packed from the end of the page backwards. The storage documentation in PostgreSQL 18 section 66.6 matters here because MVCC is implemented inside those page and tuple structures, not beside them.

A simplified tuple header looks like this:

typedef struct HeapTupleHeaderData {
    TransactionId t_xmin;   /* inserting transaction */
    TransactionId t_xmax;   /* deleting or updating transaction */
    ItemPointerData t_ctid; /* self or newer tuple version */
    uint16 t_infomask2;
    uint16 t_infomask;
    uint8  t_hoff;
} HeapTupleHeaderData;

The important fields are t_xmin, t_xmax, and t_ctid.

• t_xmin identifies the transaction that created this tuple version.
• t_xmax identifies the transaction that deleted this tuple version or replaced it with a newer one during an update.
• t_ctid points to the current physical location of this version or, for an updated older version, to the next version in the chain.

That means the row is carrying part of its own visibility history around with it.

A normal business row therefore looks more like this inside the engine than the SQL text suggests:

page 231, slot 4
xmin = 810204
xmax = 0
ctid = (231,4)
status = visible to snapshots newer than xid 810204
payload = order_id 91822, status 'paid'

Now imagine a later transaction updates the status to shipped. PostgreSQL does not normally overwrite the old tuple body in place. It writes a new tuple version and marks the older version with the updating transaction's ID. If the new version lands at page 231 slot 9, the state becomes:

old version at (231,4)
xmin = 810204
xmax = 810245
ctid = (231,9)
payload = order_id 91822, status 'paid'
 
new version at (231,9)
xmin = 810245
xmax = 0
ctid = (231,9)
payload = order_id 91822, status 'shipped'

This is the first thing worth fixing in your mental model. PostgreSQL does not treat an update as "change the row". It treats an update as "end the old visible version for future snapshots, then insert a successor version".

That design buys a lot:

• readers can keep using an older consistent view without blocking the writer
• writers do not need to wait for every reader to finish before publishing a new version
• repeatable reads can keep seeing the same logical state even while later commits happen elsewhere

It also creates immediate costs:

• old versions occupy heap space
• indexes may need to point at both old and new versions until cleanup is safe
• every read needs visibility logic, not just key lookup
• some background process must eventually reclaim obsolete history

Those costs are not edge cases. They are the steady state of the engine.

A Snapshot Is A Visibility Boundary, Not A Copy Of The Table

The phrase snapshot misleads people because it sounds like PostgreSQL copies rows somewhere and hands that copy to the transaction. It does not.

A snapshot is mostly a set of transaction boundaries plus a list of in-flight transaction IDs. In current PostgreSQL code, SnapshotData contains more fields than this, but the core shape is roughly:

typedef struct SnapshotData {
    TransactionId xmin;
    TransactionId xmax;
    TransactionId *xip;
    uint32 xcnt;
} SnapshotData;

Conceptually:

• xmin is the oldest transaction that was still relevant when the snapshot was built
• xmax is the first transaction ID that had not yet been assigned to any completed transaction in the visible set
• xip[] is the list of transactions that were in progress and therefore must be treated as not yet visible

PostgreSQL constructs that snapshot by looking at the process array of active backends in procarray.c. It is not taking a copy of every row in the table. It is taking a compact record of which transaction outcomes must count as visible, invisible, or still in doubt.

Suppose the system is in this state when a reader starts:

committed: 700, 701, 702, 703
running:   704, 706
aborted:   705
next xid:  707

A fresh snapshot might be represented conceptually as:

xmin = 704
xmax = 707
in-progress = [704, 706]

Now ask what that snapshot should do with a tuple version carrying a particular xmin.

• xmin = 702: committed before the snapshot horizon, so potentially visible
• xmin = 705: aborted, so invisible
• xmin = 706: still in progress according to xip[], so invisible
• xmin = 707: from the future relative to this snapshot, so invisible

That is the central trick. PostgreSQL does not tag rows as globally visible or globally hidden in an absolute sense. It evaluates tuple metadata against the reader's snapshot.

This explains why several readers can inspect the same physical heap page and come to different logical conclusions.

Imagine two sessions in Amsterdam.

Session A starts first and gets a snapshot before an update commits.

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT status FROM orders WHERE id = 91822;
-- returns 'paid'

Session B starts later, updates the row, and commits.

BEGIN;
UPDATE orders SET status = 'shipped' WHERE id = 91822;
COMMIT;

Session C now runs a new SELECT and sees shipped.

Session A runs the same SELECT again inside its repeatable read transaction and still sees paid.

Nothing supernatural happened. Session A and Session C are evaluating the same version chain through different snapshots.

The phrase snapshot isolation becomes much clearer once you stop imagining a copied table and start imagining a stable visibility rule.

Updates And Deletes Create Version Chains Instead Of Overwriting Rows

INSERT is the easy case. It creates a tuple version with xmin = my_xid and xmax = 0. Until the inserting transaction commits, that version is visible to itself under its own rules but not yet visible to other transactions.

DELETE does not remove the bytes immediately. It marks the tuple version with its own XID in xmax. That tells future visibility checks: "this version stops being visible once this deleting transaction counts as committed to your snapshot".

UPDATE combines both ideas.

1. mark the old version's xmax with the updating transaction ID
2. create a new version with xmin equal to that same transaction ID
3. link the old version toward the new one through ctid

That is why old row versions remain on the heap after ordinary updates. PostgreSQL needs those bytes until no valid snapshot could still need them.

A common mistake is to think that an old tuple becomes garbage at commit time. It often becomes dead to new snapshots at commit time, but dead is not the same as removable.

Consider a timeline in Brussels:

T1 xid 820 starts and takes snapshot S1
T2 xid 821 updates account 44 from balance 100 to 125 and commits
T3 xid 822 starts after the commit

Physical state after T2 commits:

version A: xmin 700, xmax 821, ctid -> version B
version B: xmin 821, xmax 0,   ctid -> self

Logical results:

• T1 under snapshot S1 can still need version A
• T3 should see version B
• vacuum must not reclaim version A yet if T1 remains active

This difference between obsolete for some readers and reclaimable for the system is one of the most important operational facts in PostgreSQL.

Another subtlety is self visibility. PostgreSQL also tracks command IDs within a transaction so a statement can reason about rows inserted, updated, or deleted earlier in the same transaction. The full machinery is more detailed than just xmin and xmax. That extra detail is why the engine can answer questions like "should this transaction see its own earlier insert?" without waiting for commit state visible to others.

Once you internalise version chains, several familiar PostgreSQL behaviours stop looking strange.

• UPDATE heavy workloads create bloat because each logical change can create an extra physical tuple version.
• DELETE heavy workloads do not immediately shrink table files because tuples are being marked obsolete, not erased in place.
• long transactions matter because they keep old snapshots alive, and old snapshots keep old versions relevant.

The storage engine is choosing concurrency first and tidy pages later.

Reads Must Prove Visibility, And That Is Work

A B tree index lookup in PostgreSQL is often only the beginning of a read, not the end.

Suppose an index finds a TID pointing to heap page 231 slot 9. The executor still has to decide whether the tuple version at that location is visible to the current snapshot. That logic lives in functions such as HeapTupleSatisfiesMVCC in heapam_visibility.c.

The visibility check asks questions like these:

1. Did the inserting transaction commit?
2. If it committed, did it commit before this snapshot?
3. Did a deleting or updating transaction mark xmax?
4. If so, did that transaction commit, abort, or remain in progress relative to this snapshot?
5. Are there cached hint bits on the tuple header that already tell us some of this?
6. If not, do we need to consult shared transaction status in pg_xact?

This is real read-path work. PostgreSQL pays it because the heap is carrying multiple versions of truth at once.

The commit status lookup path matters here. PostgreSQL stores transaction state in pg_xact, the SLRU-backed commit status store formerly known in everyday PostgreSQL speech as CLOG. If a tuple header does not already contain enough hint information, the backend may need to look up whether xmin or xmax committed or aborted.

Once it learns the answer, it can often set hint bits in the tuple header so later readers do not need to ask again. That is one reason a row can become cheaper to read after the first few visits. The tuple header accumulates cached visibility facts.

A simplified mental flow for one tuple read is:

index or heap scan finds tuple
-> read xmin/xmax/infomask
-> if hints are enough, decide locally
-> otherwise check pg_xact for commit state
-> compare result against my snapshot
-> return visible version or keep scanning

This also explains why PostgreSQL indexes normally do not make MVCC disappear. An index can find candidate tuple locations quickly, but visibility still lives in the heap plus MVCC metadata.

That is where the visibility map comes in. If a heap page is marked all-visible, PostgreSQL knows every tuple on that page is visible to all current and future transactions until the page changes again. An index-only scan can then skip the heap visit for visibility proof. Without that page-level guarantee, the heap still has to be consulted even if all requested columns are already in the index.

So when people say PostgreSQL reads are more expensive than a naive key-value lookup, this is part of what they mean. The engine is not only finding bytes. It is proving version legitimacy relative to a snapshot.

The upside is strong, predictable semantics.

The downside is that churned tables can make reads pay more heap visits, more cache misses, and more visibility checks than the SQL text suggests.

Isolation Levels Reuse The Same Version Store Differently

PostgreSQL does not implement a separate storage engine for each isolation level. It reuses the same MVCC version store and changes when snapshots are taken and how conflicts are treated.

The practical table looks like this:

Read Committed is PostgreSQL's default, and it is often misunderstood.

In Read Committed, every statement gets a fresh snapshot. That means one transaction can run two SELECT statements and get different answers if another transaction committed in between.

Example:

-- session A
BEGIN;
SELECT status FROM orders WHERE id = 91822;
-- returns 'paid'
 
-- session B
UPDATE orders SET status = 'shipped' WHERE id = 91822;
COMMIT;
 
-- session A again, still inside the same transaction
SELECT status FROM orders WHERE id = 91822;
-- now returns 'shipped'
COMMIT;

Session A did not violate consistency. It simply took two different statement snapshots inside one transaction.

Repeatable Read changes that by taking one snapshot for the whole transaction. The second SELECT in Session A would keep returning paid even after Session B committed, because Session A is still evaluating visibility through its original snapshot.

That is where MVCC becomes operationally expensive in a different way. A long repeatable read transaction can keep old row versions relevant for a long time, which in turn delays cleanup.

Serializable in PostgreSQL builds on snapshot isolation rather than reverting to coarse locking. PostgreSQL uses serializable snapshot isolation, tracking read and write dependencies to detect dangerous structures that could allow a non-serial outcome. It then aborts one participant with a serialization failure when needed.

This is worth stating plainly because the phrase serializable still makes some people imagine a system that simply locks everything in sequence. PostgreSQL is doing something more subtle. It lets transactions use snapshots, then watches for dependency patterns that cannot be allowed to stand together.

So the isolation levels are not separate products. They are different ways of driving the same tuple-version machinery.

That shared machinery is why operational symptoms cross layers.

• a long repeatable read query can hold back vacuum
• a default read committed workload can show different answers across statements in one transaction
• a serializable workload can fail with retryable serialization errors even when explicit row locks seem absent

All of that is one MVCC engine viewed through different transaction rules.

HOT Updates, Page Pruning, And Vacuum Stop History From Taking Over

If PostgreSQL only ever appended new tuple versions and never cleaned anything up until a full table rewrite, it would drown in its own history.

Three mechanisms keep that from happening in normal operation:

• HOT updates
• page pruning
• vacuum

They are related but not identical.

HOT updates reduce index churn

HOT means heap-only tuple. PostgreSQL can use a HOT update when:

• the update does not change any indexed column
• there is enough free space on the same heap page for the new version

In that case the engine can add the new tuple version on the same page and avoid creating fresh index entries for every update. Existing indexes keep pointing at the original line pointer, and heap navigation follows the chain to the currently visible version.

This is a big deal on churn-heavy tables. If a table in Milan updates a non-indexed last_seen_at column all day, HOT can keep index growth far lower than a normal update path would.

It also explains why fillfactor is not cosmetic tuning. Leaving room on the page increases the chance that the successor version fits on the same page, which increases the chance that the update qualifies for HOT.

Page pruning removes some dead chain structure during normal access

Page pruning is lighter-weight local cleanup that can happen during ordinary heap page visits. If a backend sees a page where old HOT chain members or redirectable line pointers can be compacted safely, it can do some of that work without waiting for a full table vacuum.

That is why the page header's pd_prune_xid exists. It is a hint that pruning might be worth trying.

Pruning is not a substitute for vacuum. It is local and opportunistic. It does not walk the whole relation, it does not perform global index cleanup, and it does not solve freeze obligations for the table as a whole.

But pruning does matter because it means some MVCC cleanup debt is paid on the foreground path before autovacuum arrives.

Vacuum makes reclaimability global and durable

Vacuum is the full relation-level mechanism that turns "this old version is dead" into "this space may be reused safely".

For ordinary heap tuples, concurrent plain vacuum in PostgreSQL 18 follows the familiar three-stage outline described in vacuumlazy.c:

1. scan the heap and collect dead tuple identifiers
2. clean matching index entries
3. return to the heap and reap dead line pointers so space becomes reusable

Index cleanup comes before final heap reuse because index safety comes first. An index entry must not point at a slot that has already been recycled for an unrelated tuple.

Vacuum also updates side metadata such as:

• the free space map, so future writers can find pages with room
• the visibility map, so future vacuums and index-only scans can skip work
• freeze state, so ancient XIDs do not become dangerous after wraparound

So when operators say "vacuum" they are often naming several storage-engine jobs at once.

One more practical consequence follows from all this: file size lies.

A vacuumed table may still look large on disk because the file was not rewritten. The free space is internal reuse potential, not necessarily bytes returned to the operating system. The only honest question is not "did the file shrink?" but "did old versions become reusable and did visibility metadata improve?"

Freeze Keeps Old Visible Rows Safe Across XID Wraparound

Dead tuples are not the only historical burden MVCC creates. Very old live tuples are a problem too.

PostgreSQL transaction IDs are 32 bit counters. They eventually wrap. If the system kept comparing ancient xmin values forever as if the number line never looped, an old row could eventually look like it came from the future. That would be catastrophic, because a perfectly valid row could become invisible to normal queries simply because arithmetic on transaction age stopped meaning what the reader thought it meant.

The answer is freezing.

When a tuple becomes old enough, vacuum can mark it as permanently old in MVCC terms. Historically this was described as replacing xmin with FrozenTransactionId. In modern PostgreSQL the exact representation is more nuanced because the tuple header also carries infomask state, but the operational meaning is the same: this tuple no longer depends on ordinary XID age comparison. It is old enough that every future normal transaction must treat it as visible from the distant past.

This is why even a quiet table still needs vacuum eventually. Imagine a customer record in Madrid that has not changed in nine months. There may be no dead tuples on its page at all. There may be no bloat story. But the row still carries an old xmin, and that age continues advancing as the rest of the cluster burns through transaction IDs elsewhere. Vacuum has to revisit that page at some point so wraparound arithmetic never turns a harmless old row into a dangerous ambiguity.

The cluster tracks this with horizons such as relfrozenxid on tables and datfrozenxid on databases. Once age crosses configured thresholds, vacuum becomes more aggressive about scanning pages that an ordinary cleanup-focused vacuum might have skipped. That is why anti-wraparound autovacuum can appear on tables that do not seem busy at all. The system is not cleaning churn. It is preserving the validity of MVCC time itself.

This also explains one of PostgreSQL's least intuitive operational behaviours. A team sees no obvious delete load, no giant dead-tuple count, and no alarming application latency. Then autovacuum suddenly starts forcing its way through old tables and competing with maintenance windows. From the engine's perspective that is not surprising. Freeze debt had been accumulating silently.

MVCC therefore creates two very different kinds of historical work:

• remove obsolete versions that old snapshots no longer need
• freeze ancient visible versions so wraparound cannot corrupt visibility semantics

Operators who focus only on bloat miss half the picture. PostgreSQL vacuum is not only a garbage collector. It is also the mechanism that keeps transaction time comparable after billions of commits.

Snapshot Acquisition Has A Cost Of Its Own

MVCC is often sold as if it gives cheap non-blocking reads for free. It does not. The read path pays for visibility checks, and snapshot acquisition itself has a concurrency cost.

To build a snapshot, PostgreSQL has to inspect the active transaction state in shared memory. In broad terms procarray.c gathers the current set of running XIDs, computes the visibility boundaries, and hands the reader a snapshot describing which transactions count as in progress. That work is usually fast, but it is still work, and it scales with churn and concurrency rather than with the number of rows in your specific query.

This is one reason connection storms and high transaction churn can show up in places people did not expect. The expensive part is not only the SQL plan. It is also the fact that thousands of backends may be creating fresh snapshots, checking visibility, and advancing global horizons at the same time.

Read committed workloads feel this differently from repeatable read workloads. In read committed, every statement may need a fresh snapshot, so a chatty application that fires many short statements can spend surprising effort on repeated snapshot setup even if each individual query is simple. In repeatable read, the transaction reuses one snapshot, which avoids some repeated acquisition cost, but it can preserve old history longer and push the cost into cleanup instead.

That tradeoff shows up clearly in pool sizing. A system with sensible connection pooling in Copenhagen may have the same logical workload as a system with five times as many backends and far worse behaviour. The second system is not only wasting memory. It is asking PostgreSQL to manage far more concurrent transaction state, horizon bookkeeping, and snapshot coordination than the workload really needs.

This is another reason MVCC discussions that stay at the level of "readers do not block writers" are incomplete. Readers still participate in shared global bookkeeping. They are cheap compared with coarse locking, but they are not free, and enough of them can still become a scalability problem if the application treats transactions and connections carelessly.

Long Transactions And Replicas Stretch The Cleanup Horizon

MVCC history is only cheap if obsolete versions become reclaimable reasonably soon.

The opposite case is one of PostgreSQL's classic operational pathologies: old snapshots stretch the cleanup horizon, so dead tuples remain physically present long after the business meaning moved on.

The simplest version is a forgotten transaction.

A reporting session in Vienna runs:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT ... FROM orders ...;

Then the client goes idle for two hours.

From the application's perspective this is sloppy. From the storage engine's perspective it is a visibility constraint. Any tuple version that might still be visible to that old snapshot must stay put. Vacuum can see it is old. Vacuum may know newer versions exist. Vacuum still cannot remove it if the old snapshot could legally read it.

That is how you get patterns like these:

• n_dead_tup keeps rising on busy tables
• autovacuum keeps running but bloat still grows
• pg_stat_activity shows old transactions or backend_xmin values pinning horizons

A common production question is therefore not "is autovacuum on?" but "what is stopping cleanup from advancing?"

Replicas can create the same effect in a less obvious place.

On hot standbys, replay and query serving share a tension. A standby query may be using an old snapshot while WAL replay wants to apply changes that would remove or invalidate versions that snapshot still needs. PostgreSQL can resolve that tension by cancelling the conflicting standby query, or by allowing replay lag to grow, depending on settings and workload shape.

hot_standby_feedback adds another tradeoff. It can send snapshot horizon feedback from standby to primary so the primary avoids vacuum cleanup that would break standby queries. That reduces query cancellations on replicas, but it can also preserve dead tuples on the primary for longer and contribute to bloat.

So the question "why is vacuum not reclaiming these dead rows?" can have answers nowhere near the table itself:

• an idle in transaction session on the primary
• a long repeatable read report
• a prepared transaction
• standby feedback preserving an old horizon
• replication slots or other retention pressure that changes surrounding cleanup economics

MVCC is local at the tuple header and global at the horizon. That global horizon is why one badly behaved reader can make storage costs visible cluster-wide.

MVCC Shapes Index Access And Query Plans

MVCC is not only about whether updates block. It shapes what a fast plan is allowed to look like.

The clean example is the difference between an index scan and an index-only scan.

In a naive mental model, a covering index should be enough. If the index already contains every requested column, why would PostgreSQL touch the heap at all?

Because the index entry does not prove visibility.

An index tuple can point to a heap tuple whose inserting transaction is invisible to this snapshot, or whose deleting transaction committed before this snapshot, or whose page has been modified since the last all-visible guarantee. PostgreSQL therefore needs a separate proof that heap visibility is universally safe to skip. That proof comes from the visibility map.

This is why high write churn often suppresses true index-only behaviour. The table may have the right index definition, but if pages are constantly being modified, their all-visible bits keep getting cleared. The executor then falls back to heap visibility checks.

MVCC also changes update economics for indexed tables.

• if an update changes indexed columns, PostgreSQL usually needs fresh index entries for the successor version
• if the update is HOT-eligible, index churn can be avoided
• if old versions stay around for a long time, the heap and indexes both carry more historical weight

Even page layout becomes part of planning economics. A table with low fillfactor and frequent HOT-friendly updates may behave far better than a logically similar table whose updates touch indexed columns and spill successor versions onto different pages.

This is one reason storage and query planning cannot be separated cleanly in PostgreSQL.

At the SQL layer you may think you are asking a simple question:

SELECT email
FROM customers
WHERE customer_id = 44;

At the engine layer the plan may really involve:

• B tree descent to find candidate TIDs
• heap fetches to check tuple visibility
• HOT chain following on the page
• possible pg_xact lookups if hint bits are missing
• fallback to another candidate tuple if the first one is not visible to this snapshot

That is still efficient by general database standards. It is just not a single immutable-record lookup.

The contrast with undo-chain engines helps here. In systems that keep the latest version in place and reconstruct older versions from undo records, the current read path and historical read path have different economics. PostgreSQL makes a different trade. It stores versions directly in the heap and asks readers to navigate visibility and cleanup consequences from there.

That choice gives PostgreSQL very direct snapshot semantics. It also means MVCC has fingerprints on almost every planner and storage discussion.

You Can Observe MVCC Directly In Production

A lot of PostgreSQL folklore comes from operators diagnosing MVCC symptoms indirectly. The engine is actually generous with observability if you know where to look.

Find long transactions that may pin cleanup

SELECT pid,
       usename,
       state,
       xact_start,
       backend_xmin,
       now() - xact_start AS xact_age,
       query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

This does not prove that every old transaction is causing bloat, but it shows you where to start. A very old backend_xmin is often the first clue that dead versions cannot yet die.

Watch dead tuples and vacuum cadence

SELECT relname,
       n_live_tup,
       n_dead_tup,
       last_vacuum,
       last_autovacuum,
       vacuum_count,
       autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

This helps separate "the table is busy" from "the table is accumulating historical debt".

Check freeze age before it becomes an incident

SELECT c.oid::regclass AS table_name,
       age(c.relfrozenxid) AS xid_age
FROM pg_class c
WHERE c.relkind = 'r'
ORDER BY age(c.relfrozenxid) DESC
LIMIT 20;

And at database level:

SELECT datname, age(datfrozenxid)
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

These queries matter because vacuum is not only about dead tuple cleanup. It is also how PostgreSQL keeps 32 bit transaction IDs from wrapping into nonsense.

Inspect active vacuum progress

SELECT pid,
       relid::regclass AS table_name,
       phase,
       heap_blks_total,
       heap_blks_scanned,
       heap_blks_vacuumed,
       index_vacuum_count,
       num_dead_tuples
FROM pg_stat_progress_vacuum;

If a vacuum seems slow or stuck, this tells you whether it is scanning heap pages, vacuuming indexes, vacuuming the heap, or truncating the tail.

Validate whether index-only scans are plausible

The planner output itself is the obvious place to start.

EXPLAIN (ANALYZE, BUFFERS)
SELECT email
FROM customers
WHERE customer_id = 44;

If you expected an index-only scan and keep seeing heap fetches, the problem may not be the index definition. It may be MVCC churn preventing enough pages from remaining all-visible.

If needed, look at tuple-level reality

Extensions such as pageinspect and contrib tools like pg_visibility exist for a reason. When a situation is genuinely unclear, PostgreSQL lets you inspect heap pages, visibility map state, and low-level tuple metadata directly. That is where weird-looking symptoms often become obvious.

The general lesson is simple: do not diagnose MVCC through table size alone.

Look at:

• transaction age
• backend_xmin
• dead tuple counts
• vacuum progress
• freeze age
• execution plans that reveal heap visibility costs

The system is telling you what its visibility and cleanup boundaries are. You just need to read the right counters.

PostgreSQL Chooses Predictable Visibility Semantics, Then Pays The Bill Elsewhere

Every serious database pays concurrency costs somewhere.

PostgreSQL pays many of its costs in tuple version churn, read-path visibility checks, cleanup work, and horizon management. In return it gets a model that is conceptually clean once you see the parts together.

• rows are versioned on the heap
• snapshots define which transaction outcomes count as visible
• reads evaluate tuple headers against those snapshots
• updates publish successors instead of overwriting history
• vacuum, pruning, HOT, and freeze machinery keep the version store from growing without bound

That is why PostgreSQL MVCC is best understood as a storage design with concurrency consequences, not as a concurrency feature with some storage side effects.

If you only remember one thing, make it this: PostgreSQL does not ask "what is the current row?" first. It asks "which row version is visible to this snapshot, and what work is required to prove it?"

Once you think that way, a lot of familiar PostgreSQL behaviour stops being surprising.

A second SELECT in Read Committed can return a newer value because it got a newer snapshot. A repeatable read report can keep dead tuples alive because its old snapshot still matters. An index-only scan can fall back to heap fetches because visibility proof is missing. Autovacuum can run constantly and still fail to reduce bloat if some transaction horizon refuses to move.

Those are not disconnected quirks. They are the same MVCC design seen from different angles.

And that is what "PostgreSQL MVCC" actually means in practice: tuple headers, snapshot rules, visibility checks, and cleanup discipline working together so the database can tell different readers different truthful stories about the same physical bytes without losing track of which story each reader is allowed to hear.