As DELETE and UPDATE operations accumulate dead tuples in your tables, storage grows beyond what live data requires and scan I/O increases — degrading query performance. AnalyticDB for PostgreSQL automatically scans all user databases every hour and writes results to a diagnostic table. Query that table to see which tables are bloated, how severely, and whether to run VACUUM or VACUUM FULL.
Prerequisites
Before you begin, ensure that you have:
-
An AnalyticDB for PostgreSQL instance in elastic storage mode
-
A compatible minor version:
-
V6.0: V6.3.10.0 or later
-
V7.0: V7.0.4.0 or later
-
To check your current minor version, see View the minor engine version. To upgrade, see Update the minor engine version.
How it works
AnalyticDB for PostgreSQL uses Multiversion concurrency control (MVCC) to manage concurrent transactions. Table data is stored in 32-KB pages. Each page contains a header, an item pointer array, unused space, and tuples.
Under MVCC, write operations accumulate dead tuples over time:
-
INSERT: New tuples fill unused space. When a page is full, a new page is allocated.
-
DELETE: Rows are not removed from pages immediately. Instead, they are marked as dead tuples and continue to occupy space.
-
UPDATE: The original tuple is marked as a dead tuple, and a new tuple is inserted. Every UPDATE generates one dead tuple.
As dead tuples accumulate, tables occupy more disk space than their live data requires. Bloated tables waste storage and increase I/O overhead for scans, which degrades query performance.
The diagnostics feature runs at the start of each hour (for example, after a run at 17:00:00, the next run is at 18:00:00). It covers all user databases except the five system databases — postgres, template0, template1, adbpgadmin, and aurora. Temporary tables and unlogged tables are also excluded.
Tables smaller than 1 GB are excluded by default to balance speed and coverage. See Adjust the size threshold to change this limit.
Diagnose and resolve data bloat
The following workflow takes you from querying the diagnostic table to verifying that bloat has been resolved:
-
Query the diagnostic table to identify bloated tables.
-
Filter by schema or table to narrow results if needed.
-
Eliminate data bloat by running VACUUM or VACUUM FULL based on
suggest_action. -
Trigger diagnostics manually to confirm the bloat is resolved.
Query the diagnostic table
Diagnostic results are stored in adbpg_toolkit.diag_bloat_tables. To view all results:
SELECT * FROM adbpg_toolkit.diag_bloat_tables;Results are sorted by bloat_coeff DESC, real_size DESC — the most bloated tables appear first. If two tables have the same bloat coefficient, the larger table appears first.
Diagnostic results are based on statistics collected by the PostgreSQL statistics collector. In the unlikely event that the PostgreSQL server fails to respond, the statistics collector resets the statistics. If the diagnostic information appears unreasonable, run ANALYZE to refresh statistics before re-querying. For details, see Use the ANALYZE statement to collect statistics on AnalyticDB for PostgreSQL.
The table contains the following fields:
| Field | Data type | Description |
|---|---|---|
schema_name |
name (63-byte type for storing system identifiers) | Schema that contains the table |
table_name |
name | Table name |
storage_type |
text | Storage type: heap table or append-optimized (AO) table |
expect_size |
bigint | Expected size, in bytes |
real_size |
bigint | Actual size, in bytes |
bloat_size |
bigint | Bloat size, in bytes |
bloat_coeff |
bigint | Bloat coefficient, as a percentage. Valid values: 0–100 |
suggest_action |
text | Suggested action: empty (no action), VACUUM, or VACUUM FULL |
last_vacuum |
timestamp with time zone | Last time the table was vacuumed (VACUUM FULL is not included) |
diagnose_time |
timestamp with time zone | Time when the diagnostic snapshot was taken |
Diagnostic results are also available in the AnalyticDB for PostgreSQL console. See Data bloat, data skew, and index statistics.
Filter by schema or table
Check all tables in a specific schema:
SELECT * FROM adbpg_toolkit.diag_bloat_tables WHERE schema_name = '<schema_name>';Check a specific table:
SELECT * FROM adbpg_toolkit.diag_bloat_tables WHERE table_name = '<table_name>';Eliminate data bloat
Use the suggest_action field to determine which operation to run:
suggest_action value |
What to do |
|---|---|
| *(empty)* | No action needed |
VACUUM |
Run VACUUM on the table |
VACUUM FULL |
Run VACUUM FULL on the table |
VACUUM
VACUUM removes dead tuples and makes their space available for new writes. It does not reduce the physical file size on disk.
VACUUM <table_name>;When to use: VACUUM is less disruptive than VACUUM FULL. It acquires a less restrictive lock, allowing concurrent reads and writes to continue during the operation.
Limitation: VACUUM cannot reclaim space across pages. The table's file size on disk stays the same.
VACUUM FULL
VACUUM FULL rewrites the table into a new, compact file, then drops the original. This reduces the table's size on disk.
VACUUM FULL <table_name>;When to use: VACUUM FULL reclaims disk space that VACUUM cannot. Use it when disk usage is a concern.
Limitation: VACUUM FULL acquires an ACCESS EXCLUSIVE lock, which blocks all operations on the table — including SELECT — for the duration of the operation. It also requires extra disk space to hold a copy of the reorganized data during the rewrite.
Trigger diagnostics manually
By default, diagnostics run at the start of each hour. After running VACUUM or VACUUM FULL, trigger diagnostics manually to verify the result:
SELECT adbpg_toolkit.diagnose_bloat_tables();Then re-query adbpg_toolkit.diag_bloat_tables to confirm that bloat_coeff and suggest_action have been updated for the table.
Adjust the size threshold
Tables smaller than 1 GB are excluded from diagnostics by default. To change this threshold for a specific database:
ALTER DATABASE <database_name> SET adb_diagnose_table_threshold_size TO <size_in_bytes>;For example, to diagnose tables larger than 500 MB (536,870,912 bytes):
ALTER DATABASE diagnose SET adb_diagnose_table_threshold_size TO 536870912;