On Thu, 2023-07-06 at 22:18 +0200, Matthias van de Meent wrote: > On Wed, 5 Jul 2023 at 19:55, Thom Brown <t...@linux.com> wrote: > > > > On Wed, 5 Jul 2023 at 18:05, Matthias van de Meent > > <boekewurm+postg...@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