2 New Features Summary

PDF page 8

More than 200 new features have been added to PostgreSQL 17. This chapter provides an overview of typical new features and benefits. Details of the new features will be explained in “3. New Feature Detail”.

2.1 Improvements to adapt to large scale environments

The following features have been added that can be applied to large scale environments:

Incremental Backup

Incremental backups are now supported as a standard feature of PostgreSQL. pg_basebackup command now has an option to take incremental backups. To perform an incremental backup, the manifest of the base-backup is used to determine the incremental backup. New command pg_combinebackup merges the base and incremental backups.

Supports large-scale memory

The SLRU cache is divided into multiple banks, which is expected to reduce the lock range and improve cache search speed. Additionally, several parameters have been added to determine the size of each SLRU memory region.

2.2 Improved reliability

PostgreSQL 17 implements the following enhancements to improve reliability.

Logical Replication Slot Synchronization

The replication slot information used for logical replication can now be synchronized to the standby server for streaming replication.

Wait for Streaming Replication

The feature has been added to wait for logical replication updates until change data has been sent to the standby server for streaming replication.

PDF page 9

2.3 Improved maintainability

The following features that can improve operability have been added.

Checkpointer Statistics

The pg_stat_checkpointer view has been added to retrieve checkpointer process statistics. some columns in the pg_stat_checkpointer view have been moved from the pg_stat_bgwriter view.

Enhancement of the parameter files

The maximum length of token names that can be used in the pg_hba.conf and pg_ident.conf files has been changed from 256 bytes to unlimited.

MAINTAIN privilege

The MAINTAIN privilege has been added to perform maintenance operations such as VACUUM, ANALYZE, and REINDEX statements. The pg_maintain predefined role has been added to allow maintenance operations on objects for all users.

Login event trigger

The login event trigger is now available that executes upon successful authentication.

2.4 Improvements in Programming

The following functions have been added to SQL statements.

JSON-related

Multiple JSON constructors and many JSONPATH methods have been added. Also added JSON_EXISTS, JSON_QUERY, JSON_VALUE, and JSON_TABLE functions.

COPY statement

An option has been added to continue processing in the occurrence of data type conversion errors.

MERGE statement

The MERGE statement now supports updatable views. Also, the RETURNING clause and BY SOURCE clause can now be specified.

PDF page 10

PL/pgSQL

Variable declarations can now specify arrays for the %TYPE and %ROWTYPE attributes, which indicate the data type of table columns and tuples as a whole.

2.5 Preparing for future new features

PostgreSQL 17 is now ready for features that will be provided in future versions.

Wait Event view

The pg_wait_events view has been added to retrieve the names of wait events. Currently, only the name and description of the wait event is available. In the future, it is expected to acquire the cumulative time of wait events, etc.

Providing new I/O methods

The foundation for using asynchronous I/O and multiple block I/O is now provided.

2.6 Incompatibility

In PostgreSQL 17, the following specifications have been changed from PostgreSQL 16.

2.6.1 End of support

PostgreSQL 17 changed the supported versions for the following platforms and tools. [1301c80, 8e278b6, 820b5af, 0b16bb8, cc09e65] The platforms and tools that are no longer supported are listed below.

• Microsoft Visual Studio

• LLVM 9 or earlier

• IBM AIX

• adminpack contrib module Changes in supported versions of components required to build PostgreSQL 17 are as follows.

• OpenSSL 1.0.2 or later

• LLVM 10 or later

PDF page 11

2.6.2 Configure command

The following configure command options have been removed. [68a4b58, 1c1eec0]

2.6.3 MERGE statement

The SELECT privilege on the target table is now required even when specifying the DO NOTHING clause in a MERGE statement. This specification will be backported to PostgreSQL 15 and later. [4989ce7]

postgres=# CREATE TABLE merge1(c1 INT, c2 VARCHAR(10)) ;
CREATE TABLE
postgres=# \connect postgres demo
You are now connected to database "postgres" as user "demo".
postgres=> MERGE INTO merge1 USING (SELECT 1) ON true
WHEN MATCHED THEN DO NOTHING ;
MERGE 0

postgres=# CREATE TABLE merge1(c1 INT, c2 VARCHAR(10)) ;
CREATE TABLE
postgres=# \connect postgres demo
You are now connected to database "postgres" as user "demo".
postgres=> MERGE INTO merge1 USING (SELECT 1) ON true
WHEN MATCHED THEN DO NOTHING ;
ERROR:  permission denied for table merge1

PDF page 12

2.6.4 Explain statement

The output format of subqueries with the EXPLAIN statement has been changed. [fd0398f]

postgres=> EXPLAIN (COSTS OFF) SELECT * FROM data1 WHERE
c1=(SELECT MAX(c1) FROM data1) ;
                                    QUERY PLAN
---------------------------------------------------------------------------
Index Scan using data1_pkey on data1
   Index Cond: (c1 = $1)
   InitPlan 2 (returns $1)
     ->  Result
           InitPlan 1 (returns $0)
             ->  Limit
                   ->  Index Only Scan Backward using data1_pkey on data1
data1_1
                         Index Cond: (c1 IS NOT NULL)
(8 rows)

postgres=> EXPLAIN (COSTS OFF) SELECT * FROM data1 WHERE
c1=(SELECT MAX(c1) FROM data1) ;
                                    QUERY PLAN
---------------------------------------------------------------------------
Index Scan using data1_pkey on data1
   Index Cond: (c1 = (InitPlan 2).col1)
   InitPlan 2
     ->  Result
           InitPlan 1
             ->  Limit
                   ->  Index Only Scan Backward using data1_pkey on data1
data1_1
(7 rows)

PDF page 13

2.6.5 Pgrowlocks function

The output of the modes column of the results of executing the pgrowlocks function in the pgrowlocks extension has been changed. This change will be backported to the previous version. [15d5d74]

postgres=> SELECT * FROM pgrowlocks('data1') ;
 locked_row | locker | multi | xids  |       modes       |  pids
------------+--------+-------+-------+-------------------+--------
 (0,1)      |    747 | f     | {747} | {"For Share"}     | {9555}
 (0,2)      |    747 | f     | {747} | {"For Key Share"} | {9555}
(2 rows)

2.6.6 Other functions

There is an incompatibility with the pg_walfile_name / pg_walfile_name_offset functions. In previous versions, these functions returned the previous segment number when the LSN was on a segment boundary. It has been changed to always return the current segment number of the LSN. This fix is also reflected in previous versions. [344afc7]

PDF page 14