Re: Allow single table VACUUM in transaction block

On Fri, 18 Nov 2022 at 18:26, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > On Fri, Nov 18, 2022 at 7:04 AM Simon Riggs
> > <simon(dot)riggs(at)enterprisedb(dot)com> wrote:
> >> So if consistency is also a strong requirement, then maybe we should
> >> make that new command the default, i.e. make VACUUM always just a
> >> request to vacuum in background. That way it will be consistent.
>
> > Since one fairly common reason for running vacuum in the foreground is
> > needing to vacuum a table when all autovacuum workers are busy, or
> > when they are vacuuming it with a cost limit and it needs to get done
> > sooner, I think this would surprise a lot of users in a negative way.
>
> It would also break a bunch of our regression tests, which expect a
> VACUUM to complete immediately.
>
> >> Can we at least have a vacuum_runs_in_background = on | off, to allow
> >> users to take advantage of this WITHOUT needing to rewrite all of
> >> their scripts?
>
> > I'm not entirely convinced that's a good idea, but happy to hear what
> > others think.
>
> I think the answer to that one is flat no. We learned long ago that GUCs
> with significant semantic impact on queries are a bad idea. For example,
> if a user issues VACUUM expecting behavior A and she gets behavior B
> because somebody changed the postgresql.conf entry, she won't be happy.
>
> Basically, I am not buying Simon's requirement that this be transparent.
> I think the downsides would completely outweigh whatever upside there
> may be (and given the shortage of prior complaints, I don't think the
> upside is very large).

Just to say I'm happy with that decision and will switch to the
request for a background vacuum.

--
Simon Riggs http://www.EnterpriseDB.com/