Thread

Disabling Heap-Only Tuples

Thom Brown <thom@linux.com> — 2023-07-05T10:44:31Z

Hi,

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.

This feature could be used to shrink tables in one of two ways:
temporarily disabling HOT until DML operations have compacted the data
into a smaller area, or performing a mass update on later rows to
relocate them to an earlier location, probably in stages. Of course,
this would need to be used in conjunction with a VACUUM operation.

Admittedly this isn't ideal, and it would be better if we had an
operation that could do this (e.g. VACUUM COMPACT <table_name>), or an
option that causes some operations to avoid HOT when it detects an
amount of free space over a threshold, but in lieu of those, I thought
this would at least allow users to help themselves when running into
disk space issues.

Thoughts?

Thom

Re: Disabling Heap-Only Tuples

Matthias van de Meent <boekewurm+postgres@gmail.com> — 2023-07-05T10:57:40Z

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.
>
> 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.

Kind regards,

Matthias van de Meent
Neon (https://neon.tech/)

Re: Disabling Heap-Only Tuples

Thom Brown <thom@linux.com> — 2023-07-05T11:02:55Z

On Wed, 5 Jul 2023 at 11:57, Matthias van de Meent
<boekewurm+postgres@gmail.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.
> >
> > 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".

Thom

Re: Disabling Heap-Only Tuples

Matthias van de Meent <boekewurm+postgres@gmail.com> — 2023-07-05T12:12:15Z

On Wed, 5 Jul 2023 at 13:03, Thom Brown <thom@linux.com> wrote:
>
> On Wed, 5 Jul 2023 at 11:57, Matthias van de Meent
> <boekewurm+postgres@gmail.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.
> > >
> > > 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?

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.

Kind regards,

Matthias van de Meent
Neon (https://neon.tech/)

Re: Disabling Heap-Only Tuples

Thom Brown <thom@linux.com> — 2023-07-05T12:38:44Z

On Wed, 5 Jul 2023 at 13:12, Matthias van de Meent
<boekewurm+postgres@gmail.com> wrote:
>
> On Wed, 5 Jul 2023 at 13:03, Thom Brown <thom@linux.com> wrote:
> >
> > On Wed, 5 Jul 2023 at 11:57, Matthias van de Meent
> > <boekewurm+postgres@gmail.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.
> > > >
> > > > 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);

> 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.

Thom

Re: Disabling Heap-Only Tuples

Matthias van de Meent <boekewurm+postgres@gmail.com> — 2023-07-05T17:05:36Z

On Wed, 5 Jul 2023 at 14:39, Thom Brown <thom@linux.com> wrote:
>
> On Wed, 5 Jul 2023 at 13:12, Matthias van de Meent
> <boekewurm+postgres@gmail.com> wrote:
> >
> > On Wed, 5 Jul 2023 at 13:03, Thom Brown <thom@linux.com> wrote:
> > >
> > > On Wed, 5 Jul 2023 at 11:57, Matthias van de Meent
> > > <boekewurm+postgres@gmail.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.
> > > > >
> > > > > 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?

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.

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?

Kind regards,

Matthias van de Meent
Neon (https://neon.tech)

Re: Disabling Heap-Only Tuples

Thom Brown <thom@linux.com> — 2023-07-05T17:54:53Z

On Wed, 5 Jul 2023 at 18:05, Matthias van de Meent
<boekewurm+postgres@gmail.com> wrote:
>
> On Wed, 5 Jul 2023 at 14:39, Thom Brown <thom@linux.com> wrote:
> >
> > On Wed, 5 Jul 2023 at 13:12, Matthias van de Meent
> > <boekewurm+postgres@gmail.com> wrote:
> > >
> > > On Wed, 5 Jul 2023 at 13:03, Thom Brown <thom@linux.com> wrote:
> > > >
> > > > On Wed, 5 Jul 2023 at 11:57, Matthias van de Meent
> > > > <boekewurm+postgres@gmail.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.
> > > > > >
> > > > > > 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

Re: Disabling Heap-Only Tuples

Laurenz Albe <laurenz.albe@cybertec.at> — 2023-07-05T19:47:38Z

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@gmail.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.
> > > 
> > > 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

Re: Disabling Heap-Only Tuples

Matthias van de Meent <boekewurm+postgres@gmail.com> — 2023-07-06T20:18:07Z

On Wed, 5 Jul 2023 at 19:55, Thom Brown <thom@linux.com> wrote:
>
> On Wed, 5 Jul 2023 at 18:05, Matthias van de Meent
> <boekewurm+postgres@gmail.com> wrote:
> > So what were you thinking of? A session GUC? A table option?
>
> Both.

Here's a small patch implementing a new table option max_local_update
(name very much bikesheddable). Value is -1 (default, disabled) or the
size of the table in MiB that you still want to allow to update on the
same page. I didn't yet go for a GUC as I think that has too little
control on the impact on the system.

I decided that max_local_update would be in MB because there is no
reloption value that can contain MaxBlockNumber and -1/disabled; and 1
MiB seems like enough granularity for essentially all use cases.

The added regression tests show how this feature works, that the new
feature works, and validate that lock levels are acceptable
(ShareUpdateExclusiveLock, same as for updating fillfactor).

Kind regards,

Matthias van de Meent
Neon (https://neon.tech/)

Re: Disabling Heap-Only Tuples

Dilip Kumar <dilipbalaut@gmail.com> — 2023-07-07T04:53:32Z

On Fri, Jul 7, 2023 at 1:48 AM Matthias van de Meent
<boekewurm+postgres@gmail.com> wrote:
>
> On Wed, 5 Jul 2023 at 19:55, Thom Brown <thom@linux.com> wrote:
> >
> > On Wed, 5 Jul 2023 at 18:05, Matthias van de Meent
> > <boekewurm+postgres@gmail.com> wrote:
> > > So what were you thinking of? A session GUC? A table option?
> >
> > Both.
>
> Here's a small patch implementing a new table option max_local_update
> (name very much bikesheddable). Value is -1 (default, disabled) or the
> size of the table in MiB that you still want to allow to update on the
> same page. I didn't yet go for a GUC as I think that has too little
> control on the impact on the system.

So IIUC, this parameter we can control that instead of putting the new
version of the tuple on the same page, it should choose using
RelationGetBufferForTuple(), and that can reduce the fragmentation
because now if there is space then most of the updated tuple will be
inserted in same pages.  But this still can not truncate the pages
from the heap right? because we can not guarantee that the new page
selected by RelationGetBufferForTuple() is not from the end of the
heap, and until we free the pages from the end of the heap, the vacuum
can not truncate any page.  Is my understanding correct?

-- 
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

Re: Disabling Heap-Only Tuples

Matthias van de Meent <boekewurm+postgres@gmail.com> — 2023-07-07T09:55:28Z

On Fri, 7 Jul 2023 at 06:53, Dilip Kumar <dilipbalaut@gmail.com> wrote:
>
> On Fri, Jul 7, 2023 at 1:48 AM Matthias van de Meent
> <boekewurm+postgres@gmail.com> wrote:
> >
> > On Wed, 5 Jul 2023 at 19:55, Thom Brown <thom@linux.com> wrote:
> > >
> > > On Wed, 5 Jul 2023 at 18:05, Matthias van de Meent
> > > <boekewurm+postgres@gmail.com> wrote:
> > > > So what were you thinking of? A session GUC? A table option?
> > >
> > > Both.
> >
> > Here's a small patch implementing a new table option max_local_update
> > (name very much bikesheddable). Value is -1 (default, disabled) or the
> > size of the table in MiB that you still want to allow to update on the
> > same page. I didn't yet go for a GUC as I think that has too little
> > control on the impact on the system.
>
> So IIUC, this parameter we can control that instead of putting the new
> version of the tuple on the same page, it should choose using
> RelationGetBufferForTuple(), and that can reduce the fragmentation
> because now if there is space then most of the updated tuple will be
> inserted in same pages.  But this still can not truncate the pages
> from the heap right? because we can not guarantee that the new page
> selected by RelationGetBufferForTuple() is not from the end of the
> heap, and until we free the pages from the end of the heap, the vacuum
> can not truncate any page.  Is my understanding correct?

Yes. If you don't have pages with (enough) free space for the updated
tuples in your table, or if the FSM doesn't accurately reflect the
actual state of free space in your table, this won't help (which is
also the reason why I run vacuum in the tests). It also won't help if
you don't update the tuples physically located at the end of your
table, but in the targeted workload this would introduce a bias where
new tuple versions are moved to the front of the table.

Something to note is that this may result in very bad bloat when this
is combined with a low fillfactor: All blocks past max_local_update
will be unable to use space reserved by fillfactor because FSM lookups
always take fillfactor into account, and all updates (which ignore
fillfactor when local) would go through the FSM instead, thus reducing
the space available on each block to exactly the fillfactor. So, this
might need some extra code to make sure we don't accidentally blow up
the table's size with UPDATEs when max_local_update is combined with
low fillfactors. I'm not sure where that would fit best.

Kind regards,

Matthias van de Meent
Neon (https://neon.tech/)

Re: Disabling Heap-Only Tuples

Tomas Vondra <tomas.vondra@enterprisedb.com> — 2023-07-07T10:18:04Z


On 7/7/23 11:55, Matthias van de Meent wrote:
> On Fri, 7 Jul 2023 at 06:53, Dilip Kumar <dilipbalaut@gmail.com> wrote:
>>
>> On Fri, Jul 7, 2023 at 1:48 AM Matthias van de Meent
>> <boekewurm+postgres@gmail.com> wrote:
>>>
>>> On Wed, 5 Jul 2023 at 19:55, Thom Brown <thom@linux.com> wrote:
>>>>
>>>> On Wed, 5 Jul 2023 at 18:05, Matthias van de Meent
>>>> <boekewurm+postgres@gmail.com> wrote:
>>>>> So what were you thinking of? A session GUC? A table option?
>>>>
>>>> Both.
>>>
>>> Here's a small patch implementing a new table option max_local_update
>>> (name very much bikesheddable). Value is -1 (default, disabled) or the
>>> size of the table in MiB that you still want to allow to update on the
>>> same page. I didn't yet go for a GUC as I think that has too little
>>> control on the impact on the system.
>>
>> So IIUC, this parameter we can control that instead of putting the new
>> version of the tuple on the same page, it should choose using
>> RelationGetBufferForTuple(), and that can reduce the fragmentation
>> because now if there is space then most of the updated tuple will be
>> inserted in same pages.  But this still can not truncate the pages
>> from the heap right? because we can not guarantee that the new page
>> selected by RelationGetBufferForTuple() is not from the end of the
>> heap, and until we free the pages from the end of the heap, the vacuum
>> can not truncate any page.  Is my understanding correct?
> 
> Yes. If you don't have pages with (enough) free space for the updated
> tuples in your table, or if the FSM doesn't accurately reflect the
> actual state of free space in your table, this won't help (which is
> also the reason why I run vacuum in the tests). It also won't help if
> you don't update the tuples physically located at the end of your
> table, but in the targeted workload this would introduce a bias where
> new tuple versions are moved to the front of the table.
> 
> Something to note is that this may result in very bad bloat when this
> is combined with a low fillfactor: All blocks past max_local_update
> will be unable to use space reserved by fillfactor because FSM lookups
> always take fillfactor into account, and all updates (which ignore
> fillfactor when local) would go through the FSM instead, thus reducing
> the space available on each block to exactly the fillfactor. So, this
> might need some extra code to make sure we don't accidentally blow up
> the table's size with UPDATEs when max_local_update is combined with
> low fillfactors. I'm not sure where that would fit best.
> 

I know the thread started as "let's disable HOT" and this essentially
just proposes to do that using a table option. But I wonder if that's
far too simple to be reliable, because hoping RelationGetBufferForTuple
happens to do the right thing does not seem great.

I wonder if we should invent some definition of "strategy" that would
tell RelationGetBufferForTuple what it should aim for ...

I'm imagining either a table option with a couple possible values
(default, non-hot, first-page, ...) or maybe something even more
elaborate (perhaps even a callback?).

Now, it's not my intention to hijack this thread, but this discussion
reminds me one of the ideas from my "BRIN improvements" talk, about
maybe using BRIN indexes for routing. UPDATEs may be a major issue for
BRIN, making them gradually worse over time. If we could "tell"
RelationGetBufferForTuple() which buffers are more suitable (by looking
at an index, histogram or some approximate mapping), that might help.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Disabling Heap-Only Tuples

Dilip Kumar <dilipbalaut@gmail.com> — 2023-07-07T10:57:45Z

On Fri, Jul 7, 2023 at 3:48 PM Tomas Vondra
<tomas.vondra@enterprisedb.com> wrote:
>

> On 7/7/23 11:55, Matthias van de Meent wrote:
> > On Fri, 7 Jul 2023 at 06:53, Dilip Kumar <dilipbalaut@gmail.com> wrote:
> >>
> >> On Fri, Jul 7, 2023 at 1:48 AM Matthias van de Meent
> >> <boekewurm+postgres@gmail.com> wrote:
> >>>
> >>> On Wed, 5 Jul 2023 at 19:55, Thom Brown <thom@linux.com> wrote:
> >>>>
> >>>> On Wed, 5 Jul 2023 at 18:05, Matthias van de Meent
> >>>> <boekewurm+postgres@gmail.com> wrote:
> >>>>> So what were you thinking of? A session GUC? A table option?
> >>>>
> >>>> Both.
> >>>
> >>> Here's a small patch implementing a new table option max_local_update
> >>> (name very much bikesheddable). Value is -1 (default, disabled) or the
> >>> size of the table in MiB that you still want to allow to update on the
> >>> same page. I didn't yet go for a GUC as I think that has too little
> >>> control on the impact on the system.
> >>
> >> So IIUC, this parameter we can control that instead of putting the new
> >> version of the tuple on the same page, it should choose using
> >> RelationGetBufferForTuple(), and that can reduce the fragmentation
> >> because now if there is space then most of the updated tuple will be
> >> inserted in same pages.  But this still can not truncate the pages
> >> from the heap right? because we can not guarantee that the new page
> >> selected by RelationGetBufferForTuple() is not from the end of the
> >> heap, and until we free the pages from the end of the heap, the vacuum
> >> can not truncate any page.  Is my understanding correct?
> >
> > Yes. If you don't have pages with (enough) free space for the updated
> > tuples in your table, or if the FSM doesn't accurately reflect the
> > actual state of free space in your table, this won't help (which is
> > also the reason why I run vacuum in the tests). It also won't help if
> > you don't update the tuples physically located at the end of your
> > table, but in the targeted workload this would introduce a bias where
> > new tuple versions are moved to the front of the table.
> >
> > Something to note is that this may result in very bad bloat when this
> > is combined with a low fillfactor: All blocks past max_local_update
> > will be unable to use space reserved by fillfactor because FSM lookups
> > always take fillfactor into account, and all updates (which ignore
> > fillfactor when local) would go through the FSM instead, thus reducing
> > the space available on each block to exactly the fillfactor. So, this
> > might need some extra code to make sure we don't accidentally blow up
> > the table's size with UPDATEs when max_local_update is combined with
> > low fillfactors. I'm not sure where that would fit best.
> >
>
> I know the thread started as "let's disable HOT" and this essentially
> just proposes to do that using a table option. But I wonder if that's
> far too simple to be reliable, because hoping RelationGetBufferForTuple
> happens to do the right thing does not seem great.
>
> I wonder if we should invent some definition of "strategy" that would
> tell RelationGetBufferForTuple what it should aim for ...
>
> I'm imagining either a table option with a couple possible values
> (default, non-hot, first-page, ...) or maybe something even more
> elaborate (perhaps even a callback?).
>
> Now, it's not my intention to hijack this thread, but this discussion
> reminds me one of the ideas from my "BRIN improvements" talk, about
> maybe using BRIN indexes for routing. UPDATEs may be a major issue for
> BRIN, making them gradually worse over time. If we could "tell"
> RelationGetBufferForTuple() which buffers are more suitable (by looking
> at an index, histogram or some approximate mapping), that might help.

IMHO that seems like the right direction for this feature to be
useful.  Otherwise just forcing it to select a page using
RelationGetBufferForTuple() without any input or direction to this
function can behave pretty randomly.  In fact, there should be some
way to say insert a new tuple in a smaller block number first
(provided they have free space) and with that, we might get an
opportunity to truncate some heap pages by vacuum.

-- 
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

Re: Disabling Heap-Only Tuples

Laurenz Albe <laurenz.albe@cybertec.at> — 2023-07-07T11:10:48Z

On Fri, 2023-07-07 at 16:27 +0530, Dilip Kumar wrote:
> On Fri, Jul 7, 2023 at 3:48 PM Tomas Vondra <tomas.vondra@enterprisedb.com> wrote:
> > I'm imagining either a table option with a couple possible values
> > (default, non-hot, first-page, ...) or maybe something even more
> > elaborate (perhaps even a callback?).
> > 
> > Now, it's not my intention to hijack this thread, but this discussion
> > reminds me one of the ideas from my "BRIN improvements" talk, about
> > maybe using BRIN indexes for routing. UPDATEs may be a major issue for
> > BRIN, making them gradually worse over time. If we could "tell"
> > RelationGetBufferForTuple() which buffers are more suitable (by looking
> > at an index, histogram or some approximate mapping), that might help.
> 
> IMHO that seems like the right direction for this feature to be
> useful.

Right, I agree.  A GUC/storage parameter like "update_strategy"
that is an enum (try-hot | first-page | ...).

To preserve BRIN indexes or CLUSTERed tables, there could be an additional
"insert_strategy", but that would somehow have to be tied to a certain
index.  I think that is out of scope for this effort.

Yours,
Laurenz Albe

Re: Disabling Heap-Only Tuples

Thom Brown <thom@linux.com> — 2023-07-07T11:21:03Z

On Thu, 6 Jul 2023 at 21:18, Matthias van de Meent
<boekewurm+postgres@gmail.com> wrote:
>
> On Wed, 5 Jul 2023 at 19:55, Thom Brown <thom@linux.com> wrote:
> >
> > On Wed, 5 Jul 2023 at 18:05, Matthias van de Meent
> > <boekewurm+postgres@gmail.com> wrote:
> > > So what were you thinking of? A session GUC? A table option?
> >
> > Both.
>
> Here's a small patch implementing a new table option max_local_update
> (name very much bikesheddable). Value is -1 (default, disabled) or the
> size of the table in MiB that you still want to allow to update on the
> same page. I didn't yet go for a GUC as I think that has too little
> control on the impact on the system.
>
> I decided that max_local_update would be in MB because there is no
> reloption value that can contain MaxBlockNumber and -1/disabled; and 1
> MiB seems like enough granularity for essentially all use cases.
>
> The added regression tests show how this feature works, that the new
> feature works, and validate that lock levels are acceptable
> (ShareUpdateExclusiveLock, same as for updating fillfactor).

Wow, thanks for working on this.

I've given it a test, and it does what I would expect it to do.

I'm aware of the concerns about the potential for the relocation to
land in an undesirable location, so perhaps that needs addressing.
But this is already considerably better than the current need to
update a row until it gets pushed off its current page.  Ideally there
would be tooling built around this where the user wouldn't need to
figure out how much of the table to UPDATE, or deal with VACUUMing
concerns.

But here's my quick test:

CREATE OR REPLACE FUNCTION compact_table(table_name IN TEXT)
RETURNS VOID AS $$
DECLARE
    current_row RECORD;
    old_ctid TID;
    new_ctid TID;
    keys TEXT;
    update_query TEXT;
    row_counter INTEGER := 0;
BEGIN
    SELECT string_agg(a.attname || ' = ' || a.attname, ', ')
    INTO keys
    FROM
        pg_index i
    JOIN
        pg_attribute a ON a.attnum = ANY(i.indkey)
    WHERE
        i.indrelid = table_name::regclass
        AND a.attrelid = table_name::regclass
        AND i.indisprimary;

    IF keys IS NULL THEN
        RAISE EXCEPTION 'Table % does not have a primary key.', table_name;
    END IF;

    FOR current_row IN
        EXECUTE FORMAT('SELECT ctid, * FROM %I ORDER BY ctid DESC', table_name)
    LOOP
        old_ctid := current_row.ctid;

        update_query := FORMAT('UPDATE %I SET %s WHERE ctid = $1
RETURNING ctid', table_name, keys);
        EXECUTE update_query USING old_ctid INTO new_ctid;

        row_counter := row_counter + 1;

        IF row_counter % 1000 = 0 THEN
            RAISE NOTICE '% rows relocated.', row_counter;
        END IF;

        IF new_ctid <= old_ctid THEN
            CONTINUE;
        ELSE
            RAISE NOTICE 'All non-contiguous rows relocated.';
            EXIT;
        END IF;
    END LOOP;
END; $$
LANGUAGE plpgsql;


postgres=# CREATE TABLE bigtable (id int, content text);
CREATE TABLE
postgres=# INSERT INTO bigtable SELECT x, 'This is just a way to fill
up space.' FROM generate_series(1,10000000) a(x);
INSERT 0 10000000
postgres=# DELETE FROM bigtable WHERE id % 7 = 0;
DELETE 1428571
postgres=# VACUUM bigtable;
VACUUM
postgres=# ALTER TABLE bigtable SET (max_local_update = 0);
ALTER TABLE
postgres=# ALTER TABLE bigtable ADD PRIMARY KEY (id);
ALTER TABLE
postgres=# \dt+ bigtable
                                   List of relations
 Schema |   Name   | Type  | Owner | Persistence | Access method |
Size  | Description
--------+----------+-------+-------+-------------+---------------+--------+-------------
 public | bigtable | table | thom  | permanent   | heap          | 730 MB |
(1 row)

postgres=# SELECT * FROM pgstattuple('bigtable');
 table_len | tuple_count | tuple_len | tuple_percent |
dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space |
free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
 765607936 |     8571429 | 557142885 |         72.77 |
0 |              0 |                  0 |  105901628 |        13.83
(1 row)

postgres=# SELECT compact_table('bigtable');
NOTICE:  1000 rows relocated.
NOTICE:  2000 rows relocated.
NOTICE:  3000 rows relocated.
NOTICE:  4000 rows relocated.
...
NOTICE:  1221000 rows relocated.
NOTICE:  1222000 rows relocated.
NOTICE:  1223000 rows relocated.
NOTICE:  1224000 rows relocated.
NOTICE:  All non-contiguous rows relocated.
 compact_table
---------------

(1 row)

postgres=# VACUUM bigtable;
VACUUM
postgres=# \dt+ bigtable;
                                   List of relations
 Schema |   Name   | Type  | Owner | Persistence | Access method |
Size  | Description
--------+----------+-------+-------+-------------+---------------+--------+-------------
 public | bigtable | table | thom  | permanent   | heap          | 626 MB |
(1 row)

postgres=# SELECT * FROM pgstattuple('bigtable');
 table_len | tuple_count | tuple_len | tuple_percent |
dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space |
free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
 656236544 |     8571429 | 557142885 |          84.9 |
0 |              0 |                  0 |    2564888 |         0.39
(1 row)

Works for me.

Thom

Re: Disabling Heap-Only Tuples

Ants Aasma <ants@cybertec.at> — 2023-07-07T12:43:14Z

On Fri, 7 Jul 2023 at 13:18, Tomas Vondra <tomas.vondra@enterprisedb.com> wrote:
> On 7/7/23 11:55, Matthias van de Meent wrote:
> > On Fri, 7 Jul 2023 at 06:53, Dilip Kumar <dilipbalaut@gmail.com> wrote:
> >>
> >> On Fri, Jul 7, 2023 at 1:48 AM Matthias van de Meent
> >> <boekewurm+postgres@gmail.com> wrote:
> >>>
> >>> On Wed, 5 Jul 2023 at 19:55, Thom Brown <thom@linux.com> wrote:
> >>>>
> >>>> On Wed, 5 Jul 2023 at 18:05, Matthias van de Meent
> >>>> <boekewurm+postgres@gmail.com> wrote:
> >>>>> So what were you thinking of? A session GUC? A table option?
> >>>>
> >>>> Both.
> >>>
> >>> Here's a small patch implementing a new table option max_local_update
> >>> (name very much bikesheddable). Value is -1 (default, disabled) or the
> >>> size of the table in MiB that you still want to allow to update on the
> >>> same page. I didn't yet go for a GUC as I think that has too little
> >>> control on the impact on the system.
> >>
> >> So IIUC, this parameter we can control that instead of putting the new
> >> version of the tuple on the same page, it should choose using
> >> RelationGetBufferForTuple(), and that can reduce the fragmentation
> >> because now if there is space then most of the updated tuple will be
> >> inserted in same pages.  But this still can not truncate the pages
> >> from the heap right? because we can not guarantee that the new page
> >> selected by RelationGetBufferForTuple() is not from the end of the
> >> heap, and until we free the pages from the end of the heap, the vacuum
> >> can not truncate any page.  Is my understanding correct?
> >
> > Yes. If you don't have pages with (enough) free space for the updated
> > tuples in your table, or if the FSM doesn't accurately reflect the
> > actual state of free space in your table, this won't help (which is
> > also the reason why I run vacuum in the tests). It also won't help if
> > you don't update the tuples physically located at the end of your
> > table, but in the targeted workload this would introduce a bias where
> > new tuple versions are moved to the front of the table.
> >
> > Something to note is that this may result in very bad bloat when this
> > is combined with a low fillfactor: All blocks past max_local_update
> > will be unable to use space reserved by fillfactor because FSM lookups
> > always take fillfactor into account, and all updates (which ignore
> > fillfactor when local) would go through the FSM instead, thus reducing
> > the space available on each block to exactly the fillfactor. So, this
> > might need some extra code to make sure we don't accidentally blow up
> > the table's size with UPDATEs when max_local_update is combined with
> > low fillfactors. I'm not sure where that would fit best.
> >
>
> I know the thread started as "let's disable HOT" and this essentially
> just proposes to do that using a table option. But I wonder if that's
> far too simple to be reliable, because hoping RelationGetBufferForTuple
> happens to do the right thing does not seem great.
>
> I wonder if we should invent some definition of "strategy" that would
> tell RelationGetBufferForTuple what it should aim for ...
>
> I'm imagining either a table option with a couple possible values
> (default, non-hot, first-page, ...) or maybe something even more
> elaborate (perhaps even a callback?).
>
> Now, it's not my intention to hijack this thread, but this discussion
> reminds me one of the ideas from my "BRIN improvements" talk, about
> maybe using BRIN indexes for routing. UPDATEs may be a major issue for
> BRIN, making them gradually worse over time. If we could "tell"
> RelationGetBufferForTuple() which buffers are more suitable (by looking
> at an index, histogram or some approximate mapping), that might help.

Just as another point in support of strategy based/extensible tuple
placement, I would at some point try out placing INSERT ON CONFLICT
tuples on the same page as the preceding key in the index. Use case is
in tables with (series, timestamp) primary key to get locality of
access range scanning for a single series. Placement will always be a
tradeoff that is dependent on hardware and workload, and the effect
can be pretty large. For the mentioned use case, if placement can
maintain some semblance of clustering, there will be a 10-100x
reduction in buffers accessed for a relatively minor increase in
bloat.

--
Ants Aasma
Senior Database Engineer
www.cybertec-postgresql.com

Re: Disabling Heap-Only Tuples

Laurenz Albe <laurenz.albe@cybertec.at> — 2023-07-19T12:58:51Z

On Thu, 2023-07-06 at 22:18 +0200, Matthias van de Meent wrote:
> On Wed, 5 Jul 2023 at 19:55, Thom Brown <thom@linux.com> wrote:
> > 
> > On Wed, 5 Jul 2023 at 18:05, Matthias van de Meent
> > <boekewurm+postgres@gmail.com> wrote:
> > > So what were you thinking of? A session GUC? A table option?
> > 
> > Both.
> 
> Here's a small patch implementing a new table option max_local_update
> (name very much bikesheddable). Value is -1 (default, disabled) or the
> size of the table in MiB that you still want to allow to update on the
> same page. I didn't yet go for a GUC as I think that has too little
> control on the impact on the system.
> 
> I decided that max_local_update would be in MB because there is no
> reloption value that can contain MaxBlockNumber and -1/disabled; and 1
> MiB seems like enough granularity for essentially all use cases.
> 
> The added regression tests show how this feature works, that the new
> feature works, and validate that lock levels are acceptable
> (ShareUpdateExclusiveLock, same as for updating fillfactor).

I have looked at your patch, and I must say that I like it.  Having
a size limit is better than my original idea of just "on" or "off".
Essentially, it is "try to shrink the table if it grows above a limit".

The patch builds fine and passes all regression tests.

Documentation is missing.

I agree that the name "max_local_update" could be improved.
Perhaps "avoid_hot_above_size_mb".

--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -342,6 +342,7 @@ typedef struct StdRdOptions
    int         parallel_workers;   /* max number of parallel workers */
    StdRdOptIndexCleanup vacuum_index_cleanup;  /* controls index vacuuming */
    bool        vacuum_truncate;    /* enables vacuum to truncate a relation */
+   int         max_local_update;   /* Updates to pages after this block must go through the VM */
 } StdRdOptions;
 
 #define HEAP_MIN_FILLFACTOR            10

