public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Thom Brown <[email protected]>
To: Matthias van de Meent <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Disabling Heap-Only Tuples
Date: Wed, 05 Jul 2023 21:47:38 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAA-aLv5B2reEQdOgXY5aE9szef1F7s-RQ23Oacb1R6reMPu07w@mail.gmail.com>
References: <CAA-aLv6sYZ5XnuYrytTjxZumBh3KrdyMRmasxHfgaKf-HJrNpw@mail.gmail.com>
	<CAEze2WjpvSoT+Ybp2jpBfPMWSpsdq_2heAmvbJVUcgZ+QfrXoA@mail.gmail.com>
	<CAA-aLv5B2reEQdOgXY5aE9szef1F7s-RQ23Oacb1R6reMPu07w@mail.gmail.com>

On Wed, 2023-07-05 at 12:02 +0100, Thom Brown wrote:
> On Wed, 5 Jul 2023 at 11:57, Matthias van de Meent <[email protected]> wrote:
> > On Wed, 5 Jul 2023 at 12:45, Thom Brown <[email protected]> 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.
> > > 
> > > 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".

I've been thinking about the same thing - an option that changes the update
strategy to always use the lowest block with enough free space.

That would allow to consolidate bloated tables with no down time.

Yours,
Laurenz Albe

view thread (6+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Disabling Heap-Only Tuples
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox