Re: We should stop telling users to "vacuum that database in single-user mode"

On Tue, Mar 2, 2021 at 10:07 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
> On Wed, 3 Mar 2021 at 01:12, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> >
> > On Tue, Mar 2, 2021 at 7:52 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> > > I have seen it happen that an instance has a vacuum_cost_limit set and
> > > someone did start the database in single-user mode, per the advice of
> > > the error message only to find that the VACUUM took a very long time
> > > due to the restrictive cost limit. I struggle to imagine why anyone
> > > wouldn't want the vacuum to run as quickly as possible in that
> > > situation.
> >
> > Multiple instances running on the same hardware and only one of them
> > being in trouble?
>
> You might be right. I'm not saying it's a great idea but thought it
> was worth considering.
>
> We could turn to POLA and ask; what would you be more surprised at; 1)
> Your database suddenly using more I/O than it had been previously, or;
> 2) Your database no longer accepting DML.

I think we misunderstand each other. I meant this only as a comment
about the idea of ignoring the cost limit in single user mode -- that
is, it's a reason to *want* vacuum to not run as quickly as possible
in single user mode. I should've trimmed the email better.

I agree with your other idea, that of kicking in a more aggressive
autovacuum if it's not dealing with things fast enough. Maybe even on
an incremental way - that is run with the default, then at another
threshold drop them to half, and at yet another threshold drop them to
0. I agree that pretty much anything is better than forcing the user
into single user mode.

--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/