Thread: Tables not getting vacuumed in postgres
Hi,
We have a NMS application where we are using postgres as database, what we are noticing is that vacuuming is not happening for certain tables for 2-3 days and eventually the table bloats and disk space is running out.
What could be the reason for auto vacuuming not happening for certain tables?
Autovacuum is enabled
Regards,
Karthik
On Tue, Nov 8, 2022 at 5:00 PM Karthik Jagadish (kjagadis) <kjagadis@cisco.com> wrote: > > Hi, > > We have a NMS application where we are using postgres as database, what we are noticing is that vacuuming is not happeningfor certain tables for 2-3 days and eventually the table bloats and disk space is running out. > > What could be the reason for auto vacuuming not happening for certain tables? > Check if there is any long-running or prepared transaction. Regards, Amul
Hi,
Thanks for the response.
But what I understand that insert update and delete would still work and will not interfere with vacuuming process. Yes we do perform a lot of updates on that particular table which is not vacuuming. Does it mean that it waiting for the lock to be released?
Regards,
Karthik
From: Amul Sul <sulamul@gmail.com>
Date: Tuesday, 8 November 2022 at 5:38 PM
To: Karthik Jagadish (kjagadis) <kjagadis@cisco.com>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>, Prasanna Satyanarayanan (prassaty) <prassaty@cisco.com>, Chandruganth Ayyavoo Selvam (chaayyav) <chaayyav@cisco.com>, Jaganbabu M (jmunusam) <jmunusam@cisco.com>
Subject: Re: Tables not getting vacuumed in postgres
On Tue, Nov 8, 2022 at 5:00 PM Karthik Jagadish (kjagadis)
<kjagadis@cisco.com> wrote:
>
> Hi,
>
> We have a NMS application where we are using postgres as database, what we are noticing is that vacuuming is not happening for certain tables for 2-3 days and eventually the table bloats and disk space is running out.
>
> What could be the reason for auto vacuuming not happening for certain tables?
>
Check if there is any long-running or prepared transaction.
Regards,
Amul
On Tue, Nov 8, 2022 at 6:11 PM Karthik Jagadish (kjagadis) <kjagadis@cisco.com> wrote: > > Hi, > > > > Thanks for the response. > > > > But what I understand that insert update and delete would still work and will not interfere with vacuuming process. Yeswe do perform a lot of updates on that particular table which is not vacuuming. Does it mean that it waiting for the lockto be released? > Well, yes, that won't interfere but the primary job of autovacuum is to remove the bloat, if the dead tuple(s) is visible to any transaction, then not going to remove that. > > > Regards, > > Karthik > > > > From: Amul Sul <sulamul@gmail.com> > Date: Tuesday, 8 November 2022 at 5:38 PM > To: Karthik Jagadish (kjagadis) <kjagadis@cisco.com> > Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>, Prasanna Satyanarayanan (prassaty) <prassaty@cisco.com>,Chandruganth Ayyavoo Selvam (chaayyav) <chaayyav@cisco.com>, Jaganbabu M (jmunusam) <jmunusam@cisco.com> > Subject: Re: Tables not getting vacuumed in postgres > > On Tue, Nov 8, 2022 at 5:00 PM Karthik Jagadish (kjagadis) > <kjagadis@cisco.com> wrote: > > > > Hi, > > > > We have a NMS application where we are using postgres as database, what we are noticing is that vacuuming is not happeningfor certain tables for 2-3 days and eventually the table bloats and disk space is running out. > > > > What could be the reason for auto vacuuming not happening for certain tables? > > > > Check if there is any long-running or prepared transaction. > > Regards, > Amul
I didn’t get your point dead tuples are visible to transaction means? Vacuuming job is to remove dead tuples right?
Regards,
Karthik
From: Amul Sul <sulamul@gmail.com>
Date: Tuesday, 8 November 2022 at 6:39 PM
To: Karthik Jagadish (kjagadis) <kjagadis@cisco.com>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>, Prasanna Satyanarayanan (prassaty) <prassaty@cisco.com>, Chandruganth Ayyavoo Selvam (chaayyav) <chaayyav@cisco.com>, Jaganbabu M (jmunusam) <jmunusam@cisco.com>
Subject: Re: Tables not getting vacuumed in postgres
On Tue, Nov 8, 2022 at 6:11 PM Karthik Jagadish (kjagadis)
<kjagadis@cisco.com> wrote:
>
> Hi,
>
>
>
> Thanks for the response.
>
>
>
> But what I understand that insert update and delete would still work and will not interfere with vacuuming process. Yes we do perform a lot of updates on that particular table which is not vacuuming. Does it mean that it waiting for the lock to be released?
>
Well, yes, that won't interfere but the primary job of autovacuum is
to remove the bloat, if the dead tuple(s) is visible to any
transaction, then not going to remove that.
>
>
> Regards,
>
> Karthik
>
>
>
> From: Amul Sul <sulamul@gmail.com>
> Date: Tuesday, 8 November 2022 at 5:38 PM
> To: Karthik Jagadish (kjagadis) <kjagadis@cisco.com>
> Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>, Prasanna Satyanarayanan (prassaty) <prassaty@cisco.com>, Chandruganth Ayyavoo Selvam (chaayyav) <chaayyav@cisco.com>, Jaganbabu M (jmunusam) <jmunusam@cisco.com>
> Subject: Re: Tables not getting vacuumed in postgres
>
> On Tue, Nov 8, 2022 at 5:00 PM Karthik Jagadish (kjagadis)
> <kjagadis@cisco.com> wrote:
> >
> > Hi,
> >
> > We have a NMS application where we are using postgres as database, what we are noticing is that vacuuming is not happening for certain tables for 2-3 days and eventually the table bloats and disk space is running out.
> >
> > What could be the reason for auto vacuuming not happening for certain tables?
> >
>
> Check if there is any long-running or prepared transaction.
>
> Regards,
> Amul
> On Nov 8, 2022, at 5:21 AM, Karthik Jagadish (kjagadis) <kjagadis@cisco.com> wrote: > > I didn’t get your point dead tuples are visible to transaction means? Vacuuming job is to remove dead tuples right? Please see https://www.2ndquadrant.com/en/blog/when-autovacuum-does-not-vacuum/ for more information about your question. Specifically, you might look at the third section down, "Long transactions", which starts with "So, if the tableis vacuumed regularly, surely it can’t accumulate a lot of dead rows, right?" You might benefit from reading the entirearticle rather than skipping down to that section. I hope it helps.... — Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company