Message-ID: <4f286c03f91b73e07f215e05dccc87619266b3d7.camel@cybertec.at>
Subject: Re: Disabling Heap-Only Tuples
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Thom Brown <thom@linux.com>, Matthias van de Meent
	 <boekewurm+postgres@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: Wed, 05 Jul 2023 21:47:38 +0200
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>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.46.4 (3.46.4-1.fc37) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/4f286c03f91b73e07f215e05dccc87619266b3d7.camel%40cybertec.at>
Precedence: bulk

On Wed, 2023-07-05 at 12:02 +0100, Thom Brown wrote:
> On Wed, 5 Jul 2023 at 11:57, Matthias van de Meent <boekewurm+postgres@gm=
ail.com> wrote:
> > On Wed, 5 Jul 2023 at 12:45, Thom Brown <thom@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.
> > >=20
> > > 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.
> >=20
> > 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.
>=20
> Hmm... I see your point.=C2=A0 It's when an UPDATE isn't going to land on
> the same page that it relocates to the earlier available page.=C2=A0 So I
> guess I'm after whatever mechanism would allow that to happen reliably
> and predictably.
>=20
> 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