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