In the comment, it should be FSM, not VM, right?

Other than that, I see nothing wrong.

Yours,
Laurenz Albe

Re: Disabling Heap-Only Tuples

Thom Brown <thom@linux.com> — 2023-07-19T13:13:41Z

On Wed, 19 Jul 2023, 13:58 Laurenz Albe, <laurenz.albe@cybertec.at> wrote:

> On Thu, 2023-07-06 at 22:18 +0200, Matthias van de Meent wrote:
> > On Wed, 5 Jul 2023 at 19:55, Thom Brown <thom@linux.com> wrote:
> > >
> > > On Wed, 5 Jul 2023 at 18:05, Matthias van de Meent
> > > <boekewurm+postgres@gmail.com> wrote:
> > > > So what were you thinking of? A session GUC? A table option?
> > >
> > > Both.
> >
> > Here's a small patch implementing a new table option max_local_update
> > (name very much bikesheddable). Value is -1 (default, disabled) or the
> > size of the table in MiB that you still want to allow to update on the
> > same page. I didn't yet go for a GUC as I think that has too little
> > control on the impact on the system.
> >
> > I decided that max_local_update would be in MB because there is no
> > reloption value that can contain MaxBlockNumber and -1/disabled; and 1
> > MiB seems like enough granularity for essentially all use cases.
> >
> > The added regression tests show how this feature works, that the new
> > feature works, and validate that lock levels are acceptable
> > (ShareUpdateExclusiveLock, same as for updating fillfactor).
>
> I have looked at your patch, and I must say that I like it.  Having
> a size limit is better than my original idea of just "on" or "off".
> Essentially, it is "try to shrink the table if it grows above a limit".
>
> The patch builds fine and passes all regression tests.
>
> Documentation is missing.
>
> I agree that the name "max_local_update" could be improved.
> Perhaps "avoid_hot_above_size_mb".
>

