Re: Autovacuum not functioning for large tables but it is working for few other small tables.

On 12/16/20 12:55 PM, M Tarkeshwar Rao wrote:
> Hi all,
>
> We have facing some discrepancy in Postgresql database related to the
> autovacuum functionality.
>
> By default autovacuum was enable on Postgres which is used to remove the
> dead tuples from the database.
>
> We have observed autovaccum cleaning dead rows from *table_A* but same
> was not functioning correctly for *table_B* which have a large
> size(100+GB) in comparision to table_A.
>
> All the threshold level requirements for autovacuum was meet and there
> are about Million’s of  dead tuples but autovacuum was unable to clear
> them, which cause performance issue on production server.
>
> Is autovacuum not working against large sized tables or Is there any
> parameters which  need to set to make autovacuum functioning?
>

No, autovacuum should work for tables with any size. The most likely
explanation is that the rows in the large table were deleted more
recently and there is a long-running transaction blocking the cleanup.
Or maybe not, hard to say with the info you provided.

A couple suggestions:

1) enable logging for autovacuum by setting

log_autovacuum_min_duration = 10ms (or similar low value)

2) check that the autovacuum is actually executed on the large table
(there's last_autovacuum in pg_stat_all_tables)

3) try running VACUUM VERBOSE on the large table, it may tell you that
the rows can't be cleaned up yet.

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company