Possible Visibility Map corruption in supported branches?

On Fri, 26 Jun 2026 12:19:55 +0200
Jehan-Guillaume de Rorthais <jgdr@dalibo.com> wrote:

[…]
The only physical corruption I could imagine would be that the blocks in heap
or VM were somehow reverted to their previous state **after** the vacuum, but:

* the FS is ext4

Sorry, I reported something wrong: the FS is XFS, not ext4.

But anyway, I still think it's probably more a bug than a physical corruption
because of various evidences reported in my previous email.

Regards,

Hi all,

Operations are quite slow because of summer time. Our next steps are currently
on hold waiting for everyone to be available (including me). Below a quick
follow up on this subject.

Based on the mtime of the WAL archives, the customer provided us the 256
segments of the WAL 0000019E0000D171 where the "bad batch" occurred. The
content has been parsed by pg_waldump and loaded in a table. I can provide a
200MB dump of the table if needed or the raw waldump output.

I found that the 2547 blocks having a corrupted VM were all initialized during
an INSERT+INIT xlogrecord before having between 3 to 26 follow up INSERTs
records, all in their own xact being aborted.

-- sum-up of number record kind per blocks
=# WITH b AS (
SELECT blk, kind, count(*) AS num_in_block
FROM waldump
WHERE rel = 25667392
AND blk BETWEEN 3691936 AND 3694482
GROUP BY blk, kind
)
SELECT kind, num_in_block,
count(*),
sum(num_in_block*count(*)) OVER () AS total_evt
FROM b
GROUP BY kind, num_in_block;

kind num_in_block count total_evt
----------- ------------ ----- ---------
INSERT 3 1 68452
INSERT 5 1 68452
INSERT 6 2 68452
INSERT 9 1 68452
INSERT 10 2 68452
INSERT 13 1 68452
INSERT 15 2 68452
INSERT 18 1 68452
INSERT 19 1 68452
INSERT 25 108 68452
INSERT 26 2426 68452
INSERT+INIT 1 2547 68452
(12 rows)

-- number of xact related to these blocks and their status
=# WITH b AS (
SELECT kind, txn
FROM waldump
WHERE rel = 25667392
AND blk BETWEEN 3691936 AND 3694482
)
SELECT kind, count(*)
FROM waldump w
WHERE EXISTS (SELECT 1 FROM b WHERE b.txn = w.txn)
AND w.rmgr = 'Transaction'
GROUP BY kind;

kind count
----- -----
ABORT 68452

I asked for the 25667392_vm files for the 12, 13 and 14th of May to compare how
the visibility bits for these blocks evolved. You'll find them in attachment
with the script I use to parse them, should you want to check how results below
were produced.

$ stat -c "%n %s" 20260510-210002F_202605*/pg_data/base/235301/25667392_vm
20260510-210002F_20260512-210001I/pg_data/base/235301/25667392_vm 933888
20260510-210002F_20260513-210002I/pg_data/base/235301/25667392_vm 933888
20260510-210002F_20260514-210002I/pg_data/base/235301/25667392_vm 991232

VM files have 114 blocks but the last one who grows to 121. The heap blocks we
study are all referenced in the 113th block of the VM, the last one for the
12th and 13th of May (0 indexed).

The 12th and 13th have the exact same output:

$ ./pg_vm_parse.pl \
20260510-210002F_20260512-210001I/pg_data/base/235301/25667392_vm \
{3691936..3694482} | sha1sum
909beb7d8613be2cdf6d120cdbd686ee224111f5 -

$ ./pg_vm_parse.pl \
20260510-210002F_20260513-210002I/pg_data/base/235301/25667392_vm \
{3691936..3694482} | sha1sum
909beb7d8613be2cdf6d120cdbd686ee224111f5 -

$ ./pg_vm_parse.pl \
20260510-210002F_20260513-210002I/pg_data/base/235301/25667392_vm \
{3691936..3694482} | cut -d, -f 5-6 | sort | uniq -c
72 VM_ALL_VISIBLE: 0, VM_ALL_FROZEN: 0
13 VM_ALL_VISIBLE: 0, VM_ALL_FROZEN: 1
5 VM_ALL_VISIBLE: 1, VM_ALL_FROZEN: 0
2457 VM_ALL_VISIBLE: 1, VM_ALL_FROZEN: 1

I'm not sure how the VM references them the 12th if the xlogrecord shows they
were actually initiated the 13th around 19h. This tables receive **a lot** of
aborted xact, more than 99% in 0000019E0000D171* segments. So maybe it
frequently shrinks thanks to some autovacuum truncating the VM? This is
something we plan to analyze when we'll be able to gather all the pg_waldump
data between these two backups.

More surprisingly, these VM are exactly the same for these two days wheras the
"bad batch" filled these blocks with aborted INSERTs in the meantime. Maybe the
waldump of archives between 13th 19h and 13th 21h will shed some light on this,
as soon as we have them.

The 14th have a slight difference as visibility and/or frozen bits for 17
blocks changed to all possible bits combinations.

$ diff vm-20260513-210002.log vm-20260514-210002.log | grep '^>'

HEAP blk: 3691937, … VM_ALL_VISIBLE: 1, VM_ALL_FROZEN: 0
HEAP blk: 3691938, … VM_ALL_VISIBLE: 0, VM_ALL_FROZEN: 1
HEAP blk: 3691939, … VM_ALL_VISIBLE: 0, VM_ALL_FROZEN: 1
HEAP blk: 3691940, … VM_ALL_VISIBLE: 1, VM_ALL_FROZEN: 0
HEAP blk: 3691941, … VM_ALL_VISIBLE: 0, VM_ALL_FROZEN: 0
HEAP blk: 3691942, … VM_ALL_VISIBLE: 1, VM_ALL_FROZEN: 0
HEAP blk: 3691953, … VM_ALL_VISIBLE: 0, VM_ALL_FROZEN: 0
HEAP blk: 3691954, … VM_ALL_VISIBLE: 0, VM_ALL_FROZEN: 1
HEAP blk: 3691955, … VM_ALL_VISIBLE: 0, VM_ALL_FROZEN: 0
HEAP blk: 3691956, … VM_ALL_VISIBLE: 0, VM_ALL_FROZEN: 0
HEAP blk: 3691957, … VM_ALL_VISIBLE: 1, VM_ALL_FROZEN: 1
HEAP blk: 3691958, … VM_ALL_VISIBLE: 0, VM_ALL_FROZEN: 1
HEAP blk: 3691960, … VM_ALL_VISIBLE: 0, VM_ALL_FROZEN: 1
HEAP blk: 3691961, … VM_ALL_VISIBLE: 1, VM_ALL_FROZEN: 1
HEAP blk: 3691962, … VM_ALL_VISIBLE: 1, VM_ALL_FROZEN: 0
HEAP blk: 3691964, … VM_ALL_VISIBLE: 0, VM_ALL_FROZEN: 0
HEAP blk: 3691966, … VM_ALL_VISIBLE: 0, VM_ALL_FROZEN: 1

I'm not sure when/how some visibility bits could flips for some of these blocks
considering in june the HEAP blocks were still having the aborted lines
inserted by the bad batch the 13th of May around 19h.

I really hope we'll be able to find what happen to these blocks in other
pg_waldump extractions between these backups. The snapshot of the backup
repository we restored covers archived WAL between May 03rd 21:30 and May 16th
23:00. Two full backups on sundays and daily incrementals in between.

Stay tuned, but again, any ideas, scenario, information to gather are welcome!

Thank you for reading me down to here!

Hi,

On 2026-06-26 12:19:55 +0200, Jehan-Guillaume de Rorthais wrote:

The cluster is running the unsupported 13.23 without checksum enabled, but I
suspect they would not have helped anyway.

I'm reaching pgsql-hackers because last minor version is from November 2025 and
unless I'm wrong, no bug related to VM has been fixed in current supported
releases since then, so they might be subject to the same bug as well (if this
is a bug). The v19 might not be subject to this though, as Melanie Plageman
refactored some code around VM there.

There is:

2025-12-16 e4b1986b989 WAL-log inplace update before revealing it to other sessions.

Which could be related. You say there was no crash though in the relevant
timeframe, so maybe the change does not turn out to be relevant.

A few questions to look into:

- You seem to have concluded that the corruption is in the VM, rather than the
heap. Why? You say that is improbable, that's not my experience. I've seen
this kind of thing due to both postgres bugs, OS bugs and storage system
issues.

- How large is the relation, what range of blocks was affected? If it's the
tail of the relation that was affected autovacuum truncations would be where
I'd start looking.

I think it may be the tail, based on :

INFO: "<TABLE_NAME>": found 524506 removable, 10300212 nonremovable row versions in 489069 out of 3919859 pages

and

All theses rows came from the blocks 3691935 to 3694483 (about 20MB).

- I'd check the WAL that you collected (as described further down in the
thread) for truncations of the relation. I'd also check if one of the
affected pages has multiple +INIT records.

- Are there any vacuum related WAL records for the affected heap blocks? If
e.g. the relevant rows were removed as part of a VACUUM, this would likely
be evidence of the write somehow having gotten lost.

I'd probably pick one of the affected pages and look for all WAL records
referencing it. That often can tell you most of what happened.

One interesting thing to look at is whether there are WAL records for the
affected page with a later LSN than what you saw when the first vacuum
failed.

- Do you have base backups between the time the rows were inserted and the
time the problem first became apparent? I guess so, based on your later
mail.

I'd check a few of the base backups for
a) the size of the heap relation
b) the contents of one of the affected blocks

- Does this system have longrunning transactions? I'm basically wondering if
it is possible for the nightly VACFREEZE to have encountered the problematic
rows without having been able to remove them.

- Does the system use pg_repack/reorg or such? I've seen those cause all kinds
of corruption.

- What is the xid epoch? Best post all of pg_controldata.

- Have there been any crashes, failovers or such in the past? Sometimes older
corruption can manifest later (e.g. if you have an incorrectly initialized
PITR you can have "unconnected" segments beyond the current end of the
relation that "appear" once the relation grows sufficiently large)

- I'd check if you can figure out when the clog was truncated from the WAL,
that would provide a bound to which range of the WAL you need to be looking
at.

- Were there any graceful restarts of the system?

- What kernel version, distro and what kind of storage? As hinted above, it's
not unheard of for storage / filesystem bugs to lead to lost writes or lost
truncations (if a truncation fails / isn't permanent, it can have the effect
of basically making older data "reappear")

Greetings,

Andres Freund

Hi Andres,

On Fri, 17 Jul 2026 17:15:05 -0400
Andres Freund <andres@anarazel.de> wrote:

On 2026-06-26 12:19:55 +0200, Jehan-Guillaume de Rorthais wrote:

The cluster is running the unsupported 13.23 without checksum enabled, but I
suspect they would not have helped anyway.

I'm reaching pgsql-hackers because last minor version is from November 2025
and unless I'm wrong, no bug related to VM has been fixed in current
supported releases since then, so they might be subject to the same bug as
well (if this is a bug). The v19 might not be subject to this though, as
Melanie Plageman refactored some code around VM there.

There is:

2025-12-16 e4b1986b989 WAL-log inplace update before revealing it to other
sessions.

Which could be related. You say there was no crash though in the relevant
timeframe, so maybe the change does not turn out to be relevant.

No crash indeed. Also, there was only INSERTs on INITed blocks, not a single
inplace UPDATEs involved.

A few questions to look into:

- You seem to have concluded that the corruption is in the VM, rather than the
heap. Why? You say that is improbable, that's not my experience.

This looked improbable to me because I experienced various corruptions from
various places in the past, but I don't remember having heard of some data
reappearance. This doesn't mean it can not happen, just that it looks
improbable to me.

I've seen this kind of thing due to both postgres bugs, OS bugs and storage
system issues.

Andrey Borodin point me offline the commit 38c579b08988. And now with your
feedback, this gives more weight to the data resurrection scenario.

- How large is the relation, what range of blocks was affected?

I'll ask how large is the relation on disk from the previous daily backup.

If it's the tail of the relation that was affected autovacuum truncations
would be where I'd start looking.

I think it may be the tail, based on :

INFO: "<TABLE_NAME>": found 524506 removable, 10300212 nonremovable row
versions in 489069 out of 3919859 pages

and

All theses rows came from the blocks 3691935 to 3694483 (about 20MB).

Also, they are all in the last block of the VM, but I'm not 100% sure yet they
are the actual tail, this is not enough.

For the sake of my understanding: they all have one INSERT+INIT record, but
this only means the blocks were empty before this INSERT, right? INSERT+INIT is
not enough to tell if such a block came from anywhere in the relation or from
the tail, am I correct?

As soon as I can gather the waldump output starting from previous backup, this
and the size of the relation should be enough to understand if the blocks were
the tail of the relation during these INSERT+INIT.

- I'd check the WAL that you collected (as described further down in the
thread) for truncations of the relation.

Not a single 'TRUNCATE' event in the WAL segments I collected, but keep in mind
they span from 18:35:26 to 19:50:39, May 13th.

I'd also check if one of the affected pages has multiple +INIT records.

Not in the the WAL I collected.

- Are there any vacuum related WAL records for the affected heap blocks? If
e.g. the relevant rows were removed as part of a VACUUM, this would likely
be evidence of the write somehow having gotten lost.

There's some CLEAN records, but not on this relation.

I'd probably pick one of the affected pages and look for all WAL records
referencing it. That often can tell you most of what happened.

That's what I planed to do as soon as I have the complete waldump story from
May 12th backup up to May 13th around 19h02.

One interesting thing to look at is whether there are WAL records for the
affected page with a later LSN than what you saw when the first vacuum
failed.

Will check.

- Do you have base backups between the time the rows were inserted and the
time the problem first became apparent? I guess so, based on your later
mail.

The customer has some snapshots of the pgbackrest repository, but I'm not sure
of their retention policy. They restored the snapshot from May 15th, covering
backups from May 3rd to the 15th. The repository itself keeps two Full
backups taken on Sunday, plus daily increments. I'm not sure yet if we can
restore up to June 3rd. Unfortunately, due to summer holidays, we'll not be
able to know before August 3rd.

I'd check a few of the base backups for
a) the size of the heap relation
b) the contents of one of the affected blocks

- Does this system have longrunning transactions? I'm basically wondering if
it is possible for the nightly VACFREEZE to have encountered the problematic
rows without having been able to remove them.

I'll ask.

- Does the system use pg_repack/reorg or such? I've seen those cause all kinds
of corruption.

As far as I know, no. But I might be wrong, I'll double check with them.

- What is the xid epoch? Best post all of pg_controldata.

Will do.

- Have there been any crashes, failovers or such in the past? Sometimes older
corruption can manifest later (e.g. if you have an incorrectly initialized
PITR you can have "unconnected" segments beyond the current end of the
relation that "appear" once the relation grows sufficiently large)

The servers used to be rebooted every Sunday, after automatic updates. But they
stopped auto-rebooting since March 1st.

The last Patroni history event shows a leader change the 2026-03-28T22:41:28.
But we should remember this table has been created the 21st of April, 3 weeks
later.

- I'd check if you can figure out when the clog was truncated from the WAL,
that would provide a bound to which range of the WAL you need to be looking
at.

Let's hope we can gather enough WAL to find this bound.

- Were there any graceful restarts of the system?

Weekly, up to March 1st. None since then, based on Patroni history.

- What kernel version, distro and what kind of storage? As hinted above, it's
not unheard of for storage / filesystem bugs to lead to lost writes or lost
truncations (if a truncation fails / isn't permanent, it can have the effect
of basically making older data "reappear")

I'll ask for more details about the exact versions of OS and kernel (RHEL 8) and
about the storage backend itself.

I'll keep the list inform as soon as I can get more data/informations.

Thank you for all these ideas and places to dig!