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 <rjuju...@gmail.com> Date: Monday, 7 November 2022 at 7:06 PM To: Laurenz Albe <laurenz.a...@cybertec.at> Cc: Karthik Jagadish (kjagadis) <kjaga...@cisco.com>, Dave Page <dp...@pgadmin.org>, pgsql-hack...@postgresql.org <pgsql-hack...@postgresql.org>, Chandruganth Ayyavoo Selvam (chaayyav) <chaay...@cisco.com>, Prasanna Satyanarayanan (prassaty) <prass...@cisco.com>, Jaganbabu M (jmunusam) <jmunu...@cisco.com>, Joel Mariadasan (jomariad) <jomar...@cisco.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@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.