Or "hot_table_size_threshold" or "hot_update_limit"?

Thom

Re: Disabling Heap-Only Tuples

Matthias van de Meent <boekewurm+postgres@gmail.com> — 2023-08-24T15:22:34Z

On Fri, 7 Jul 2023 at 12:18, Tomas Vondra <tomas.vondra@enterprisedb.com> wrote:
>
> On 7/7/23 11:55, Matthias van de Meent wrote:
>> On Fri, 7 Jul 2023 at 06:53, Dilip Kumar <dilipbalaut@gmail.com> wrote:
>>>
>>>
>>> So IIUC, this parameter we can control that instead of putting the new
>>> version of the tuple on the same page, it should choose using
>>> RelationGetBufferForTuple(), and that can reduce the fragmentation
>>> because now if there is space then most of the updated tuple will be
>>> inserted in same pages.  But this still can not truncate the pages
>>> from the heap right? because we can not guarantee that the new page
>>> selected by RelationGetBufferForTuple() is not from the end of the
>>> heap, and until we free the pages from the end of the heap, the vacuum
>>> can not truncate any page.  Is my understanding correct?
>>
>> Yes. If you don't have pages with (enough) free space for the updated
>> tuples in your table, or if the FSM doesn't accurately reflect the
>> actual state of free space in your table, this won't help (which is
>> also the reason why I run vacuum in the tests). It also won't help if
>> you don't update the tuples physically located at the end of your
>> table, but in the targeted workload this would introduce a bias where
>> new tuple versions are moved to the front of the table.
>>
>> Something to note is that this may result in very bad bloat when this
>> is combined with a low fillfactor: All blocks past max_local_update
>> will be unable to use space reserved by fillfactor because FSM lookups
>> always take fillfactor into account, and all updates (which ignore
>> fillfactor when local) would go through the FSM instead, thus reducing
>> the space available on each block to exactly the fillfactor. So, this
>> might need some extra code to make sure we don't accidentally blow up
>> the table's size with UPDATEs when max_local_update is combined with
>> low fillfactors. I'm not sure where that would fit best.
>>
>
> I know the thread started as "let's disable HOT" and this essentially
> just proposes to do that using a table option. But I wonder if that's
> far too simple to be reliable, because hoping RelationGetBufferForTuple
> happens to do the right thing does not seem great.
>
> I wonder if we should invent some definition of "strategy" that would
> tell RelationGetBufferForTuple what it should aim for ...
>
> I'm imagining either a table option with a couple possible values
> (default, non-hot, first-page, ...) or maybe something even more
> elaborate (perhaps even a callback?).

I mostly agree, but the point is that first we have to get the update
away from the page. Once we've done that, we can start getting smart
about placement in RelationGetBufferForTuple, but unless we decide to
not put the tuple on the old tuple's page no code from
RelationGetBufferForTuple is executed.

We could change the update code to always go through
RelationGetBufferForTuple to determine the target buffer, and make
that function consider page-local updates (instead of heap_update, who
does that now), but I think that'd need significant extra work in
other callsites of RelationGetBufferForTuple as well as that function
itself.

> Now, it's not my intention to hijack this thread, but this discussion
> reminds me one of the ideas from my "BRIN improvements" talk, about
> maybe using BRIN indexes for routing. UPDATEs may be a major issue for
> BRIN, making them gradually worse over time. If we could "tell"
> RelationGetBufferForTuple() which buffers are more suitable (by looking
> at an index, histogram or some approximate mapping), that might help.

Improved tuple routing sounds like a great idea, and I've thought
about it as well. I'm not sure whether BRIN (as-is) is the best
candidate though, considering its O(N) scan complexity - 100GB-scale
tables can reasonably have BRIN indexes of MBs, and running a scan on
that is not likely to have good performance.
If BRIN had hierarchical summaries (e.g. if we had range summaries for
data stored in every nonnegative power of 16 of page ranges) then we
could reduce that to something more reasonable, but that's not
currently implemented and so I don't think that's quite relevant yet.

Kind regards,

Matthias van de Meent
Neon (https://neon.tech/)

Re: Disabling Heap-Only Tuples

Matthias van de Meent <boekewurm+postgres@gmail.com> — 2023-08-24T16:23:18Z

On Wed, 19 Jul 2023 at 15:13, Thom Brown <thom@linux.com> wrote:
>
> On Wed, 19 Jul 2023, 13:58 Laurenz Albe, <laurenz.albe@cybertec.at> wrote:
>> I agree that the name "max_local_update" could be improved.
>> Perhaps "avoid_hot_above_size_mb".
>
> Or "hot_table_size_threshold" or "hot_update_limit"?

Although I like these names, it doesn't quite cover the use of the
parameter for me, as updated tuples prefer to be inserted on the same
page as the old tuple regardless of whether HOT applies.

Example: a bloated table test(
   id int primary key,
   num_updates int,
   unique (id, num_updates)
)
would be assumed to remain bloated if I'd set a parameter named
something_hot_something, as all updates would be non-hot and thus
should not be influenced by the GUC/parameter.

How about 'local_update_limit'?

Kind regards,

Matthias van de Meent

Re: Disabling Heap-Only Tuples

Laurenz Albe <laurenz.albe@cybertec.at> — 2023-08-28T12:20:17Z

On Thu, 2023-08-24 at 18:23 +0200, Matthias van de Meent wrote:
> On Wed, 19 Jul 2023 at 15:13, Thom Brown <thom@linux.com> wrote:
> > 
> > On Wed, 19 Jul 2023, 13:58 Laurenz Albe, <laurenz.albe@cybertec.at> wrote:
> > > I agree that the name "max_local_update" could be improved.
> > > Perhaps "avoid_hot_above_size_mb".
> > 
> > Or "hot_table_size_threshold" or "hot_update_limit"?
> 
> Although I like these names, it doesn't quite cover the use of the
> parameter for me, as updated tuples prefer to be inserted on the same
> page as the old tuple regardless of whether HOT applies.
> 
> How about 'local_update_limit'?

I agree with your concern.  I cannot think of a better name than yours.

Yours,
Laurenz Albe

Re: Disabling Heap-Only Tuples

Matthias van de Meent <boekewurm+postgres@gmail.com> — 2023-08-28T13:51:07Z

On Wed, 19 Jul 2023 at 14:58, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> On Thu, 2023-07-06 at 22:18 +0200, Matthias van de Meent wrote:
> > On Wed, 5 Jul 2023 at 19:55, Thom Brown <thom@linux.com> wrote:
> > >
> > > On Wed, 5 Jul 2023 at 18:05, Matthias van de Meent
> > > <boekewurm+postgres@gmail.com> wrote:
> > > > So what were you thinking of? A session GUC? A table option?
> > >
> > > Both.
> >
> > Here's a small patch implementing a new table option max_local_update
> > (name very much bikesheddable). Value is -1 (default, disabled) or the
> > size of the table in MiB that you still want to allow to update on the
> > same page. I didn't yet go for a GUC as I think that has too little
> > control on the impact on the system.
> >
> > I decided that max_local_update would be in MB because there is no
> > reloption value that can contain MaxBlockNumber and -1/disabled; and 1
> > MiB seems like enough granularity for essentially all use cases.
> >
> > The added regression tests show how this feature works, that the new
> > feature works, and validate that lock levels are acceptable
> > (ShareUpdateExclusiveLock, same as for updating fillfactor).
>
> I have looked at your patch, and I must say that I like it.  Having
> a size limit is better than my original idea of just "on" or "off".
> Essentially, it is "try to shrink the table if it grows above a limit".
>
> The patch builds fine and passes all regression tests.
>
> Documentation is missing.

Yes, the first patch was a working proof-of-concept. Here's a new one
with documentation.

> I agree that the name "max_local_update" could be improved.
> Perhaps "avoid_hot_above_size_mb".
>
> --- a/src/include/utils/rel.h
> +++ b/src/include/utils/rel.h
> @@ -342,6 +342,7 @@ typedef struct StdRdOptions
>     int         parallel_workers;   /* max number of parallel workers */
>     StdRdOptIndexCleanup vacuum_index_cleanup;  /* controls index vacuuming */
>     bool        vacuum_truncate;    /* enables vacuum to truncate a relation */
> +   int         max_local_update;   /* Updates to pages after this block must go through the VM */
>  } StdRdOptions;
>
>  #define HEAP_MIN_FILLFACTOR            10
>
> In the comment, it should be FSM, not VM, right?

Good catch.

In this new patch, I've updated a few comments to get mostly within
line length limits; the name of the storage parameter is now
"local_update_limit", as per discussion on naming.
I've also added local_update_limit to psql's autocomplete file, and
added documentation on how the parameter behaves - including warnings
- in create_table.sgml.

Kind regards,

Matthias van de Meent

Re: Disabling Heap-Only Tuples

Robert Haas <robertmhaas@gmail.com> — 2023-08-28T15:14:13Z

