PolarDB:PolarDB for PostgreSQL 16 is officially released

Performance improvements

The performance of PostgreSQL functionalities is improved through a new query planner.

• Supports parallel execution of FULL and RIGHT joins.

• Generates efficient execution plans for aggregations that use DISTINCT or ORDER BY clauses.

• Introduces incremental sorting to improve the performance of SELECT DISTINCT queries.

• Optimizes window functions to improve the execution efficiency of related queries.

• Improves the handling of RIGHT and OUTER anti-joins to enable better identification of rows not present in the joined tables.

PostgreSQL 16 improves the single-threaded and concurrent COPY operations for bulk data loading. Tests show that performance can improve by up to 300%. It also supports client-side load balancing for applications that use the libpq library. The vacuum policy is improved to reduce the need for full-table freeze vacuums. SIMD-based CPU acceleration is applied in x86 and ARM architectures. This improves system performance in processing ASCII and JSON strings and conducting array and subtransaction searches.

Logical replication

Logical replication duplicates data streams to other nodes or subscribers that support the PostgreSQL logical replication protocol. PostgreSQL 16 supports logical replication from read-only nodes to publish logical changes to other servers. This gives developers new options for distributing workloads, such as offloading logical replication tasks from a high-load primary node to a read-only node.

PostgreSQL 16 provides performance improvements for logical replication. Subscribers can simultaneously handle large transactions. Subscribers can use B-tree indexes instead of sequential scans to find rows in tables without a primary key. In specific cases, the binary format can be used to accelerate initial table synchronization.

PostgreSQL 16 improves access control for logical replication. A new predefined role, pg_create_subscription, is added to allow users to create logical subscriptions.

PostgreSQL 16 supports bidirectional logical replication. Data can be replicated between tables from two different publishers.

Developer experience

PostgreSQL 16 supports more standard SQL/JSON syntax, including constructors and predicates, such as JSON_ARRAY(), JSON_ARRAYAGG(), and IS JSON. Underscores (_) can be used as thousands separators. Example: 5_432_000. Non-decimal integer constants, such as 0x1538, 0o12470, and 0b1010100111000, are supported.

More psql commands are supported, including \bind. This allows you to use queries with parameters and use \bind instead of variables. Example: SELECT $1::int + $2::int \bind 1 2 \g.

Text collations are improved. By default, ICU is enabled. The default ICU locale is determined from the system environment. ICU collation rules can be customized.

Monitoring

Monitoring I/O operations is crucial in optimizing database performance. PostgreSQL 16 introduces the pg_stat_io metric to improve the analysis of I/O access patterns.

A new field is added to the pg_stat_all_tables view to record the timestamp of the last scan performed on a table or index. Parameter values passed to queries are recorded, which improves the readability of auto_explain logs and the accuracy of query tracking in the pg_stat_statements and pg_stat_activity views.

Access management and security

PostgreSQL 16 provides more granular access management options and enhanced security features. Regular expressions can be used to match database and role entries in pg_hba.conf and pg_ident.conf files. The include directive can be used to reference external configuration files.

The following client connection parameters related to security are added:

• require_auth: specifies the acceptable authentication methods.

• sslrootcert="system": allows libpq to use the system certificate pool for certificate verification.

Kerberos trust delegation is supported, which allows postgres_fdw and dblink plug-ins to connect to trusted services by using authenticated credentials.

Upgrade

We recommend that you upgrade your cluster to PolarDB for PostgreSQL 16. After you upgrade your cluster to PolarDB for PostgreSQL compatible with PostgreSQL 16, you can keep up with the PostgreSQL community and get more resources and support to help you solve problems and learn best practices for databases.

After you upgrade your cluster from PolarDB for PostgreSQL 15 to PolarDB for PostgreSQL 16, you can enjoy more improvements of technical details while most data types, built-in functions, table columns, and objects remain unchanged. You can use logical replication (DTS is recommended. For more information, see Migrate data between PolarDB for PostgreSQL versions) to achieve near 100% compatibility. The following sections describe the improvements.

Data types

The NULLS NOT DISTINCT indexes cannot be used for primary keys.

Functions and expressions

• Assignment rules for bound cursor variables in PL/pgSQL are changed. In releases earlier than PostgreSQL 16, the string value of such variables is set to match the variable name during cursor assignment. In PostgreSQL 16, the value is assigned during the OPEN operation and no longer matches the variable name. To restore the previous setting, assign the desired portal name to the cursor variable before the OPEN operation.

• The pg_walinspect functions pg_get_wal_records_info_till_end_of_wal() and pg_get_wal_stats_till_end_of_wal() are removed.

• Restrictions on GENERATED expressions in inherited and partitioned tables are stricter. All columns of parent/partitioned and child/partition tables must have the same generation status. However, the actual generation expressions may differ between them.

• The REINDEX DATABASE statement and the reindexdb utility no longer process indexes on system catalogs. You must use the REINDEX SYSTEM statement and the reindexdb --system utility instead.

• ON SELECT rules can no longer be used to manually create views.

Permissions

• The default inheritance behavior of roles can be overridden by using the new GRANT ... WITH INHERIT clause. This allows a member to inherit specific roles while not inheriting others, because the inheritance status of member roles is set in the GRANT command. Previously, the inheritance state of member roles is controlled only by its own inheritance status, and changes to the inheritance status of roles affect all existing and future member roles.

• The ability of a role with CREATEROLE privileges to modify other roles is restricted. In releases earlier than PostgreSQL 16, roles with CREATEROLE privileges can change many non-superuser role permissions. In PostgreSQL 16, operations, such as adding members to roles, require the ADMIN OPTION permission. For example, roles can modify properties, such as CREATEDB, REPLICATION, and BYPASSRLS, only if they have the ADMIN OPTION permission.

Others

• The lc_collate and lc_ctype read-only parameters are removed.

• The boost_trigger_file parameter is removed.

• The vacuum_defer_cleanup_age parameter is removed.

• The force_parallel_mode parameter is renamed to debug_parallel_query.