On Tue, Sep 19, 2023 at 12:56 PM Andres Freund <and...@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