On Mon, Aug 28, 2023 at 10:52 AM Matthias van de Meent
<boekewurm+postgres@gmail.com> wrote:
> In this new patch, I've updated a few comments to get mostly within
> line length limits; the name of the storage parameter is now
> "local_update_limit", as per discussion on naming.
> I've also added local_update_limit to psql's autocomplete file, and
> added documentation on how the parameter behaves - including warnings
> - in create_table.sgml.

I feel like this is the sort of setting that experts will sometimes be
able to use to improve the situation, and non-experts will have great
difficulty using. It relies on the user to know what size limit will
work out well, which probably involves knowing how much real data is
in the table, and how that's going to change over time, and probably
also some things about how PostgreSQL does space management
internally. I don't know that I'd be able to guide a non-expert user
in how to make effective use of this as a tool.

I don't know exactly what to propose, but I would definitely like it
if we could come up with something with which a casual user would be
less likely to shoot themselves in the foot and more likely to derive
a benefit.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

Re: Disabling Heap-Only Tuples

Matthias van de Meent <boekewurm+postgres@gmail.com> — 2023-08-28T15:49:50Z

On Mon, 28 Aug 2023 at 17:14, Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Mon, Aug 28, 2023 at 10:52 AM Matthias van de Meent
> <boekewurm+postgres@gmail.com> wrote:
> > In this new patch, I've updated a few comments to get mostly within
> > line length limits; the name of the storage parameter is now
> > "local_update_limit", as per discussion on naming.
> > I've also added local_update_limit to psql's autocomplete file, and
> > added documentation on how the parameter behaves - including warnings
> > - in create_table.sgml.
>
> I feel like this is the sort of setting that experts will sometimes be
> able to use to improve the situation, and non-experts will have great
> difficulty using. It relies on the user to know what size limit will
> work out well, which probably involves knowing how much real data is
> in the table, and how that's going to change over time, and probably
> also some things about how PostgreSQL does space management
> internally. I don't know that I'd be able to guide a non-expert user
> in how to make effective use of this as a tool.

Agreed on all points. But isn't that true for most most tools on bloat
prevention and/or detection? E.g. fillfactor, autovacuum_*, ...

> I don't know exactly what to propose, but I would definitely like it
> if we could come up with something with which a casual user would be
> less likely to shoot themselves in the foot and more likely to derive
> a benefit.

I'd prefer that too, but by lack of other work in this area this seems
like it fills a niche that would otherwise require extremely expensive
locking over a long time for CLUSTER, superuser+pg_repack, or manual
scripts that update tuples until they're located on a different page
(begin; update tuple WHERE ctid > '(12,0)' returning ctid; ...;
commit;). I agree this is very minimal and can definitely be used as a
footgun, but with the description that it can be a footgun I don't
think it's (much) worse than the current situation - a user should
only reach for this once they've realized they actually have an issue.

Kind regards,

