Обсуждение: How did VACUUM ANALYZE reclaim large TOAST bloat at disk level in PostgreSQL 16?
How did VACUUM ANALYZE reclaim large TOAST bloat at disk level in PostgreSQL 16?
От
pramod gupta
Дата:
Hello Everyone,
We have a table with a total size of ~628 GB, out of which ~601 GB was TOAST data.
After running VACUUM ANALYZE on a weekly basis, the table size reduced significantly to ~109 GB, indicating a large amount of bloat removal.
I would like to understand:
How was VACUUM ANALYZE able to reclaim such a large amount of space, especially for TOAST data?
Under what conditions does PostgreSQL reclaim disk space without requiring VACUUM FULL or CLUSTER?
Is this behavior expected in PostgreSQL 16, particularly for heavily updated or deleted TOASTed columns?
Any insights or documentation references would be greatly appreciated.
PostgreSQL version: 16
Thanks in advance.
Pramod Gupta
We have a table with a total size of ~628 GB, out of which ~601 GB was TOAST data.
After running VACUUM ANALYZE on a weekly basis, the table size reduced significantly to ~109 GB, indicating a large amount of bloat removal.
I would like to understand:
How was VACUUM ANALYZE able to reclaim such a large amount of space, especially for TOAST data?
Under what conditions does PostgreSQL reclaim disk space without requiring VACUUM FULL or CLUSTER?
Is this behavior expected in PostgreSQL 16, particularly for heavily updated or deleted TOASTed columns?
Any insights or documentation references would be greatly appreciated.
PostgreSQL version: 16
Thanks in advance.
Pramod Gupta
Re: How did VACUUM ANALYZE reclaim large TOAST bloat at disk level in PostgreSQL 16?
От
Ron Johnson
Дата:
On Mon, Dec 29, 2025 at 10:53 AM pramod gupta <mail2sony2010@gmail.com> wrote:
Hello Everyone,
We have a table with a total size of ~628 GB, out of which ~601 GB was TOAST data.
After running VACUUM ANALYZE on a weekly basis, the table size reduced significantly to ~109 GB, indicating a large amount of bloat removal.
I would like to understand:
How was VACUUM ANALYZE able to reclaim such a large amount of space, especially for TOAST data?
Under what conditions does PostgreSQL reclaim disk space without requiring VACUUM FULL or CLUSTER?
Is this behavior expected in PostgreSQL 16, particularly for heavily updated or deleted TOASTed columns?
Any insights or documentation references would be greatly appreciated.
PostgreSQL version: 16
See the TRUNCATE option:
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
Re: How did VACUUM ANALYZE reclaim large TOAST bloat at disk level in PostgreSQL 16?
От
Christoph Moench-Tegeder
Дата:
## pramod gupta (mail2sony2010@gmail.com): > How was VACUUM ANALYZE able to reclaim such a large amount of space, > especially for TOAST data? > > Under what conditions does PostgreSQL reclaim disk space without requiring > VACUUM FULL or CLUSTER? https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-SPACE-RECOVERY "in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained" Regards, Christoph -- Spare Space