On Sat, Nov 22, 2025 at 12:28 PM Sami Imseih <[email protected]> wrote: > What I have not been able to prove from my tests is that the processing > order of tables by autovacuum will actually make things any better or any > worse. My tests have been short 30 minute tests that count how many > vacuum cycles tables with various DML activity and sizes received. > I have not found much difference. I am also not sure how valuable > these short-duration tests are either.
Yeah, I'm not sure that would be the right way to look for a benefit from something like this. I think that a better test scenario might involve figuring out how fast we can recover from a bad situation. As we've discussed before, if VACUUM is chronically unable to keep up with the workload, then the system is going to get into a very bad state and there's not really any help for it. But if we start to get into a bad situation due to some outside interference and then someone removes the interference, we might hope that this patch would help us get back on our feet more quickly. For instance, suppose that we have a database with a stale replication slot, so the oldest-XID value for the cluster keeps getting older and older. autovacuum is probably running but it can't clean anything up. Then at some point, the DBA realizes that bad things are happening and drops the replication slot. You might hope that, with the patch, autovacuum would do a better job getting the system back to a working state. If you set up some kind of test scenario, you could ask questions like "what is the largest age(relfrozenxid) that we observe in the database at any point during the test?" or "from the time the replication slot is dropped, how much time passes before age(datfrozenxid) drops to normal?" or "what is the maximum observed amount of bloat during the test?". The same kind of idea could apply to anything else that stops vacuum from running or makes it unproductive: a full table lock on a key table, an open transaction, a table where VACUUM is failing. I actually don't know exactly what kind of scenario would be good to test here, because I struggle to think of a concrete scenario in which we'd be better off with this than without it (which might be a reason not to proceed with it, despite the fact that I think we all agree that, from a theoretical point of view, the idea of prioritizing sounds better than the idea of not prioritizing). But I think that if the patch has a benefit, it won't be one where the system is in a steady state where vacuum is able to keep up. It might be one where we're in a steady state where vacuum is not able to keep up and things are getting worse and worse, but the patch allows us to survive for longer before terrible things happen. But I would say that the most promising scenario for this patch would be something like what I describe above, where we're not in a steady state at all: something bad has happened and now we're trying to recover. -- Robert Haas EDB: http://www.enterprisedb.com
