Horton

Re: Removing more vacuumlazy.c special cases, relfrozenxid optimizations

Peter Geoghegan <pg@bowt.ie>

View thread View on postgr.es
From: Peter Geoghegan <pg@bowt.ie>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>, Andres Freund <andres@anarazel.de>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2022-01-20T16:45:07Z
Lists: pgsql-hackers 
On Thu, Jan 20, 2022 at 6:55 AM Robert Haas <robertmhaas@gmail.com> wrote:
> Great, I'm glad we agree on that much. I would be interested in
> hearing what other people think about this scenario.

Agreed.

> I'm just being honest here when I say that I can't see any huge
> reduction in risk. Nor a huge increase in risk. It just seems
> speculative to me. If I knew something about the system or the
> workload, then I could say what would likely work out best on that
> system, but in the abstract I neither know nor understand how it's
> possible to know.

I think that it's very hard to predict the timeline with a scenario
like this -- no question. But I often imagine idealized scenarios like
the one you brought up with cursors, with the intention of lowering
the overall exposure to problems to the extent that that's possible;
if it was obvious, we'd have fixed it by now already. I cannot think
of any reason why making FreezeLimit into what I've been calling a
backstop introduces any new risk, but I can think of ways in which it
avoids risk. We shouldn't be waiting indefinitely for something
totally outside our control or understanding, and so blocking all
freezing and other maintenance on the table, until it's provably
necessary.

More fundamentally, freezing should be thought of as an overhead of
storing tuples in heap blocks, as opposed to an overhead of
transactions (that allocate XIDs). Meaning that FreezeLimit becomes
almost an emergency thing, closely associated with aggressive
anti-wraparound VACUUMs.

> My gut feeling is that it's going to make very little difference
> either way. People who never release their cursors or locks or
> whatever are going to be sad either way, and people who usually do
> will be happy either way.

In a real world scenario, the rate at which XIDs are used could be
very low. Buying a few hundred million more XIDs until the pain begins
could amount to buying weeks or months for the user in practice. Plus
they have visibility into the issue, in that they can potentially see
exactly when they stopped being able to advance relfrozenxid by
looking at the autovacuum logs.

My thinking on vacuum_freeze_min_age has shifted very slightly. I now
think that I'll probably need to keep it around, just so things like
VACUUM FREEZE (which sets vacuum_freeze_min_age to 0 internally)
continue to work. So maybe its default should be changed to -1, which
is interpreted as "whatever autovacuum_freeze_max_age/2 is". But it
should still be greatly deemphasized in user docs.

--
Peter Geoghegan

Commits

  1. Have VACUUM warn on relfrozenxid "in the future".

  2. vacuumlazy.c: Further consolidate resource allocation.

  3. Generalize how VACUUM skips all-frozen pages.

  4. Set relfrozenxid to oldest extant XID seen by VACUUM.

  5. Doc: Add relfrozenxid Tip to XID wraparound section.

  6. vacuumlazy.c: document vistest and OldestXmin.

  7. Increase hash_mem_multiplier default to 2.0.

  8. Consolidate VACUUM xid cutoff logic.

  9. Add VACUUM instrumentation for scanned pages, relfrozenxid.

  10. Simplify lazy_scan_heap's handling of scanned pages.

  11. Try to stabilize reloptions test, again.

  12. Unify VACUUM VERBOSE and autovacuum logging.

  13. Fix possible HOT corruption when RECENTLY_DEAD changes to DEAD while pruning.

  14. pg_resetxlog: add option to set oldest xid & use by pg_upgrade

  15. Teach VACUUM to bypass unnecessary index vacuuming.

  16. Centralize horizon determination for temp tables, fixing bug due to skew.

  17. pg_surgery: Try to stabilize regression tests.

  18. Add "split after new tuple" nbtree optimization.

  19. Fix bugs in vacuum of shared rels, by keeping their relcache entries current.

  20. Avoid useless truncation attempts during VACUUM.

  21. Only skip pages marked as clean in the visibility map, if the last 32

  22. Fix recently-understood problems with handling of XID freezing, particularly