On Fri, Jul 8, 2022 at 10:47 AM Alvaro Herrera <alvhe...@alvh.no-ip.org> wrote: > Saving some sort of history would be much more useful, but of course a > lot more work.
I think that storing a certain amount of history would be very useful, for lots of reasons. Not just for instrumentation purposes; I envisage a design where VACUUM itself makes certain decisions based on the history of each VACUUM operation against the table. The direction that things have taken suggests a certain amount about the direction that things are going in, which we should try to influence. The simplest and best example of how this could help is probably freezing, and freeze debt. Currently, the visibility map interacts with vacuum_freeze_min_age in a way that allows unfrozen all-visible pages to accumulate. These pages won't be frozen until the next aggressive VACUUM. But there is no fixed relationship between the number of XIDs consumed by the system (per unit of wallclock time) and the number of unfrozen all-visible pages (over the same duration). So we might end up having to freeze an absolutely enormous number of pages in the eventual aggressive vacuum. We also might not -- it's really hard to predict, for reasons that just don't make much sense. There are a few things we could do here, but having a sense of history seems like the important part. If (say) the table exceeds a certain size, and the number of all-visible pages grows and grows (without any freezing taking place), then we should "proactively" freeze at least some of the unfrozen all-visible pages in earlier VACUUM operations. In other words, we should (at the very least) spread out the burden of freezing those pages over time, while being careful to not pay too much more than we would with the old approach if and when the workload characteristics change again. More generally, I think that we should blur the distinction between aggressive and non-aggressive autovacuum. Sure, we'd still need VACUUM to "behave aggressively" in some sense, but that could all happen dynamically, without committing to a particular course of action until the last moment -- being able to change our minds at the last minute can be very valuable, even though we probably won't change our minds too often. -- Peter Geoghegan
