VACUUM Is Executed After Table Data Deletion, But the Space Is Not Released

Symptom

After a user deletes the data in a table and executes VACUUM, the storage space is not released.

Possible Causes

• The user may not have the permission for executing VACUUM on some tables or the database does not have too much data expansion.
• By default, VACUUM clears only the tables on which the current user has permissions in the database.
• The vacuum_defer_cleanup_age parameter is not set to 0. In earlier versions, the default value of this parameter is 8000, indicating that dirty data generated by the latest 8000 transactions is not cleared.
• Dirty data generated by the transactions whose ID is greater than that of the currently active transactions is not cleared to ensure transaction visibility.

Handling Procedure

• Run VACUUM FULL on a single table. The command format is VACUUM FULL Table_name.
• If you do not have the permission on the table, contact the database administrator or the table owner.
• If the value of vacuum_defer_cleanup_age is not 0, set this parameter to 0 to cancel the transaction delay of VACUUM.
• If old transactions exist, restart the cluster and run the VACUUM FULL command again, which can ensure that space is reclaimed. Otherwise, run the VACUUM FULL command only after the old transactions are complete.