On Wed, 5 Jul 2023 at 18:05, Matthias van de Meent <boekewurm+postg...@gmail.com> wrote: > > On Wed, 5 Jul 2023 at 14:39, Thom Brown <t...@linux.com> wrote: > > > > On Wed, 5 Jul 2023 at 13:12, Matthias van de Meent > > <boekewurm+postg...@gmail.com> wrote: > > > > > > On Wed, 5 Jul 2023 at 13:03, Thom Brown <t...@linux.com> wrote: > > > > > > > > On Wed, 5 Jul 2023 at 11:57, Matthias van de Meent > > > > <boekewurm+postg...@gmail.com> wrote: > > > > > > > > > > On Wed, 5 Jul 2023 at 12:45, Thom Brown <t...@linux.com> wrote: > > > > > > Heap-Only Tuple (HOT) updates are a significant performance > > > > > > enhancement, as they prevent unnecessary page writes. However, HOT > > > > > > comes with a caveat: it means that if we have lots of available > > > > > > space > > > > > > earlier on in the relation, it can only be used for new tuples or in > > > > > > cases where there's insufficient space on a page for an UPDATE to > > > > > > use > > > > > > HOT. > > > > > > > > > > > > This mechanism limits our options for condensing tables, forcing us > > > > > > to > > > > > > resort to methods like running VACUUM FULL/CLUSTER or using external > > > > > > tools like pg_repack. These either require exclusive locks (which > > > > > > will > > > > > > be a deal-breaker on large tables on a production system), or > > > > > > there's > > > > > > risks involved. Of course we can always flood pages with new > > > > > > versions > > > > > > of a row until it's forced onto an early page, but that shouldn't be > > > > > > necessary. > > > > > > > > > > > > Considering these trade-offs, I'd like to propose an option to allow > > > > > > superusers to disable HOT on tables. The intent is to trade some > > > > > > performance benefits for the ability to reduce the size of a table > > > > > > without the typical locking associated with it. > > > > > > > > > > Interesting use case, but I think that disabling HOT would be missing > > > > > the forest for the trees. I think that a feature that disables > > > > > block-local updates for pages > some offset would be a better solution > > > > > to your issue: Normal updates also prefer the new tuple to be stored > > > > > in the same pages as the old tuple if at all possible, so disabling > > > > > HOT wouldn't solve the issue of tuples residing in the tail of your > > > > > table - at least not while there is still empty space in those pages. > > > > > > > > Hmm... I see your point. It's when an UPDATE isn't going to land on > > > > the same page that it relocates to the earlier available page. So I > > > > guess I'm after whatever mechanism would allow that to happen reliably > > > > and predictably. > > > > > > > > So $subject should really be "Allow forcing UPDATEs off the same page". > > > > > > You'd probably want to do that only for a certain range of the table - > > > for a table with 1GB of data and 3GB of bloat there is no good reason > > > to force page-crossing updates in the first 1GB of the table - all > > > tuples of the table will eventually reside there, so why would you > > > take a performance penalty and move the tuples from inside that range > > > to inside that same range? > > > > I'm thinking more of a case of: > > > > <magic to stop UPDATES from landing on same page> > > > > UPDATE bigtable > > SET primary key = primary key > > WHERE ctid IN ( > > SELECT ctid > > FROM bigtable > > ORDER BY ctid DESC > > LIMIT 100000); > > So what were you thinking of? A session GUC? A table option?
Both. > The benefit of a table option is that it is retained across sessions > and thus allows tables that get enough updates to eventually get to a > cleaner state. The main downside of such a table option is that it > requires a temporary table-level lock to update the parameter. Yes, but the maintenance window to make such a change would be extremely brief. > The benefit of a session GUC is that you can set it without impacting > other sessions, but the downside is that you need to do the > maintenance in that session, and risk that cascading updates to other > tables (e.g. through AFTER UPDATE triggers) are also impacted by this > non-local update GUC. > > > > Something else to note: Indexes would suffer some (large?) amount of > > > bloat in this process, as you would be updating a lot of tuples > > > without the HOT optimization, thus increasing the work to be done by > > > VACUUM. > > > This may result in more bloat in indexes than what you get back from > > > shrinking the table. > > > > This could be the case, but I guess indexes are expendable to an > > extent, unlike tables. > > I don't think that's accurate - index rebuilds are quite expensive. > But, that's besides the point of this thread. > > Somewhat related: did you consider using pg_repack instead of this > potential feature? pg_repack isn't exactly innocuous, and can leave potentially the database in an irrevocable state. Plus, if disk space is an issue, it doesn't help. Thom
