New Features in 9.1.1.x

Timeliness

Materialized view enhanced

There are full and incremental binlog-based streaming automatic refresh. The visualized interaction experience is optimized, the technical architecture is simplified, and the timeliness is improved.

• The GUC parameter mv_refresh_parts_per_trans is available. It specifies the number of materialized view partitions submitted in each batch for a partitioned materialized view, so that the latest partitions are refreshed and submitted first.
• The FAST, ON COMMIT, and ON STATEMENT parameters are added to CREATE MATERIALIZED VIEW. The mv_computing_resource and mv_incremental_mode parameters are added.

• DWS Materialized Views
• mv_refresh_parts_per_trans
• CREATE MATERIALIZED VIEW

Unified storage engine

The standard data warehouse supports HStore Opt tables. By default, new column-store tables use the HStore mode and Turbo engine. Compared with old column-store tables of earlier versions, the new tables deliver better performance. The import, DELETE, and UPSERT performance is improved. The column-store CU lock is changed to the row-level lock, which is the same as the row-store lock.

• The DELTAROW_THRESHOLD parameter is available in the CREATE TABLE syntax. In the standard data warehouse, if this parameter is set to 0, HStore Opt tables are used.
• The column_hstore_opt option is available in the GUC parameter column_default_format to create HStore Opt tables in the standard data warehouse.

• CREATE TABLE
• column_default_format

Wide table function enhanced

The wide table function is enhanced. The enable_wide_table parameter is available in the WITH parameter description of CREATE TABLE (related to HStore tables).

This parameter specifies whether to enable the wide table function and applies only to HStore Opt tables. After this function is enabled, a maximum of 5000 columns are supported.

CREATE TABLE

Lakehouse

S3 protocol supported

You can create a server whose type is S3 and then use the server to create a foreign table. The usage of the foreign table is the same as that of the original foreign table.

CREATE SERVER

OceanStor Pacific supported by HDFS foreign tables

HDFS foreign tables can connect to OceanStor Pacific.

-

GDS functions enhanced

When importing data using a GDS foreign table, you can specify the number of rows to be skipped in addition to the header rows that can be skipped.

• The headernum parameter is available in CREATE FOREIGN TABLE (for GDS import and export). It specifies the number of header rows in the imported file. That is, it controls how many rows are used as the header rows.
• The inherit-perms and inherit-user parameters are added to the GDS tool command to control whether the exported data file can inherit the directory permissions and directory user-level user group permissions.

• CREATE FOREIGN TABLE (for GDS Import and Export)
• gds

Backup and DR

Backup and restoration enhanced

• Resumable fine-grained backup and restoration: Resumable operations are available to fine-grained full backup and cluster-level full backup. They are unavailable to incremental backup and V3 table backup.
• New restoration scenarios: Fine-grained restoration of V3 tables is supported. The capability is the same as that of V2 tables.

It is a beta feature. To use it, contact technical support.

Basic quality (cluster)

Quick cluster restart

Quick cluster restart is supported. After a GUC parameter is modified, a cluster does not need to be restarted, reducing the impact on services.

-

Subhealth recovery capability hardening

In earlier versions, if a single CN is faulty and then removed, historical applications are still connected to the faulty CN. As a result, the operations are in the to-be-submitted state, blocking new operations.

To solve such an issue, the system uses arbitration to quickly close the TCP connection between the old instance and the faulty instance, ensuring that the old instance works properly.

-

Basic quality in SQL

Consistency verification enhanced

In this version, the verification of data hash distribution is enhanced. The data hash distribution can be verified in all scenarios, such as INSERT, COPY, and direct connection to DNs for data import. The consistency verification is supplemented. When data is written to DNs in different ways, a consistency verification mechanism for hash data distribution is available on the DNs to ensure correct data distribution.

• The node_ids column is available to the PGXC_CLASS system catalog, indicating IDs of nodes where tables are distributed.
• The REFRESH DISTRIBUTION parameter is available to ALTER TABLE. Based on the distribution column information and DN information of a table, the record information in the PGXC_CLASS system catalog is updated for consistency verification of data distribution. The information is updated on both CNs and DNs synchronously.

• PGXC_CLASS
• ALTER TABLE

Redistribution write time optimized

The time-consuming statements in the incremental catchup phase of redistribution are optimized.

