One of the most persistent misconceptions about PostgreSQL MVCC is that old row versions accumulate in a chain until VACUUM removes them, making reads increasingly expensive as updates pile up.
That's not how PostgreSQL works. Space amplification is common in MVCC databases because they need to access multiple versions over time, but this doesn't necessarily lead to read amplification. Databases are built to read only the data they need from a larger dataset.
In this article, I'll demonstrate three important facts:
- Scans don't walk version chains across pages — Seq Scans examine heap tuples directly and skip invisible ones; Index Scans follow the HOT chain only within a single page; Bitmap Scans behave like one or the other depending on bitmap losiness.
- Making space reusable in heap and indexes doesn't wait for vacuum — normal reads perform maintenance with hint bits and opportunistic heap pruning, even with autovacuum disabled.
- Index scans pay the visibility cost once, and mark dead entries LP_DEAD to skip future heap visits.
As a result, PostgreSQL can build up dead tuples and index entries, but read amplification doesn't increase proportionally, and space can be reused over time.
Setup
PostgreSQL MVCC is known for space amplification (called bloat), but it doesn't accumulate old versions forever. Some garbage collection (called vacuum) happens in the background. However, this article focuses on read amplification before vacuum. For the purpose of the demo, I created a table and disabled auto-vacuum:
drop table if exists mvcc_demo;
create table mvcc_demo (
id int,
a int,
b int,
filler text default repeat('x',1000)
);
alter table mvcc_demo set (autovacuum_enabled = off);
create index on mvcc_demo ( a );
create index on mvcc_demo ( b );
insert into mvcc_demo
select n,n,n
from generate_series(1,8) n;
vacuum analyze;
To inspect heap pages, I prepare a helper query that lists all line pointers in a page except those with length zero, which are only small stubs:
create extension if not exists pageinspect;
prepare show_tuples(int,int) as
select page, lp, t_xmin, t_xmax, t_ctid,
regexp_replace(t_data::text,'^(\\x)(.{8})(.{8})(.{8})(.{8}).*$','id=\\x\2 a=\\x\3 b=\\x\4 filler=\\x\5...'),
t_infomask, t_infomask2
from generate_series($1,$2) page, lateral (
select * from heap_page_items(get_raw_page('mvcc_demo', page)) where lp_len>0
) order by page, lp
;
execute show_tuples(0,1)
;
The initial state shows eight tuples:
page | lp | t_xmin | t_xmax | t_ctid | t_infomask | regexp_replace
------+----+--------+--------+--------+------------+--------------------------------------------------------------
0 | 1 | 697 | 0 | (0,1) | 2306 | id=\x01000000 a=\x01000000 b=\x01000000 filler=\xb00f0000...
0 | 2 | 697 | 0 | (0,2) | 2306 | id=\x02000000 a=\x02000000 b=\x02000000 filler=\xb00f0000...
0 | 3 | 697 | 0 | (0,3) | 2306 | id=\x03000000 a=\x03000000 b=\x03000000 filler=\xb00f0000...
0 | 4 | 697 | 0 | (0,4) | 2306 | id=\x04000000 a=\x04000000 b=\x04000000 filler=\xb00f0000...
0 | 5 | 697 | 0 | (0,5) | 2306 | id=\x05000000 a=\x05000000 b=\x05000000 filler=\xb00f0000...
0 | 6 | 697 | 0 | (0,6) | 2306 | id=\x06000000 a=\x06000000 b=\x06000000 filler=\xb00f0000...
0 | 7 | 697 | 0 | (0,7) | 2306 | id=\x07000000 a=\x07000000 b=\x07000000 filler=\xb00f0000...
1 | 1 | 697 | 0 | (1,1) | 2306 | id=\x08000000 a=\x08000000 b=\x08000000 filler=\xb00f0000...
(8 rows)
The value 0 of t_xmax and the t_ctid pointing to itself indicate that the tuple is the current version of the row.
Creating a version chain
I'll update one row several times with the following statement:
postgres=# update mvcc_demo
set a=a+1
where id=1
;
UPDATE 1
The first update created a new row version on another page because there was no space on the same page. The original tuple is updated with t_ctid storing the address of the next version, and receives its end of visibility in xmax:
page | lp | t_xmin | t_xmax | t_ctid | t_infomask | regexp_replace
------+----+--------+--------+--------+------------+--------------------------------------------------------------
0 | 1 | 697 | 740 | (1,2) | 258 | id=\x01000000 a=\x01000000 b=\x01000000 filler=\xb00f0000...
0 | 2 | 697 | 0 | (0,2) | 2306 | id=\x02000000 a=\x02000000 b=\x02000000 filler=\xb00f0000...
0 | 3 | 697 | 0 | (0,3) | 2306 | id=\x03000000 a=\x03000000 b=\x03000000 filler=\xb00f0000...
0 | 4 | 697 | 0 | (0,4) | 2306 | id=\x04000000 a=\x04000000 b=\x04000000 filler=\xb00f0000...
0 | 5 | 697 | 0 | (0,5) | 2306 | id=\x05000000 a=\x05000000 b=\x05000000 filler=\xb00f0000...
0 | 6 | 697 | 0 | (0,6) | 2306 | id=\x06000000 a=\x06000000 b=\x06000000 filler=\xb00f0000...
0 | 7 | 697 | 0 | (0,7) | 2306 | id=\x07000000 a=\x07000000 b=\x07000000 filler=\xb00f0000...
1 | 1 | 697 | 0 | (1,1) | 2306 | id=\x08000000 a=\x08000000 b=\x08000000 filler=\xb00f0000...
1 | 2 | 740 | 0 | (1,2) | 10242 | id=\x01000000 a=\x02000000 b=\x01000000 filler=\xb00f0000...
(9 rows)
This t_ctid is what makes people think every read must follow a growing chain of versions. Ordinary visibility checks don't work that way because:
- if the query's read snapshot is between
xminandxmax, this is the right row, and there's no need to get another one - if the tuple is not visible to the snapshot, it is skipped. The scan continues normally and may encounter another version of the same logical row elsewhere in the heap.
Here, the row with id=1 (\x01000000) has two versions in two pages - it's not a HOT (heap-only tuple) update. The new version was inserted on a page with free space.
After a second update, the same happens, but there is free space on the same page, so the new version is inserted there, with two versions of id=1 (\x01000000) on page 1:
page | lp | t_xmin | t_xmax | t_ctid | t_infomask | regexp_replace
------+----+--------+--------+--------+------------+--------------------------------------------------------------
0 | 2 | 697 | 0 | (0,2) | 2306 | id=\x02000000 a=\x02000000 b=\x02000000 filler=\xb00f0000...
0 | 3 | 697 | 0 | (0,3) | 2306 | id=\x03000000 a=\x03000000 b=\x03000000 filler=\xb00f0000...
0 | 4 | 697 | 0 | (0,4) | 2306 | id=\x04000000 a=\x04000000 b=\x04000000 filler=\xb00f0000...
0 | 5 | 697 | 0 | (0,5) | 2306 | id=\x05000000 a=\x05000000 b=\x05000000 filler=\xb00f0000...
0 | 6 | 697 | 0 | (0,6) | 2306 | id=\x06000000 a=\x06000000 b=\x06000000 filler=\xb00f0000...
0 | 7 | 697 | 0 | (0,7) | 2306 | id=\x07000000 a=\x07000000 b=\x07000000 filler=\xb00f0000...
1 | 1 | 697 | 0 | (1,1) | 2306 | id=\x08000000 a=\x08000000 b=\x08000000 filler=\xb00f0000...
1 | 2 | 740 | 741 | (1,3) | 8450 | id=\x01000000 a=\x02000000 b=\x01000000 filler=\xb00f0000...
1 | 3 | 741 | 0 | (1,3) | 10242 | id=\x01000000 a=\x03000000 b=\x01000000 filler=\xb00f0000...
(9 rows)
Actually, all versions of id=1 (\x01000000) are on the same page because the initial version has disappeared from the first page even without vacuum, proof that free space is released even before vacuum runs. What happened is that the update has read the first page and did some cleanup while the buffer was pinned.
This is proof that garbage collection can happen without vacuum, simply when UPDATE, DELETE, or SELECT reads after the update. It is called opportunistic pruning: pruning is attempted whenever a page's free space heuristically looks low, or a page previously failed to fit an updated tuple. Space reclamation happens during tuple retrieval when the page is full or nearly full (<10% free or fillfactor target) and a buffer cleanup lock can be acquired.
Additionally, when the UPDATE has to move a new version to a different page because there isn't room, it flags the old page as full. Here, there was space to place the new version on the same page.
Here is a third update that adds another version:
page | lp | t_xmin | t_xmax | t_ctid | t_infomask | regexp_replace
------+----+--------+--------+--------+------------+--------------------------------------------------------------
0 | 2 | 697 | 0 | (0,2) | 2306 | id=\x02000000 a=\x02000000 b=\x02000000 filler=\xb00f0000...
0 | 3 | 697 | 0 | (0,3) | 2306 | id=\x03000000 a=\x03000000 b=\x03000000 filler=\xb00f0000...
0 | 4 | 697 | 0 | (0,4) | 2306 | id=\x04000000 a=\x04000000 b=\x04000000 filler=\xb00f0000...
0 | 5 | 697 | 0 | (0,5) | 2306 | id=\x05000000 a=\x05000000 b=\x05000000 filler=\xb00f0000...
0 | 6 | 697 | 0 | (0,6) | 2306 | id=\x06000000 a=\x06000000 b=\x06000000 filler=\xb00f0000...
0 | 7 | 697 | 0 | (0,7) | 2306 | id=\x07000000 a=\x07000000 b=\x07000000 filler=\xb00f0000...
1 | 1 | 697 | 0 | (1,1) | 2306 | id=\x08000000 a=\x08000000 b=\x08000000 filler=\xb00f0000...
1 | 2 | 740 | 741 | (1,3) | 9474 | id=\x01000000 a=\x02000000 b=\x01000000 filler=\xb00f0000...
1 | 3 | 741 | 742 | (1,4) | 8450 | id=\x01000000 a=\x03000000 b=\x01000000 filler=\xb00f0000...
1 | 4 | 742 | 0 | (1,4) | 10242 | id=\x01000000 a=\x04000000 b=\x01000000 filler=\xb00f0000...
(10 rows)
The row id=1 (\x01000000) started with a=1 (\x01000000), then updated to a=2 (\x02000000), a=3 (\x03000000), and a=4 (\x04000000). Because no open transactions need to read those old values, Postgres cleans them up when possible to free space on the page.
In this example, I update an indexed column, so even if the new version lands on the same page, this isn't a HOT update—a separate index entry is created. Because the old version might still be referenced by an index entry, ordinary read-triggered pruning cannot fully discard its line pointer. It is still there with a length of zero, which I filter out with lp_len>0 - so that id=1 (\x01000000), a=1 (\x01000000) disappeared.
However, page defragmentation reclaimed the tuple storage even though the line pointer is retained as a stub, and this space can be reused before any VACUUM runs and removes the line pointer. I update another row, id=2 (\x02000000) in the first page, and the new version fits there in a new line pointer of the same page:
postgres=# update mvcc_demo
set a=a+1
where id=2
;
UPDATE 1
postgres=# execute show_tuples(0,1)
;
page | lp | t_xmin | t_xmax | t_ctid | t_infomask | regexp_replace
------+----+--------+--------+--------+------------+--------------------------------------------------------------
0 | 2 | 697 | 743 | (0,8) | 258 | id=\x02000000 a=\x02000000 b=\x02000000 filler=\xb00f0000...
0 | 3 | 697 | 0 | (0,3) | 2306 | id=\x03000000 a=\x03000000 b=\x03000000 filler=\xb00f0000...
0 | 4 | 697 | 0 | (0,4) | 2306 | id=\x04000000 a=\x04000000 b=\x04000000 filler=\xb00f0000...
0 | 5 | 697 | 0 | (0,5) | 2306 | id=\x05000000 a=\x05000000 b=\x05000000 filler=\xb00f0000...
0 | 6 | 697 | 0 | (0,6) | 2306 | id=\x06000000 a=\x06000000 b=\x06000000 filler=\xb00f0000...
0 | 7 | 697 | 0 | (0,7) | 2306 | id=\x07000000 a=\x07000000 b=\x07000000 filler=\xb00f0000...
0 | 8 | 743 | 0 | (0,8) | 10242 | id=\x02000000 a=\x03000000 b=\x02000000 filler=\xb00f0000...
1 | 1 | 697 | 0 | (1,1) | 2306 | id=\x08000000 a=\x08000000 b=\x08000000 filler=\xb00f0000...
1 | 2 | 740 | 741 | (1,3) | 9474 | id=\x01000000 a=\x02000000 b=\x01000000 filler=\xb00f0000...
1 | 3 | 741 | 742 | (1,4) | 9474 | id=\x01000000 a=\x03000000 b=\x01000000 filler=\xb00f0000...
1 | 4 | 742 | 0 | (1,4) | 10498 | id=\x01000000 a=\x04000000 b=\x01000000 filler=\xb00f0000...
(11 rows)
The two versions of id=2 (\x02000000) are on the same page. This page is now full again, and another update, on id =3 (\ x03000000), will need to insert its new version on another page:
postgres=# update mvcc_demo
set a=a+1
where id=3
;
UPDATE 1
postgres=# execute show_tuples(0,1)
;
page | lp | t_xmin | t_xmax | t_ctid | t_infomask | regexp_replace
------+----+--------+--------+--------+------------+--------------------------------------------------------------
0 | 2 | 697 | 743 | (0,8) | 1282 | id=\x02000000 a=\x02000000 b=\x02000000 filler=\xb00f0000...
0 | 3 | 697 | 744 | (1,5) | 258 | id=\x03000000 a=\x03000000 b=\x03000000 filler=\xb00f0000...
0 | 4 | 697 | 0 | (0,4) | 2306 | id=\x04000000 a=\x04000000 b=\x04000000 filler=\xb00f0000...
0 | 5 | 697 | 0 | (0,5) | 2306 | id=\x05000000 a=\x05000000 b=\x05000000 filler=\xb00f0000...
0 | 6 | 697 | 0 | (0,6) | 2306 | id=\x06000000 a=\x06000000 b=\x06000000 filler=\xb00f0000...
0 | 7 | 697 | 0 | (0,7) | 2306 | id=\x07000000 a=\x07000000 b=\x07000000 filler=\xb00f0000...
0 | 8 | 743 | 0 | (0,8) | 10498 | id=\x02000000 a=\x03000000 b=\x02000000 filler=\xb00f0000...
1 | 1 | 697 | 0 | (1,1) | 2306 | id=\x08000000 a=\x08000000 b=\x08000000 filler=\xb00f0000...
1 | 2 | 740 | 741 | (1,3) | 9474 | id=\x01000000 a=\x02000000 b=\x01000000 filler=\xb00f0000...
1 | 3 | 741 | 742 | (1,4) | 9474 | id=\x01000000 a=\x03000000 b=\x01000000 filler=\xb00f0000...
1 | 4 | 742 | 0 | (1,4) | 10498 | id=\x01000000 a=\x04000000 b=\x01000000 filler=\xb00f0000...
1 | 5 | 744 | 0 | (1,5) | 10242 | id=\x03000000 a=\x04000000 b=\x03000000 filler=\xb00f0000...
(12 rows)
This is about storage amplification. PostgreSQL uses more space than undo-based MVCC because it copies the whole row. Undo-based MVCC databases typically reconstruct an older row version from smaller undo information during visibility checks. PostgreSQL can evaluate visibility directly against heap tuples and transaction status information rather than reconstructing an earlier page image from undo records.
For now, this tells us nothing about read amplification. Let's run a SELECT and count the number of pages visited.
Sequential scans do not follow CTID chains
Let's run a simple table scan:
explain (analyze, buffers, wal, costs off, summary off)
select ctid, xmin, xmax, *
from mvcc_demo;
QUERY PLAN
--------------------------------------------------------------------
Seq Scan on mvcc_demo (actual time=0.032..0.038 rows=8.00 loops=1)
Buffers: shared hit=3 dirtied=2
WAL: records=3 fpi=2 bytes=12736 fpi bytes=12580
Planning:
Buffers: shared hit=19
(5 rows)
QUERY PLAN
--------------------------------------------------------------------
Seq Scan on mvcc_demo (actual time=0.015..0.018 rows=8.00 loops=1)
Buffers: shared hit=2
(2 rows)
The first read did some cleanup, and this opportunistic pruning generated some WAL records, but the next sequential scans read each page only once. With a Seq Scan, the versions invisible to the read snapshot are ignored without following any chain.
A sequential scan iterates through line pointers on the page and checks visibility (HeapTupleSatisfiesVisibility, HeapTupleSatisfiesMVCC, or the batched HeapTupleSatisfiesMVCCBatch in heapam.c). Old/dead versions are simply other heap tuples on the page (or other pages) that fail the visibility check and get skipped. The scan doesn't have to build another version of the page or follow a chain of versions, and is as simple as:
- tuple visible? -> return it and continue on the same page
- tuple invisible? -> skip it and continue on the same page
The same applies to the HOT updates chain: Sequential scans don't need to pay attention to HOT links because they scan every line pointer on the page anyway. The cost is driven primarily by pages read, not by versions count, so there's no version-chain read amplification.
Reads perform cleanup without VACUUM
I executed show_tuples(0,1) to observe that after the SELECT, some old versions were cleaned, and some infomasks changed:
page | lp | t_xmin | t_xmax | t_ctid | regexp_replace | t_infomask | t_infomask2
------+----+--------+--------+--------+--------------------------------------------------------------+------------+-------------
0 | 4 | 697 | 0 | (0,4) | id=\x04000000 a=\x04000000 b=\x04000000 filler=\xb00f0000... | 2306 | 4
0 | 5 | 697 | 0 | (0,5) | id=\x05000000 a=\x05000000 b=\x05000000 filler=\xb00f0000... | 2306 | 4
0 | 6 | 697 | 0 | (0,6) | id=\x06000000 a=\x06000000 b=\x06000000 filler=\xb00f0000... | 2306 | 4
0 | 7 | 697 | 0 | (0,7) | id=\x07000000 a=\x07000000 b=\x07000000 filler=\xb00f0000... | 2306 | 4
0 | 8 | 743 | 0 | (0,8) | id=\x02000000 a=\x03000000 b=\x02000000 filler=\xb00f0000... | 10498 | 4
1 | 1 | 697 | 0 | (1,1) | id=\x08000000 a=\x08000000 b=\x08000000 filler=\xb00f0000... | 2306 | 4
1 | 2 | 740 | 741 | (1,3) | id=\x01000000 a=\x02000000 b=\x01000000 filler=\xb00f0000... | 9474 | 4
1 | 3 | 741 | 742 | (1,4) | id=\x01000000 a=\x03000000 b=\x01000000 filler=\xb00f0000... | 9474 | 4
1 | 4 | 742 | 0 | (1,4) | id=\x01000000 a=\x04000000 b=\x01000000 filler=\xb00f0000... | 10498 | 4
1 | 5 | 744 | 0 | (1,5) | id=\x03000000 a=\x04000000 b=\x03000000 filler=\xb00f0000... | 10498 | 4
(10 rows)
I displayed t_infomask2 to confirm that these updates are not HOT updates even when successor versions happen to be stored on the same page. The low value 4 corresponds only to the number of attributes in the tuple. None of the HOT-related flags (HEAP_HOT_UPDATED or HEAP_ONLY_TUPLE) are present.
No VACUUM was executed. The SELECT itself performed maintenance. The pruning operation is opportunistic:
- only if the page looks worth pruning
- only if a cleanup lock can be obtained immediately
- only if tuples appear globally removable
Ordinary reads can do lots of cleanup before vacuum:
- set hint bits / discover committed or aborted transactions. Except when checksums are enabled, this generates no WAL by default, since wal_log_hints=off)
- prune dead tuples on the page — whether part of a HOT chain or a standalone dead tuple — which includes redirecting the root line pointer (LP_REDIRECT) past removed intermediate members, or marking a fully-dead tuple/chain LP_DEAD/LP_UNUSED. This is WAL-logged regardless of wal_log_hints.
VACUUM is the primary cleanup mechanism, but readers continuously perform lightweight housekeeping. PostgreSQL isn't the only database that does cleanup on read and generates WAL for it, because visibility changes after COMMIT, and long transactions cannot revisit all updated pages during a commit.
Index scans must check visibility from heap at least once
The table is too small for the planner to choose an index scan, but I force it with pg_plan_advice (introduced in PostgreSQL 19 - I'm using the beta version while writing this article):
load 'pg_plan_advice';
set pg_plan_advice.advice =
'INDEX_SCAN(mvcc_demo mvcc_demo_a_idx)
NO_GATHER(mvcc_demo)';
I query for the value a=1 \x01000000 which has no rows after my updates:
postgres=# explain (analyze, buffers, wal, costs off, summary off)
select ctid, xmin, xmax, * from mvcc_demo where a=1
;
QUERY PLAN
--------------------------------------------------------------------------------------------
Index Scan using mvcc_demo_a_idx on mvcc_demo (actual time=0.011..0.012 rows=0.00 loops=1)
Index Cond: (a = 1)
Index Searches: 1
Buffers: shared hit=3 dirtied=1
WAL: records=1 fpi=1 bytes=349 fpi bytes=300
Planning:
Buffers: shared hit=6
Supplied Plan Advice:
INDEX_SCAN(mvcc_demo mvcc_demo_a_idx) /* matched */
NO_GATHER(mvcc_demo) /* matched */
(10 rows)
The index scan has read three pages (Buffers: shared hit=3). The index is small and fits in a single page:
postgres=# select *
from bt_page_items('mvcc_demo_a_idx', 1)
;
itemoffset | ctid | itemlen | nulls | vars | data | dead | htid | tids
------------+-------+---------+-------+------+-------------------------+------+-------+------
1 | (0,1) | 16 | f | f | 01 00 00 00 00 00 00 00 | t | (0,1) |
2 | (0,2) | 16 | f | f | 02 00 00 00 00 00 00 00 | f | (0,2) |
3 | (1,2) | 16 | f | f | 02 00 00 00 00 00 00 00 | f | (1,2) |
4 | (0,3) | 16 | f | f | 03 00 00 00 00 00 00 00 | f | (0,3) |
5 | (0,8) | 16 | f | f | 03 00 00 00 00 00 00 00 | f | (0,8) |
6 | (1,3) | 16 | f | f | 03 00 00 00 00 00 00 00 | f | (1,3) |
7 | (0,4) | 16 | f | f | 04 00 00 00 00 00 00 00 | f | (0,4) |
8 | (1,4) | 16 | f | f | 04 00 00 00 00 00 00 00 | f | (1,4) |
9 | (1,5) | 16 | f | f | 04 00 00 00 00 00 00 00 | f | (1,5) |
10 | (0,5) | 16 | f | f | 05 00 00 00 00 00 00 00 | f | (0,5) |
11 | (0,6) | 16 | f | f | 06 00 00 00 00 00 00 00 | f | (0,6) |
12 | (0,7) | 16 | f | f | 07 00 00 00 00 00 00 00 | f | (0,7) |
13 | (1,1) | 16 | f | f | 08 00 00 00 00 00 00 00 | f | (1,1) |
(13 rows)
Index entries for the previous versions exist, and the heap pages must be read to determine whether they are visible, so both pages have been read. However, some have been marked as "dead".
Again, even without VACUUM, the read did some housekeeping. I ran the same query, and it reads only one page (Buffers: shared hit=1):
postgres=# explain (analyze, buffers, wal, costs off, summary off)
select ctid, xmin, xmax, * from mvcc_demo where a=1
;
QUERY PLAN
--------------------------------------------------------------------------------------------
Index Scan using mvcc_demo_a_idx on mvcc_demo (actual time=0.013..0.014 rows=0.00 loops=1)
Index Cond: (a = 1)
Index Searches: 1
Buffers: shared hit=1
Supplied Plan Advice:
INDEX_SCAN(mvcc_demo mvcc_demo_a_idx) /* matched */
NO_GATHER(mvcc_demo) /* matched */
(7 rows)
When the first index scan finds that an index entry points to a heap tuple known to be dead to all transactions, it can mark the index item LP_DEAD. Future index scans can then skip the corresponding heap fetch.
When an UPDATE is not a HOT update, PostgreSQL inserts new index entries because index tuples store a heap TID (block, offset). The new tuple version has a different TID, so all indexes must point to the new location. This allows index scans to jump directly to the current row version rather than following a chain across pages.
Some databases skip index updates for unchanged columns but follow chains for row pieces stored elsewhere, like migrated rows, overflow pages, or undo data. PostgreSQL favors adding new index entries for each non-HOT update, accepting write overhead to prevent chain-following during reads and reduce read overhead.
This write amplification is reduced further when HOT updates are possible, since no new index entries are created.
A little read amplification can still happen on the first read that reaches a now-dead old tuple through a stale index entry — the visibility check requires a heap visit — but that entry is then marked LP_DEAD, so subsequent index scans skip the heap visit entirely.
Generating more dead versions to see how it scales
To increase the amount of dead data, I update all rows fifty times:
postgres=# update mvcc_demo set a=a+1
\watch c=50 i=0.01
Every row gets fifty new versions. Many obsolete heap tuples and index entries now exist. No VACUUM has run.
I look up rows whose current value is a=54 after fifty update cycles. The initial a=4 is now a=54. I query it two times:
postgres=# explain (analyze, buffers, wal, costs off, summary off)
select ctid, xmin, xmax, * from mvcc_demo where a=54
;
QUERY PLAN
--------------------------------------------------------------------------------------------
Index Scan using mvcc_demo_a_idx on mvcc_demo (actual time=0.052..0.071 rows=3.00 loops=1)
Index Cond: (a = 54)
Index Searches: 1
Buffers: shared hit=9 dirtied=5
WAL: records=5 fpi=5 bytes=28721 fpi bytes=28476
Supplied Plan Advice:
INDEX_SCAN(mvcc_demo mvcc_demo_a_idx) /* matched */
NO_GATHER(mvcc_demo) /* matched */
(8 rows)
postgres=# explain (analyze, buffers, wal, costs off, summary off)
select ctid, xmin, xmax, * from mvcc_demo where a=54
;
QUERY PLAN
--------------------------------------------------------------------------------------------
Index Scan using mvcc_demo_a_idx on mvcc_demo (actual time=0.020..0.039 rows=3.00 loops=1)
Index Cond: (a = 54)
Index Searches: 1
Buffers: shared hit=4
Supplied Plan Advice:
INDEX_SCAN(mvcc_demo mvcc_demo_a_idx) /* matched */
NO_GATHER(mvcc_demo) /* matched */
(7 rows)
The first read experienced some read amplification (shared hit=9 to get one row), but the next read had enough information in the index to avoid reading more than necessary (Buffers: shared hit=4). Notice that hint-bit updates and index dead-marking can generate WAL, especially when checksums or WAL logging of hints is enabled. In my case, the first lookup generated WAL: records=5.
If MVCC versions accumulated, as many think when they hear about bloat, we would have expected fifty versions for each id, but we observe much less:
postgres=# select id, a, b from mvcc_demo
;
postgres=# select id, a, b, 'x'||to_hex(a) a_hex from mvcc_demo
;
id | a | b | a_hex
----+----+---+-------
4 | 54 | 4 | x36
6 | 56 | 6 | x38
2 | 53 | 2 | x35
5 | 55 | 5 | x37
7 | 57 | 7 | x39
1 | 54 | 1 | x36
8 | 58 | 8 | x3a
3 | 54 | 3 | x36
(8 rows)
postgres=# execute show_tuples(0,17)
;
page | lp | t_xmin | t_xmax | t_ctid | regexp_replace | t_infomask | t_infomask2
------+----+--------+--------+---------+--------------------------------------------------------------+------------+-------------
0 | 57 | 790 | 791 | (0,58) | id=\x04000000 a=\x32000000 b=\x04000000 filler=\xb00f0000... | 9474 | 4
0 | 58 | 791 | 792 | (0,59) | id=\x04000000 a=\x33000000 b=\x04000000 filler=\xb00f0000... | 9474 | 4
0 | 59 | 792 | 793 | (0,60) | id=\x04000000 a=\x34000000 b=\x04000000 filler=\xb00f0000... | 9474 | 4
0 | 60 | 793 | 794 | (0,61) | id=\x04000000 a=\x35000000 b=\x04000000 filler=\xb00f0000... | 8450 | 4
0 | 61 | 794 | 0 | (0,61) | id=\x04000000 a=\x36000000 b=\x04000000 filler=\xb00f0000... | 10498 | 4
1 | 54 | 790 | 791 | (1,55) | id=\x06000000 a=\x34000000 b=\x06000000 filler=\xb00f0000... | 9474 | 4
1 | 55 | 791 | 792 | (1,56) | id=\x06000000 a=\x35000000 b=\x06000000 filler=\xb00f0000... | 9474 | 4
1 | 56 | 792 | 793 | (1,57) | id=\x06000000 a=\x36000000 b=\x06000000 filler=\xb00f0000... | 9474 | 4
1 | 57 | 793 | 794 | (1,58) | id=\x06000000 a=\x37000000 b=\x06000000 filler=\xb00f0000... | 8450 | 4
1 | 58 | 794 | 0 | (1,58) | id=\x06000000 a=\x38000000 b=\x06000000 filler=\xb00f0000... | 10242 | 4
2 | 53 | 790 | 791 | (2,54) | id=\x02000000 a=\x31000000 b=\x02000000 filler=\xb00f0000... | 9474 | 4
2 | 54 | 791 | 792 | (2,55) | id=\x02000000 a=\x32000000 b=\x02000000 filler=\xb00f0000... | 9474 | 4
2 | 55 | 792 | 793 | (2,56) | id=\x02000000 a=\x33000000 b=\x02000000 filler=\xb00f0000... | 9474 | 4
2 | 56 | 793 | 794 | (2,57) | id=\x02000000 a=\x34000000 b=\x02000000 filler=\xb00f0000... | 8450 | 4
2 | 57 | 794 | 0 | (2,57) | id=\x02000000 a=\x35000000 b=\x02000000 filler=\xb00f0000... | 10242 | 4
4 | 48 | 789 | 790 | (4,49) | id=\x05000000 a=\x32000000 b=\x05000000 filler=\xb00f0000... | 9474 | 4
4 | 49 | 790 | 791 | (4,50) | id=\x05000000 a=\x33000000 b=\x05000000 filler=\xb00f0000... | 9474 | 4
4 | 50 | 791 | 792 | (4,51) | id=\x05000000 a=\x34000000 b=\x05000000 filler=\xb00f0000... | 9474 | 4
4 | 51 | 792 | 793 | (4,52) | id=\x05000000 a=\x35000000 b=\x05000000 filler=\xb00f0000... | 9474 | 4
4 | 52 | 793 | 794 | (4,53) | id=\x05000000 a=\x36000000 b=\x05000000 filler=\xb00f0000... | 9474 | 4
4 | 53 | 794 | 0 | (4,53) | id=\x05000000 a=\x37000000 b=\x05000000 filler=\xb00f0000... | 10242 | 4
6 | 48 | 793 | 794 | (6,49) | id=\x07000000 a=\x38000000 b=\x07000000 filler=\xb00f0000... | 8450 | 4
6 | 49 | 794 | 0 | (6,49) | id=\x07000000 a=\x39000000 b=\x07000000 filler=\xb00f0000... | 10242 | 4
16 | 7 | 791 | 792 | (16,8) | id=\x01000000 a=\x33000000 b=\x01000000 filler=\xb00f0000... | 9474 | 4
16 | 8 | 792 | 793 | (16,9) | id=\x01000000 a=\x34000000 b=\x01000000 filler=\xb00f0000... | 9474 | 4
16 | 9 | 793 | 794 | (16,10) | id=\x01000000 a=\x35000000 b=\x01000000 filler=\xb00f0000... | 8450 | 4
16 | 10 | 794 | 0 | (16,10) | id=\x01000000 a=\x36000000 b=\x01000000 filler=\xb00f0000... | 10498 | 4
17 | 1 | 791 | 792 | (17,2) | id=\x03000000 a=\x33000000 b=\x03000000 filler=\xb00f0000... | 9474 | 4
17 | 2 | 792 | 793 | (17,4) | id=\x03000000 a=\x34000000 b=\x03000000 filler=\xb00f0000... | 9474 | 4
17 | 3 | 793 | 794 | (17,5) | id=\x08000000 a=\x39000000 b=\x08000000 filler=\xb00f0000... | 8450 | 4
17 | 4 | 793 | 794 | (17,6) | id=\x03000000 a=\x35000000 b=\x03000000 filler=\xb00f0000... | 8450 | 4
17 | 5 | 794 | 0 | (17,5) | id=\x08000000 a=\x3a000000 b=\x08000000 filler=\xb00f0000... | 10242 | 4
17 | 6 | 794 | 0 | (17,6) | id=\x03000000 a=\x36000000 b=\x03000000 filler=\xb00f0000... | 10498 | 4
(33 rows)
Only the most recent versions are stored here, and most of them are clustered on the same page. This greatly reduces the space, even before autovacuum runs, by pruning unnecessary data.
VACUUM
Note that I disabled VACUUM to show the lightweight housekeeping that happens during reads, but VACUUM remains important to reduce space amplification and avoid XID wraparound:
- reclaiming heap space
- returning space to the free space map
- removing dead index tuples
- freezing old transaction IDs
- advancing relfrozenxid
- preventing transaction ID wraparound
Finally, I run VACUUM to show what happens when auto-vacuum is enabled:
postgres=# vacuum mvcc_demo
;
VACUUM
postgres=# execute show_tuples(0,17)
;
page | lp | t_xmin | t_xmax | t_ctid | regexp_replace | t_infomask | t_infomask2
------+----+--------+--------+---------+--------------------------------------------------------------+------------+-------------
0 | 61 | 794 | 0 | (0,61) | id=\x04000000 a=\x36000000 b=\x04000000 filler=\xb00f0000... | 10498 | 4
1 | 58 | 794 | 0 | (1,58) | id=\x06000000 a=\x38000000 b=\x06000000 filler=\xb00f0000... | 11010 | 4
2 | 57 | 794 | 0 | (2,57) | id=\x02000000 a=\x35000000 b=\x02000000 filler=\xb00f0000... | 11010 | 4
4 | 53 | 794 | 0 | (4,53) | id=\x05000000 a=\x37000000 b=\x05000000 filler=\xb00f0000... | 11010 | 4
6 | 49 | 794 | 0 | (6,49) | id=\x07000000 a=\x39000000 b=\x07000000 filler=\xb00f0000... | 11010 | 4
16 | 10 | 794 | 0 | (16,10) | id=\x01000000 a=\x36000000 b=\x01000000 filler=\xb00f0000... | 11010 | 4
17 | 5 | 794 | 0 | (17,5) | id=\x08000000 a=\x3a000000 b=\x08000000 filler=\xb00f0000... | 11010 | 4
17 | 6 | 794 | 0 | (17,6) | id=\x03000000 a=\x36000000 b=\x03000000 filler=\xb00f0000... | 11010 | 4
(8 rows)
The table has 8 rows across 7 blocks, leaving ample free space. PostgreSQL will reuse this space. The free space was created during previous updates and remains allocated for similar future activity. If an unusual workload generated the free space, you might consider reclaiming it with VACUUM FULL (offline) or REPACK, and set the FILLFACTOR according to future activity.
When PostgreSQL does follow a chain
You may wonder why PostgreSQL stores the CTID of the next version, or may want to understand a specific situation where PostgreSQL has to follow the chain. The claims above are about ordinary MVCC-snapshot reads: plain SELECTs (seq scan, index scan, bitmap scan) never need to walk a version chain across pages. But PostgreSQL does follow CTID links across pages in a few specific situations, all related to write/lock conflict resolution rather than plain visibility checks:
- Concurrent UPDATE/DELETE/MERGE in read-committed semantics: If the row being updated or deleted was concurrently modified by another transaction, PostgreSQL must locate the latest version to re-check the query's predicate against it (EvalPlanQual). This walks the version chain, no matter how many pages it spans.
- SELECT ... FOR UPDATE/SHARE and other row locking: Locking a row whose key might be referenced by a future version requires locking every descendant in the chain, which can span multiple pages.
Here is an example:
-- start a READ COMMITTED transaction
begin;
-- UPDATE a row one thousand times
update mvcc_demo set a = a+1 where id=4
\watch c=1000 i=0.01
-- in another session, UPDATE the same row
\! psql -c "explain (analyze) update mvcc_demo set a = a+1 where id=4" & sleep 1
-- the concurrent session waits until we commit
commit;
Once my main session commits the thousands of updates, the concurrent update can continue. It has read the old version and followed the chain to the current version, showing a thousand of page reads:
-------------------------------------------------------------------------------------------------------
Update on mvcc_demo (cost=0.00..6.60 rows=0 width=0) (actual time=202.644..202.644 rows=0.00 loops=1)
Buffers: shared hit=1012
-> Seq Scan on mvcc_demo (cost=0.00..6.60 rows=1 width=10) (actual time=0.015..0.031 rows=1.00 loops=1)
Filter: (id = 4)
Rows Removed by Filter: 7
Buffers: shared hit=6
Planning:
Buffers: shared hit=30
Planning Time: 0.229 ms
Execution Time: 202.752 ms
These are inherent to correctly implementing snapshot isolation semantics under concurrent writes, not a consequence of accumulated bloat: the chain is only as long as the number of concurrent update attempts on that specific row, not the table's total historical version count.
Deletes
Unlike undo-based databases, an update doesn't need to remove the old index entry. It stays there until VACUUM and is marked LP_DEAD when encountered. The same applies to DELETE, which is like an UPDATE with no new version. In that sense, PostgreSQL sits between undo-based databases, like MySQL, which make all changes in place, and LSM-based databases, like YugabyteDB, which only append new versions or tombstones.
PostgreSQL physically follows what SQL does logically: before commit, the changes are not effective, just intents, where existing rows are tagged by the transaction that is modifying them. Only after commit, and when old versions are no longer needed, do changes become effective and cleanup can occur.
DELETE operations are costly in undo-based databases because they require copying old values, updating indexes, and restoring data if the statement fails or the system crashes. In PostgreSQL, however, DELETE simply records the delete transaction in the tuple header as a tombstone. If the statement or transaction rolls back, it doesn't need to physically restore row contents or index entries. Once the transaction commits and old versions are obsolete, reads can mark the entries with LP_DEAD, and vacuum cleanup removes the entry.
Here is an example where deleting one row generates only one WAL record, showing that PostgreSQL does not physically remove index entries during DELETE execution:
postgres=# explain (analyze, buffers, wal, costs off, summary off)
delete from mvcc_demo where a=58 -- 0x3a
;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Delete on mvcc_demo (actual time=0.082..0.082 rows=0.00 loops=1)
Buffers: shared hit=4 dirtied=2
WAL: records=1 fpi=2 bytes=10392 fpi bytes=10320
-> Bitmap Heap Scan on mvcc_demo (actual time=0.021..0.021 rows=1.00 loops=1)
Recheck Cond: (a = 58)
Heap Blocks: exact=1
Buffers: shared hit=2
-> Bitmap Index Scan on mvcc_demo_a_idx (actual time=0.012..0.012 rows=1.00 loops=1)
Index Cond: (a = 58)
Index Searches: 1
Buffers: shared hit=1
(11 rows)
postgres=# execute show_tuples(0,17)
;
page | lp | t_xmin | t_xmax | t_ctid | regexp_replace | t_infomask | t_infomask2
------+----+--------+--------+---------+--------------------------------------------------------------+------------+-------------
0 | 61 | 794 | 0 | (0,61) | id=\x04000000 a=\x36000000 b=\x04000000 filler=\xb00f0000... | 10498 | 4
1 | 58 | 794 | 0 | (1,58) | id=\x06000000 a=\x38000000 b=\x06000000 filler=\xb00f0000... | 11010 | 4
2 | 57 | 794 | 0 | (2,57) | id=\x02000000 a=\x35000000 b=\x02000000 filler=\xb00f0000... | 11010 | 4
4 | 53 | 794 | 0 | (4,53) | id=\x05000000 a=\x37000000 b=\x05000000 filler=\xb00f0000... | 11010 | 4
6 | 49 | 794 | 0 | (6,49) | id=\x07000000 a=\x39000000 b=\x07000000 filler=\xb00f0000... | 11010 | 4
16 | 10 | 794 | 0 | (16,10) | id=\x01000000 a=\x36000000 b=\x01000000 filler=\xb00f0000... | 11010 | 4
17 | 5 | 794 | 20923 | (17,5) | id=\x08000000 a=\x3a000000 b=\x08000000 filler=\xb00f0000... | 8962 | 8196
17 | 6 | 794 | 0 | (17,6) | id=\x03000000 a=\x36000000 b=\x03000000 filler=\xb00f0000... | 11010 | 4
(8 rows)
As the index has not been updated, a SELECT must go to the heap (Heap Fetches: 1) to check visibility, even if no columns are needed from it:
postgres=# explain (analyze, buffers, wal, costs off, summary off)
select from mvcc_demo where a=58
;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Index Only Scan using mvcc_demo_a_idx on mvcc_demo (actual time=0.036..0.036 rows=0.00 loops=1)
Index Cond: (a = 58)
Heap Fetches: 1
Index Searches: 1
Buffers: shared hit=3 dirtied=1
WAL: records=1 fpi=1 bytes=249 fpi bytes=200
(6 rows)
postgres=# explain (analyze, buffers, wal, costs off, summary off)
select from mvcc_demo where a=58
;
Another WAL record was generated here because the index scan marks the index item's line pointer LP_DEAD after confirming the referenced heap tuple is globally dead. Subsequent reads of this entry do not require reading the heap again (Heap Fetches: 0) if it's an index-only scan:
QUERY PLAN
-------------------------------------------------------------------------------------------------
Index Only Scan using mvcc_demo_a_idx on mvcc_demo (actual time=0.012..0.013 rows=0.00 loops=1)
Index Cond: (a = 58)
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=1
(5 rows)
In PostgreSQL, the real work of a DELETE is delayed until after COMMIT and the visibility horizon, when subsequent reads can see the deletion as durable and visible to all transactions. In the meantime, space is used, but there's no version chain to follow or undo to apply.
What this demo actually proves
This demonstration shows that PostgreSQL MVCC is designed around visibility checks and opportunistic maintenance, not repeated traversal of historical versions or change vectors. Dead tuple accumulation does not imply that every query must revisit every old version:
- Sequential scans visit every heap line pointer directly and skip invisible tuples, without following version chains.
- Ordinary reads opportunistically prune dead tuples (HOT or not) and set hint bits, even without VACUUM running.
- Index scans may need to follow a HOT chain within a single pinned buffer, but once a chain is found fully dead, the index entry can be marked LP_DEAD.
- Future index scans skip the heap visit entirely for entries already marked LP_DEAD.
- The chain is followed either within a page (by index scans, via HOT links) or across pages by DML/row-locking that must recheck predicates against a newly committed version.
- Deletes are logical until they are confirmed by COMMIT, in order to avoid unpredictable rollback or recovery time.
- VACUUM is still required for space reclamation and index cleanup, but not for every visibility decision.
The practical implication is that PostgreSQL, despite its reputation for "bloat," can handle significant MVCC history while maintaining relatively low read amplification. Some patterns can increase the effect of a particular MVCC implementation, but these are generally more complex than straightforward updates from short OLTP transactions. FILLFACTOR is important to consider to leave space for active versions.
This was tested on Azure Database for PostgreSQL Flexible Server 18 with the pageinspect and pgstattuple extensions enabled, and autovacuum disabled for the test table.
