Diagnose data skew

Prerequisites

Before you begin, ensure that you have:

• An AnalyticDB for PostgreSQL instance in elastic storage mode

• One of the following minor versions:

  • AnalyticDB for PostgreSQL V6.0: V6.3.10.0 or later

  • AnalyticDB for PostgreSQL V7.0: V7.0.4.0 or later

To check your minor version or upgrade, see View the minor engine version and Update the minor engine version.

How it works

AnalyticDB for PostgreSQL uses Massively Parallel Processing (MPP) to distribute data across compute nodes. When data is unevenly distributed, some nodes handle more data than others—this is data skew. Skewed tables slow down queries because MPP relies on parallel processing across balanced workloads.

The diagnostics feature runs automatically at the start of each hour. It queries only metadata tables, so it does not affect query performance.

What gets scanned:

• All user databases except the five system databases: postgres, template0, template1, adbpgadmin, and aurora

• All tables except temporary tables and unlogged tables

• By default, only tables 1 GB or larger (configurable)

What gets reported:

• By default, only tables with a skew ratio of 20% or higher (configurable)

The following examples show the data distribution for an appropriate distribution key and an inappropriate distribution key.

• Appropriate distribution key

  In the example above, the primary key `id` is selected as the distribution key. The data is evenly distributed across the four compute nodes.

• Inappropriate distribution key

  In the example above, the `gender` field is selected as the distribution key. Because the `gender` field has only two values, `true` and `false`, data is stored on only two compute nodes. The other nodes do not store any data. This causes data skew.

Check for data skew

Query adbpg_toolkit.diag_skew_tables to see diagnostics results. Two options are available: query the table directly in SQL, or view results in the console.

Option 1: Query in SQL

Connect to your database and run:

SELECT * FROM adbpg_toolkit.diag_skew_tables;

To filter by schema or table:

-- All tables in a schema
SELECT * FROM adbpg_toolkit.diag_skew_tables WHERE schema_name = '<schema_name>';

-- A specific table
SELECT * FROM adbpg_toolkit.diag_skew_tables WHERE table_name = '<table_name>';

Option 2: View in the console

See Data bloat, data skew, and index statistics.

Trigger diagnostics manually

By default, diagnostics runs at the start of each hour. To check results immediately after changing a distribution key, trigger diagnostics manually:

SELECT adbpg_toolkit.diagnose_skew_tables();

Configure diagnostics parameters

ALTER DATABASE <database_name> SET adb_diagnose_table_threshold_size TO <size_in_bytes>;

ALTER DATABASE diagnose SET adb_diagnose_table_threshold_size TO 536870912;

ALTER DATABASE <database_name> SET adb_diagnose_skew_percent TO <skew_ratio>;

ALTER DATABASE diagnose SET adb_diagnose_skew_percent TO 0;

How the skew ratio is calculated

The skew ratio measures how unevenly data is distributed across compute nodes. Given n compute nodes where S1, S2, ..., Sn represent the data size on each node:

Avg = (S1 + S2 + ... + Sn) / n
Max = max(S1, S2, ..., Sn)
Skew ratio = (1 - Avg / Max) / (1 - 1/n) × 100%

The ratio ranges from 0% to 100%. A value of 0% means data is perfectly balanced; 100% means all data is on a single node.

Fix data skew

Data skew most commonly occurs with hash distribution when the distribution key has many repeated values. Nodes that store those repeated values end up with more data than others.

To fix skew, change the distribution key to a column with more distinct values—ideally the primary key, or a column with evenly distributed data:

ALTER TABLE <table_name> SET DISTRIBUTED BY (<column_name>);

Example: Change the distribution key of t1 to column c2:

ALTER TABLE t1 SET DISTRIBUTED BY (c2);

Not all skewed tables need to be fixed. Changing a distribution key involves redistributing table data across nodes, which takes time and resources. For smaller or infrequently queried tables, the performance gain may not justify the cost.

For guidance on choosing a distribution key, see Selection of distribution keys.

What's next

• Selection of distribution keys

• Data bloat, data skew, and index statistics