Re: do only critical work during single-user vacuum?

On 1/18/22, 9:47 PM, "Masahiko Sawada" <sawada(dot)mshk(at)gmail(dot)com> wrote:
> IIUC what we want to do here are two things: (1) select only old
> tables and (2) set INDEX_CLEANUP = off, TRUNCATE = off, and FREEZE =
> on. VACUUM LIMIT statement does both things at the same time. Although
> I’m concerned a bit about its flexibility, it’s a reasonable solution.
>
> On the other hand, it’s probably also useful to do either one thing in
> some cases. For instance, having a selector for (1) would be useful,
> and having a new option like FAST_FREEZE for (2) would also be useful.
> Given there is already a way for (2) (it does not default though), I
> think it might also be a good start inventing something for (1). For
> instance, a selector for VACUUM statement I came up with is:
>
> VACUUM (verbose on) TABLES WITH (min_xid_age = 1600000000);
> or
> VACUUM (verbose on) TABLES WITH (min_age = failsafe_limit);
>
> We can expand it in the future to select tables by, for example, dead
> tuple ratio, size, etc.
>
> It's a random thought but maybe worth considering.

That's an interesting idea. A separate selector clause could also
allow users to choose how they interacted (e.g., should the options be
OR'd or AND'd).

Nathan