public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: Shenavai, Manuel <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: Autovacuum, dead tuples and bloat
Date: Thu, 20 Jun 2024 10:06:16 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <AM9PR02MB7410603C5F467DED657D5330E8C82@AM9PR02MB7410.eurprd02.prod.outlook.com>
References: <AM9PR02MB7410603C5F467DED657D5330E8C82@AM9PR02MB7410.eurprd02.prod.outlook.com>

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
[email protected]

view thread (11+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Autovacuum, dead tuples and bloat
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox