Feedback-ID: i76984098:Fastmail
Message-ID: <53da5a20-ca35-40fa-85a3-c59d4302a512@aklaver.com>
Date: Fri, 21 Jun 2024 13:39:13 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Autovacuum, dead tuples and bloat
To: "Shenavai, Manuel" <manuel.shenavai@sap.com>,
 Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>,
 "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
References: <AM9PR02MB7410603C5F467DED657D5330E8C82@AM9PR02MB7410.eurprd02.prod.outlook.com>
 <2f2aafe6-3e29-4305-a279-1b3d56e9389e@cloud.gatewaynet.com>
 <AM9PR02MB7410AA1DEA442AED52FBB5E9E8C92@AM9PR02MB7410.eurprd02.prod.outlook.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <AM9PR02MB7410AA1DEA442AED52FBB5E9E8C92@AM9PR02MB7410.eurprd02.prod.outlook.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/53da5a20-ca35-40fa-85a3-c59d4302a512%40aklaver.com>
Precedence: bulk

On 6/21/24 12:31, Shenavai, Manuel wrote:
> Hi,
> 
> Thanks for the suggestions. I found the following details to our 
> autovacuum (see below). The related toast-table of my table shows some 
> logs related the vacuum. This toast seems to consume all the data 
> (27544451 pages * 8kb ≈ 210GB )

Those tuples(pages) are still live per the pg_stat entry in your second 
post:

"n_dead_tup": 12,
"n_live_tup": 819294

So they are needed.

Now the question is why are they needed?

1) All transactions that touch that table are done and that is the data 
that is left.

2) There are open transactions that still need to 'see' that data and 
autovacuum cannot remove them yet. Take a look at:

pg_stat_activity:

https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW

and

pg_locks

https://www.postgresql.org/docs/current/view-pg-locks.html

to see if there is a process holding that data open.

> 
> Any thoughts on this?
> 
> Best regards,
> Manuel
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com