Re: Autovacuum, dead tuples and bloat

Στις 20/6/24 19:46, ο/η Shenavai, Manuel έγραψε:
>
> 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.
>
> 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).
>
> 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).
>
Your only assumption should be the official manual, and other material
such as books, articles from reputable sources, even reading the source
as a last resort could be considered.

For starters : do you have autovacuum enabled ? If not, then enable this.

Then monitor for vacuum via pg_stat_user_tables, locate the tables that
you would expect vacuum to have happened but did not, then consider
autovacuum tuning.

Watch the logs for lines such as :

<N> dead row versions cannot be removed yet, oldest xmin: <some xid>

those are held from being marked as removed, due to being visible by
long running transactions. Monitor for those transactions.

You have to monitor (if this is the case) about autovacuum being killed
and not allowed to do its job.

> Can you help me to verify these assumptions? Are there any statistics
> available that could help me with my verification?
>
> Thanks in advance &
>
> Best regards,
>
> Manuel
>
--
Achilleas Mantzios
IT DEV - HEAD
IT DEPT
Dynacom Tankers Mgmt (as agents only)