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!

Hi All, Hi Andres,

First, customer told us the backup repository snapshot we were working on was
lost. We only have the WAL files and the VM files left…

He has been able to keep the repo snapshot from June 10th, holding data and
archives down to May 31st. But we might not be able to keep it for a long time…

1. Why the error appeared June 4th

As I wrote upthread, the error about missing "pg_xact/0D44" appeared during
the nightly vacuum of June 4th 2AM. The pg_control files allows to explain
what happened:

20260531-210002F: Latest checkpoint's oldestXID: 3498165236
20260531-210002F_20260601-210002I: Latest checkpoint's oldestXID: 3498165236
20260531-210002F_20260602-210001I: Latest checkpoint's oldestXID: 3498165236
20260531-210002F_20260603-210001I: Latest checkpoint's oldestXID: 3596946949
20260531-210002F_20260604-210002I: Latest checkpoint's oldestXID: 3596946949
20260531-210002F_20260605-210001I: Latest checkpoint's oldestXID: 3596946949
20260531-210002F_20260606-210002I: Latest checkpoint's oldestXID: 3596946949

The horizon jump from 3,498,165,236 to 3,596,946,949 between June 2nd and
3rd, removing all segments from 0D08 à 0D65, including 0D44. An old rows
might have been frozen somewhere in the cluster, this doesn't seem
unexpected. The corruption probably occurred way before this time. We could
check the WAL between 2nd and 3rd of June, as long as the snapshot is kept,
if someone think of something fishy about this oldestXID jump.

2. WAL analysis between May 13th to May 15th

I've been able to collect more WALs covering the INSERTs/ABORT in these
blocks and what happened after.

The nightly vacuum of May 14th 2AM is interesting. I checked what happened to
500 consecutive blocks before and 500 consecutive blocks after the corruption
zone (3,691,936 -> 3,694,482). Blocks outside of the corruption zone has been
vacuumed and visibility bits sets, eg for one of them:

LSN Num Record type Archiving date
D171/7E94D590 1 INSERT+INIT May 13th 19h02
D171/7E94EBC0 to D171/7E954568 26 INSERT May 13th 19h02
D171/7E94EB68 to D171/7E9546F0 27 ABORT May 13th 19h02
D1A5/14E22C58 1 CLEAN remxid 0 May 14th 02h11
D1A6/6421D380 1 CLEAN remxid 0 FPW May 14th 02h14
D1A6/6421D440 1 VISIBLE cutoff xid 0 May 14th 02h14

Looking at bad blocks they all have been skipped by the vacuum. There is ONLY
the INSERT+INIT/INSERT/ABORT records for them.

Here's the sum up of the xlog records including the insert batch + nightly
vacuum, for the 500 consecutive blocks before and after the corruption zone:

# 500 before (3,691,436 to 3,691,935):
kind │ count
--------------+-------
INSERT+INIT │ 500
INSERT │ 12983
ABORT │ 13483
FPI_FOR_HINT │ 504
CLEAN │ 1000
VISIBLE │ 500

# 500 after (3,694,483 to 3,694,982):
kind │ count
--------------+-------
INSERT │ 12981
FPI_FOR_HINT │ 515
ABORT │ 13481
CLEAN │ 1000
VISIBLE │ 500
INSERT+INIT │ 500

For each block, there's one INSERT+INIT, about 27 INSERT and as many ABORT
rollbacking all of them. Each block has two CLEAN record and one VISIBLE.

Sum up of the same period for the 2547 consecutive corrupted blocks:

kind │ count
--------------+-------
INSERT | 65905
INSERT+INIT │ 2547
ABORT │ 68452
FPI_FOR_HINT │ 50

Well, no CLEAN, no VISIBLE there. I don't understand what would keep the
vacuum away specifically for these blocks and not the other surrounding ones…

So considering your question:

- 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.

It doesn't seems a vacuum record has been lost. We don't have the vacuum
activity from May 15th though.

Considering:

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.

No activity past the May 13th ~19h02 batch on these blocks. Note that I only
checked between May 13th 18h22 and May 15th 02h05.

I'd check a few of the base backups for
a) the size of the heap relation

Unfortunately, this will not be possible.

b) the contents of one of the affected blocks

Not possible either. Note that I still have the xlog records writting these
blocks and they are similar to what we observed and extracted from the table
mid-june.

3. Tail of the heap?

As written in intro, unfortunately we don't have the heap files anymore.
However, I've been able to find six blocks located after the corruption zone
with only INSERT records and no INSERT+INIT. One of them (3,787,952) is in
the same heap segment. So I suppose the corruption zone was not the tail of
the heap, neither the tail of a segment, despite the large amount of
INSERT+INIT found.

