On Thu, Jun 20, 2024 at 12:47 PM Shenavai, Manuel <manuel.shena...@sap.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.
