Re: Postgres auto vacuum - Disable

Hi Again,

Is there any difference in the way vacuum is handled in postgres9.6 and postgres12.9, We are noticing the below issue of waiting process only after upgrading to postgres12.5

$ ps -ef | grep 'waiting'
postgres 8833 62646 0 Jul28 ? 00:00:00 postgres: postgres cgms [local] VACUUM waiting
postgres 18437 62646 0 Jul27 ? 00:00:00 postgres: postgres cgms [local] VACUUM waiting

Regards,
Karthik

From: Julien Rouhaud <rjuju123(at)gmail(dot)com>
Date: Monday, 7 November 2022 at 7:06 PM
To: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
Cc: Karthik Jagadish (kjagadis) <kjagadis(at)cisco(dot)com>, Dave Page <dpage(at)pgadmin(dot)org>, pgsql-hackers(at)postgresql(dot)org <pgsql-hackers(at)postgresql(dot)org>, Chandruganth Ayyavoo Selvam (chaayyav) <chaayyav(at)cisco(dot)com>, Prasanna Satyanarayanan (prassaty) <prassaty(at)cisco(dot)com>, Jaganbabu M (jmunusam) <jmunusam(at)cisco(dot)com>, Joel Mariadasan (jomariad) <jomariad(at)cisco(dot)com>
Subject: Re: Postgres auto vacuum - Disable
Hi,

On Mon, Nov 07, 2022 at 02:22:56PM +0100, Laurenz Albe wrote:
> On Mon, 2022-11-07 at 12:12 +0000, Karthik Jagadish (kjagadis) wrote:
> > I have follow-up question where the vacuum process is waiting and not doing it’s job.
> > When we grep on waiting process we see below output. Whenever we see this we notice
> > that the vacuum is not happening and the system is running out of space.
> >
> > [root(at)zpah0031 ~]# ps -ef | grep 'waiting'
> > postgres 8833 62646 0 Jul28 ? 00:00:00 postgres: postgres cgms [local] VACUUM waiting
> > postgres 18437 62646 0 Jul27 ? 00:00:00 postgres: postgres cgms [local] VACUUM waiting
> >
> >
> > What could be the reason as to why the vacuum is not happening? Is it because some lock is
> > present in the table/db or any other reason?
>
> Look in "pg_stat_activity". I didn't check, but I'm sure it's the intentional break
> configured with "autovacuum_vacuum_cost_delay". Reduce that parameter for more
> autovacuum speed.

Really? An autovacuum should be displayed as "autovacuum worker", this looks
like plain backends to me, where an interactive VACUUM has been issued and is
waiting on a heavyweight lock.