Feedback-ID: i76984098:Fastmail
Message-ID: <3637977d-554d-4ebe-a8fc-3a4da49e5664@aklaver.com>
Date: Thu, 20 Jun 2024 10:06:16 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Autovacuum, dead tuples and bloat
To: "Shenavai, Manuel" <manuel.shenavai@sap.com>,
 pgsql-general <pgsql-general@postgresql.org>
References: <AM9PR02MB7410603C5F467DED657D5330E8C82@AM9PR02MB7410.eurprd02.prod.outlook.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <AM9PR02MB7410603C5F467DED657D5330E8C82@AM9PR02MB7410.eurprd02.prod.outlook.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/3637977d-554d-4ebe-a8fc-3a4da49e5664%40aklaver.com>
Precedence: bulk

On 6/20/24 09:46, Shenavai, Manuel wrote:
> Hi everyone,
> 
> we can see in our database, that the DB is 200GB of size, with 99% 
> bloat. After vacuum full the DB decreases to 2GB.
> 
> DB total size: 200GB
> 
> DB bloat: 198 GB >
> DB non-bloat: 2GB
> 
> We further see, that during bulk updates (i.e. a long running 
> transaction), the DB is still growing, i.e. the size of the DB growth by 
> +20GB after the bulk updates.

How soon after the updates did you measure the above?

> 
> My assumption is, that after an autovacuum, the 99% bloat should be 
> available for usage again. But the DB size would stay at 200GB. In our 
> case, I would only expect a growth of the DB, if the bulk-updates exceed 
> the current DB size (i.e. 220 GB).

Was the transaction completed(commit/rollback)?

Are there other transactions using the table or tables?

> 
> How could I verify my assumption?
> 
> I think of two possibilities:
> 
>  1. My assumption is wrong and for some reason the dead tuples are not
>     cleaned so that the space cannot be reused
>  2. The bulk-update indeed exceeds the current DB size. (Then the growth
>     is expected).
> 
> Can you help me to verify these assumptions? Are there any statistics 
> available that could help me with my verification?

Use:

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

Select the rows that cover the table or tables involved. Look at the 
vacuum/autovacuum/analyze fields.

> 
> Thanks in advance &
> 
> Best regards,
> 
> Manuel
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com