Re: BTree index corruption (heap-tid-past-end, unexpected zero page, misplaced TID in posting list) recurring on high-churn tables, PG 18.3, data_checksums=on, no preceding crash

Hi Peter,

Sorry for the delay, it took me a while to catch this in the act. Raw page
images are attached. One thing first, because I think it is the useful part:
bt_index_check with heapallindexed does not catch this.

This morning production was failing with

ERROR: heap tid from index tuple (14066,1) points past end of heap page
line pointer array at offset 287 of block 7 in index
"mpaapp_cache_content_type_id_object_i_f6ecfefa_uniq"

Nine minutes after the last of those errors, bt_index_check(heapallindexed
=> true) passed on all five indexes of that table, and verify_heapam
returned zero rows. I had seen the same thing four weeks earlier on another
cluster and concluded it had healed itself, which was wrong. heapallindexed
checks heap to index, and what I have is the other direction, an index
tuple too many. So your precondition for sending page images never triggers
here.

The reason both tools are quiet is that the heap page is not empty, it is
zeroed:

SELECT * FROM page_header(get_raw_page('mpaapp_cache', 14066));
lsn | checksum | flags | lower | upper | special | pagesize | version
| prune_xid

-----+----------+-------+-------+-------+---------+----------+---------+-----------
0/0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
| 0

PageIsVerified only computes the checksum inside if (!PageIsNew(page)), so
nothing is ever logged despite data_checksums being on, and
PageGetMaxOffsetNumber returns 0 so verify_heapam never enters its tuple
loop. I found these pages only by reading every block with
pg_read_binary_file and comparing against 8192 zero bytes.

Exactly two blocks in that relation are zeroed, 14066 and 14282, out of
14750. Neighbouring blocks have valid headers, so the damage is 8192 bytes
wide and sits on the block boundary, same as the 2010 "unexpected
zero-pages" thread. Index entries still pointing into them:
pkey 11, the unique index 3 (all on index block 7, all dead=t), two others
2 each. At 11.5 rows per page that suggests page 14066 held 11 live rows.

It cannot clear itself: those entries are already LP_DEAD, and the only
path that would remove them, _bt_delete_or_dedup_one_page from
_bt_findinsertloc, dies in index_delete_check_htid first. So every insert
landing on that leaf page fails again, about 1100 statements in sixteen
hours, always the same heap tid.
My original guess was wrong, by the way. No posting list tuples are
involved and dedup does not seem related, the heap page is simply gone.
PostgreSQL 18.3 (Ubuntu 18.3-1.pgdg24.04+1), checksums on, full_page_writes
on, fsync on, wal_log_hints off, KVM/QEMU guest under Proxmox. No crash or
restart before any occurrence. Heavily churned table, 2.1M updates of which
2.0M HOT, 638k deletes, 156k live rows. I scanned the other 77 clusters on
this host and found no comparable case.
Attached, taken with the wiki procedure while the errors were ongoing:

dump_block_7_index.page index block 7 of the unique index
dump_block_14066.page heap block 14066, all zero
dump_block_14282.page heap block 14282, all zero

I have not reindexed, the cluster is still in this state, so tell me if
anything else is
worth pulling out before I repair it.

Regards,

# Alessandro Regolini

Il giorno gio 2 lug 2026 alle ore 20:33 Peter Geoghegan <pg(at)bowt(dot)ie> ha
scritto:

> On Thu, Jul 2, 2026 at 2:25 PM Alessandro Regolini
> <alessandro(at)regolini(dot)it> wrote:
> > What we can provide on the next occurrence
> > ------------------------------------------
> > We run a periodic amcheck sweep, so we usually catch a fresh case before
> > reindexing. Before REINDEX we can capture, for the affected block:
> > - contrib/pageinspect: bt_page_items() of the index block and
> > heap_page_items() / page_header() of the referenced heap block
> > - full bt_index_check / bt_index_parent_check output
> > - verify_heapam() of the underlying table
> > Please tell us which dumps would be most useful to root-cause this (btree
> > deduplication / VACUUM interaction is our current suspicion).
>
> Those all seem useful, but I doubt that bt_index_parent_check is going
> to add much over bt_index_check. However, you should be sure to run
> bt_index_check with heapallindexed=true, which will verify agreement
> between the index and the underlying table.
>
> If you see a heapallindexed=true failure, getting page images for the
> pointed-to heap page is useful (as well as the index page). I prefer a
> raw dump of the page itself over a textual representation. See the
> procedure here:
>
>
> https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD#contrib/pageinspect_page_dump
>
> --
> Peter Geoghegan
>