How to reclaim space-AnalyticDB(AnalyticDB)-阿里云帮助中心

After UPDATE or DELETE operations, deleted rows are marked as invisible but remain as holes on data pages. Over time, this causes data bloat: tables occupy more disk space than needed, and scans slow down because they must read through the holes. Regular storage reclamation:

Recovers disk space occupied by deleted or updated rows.
Keeps scans fast by eliminating holes in data pages.
Reduces the XID age.

AnalyticDB for PostgreSQL provides the auto-vacuum feature to handle routine cleanup automatically. Auto-vacuum is enabled by default and runs in the background to clean up dirty data and reduce the XID age. For tables with severe data bloat, run the VACUUM statement manually. To identify which tables need attention, use the intelligent data bloat diagnostics feature.

VACUUM commands

Use the following syntax to run a VACUUM operation:

VACUUM [FULL] [FREEZE] [VERBOSE] [table];

Command	Description	Blocks reads and writes?
`VACUUM`	Runs VACUUM on all tables.	No
`VACUUM [table]`	Cleans dirty data and releases storage for a specific table: frees all dirty data storage in column-oriented tables, or frees holes at the end of row-oriented tables.	No
`VACUUM FULL [table]`	Releases all dirty data storage in a specific table by rewriting the table. Requires an exclusive lock.	Yes
`VACUUM FREEZE [table]`	Reduces the XID age of a specific table.	No
`VACUUM VERBOSE [table]`	Prints detailed logs of the cleanup process.	No

Important

VACUUM FULL applies an exclusive lock and blocks all reads and writes on the table for the duration of the operation.

Choose a VACUUM strategy

The usual goal of routine maintenance is to run VACUUM frequently enough to avoid ever needing VACUUM FULL. Auto-vacuum works this way—it keeps tables at a stable disk usage level rather than shrinking them to their minimum size. VACUUM FULL reclaims the most space, but it holds an exclusive lock for the duration of the operation. For heavily updated tables, frequent standard VACUUM runs are more practical than infrequent VACUUM FULL runs.

Detect table bloat

AnalyticDB for PostgreSQL provides the gp_toolkit.gp_bloat_diag view to measure the ratio of actual pages to expected pages. Run ANALYZE TABLE on the table first to update statistics, then query the view:

SELECT * FROM gp_toolkit.gp_bloat_diag;

The view returns only tables with moderate or significant data bloat:

 bdirelid | bdinspname | bdirelname | bdirelpages | bdiexppages |                bdidiag
----------+------------+------------+-------------+-------------+---------------------------------------
    21488 | public     | t1         |          97 |           1 | significant amount of bloat suspected
(1 row)

Bloat level	Condition (`bdirelpages / bdiexppages`)
Moderate	Greater than 4 and less than 10
Significant	Greater than 10

Run VACUUM FULL on any tables identified as bloated to reclaim all dirty data storage.

Reclaim storage with minimal downtime

VACUUM FULL is the most thorough way to reclaim storage, but its exclusive lock can disrupt online workloads on large tables. The following method reclaims storage through data redistribution, keeping the table readable throughout most of the process.

How it works

The method creates a temporary table with the same schema as the original, copies all data into it, swaps the metadata of the two tables, and then drops the temporary table. Because the data is completely rewritten, no holes remain in the new table.

When to use this method

Use this method when either of the following conditions applies:

Auto-vacuum cannot reclaim storage in time because the lock-yielding mechanism prevents it from holding locks long enough.
Running VACUUM FULL on a large bloated table would hold an exclusive lock for an extended period, affecting production workloads.

Limitations

The method generates a temporary data replica during reclamation, which increases disk usage for a short time.
Index rebuilding runs in the background but still blocks reads and writes during that phase. This method is not suitable for tables with large indexes, such as vector indexes.

Procedure

Create the following function in your database:

CREATE OR REPLACE FUNCTION reorganize_table(table_name regclass)
RETURNS void AS $$
DECLARE
    distribution_policy text;
    alter_sql text;
BEGIN
    SELECT pg_get_table_distributedby(table_name) INTO distribution_policy;
    alter_sql := format('ALTER TABLE ONLY %s SET WITH (REORGANIZE=true) %s READABLE', table_name, distribution_policy);

    EXECUTE alter_sql;
END;
$$ LANGUAGE plpgsql;

Call the function with the target table name:
```
SELECT reorganize_table('schema_name.table_name');
```
Replace schema_name.table_name with the actual schema and table name.

Permissions

Only the initial account or a privileged account with RDS_SUPERUSER permission can run VACUUM. The statement has no effect if the account lacks the required permission.