On Tue, Sep 19, 2023 at 12:56 PM Andres Freund <andres@anarazel.de> wrote:
> Yea, a setting like what's discussed here seems, uh, not particularly useful
> for achieving the goal of compacting tables. I don't think guiding this
> through SQL makes a lot of sense. For decent compaction you'd want to scan the
> table backwards, and move rows from the end to earlier, but stop once
> everything is filled up. You can somewhat do that from SQL, but it's going to
> be awkward and slow. I doubt you even want to use the normal UPDATE WAL
> logging.
>
> I think having explicit compaction support in VACUUM or somewhere similar
> would make sense, but I don't think the proposed GUC is a useful stepping
> stone.
I think there's a difference between wanting to compact instantly and
wanting to compact over time. I think that this kind of thing is
reasonably well-suited to the latter, if we can engineer away the
cases where it backfires.
But I know people will try to use it for instant compaction too, and
there it's worth remembering why we removed old-style VACUUM FULL. The
main problem is that it was mind-bogglingly slow. The other really bad
problem is that it caused massive index bloat. I think any system
that's based on moving around my tuples right now to make my table
smaller right now is likely to have similar issues.
In the case where you're trying to compact gradually, I think there
are potentially serious issues with index bloat, but only potentially.
It seems like there are reasonable cases where it's fine.
Specifically, if you have relatively few indexes per table, relatively
few long-running transactions, and all tuples get updated on a
semi-regular basis, I'm thinking that you're more likely to win than
lose.
--
Robert Haas
EDB: http://www.enterprisedb.com