Help Center> Data Warehouse Service> Troubleshooting> Database Performance and Resources> Table Size Does not Change After VACUUM FULL Is Executed on the Table
View PDF

Table Size Does not Change After VACUUM FULL Is Executed on the Table

Symptom

A user runs the VACUUM FULL command to clear a table, but the table size does not change.

Possible Causes

Assume the table is name table_name. Possible causes are as follows:

  1. No data has been deleted from the table_name table. Therefore, VACUUM FULL table_name has nothing to delete, and the table size does not change.
  2. Concurrent transactions exist during the execution of VACUUM FULL table_name, causing the recently deleted data to be skipped. As a result, the table size does not change.

Solution

The following are solutions for the second possible cause:
  • Wait until all concurrent transactions are complete, and then run the VACUUM FULL table_name command again.
  • If the table file size remains unchanged after the preceding operations are performed, ensure that no task is running in the cluster and all data has been saved. Then, perform the following operations:
  1. Run the following command to query the current transaction XID. 

    select txid_current();

  2. Run the following command to view the active transaction list. 

    select txid_current_snapshot();

  3. If the XID of any transaction in the active transaction list is smaller than that of the current transaction, restart the cluster and run the VACUUM FULL statement to clear the table again.