Re: Autovacuum, dead tuples and bloat

On Thu, Jun 20, 2024 at 12:47 PM Shenavai, Manuel <manuel(dot)shenavai(at)sap(dot)com>
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.
>
>
>
> 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).
>
>
That's also my understanding of how vacuum works.

Note: I disable autovacuum before bulk modifications, manually VACUUM
ANALYZE and then reenable autovacuum. That way, autovacuum doesn't jump in
the middle of what I'm doing.

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?
>

I've got a weekly process that deletes all records older than N days from a
set of tables.
db=# ALTER TABLE t1 SET (autovacuum_enabled = off);
db=# ALTER TABLE t2 SET (autovacuum_enabled = off);
db=# ALTER TABLE t3 SET (autovacuum_enabled = off);
db=# DELETE FROM t1 WHERE created_on < (CURRENT_TIMESTAMP - INTERVAL '90
DAY');
db=# DELETE FROM t2 WHERE created_on < (CURRENT_TIMESTAMP - INTERVAL '90
DAY');
db=# DELETE FROM t3 WHERE created_on < (CURRENT_TIMESTAMP - INTERVAL '90
DAY');
$ vacuumdb --jobs=3 -t t1 -t t2 -t t3
db=# ALTER TABLE t1 SET (autovacuum_enabled = on);
db=# ALTER TABLE t2 SET (autovacuum_enabled = on);
db=# ALTER TABLE t3 SET (autovacuum_enabled = on);

pgstattuple shows that that free percentage stays pretty constant. That
seems to be what you're asking about.