On Wed, 14 Dec 2022 at 00:07, Peter Geoghegan <p...@bowt.ie> wrote: > > On Tue, Dec 13, 2022 at 9:16 AM Peter Geoghegan <p...@bowt.ie> wrote: > > That's not the only thing we care about, though. And to the extent we > > care about it, we mostly care about the consequences of either > > freezing or not freezing eagerly. Concentration of unfrozen pages in > > one particular table is a lot more of a concern than the same number > > of heap pages being spread out across multiple tables. Those tables > > can all be independently vacuumed, and come with their own > > relfrozenxid, that can be advanced independently, and are very likely > > to be frozen as part of a vacuum that needed to happen anyway. > > At the suggestion of Jeff, I wrote a Wiki page that shows motivating > examples for the patch series: > > https://wiki.postgresql.org/wiki/Freezing/skipping_strategies_patch:_motivating_examples > > These are all cases where VACUUM currently doesn't do the right thing > around freezing, in a way that is greatly ameliorated by the patch. > Perhaps this will help other hackers to understand the motivation > behind some of these mechanisms. There are plenty of details that only > make sense in the context of a certain kind of table, with certain > performance characteristics that the design is sensitive to, and seeks > to take advantage of in one way or another.
In this mentioned wiki page, section "Simple append-only", the following is written: > Our "transition from lazy to eager strategies" concludes with an autovacuum > that actually advanced relfrozenxid eagerly: >> automatic vacuum of table "regression.public.pgbench_history": index scans: 0 >> pages: 0 removed, 1078444 remain, 561143 scanned (52.03% of total) >> [...] >> frozen: 560841 pages from table (52.00% of total) had 88051825 tuples frozen >> [...] >> WAL usage: 1121683 records, 557662 full page images, 4632208091 bytes I think that this 'transition from lazy to eager' could benefit from a limit on how many all_visible blocks each autovacuum iteration can freeze. This first run of (auto)vacuum after the 8GB threshold seems to appear as a significant IO event (both in WAL and relation read/write traffic) with 50% of the table updated and WAL-logged. I think this should be limited to some degree, such as only freeze all_visible blocks up to 10% of the table's blocks in eager vacuum, so that the load is spread across a larger time frame and more VACUUM runs. Kind regards, Matthias van de Meent.