-

Top SQL function enhanced

In high-concurrency scenarios, the top SQL function supports short query aggregation to reduce the space occupied by short query records.

Multiple columns in system catalogs such as GS_WLM_SESSION_INFO, GS_WLM_SESSION_HISTORY, GS_WLM_SESSION_INFO, PGXC_WLM_SESSION_HISTORY and PGXC_WLM_SESSION_INFO are aggregated. The main columns to be aggregated are datid, dbname, resource_pool, node_group, username, application_name, client_addr, sql_hash, plan_hash, query_band, and nodename.

-

Functions for killing processes enhanced

When you use the pg_cancel_query and pg_terminate_query functions to kill a process, the system checks whether the process is successfully killed. If the process fails to be killed, the system returns f.

-

CN concurrency control optimized

The global CN concurrency control is moved forward to before statement parsing to reduce resource overhead.

-

Partitioning of WLM system catalogs

WLM system catalogs are reconstructed from non-partitioned tables to partitioned tables. Partitions are created by day, and expired partitions are deleted every day to prevent high I/O usage from blocking services.

-

pg_settings synchronization mechanism enhanced

The corresponding columns are added to the pg_settings and pgxc_settings views and can be queried properly. sync_to_standby is changed to sync_to_stream.

• PG_SETTINGS
• PGXC_SETTINGS

Basic quality in storage

Online VACUUM FULL

Online VACUUM FULL operations have little impact on ongoing services, improving service continuity and database availability.

VACUUM FULL <tbl_name> ONLINE is used.

It is a beta feature. To use it, contact technical support.

VACUUM

Connection between sequences and GTM optimized

The time required for creating sequences or tables with auto-increment columns is shortened, improving user experience.

The new GUC parameter enable_multi_sequence_file controls whether to split the gtm.sequence file in GTM. After this parameter is enabled, sequence persistence operations are grouped by UUID, improving concurrency performance.

enable_multi_sequence_file

Residual transaction clearance optimized

gs_clean supports residual transaction clearance on DNs in parallel. Transaction clearance is decoupled from non-transaction clearance.

-

New features

None

-

-

-

Resolved issues

When an IAM user logs in to a cluster, an error message is displayed, indicating that the username or password is invalid.

When an IAM user logs in, the authentication token needs to be parsed and verified. After the parsing ends and the parsed objects are released, some fields become null pointers. As a result, abnormal data is read during the verification, and the system incorrectly determines that the login fails.

9.1.1.x

Upgrade the version to 9.1.1.210 or later.

When cost_model_version is set to 4, the associated skew is not identified.

When generating a plan, the system incorrectly selects a path with severe skew as the global optimal path and inherits the skew value of the path. As a result, the distribution costs are high based on the skew, and the skew optimization path is not selected.

9.1.1.x

When the \df meta-command is executed after the client of a cluster 9.1.1 is connected to a database of a cluster 9.1.0, an error is reported.

The prokind field is added to pg_proc in clusters of version 9.1.1. After being adapted to gsql, clusters of version 9.1.1 need to be backward compatible with clusters of earlier versions.

9.1.1.x

In clusters of version 9.1.1 or later, when you use the COMMIT/ROLLBACK function of a stored procedure, the logic judgment of the stored procedure contains system functions. When you run the commit command, the error message "cannot commit/rollback within function" is displayed.

Built-in functions are incorrectly set as stored procedures.

9.1.1.x

When using the fine-grained transparent encryption, users need to log in to KMS using a username and password.

When using fine-grained transparent encryption in clusters of version 9.1.1.200, users cannot log in to KMS using a username and password. The username and password can be used for login authentication.

9.1.1.x

The CVE-2025-12818 vulnerability in DWS needs to be fixed.

In multiple functions of libpq, integer overflows allow application input providers or network peers to cause libpq to allocate significantly less memory than expected, resulting in the writing of hundreds of megabytes of out-of-bounds data. As a result, the applications using libpq encounter errors.

9.1.1.x

The CVE-2024-10976 vulnerability in DWS needs to be fixed.

Row-level security policy tables are not completely tracked. As a result, reused queries can view or change unexpected rows. Specifically, when a query is planned under one role but executed under another role, an incorrect policy is applied, allowing users to perform prohibited read and write operations.

9.1.1.x

