Database bloat is excess table or index space occupied by dead row versions and other no-longer-useful data. In PostgreSQL, VACUUM removes dead tuples once no transaction can see them; autovacuum performs that maintenance automatically. When it cannot keep up or is blocked, scans must read additional junk data, queries slow down, and table files can grow unnecessarily. section 6.5.1 section 6.7
A common mistake is treating autovacuum as optional overhead. Disabling or weakening it may produce a short-term resource gain, but accumulated bloat, stale planner statistics, and anti-wraparound work eventually outweigh it. Long-lived idle-in-transaction sessions are another cause: they can retain visibility of old rows and prevent vacuum from removing them. section 6.13 section 6.5.1 section 6.7
Keep bloat from becoming an operational recovery problem:
- Keep autovacuum enabled and automatic; it handles vacuuming, analyzing, and protection against transaction ID wraparound. section 6.7
- Avoid leaving sessions idle inside transactions, especially when they have modified data or use REPEATABLE READ or SERIALIZABLE isolation. section 6.5.1
- Set idle_in_transaction_session_timeout where application processing can tolerate timeout disconnections, choosing a value that still permits legitimate transaction work. section 6.5.1
- Review and adjust autovacuum settings before tables balloon; act when query performance worsens rather than waiting for a crisis. section 11.2
Simple VACUUM can mark reclaimed space as reusable by PostgreSQL, but it does not return that space to the operating system. If bloat grows unchecked, VACUUM FULL can reclaim filesystem space, but it is slow, needs additional disk space, and completely locks the table. Do not run it on an active table without understanding that disruption. section 6.7
An idle transaction delays cleanup
Client A begins a transaction and reads mytable, then remains open. While it is open, Client B attempts ALTER TABLE mytable ADD description text and waits. The same long-lived transaction can also force dead rows to remain visible, preventing vacuum from removing them and allowing bloat to accumulate. section 6.5.1
