All Products
Search

This Product

    PolarDB:PolarDB for PostgreSQL 15 is formally launched

    Document Center

    PolarDB:PolarDB for PostgreSQL 15 is formally launched

    Last Updated:Dec 18, 2024

    PolarDB for PostgreSQL that is compatible with PostgreSQL 15 is formally launched.

    Overview

    PolarDB for PostgreSQL that is compatible with PostgreSQL 15 focuses more on improving performance. It excels in managing workloads in local and distributed deployments.

    It mainly enhances and optimizes the following aspects:

    • Sorting: The sorting algorithm for memory and disks is improved.

    • SQL: The standard SQL MERGE statement is supported. New function that use regular expressions are added to check strings.

    • Logical replication: You can replicate two-phase commit (2PC) transactions. You can use WHERE to filter the published tables. You can publish all tables in a schema at a time.

    • Database performance: Memory usage is reduced. Crash recovery is accelerated.

    Features

    Based on PostgreSQL 15, PolarDB also supports additional features, such as GanosBase engine, global execution plan cache, and GBK/GB18030 character set. The following extensions are supported in the first release of PolarDB for PostgreSQL 15: hll, hypopg, log_fdw, pase, pg_bigm, pg_cron, pg_jieba, pg_repack, pg_similarity, pgtap, pgvector, pldebugger, prefix, roaringbitmap, rum, varbitx, zhparser, and ganos.

    PolarDB also implements performance optimization, such as optimizing SIMD instruction set and atomic operation instruction set, and enabling them in transaction processing modules. System-level optimization methods such as PGO, BOLT, LTO, and huge pages are used. Batch data reading, scaling, improved I/O performance, and parameter tuning are supported to provide excellent performance.

    Note

    For more information, see the community announcement at https://www.postgresql.org/docs/15/release-15.html.

    Release date

    July 19, 2024

    Billing methods

    The subscription and pay-as-you-go billing methods are supported. You can also use them in combination with multiple compute and storage plans for flexible billing. For more information, see Billing.

    Key improvements

    PolarDB for PostgreSQL that is compatible PostgreSQL 15 provides the following key improvements:

    Improved sorting and compression performance

    • The sorting algorithm for memory and disks is improved. The benchmark test results show that the sorting speed is accelerated by 20% to 400% for different data types.

    • The performance of the following functions is improved when they are used as window functions: row_number(), rank(), dense_rank(), and count().

    • Parallel queries using the SELECT DISTINCT statement are supported.

    • The asynchronous remote query feature based on PostgreSQL 14 supports asynchronous submission of the postgres_fdw wrapper.

    • WAL log files can be compressed in the LZ4 and Zstandard (zstd) formats to improve space usage and performance.

    • For some operating systems, preloading of WAL pages is supported to shorten the recovery time.

    • The backup command pg_basebackup built in PostgreSQL can be used to compress server-side backup files in the gzip, LZ4, and zstd formats.

    • Custom module archiving capabilities are supported to reduce the costs of using shell commands.

    Developer features

    • The standard MERGE statement is supported. You can write SQL statements that contain the INSERT, UPDATE, and DELETE operations.

    • The following new functions that use regular expressions are added to check strings: regexp_count(), regexp_instr(), regexp_like(), and regexp_substr().

    • The range_agg function is added to aggregate multirange data types.

    • The security_invoker option is added to the CREATE VIEW statement to allow users with guest permissions instead of view creator permissions to create views. Additional protections are provided to ensure that the view caller uses the proper permissions to process underlying data.

    More logical replication options

    • Logical replication flexibility is improved by introducing row filtering and data column lists for publication. You can select a subset of the data and copy it from the table.

    • Some features are added to simplify conflict management.

      • Conflicting transactions can be skipped and executed again.

      • Subscriptions can be automatically stopped when an error is detected.

    • 2PC is supported in logical replication.

    Log and configuration enhancements

    • The JSON log format is supported. Log data can be printed in the JSON format. You can process PostgreSQL logs in the structured log system.

    • In terms of PostgreSQL configuration management, the database administrator can grant users permissions to modify server-level configuration parameters.

    • The \dconfig command is added to allow you to view the configuration information using the psql command tool.

    Other noteworthy changes

    • Server-level statistics are collected to shared memory, which optimizes the statistics collection process and the process of periodically writing statistics to the disk.

    • ICU sorting may be used as the default sorting for a cluster or a single database.

    • The built-in pg_walinspect module is added, which allows you to check the contents of WAL log files using SQL.

    • The CREATE permissions can be revoked for all accounts in databases of the public (or default) schema, except the database owner.

    • The long-deprecated excusive backup mode is removed. PL/Python does not support Python 2.

    Updates

    We recommend that you upgrade to PolarDB for PostgreSQL 15. After you use PolarDB for PostgreSQL that is compatible with PostgreSQL 15, 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 from PolarDB for PostgreSQL 14 to PolarDB for PostgreSQL 15, 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 such improvements.

    Data types

    • Zero-length Unicode identifiers such as U&"" are prohibited.

    • Numeric text cannot have non-digit characters at the end. For example, 123abc is parsed as 123.

    • JSON numeric text is processed in a way that comply with SQL/JSON standards. Both 1. and .1 are valid, while non-digit endings such as 1.type() are invalid.

    • If the Interval value is fractional, it is rounded to the nearest month. For example, 1.99 year is converted to 2 years instead of 1 year 11 months that was used in the past.

    • Parsing consistency of Interval and trailing period is improved.

    • char: Non-ASCII characters are displayed in octal notion.

    Functions and expressions

    • array_to_tsvector(): If you enter an empty string as an array element, an error is returned.

    • chr(): If you enter a negative argument, an error is returned.

    • Because a function whose output type is interval depends on IntervalStyle, the function marked as stable instead of as immutable.

    • The integer overflow check is added for the following interval functions: justify_interval(), justify_hours(), and justify_days().

    • The random() algorithm is improved: Even if the seed value is the same, the results of random() is different from the previous version.

    Query optimization

    • The default value of Hash_mem_multiplier is increased to 2.0, allowing hash query operations to use more work_mem.

    • The CREATE OR REPLACE VIEW statement cannot change the sorting of output columns.

    Permissions

    • By default, the login role does not have the ADMIN OPTION permissions on its own members. A login role cannot add or remove members of its own role without the ADMIN OPTION permissions.

    • Logical replication can run as the subscription owner.

    • The subscription owner cannot perform logical replication operations (UPDATE DELETE) on tables on which the subscription owner does not have the SELECT permissions.

    Others

    • The exclusive backup mode and related functions are removed.

    • Python 2 is not supported.

    • The xml_is_well_formed function is removed.

    • The PQsendQuery() function of libpq does not support the pipeline mode, which may cause incompatibility with some applications or drivers.

    • The obsolete stats_temp_directory parameter is removed.

    • When a schema of a session temporary object is referenced in the EXPLAIN statement, it is called a pg_temp. The old schema name is also included.