Disk Space Reclamation in PostgreSQL: DELETE vs TRUNCATE

Key Takeaways

• TRUNCATE immediately reclaims physical disk space, whereas DELETE does not free up physical space without a VACUUM FULL operation.

• DELETE is preferable for tables with frequent queries, as it allows ongoing transactions access to rows marked for deletion.

• VACUUM FULL is necessary after DELETE to actually free up space, but it requires exclusive access and temporarily uses additional disk space.

• For large tables where data is periodically purged, TRUNCATE is more efficient and simpler than DELETE + VACUUM FULL.

Deep Dive Summary

PostgreSQL manages disk space through MVCC, allowing multiple versions of a data item to coexist for concurrency and recovery. However, this approach complicates physical space reclamation after data deletion. The DELETE operation marks rows for deletion but doesn't immediately free up space, maintaining accessibility for other transactions. To reclaim space, a VACUUM FULL command must be executed, which rewrites the table to disk without the deleted rows, temporarily requiring additional disk space equal to the table size.

Contrastingly, TRUNCATE bypasses the row-level deletion process, resetting the table to its initial state and instantly freeing up disk space. This operation is particularly useful for large tables where complete data removal is intended, offering a quick and straightforward way to recover space without the overhead of VACUUM FULL.

Expert Q&A

Q: Why doesn't PostgreSQL immediately reclaim disk space after a DELETE operation?

A: PostgreSQL relies on MVCC for data consistency and recovery, allowing transactions to see data as it was at the start of the transaction. Immediate space reclamation after DELETE could interfere with this visibility. VACUUM FULL is required to clean up and reclaim space, ensuring that the database maintains consistent views for all transactions.

Further Exploration

PostgreSQL Does Not Free Up Physical Space After DELETE: A detailed examination of PostgreSQL's data deletion mechanics, focusing on how DELETE and TRUNCATE differ in their impact on disk space reclamation and the vital role of VACUUM FULL.