Storage degradation and query slowdowns often stem from three root causes: dead rows accumulating after updates and deletes, data distributed unevenly across compute nodes, and large indexes that are rarely used. The Key Metrics page in Diagnostics and Optimization gives you visibility into all three, scanning every table automatically so you can identify problems and take corrective action.
Prerequisites
Before you begin, ensure that:
The instance is in elastic storage mode
The minor engine version is V6.3.10.1 or later
To check the current version, see View the minor engine version. To upgrade, see Update the minor engine version.
Open Key Metrics
Log on to the AnalyticDB for PostgreSQL console.
In the upper-left corner, select the region where the instance resides.
Click the ID of the instance you want to manage.
In the left-side navigation pane, choose Diagnostics and Optimization > Key Metrics.
The Key Metrics page has three tabs: Data Bloat, Data Skew, and Index Statistics. The intelligent diagnostics feature scans all database tables automatically at the start of each hour.
Data bloat
AnalyticDB for PostgreSQL uses multiversion concurrency control (MVCC), so UPDATE and DELETE operations may cause dead rows. Over time, dead rows inflate table size beyond what live data requires. The Data Bloat tab lists tables larger than 1 GB that have accumulated dead rows, so you can identify which tables need reclaiming.
| Parameter | Description |
|---|---|
| Database Name | The name of the database. |
| Schema Name | The name of the schema. |
| Partitioned Table Name | The name of the partitioned table. |
| Table Type | The storage type, such as heap table or append-optimized (AO) table. |
| Expected Table Size | The expected size of the table. Unit: bytes. The expected table size indicates the size of the table that has no data bloat. |
| Actual Table Size | The actual size of the table. Unit: bytes. |
| Table Bloat Size | The bloat size of the table. It indicates the amount of space that can be released. Unit: bytes. |
| Bloat Coefficient | Dead rows divided by active rows. A value of 0 means no bloat. Higher values indicate more severe bloat and more storage that can be reclaimed. |
| Last Diagnosed At | The time when the last diagnostic scan ran. |
Data skew
Data skew concentrates query work on a subset of compute nodes, creating bottlenecks that slow queries even when overall cluster resources are available. The Data Skew tab lists tables larger than 1 GB where data is distributed unevenly across nodes, so you can identify and fix distribution imbalances.
| Parameter | Description |
|---|---|
| Database Name | The name of the database. |
| Schema Name | The name of the schema. |
| Partitioned Table Name | The name of the partitioned table. |
| Owner | The owner of the table. |
| Rows | The total number of rows in the table. |
| Skew Ratio | The proportion of imbalance across nodes, expressed as a percentage. Calculated as: 1 - (average table size across all nodes / maximum table size across any single node). A value of 0% means data is perfectly balanced. A less value indicates less impact on query performance. Higher values indicate more severe skew. |
| Distribution Key | The column used to distribute rows across nodes. |
| Last Diagnosed At | The time when the last diagnostic scan ran. |
Index statistics
Large indexes that are rarely accessed consume storage and add write overhead without benefiting query performance. The Index Statistics tab displays indexes larger than 100 MB, so you can identify candidates for removal or optimization.
| Parameter | Description |
|---|---|
| Database Name | The name of the database. |
| Schema Name | The name of the schema. |
| Partitioned Table Name | The name of the partitioned table. |
| Whether Partitioned Table | Indicates whether the table is a partitioned table. |
| Table Name | The name of the table. |
| Index Size | The size of the index. Unit: bytes. |
| Index Scans | The total number of times the index has been used in scans. |
| Index-scanned Rows | The total number of rows returned through index scans. |
| Index Name | The name of the index. |
| Last Diagnosed At | The time when the last diagnostic scan ran. |
| Index Definition | The DDL statement that defines the index. |