2 New Features Summary

PDF page 7

More than 200 new features have been added to PostgreSQL 16. 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:

Parallel Query Improvements

Parallel query is now available for execution of FULL OUTER JOIN syntax, RIGHT OUTER JOIN syntax, string_agg function and array_agg function.

Extended Statistics

Add the pg_stat_io view to get detailed I/O statistics. In addition, the pg_stat_all_tables view and the pg_stat_all_indexes view have added a column that shows the last access time for an object.

Improved batch processing performance

It is now possible to batch insert multiple records with the COPY statement for a foreign table using the postgres_fdw module.

2.2 Improved reliability

PostgreSQL 16 implements the following enhancements to improve reliability.

Improvements in logical Replication

Logical replication is one of the major enhancements in this version. Improved apply performance for long-running transactions, limited bi-directional replication, roles that allow SUBSCRIPTION creation, improved initial data transfer performance, logical decoding on standby instances, and much more.

Backup compression

LZ4 compression and Zstandard compression are now available for the output of the pg_dump command.

PDF page 8

2.3 Improved maintainability

The following features that can improve operability have been added.

Extension of the parameter file

Regular expressions can now be used for some setting items in the pg_hba.conf file and pg_ident.conf file. Also, the pg_hba.conf and pg_ident.conf files can now include external files, just like postgresql.conf.

Security

GSSAPI/Kerberos credential delegation is now supported. Add pg_use_reserved_connections to reserve reserved sessions for general users.

2.4 Improvements in Programming

The following functions have been added to SQL statements.

JSON-related

A function conforming to the SQL standard has been added to generate SQL/JSON. Add IS JSON clause that can check JSON syntax.

Subquery

The alias name is now optional if the FROM clause contains a subquery.

Literals

Numeric literals can now also contain binary, octal, and hexadecimal numbers. Underscores in numeric strings are supported.

2.5 Preparing for future new features

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

Extension of ICU locales

Adding original rules to the ICU locale is supported, and the UNICODE locale has been added.

PDF page 9

Standby instance

Logical decoding is now possible on the standby instance.

2.6 Incompatibility

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

2.6.1 End of support

PostgreSQL 16 changed the supported versions for the following platforms and tools. [9db300c, 495ed0e, 6203583, 8efefa7, 8b878bf, b086a47, 4c15327, 8efefa7, e692727] Platforms and tools that are no longer supported are listed below.

• HP-UX

• HP/Intel Itanium processor

• M68K, M88K, M32R and SuperH processors

• Microsoft Windows prior to Windows 10

• Microsoft Visual Studio 2013 Changes in supported versions of components required to build PostgreSQL 16 are as follows.

• Bison 2.3 or later

• Flex 2.5.35 or later

• Perl 5.14 or later

• GNU make 3.81 or later

2.6.2 Promotion

Standby database promotion by trigger file creation is no longer supported. The parameter promote_trigger_file has been removed accordingly. Use the pg_ctl promote command or pg_promote function to execute promotion. [cd4329d]

2.6.3 Bootstrap user attribute

The SUPERUSER attribute can no longer be removed from the bootstrap user. [e530be2]

PDF page 10

postgres=# \du
List of roles
-[ RECORD 1 ]----------------------------------------------------------
Role name  | postgres
Attributes | Superuser, Create role, Create DB, Replication, Bypass RLS
Member of  | {}
postgres=# ALTER USER postgres NOSUPERUSER ;
ERROR:  permission denied to alter role
DETAIL:  The bootstrap user must have the SUPERUSER attribute.

2.6.4 WAL Archive

The WAL archive of PostgreSQL 16 has the following incompatibilities: [d627ce3, 35739b8]

Parameter settings

The archive_command parameters and the archive_library can no longer be set at the same time. In PostgreSQL 15 archive_library was preferred. If set at the same time, the following error will be output to the log and the WAL archive will not be output.

$ tail -2 data/log/postgresql-2023-09-14_225124.log
FATAL:  both archive_command and archive_library set
DETAIL:  Only one of archive_command, archive_library may be set.

Specification change of the WAL archive module

The specification of the callback function of the shared library specified in the parameter archive_module has been changed. An address of type ArchiveModuleState has been added to the parameters of each callback.

PDF page 11

Callback Modify Call definition in PostgreSQL 16

2.6.5 Extension

