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:
My assumption is wrong and for some reason the dead tuples are not cleaned so that the space cannot be reused
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.