public inbox for [email protected]
help / color / mirror / Atom feedBTree 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
2+ messages / 2 participants
[nested] [flat]
* 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
@ 2026-07-01 08:12 Alessandro Regolini <[email protected]>
2026-07-02 18:32 ` 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 Peter Geoghegan <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Alessandro Regolini @ 2026-07-01 08:12 UTC (permalink / raw)
To: [email protected]
PostgreSQL version:
PostgreSQL 18.3 (Ubuntu 18.3-1.pgdg24.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04.1) 13.3.0, 64-bit
OS: Ubuntu 24.04 (noble), running as a KVM/QEMU guest
data_checksums: on
shared_buffers: 128MB
btree deduplication: enabled (default)
Storage: ext4 (rw,relatime,errors=remount-ro) on a dedicated virtual disk
Relevant GUCs: fsync=on, full_page_writes=on, wal_level=replica,
autovacuum=on
*Summary*-------
Over ~4 weeks we hit recurring btree index corruption on multiple
independent
databases hosted on the same physical host (~80 PG 18 clusters, one DB per
cluster). Corruption concentrates exclusively on high-churn tables
(INSERT + DELETE + HOT update + frequent autovacuum: application cache and a
message-queue table). We observed three distinct signatures, all of them
btree-vs-heap desync:
1. ERROR: heap tid from index tuple (X,Y) points past end of heap page
line pointer array at offset NNN of block B in index "<idx>"
2. index "<idx>" contains unexpected zero page at block B
3. posting list contains misplaced TID in index "<idx>" (btree dedup)
Timeline (verbatim runtime + amcheck log lines, times CEST)
-----------------------------------------------------------
*DB "soave":* 2026-06-05 .. 2026-06-08 ERROR: heap tid from index tuple
points past end
of heap page line pointer array at offset 272 of block N in index
"django_webix_sender_messagerecipient_pkey"
-> emitted every 4h by a periodic query, then stopped on its own.
2026-06-30 09:35 / 09:46 bt_index_check(heapallindexed=>true) reported:
- index "qdc_magazzino_articolo_id_prev_a0195938"
contains unexpected zero page at block N
- index "django_webix_sender_messagerecipient_content_type_id_7552397e"
posting list contains misplaced TID
*DB "ermes":* 2026-06-24 .. 2026-06-30 ERROR: heap tid from index tuple
points past end
of heap page line pointer array at offset 248 of block N in index
"mpaapp_cache_content_type_id_object_i_f6ecfefa_uniq"
-> dozens of occurrences over the window.
Key facts
---------
- data_checksums=on and NO invalid-page / checksum-failure error was ever
logged. The corrupt pages therefore carry valid checksums but structurally
wrong content: they were written by PostgreSQL, this is not a torn write
or
a storage-layer bit rot detected by checksums.
- No crash, PANIC, OOM or immediate shutdown preceded any event. The
postmaster
was up continuously across each window. Host kernel journal shows zero
MCE /
ECC / OOM events in the last 90 days.
- REINDEX INDEX CONCURRENTLY fixed every case with zero data loss (the heap
was
always intact and fully readable), which points to an index-vs-heap desync
rather than heap damage.
- The affected indexes are the busiest tables (application cache and message
queue), constantly vacuumed. Corruption is intermittent and self-heals
after
VACUUM / REINDEX.
- The same signature family reproduced on independent databases with
identical
schema (same application deployed many times), which argues against random
hardware/memory corruption: a random flip would not preferentially target
the
same vacuum-heavy indexes with the same error text across separate
clusters.
- This is not a collation/glibc issue: the symptoms are physical structural
desync (heap-tid-past-end, zero page, misplaced TID), not wrong sort
order.
- fsync=on and full_page_writes=on, so the two most common self-inflicted
corruption causes are excluded.
*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).
# Alessandro Regolini
^ permalink raw reply [nested|flat] 2+ messages in thread
* 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
2026-07-01 08:12 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 Alessandro Regolini <[email protected]>
@ 2026-07-02 18:32 ` Peter Geoghegan <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Peter Geoghegan @ 2026-07-02 18:32 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
On Thu, Jul 2, 2026 at 2:25 PM Alessandro Regolini
<[email protected]> 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#...
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-07-02 18:32 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-07-01 08:12 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 Alessandro Regolini <[email protected]>
2026-07-02 18:32 ` Peter Geoghegan <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox