Table and Index Bloat: When Your Pages Are Mostly Empty Space
Bloat is the gap between the rows a table or index really holds and the number of pages it occupies, and it builds up when dead row versions accumulate faster than VACUUM clears them, or when a bulk delete leaves pages half empty. Since the page is the unit of I/O, a table that is fifty percent bloat makes every sequential scan read twice the pages for the same answer, and bloated indexes get deeper and slower too. You detect it by comparing size to live rows, and you fix it by repairing the maintenance that fell behind, not by rewriting the query.
Questions this Concept answers
Why does a table that is fifty percent bloat roughly double the cost of every sequential scan over it?
J
jeremy
Video
Table Bloat and Autovacuum Tuning in PostgreSQL
Table bloat is unused space occupied on disk by row versions that deletes and updates rendered dead but which has not yet been reclaimed for reuse; because a multiversion storage engine implements an…