After you execute UPDATE or DELETE statements in a database, data that is deleted is labeled as invisible but remains as holes on data pages. As a result, a large amount of time is required to scan data because the holes are also scanned. You can reclaim table storage in a regular manner to improve data reading efficiency.
AnalyticDB for PostgreSQL provides the auto-vacuum feature to automatically perform dirty data cleanup operations on tables in the background. For more information, see Use auto-vacuum.
To clean up table data at the earliest opportunity after you perform a large number of update or delete operations in AnalyticDB for PostgreSQL, you can execute the VACUUM statement on a database or a table. For more information, see Configure regular vacuum tasks.
Storage reclamation methods
You can execute the following VACUUM statement to rearrange table data and reclaim table storage to improve data reading performance:
VACUUM [FULL] [FREEZE] [VERBOSE] [table];VACUUM: performs the VACUUM operation on all tables.VACUUM [table]: performs the VACUUM operation on a specific table to clean up dirty data and release the storage of dirty data in the column-oriented table or the holes at the end of the row-oriented table. This operation does not block data reads and writes of the table.VACUUM FULL [table]: performs the VACUUM FULL operation on a specific table to release all storage of dirty data in the table. This operation requires an exclusive lock to block data reads and writes of the table.VACUUM FREEZE [table]: performs the VACUUM FREEZE operation on a specific table to reduce the XID age of the table. This operation does not block data reads and writes of the table.VACUUM VERBOSE [table]: displays the logs of dirty data cleanup during the VACUUM operation.
Usage notes
By default, the auto-vacuum feature is enabled for AnalyticDB for PostgreSQL instances. The system automatically cleans up dirty data and reduces the XID age.
After you perform a large number of update or delete operations, you can execute the VACUUM FULL statement to release all table storage.
You can execute the VACUUM operation only by using an Alibaba Cloud account. If you do not have the required permissions, the VACUUM operation does not take effect.
Query tables that require the VACUUM operation
AnalyticDB for PostgreSQL provides the intelligent data bloat diagnostics feature. This feature helps you find bloated tables and query tables that require the VACUUM operation. For more information, see Data bloat diagnostics.
AnalyticDB for PostgreSQL uses a view named gp_bloat_diag to measure the ratio of actual pages to expected pages. You can execute the ANALYZE TABLE statement to collect statistics and check the gp_bloat_diag view.
gpadmin=# SELECT * FROM gp_toolkit.gp_bloat_diag;
bdirelid | bdinspname | bdirelname | bdirelpages | bdiexppages | bdidiag
----------+------------+------------+-------------+-------------+---------------------------------------
21488 | public | t1 | 97 | 1 | significant amount of bloat suspected
(1 row)The result includes only tables that have moderate or significant data bloats. If the ratio of actual pages to expected pages (bdirelpages/bdiexppages) is greater than 4 and less than 10, the table is considered to have moderate data bloat. If the ratio is greater than 10, the table is considered to have significant data bloat. You can execute the VACUUM FULL statement to reclaim the storage of bloated tables.