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.klaver@xxxxxxxxxxx
