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

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. 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.

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.

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 table is vacuumed regularly, surely it can’t accumulate a lot of dead rows, right?" You might benefit from reading the entire article rather than skipping down to that section.

I hope it helps....

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company