Re: Allow single table VACUUM in transaction block

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

regards, tom lane