PolarDB:PolarDB for PostgreSQL 17 is officially released

Performance improvements

• Reduced memory consumption of the vacuum process: The vacuum process is critical to the health of the PostgreSQL database. However, it consumes a lot of cluster resources. PostgreSQL 17 introduces a new internal memory structure for vacuum operations to decrease memory consumption by up to 20 times. This accelerates the vacuum process, decreases its usage of shared resources, and frees up more resources for other workloads.

• Improved I/O performance: PostgreSQL 17 improves write-ahead logging (WAL) processing and doubles the write throughput in high-concurrency scenarios. The new streaming I/O interface accelerates sequential scans (full table scans) and ANALYZE operations.

• Improved query performance: PostgreSQL 17 improves the performance of IN clause queries with B-tree indexes (default indexing method in PostgreSQL). This release also supports the parallel building of block range indexes (BRINs). PostgreSQL 17 improves query planning and optimizes NOT NULL constraints and common table expressions (WITH queries). This release uses more Single Instruction/Multiple Data (SIMD) instructions to accelerate computations, including using AVX-512 to accelerate the bit_count function.

Developer experience

• PostgreSQL was the first relational database to introduce native JSON support in 2012 and has continuously improved the implementation of the SQL/JSON standard in PostgreSQL 17.

  • Introduces the JSON_TABLE() function to convert JSON data into standard PostgreSQL tables.

  • Introduces SQL/JSON constructor functions JSON, JSON_SCALAR, and JSON_SERIALIZE and query functions JSON_EXISTS, JSON_QUERY, and JSON_VALUE to give developers more ways to interact with JSON data.

  • Adds more JSONPath expressions to convert JSON data to native PostgreSQL data types, including numbers, booleans, strings, and dates/times.

• PostgreSQL 17 adds more functionalities to the MERGE statement, including the RETURNING clause for conditional updates and the ability to update views.

• PostgreSQL 17 adds new features that improve batch loading and data export. Exporting large rows by using the COPY statement is now up to two times faster. When the source encoding matches the target encoding, the COPY statement performs more efficiently. Additionally, the ON_ERROR option is introduced to ensure an uninterrupted import process even if insertion errors occur.

• PostgreSQL 17 extends the capabilities for managing partitioned data and data distributed across remote PolarDB PostgreSQL clusters. This release supports identity columns and exclusion constraints in partitioned tables. The PostgreSQL foreign data wrapper (postgres_fdw) supports pushing down EXISTS and IN subqueries to remote servers for more efficient query processing.

• PostgreSQL 17 introduces a built-in, cross-platform, and guaranteed immutable collation provider. This provider has the same sorting semantics as the C collation but uses UTF-8 encoding instead of SQL_ASCII. It also ensures that text-based queries return consistent sorting results, regardless of the platform used by PostgreSQL.

Logical replication

Logical replication is essential for real-time data streaming in various scenarios. In releases earlier than PostgreSQL 17, the logical replication slots must be removed before a major version upgrade. As a result, subscriber data must be resynchronized after the upgrade. Starting from PostgreSQL 17, a major upgrade can be performed without removing logical replication slots.

PostgreSQL 17 introduces failover control for logical replication and enhances reliability in high-availability environments. This release introduces the pg_createsubscriber command-line tool, which facilitates logical replication on replicas that use physical replication.

Security and operation management options

• PostgreSQL 17 improves how users manage their database systems throughout the entire lifecycle. PolarDB for PostgreSQL introduces a new Transport Layer Security (TLS) option called sslnegotiation. It allows users to perform a direct TLS handshake when using Application-Layer Protocol Negotiation (ALPN), with postgresql registered in the ALPN directory.

• PostgreSQL 17 adds the pg_maintain role, which allows users to perform maintenance operations.

• The native PostgreSQL backup tool pg_basebackup of PostgreSQL 17 supports incremental backups. The pg_combinebackup tool is added to reconstruct full backups. The --filter option is added to the pg_dump tool to allow users to specify the objects to include in the dump.

• PostgreSQL 17 also enhances monitoring and analysis capabilities.

  • The time spent on local I/O block reads and writes is displayed in the EXPLAIN statement. The statement also includes two new options, namely, SERIALIZE and MEMORY, which show the time spent on data conversion during network transmission and the memory usage, respectively.

  • The index cleanup progress can be viewed. The pg_wait_events system view is added. Together with the pg_stat_activity view, this new view can help you better understand why active sessions are waiting.

Data types

• ago appears only at the end of interval values.

• Multiple empty interval units are not allowed.

Views

• The pgrowlocks lock mode output labels are updated. Share is changed to For Share and Key Share is changed to For Key Share.

• The file boundary handling of two WAL file name functions is updated. In releases earlier than PostgreSQL 17, the pg_walfile_name() and pg_walfile_name_offset() functions return the previous LSN segment number when an LSN is on a file segment boundary. In PostgreSQL 17, the two functions return the current LSN segment.

• The domain_default field is removed from the element_types table in the information_schema.

• The I/O block read/write timing statistics columns of pg_stat_statements are renamed. The blk_read_time column is renamed to shared_blk_read_time and the blk_write_time column is renamed to shared_blk_write_time.

• The representation of the default statistics target is changed. The pg_attribute.attstattarget and pg_statistic_ext.stxstattarget columns use NULL to represent their default statistics targets.

• The localization-related columns are renamed. The pg_collation.colliculocale column is renamed to colllocale and the pg_database.daticulocale column is renamed to datlocale.

• The columns that track the progress of VACUUM operations are reconstructed. The max_dead_tuples column is renamed to max_dead_tuple_bytes, the num_dead_tuples column is renamed to num_dead_item_ids, and the dead_tuple_bytes column is added.

• The SLRU-related statistics columns are renamed. The pg_stat_slru system view and the column names accepted by the pg_stat_reset_slru() function are changed.

Functions and expressions

Functions are required to use a safe search_path during maintenance operations (ANALYZE, CLUSTER, CREATE INDEX, CREATE MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW, REINDEX, or VACUUM) to prevent unauthorized access. Functions used by expression indexes and materialized views that need to reference non-default schemas must specify a search path upon creation.

Permissions

• The SET SESSION AUTHORIZATION handling of the initial session user's superuser status is updated. The new handling is based on the session user's superuser status when the SET SESSION AUTHORIZATION command is run, not the superuser status at the connection time.

• The db_user_namespace feature that simulates per-database users is removed due to rare use.

Others

• The old_snapshot_threshold server parameter is removed. This parameter allows the vacuum process to remove rows that may still be visible to a running transaction, causing a snapshot too old when accessed later.

• The adminpack extension is removed. This extension was used by pgAdmin III, which has reached end of life (EOL).

• The trace_recovery_messages server parameter is removed.