Re: Disabling Heap-Only Tuples

On Mon, 28 Aug 2023 at 17:14, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Mon, Aug 28, 2023 at 10:52 AM Matthias van de Meent
> <boekewurm+postgres(at)gmail(dot)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)