Re: Overhauling "Routine Vacuuming" docs, particularly its handling of freezing

Hi,

On Mon, Apr 24, 2023 at 2:58 PM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:

> My work on page-level freezing for PostgreSQL 16 has some remaining
> loose ends to tie up with the documentation. The "Routine Vacuuming"
> section of the docs has no mention of page-level freezing. It also
> doesn't mention the FPI optimization added by commit 1de58df4. This
> isn't a small thing to leave out; I fully expect that the FPI
> optimization will very significantly alter when and how VACUUM
> freezes. The cadence will look quite a lot different.
>
> It seemed almost impossible to fit in discussion of page-level
> freezing to the existing structure. In part this is because the
> existing documentation emphasizes the worst case scenario, rather than
> talking about freezing as a maintenance task that affects physical
> heap pages in roughly the same way as pruning does. There isn't a
> clean separation of things that would allow me to just add a paragraph
> about the FPI thing.
>
> Obviously it's important that the system never enters xidStopLimit
> mode -- not being able to allocate new XIDs is a huge problem. But it
> seems unhelpful to define that as the only goal of freezing, or even
> the main goal. To me this seems similar to defining the goal of
> cleaning up bloat as avoiding completely running out of disk space;
> while it may be "the single most important thing" in some general
> sense, it isn't all that important in most individual cases. There are
> many very bad things that will happen before that extreme worst case
> is hit, which are far more likely to be the real source of pain.
>
> There are also very big structural problems with "Routine Vacuuming",
> that I also propose to do something about. Honestly, it's a huge mess
> at this point. It's nobody's fault in particular; there has been
> accretion after accretion added, over many years. It is time to
> finally bite the bullet and do some serious restructuring. I'm hoping
> that I don't get too much push back on this, because it's already very
> difficult work.
>

Thanks for taking the time to do this. It is indeed difficult work. I'll
give my perspective as someone who has not read the vacuum code but have
learnt most of what I know about autovacuum / vacuuming by reading the
"Routine Vacuuming" page 10s of times.