Execution of the CREATE OR REPLACE statement on an existing object that does not belong to an extension script is prohibited. Previously, an extension script would execute the CREATE OR REPLACE statement, overwriting an existing object if one existed that did not belong to the extension. [b9b21ac]

2.6.6 Datetime string format

Date-time strings combining “epoch” and “infinity” with other date-time fields are prohibited in PostgreSQL 16. Also, ISO 8601 will be strictly enforced for string formatting. [bcc704b, 5b3c595]

postgres=> SELECT date '1995-08-06 epoch' ;
ERROR:  invalid input syntax for type date: "1995-08-06 epoch"
LINE 1: SELECT date '1995-08-06 epoch' ;
                    ^
postgres=> SELECT timestamp '1995-08-06 infinity' ;
ERROR:  invalid input syntax for type timestamp: "1995-08-06 infinity"
LINE 1: SELECT timestamp '1995-08-06 infinity';
postgres=> SELECT date '1995-08-06  J J J' ;
ERROR:  invalid input syntax for type date: "1995-08-06  J J J"
LINE 1: SELECT date '1995-08-06  J J J' ;

PDF page 12

2.6.7 ASCII string conversion

Changed the conversion rules when non-ASCII characters are specified for ASCII-only strings such as parameters application_name and cluster_name. Previously, it was converted in byte units with a question mark (?), but in PostgreSQL 16, it is converted to a hexadecimal string. [45b1a67]

2.6.8 ALTER DEFAULT PRIVILEGES statement

Execution of ALTER DEFAULT PRIVILEGES requires explicit privileges, not membership in a role. The ALTER DEFAULT PRIVILEGES statement included in the following example will succeed in PostgreSQL 15. [48a257d]

postgres=# CREATE USER role1 ;
CREATE ROLE
postgres=# CREATE USER role2 IN ROLE role1 NOINHERIT ;
CREATE ROLE
postgres=# \connect postgres role2
You are now connected to database "postgres" as user "role2".
postgres=> ALTER DEFAULT PRIVILEGES FOR ROLE role1 IN SCHEMA public GRANT SELECT
ON TABLES TO public ;
ERROR:  permission denied to change default privileges

2.6.9 ALTER TABLE statement

The unique indexes with the NULLS NOT DISTINCT clause can no longer be specified as primary keys for tables. [d959523]

PDF page 13

postgres=> CREATE TABLE pktest1(c1 INTEGER, c2 INTEGER) ;
CREATE TABLE
postgres=> CREATE UNIQUE INDEX idx1_pktest1 ON pktest1 (c1) NULLS NOT DISTINCT ;
CREATE INDEX
postgres=> ALTER TABLE pktest1 ADD PRIMARY KEY USING INDEX idx1_pktest1 ;
ERROR:  primary keys cannot use NULLS NOT DISTINCT indexes

2.6.10 CREATE RULE statement

In previous versions, it was possible to create an ON SELECT rule for a table and convert it to a view, but PostgreSQL 16 prohibits it. [b23cd18]

2.6.11 CREATE TABLE statement

System columns can no longer be used as FOREIGN KEYs. This change will be backported to the previous version. [f0d65c0]

postgres=> CREATE TABLE pktable (c1 INT PRIMARY KEY, c2 OID) ;
CREATE TABLE
postgres=> CREATE TABLE fktable (c1 INT, CONSTRAINT fk1 FOREIGN KEY (tableoid)
REFERENCES pktable(c2)) ;
ERROR:  system columns cannot be used in foreign keys

2.6.12 CREATEROLE attribute

In PostgreSQL 16, it is no longer possible to grant roles without the WITH ADMIN option to other users, even if they have the CREATEROLE attribute. In previous versions, users with the CREATEROLE attribute could perform most changes to other users. The first GRANT statement in the example below succeeds in PostgreSQL 15. [cf5eb37]

PDF page 14

postgres=# CREATE USER useradm1 PASSWORD '<<PASSWORD>>' CREATEROLE ;
CREATE ROLE
postgres=# \connect postgres useradm1
You are now connected to database "postgres" as user "useradm1".
postgres=> CREATE USER monitor1 PASSWORD '<<PASSWORD>>' ;
CREATE ROLE
postgres=> GRANT pg_monitor TO monitor1 ;
ERROR:  permission denied to grant role "pg_monitor"
DETAIL:  Only roles with the ADMIN option on role "pg_monitor" may grant this
role.
postgres=> \connect postgres
You are now connected to database "postgres" as user "postgres".
postgres=# GRANT pg_monitor TO useradm1 WITH ADMIN OPTION ;
GRANT ROLE
postgres=# \connect postgres useradm1
You are now connected to database "postgres" as user "useradm1".
postgres=> GRANT pg_monitor TO monitor1 ;
GRANT ROLE