Matthias van de Meent
Neon (https://neon.tech)

Re: Disabling Heap-Only Tuples

Robert Haas <robertmhaas@gmail.com> — 2023-08-28T15:57:03Z

On Mon, Aug 28, 2023 at 11:50 AM Matthias van de Meent
<boekewurm+postgres@gmail.com> wrote:
> Agreed on all points. But isn't that true for most most tools on bloat
> prevention and/or detection? E.g. fillfactor, autovacuum_*, ...

Not nearly to the same extent, IMHO. A lot of those parameters can be
left alone forever and you lose nothing. That's not so here.

> I'd prefer that too, but by lack of other work in this area this seems
> like it fills a niche that would otherwise require extremely expensive
> locking over a long time for CLUSTER, superuser+pg_repack, or manual
> scripts that update tuples until they're located on a different page
> (begin; update tuple WHERE ctid > '(12,0)' returning ctid; ...;
> commit;). I agree this is very minimal and can definitely be used as a
> footgun, but with the description that it can be a footgun I don't
> think it's (much) worse than the current situation - a user should
> only reach for this once they've realized they actually have an issue.

Well, I sort of expected that counter-argument, but I'm not sure that I buy it.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

Re: Disabling Heap-Only Tuples

Matthias van de Meent <boekewurm+postgres@gmail.com> — 2023-08-30T13:01:36Z

On Mon, 28 Aug 2023 at 17:57, Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Mon, Aug 28, 2023 at 11:50 AM Matthias van de Meent
> <boekewurm+postgres@gmail.com> wrote:
> > Agreed on all points. But isn't that true for most most tools on bloat
> > prevention and/or detection? E.g. fillfactor, autovacuum_*, ...
>
> Not nearly to the same extent, IMHO. A lot of those parameters can be
> left alone forever and you lose nothing. That's not so here.

I've reworked the patch a bit to remove the "excessive bloat with low
fillfactors when local space is available" issue that this parameter
could cause - local updates are now done if the selected page we would
be inserting into is after the old tuple's page and the old tuple's
page still (or: now) has space available.

Does that alleviate your concerns?

Kind regards,

Matthias van de Meent
Neon (https://neon.tech)

Re: Disabling Heap-Only Tuples

Robert Haas <robertmhaas@gmail.com> — 2023-08-30T13:31:07Z

On Wed, Aug 30, 2023 at 9:01 AM Matthias van de Meent
<boekewurm+postgres@gmail.com> wrote:
> I've reworked the patch a bit to remove the "excessive bloat with low
> fillfactors when local space is available" issue that this parameter
> could cause - local updates are now done if the selected page we would
> be inserting into is after the old tuple's page and the old tuple's
> page still (or: now) has space available.
>
> Does that alleviate your concerns?

That seems like a good chance, but my core concern is around people
having to micromanage local_update_limit, and probably either not
knowing how to do it properly, or not being able or willing to keep
updating it as things change.

In a way, this parameter is a lot like work_mem, which is notoriously
very difficult to tune. If you set it too high, you run out of memory.
If you set it too low, you get bad plans. You can switch from having
one of those problems to having the other very quickly as load changs,
and sometimes you can have both at the same time. If an omniscient
oracle could set work_mem properly for every query based not only on
what the query does but the state of the system at that moment, it
would still be a very crude parameter, and since omniscient oracles
are rare in practice, problems are reasonably common. I think that if
we add this parameter, it's going to end up in the same category. A
lot of people will ignore it, and they'll be OK, but 30% of the people
who do try to use it will shoot themselves in the foot, or something
like that.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

Re: Disabling Heap-Only Tuples

Robert Haas <robertmhaas@gmail.com> — 2023-08-30T13:31:41Z

On Wed, Aug 30, 2023 at 9:31 AM Robert Haas <robertmhaas@gmail.com> wrote:
> That seems like a good chance, but

*change

-- 
Robert Haas
EDB: http://www.enterprisedb.com

Re: Disabling Heap-Only Tuples

Matthias van de Meent <boekewurm+postgres@gmail.com> — 2023-08-30T16:11:05Z

On Wed, 30 Aug 2023 at 15:31, Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Wed, Aug 30, 2023 at 9:01 AM Matthias van de Meent
> <boekewurm+postgres@gmail.com> wrote:
> > I've reworked the patch a bit to remove the "excessive bloat with low
> > fillfactors when local space is available" issue that this parameter
> > could cause - local updates are now done if the selected page we would
> > be inserting into is after the old tuple's page and the old tuple's
> > page still (or: now) has space available.
> >
> > Does that alleviate your concerns?
>
> That seems like a good chance, but my core concern is around people
> having to micromanage local_update_limit, and probably either not
> knowing how to do it properly, or not being able or willing to keep
> updating it as things change.

Assuming you do want to provide a way to users to solve the issue of
"there is a lot of free space in the table, but I don't want to take
an access exclusive lock or wait for new inserts to fix the issue",
how would you suggest we do that then?

Alternative approaches that I can think of are:

- A %-based parameter.
  This does scale with the table, but doesn't stop being a performance
hog once you've reached the optimal table size, and thus also needs to
be disabled.

- Measure the parameter from the end of the table, instead of from the
front; i.e. "try to empty the last X=50 MBs of the table".
  Scales with the table, but same issue as above - once the table has
an optimal size, it doesn't stop.

- Install one more dynamic system to move the tuples to a better page,
one the users don't directly control (yet to be designed).
  I don't know if or when this will be implemented and what benefits
it will have, but we don't have access to a lot of state in
table_tuple_update or heap_update, so any data needs special lookup.

- Let users keep using VACUUM FULL and CLUSTER instead.
  I don't think this is a reasonable solution.

> In a way, this parameter is a lot like work_mem, which is notoriously
> very difficult to tune. If you set it too high, you run out of memory.
> If you set it too low, you get bad plans. You can switch from having
> one of those problems to having the other very quickly as load changs,
> and sometimes you can have both at the same time. If an omniscient
> oracle could set work_mem properly for every query based not only on
> what the query does but the state of the system at that moment, it
> would still be a very crude parameter, and since omniscient oracles
> are rare in practice, problems are reasonably common. I think that if
> we add this parameter, it's going to end up in the same category. A
> lot of people will ignore it, and they'll be OK, but 30% of the people
> who do try to use it will shoot themselves in the foot, or something
> like that.

The "shoot yourself in the foot" in this case is limited to "your
UPDATE statement's performance is potentially Y times worse due to
forced FSM lookups for every update at the end of the table". I'll
admit that this is not great, but I'd say it is also not the end of
the world, and still much better than the performance differences that
you can see when the plan changes due to an updated work_mem.

I'd love to have more contextual information available on the table's
free space distribution so that this decision could be made by the
system, but that info just isn't available right now. We don't really
have infrastructure in place that would handle such information
either, and table_tuple_update does not get to use reuse state across
tuples, so any use of information will add cost for every update. With
this patch, the FSM cost is gated behind the storage parameter, and
thus only limited, but I don't think we can store much more than
storage parameters in the Relation data.

VACUUM / ANALYZE could probably create and store sketches about the
free space distribution in the relation, but that would widen the
scope significantly, and I have only limited bandwidth available for
this.
So, while I do plan to implement any small changes or fixes required
to get this in, a major change in direction for this patch won't put
it anywhere high on my active items list.

Kind regards,

Matthias van de Meent

Re: Disabling Heap-Only Tuples

Laurenz Albe <laurenz.albe@cybertec.at> — 2023-09-06T03:15:45Z

On Wed, 2023-08-30 at 09:31 -0400, Robert Haas wrote:
> On Wed, Aug 30, 2023 at 9:01 AM Matthias van de Meent
> <boekewurm+postgres@gmail.com> wrote:
> > I've reworked the patch a bit to remove the "excessive bloat with low
> > fillfactors when local space is available" issue that this parameter
> > could cause - local updates are now done if the selected page we would
> > be inserting into is after the old tuple's page and the old tuple's
> > page still (or: now) has space available.
> > 
> > Does that alleviate your concerns?
> 
> That seems like a good chance, but my core concern is around people
> having to micromanage local_update_limit, and probably either not
> knowing how to do it properly, or not being able or willing to keep
> updating it as things change.
> 
> In a way, this parameter is a lot like work_mem, which is notoriously
> very difficult to tune.

I don't think that is a good comparison.  While most people probably
never need to touch "local_update_limit", "work_mem" is something everybody
has to consider.

And it is not so hard to tune: the setting would be the desired table
size, and you could use pgstattuple to find a good value.

I don't know what other use cases come to mind, but I see it as a tool to
shrink a table after it has grown big holes, perhaps after a mass delete.
Today, you can only VACUUM (FULL) or play with the likes of pg_squeeze and
pg_repack.

I think this is useful.

To alleviate your concerns, perhaps it would help to describe the use case
and ideas for a good setting in the documentation.

Yours,
Laurenz Albe

Re: Disabling Heap-Only Tuples

Robert Haas <robertmhaas@gmail.com> — 2023-09-18T16:22:26Z

On Tue, Sep 5, 2023 at 11:15 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> I don't think that is a good comparison.  While most people probably
> never need to touch "local_update_limit", "work_mem" is something everybody
> has to consider.
>
> And it is not so hard to tune: the setting would be the desired table
> size, and you could use pgstattuple to find a good value.

What I suspect would happen, though, is that you'd end up tuning the
value over and over. You'd set it to some value and after some number
of vacuums maybe you'd realize that you could save even more disk
space if you reduced it a bit further or maybe your data set would
grow a bit and you'd have to increase it a little (or a lot). And if
you didn't keep adjusting it then maybe something quite bad would
happen to your database.

work_mem isn't quite the same in the sense that most people don't need
to keep on iteratively tuning work_mem, at least not in my experience.
You figure out a value that works OK in practice and then leave it
alone. The problem is mostly discovering what that initial value ought
to be, which is often hard. But what is the same here and in the case
of work_mem is that you can suddenly get hosed if the situation
changes substantially and you don't respond by updating the parameter
setting. In the case of work_mem, again in my experience, it's quite
common for people to suddenly find themselves in a lot of trouble if
they have a load spike, because now they're running a lot more copies
of the same query and the machine runs out of memory. The equivalent
problem here would be if the table suddenly gets a lot bigger due to a
load spike or some change in the way the application is used. Then
suddenly, a setting that was previously serving to keep the table
pleasantly small and un-bloated on disk is instead causing tons of
updates that would have been HOT to become non-HOT, which could very
easily result in both the table and its indexes bloating quite
rapidly. I really don't like the idea of an anti-bloat feature that,
when set to the wrong value, becomes a bloat-amplification feature. I
don't know how to describe that other than "fragile and dangerous."

Imagine a hypothetical feature that knew how small the table could
reasonably be kept, say by magic, and did non-HOT updates instead of
HOT updates whenever doing so would allow moving a tuple from a page
beyond that magical boundary to an earlier page. Such a feature would
not have the downsides that this one does -- if there were
opportunities to make the table smaller, the system would take
advantage of them automatically, and if the table grew, the system
would automatically become more relaxed to stay out of trouble. Such a
feature is clearly more work to design and implement than what is
proposed here, but it would also work a lot better in practice. In
fact, I daresay that if we accept the feature as proposed, somebody's
going to go out and write a tool to calculate what the threshold ought
to be and automatically adjust it as things change. Users of the tool
will then divide into two camps:

- People who try to tune it manually and get burned if anything
changes on their system.
- People who use that out-of-core tool.

So the out-of-core tool that does this tuning becomes a stealth
dependency for any user who is facing this problem. Gosh, don't we
have enough of those already? Connection pooling being perhaps the
most obvious example, but far from the only one.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

Re: Disabling Heap-Only Tuples

Laurenz Albe <laurenz.albe@cybertec.at> — 2023-09-18T20:02:04Z

On Mon, 2023-09-18 at 12:22 -0400, Robert Haas wrote:
> On Tue, Sep 5, 2023 at 11:15 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > I don't think that is a good comparison.  While most people probably
> > never need to touch "local_update_limit", "work_mem" is something everybody
> > has to consider.
> > 
> > And it is not so hard to tune: the setting would be the desired table
> > size, and you could use pgstattuple to find a good value.
> 
> What I suspect would happen, though, is that you'd end up tuning the
> value over and over. You'd set it to some value and after some number
> of vacuums maybe you'd realize that you could save even more disk
> space if you reduced it a bit further or maybe your data set would
> grow a bit and you'd have to increase it a little (or a lot). And if
> you didn't keep adjusting it then maybe something quite bad would
> happen to your database.

There is that risk, yes.

> work_mem isn't quite the same [...] But what is the same here and in the case
> of work_mem is that you can suddenly get hosed if the situation
> changes substantially and you don't respond by updating the parameter
> setting. In the case of work_mem, again in my experience, it's quite
> common for people to suddenly find themselves in a lot of trouble if
> they have a load spike, because now they're running a lot more copies
> of the same query and the machine runs out of memory.

So the common ground is "both parameters are not so easy to get right,
and if you get them wrong, it's a problem".  For me the big difference is
that while you pretty much have to tune "work_mem", you can normally just ignore
"local_update_limit".

> The equivalent
> problem here would be if the table suddenly gets a lot bigger due to a
> load spike or some change in the way the application is used. Then
> suddenly, a setting that was previously serving to keep the table
> pleasantly small and un-bloated on disk is instead causing tons of
> updates that would have been HOT to become non-HOT, which could very
> easily result in both the table and its indexes bloating quite
> rapidly. I really don't like the idea of an anti-bloat feature that,
> when set to the wrong value, becomes a bloat-amplification feature. I
> don't know how to describe that other than "fragile and dangerous."

Yes, you can hurt yourself that way.  But that applies to many other
settings as well.  You can tank your performance with a bad value for
"commit_delay", "hot_standby_feedback" can bloat your primary, and
so on.  Still we consider these useful parameters.

> Imagine a hypothetical feature that knew how small the table could
> reasonably be kept, say by magic, and did non-HOT updates instead of
> HOT updates whenever doing so would allow moving a tuple from a page
> beyond that magical boundary to an earlier page. Such a feature would
> not have the downsides that this one does -- if there were
> opportunities to make the table smaller, the system would take
> advantage of them automatically, and if the table grew, the system
> would automatically become more relaxed to stay out of trouble. Such a
> feature is clearly more work to design and implement than what is
> proposed here, but it would also work a lot better in practice.

That sounds a bit like we should not have "shared_buffers" unless we
have a magical tool built in that gets the value right automatically.
Yes, the better is the enemy of the good.  You can kill everything with
a line of reasoning like that.

> In
> fact, I daresay that if we accept the feature as proposed, somebody's
> going to go out and write a tool to calculate what the threshold ought
> to be and automatically adjust it as things change. Users of the tool
> will then divide into two camps:
> 
> - People who try to tune it manually and get burned if anything
> changes on their system.
> - People who use that out-of-core tool.
> 
> So the out-of-core tool that does this tuning becomes a stealth
> dependency for any user who is facing this problem. Gosh, don't we
> have enough of those already? Connection pooling being perhaps the
> most obvious example, but far from the only one.

I cannot follow you there.  What I envision is that "local_update_limit"
is not set permanently on a table.  You set it when you realize your table
got bloated.  Then you wait until the bloat goes away or you launch a
couple of UPDATEs that eventually shrink the table.  Then you reset
"local_update_limit" again.
It's a more difficult, but less invasive alternative to VACUUM (FULL).

If a setting is hard to understand and hard to get right, we could invest
in good documentation that explains the use cases and pitfalls.
Wouldn't that go a long way towards defusing this perceived footgun?
I am aware that a frightening number of users don't read documentation,
but I find it hard to believe that anyone would twiddle a non-obvious
knob like "local_update_limit" without first trying to figure out what
it actually does.

Yours,
Laurenz Albe

Re: Disabling Heap-Only Tuples

Alvaro Herrera <alvherre@alvh.no-ip.org> — 2023-09-19T10:26:36Z

On 2023-Sep-18, Robert Haas wrote:

> On Tue, Sep 5, 2023 at 11:15 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > I don't think that is a good comparison.  While most people probably
> > never need to touch "local_update_limit", "work_mem" is something everybody
> > has to consider.
> >
> > And it is not so hard to tune: the setting would be the desired table
> > size, and you could use pgstattuple to find a good value.
> 
> What I suspect would happen, though, is that you'd end up tuning the
> value over and over. You'd set it to some value and after some number
> of vacuums maybe you'd realize that you could save even more disk
> space if you reduced it a bit further or maybe your data set would
> grow a bit and you'd have to increase it a little (or a lot). And if
> you didn't keep adjusting it then maybe something quite bad would
> happen to your database.

As I understand it, the setting being proposed is useful as an emergency
for removing excessive bloat -- a substitute for VACUUM FULL when you
don't want to lock the table for long.  Trying to use it as a permanent
gadget is going to be misguided.  So my first thought is that we should
tell people to use it that way: if you're not in the irrecoverable-space
situation, just do not use this.  Then we don't have to worry about
people misusing it the way you imagine.

Second, I think we should make it auto-reset.  That is, have the user
set some value; later, when some condition triggers (say, the table size
is 1.2x the limit value you configured), then the local_update_limit is
automatically removed from the table options.  From that point onwards,
the table is operated normally.

This removes the other concern that makes the system behaves
suboptimally because some DBA in the past decade left this set for no
good reason: if you run into an emergency, then you activate the
emergency escape hatch, and it will close on its own as soon as the
emergency is over.

This also dissuades people from using it for these other things you
describe.  It just won't work.

The point here is that third-party tools such as pg_repack or pg_squeeze
exist, which work in a way we don't like, yet we offer no alternative.
This proposal is a mechanism that essentially replaces those tools with
a simple in-core feature, without having to include the tool itself in
core.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
Thou shalt check the array bounds of all strings (indeed, all arrays), for
surely where thou typest "foo" someone someday shall type
"supercalifragilisticexpialidocious" (5th Commandment for C programmers)

Re: Disabling Heap-Only Tuples

Robert Haas <robertmhaas@gmail.com> — 2023-09-19T16:09:09Z

On Tue, Sep 19, 2023 at 6:26 AM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> Second, I think we should make it auto-reset.  That is, have the user
> set some value; later, when some condition triggers (say, the table size
> is 1.2x the limit value you configured), then the local_update_limit is
> automatically removed from the table options.  From that point onwards,
> the table is operated normally.

That's an interesting idea. It would require taking AEL on the table.
And also, what do you mean by 1.2x the limit value? Is that supposed
to be a >= condition or a <= condition? It can't really be a >=
condition, but you wouldn't set it in the first place unless the table
were significantly bigger than it could be. But if it's a <= condition
it doesn't really protect you from hosing yourself. You just have to
insert a bit more data before enough of the bloat gets removed, and
now the table just bloats infinitely and probably rather quickly. The
correct value of the setting depends on the amount of real data
(non-bloat) in the table, not the actual table size.

> The point here is that third-party tools such as pg_repack or pg_squeeze
> exist, which work in a way we don't like, yet we offer no alternative.
> This proposal is a mechanism that essentially replaces those tools with
> a simple in-core feature, without having to include the tool itself in
> core.

I agree that it would be nice to have something in core that can be
used to help with this problem, but this feature isn't the same thing
as pg_repack or pg_squeeze, either. In some ways, it's better, because
it can shrink the table without rewriting it, which is very desirable.
But in other ways, it's worse, and the fact that it seems like it can
backfire spectacularly if you set the wrong value seems like one big
way that it is a lot worse. If there is a way that we can make this a
mode that you activate for a table, and the system calculates and
updates the threshold, I think that would actually be a pretty good
feature. It would be tricky to use it to recover from acute
emergencies, because it doesn't actually do anything until updates
happen, but you could use it for that in a pinch. And even without
that it would be useful if you have a table that is sometimes very
large and sometimes very small and you want to get the space back from
the OS when it is in the small phase of its lifecycle.

But without any kind of auto-tuning, in my opinion, it's a fairly poor
feature. Sure, some people will get use out of it, if they're
sufficiently knowledgeable and sufficiently determined. But I think
for most people in most situations, it will be a struggle.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

Re: Disabling Heap-Only Tuples

Alvaro Herrera <alvherre@alvh.no-ip.org> — 2023-09-19T16:30:44Z

On 2023-Sep-19, Robert Haas wrote:

> On Tue, Sep 19, 2023 at 6:26 AM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> > Second, I think we should make it auto-reset.  That is, have the user
> > set some value; later, when some condition triggers (say, the table size
> > is 1.2x the limit value you configured), then the local_update_limit is
> > automatically removed from the table options.  From that point onwards,
> > the table is operated normally.
> 
> That's an interesting idea. It would require taking AEL on the table.
> And also, what do you mean by 1.2x the limit value? Is that supposed
> to be a >= condition or a <= condition? It can't really be a >=
> condition, but you wouldn't set it in the first place unless the table
> were significantly bigger than it could be. But if it's a <= condition
> it doesn't really protect you from hosing yourself. You just have to
> insert a bit more data before enough of the bloat gets removed, and
> now the table just bloats infinitely and probably rather quickly. The
> correct value of the setting depends on the amount of real data
> (non-bloat) in the table, not the actual table size.

I was thinking something vaguely like "a table size that's roughly what
an optimal autovacuuming schedule would leave the table at" assuming 0.2
vacuum_scale_factor.  You would determine the absolute minimum size for
the table given the current live tuples in the table, then add 20% to
account for a steady state of dead tuples and vacuumed space.  So it's
not 1.2x of the "current" table size at the time the local_update_limit
feature is installed, but 1.2x of the optimal table size.

This makes me think that maybe the logic needs to be a little more
complex to avoid the problem you describe: if an UPDATE is prevented
from being HOT because of this setting, but then it goes and consults
FSM and it gives the update a higher block number than the tuple's
current block (or it fails to give a block number at all so it is forced
to extend the relation), then the update should give up on that strategy
and use a HOT update after all.  (I have not read the actual patch;
maybe it already does this?  It sounds kinda obvious.)

Having to set AEL is not nice for sure, but wouldn't
ShareUpdateExclusiveLock be sufficient?  We have a bunch of reloptions
for which that is sufficient.

> But without any kind of auto-tuning, in my opinion, it's a fairly poor
> feature. Sure, some people will get use out of it, if they're
> sufficiently knowledgeable and sufficiently determined. But I think
> for most people in most situations, it will be a struggle.
> 
> -- 
> Robert Haas
> EDB: http://www.enterprisedb.com

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"Tiene valor aquel que admite que es un cobarde" (Fernandel)

Re: Disabling Heap-Only Tuples

Robert Haas <robertmhaas@gmail.com> — 2023-09-19T16:52:10Z

On Tue, Sep 19, 2023 at 12:30 PM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> I was thinking something vaguely like "a table size that's roughly what
> an optimal autovacuuming schedule would leave the table at" assuming 0.2
> vacuum_scale_factor.  You would determine the absolute minimum size for
> the table given the current live tuples in the table, then add 20% to
> account for a steady state of dead tuples and vacuumed space.  So it's
> not 1.2x of the "current" table size at the time the local_update_limit
> feature is installed, but 1.2x of the optimal table size.

Right, that would be great. And honestly if that's something we can
figure out, then why does the parameter even need to be an integer
instead of a Boolean? If the system knows the optimal table size, then
the user can just say "try to compact this table" and need not say to
what size. The 1.2 multiplier is probably situation dependent and
maybe the multiplier should indeed be a configuration parameter, but
we would be way better off if the absolute size didn't need to be.

> This makes me think that maybe the logic needs to be a little more
> complex to avoid the problem you describe: if an UPDATE is prevented
> from being HOT because of this setting, but then it goes and consults
> FSM and it gives the update a higher block number than the tuple's
> current block (or it fails to give a block number at all so it is forced
> to extend the relation), then the update should give up on that strategy
> and use a HOT update after all.  (I have not read the actual patch;
> maybe it already does this?  It sounds kinda obvious.)

+1 to all of that. Anything we can do to reduce the chance of the
parameter doing the opposite of what it's intended to do is, IMHO,
really, really valuable. If you're in the situation where you really
need something like this, you're probably having a pretty bad day
already.

Just to be more clear about my position, I don't think that having
some kind of a feature along these lines is a bad idea. I do think
that this is one of those cases where the perfect is the enemy of the
good, and we can fall into the trap of saying that since we can't do
the perfect thing let's not do anything at all. At the same time, just
because we need to do something doesn't mean we should do exactly the
first thing that anybody thought up, or that we shouldn't try as hard
as we can to mitigate the downsides. If we add something like this I
bet it will get a lot of use. Even a minor improvement to the design
that removes one pitfall of many could turn out to help a lot of
people. If we could get to the point where most people have a positive
user experience without too much effort, this could turn out to be one
of the most impactful features in years.

> Having to set AEL is not nice for sure, but wouldn't
> ShareUpdateExclusiveLock be sufficient?  We have a bunch of reloptions
> for which that is sufficient.

Hmm, yeah, I think you're right.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

Re: Disabling Heap-Only Tuples

Andres Freund <andres@anarazel.de> — 2023-09-19T16:56:33Z

Hi,

On 2023-09-19 18:30:44 +0200, Alvaro Herrera wrote:
> This makes me think that maybe the logic needs to be a little more
> complex to avoid the problem you describe: if an UPDATE is prevented
> from being HOT because of this setting, but then it goes and consults
> FSM and it gives the update a higher block number than the tuple's
> current block (or it fails to give a block number at all so it is forced
> to extend the relation), then the update should give up on that strategy
> and use a HOT update after all.  (I have not read the actual patch;
> maybe it already does this?  It sounds kinda obvious.)

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.

> > But without any kind of auto-tuning, in my opinion, it's a fairly poor
> > feature. Sure, some people will get use out of it, if they're
> > sufficiently knowledgeable and sufficiently determined. But I think
> > for most people in most situations, it will be a struggle.

Indeed. I think it'd often just explode table and index sizes, because HOT
pruning won't be able to make usable space in pages anymore (due to dead
items).

Greetings,

Andres Freund

Re: Disabling Heap-Only Tuples

Matthias van de Meent <boekewurm+postgres@gmail.com> — 2023-09-19T17:33:22Z

On Tue, 19 Sept 2023 at 18:56, Andres Freund <andres@anarazel.de> wrote:
>
> Hi,
>
> On 2023-09-19 18:30:44 +0200, Alvaro Herrera wrote:
> > This makes me think that maybe the logic needs to be a little more
> > complex to avoid the problem you describe: if an UPDATE is prevented
> > from being HOT because of this setting, but then it goes and consults
> > FSM and it gives the update a higher block number than the tuple's
> > current block (or it fails to give a block number at all so it is forced
> > to extend the relation), then the update should give up on that strategy
> > and use a HOT update after all.  (I have not read the actual patch;
> > maybe it already does this?  It sounds kinda obvious.)
>
> 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.

We can't move tuples around (or, not that I know of) without using a
transaction ID to control the visibility of the two locations of that
tuple. Doing table compaction would thus likely require using
transactions to move these tuples around. Using a single backend and
bulk operations, it'll still lock each tuple that is being moved, and
that can be noticed by user DML queries. I'd rather make the user's
queries move the data around than this long-duration, locking
background operation.

> 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.

The point of this GUC is that the compaction can happen organically in
the user's UPDATE workflow, so that there is no long locking operation
going on (as you would see with VACUUM FULL / CLUSTER / pg_repack).

> > > But without any kind of auto-tuning, in my opinion, it's a fairly poor
> > > feature. Sure, some people will get use out of it, if they're
> > > sufficiently knowledgeable and sufficiently determined. But I think
> > > for most people in most situations, it will be a struggle.
>
> Indeed. I think it'd often just explode table and index sizes, because HOT
> pruning won't be able to make usable space in pages anymore (due to dead
> items).

