On Tue, 2023-09-19 at 12:52 -0400, Robert Haas wrote: > On Tue, Sep 19, 2023 at 12:30 PM Alvaro Herrera <alvhe...@alvh.no-ip.org> > wrote: > > I was thinking something vaguely like "a table size that's roughly what > > an optimal autovacuuming schedule would leave the table at" assuming 0.2 > > vacuum_scale_factor. You would determine the absolute minimum size for > > the table given the current live tuples in the table, then add 20% to > > account for a steady state of dead tuples and vacuumed space. So it's > > not 1.2x of the "current" table size at the time the local_update_limit > > feature is installed, but 1.2x of the optimal table size. > > Right, that would be great. And honestly if that's something we can > figure out, then why does the parameter even need to be an integer > instead of a Boolean? If the system knows the optimal table size, then > the user can just say "try to compact this table" and need not say to > what size. The 1.2 multiplier is probably situation dependent and > maybe the multiplier should indeed be a configuration parameter, but > we would be way better off if the absolute size didn't need to be.
I don't have high hopes for a reliable way to automatically determine the target table size. There are these queries floating around to estimate table bloat, which are used by various monitoring systems. I find that they get it right a lot of the time, but sometimes they get it wrong. Perhaps we can do better than that, but I vastly prefer a setting that I can control (even at the danger that I can misconfigure it) over an automatism that I cannot control and that sometimes gets it wrong. I like Alvaro's idea to automatically reset "local_update_limit" when the table has shrunk enough. Why not perform that task during vacuum truncation? If vacuum truncation has taken place, check if the table size is no bigger than "local_update_limit" * (1 + "autovacuum_vacuum_scale_factor"), and if it is no bigger, reset "local_update_limit". That way, we would not have to worry about a lock, because vacuum truncation already has the table locked. Yours, Laurenz Albe