2.6.13 POWER function

In PostgreSQL 16, the accuracy when specifying an integer to the POWER function is improved, and the effective accuracy matches when specifying a non-integer. This fix will not be backported to previous versions. [40c7fcb]

postgres=> SELECT power(0.084738, 16) ;
       power
--------------------
 0.0000000000000000
(1 row)

PDF page 15

postgres=> SELECT power(0.084738, 16) ;
                power
-------------------------------------
 0.000000000000000007067264604102046
(1 row)

2.6.14 RESET statement

The RESET statement for the parameters below will fail. [3853664]

• transaction_read_only

• transaction_deferrable

• seed

• transaction_isolation

postgres=> RESET transaction_read_only ;
ERROR:  parameter "transaction_read_only" cannot be reset
postgres=> RESET transaction_deferrable ;
ERROR:  parameter "transaction_deferrable" cannot be reset
postgres=> RESET seed ;
ERROR:  parameter "seed" cannot be reset
postgres=> BEGIN ;
BEGIN
postgres=*> RESET transaction_isolation ;
ERROR:  parameter "transaction_isolation" cannot be reset
postgres=!> ROLLBACK ;
ROLLBACK

2.6.15 Pg_stat_get_backend_idset function

The pg_stat_get_backend_idset function now returns the ID of the backend process. Until the previous version, it returned the order starting from 1. [d7e39d7]

PDF page 16

postgres=> SELECT pg_stat_get_backend_idset() ;
 pg_stat_get_backend_idset
---------------------------
                         1
                         2
                         3
                         4
                         5
                         6
(6 rows)

postgres=> SELECT pg_stat_get_backend_idset() ;
 pg_stat_get_backend_idset
---------------------------
                         1
                         2
                         4
                       124
                       126
                       127
(6 rows)

2.6.16 Pg_walinspect extension

Pg_get_wal_records_info function, pg_get_wal_stats function, and pg_get_wal_block_info function of pg_walinspect extension no longer generate an error even if the end LSN specified is higher than the current LSN. The pg_get_wal_records_info_till_end_of_wal and pg_get_wal_stats_till_end_of_wal functions, which are no longer needed due to this change, have been removed. [5c1b662]

PDF page 17

2.6.17 Postmaster

The -T option may send a SIGABRT signal instead of a SIGSTOP signal by setting the send_abort_for_crash parameter. Also, the -n option has been removed. Symbolic links pointing to the postmaster are no longer created. [51b5834, 37e2673]

2.6.18 Psql

The following specifications of the psql command have been changed. [3dfae91, 00beecf]

The \df+ meta-command

The \df+ meta-command output column name “Source code” changed to “Internal name”.

postgres=> \df+ scale
List of functions
-[ RECORD 1 ]-------+------------------------------------------------
Schema              | pg_catalog
Name                | scale
Result data type    | integer
Argument data types | numeric
Type                | func
Volatility          | immutable
Parallel            | safe
Owner               | postgres
Security            | invoker
Access privileges   |
Language            | internal
Internal name       | numeric_scale
Description         | number of decimal digits in the fractional part

The \watch meta-command

Negative numbers and strings specified in the \watch meta command will result in an error. Previous versions assumed 1 second. If 0 is specified, the command will be re-executed without an interval.

PDF page 18

postgres=> \watch -1
\watch: incorrect interval value "-1"
postgres=> \watch abc
\watch: incorrect interval value "abc"

2.6.19 Libpq

Removed authorization code with SCM credentials. The backend code was removed in PostgreSQL 9.1, but the libpq code remained. [98ae2c8]

2.6.20 PL/Python

The {installation destination}/lib/pgxs/src/pl/plpython directory is no longer created. [7d5852c]

2.6.21 ECPG

The SQL keywords used in EXEC SQL commands can no longer be declared as typedefs. The following example will result in a syntax error. [83f1c7b]

EXEC SQL BEGIN DECLARE SECTION;
    typedef int start;
EXEC SQL END DECLARE SECTION;
...
EXEC SQL START TRANSACTION;

2.6.22 configure

The options of the configure command have been changed as follows. [20c105c, fcb21b3]

PDF page 19

PDF page 20