You seem to misunderstand the latest patch. It explicitly only blocks
local updates if the update can then move the new tuple to an earlier
page. If that is not possible, then it'll insert locally (assuming
that is still possible) and HOT can then still apply.

And yes, moving tuples to earlier pages will indeed increase index
bloat, because it does create dead tuples where previously we could've
applied HOT. But we do have VACUUM and REINDEX CONCURRENTLY to clean
that up without serious long-duration stop-the-world actions, while
the other builtin cleanup methods don't.

Kind regards,

Matthias van de Meent
Neon (https://neon.tech)

Re: Disabling Heap-Only Tuples

Matthias van de Meent <boekewurm+postgres@gmail.com> — 2023-09-19T18:20:06Z

On Tue, 19 Sept 2023 at 18:52, Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Tue, Sep 19, 2023 at 12:30 PM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> > I was thinking something vaguely like "a table size that's roughly what
> > an optimal autovacuuming schedule would leave the table at" assuming 0.2
> > vacuum_scale_factor.  You would determine the absolute minimum size for
> > the table given the current live tuples in the table, then add 20% to
> > account for a steady state of dead tuples and vacuumed space.  So it's
> > not 1.2x of the "current" table size at the time the local_update_limit
> > feature is installed, but 1.2x of the optimal table size.
>
> Right, that would be great. And honestly if that's something we can
> figure out, then why does the parameter even need to be an integer
> instead of a Boolean? If the system knows the optimal table size, then
> the user can just say "try to compact this table" and need not say to
> what size. The 1.2 multiplier is probably situation dependent and
> maybe the multiplier should indeed be a configuration parameter, but
> we would be way better off if the absolute size didn't need to be.

Mostly agreed, but I think there's a pitfall here. You seem to assume
we have a perfect oracle that knows the optimal data size, but we
already know that our estimates can be significantly off. I don't
quite trust the statistics enough to do any calculations based on the
number of tuples in the relation. That also ignores the fact that we
don't actually have any good information about the average size of the
tuples in the table. So with current statistics, any automated "this
is how large the table should be" decisions would result in an
automated footgun, instead of the current patch's where the user has
to decide to configure it to an explicit value.

But about that: I'm not sure what the "footgun" is that you've
mentioned recently?
The issue with excessive bloat (when the local_update_limit is set too
small and fillfactor is low) was fixed in the latest patch nearly
three weeks ago, so the only remaining issue with misconfiguration is
slower updates. Sure, that's not great, but in my opinion not a
"footgun": performance returns immediately after resetting
local_update_limit, and no space was lost.

> > This makes me think that maybe the logic needs to be a little more
> > complex to avoid the problem you describe: if an UPDATE is prevented
> > from being HOT because of this setting, but then it goes and consults
> > FSM and it gives the update a higher block number than the tuple's
> > current block (or it fails to give a block number at all so it is forced
> > to extend the relation), then the update should give up on that strategy
> > and use a HOT update after all.  (I have not read the actual patch;
> > maybe it already does this?  It sounds kinda obvious.)
>
> +1 to all of that. Anything we can do to reduce the chance of the
> parameter doing the opposite of what it's intended to do is, IMHO,
> really, really valuable. If you're in the situation where you really
> need something like this, you're probably having a pretty bad day
> already.

Yes, it does that with the latest patch, from not quite 3 weeks ago.

> Just to be more clear about my position, I don't think that having
> some kind of a feature along these lines is a bad idea.

Thanks for clarifying.

> I do think
> that this is one of those cases where the perfect is the enemy of the
> good, and we can fall into the trap of saying that since we can't do
> the perfect thing let's not do anything at all. At the same time, just
> because we need to do something doesn't mean we should do exactly the
> first thing that anybody thought up, or that we shouldn't try as hard
> as we can to mitigate the downsides. If we add something like this I
> bet it will get a lot of use. Even a minor improvement to the design
> that removes one pitfall of many could turn out to help a lot of
> people.

100% agreed.

> > Having to set AEL is not nice for sure, but wouldn't
> > ShareUpdateExclusiveLock be sufficient?  We have a bunch of reloptions
> > for which that is sufficient.
>
> Hmm, yeah, I think you're right.

Updating the reloption after relation truncation implies having the
same lock as relation truncation, i.e. AEL (if the vacuum docs are to
be believed). So the AEL is not reqiored for updating the storage
option (that would require SUEL), but for the block truncation
operation operation.

Kind regards,

Matthias van de Meent
Neon (http://neon.tech)

Re: Disabling Heap-Only Tuples

Robert Haas <robertmhaas@gmail.com> — 2023-09-19T18:50:13Z

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

Re: Disabling Heap-Only Tuples

Andres Freund <andres@anarazel.de> — 2023-09-19T18:55:40Z

Hi,

On 2023-09-19 19:33:22 +0200, Matthias van de Meent wrote:
> On Tue, 19 Sept 2023 at 18:56, Andres Freund <andres@anarazel.de> wrote:
> >
> > Hi,
> >
> > On 2023-09-19 18:30:44 +0200, Alvaro Herrera wrote:
> > > This makes me think that maybe the logic needs to be a little more
> > > complex to avoid the problem you describe: if an UPDATE is prevented
> > > from being HOT because of this setting, but then it goes and consults
> > > FSM and it gives the update a higher block number than the tuple's
> > > current block (or it fails to give a block number at all so it is forced
> > > to extend the relation), then the update should give up on that strategy
> > > and use a HOT update after all.  (I have not read the actual patch;
> > > maybe it already does this?  It sounds kinda obvious.)
> >
> > 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.
>
> We can't move tuples around (or, not that I know of) without using a
> transaction ID to control the visibility of the two locations of that
> tuple.

Correct, otherwise you'd end up with broken visibility in scans (seeing the
same tuple twice or never).

> Doing table compaction would thus likely require using transactions to move
> these tuples around.

Yes - but I don't think that has to be a problem. I'd expect something like
this to use multiple transactions internally. Possibly optimizing xid usage by
checking if other transactions are currently waiting on the xid and committing
if that's the case. Processing a single page should be quite fast, so the
maximum delay on other sessions is quite small.

> Using a single backend and bulk operations, it'll still lock each tuple that
> is being moved, and that can be noticed by user DML queries. I'd rather make
> the user's queries move the data around than this long-duration, locking
> background operation.

I doubt that works well enough in practice. It's very common to have tuples
that aren't updated after some point. So you then end up with needing tooling
that triggers UPDATEs for tuples at the end of the relation.

> > 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.
>
> The point of this GUC is that the compaction can happen organically in
> the user's UPDATE workflow, so that there is no long locking operation
> going on (as you would see with VACUUM FULL / CLUSTER / pg_repack).

It certainly shouldn't use an AEL. I think we could even get away without an
SUE (it's basically just UPDATEs after all), but whether it's worth doing that
I'm not sure.

> > > > But without any kind of auto-tuning, in my opinion, it's a fairly poor
> > > > feature. Sure, some people will get use out of it, if they're
> > > > sufficiently knowledgeable and sufficiently determined. But I think
> > > > for most people in most situations, it will be a struggle.
> >
> > Indeed. I think it'd often just explode table and index sizes, because HOT
> > pruning won't be able to make usable space in pages anymore (due to dead
> > items).
>
> You seem to misunderstand the latest patch. It explicitly only blocks
> local updates if the update can then move the new tuple to an earlier
> page. If that is not possible, then it'll insert locally (assuming
> that is still possible) and HOT can then still apply.

I indeed apparently had looked at the wrong patch. But I still don't think
this is a useful way of controlling this.  I guess it could be a small part of
something larger, but you are going to need something that actively updates
tuples at the end of the table, otherwise it's very unlikely in practice that
you'll ever be able to shrink the table.

Leaving aside what process "moves" tuples, I doubt that controlling "moving"
via the table size is useful. Controlling via the amount free space in the FSM
would make more sense. If there's no known free space in the FSM, this
approach can't compact. Using the table size to control also means that the
value needs to be updated with the growth of the table. Whereas controlling
moving via a percentage of free space in the FSM would allow the same setting
to be used even for a growing (or shrinking) table.

Greetings,

Andres Freund

Re: Disabling Heap-Only Tuples

Robert Haas <robertmhaas@gmail.com> — 2023-09-19T21:08:02Z

On Tue, Sep 19, 2023 at 2:20 PM Matthias van de Meent
<boekewurm+postgres@gmail.com> wrote:
> Mostly agreed, but I think there's a pitfall here. You seem to assume
> we have a perfect oracle that knows the optimal data size, but we
> already know that our estimates can be significantly off. I don't
> quite trust the statistics enough to do any calculations based on the
> number of tuples in the relation. That also ignores the fact that we
> don't actually have any good information about the average size of the
> tuples in the table. So with current statistics, any automated "this
> is how large the table should be" decisions would result in an
> automated footgun, instead of the current patch's where the user has
> to decide to configure it to an explicit value.

I'm not assuming that there's an oracle here. I'm hoping that there's
some way that we can construct one. If we can't, then I think we're
asking the user to figure out a value that we don't have any idea how
to compute ourselves. And I think that kind of thing is usually a bad
idea. It's reasonable to ask the user for input when they know
something relevant that we can't know, like how large they think their
database will get, or what hardware they're using. But it's not
reasonable to essentially hope that the user is smarter than we are.
That's leaving our job half-undone and forcing the user into coping
with the result. And note that the value we need here is largely about
the present, not the future. The question is "how small can the table
be practically made right now?". And there is no reason at all to
suppose that the user is better-placed to answer that question than
the database itself.

