Horton
Menu

Re: eliminate xl_heap_visible to reduce WAL (and eventually set VM on-access)

Kirill Reshke <reshkekirill@gmail.com>

View thread
From: Kirill Reshke <reshkekirill@gmail.com>
To: Alexander Lakhin <exclusion@gmail.com>
Cc: Melanie Plageman <melanieplageman@gmail.com>, Andres Freund <andres@anarazel.de>, Andrey Borodin <x4mmm@yandex-team.ru>, Chao Li <li.evan.chao@gmail.com>, Xuneng Zhou <xunengzhou@gmail.com>, Robert Haas <robertmhaas@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Heikki Linnakangas <hlinnaka@iki.fi>
Date: 2026-01-29T13:39:07Z
Lists: pgsql-hackers 
On Thu, 29 Jan 2026 at 10:00, Alexander Lakhin <exclusion@gmail.com> wrote:
>
> Hello Melanie,
>
> 29.01.2026 01:16, Melanie Plageman wrote:
>
> Thanks for the review!
> I pushed v33 0001-0003 after incorporating your feedback.
>
>
> The buildfarm animal scorpion has detected an instability of the addition
> to pg_visibility from 21796c267 [1]:
>
> 80/82 postgresql:pg_visibility-running / pg_visibility-running/regress             ERROR             7.23s   exit status 1
>
> diff -U3 /home/bf/bf-build/scorpion/HEAD/pgsql/contrib/pg_visibility/expected/pg_visibility.out /home/bf/bf-build/scorpion/HEAD/pgsql.build/testrun/pg_visibility-running/regress/results/pg_visibility.out
> --- /home/bf/bf-build/scorpion/HEAD/pgsql/contrib/pg_visibility/expected/pg_visibility.out    2026-01-26 22:07:12.923378464 +0100
> +++ /home/bf/bf-build/scorpion/HEAD/pgsql.build/testrun/pg_visibility-running/regress/results/pg_visibility.out    2026-01-28 20:15:13.802517085 +0100
> @@ -213,7 +213,7 @@
>  select pg_visibility_map_summary('test_vac_unmodified_heap');
>   pg_visibility_map_summary
>  ---------------------------
> - (1,1)
> + (0,0)
>  (1 row)
>
>  -- the checkpoint cleans the buffer dirtied by freezing the sole tuple
> @@ -237,7 +237,7 @@
>          FROM page_header(get_raw_page('test_vac_unmodified_heap', 0));
>   ?column?
>  ----------
> - t
> + f
>  (1 row)
>
>  -- vacuum sets the VM
>
> I've managed to reproduce it locally with the attached and:
> echo "autovacuum_naptime = 1" > /tmp/temp.config
> TEMP_CONFIG=/tmp/temp.config make -s check -C contrib/pg_visibility/
> ...
> ok 85        - pg_visibility                              30 ms
> not ok 86    - pg_visibility                             165 ms
> ok 87        - pg_visibility                              36 ms
> ...
> # 1 of 100 tests failed.
>
> Could you please look at this?
>
> Probably you'll find [2] helpful.
>
> [1] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=scorpion&dt=2026-01-28%2019%3A07%3A32
> [2] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1c64d2fcb
>
> Best regards,
> Alexander


Thanks Alexander!
This is a good and detailed report, I was able to reproduce this.

I have added some logs to my copy of postgres with your patch and I
think problem causing this test to fail is this sequence:

1) Autovacuum starts, does its deeds, and acquiring xid = 118518
2) insert into test_vac_unmodified_heap values (1); executes and
commits with xid = 118519 (from my log)
3) vacuum freeze starts and computes cutoff xid = 118518, because
oldest xmin is 118518 from (1)

*and we cannot freeze tuple*


```

2026-01-29 13:27:44.559 UTC [133670] DEBUG:  CommitTransaction(1)
name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid:
118519/1/0 (used)
...
2026-01-29 13:27:44.559 UTC [133672] DEBUG:  CommitTransaction(1)
name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid:
118518/1/2
...
2026-01-29 13:27:44.560 UTC [133670] INFO:  finished vacuuming
"contrib_regression.public.test_vac_unmodified_heap": index scans: 0
        pages: 0 removed, 1 remain, 1 scanned (100.00% of total), 0
eagerly scanned
        tuples: 0 removed, 1 remain, 0 are dead but not yet removable
        removable cutoff: 118518, which was 2 XIDs old when operation ended
        new relfrozenxid: 118518, which is 1 XIDs ahead of previous value
        frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
        visibility map: 0 pages set all-visible, 0 pages set
all-frozen (0 were all-visible)
        index scan not needed: 0 pages from table (0.00% of total) had
0 dead item identifiers removed
        avg read rate: 0.000 MB/s, avg write rate: 54.253 MB/s
        buffer usage: 22 hits, 0 reads, 3 dirtied


```

I did not come up with a fix yet though.

-- 
Best regards,
Kirill Reshke

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Stabilize btree_gist test against on-access VM setting

  2. Stabilize plancache test against on-access VM setting

  3. Make local buffers pin limit more conservative

  4. Update FSM during prune/freeze replay even if freespace is zero

  5. Pass down information on table modification to scan nodes

  6. Thread flags through begin-scan APIs

  7. Remove PlannedStmt->resultRelations in favor of resultRelationRelids

  8. Make it cheap to check if a relation is modified by a query

  9. Keep newest live XID up-to-date even if page not all-visible

  10. Add pruning fast path for all-visible and all-frozen pages

  11. Detect and fix visibility map corruption in more cases

  12. Save vmbuffer in heap-specific scan descriptors for on-access pruning

  13. Avoid BufferGetPage() calls in heap_update()

  14. Initialize missing fields in CreateExecutorState()

  15. Prefix PruneState->all_{visible,frozen} with set_

  16. Add PageGetPruneXid() helper

  17. Move commonly used context into PruneState and simplify helpers

  18. Remove table_scan_analyze_next_tuple unneeded parameter OldestXmin

  19. Simplify visibility check in heap_page_would_be_all_visible()

  20. Rename LVRelState VM-related logging counters

  21. Fix flakiness in the pg_visibility VM-only vacuum test by using a temporary table.

  22. Refactor lazy_scan_prune() VM clear logic into helper

  23. Eliminate use of cached VM value in lazy_scan_prune()

  24. Combine visibilitymap_set() cases in lazy_scan_prune()

  25. Fix const qualification in prune_freeze_setup()

  26. Simplify vacuum visibility assertion

  27. Split heap_page_prune_and_freeze() into helpers

  28. Assert that cutoffs are provided if freezing will be attempted

  29. Split PruneFreezeParams initializers to one field per line

  30. Refactor heap_page_prune_and_freeze() parameters into a struct

  31. Make heap_page_is_all_visible independent of LVRelState

  32. Inline TransactionIdFollows/Precedes[OrEquals]()

  33. Add helper for freeze determination to heap_page_prune_and_freeze

  34. Bump XLOG_PAGE_MAGIC after xl_heap_prune change

  35. Correct prune WAL record opcode name in comment

  36. Add error codes when vacuum discovers VM corruption

  37. Remove unused xl_heap_prune member, reason

  38. Remove unneeded VM pin from VM replay

  39. Add assert and log message to visibilitymap_set

  40. Stamp HEAD as 19devel.

  41. Add error codes to some corruption log messages