4. Write activity during maintenance

It seems the INSERT batch was still running during the vacuum of the table
(May 14th, between 02h00 and 02h15), with 167778 records inserted during this
maintenance (according to LSN).

I didn't dig this yet.

5. Storage setup & versions

The server is a VM managed by VMWare. Each ESX access 4TB datastores in a SAN
using Hitachi hardware as storage layer, using multipath (eight paths per
LUN). VMs are using 1TB VMDKs created in these datastores. LVM is used inside
the VMs.

OS: RHEL8, May 12th kernel version was kernel-4.18.0-553.111.1.el8_10.x86_64.

No known failures on the SAN or ESXs around May 12th.

6. pg_reorg or pg_repack

Using such tools is prohibited in production without asking internal DBA team
first. The DBA team confirmed with developers they never used them, neither
in scripts or manualy, but some are still on holidays.

However, it seems highly improbable these tools were used in production.

7. Other

There's still no TRUNCATE on this relation and none of the corrupted blocks
has more than one +INIT xlog record during the batch and VACUUM maintenance.

I keep digging 4.

Regards,

Hi Andres, Hi all,

We found an event in the cluster I somehow missed. The 6th of May, a sysadmin
operation moved the VM's storage to another datastore using svmotion. The
storages were moved without shutting down the VMs nor PostgreSQL themselves. In
this three node Patroni cluster, instances were all moved as standbys. Which
means a swichover occured. Let's call the nodes: n21, n22, n23.

Here is a sum up of the events:

0. Node n23 is the current primary (since March)
1. 2026-05-06 11h30: n21 storage moved using svmotion without shutting it down
2. 2026-05-06 22h30: switchover. n21 becomes primary, n23 a standby
3. 2026-05-07 11h30: n22 storage moved using svmotion without shutting it down
4. 2026-05-11 11h30: n23 storage moved using svmotion without shutting it down
5. 2026-05-13 19h00: a daily batch issues a lot of INSERT+INIT continuous
blocks, including the 2547 affected ones. Each block is filled with about 27
xacts inserting only one row and being **all** immediately aborted. There's
at least 500 blocks before and after the affected zone having the same exact
workload. Only inserted data differ. Xact id increase monotonically.
6. 2026-05-14 02h00: daily vacuum starts on the affected table. Among usual
other tasks, all blocks filled with only dead rows are "CLEAN remxid 0" and
set "VISIBLE cutoff xid 0". All? no. Not the 2547 affected ones. For some
reason, VACUUM ignored them.
7. between 2026-06-02 21h00 and 2026-06-03 21h00: an old row has been cleaned
somewhere, moving the xid horizon from 3,498,165,236 to 3,596,946,949 and
removing all pg_xact segments from 0D08 to 0D65
8. 2026-06-04 02h20: daily vacuum analyze raise the first error about missing
file "pg_xact/0D44". The xact status of the 2547 affected blocks were all in
this file.
9. 2026-06-12: the customer contact us about this issue
10. 2026-06-15: pg_check_frozen() exposed all the affected blocks

Note that step 8 was repeating every day until we fixed the production (see
first email of the thread). The error was always raised during the ANALYZE
phase. So it _seems_ (but I can't prove it) VACUUM never cleaned these pages at
all between May 13th and June 12th.

I can accept the n21 datastore hot move hadn't been really atomic, seeding
some incoherence here and there. We already discover a corruption after a
vmotion (not svmotion) with another customer. In short, standby n21 might been
promoted with a silent logical corruption. However, I can not be 100% sure
it would be the root cause of the problem here. Especially since I fail to
find what would be the corruption.

Indeed, the affected data have been written **after** this switchover. What
kind of corruption would have trigger this behavior? The Visibility Map should
have been updated during the writes… shouldn't they? But according to
pg_check_frozen(), it wasn't!

What could forbid the vacuum to clean this specific region for such a long time
if it's not the visibility map?

Looking at the affected data we collected in june, they all have flags:

HEAP_HASNULL, HEAP_HASVARWIDTH, HEAP_XMAX_INVALID

Some have an additional HEAP_XMIN_INVALID that could have been set when some
readings or a vacuum analyze land here. But nothing would keep vacuum away
from them...

Does this story ring any bell to someone?

I build some graphical map of the relation to visualize various actions on each
blocks, but "decent" png are about 5MB and I don't want to spam everyone on this
list with them. If you're interested, I can share them offlist.

Regards,