> But about that: I'm not sure what the "footgun" is that you've
> mentioned recently?
> The issue with excessive bloat (when the local_update_limit is set too
> small and fillfactor is low) was fixed in the latest patch nearly
> three weeks ago, so the only remaining issue with misconfiguration is
> slower updates. Sure, that's not great, but in my opinion not a
> "footgun": performance returns immediately after resetting
> local_update_limit, and no space was lost.

That does seem like a very good change, but I'm not convinced that it
solves the whole problem. I would agree with your argument if the only
downside of enabling the feature were searching the FSM, failing to
find a suitable free page, and falling back to a HOT update. Such a
thing might be slow, but it won't cause any bloat, and as you say, if
the feature doesn't do what you want, don't use it. But I think the
feature can still cause bloat.

If we're using this feature on a reasonably heavily-updated table,
then sometimes when we check whether any low-numbered pages have free
space, it will turn out that one of them does. This will happen even
if local_update_limit is set far too low, because the table is
heavily-updated, and sometimes that means tuples are moving around,
leaving holes. So when there is a hole, i.e. just by luck we happen to
find some space on a low-numbered page, we'll suffer the cost of a
non-HOT update to move that tuple to an earlier page of the relation.
However, there's a good chance that the next time we update that
tuple, the page will have become completely full, because everybody's
furiously trying to jam as many tuples as possible into those
low-numbered pages, so now the tuple will have to bounce to some
higher-numbered page.

So I think what will happen if the local update limit is set too low,
and the table is actually being updated a lot, is that we'll just
uselessly do a bunch of HOT updates on high-numbered pages as non-HOT,
which will fill up low-numbered pages turning even potentially HOT
updates on those pages to non-HOT as well. Doing a bunch of updates
that could have been HOT as non-HOT can for sure cause index bloat. It
could maybe also cause table bloat, because if we'd done the updates
as HOT, we would have been able to recover the line pointers via
HOT-pruning, but since we turned them into non-HOT updates, we have to
wait for vacuum, which is comparatively much less frequent.

I'm not quite sure how bad this residual problem is. It's certainly a
lot better if a failed attempt to move a tuple earlier can turn into a
normal HOT update instead of a non-HOT update. But I don't think it
completely eliminates the problem of useless tuple movement either.

As Andres points out, I think rightly, we should really be thinking
about ways to guide this behavior other than a page number. As you
point out, there's no guarantee that we can know the right page
number. If we can, cool. But there are other approaches too. He
mentions looking at how full the FSM is, which seems like an
interesting idea although surely we don't want every backend
repeatedly iterating over the FSM to recompute statistics. I wonder if
there are other good ideas we haven't thought of yet. Certainly, if
you found that you were frequently being forced to move tuples to
higher-numbered pages for lack of space anywhere else, that would be a
good sign that you were trying to squeeze the relation into too few
pages. But ideally you'd like to realize that you have a problem
before things get to that point.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

Re: Disabling Heap-Only Tuples

Laurenz Albe <laurenz.albe@cybertec.at> — 2023-09-20T03:02:42Z

On Tue, 2023-09-19 at 14:50 -0400, Robert Haas wrote:
> 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.

I had the same feeling that this is sort of bringing back old-style
VACUUM (FULL).  But I don't think that index bloat is a show stopper
these days, when we have REINDEX CONCURRENTLY, so I am not worried.

Yours,
Laurenz Albe

Re: Disabling Heap-Only Tuples

Laurenz Albe <laurenz.albe@cybertec.at> — 2023-09-20T03:18:20Z

On Tue, 2023-09-19 at 12:52 -0400, Robert Haas wrote:
> On Tue, Sep 19, 2023 at 12:30 PM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> > I was thinking something vaguely like "a table size that's roughly what
> > an optimal autovacuuming schedule would leave the table at" assuming 0.2
> > vacuum_scale_factor.  You would determine the absolute minimum size for
> > the table given the current live tuples in the table, then add 20% to
> > account for a steady state of dead tuples and vacuumed space.  So it's
> > not 1.2x of the "current" table size at the time the local_update_limit
> > feature is installed, but 1.2x of the optimal table size.
> 
> Right, that would be great. And honestly if that's something we can
> figure out, then why does the parameter even need to be an integer
> instead of a Boolean? If the system knows the optimal table size, then
> the user can just say "try to compact this table" and need not say to
> what size. The 1.2 multiplier is probably situation dependent and
> maybe the multiplier should indeed be a configuration parameter, but
> we would be way better off if the absolute size didn't need to be.

I don't have high hopes for a reliable way to automatically determine
the target table size.  There are these queries floating around to estimate
table bloat, which are used by various monitoring systems.  I find that they
get it right a lot of the time, but sometimes they get it wrong.  Perhaps
we can do better than that, but I vastly prefer a setting that I can control
(even at the danger that I can misconfigure it) over an automatism that I
cannot control and that sometimes gets it wrong.

I like Alvaro's idea to automatically reset "local_update_limit" when the
table has shrunk enough.  Why not perform that task during vacuum truncation?
If vacuum truncation has taken place, check if the table size is no bigger
than "local_update_limit" * (1 + "autovacuum_vacuum_scale_factor"), and if
it is no bigger, reset "local_update_limit".  That way, we would not have
to worry about a lock, because vacuum truncation already has the table locked.

Yours,
Laurenz Albe

Re: Disabling Heap-Only Tuples

Stephen Frost <sfrost@snowman.net> — 2023-09-20T14:02:23Z

Greetings,

* Laurenz Albe (laurenz.albe@cybertec.at) wrote:
> On Tue, 2023-09-19 at 12:52 -0400, Robert Haas wrote:
> > On Tue, Sep 19, 2023 at 12:30 PM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> > > I was thinking something vaguely like "a table size that's roughly what
> > > an optimal autovacuuming schedule would leave the table at" assuming 0.2
> > > vacuum_scale_factor.  You would determine the absolute minimum size for
> > > the table given the current live tuples in the table, then add 20% to
> > > account for a steady state of dead tuples and vacuumed space.  So it's
> > > not 1.2x of the "current" table size at the time the local_update_limit
> > > feature is installed, but 1.2x of the optimal table size.
> > 
> > Right, that would be great. And honestly if that's something we can
> > figure out, then why does the parameter even need to be an integer
> > instead of a Boolean? If the system knows the optimal table size, then
> > the user can just say "try to compact this table" and need not say to
> > what size. The 1.2 multiplier is probably situation dependent and
> > maybe the multiplier should indeed be a configuration parameter, but
> > we would be way better off if the absolute size didn't need to be.
> 
> I don't have high hopes for a reliable way to automatically determine
> the target table size.  There are these queries floating around to estimate
> table bloat, which are used by various monitoring systems.  I find that they
> get it right a lot of the time, but sometimes they get it wrong.  Perhaps
> we can do better than that, but I vastly prefer a setting that I can control
> (even at the danger that I can misconfigure it) over an automatism that I
> cannot control and that sometimes gets it wrong.

Not completely against a setting- but would certainly prefer that this
be done in a more automated way, if possible.

To that end, my thought would be some kind of regular review of the FSM,
or maybe actual review by walking through the table (as VACUUM already
does...) to get an idea of where there's space and where there's used up
areas and then use that to inform various operations (either VACUUM
itself or perhaps UPDATEs from SQL).  We could also try to 'start
simple' and look for cases that we can say "well, that's definitely not
good" and address those initially.

Consider (imagine as a histogram; X is used space, . is empty):

 1: XXXXXXX
 2: XXX
 3: XXXXXXX
 4: XXX
 5: X
 6: X
 7: .
 8: .
 9: .
10: .
11: .
12: .
13: .
14: .
15: .
16: .
17: .
18: .
19: .
20: X

Well, obviously there's tons of free space in the middle and if we could
just move those few tuples/pages/whatever that are near the end to
earlier in the table then we'd be able to truncate off and shrink a
lot of the table.

> I like Alvaro's idea to automatically reset "local_update_limit" when the
> table has shrunk enough.  Why not perform that task during vacuum truncation?
> If vacuum truncation has taken place, check if the table size is no bigger
> than "local_update_limit" * (1 + "autovacuum_vacuum_scale_factor"), and if
> it is no bigger, reset "local_update_limit".  That way, we would not have
> to worry about a lock, because vacuum truncation already has the table locked.

Agreed on this too.  Essentially, once we've done some truncation, we
should 'reset'.

I've no doubt that there's some better algorithm for this, but I keep
coming back to something as simple as- if the entire second half of the
table will fit into the entire first half then the table is twice as
large as it needs to be and perhaps that triggers a preference for
placing tuples in the first half of the table.  As for what handles
this- maybe have both UPDATE and VACUUM able to, but prefer for UPDATE
to do so and only have VACUUM kick in once the tuples at the end of the
relation are older than some xid-based threshold (perhaps all of the
tuples on a given page have to be old enough?).

While it feels a bit 'late' in terms of when to start taking this
action, we could possibly start with 'all frozen' as an indicator of
'old enough'?  Then, between the FSM and the VM, VACUUM could decide
that pages at the end of the table should be moved to be earlier and go
about making that happen.  I'm a bit concerned about the risk of some
kind of deadlock or similar happening between VACUUM and user processes
if we're trying to do this with multiple tuples at a time but hopefully
we could come up with a way to avoid that.  This process naturally would
have to involve updating indexes and the VM and FSM as the tuples get
moved.

In terms of what this would look like, my thinking is that VACUUM would
scan the table and the FSM and perhaps the VM and then say "ok, this
table is bigger than it needs to be, let's try to fix that" and then set
a flag on the table, which a user could also explicitly set to give them
control over this process happening sooner or not happening at all, and
that would indicate to UPDATE to prefer earlier pages over the current
page or HOT updates, while VACUUM would also look at the flag to decide
if it should try to move tuples itself to earlier.  Then, once a VACUUM
has been able to come through and truncate the table, the flag would be
reset (maybe even if the user set it?  Or perhaps we'd have a way for
the user to indicate if they want VACUUM to reset the flag on truncation
or not).

Broadly speaking, I agree with the points made that we should be trying
to design a way for this to all happen both automatically and from a
background process without requiring the user to issue UPDATE statements
to make it happen- but I do like the idea of making it work with user
issued UPDATE statements if the right conditions are met, to avoid the
case of VACUUM getting in the way of user activity due to locking or
creating excess writes.

Thanks,

Stephen

Re: Disabling Heap-Only Tuples

Andres Freund <andres@anarazel.de> — 2023-09-21T22:33:35Z

Hi,

On 2023-09-19 14:50:13 -0400, Robert Haas wrote:
> 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.

I think some of the slowness was implementation related, rather than
fundamental. But more importantly, storage was something entirely different
back then than it is now.

> 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.

I think the problem of exploding WAL usage exists both for compaction being
done in VACUUM (or a dedicated command) and being done by backends. I think to
make using a facility like this realistic, you really need some form of rate
limiting, regardless of when compaction is performed. Even leaving WAL volume
aside, naively doing on-update compaction will cause lots of additional
contention on early FSM pages.

> 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.

Maybe - but are you going to have a significant bloat issue in that case?
Sure, if the updates update most of the table, youre are going to - but then
on-update compaction won't really be needed either, since you're going to run
out of space on pages on a regular basis.

Greetings,

Andres Freund

Re: Disabling Heap-Only Tuples

Andres Freund <andres@anarazel.de> — 2023-09-21T23:18:52Z

Hi,

On 2023-09-19 20:20:06 +0200, Matthias van de Meent wrote:
> Mostly agreed, but I think there's a pitfall here. You seem to assume
> we have a perfect oracle that knows the optimal data size, but we
> already know that our estimates can be significantly off. I don't
> quite trust the statistics enough to do any calculations based on the
> number of tuples in the relation. That also ignores the fact that we
> don't actually have any good information about the average size of the
> tuples in the table. So with current statistics, any automated "this
> is how large the table should be" decisions would result in an
> automated footgun, instead of the current patch's where the user has
> to decide to configure it to an explicit value.

The proposed patch already relies on the FSM being reasonably up2date, no? If
the FSM doesn't know about free space, the patch won't be able to place tuples
earlier in the relation. And if the FSM wrongly thinks there's lots of free
space, it'll make updates very expensive.

We obviously don't want to scan the whole FSM on an ongoing basis, but
visiting the top-level FSM pages and/or having vacuum/analyze update some
statistic based on a more thorough analysis of the FSM doesn't seem insane.

A related issue is that an accurate tuple size and accurate number of tuples
isn't really sufficient - if tuples are wider, there can be plenty space on
pages without updates being able to reuse that space. And the width of tuples
doesn't have to be evenly distributed, so a simple approach of calculating how
many tuples of the average width fit in a page and then using that to come up
with the overall number of required pages isn't necessarily accurate either.

> But about that: I'm not sure what the "footgun" is that you've
> mentioned recently?
> The issue with excessive bloat (when the local_update_limit is set too
> small and fillfactor is low) was fixed in the latest patch nearly
> three weeks ago, so the only remaining issue with misconfiguration is
> slower updates.

There seem to be plenty footguns. Just to name a few:

- The user has to determine a good value for local_update_limit, without
  really any good way of doing so.

- A "too low" local_update_limit will often succeed in finding some space in
  earlier pages, without that providing useful progress on compaction -
  e.g. because subsequently tuples on the earlier page will be updated and
  there's now no space anymore. Leading to index bloat.

