Auto-merge-AnalyticDB(AnalyticDB)-阿里云帮助中心

Auto-merge is a background process in AnalyticDB for PostgreSQL that keeps your data sorted automatically. It monitors append-optimized (AO) tables periodically, detects new unsorted rows, and merges them with existing sorted data — without manual intervention.

Auto-merge improves query performance but consumes I/O resources during the merge process.

How it works

When new rows are written to an AO table, they arrive unsorted. Auto-merge tracks the number of unsorted rows. When that count crosses a configurable threshold (automerge_unsorted_row_num_threshold), auto-merge triggers a background sort-and-merge operation that integrates the new rows into the existing sorted data segments.

Queries against fully sorted data can skip entire data blocks that fall outside the filter range, instead of scanning all rows. This is most effective for queries using WHERE, GROUP BY, JOIN, or ORDER BY clauses.

To monitor an active merge job, query the pg_stat_activity view.

Prerequisites

Before you begin, ensure that you have:

An AO table with sort keys defined (auto-merge supports only AO tables with sort keys)
Auto-vacuum enabled (auto-merge depends on auto-vacuum)
An AnalyticDB for PostgreSQL instance running minor version 6.3.5.0 or later

For details on enabling auto-vacuum, see Configure scheduled maintenance tasks to clear junk data. To check or update your minor version, see Query the minor engine version and Upgrade the engine version.

Define sort keys

Auto-merge requires sort keys. Define them when creating a table or add them to an existing one.

At table creation:

CREATE TABLE table_name (
    col_name type,
    ...
)
WITH (appendonly = true, orientation = row/column)
DISTRIBUTED BY (distributed_keys)
ORDER BY (sort_keys);

On an existing table:

ALTER TABLE table_name SET ORDER BY (sort_keys);

To check whether an existing table is an AO table:

SELECT reloptions FROM pg_class WHERE relname = 'table_name';

appendonly=true in the output confirms the table is AO:

reloptions
----------------------------------------------------------------------
 {appendonly=true,orientation=column,compresstype=lz4,compresslevel=9}
(1 row)

Choose effective sort keys

The sort key you choose determines how much data the engine can skip during a query. To get the most out of auto-merge:

Use columns that appear frequently in WHERE clauses and filter out a large portion of rows.
For queries that use AGGREGATE, JOIN, or ORDER BY, use the group key, join key, or order key as the sort key. Set the distribution key and sort key to the same column.

Enable or disable auto-merge

Auto-merge is enabled for all tables by default.

Enable auto-merge on a table:

ALTER TABLE table_name SET (automerge = on);

Disable auto-merge on a table:

ALTER TABLE table_name SET (automerge = off);

To disable auto-merge across all tables in an instance, submit a ticket.

Check auto-merge status on a table:

SELECT relname, reloptions FROM pg_class WHERE relname = 'table_name';

Sample output:

relname    |                     reloptions
-----------+----------------------------------------------------
 table_name | {appendonly=true,orientation=column,automerge=on}

automerge=on confirms auto-merge is enabled. If the output does not include the automerge field, the table's setting matches the instance-level default. Run the following to check the instance-level value:

SHOW automerge;

Test auto-merge performance

The following steps demonstrate the query performance improvement from auto-merge. The test uses a controlled setup: data is loaded in two batches, with the first batch kept below the merge threshold to establish a pre-merge baseline.

Confirm auto-vacuum and auto-merge are both enabled.
```
SHOW autovacuum;
SHOW automerge;
```
Both should return on.
Disable the Laser computing engine.
This step is for testing only. Enable the Laser computing engine in production. For details, see Use the Laser computing engine.
```
SET laser.enable = off;
```

Create a test table with a sort key on column b.

CREATE TABLE test_automerge (a bigint, b bigint)
WITH (appendonly = true, orientation = column, compresstype = lz4, compresslevel = 9)
DISTRIBUTED BY (a)
ORDER BY (b);

Set the merge threshold to 10,000,000 rows, then insert 9,000,000 rows — just below the threshold.

ALTER TABLE test_automerge SET (automerge_unsorted_row_num_threshold = 10000000);

INSERT INTO test_automerge
SELECT i, round(random() * 100)
FROM generate_series(1, 9000000) AS i;

Run a query on the unsorted data.

SELECT count(*) FROM test_automerge WHERE b = 5;

Sample output:

 count
-------
 89713
(1 row)

Time: 204.918 ms

Insert 1,000,000 more rows to push the total past the threshold and trigger auto-merge.

INSERT INTO test_automerge
SELECT i, round(random() * 100)
FROM generate_series(1, 1000000) AS i;

After the merge completes, run the same query again.

SELECT count(*) FROM test_automerge WHERE b = 5;

Sample output:

 count
-------
 99683
(1 row)

Time: 15.289 ms

The query time dropped from 204 ms to 15 ms. Results vary by data volume, sort key selectivity, and hardware, but the improvement is most pronounced for queries that filter on the sort key column.

Next steps

Configure sorting acceleration