Horton
Menu

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

Andres Freund <andres@anarazel.de>

View thread
From: Andres Freund <andres@anarazel.de>
To: Alexander Lakhin <exclusion@gmail.com>
Cc: Melanie Plageman <melanieplageman@gmail.com>, Tomas Vondra <tomas@vondra.me>, David Rowley <dgrowleyml@gmail.com>, Kirill Reshke <reshkekirill@gmail.com>, Chao Li <li.evan.chao@gmail.com>, Andrey Borodin <x4mmm@yandex-team.ru>, Xuneng Zhou <xunengzhou@gmail.com>, Robert Haas <robertmhaas@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Heikki Linnakangas <hlinnaka@iki.fi>
Date: 2026-04-18T16:25:48Z
Lists: pgsql-hackers 
Hi,

On 2026-04-18 18:00:00 +0300, Alexander Lakhin wrote:
> Hello Melanie and Andres,
> 
> 03.04.2026 08:00, Alexander Lakhin wrote:
> > 
> > 31.03.2026 19:19, Melanie Plageman wrote:
> > > Thanks for the reply! I have committed the patches in this thread and
> > > marked the CF entry accordingly.
> > 
> > I've come across an interesting failure produced starting from 378a21618:
> > ...
> 
> I've discovered one more behaviour change introduced in 378a21618. I
> investigated a yesterday's skink failure [1]:
> # --- /home/bf/bf-build/skink-master/HEAD/pgsql/contrib/btree_gist/expected/enum.out 2025-06-23 20:17:56.295775456 +0200
> # +++ /home/bf/bf-build/skink-master/HEAD/pgsql.build/testrun/btree_gist/regress/results/enum.out
> 2026-04-17 22:35:37.212061309 +0200
> # @@ -83,12 +83,10 @@
> #
> #  EXPLAIN (COSTS OFF)
> #  SELECT count(*) FROM enumtmp WHERE a >= 'g'::rainbow;

Random: I wonder if the author if this intended this to be a temp table, based
on the name? That'd prevent any concurrent autovacuums/analyzes from changing
anything.


> # --- /home/vagrant/postgres/contrib/btree_gist/expected/enum.out 2026-04-18 11:41:17.224063241 +0000
> # +++ /home/vagrant/postgres/contrib/btree_gist/results/enum.out 2026-04-18 11:52:43.870049782 +0000
> # @@ -91,18 +91,16 @@
> #  where c.relname in ('enumtmp', 'enumidx');
> #   relname | relpages | reltuples | autovacuum_count | autoanalyze_count
> # ---------+----------+-----------+------------------+-------------------
> # - enumtmp |        3 |       595 |                0 |                 0
> # + enumtmp |        3 |       595 |                0 |                 1
> #   enumidx |        4 |       595 | |
> #  (2 rows)
> #
> #  EXPLAIN (COSTS OFF)
> #  SELECT count(*) FROM enumtmp WHERE a >= 'g'::rainbow;
> # -                  QUERY PLAN
> # ------------------------------------------------
> # +                   QUERY PLAN
> # +------------------------------------------------
> #   Aggregate
> # -   ->  Bitmap Heap Scan on enumtmp
> # -         Recheck Cond: (a >= 'g'::rainbow)
> # -         ->  Bitmap Index Scan on enumidx
> # -               Index Cond: (a >= 'g'::rainbow)
> # -(5 rows)
> # +   ->  Index Only Scan using enumidx on enumtmp
> # +         Index Cond: (a >= 'g'::rainbow)
> # +(3 rows)
> #
> not ok 48    - enum                                    10596 ms

The interesting column to show here would presumably be relallvisible.

What I assume is happening is that occasionally analyze now sees enough all
visible pages (due to on-access pruning marking the pages all visible) to
consider the index only scan worthwhile, whereas before that wasn't (or only
very rarely) happened.

Maybe I'm daft, but what would prevent this from happening before? The path
for it would be a bit more complicated, you'd have to have an autovacuum
instead of just an analyze - but that seems possible. It might require running
against a pre-existing install to be likely enough.


> It's not reproduced at 378a21618~1, though.
> 
> Could you please look if this can be fixed?

When you say fix, I assume you mean address the test instability, rather than
actual code changes?

Greetings,

Andres Freund

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