Performance

Hash algorithms optimized

In multi-column HASH JOIN and HASH AGG operations, multiple columns are treated as a single composite key for unified hashing and matching. This optimization significantly boosts performance on TPC-H and TPC-DS benchmarks.

enable_combine_hash controls whether the HASH JOIN or HASH AGG operator uses the combine hash algorithm for optimization. This function is enabled by default. This parameter takes effect only when there are multiple GROUP BY columns, JOIN columns, or non-JOIN columns on the join probe side.

enable_combine_hash

Runtime filter enhanced

Runtime Filter supports cross-VW. The overall performance of TPC-DS 3000x is improved by 5%.

N/A

Turbo engine enhanced

The following aggregate functions support the Turbo engine to improve performance.

sum, min, max, count, agg, and uniq.

N/A

Gather performance optimized for page turning

The overall performance of the numeric, string, and INT types is more than doubled.

The ninth parameter is added to late_read_thresholds. This parameter indicates the estimated memory threshold when value redistribute is used for topk. The value ranges from 0 to INTMAX, in KB. If this parameter is set to 0, value redistribute is disabled for topk.

late_read_thresholds

Automatically binding the optimal plan on Plan Management

Plan Management finds and applies the optimal execution plan to maintain consistent performance and avoid disruptions from changing plans. The system also deletes old plans regularly to free up storage space.

• The enable_plan_selection option is added to the GUC parameter planmgmt_options, indicating that an optimal plan is selected from multiple available plans for a statement.
• The GUC parameter planmgmt_selection_cost_threshold is added. When the optimizer creates a new plan that is not in the baseline and its cost falls below the set threshold, the system uses that plan for execution.
• The status, cost, and normalize_cost columns are added to the PG_PLAN_BASELINE system catalog and the SQL_PLAN_BASELINE view.
• The pgxc_clean_plan(clean_period integer), pgxc_create_auto_clean_plan_task(clean_period integer, clean_time timestamp without time zone, clean_interval interval), pgxc_alter_auto_clean_plan_task(clean_period integer, clean_time timestamp without time zone, clean_interval interval) and pgxc_drop_auto_clean_plan_task() functions are added to the plan management function.

• Plan Management
• PG_PLAN_BASELINE
• SQL_OUTLINE
• Plan Management Functions

Real-time analysis

DISTINCT in window functions

The following window functions support DISTINCT in the partition scenario and are compatible with Oracle: count, sum, min, max, avg, array_agg, stddev (stddev_samp), and variance (var_samp).

Window Functions

Kunpeng Accelerator Engine (KAE)

KAE is a hardware acceleration solution based on the Kunpeng 920 series processors. It includes an encryption and decryption module and a compression and decompression module. KAE speeds up data compression and decompression, cutting down on CPU usage and boosting its performance.

enable_kae_accelerate enables or disables KAE acceleration. No environment variable needs to be set.

To enable this function, set enable_kae_accelerate to on. For details, contact technical support.

Column-store tables created by default

If RELOPTIONS is not specified, the system automatically creates a HStore Opt table.

If column_default_format is set to all_hstore_opt and orientation is not set to row, the HStore Opt table is created by default.

column_default_format

Hybrid row-column storage optimized

Column-store 3.0, binlog, and light update are supported.

Using Hybrid Row-Column Storage

Partitioned tables optimized

ALTER TABLE DROP PARTITION and DROP partitioned table operations now execute faster. Faster data imports for IoT applications enhance real-time processing capabilities.

max_partition_per_table controls the maximum number of partitions in a partitioned table. cache_partition_options controls whether the system caches the most recent partitioning result when data is written to a partitioned table in batches.

To configure max_partition_per_table and cache_partition_options, contact technical support.

Lakehouse

Automatic mapping of foreign table's field names and data schemas

When data is read from Hive or other remote sources, changes like adding or removing columns in the source table trigger automatic updates to the field mappings in DWS. This prevents errors like mismatched field types and enhances service reliability.

• enable_external_column_index_access controls the matching mode between foreign table fields in the external schema and actual file fields. This parameter is enabled by default, indicating that foreign table fields and actual file fields are matched in sequence.
• column_index_access is added to CREATE FOREIGN TABLE (SQL on OBS or Hadoop) to control the matching mode between foreign table fields and actual file fields. Only read-only foreign tables in ORC or PARQUET format are supported.

