From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com>, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> |
Cc: | Matthias van de Meent <boekewurm+postgres(at)gmail(dot)com>, Thom Brown <thom(at)linux(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Disabling Heap-Only Tuples |
Date: | 2023-09-20 03:18:20 |
Message-ID: | 6de9b1f1aa35511f635f250a139a0690d65fe7bc.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Tue, 2023-09-19 at 12:52 -0400, Robert Haas wrote:
> On Tue, Sep 19, 2023 at 12:30 PM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Maciek Sakrejda | 2023-09-20 03:23:44 | Re: CREATE FUNCTION ... SEARCH { DEFAULT | SYSTEM | SESSION } |
Previous Message | Amit Langote | 2023-09-20 03:07:23 | Re: remaining sql/json patches |