Re: Autovacuum, dead tuples and bloat

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(dot)klaver(at)aklaver(dot)com