Exercise 2: What VACUUM Does, What It Does NOT Do, and Table Bloat — Possible Solution ==================================================================== WHAT VACUUM DOES ------------------------------ Per this chapter, "VACUUM's job is to scan a table, identify dead tuples no longer needed by any currently-running transaction, and mark that space as reusable for future inserts and updates." VACUUM goes through a table, finds the dead tuples left behind by previous UPDATEs/DELETEs (per this chapter's own exercise 1 material), confirms that no still-running transaction could possibly still need to see those old versions, and marks the space those dead tuples occupy as available to be reused by future writes to that same table. WHAT ORDINARY VACUUM DOES NOT DO ------------------------------ Per this chapter, "an important nuance: ordinary VACUUM does NOT shrink the file on disk — that's VACUUM FULL, a much heavier, table-locking operation. Ordinary VACUUM just marks space as internally reusable, so the table can absorb new data without allocating new disk space, without ever actually returning space to the operating system." This is a crucial distinction: after an ordinary VACUUM runs, the table's file on disk is NOT smaller — the freed-up space is only usable again BY THAT SAME TABLE for its own future writes, not returned to the operating system as free disk space. Actually shrinking the physical file requires the separate, much heavier VACUUM FULL operation, which locks the table while it rewrites it. WHAT TABLE BLOAT IS AND WHY IT HAPPENS ------------------------------ Per this chapter, "if VACUUM falls behind — running less often than the table's own update/delete rate demands — dead tuples accumulate faster than they're reclaimed, and the table's real on-disk size can grow well beyond what its live data actually requires. This is table bloat." If a table's rate of updates/deletes consistently outpaces how often VACUUM actually gets to run and reclaim space, dead tuples pile up faster than they're cleaned. Because ordinary VACUUM doesn't shrink the file (per the point above), this accumulation shows up directly as the table's physical size on disk growing well past what its actual current, live data would need — wasted space that both slows down queries scanning the table and consumes real disk capacity. WHY THIS WORKS AS AN ANSWER ------------------------------ It clearly separates what VACUUM does from what it explicitly does NOT do (distinguishing it from VACUUM FULL), using the chapter's own precise wording, and then explains table bloat as the direct, logical consequence of VACUUM falling behind combined with ordinary VACUUM's own space-reuse-not-shrink behavior.