Re: Disabling Heap-Only Tuples

On Wed, 19 Jul 2023 at 14:58, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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(at)linux(dot)com> wrote:
> > >
> > > On Wed, 5 Jul 2023 at 18:05, Matthias van de Meent
> > > <boekewurm+postgres(at)gmail(dot)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