Re: Overhauling "Routine Vacuuming" docs, particularly its handling of freezing
lity." It feels like one connection you could make to the bullet list above is in this area and not mentioned. By freeing up space and reducing the number of pages that need to be read for satisfying a query, vacuum and recovering disk space (theoretically) improves query performance. Not 100% how to add it in context of these first two paragraphs. 4) Caution: "It may be a good idea to add monitoring to alert you about this." I hate to be pedantic about it, but I think we should spell out "this". Do we have a pointer in documentation to what kinds of things to monitor for? Am monitoring long-running transactions or some metric that shows me that VACUUM is being "held back"? I know what you mean, but it's not clear to me how to do the right thing in my environment here. 5) The plethora of tips/notes/warnings. As you and others have mentioned, as presented these really have no context for me. Individually they are good/helpful information, but it's really hard to make a connection to what I should "do" about it. It seems to me that this would be a good place to put a subsection which is something like, "A note about reclaiming disk space" or something. In my experience, most people hear about and end up using VACUUM FULL because things got out of control and they want to get into a better spot (I have been in that boat). I think with a small section that says, in essence, "hey, now that you understand why/how vacuum reclaims disk resources normally, if you're in a position where things aren't in a good state, this is what you need to know if you want to reclaim space from a really inefficient table" For me, at least, I think it would be easier to read/grok what you're sharing in these callouts. 6) One last missing piece that very well might be in another page not referenced (I obviously need to get the PG16 docs pulled and built locally so that I can have better overall reference. My apologies). In my experience, one of the biggest issues with the thresholds and recovering space is the idea of tuning individual tables, not just the entire database. 5/10/20% might be fine for most tables, but it's usually the really active ones that need the tuning, specifically lowering the thresholds. That doesn't come across to me in this section at all. Again, maybe I've missed something on another page and it's all good, but it felt worth calling out. Plus, it may provide an opportunity to bring in the threshold formulas again if they aren't referenced elsewhere (although they probably are). Hope that makes sense. ** Section 2.5.2: Freezing to manage... ** As stated above, the effort here overall is great IMO. I like the flow and reduction in alarmist tone for things like wraparound, etc. I understand more about freezing, aggressive and otherwise, than I did before. 7) That said, totally speaking as a non-contributor, this section is obviously very long for good reason. But, by the time I've gotten down to 25.2.2.3, my brain is a bit bewildered on where we've gotten to. That's more a comment on my capability to process it all, but I wonder if a slightly more explicit intro could help set the stage at least. "One side-effect of vacuum and transaction ID management at the row level is that PostgreSQL would normally need to inspect each row for every query to ensure it is visible to each requesting transaction. In order to reduce the need to read and inspect excessive amounts of data at query time or when normal vacuum maintenance kicks in, VACUUM has a second job called freezing, which accomplishes three goals: (attempting to tie in the three sections) * speeding up queries and vacuum operations by... * advancing the transaction ID space on generally static tables... * ensure there are always free transaction IDs available for normal operation... " Maybe totally worthless and too much, but something like that might set a reader up for just a bit more context. Then you could take most of what comes before "2.5.2.2.1 Aggressive Vacuum" as a subsection (would require a renumber below) with something like "2.5.2.2.1 Normal Freezing Activity" 8) Note "In PostgreSQL versions before 16..." Showing my naivety, somehow this isn't connecting with me totally. If it's important to call out, then maybe we need a connecting sentence. Based on the content above, I think you're pointing to "It's also why VACUUM will freeze all eligible tuples from a heap page once the decision to freeze at least one tuple is taken:" If that's it, it's just not clear to me what's totally changed. Sorry, more learning. :-) --- Hope something in there is helpful. Ryan Booz > > -- > Peter Geoghegan
Re: Overhauling "Routine Vacuuming" docs, particularly its handling of freezing
And, of course, I forgot that I switch to text-mode after writing most of this, so the carriage returns were unnecessary. (facepalm... sigh) -- Ryan On Fri, May 12, 2023 at 1:36 PM Ryan Booz wrote: > > Thanks for the continued work, Peter. I hate to be the guy that starts this > way, > but this is my first ever response on pgsql-hackers. (insert awkward > smile face). > Hopefully I've followed etiquette well, but please forgive any > missteps, and I'm > happy for any help in making better contributions in the future. > > On Thu, May 11, 2023 at 9:19 PM Peter Geoghegan wrote: > > > > On Thu, May 4, 2023 at 3:18 PM samay sharma wrote: > > > What do you think about the term "Exhaustion"? Maybe something like "XID > > > allocation exhaustion" or "Exhaustion of allocatable XIDs"? > > > > I use the term "transaction ID exhaustion" in the attached revision, > > v4. Overall, v4 builds on the work that went into v2 and v3, by > > continuing to polish the overhaul of everything related to freezing, > > relfrozenxid advancement, and anti-wraparound autovacuum. > > Just to say on the outset, as has been said earlier in the tread by others, > that this is herculean work. Thank you for putting the effort you have thus > far. > There's a lot of good from where I sit in the modification efforts. > It's a heavy, > dense topic, so there's probably never going to be a perfect way to > get it all in, > but some of the context early on, especially, is helpful for framing. > > > > > It would be nice if it was possible to add an animation/diagram a > > little like this one: https://tuple-freezing-demo.angusd.com (this is > > how I tend to think about the "transaction ID space".) > > Indeed. With volunteer docs, illustrations/diagrams are hard for sure. But, > this or something akin to the "clock" image I've seen elsewhere when > describing the transaction ID space would probably be helpful if it were ever > possible. In fact, there's just a lot about the MVCC stuff in general that > would benefit from diagrams. But alas, I guess that's why we have some > good go-to community talks/slide decks. :-) > > > v4 also limits use of the term "wraparound" to places that directly > > discuss anti-wraparound autovacuums (plus one place in xact.sgml, > > where discussion of "true unsigned integer wraparound" and related > > implementation details has been moved). Otherwise we use the term > > "transaction ID exhaustion", which is pretty much the user-facing name > > for "xidStopLimit". I feel that this is a huge improvement, for the > > reason given to Greg earlier. I'm flexible on the details, but I feel > > strongly that we should minimize use of the term wraparound wherever > > it might have the connotation of "the past becoming the future". This > > is not a case of inventing a new terminology for its own sake. If > > anybody is skeptical I ask that they take a look at what I came up > > with before declaring it a bad idea. I have made that as easy as > > possible, by once again attaching a prebuilt routine-vacuuming.html. > > Thanks again for doing this. Really helpful for doc newbies like me that > want to help but are still working through the process. Really helpful > and appreciated. > > > > > > > Other changes in v4, compared to v3: > > > > * Improved discussion of the differences between non-aggressive and > > aggressive VACUUM. > > This was helpful for me and not something I've previously put much thought > into. Helpful context that is missing from the current docs. > > > * Explains "catch-up freezing" performed by aggressive VACUUMs directly. > > > > "Catch-up" freezing is the really important "consequence" -- something > > that emerges from how each type of VACUUM behaves over time. It is an > > indirect consequence of the behaviors. I would like to counter the > > perception that some users have about freezing only happening during > > aggressive VACUUMs (or anti-wraparound autovacuums). But more than > > that, talking about catch-up freezing seems essential because it is > > the single most important difference. > > > > Similarly, this was helpful overall context of various things > happening with freezing. > > > * Much improved handling of the discussion of anti-wraparound > > autovacuum, and how it relates to aggressive VACUUMs, following > > feedback from Samay. > > > > There is now only fairly minimal overlap in the d