• enable_external_column_index_access
• CREATE FOREIGN TABLE (SQL on OBS or Hadoop)

Security

Special characters allowed in accounts

The role name, username, and database name can contain special characters such as @ and periods (.) or consist solely of numbers.

iden_spec_char of behavior_compat_options controls whether to allow special characters in database object names when creating roles, users, and databases.

It is a beta feature. To use it, contact technical support.

behavior_compat_options

KAE encryption and decryption performance of Kunpeng optimized

The Kunpeng hardware acceleration engine boosts data encryption and decryption using the SM2, SM3, and SM4 algorithms. It enhances the encryption and decryption capabilities of the SM series cryptographic algorithms and the performance of clusters with TDE enabled.

kae_options controls whether to enable KAE acceleration. The default value is encryption, indicating that the function is enabled.

kae_options

Fine-grained table-level TDE

1. DWS supports table-level TDE for row-store tables and column-level TDE for column-store tables.
2. The data encryption granularity can be flexibly configured as required, and the impact on performance is reduced.
3. Key rotation helps prevent security issues by regularly using one data encryption key.
4. The system catalog PG_TDE stores the TDE key information.
5. The TDE-related parameters tde_config, tde_version, and tde_cache_config are added.
6. The ALTER CLUSTER syntax is added.
7. The encryption syntax is added to ALTER TABLE/CREATE TABLE.

It is a beta feature. To use it, contact technical support.

• PG_TDE
• To configure tde_config, tde_version, and tde_cache_config, contact technical support.
• ALTER CLUSTER
• ALTER TABLE
• CREATE TABLE

Basic quality

OBS I/O management across resource pools

1. OBS I/O resources can be managed in the storage and compute decoupling architecture.
2. When resources are available, the system schedules them as needed to improve OBS I/O efficiency.
3. When resources are limited, the system allocates them according to assigned quotas. Resources with a higher priority are scheduled first.
4. enable_obs_io_manager specifies whether to enable OBS I/O resource management.
5. Fields such as obs_io_read_kbytes, obs_io_write_kbytes, obs_io_read_bps and obs_io_write_bps are added to the system catalogs and views (such as xx_RESOURCE_xx and xx_SESSION_xx).
6. obs_io_share in ALTER/CREATE RESOURCE POOL specifies the OBS I/O weight of the resource pool.

It is a beta feature. To use it, contact technical support.

• To configure enable_obs_io_manager, contact technical support.
• CREATE RESOURCE POOL
• ALTER RESOURCE POOL

Internal thread status information added to the waiting views

The system monitors views and then detects abnormal status of auxiliary threads in a timely manner. This makes it easier to locate and analyze issues on the live network.

In the system view PG_THREAD_WAIT_STATUS, the following files are added in the I/O wait event list:

AuditWrite (audit log thread write file), LogRead (log thread read file), and LogWrite (log thread write file)

PG_THREAD_WAIT_STATUS

Internal connections optimized

Internal connections work better for high-concurrency situations. The communication thread between the CN and CCN is changed to a persistent connection. This approach maintains resource stability under excessive connections.

cn_connection_policy controls whether to enable internal connection optimization.

enable_wlm_cn_comm indicates that the internal connection optimization is enabled. When internal resource management is enabled, all services use a fixed channel to communicate with the CCN. No additional connections are used, preventing too many connections (threads) on the CCN. This parameter is enabled by default.

cn_connection_policy

Online scale-out

The self-service scaling success rate is improved, the time required is reduced, and the performance is optimized.

If a scale-out thread cannot obtain the lock after the scale-out has started for a long time, the system will terminate the peer thread that holds the lock to prevent the scale-out from failing and services from being blocked. If the peer thread is not in a transaction, the thread will be retried automatically, and no error will be reported. If the thread is in a transaction, the connection will be interrupted, and an error will be reported. In this case, the thread must be retried by the service.

It is a beta feature. To use it, contact technical support.

Scaling Out a Cluster

Binlog partition-level incremental computing

The system extracts incremental data only from specific partitions of the materialized view, as defined by the partition conditions. This avoids processing the entire table, making incremental updates faster and more efficient.

N/A

Functions added for detecting duplicate primary keys

The functions for detecting duplicate primary keys and clearing primary keys are added.

• pgxc_get_duplicate_pk(tableName regclass) checks whether the primary key of a table is duplicate. The table to be queried must have a primary key.
• pgxc_delete_duplicate_pk(tableName regclass, doActualDelete bool) removes duplicate primary keys from a column-store table while keeping the most recent entry. The table must include a defined primary key.

Hybrid Data Warehouse Functions

Self-check view of the dirty page rate at the database/schema level

PGXC_GET_ALL_TABLES_DIRTY_RATIO is used to obtain the dirty page ratios and sizes for the cudesc and delta tables and CU files of all HStore_opt tables in the current database.

PGXC_GET_ALL_TABLES_DIRTY_RATIO

User-mode cache management

The OS cache is automatically cleared in the background to ensure that the memory usage is within the range. It boosts database reliability during heavy I/O operations and fixes cluster errors that occur when reclaiming cache in blocking mode.

oscache_clean_policy specifying the policy for clearing the OS file cache.

The following functions are added:

• gs_table_oscache(regclass) queries the cache status of table data files in the OS file cache.
• gs_table_freecache(regclass) clears the OS file cache of the data files of a specified table.
• gs_file_freecache(IN file_path TEXT) clears the OS file cache of a specified file.
• gs_filelist_freecache(IN filelist_path TEXT) clears the OS file cache of the file list in a specified file.

• To configure oscache_clean_policy, contact technical support.
• Memory Management Functions

Flexible architecture

ANALYZE supported by elastic VWs

Elastic VWs support the ANALYZE function, reducing the pressure on the primary VW and improving system scalability. This function is controlled by the GUC parameter analyze_options. If the value is analyze_on_vw, this function is enabled. By default, this function is enabled.

analyze_options

Elastic VW execution capability enhanced

The job routing is more flexible and resource management mechanism is more diversified (except index point query and short query scenarios).

On-demand VW Scaling in the Storage and Compute Decoupling: Cost-Effective Adaptation to Flexible and Changing Service Requirements

Real-time analysis

Hybrid row-column storage supported

Hybrid row- and column-store tables (based on HStore Opt tables) are supported. The performance of full-field point query is improved by 3 to 5 times, matching the efficiency of row-store compression.

Using Hybrid Row-Column Storage

Light update supported by the HStore engine

The HStore engine supports light update. When some columns of a wide table are updated, the import performance is improved by more than three times, which is ahead of similar products. The query performance is affected by less than 20% and the eventual consistency is ensured.

When creating an HStore Opt table, enable the enable_light_update parameter and ensure that the global GUC parameter enable_hstore_lightupdate_table (whether to create an HStore Opt table for lightweight update) is enabled.

Volatile temporary tables supported in copy mode

The Copy mode supports volatile temporary tables, which are not recorded in Xlogs. This further improves the performance of batch import.

-

Autovacuum optimized

The timeliness of Autovacuum is greatly optimized. The delta tablespace expansion of the HStore engine can be controlled within 1x.

-

Binlog optimized

Binlog is available in the HStore Opt V3 tables with decoupled storage and compute. It supports partial update.

Hybrid Data Warehouse Binlog

Binlog optimizes the export and reverse query logic, full synchronization overhead between CNs and DNs, and auxiliary tablespace clearing logic. The performance is improved by about 30%, and the auxiliary tablespace is reduced by about 35%.

-

Consistent hash algorithm supported when DNs are directly connected for data import

The consistent hash algorithm is supported when DNs are directly connected for data import. The algorithm has been integrated into dws-connector.

Introduction to Flink SQL

Out-of-the-box performance improved

The out-of-the-box performance is optimized. The standard benchmark performance is improved by 30%. The Turbo engine SIMD is optimized and the global runtime filter is executed in distributed mode.

-

COUNT (DISTINCT xxx) statements written using the uniq() function

COUNT (DISTINCT) can be automatically converted to the built-in uniq() function, improving SQL performance.

This function is controlled by the count_distinct_rewrite_opt option of rewrite_rule. This function is enabled by default.

Filter pushdown enhanced

The filter pushdown supports INT and numeric types as well as greater than (>) and less than (<) expressions. The overall performance is improved by 20%, and the performance in advantageous scenarios is improved by 3 to 5 times.

