A dead tuple is a row version that remains in PostgreSQL storage after it is no longer needed for visibility. It is not removed when data changes occur; VACUUM later cleans it out and makes the cleared space reusable within the table’s data files. VACUUM also cleans corresponding table indexes. section 6.7
The tempting mistake is to treat autovacuum as expendable background work because disabling it can produce a short-lived performance gain. Dead tuples then accumulate, while automatic maintenance no longer regularly prevents bloat, maintains planner statistics through ANALYZE, and handles row freezing for XID wraparound safety. section 6.7
When disk space is tight, deleting data is not an immediate dead-tuple remedy:
- DELETE and TRUNCATE require enough free space to write WAL for the change. section 7.1
- Deleting rows leaves dead rows that still need VACUUM before their space can be reused. section 7.1
- Ordinary VACUUM marks space free inside table files; it does not return that space to the operating system. section 7.1
- VACUUM FULL can return space to the operating system, but rewrites the table under an ACCESS EXCLUSIVE lock, making the table unusable during the operation. section 7.1
Long-lived idle-in-transaction sessions are another operational concern. PostgreSQL uses transaction IDs to determine tuple visibility under MVCC; a session left open after BEGIN can retain its transaction context while doing no active work. End transactions promptly rather than leaving clients idle in transaction. section 6.5.1
Disk-full cleanup trap
A server nearing full disk may fail before a cleanup attempt can help: data changes need WAL space, and even a successful DELETE leaves dead rows behind until VACUUM runs. Plan disk capacity and routine vacuuming instead of relying on emergency deletion or a disruptive VACUUM FULL. section 7.1
ERROR: could not extend file "base/12609/16048": No space left on device HINT: Check free disk space.
This error illustrates the immediate failure mode when PostgreSQL cannot extend a data file because disk space is exhausted. section 7.1