- Configuring local_update_limit as a fixed size will be fragile when the data
  actually grows, leading to lots of pointless out-of-page updates.

I think a minimal working approach could be to have the configuration be based
on the relation size vs space known to the FSM. If the target block of an
update is higher than ((relation_size - fsm_free_space) *
new_reloption_or_guc), try finding the target block via the FSM, even if
there's space on the page.

> Sure, that's not great, but in my opinion not a
> "footgun": performance returns immediately after resetting
> local_update_limit, and no space was lost.

I think there's plenty ways to get pointless out-of-page updates, and
therefore index bloat, with local_update_limit as-proposed (see earlier in the
email). Once you have such pointless out-of-page updates, disabling
local_update_limit won't bring performance back immediately (space usage due
to index bloat and lookup performance issues due to the additional index
entries).

> Updating the reloption after relation truncation implies having the
> same lock as relation truncation, i.e. AEL (if the vacuum docs are to
> be believed).

Aside: We really need to get rid of the AEL for relation trunction - it's
quite painful for hot standby workloads...

Thomas has been talking about a patch (and perhaps even posted it) that adds
infrastructure providing a "shared smgrrelation". Once we have that I think we
could lower the required lock level for truncation, by having storing both the
filesystem size and the "valid" size. There's a few potential models:

- Vacuum truncation could lower the valid size in-memory, end its transaction,
  wait for concurrent accesses to the relation to finish, check if/where to
  the relation has been extended since, acquire the extension lock and
  truncate down to the "valid" size.

  The danger with that is that the necessary waiting can be long, threatening
  to starve autovacuum of workers.

- Instead of making a single vacuum wait, we could have one vacuum update the
  valid size of the relation and also store an xid horizon. Later vacuums can
  truncate the physical size down the to valid size if there are no snapshot
  conflicts with said xid anymore.

If we had such an shared smgrrel, we could also make relation extension a lot
more efficient, because we would not need to pin all pages that a relation
extension "covers" - the reason that we need to pin the to-be-extended-pages
is to prevent concurrent scans from reading "new" blocks while the extension
is in progress, as otherwise such a buffer can be dirtied and written out,
potentially leading to lost writes and other fun issues. But with the shared
smgrrel, we can store the size-currently-being-extended-to separately from the
filesystem size. If it's not allowed to read the block range covered by those
blocks into s_b, the race doesn't exist anymore.

Greetings,

Andres Freund

Re: Disabling Heap-Only Tuples

Laurenz Albe <laurenz.albe@cybertec.at> — 2024-03-13T13:27:27Z

On Thu, 2023-09-21 at 16:18 -0700, Andres Freund wrote:
> I think a minimal working approach could be to have the configuration be based
> on the relation size vs space known to the FSM. If the target block of an
> update is higher than ((relation_size - fsm_free_space) *
> new_reloption_or_guc), try finding the target block via the FSM, even if
> there's space on the page.

That sounds like a good way forward.

The patch is in state "needs review", but it got review.  I'll change it to
"waiting for author".

Yours,
Laurenz Albe

Re: Disabling Heap-Only Tuples

Matthias van de Meent <boekewurm+postgres@gmail.com> — 2024-03-15T11:06:31Z

On Wed, 13 Mar 2024 at 14:27, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> On Thu, 2023-09-21 at 16:18 -0700, Andres Freund wrote:
> > I think a minimal working approach could be to have the configuration be based
> > on the relation size vs space known to the FSM. If the target block of an
> > update is higher than ((relation_size - fsm_free_space) *
> > new_reloption_or_guc), try finding the target block via the FSM, even if
> > there's space on the page.
>
> That sounds like a good way forward.
>
> The patch is in state "needs review", but it got review.  I'll change it to
> "waiting for author".

Then I'll withdraw this patch as I don't currently have (nor expect to
have anytime soon) the bandwitdh or expertise to rewrite this patch to
include a system that calculates the free space available in a
relation.

I've added a TODO item in the UPDATE section with a backlink to this
thread so the discussion isn't lost.

-Matthias

Re: Disabling Heap-Only Tuples

James Locke <james.locke.uk@gmail.com> — 2026-05-08T12:25:50Z

On Fri, May 8, 2026 at 12:45 PM Matthias van de Meent <
boekewurm+postgres@gmail.com> wrote:
>
> On Wed, 13 Mar 2024 at 14:27, Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
> >
> > On Thu, 2023-09-21 at 16:18 -0700, Andres Freund wrote:
> > > I think a minimal working approach could be to have the configuration
be based
> > > on the relation size vs space known to the FSM. If the target block
of an
> > > update is higher than ((relation_size - fsm_free_space) *
> > > new_reloption_or_guc), try finding the target block via the FSM, even
if
> > > there's space on the page.
> >
> > That sounds like a good way forward.
> >
> > The patch is in state "needs review", but it got review.  I'll change
it to
> > "waiting for author".
>
> Then I'll withdraw this patch as I don't currently have (nor expect to
> have anytime soon) the bandwitdh or expertise to rewrite this patch to
> include a system that calculates the free space available in a
> relation.
>
> I've added a TODO item in the UPDATE section with a backlink to this
> thread so the discussion isn't lost.

Attached is a POC to enable userland table compaction: A top-level COMPACT
command that performs the relocation directly in the server, with a
stripped-down heap_relocate primitive instead of full UPDATE, and a
built-in prune-and-truncate pass so it runs to a useful end state in one
command.  Rough comparison:

  Approach           Driver          Disk overhead   One shot
  -------------------------------------------------------------
  VACUUM FULL        server          ~live size      yes
  REPACK             server          ~live size      yes
  local_update_limit + UPDATE loop   user            ~1 page         no
  COMPACT (this POC) server          ~1 page         yes

Design summary:

  - heap_relocate: low-level "move this tuple to that page" primitive,
    sibling of heap_update.  Skips HOT, modified-attr analysis, toast,
    and replica-identity extraction (data is byte-identical).
    Concurrency: handles key-share lockers and multixacts via the
    same logic as heap_update (no-wait variant).  Skips tuples held
    under stronger locks or being updated by a live transaction; the
    caller revisits them on a future run.

  - XLH_UPDATE_RELOCATED: new flag on xl_heap_update.  Replay treats
    these as ordinary updates; logical decoding's DecodeUpdate
    filters them out so subscribers see no phantom UPDATE events.

  - lazy_compact_heap: new internal vacuum phase, walks pages
    high-to-low, snapshots live tuples, calls heap_relocate with
    FSM-chosen low-numbered targets, and inserts matching index
    entries via index_insert(UNIQUE_CHECK_NO).

  - COMPACT command: runs three vacuum() invocations per relation
    (compact, then prune+truncate in a fresh xact, then optional
    analyze).  No AccessExclusiveLock except briefly during truncate.

How it engages with the concerns previously raised on this thread:

  - Index bloat: confirmed and unavoidable -- every
    relocation creates a new index entry, the old one stays until
    the next ordinary vacuum reaps it.  For an index-heavy table,
    REINDEX CONCURRENTLY after COMPACT is the recommended remedy.
    Documented in compact.sgml.

  - Tuning difficulty: no parameter to tune.  The user
    runs COMPACT and the server figures out what to move where.  At
    the cost of being less flexible than local_update_limit (you
    can't, e.g., compact only the top 50 GB of a 100 GB table, you
    compact what the FSM and the high-water heuristic decide to
    compact).

  - Page selection: the compaction loop only places
    tuples on pages strictly lower than the one it's draining, so
    progress is monotonic and tail pages will be empty if compaction
    succeeds (the truncation pass then reclaims them).

  - Low-fillfactor interaction: not directly relevant
    here. COMPACT doesn't change UPDATE behaviour generally, only
    runs an explicit relocation loop.  fillfactor is honoured when
    picking targets.

 This POC is intended as an end-to-end demonstration that the use case can
be served by a server-side automated command rather than a userland UPDATE
loop.  All five patches build cleanly; make check-world
    passes; isolation spec covers concurrent FOR UPDATE / UPDATE /  FOR KEY
SHARE / multixact / REPEATABLE READ readers.

  - Isn't: production-ready.  Notable rough edges:

      * The three-pass vacuum() structure inside ExecCompact is
        correct but ugly.  Folding the prune-and-truncate pass back
        into a single vacuum() invocation that retries with a fresh
        snapshot would be better.  This requires letting vacuum_rel
        commit and start a new transaction mid-flight; doable but
        invasive.

      * Index updates inside lazy_compact_heap call index_insert
        directly with UNIQUE_CHECK_NO.  An earlier attempt to use
        ExecInsertIndexTuples crashed because the executor
        scaffolding (es_snapshot, ECxt, range table) isn't fully
        constructible from outside the executor.  Worth a closer
        look from someone who knows that area.

      * contrib/pg_compact_test (in 0005) exposes the lower
        primitives to SQL for development testing.  Not really
        appropriate for in-tree.  Useful for review.

The two approaches are not exclusive.  local_update_limit serves users
who want a knob and don't want a new command; COMPACT serves users
who want one-shot behaviour.  Posting this so the current thread can
consider whether server-side automation is on the table at all,
before settling on the reloption design.

Patch series structure:

  0001 -- New hio primitive, RelationGetSpecificBufferForTuple
  0002 -- New heap primitive, heap_relocate (+ XLH_UPDATE_RELOCATED)
  0003 -- New internal vacuum phase, lazy_compact_heap
  0004 -- New COMPACT command (grammar, executor, docs)
  0005 -- Tests (regression, isolation, contrib test)

Benchmark on a 24 MB / 100K-row workload (90% deleted at the head,
then VACUUMed):

  Strategy        Mean WAL    Mean time
  COMPACT         2.97 MB     51 ms
  VACUUM FULL     2.39 MB     50 ms
  REPACK          2.43 MB     43 ms

COMPACT writes ~24% more WAL than the rewrite-based strategies (one
cross-page heap update + index inserts per relocated tuple, vs. a
single bulk relation rewrite).  Final size is identical for all
three.  Peak extra disk: ~1 page for COMPACT, ~live-data-size for
the others.

James

Re: Disabling Heap-Only Tuples

Alvaro Herrera <alvherre@alvh.no-ip.org> — 2026-05-08T13:00:06Z

Hello James,

On 2026-May-08, James Locke wrote:

> Attached is a POC to enable userland table compaction: A top-level COMPACT
> command that performs the relocation directly in the server, with a
> stripped-down heap_relocate primitive instead of full UPDATE, and a
> built-in prune-and-truncate pass so it runs to a useful end state in one
> command.

How does this implementation handle the case of a seqscan in the middle
of scanning the table, which has already skipped the destination page
and not yet the page from where the table is to be removed?  There needs
to be a way to distinguish which of these to show (it must be exactly
one), and you didn't mention this in your description.

Thanks

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/

Re: Disabling Heap-Only Tuples

James Locke <james.locke.uk@gmail.com> — 2026-05-08T14:13:44Z

On Fri, May 8, 2026 at 2:00 PM Alvaro Herrera <alvherre@alvh.no-ip.org>
wrote:
>
> Hello James,
>
> On 2026-May-08, James Locke wrote:
>
> > Attached is a POC to enable userland table compaction: A top-level
COMPACT
> > command that performs the relocation directly in the server, with a
> > stripped-down heap_relocate primitive instead of full UPDATE, and a
> > built-in prune-and-truncate pass so it runs to a useful end state in one
> > command.
>
> How does this implementation handle the case of a seqscan in the middle
> of scanning the table, which has already skipped the destination page
> and not yet the page from where the table is to be removed?  There needs
> to be a way to distinguish which of these to show (it must be exactly
> one), and you didn't mention this in your description.

It's the same invariant a cross-page UPDATE relies on, and heap_relocate
inherits it because the on-disk and WAL record are identical to a regular
update.

heap_relocate sets the source's xmax and the new tuple's xmin to the same
xid (the relocator's), and both writes go through one log_heap_update AL
record. So when HeapTupleSatisfiesMVCC asks "is this visible" for either
tuple, it ends up asking the same XidInMVCCSnapshot(R, snap) question
against the eqscan's snapshot; once for the destination's xmin and once for
the source's xmax. Same xid, same answer.

seqscan reads block 5 first and sees no live tuple there, either because
the relocation hasn't happened yet, or it has but R is still in the
snapshot's xip list so xmin reads as in-progress. Then COMPACT commits
cluster-wide. Seqscan reaches block 200 still using the snapshot it took at
scan start, which treats R the same way it did at block 5; snapshots don't
change mid-scan. So either both pages treated R as committed (block 5
returned the row already, block 200 now sees the source as dead) or both
treated it as running (block 5 saw nothing, block 200 returns the source).
Exactly one.

The page-level atomicity comes from log_heap_update registering both
buffers in one record and the modifications happening inside one
RIT_SECTION with exclusive content locks on both pages; concurrent
share-locking readers can't see half-applied state.

James

Re: Disabling Heap-Only Tuples

Alvaro Herrera <alvherre@alvh.no-ip.org> — 2026-05-08T14:35:50Z

On 2026-May-08, James Locke wrote:

> It's the same invariant a cross-page UPDATE relies on, and heap_relocate
> inherits it because the on-disk and WAL record are identical to a regular
> update.

Ah, that makes sense, and I understand why you say this would cause
index bloat.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"The Gord often wonders why people threaten never to come back after they've
been told never to return" (www.actsofgord.com)