This function is controlled by enable_cu_predicate_pushdown. After this function is enabled, the query performance is generally improved, especially when the bitmap_columns column and PCK sorting column are involved.

The allmeet quick filtering of roughcheck of CU improves performance by 35%.

-

High availability

Sub-health scenario detection added

Detection for subhealth scenarios such as network delay and packet loss is added. When such a sub-health scenario occurs, a primary/standby switchover is triggered in a timely manner to improve system reliability.

-

Automatic dump of secondary nodes' data to OBS

Data on the secondary nodes can be automatically dumped to OBS, preventing full disk space usage in fault scenarios.

-

Foreign table export performance in Parquet optimized

Data can be exported using foreign tables and the performance in Parquet is optimized to be the same as that in ORC.

-

OBS APIs optimized

In foreign tables, unnecessary OBS APIs, such as ListBucket and ListObject are not called. This method significantly improves performance when there are a large number of files.

The PG_DB_FILE system catalog is added to store information about OBS files.

The GET_TABLESPACE_OBS_FILE and PGXC_GET_TABLESPACE_OBS_FILE views are added to obtain all file information in the PG_DB_FILE system catalog of the previous tablespace.

High security

Operations of adding, deleting, and modifying audit logs optimized

The operations of adding, deleting, and modifying audit logs are optimized. Audit items such as insert overwrite and exchange partition are added.

-

New mechanism used for dumping audit logs to OBS

A new mechanism is used to dump audit logs to OBS. This prevents some logs from being lost in extreme scenarios.

-

Lakehouse

ANALYZE interconnection of foreign tables optimized

ANALYZE interconnection of foreign tables is optimized. The date type is supported.

-

Partition pruning capability for foreign table queries enhanced

The partition pruning capability of foreign table queries is enhanced. There are directory pruning, metadata service pruning, and partition information pruning.

-

O&M and monitoring

Online scale-out enhanced

V3 tables (with indexes) and Delta tables support online scale-out and redistribution. Services are not interrupted. Such tables follow the same constraints as V2 tables.

Scaling Out a Cluster

Query filter enhanced

The query filter supports the blacklist function in SQL hash mode.

Using DWS Query Filters to Intercept Slow SQL Statements

Based on the existing rules, the query filter adds capabilities such as resource pool switchover, concurrency control, and traffic limiting configuration to optimize the flexibility of filtering abnormal SQL statements.

Plan Management enhanced

Plan Management supports skew hints.

Plan Management

TopSQL view enhanced

The TopSQL view displays the reasons that jobs cannot be routed to the elastic logical clusters. The new view displays the number of statements that are executed in each logical cluster.

The transaction ID gxid and the inserted_rows, deleted_rows, updated_rows and returned_rows fields are added to the GS_WLM_SESSION_INFO, GS_WLM_SESSION_HISTORY, PGXC_WLM_SESSION_INFO, and GS_WLM_SESSION_STATISTICS views.

Resource pool management in whitelist optimized

The resource pool management in whitelist is optimized. The full match mode is supported. The SELECT 1, SHOW, DISCARD, COMMIT, and ROLLBACK statements are enabled.

-

Partition statistics optimized

The partition statistics are optimized. The execution time is combined, doubling the performance.

-

VACUUM FULL optimized

VACUUM FULL and concurrent services can share locks with each other. The lock priority can be identified, and lock operations can be refined by transaction block.

-

Auto-increment partitions optimized

The lock waiting mechanism is optimized for the scheduling of auto-increment partitions. This solves the lock timeout problem when partitions are automatically added or deleted. The management plane provides the service priority configuration capability.

-

Intelligent O&M

The intelligent O&M scheduler optimizes the scheduling sequence of the automatic vacuum full tables to resolve disk overload issues, such as automatic abandonment when the disk space is insufficient during vacuum full, and unprocessed dirty pages, small CUs, cold partitions, or OBS tables.

Managing O&M Plans

Autovacuum executed for V3 tables across VWs

V3 tables can be autovacuumed across VWs. It means that resources of the primary VW are not occupied.

-

Asynchronous sorting for non-PCK tables

Asynchronous sorting can be triggered for non-PCK tables to replace the small CU combination function.

-

Other optimizations

Upgrade behavior change

User-defined objects are not cascadingly deleted during the upgrade.

-