2021

Materialized view: query rewrite

Query rewrite is now supported for materialized views, improving performance for JOIN operations, aggregate functions, subqueries, common table expressions (CTEs), and high-concurrency SQL statements.

Real-time materialized views are now supported for partitioned tables.

References:

• Query rewrite for materialized views

• Use real-time materialized views to accelerate queries that contain variable parameters

• Real-time materialized views

Partitioned table: conflict handling

INSERT ON CONFLICT and COPY ON CONFLICT statements are now supported for partitioned tables.

References:

• Use INSERT ON CONFLICT to overwrite data

• Use COPY ON CONFLICT to overwrite data

pg_cron V1.4: enhanced scheduled tasks

The pg_cron extension is updated to V1.4 with the following additions:

• Scheduled tasks can run across databases.

• Existing scheduled tasks can be modified.

• Task names can be specified.

• The cron.job_run_details table records execution details for each scheduled task run.

Reference: Use the pg_cron extension to configure scheduled tasks

Data reads during compute node specification changes

Before V6.3.6.1, all tables were unavailable (neither readable nor writable) during compute node specification changes, because data was redistributed across all tables. Starting from V6.3.6.1, all tables remain readable during the process. Only tables actively undergoing data redistribution are temporarily not writable.

Auto-merge

Auto-merge automatically sorts and consolidates data in the background. It periodically checks table data, sorts newly inserted unordered data, and merges it with the existing ordered data.

Auto-merge supports only append-optimized (AO) tables that are configured with sort keys.

Reference: Auto-merge

Automatic closing of idle connections

Connections that have been idle for 6 hours are automatically closed.

Sorting acceleration

Sorting acceleration now applies when data is updated.

Reference: Configure sorting acceleration

Laser engine improvements

• Improved Laser engine execution performance on Master nodes.

• Improved ORDER BY performance on large datasets.

• Improved COUNT DISTINCT performance.

Reference: Use the Laser computing engine

Increased maximum user connections

The maximum number of user connections is increased for the following instance specifications:

Reference: Limits

Real-time materialized view enhancements

• INSERT ON CONFLICT DO UPDATE and COPY ON CONFLICT DO UPDATE statements are now supported.

• Fixed incorrect refresh of real-time materialized views in Multi-Master scenarios.

• Replicated tables are now supported.

• UPSERT clauses are now supported.

Reference: Real-time materialized views

PL/Java disabled by default

PL/Java is now disabled by default.

Reference: Create and use PL/Java UDFs

Laser engine enabled by default

The Laser computing engine is now enabled by default.

Reference: Use the Laser computing engine

Parallel query enabled by default

Parallel query for individual tables is now enabled by default.

Reference: Configure parallel query

Sorting acceleration

Sorting acceleration pushes SORT, AGG, and JOIN operators down to the storage layer, accelerating queries based on the physical order of data. Run SORT <tablename> to sort the data of a table and activate storage-layer acceleration for that table.

Reference: Configure sorting acceleration

Minor version query via GUC parameter

Use the following Global User Configuration (GUC) parameter to query the minor engine version of your instance:

show adbpg_version;

Reference: View the minor engine version

Real-time materialized view performance

Real-time materialized view performance is improved. The MAX and MIN aggregate functions can now be used in DELETE and UPDATE statements. CTEs can now be used in change statements executed on base tables.

Reference: Real-time materialized views

auto-vacuum

auto-vacuum automatically runs VACUUM on tables. By default, it triggers when more than 50% of a table's rows have been deleted, removing dead rows to keep query performance stable. auto-vacuum checks for tables that have a large number of INSERT, UPDATE, or DELETE operations.

For Multi-Master instances, only changes on the primary Master can currently be tracked. Changes on secondary Masters do not trigger AUTO VACUUM.

Reference: Configure scheduled maintenance tasks to clear junk data

auto-analyze

auto-analyze automatically runs ANALYZE on tables that have a significant number of INSERT, UPDATE, or DELETE operations. By default, it triggers when more than 10% of a table's rows are affected, keeping table statistics up to date for accurate query plans.

For Multi-Master instances, only changes on the primary Master can currently be tracked. Changes on secondary Masters do not trigger AUTO ANALYZE.

Reference: Use the ANALYZE statement to collect statistics on AnalyticDB for PostgreSQL

BRIN index (Block Range Index)

BRIN indexes are now supported for compressed AO tables. They can be used to query large-range data from an ordered table or filter out data blocks that are not needed to reduce I/O. Compared to B-tree indexes on large datasets, BRIN indexes provide similar or superior performance while requiring significantly less storage and are faster to build.

Reference: Manage indexes

Parallel query (Preview)

The rds_segment_expansion_coeff parameter enables session-level parallel query. It specifies a multiplier of cores allocated to a single query. The default value is 1 (INTEGER type).

This parameter improves performance linearly for compute-intensive queries that run longer than 3 seconds when queries per second (QPS) is low. Effective scenarios include aggregate queries on individual tables (such as TPC-H Q1 and TPC-H Q6) and joins between large and small tables. It provides limited improvement for I/O-intensive queries and high disk usage scenarios, and may reduce performance for network-intensive queries.

Real-time materialized view (Preview)

Real-time materialized views are now available. Unlike standard materialized views, they stay up to date automatically when the underlying data changes—no manual REFRESH statements needed.

Reference: Real-time materialized views

Instance endpoint enhancements

• DDL operations can now be performed between the BEGIN and ROLLBACK/COMMIT transaction blocks via instance endpoints.

• TEMP TABLE and TEMP VIEW operations are now supported for session-level queries via instance endpoints.