Subject: Re: Autovacuum not functioning for large tables but it is working for
 few other small tables.
To: M Tarkeshwar Rao <m.tarkeshwar.rao@ericsson.com>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Cc: Neeraj Gupta G <neeraj.g.gupta@ericsson.com>,
 Atul Parashar <atul.parashar@ericsson.com>,
 Shishir Singh <shishir.singh@globallogic.com>,
 Ankit Sharma <ankit.sharma10@globallogic.com>
References: 
 <AM6PR0702MB3783F4CFF71E3FD27E9C9AC0AEC50@AM6PR0702MB3783.eurprd07.prod.outlook.com>
 <AM6PR0702MB3783AE08F338E43BE1D82914AEC50@AM6PR0702MB3783.eurprd07.prod.outlook.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
Message-ID: <cdfe8ced-45b9-2607-e1e5-98ee45afd694@enterprisedb.com>
Date: Thu, 17 Dec 2020 02:46:16 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.5.0
MIME-Version: 1.0
In-Reply-To: 
 <AM6PR0702MB3783AE08F338E43BE1D82914AEC50@AM6PR0702MB3783.eurprd07.prod.outlook.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk

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