>
> Attached patch series shows what I consider to be a much better
> overall structure. To make this convenient to take a quick look at, I
> also attach a prebuilt version of routine-vacuuming.html (not the only
> page that I've changed, but the most important set of changes by far).
>
> This initial version is still quite lacking in overall polish, but I
> believe that it gets the general structure right. That's what I'd like
> to get feedback on right now: can I get agreement with me about the
> general nature of the problem? Does this high level direction seem
> like the right one?
>

There are things I like about the changes you've proposed and some where I
feel that the previous section was easier to understand. I'll comment
inline on the summary below and will put in a few points about things I
think can be improved at the end.

>
> The following list is a summary of the major changes that I propose:
>
> 1. Restructures the order of items to match the actual processing
> order within VACUUM (and ANALYZE), rather than jumping from VACUUM to
> ANALYZE and then back to VACUUM.
>
> This flows a lot better, which helps with later items that deal with
> freezing/wraparound.
>

+1

>
> 2. Renamed "Preventing Transaction ID Wraparound Failures" to
> "Freezing to manage the transaction ID space". Now we talk about
> wraparound as a subtopic of freezing, not vice-versa. (This is a
> complete rewrite, as described by later items in this list).
>

+1 on this too. Freezing is a normal part of vacuuming and while the
aggressive vacuums are different, I think just talking about the worst case
scenario while referring to it is alarmist.

>
> 3. All of the stuff about modulo-2^32 arithmetic is moved to the
> storage chapter, where we describe the heap tuple header format.
>
> It seems crazy to me that the second sentence in our discussion of
> wraparound/freezing is still:
>
> "But since transaction IDs have limited size (32 bits) a cluster that
> runs for a long time (more than 4 billion transactions) would suffer
> transaction ID wraparound: the XID counter wraps around to zero, and
> all of a sudden transactions that were in the past appear to be in the
> future"
>
> Here we start the whole discussion of wraparound (a particularly
> delicate topic) by describing how VACUUM used to work 20 years ago,
> before the invention of freezing. That was the last time that a
> PostgreSQL cluster could run for 4 billion XIDs without freezing. The
> invariant is that we activate xidStopLimit mode protections to avoid a
> "distance" between any two unfrozen XIDs that exceeds about 2 billion
> XIDs. So why on earth are we talking about 4 billion XIDs? This is the
> most confusing, least useful way of describing freezing that I can
> think of.
>
> 4. No more separate section for MultiXactID freezing -- that's
> discussed as part of the discussion of page-level freezing.
>
> Page-level freezing takes place without regard to the trigger
> condition for freezing. So the new approach to freezing has a fixed
> idea of what it means to freeze a given page (what physical
> modifications it entails). This means that having a separate sect3
> subsection for MultiXactIds now makes no sense (if it ever did).
>
> 5. The top-level list of maintenance tasks has a new addition: "To
> truncate obsolescent transaction status information, when possible".
>
> It makes a lot of sense to talk about this as something that happens
> last (or last among those steps that take place during VACUUM). It's
> far less important than avoiding xidStopLimit outages, obviously
> (using some extra disk space is almost certainly the least of your
> worries when you're near to xidStopLimit). The current documentation
> seems to take precisely the opposite view, when it says the following:
>
> "The sole disadvantage of increasing autovacuum_freeze_max_age (and
> vacuum_freeze_table_age along with it) is that the pg_xact and
> pg_commit_ts subdirectories of the database cluster will take more
> space"
>
> This sentence is dangerously bad advice. It is precisely backwards. At
> the same time, we'd better say something about the need to truncate
> pg_xact/clog here. Besides all this, the new section for this is a far
> more accurate reflection of what's really going on: most individual
> VACUUMs (even most aggressive VACUUMs) won't ever truncate
> pg_xact/clog (or the other relevant SLRUs). Truncation only happens
> after a VACUUM that advances the relfrozenxid of the table which
> previously had the oldest relfrozenxid among all tables in the entire
> cluster -- so we need to talk about it as an issue with the high
> watermark storage for pg_xact.
>
> 6. Rename the whole "Routine Vacuuming" section to "Autovacuum
> Maintenance Tasks".
>
> This is what we should be emphasizing over manually run VACUUMs.
> Besides, the current title just seems wrong -- we're talking about
> ANALYZE just as much as VACUUM.
>

+1 on this. Talking about autovacuum as the default and how to get the most
out of it seems like the right way to go.

I read through the new version a couple times and here is some of my
feedback. I haven't yet reviewed individual patches or done a very detailed
comparison with the previous version.

1) While I agree that bundling VACUUM and VACUUM FULL is not the right way,
moving all VACUUM FULL references into tips and warnings also seems
excessive. I think it's probably best to just have a single paragraph which
talks about VACUUM FULL as I do think it should be mentioned in the
reclaiming disk space section.
2) I felt that the new section, "Freezing to manage the transaction ID
space" could be made simpler to understand. As an example, I understood
what the parameters (autovacuum_freeze_max_age, vacuum_freeze_table_age) do
and how they interact better in the previous version of the docs.
3) In the "VACUUMs aggressive strategy" section, we should first introduce
what an aggressive VACUUM is before going into when it's triggered, where
metadata is stored etc. It's only several paragraphs later that I get to
know what we are referring to as an "aggressive" autovacuum.
4) I think we should explicitly call out that seeing an anti-wraparound
VACUUM or "VACUUM table (to prevent wraparound)" is normal and that it's
just a VACUUM triggered due to the table having unfrozen rows with an XID
older than autovacuum_freeze_max_age. I've seen many users panicking on
seeing this and feeling that they are close to a wraparound. Also, we
should be more clear about how it's different from VACUUMs triggered due to
the scale factors (cancellation behavior, being triggered when autovacuum
is disabled etc.). I think you do some of this but given the panic around
transactionid wraparounds, being more clear about this is better.
5) Can we use a better name for the XidStopLimit mode? It seems like a very
implementation centric name. Maybe a better version of "Running out of the
XID space" or something like that?
6) In the XidStopLimit mode section, it would be good to explain briefly
why you could get to this scenario. It's not something which should happen
in a normal running system unless you have a long running transaction or
inactive replication slots or a badly configured system or something of
that sort. If you got to this point, other than running VACUUM to get out
of the situation, it's also important to figure out what got you there in
the first place as many VACUUMs should have attempted to advance the
relfrozenxid and failed.

There are a few other small things I noticed along the way but my goal was
to look at the overall structure. As we address some of these, I'm happy to
do more detailed review of individual patches.

Regards,
Samay

Thoughts?
>
> --
> Peter Geoghegan
>