Help Center/ GaussDB(DWS)/ Troubleshooting/ Cluster Performance/ Table Size Does not Change After VACUUM FULL Is Executed on the Table
Updated on 2024-01-25 GMT+08:00

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. Therefore, VACUUM FULL table_name has nothing to delete, causing that 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.

    1
    SELECT txid_current();
    

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

    1
    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.