Skip to content

2 New Features Summary

The source document’s overview of the release, benefits, and incompatibilities.
Beta 1Source pages: PDF 8-17Original PDF on GitHub

PDF page 8

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

Splitting and Merging Partitions

The feature to merge multiple partitions and split a single partition has been implemented for a partitioned tables.

Dynamic Table Configuration Changes

It is now possible to modify the evaluation method for virtual columns and the ENFORCED setting for CHECK constraints using the ALTER TABLE statement.

Use of Parallel Workers in Automatic VACUUM

In previous versions, parallel workers were not used during automatic VACUUM. In PostgreSQL 19, automatic VACUUM now supports parallel processing.

2.2 Improved reliability

PostgreSQL 19 implements the following enhancements to improve reliability.

Addition of Output Formats for the pg_dumpall Command

The pg_dumpall command now supports output formats other than text. If a format other than text is selected, the restore operation uses the pg_restore command.

SSL/SNI Support

PostgreSQL server-side support for SSL/SNI has been added. The pg_hosts.conf file is now available for configuring SNI settings.

PDF page 9

2.3 Improved maintainability

The following features that can improve operability have been added.

Sequence Replication and Excepted Tables

Sequence replication is now supported in logical replication environments. it is now possible to specify tables that are excepted from replication on the publication side.

REPACK Statement

The REPACK statement has been added to execute VACUUM FULL and CLUSTER statements simultaneously.

2.4 Improvements in Programming

The following features have been added to SQL statements.

SQL Property Graph

PROPERTY GRAPH objects, which execute graph queries, can now be created. The GRAPH_TABLE function is provided for PROPERTY GRAPH queries.

Enhancement to the INSERT Statement

The DO SELECT clause can now be specified in the INSERT ON CONFLICT statement.

Enhancement to the UPDATE / DELETE Statements

The ON PORTION OF clause, which enables partial updates of the range, is now supported in UPDATE and DELETE statements.

GROUP BY ALL Clause

The GROUP BY ALL clause, which executes GROUP BY processing on all output columns, is now supported.

New Contrib Modules

The Contrib modules pg_plan_advice and pg_stash_advice, which control execution plans, have been added.

PDF page 10

2.5 Incompatibility

In PostgreSQL 19, the following specifications have been changed from PostgreSQL 18.

2.5.1 Support Information

PostgreSQL 19 changes the supported versions for the following platforms and tools The changes in component support versions required to build PostgreSQL 19 are as follows.

  • Meson 0.57.2 or higher [f039c22]

  • gcc 4.7 or higher [4fbe015]

  • C11 standard [f5e0186]

  • Visual Studio 2019 or higher [8fd9bb1] The following components are now supported in PostgreSQL 19.

  • Tcl 9 support added [32a2aa7] PostgreSQL 19 has restored support for the following operating systems.

  • IBM AIX [4a1b05c] PostgreSQL 19 now supports the following standards.

  • UNICODE 17.0.0 [57ee397]

  • GB18030 2022 [5334620]

  • C23 standards [76f4b92]

2.5.2 Transaction ID Round Warning

The DETAIL log now includes a percentage remaining in the transaction ID round warning message. Additionally, the threshold for triggering the warning has been changed from 40 million to 100 million remaining IDs. [e646450, 48f11bf]

PDF page 11

Example 1 Transaction Round Warning Log Message
WARNING:  database "postgres" must be vacuumed within 3001666 transactions
DETAIL:  Approximately 0.14% of transaction IDs are available for use.
HINT:  To avoid transaction ID assignment failures, execute a database-wide
VACUUM in that database.
You might also need to commit or roll back old prepared transactions, or drop
stale replication slots.

2.5.3 LSN Notation

The notation for LSN has been standardized, and the lower 32 bits are now always zero-padded when displayed. This change breaks compatibility with the notation used in some views and function return values. [2633dae]

Example 2 LSN Notation
postgres=# SELECT * FROM
pg_create_logical_replication_slot('slot1', 'pgoutput') ;
 slot_name |    lsn
-----------+------------
 slot1     | 0/019D2F30
(1 row)

2.5.4 LDAP protocol

The connection to the LDAP server now uses LDAP version 3. Previously, version 2 was used. [5f19d13]

2.5.5 Object Name

The CR and LF characters can no longer be used in the names of databases, roles, and tablespaces. [b380a56]

2.5.6 GUC

PostgreSQL 19 introduces the following incompatibilities related to parameter (GUC) settings.

PDF page 12

Parameter standard_conforming_strings

This parameter can no longer be set to “off”. [4576208]

Example 3 Changes to the standard_conforming_strings parameter
postgres=> SET standard_conforming_strings = off ;
ERROR:  non-standard string literals are not supported
Resetting Parameters

Specifying a parameter that does not exist in the ALTER DATABASE RESET or ALTER USER RESET statements will result in an error. In previous versions, this did not result in an error. [9a71989]

Example 4 Execute ALTER DATABASE/USER RESET statement
postgres=# ALTER DATABASE postgres RESET foo ;
ERROR:  unrecognized configuration parameter "foo"
postgres=# ALTER USER postgres RESET foo ;
ERROR:  unrecognized configuration parameter "foo"

2.5.7 MD5 Authentication

When the md5_password_warnings parameter is set to “on” (the default value), the warning is now output during authentication using an MD5 password. [bc60ee8]

Example 5 Warning when using MD5 authentication
$ psql -d postgres -U user1 -h dbsvr1
Password for user user1: <<PASSWORD>>
WARNING:  authenticated with an MD5-encrypted password
DETAIL:  MD5 password support is deprecated and will be removed in a future
release of PostgreSQL.
psql (19beta1)
Type "help" for help.

2.5.8 Catalog pg_statistic_ext_data

The JSON output format for the stxdndistinct and the stxddependencies columns in the pg_statistic_ext_data catalog has been changed. [1f927cc, e76defb]

PDF page 13

Example 6 PostgreSQL 18 Output Formats
postgres=# SELECT jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct,
jsonb_pretty(d.stxddependencies::text::jsonb) AS stxddependencies
FROM pg_statistic_ext s LEFT JOIN pg_statistic_ext_data d ON (d.stxoid = s.oid)
WHERE s.stxname = 'data1_col1_col2' ;
 stxdndistinct  |    stxddependencies
----------------+------------------------
 {             +| {                     +
     "1, 2": 10+|     "1 => 2": 1.000000+
 }              | }
(1 row)
Example 7 PostgreSQL 19 Output Formats
postgres=# SELECT jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct,
jsonb_pretty(d.stxddependencies::text::jsonb) AS stxddependencies
FROM pg_statistic_ext s LEFT JOIN pg_statistic_ext_data d ON (d.stxoid = s.oid)
WHERE s.stxname = 'data1_col1_col2' ;
      stxdndistinct       |      stxddependencies
--------------------------+-----------------------------
 [                       +| [                          +
     {                   +|     {                      +
         "ndistinct": 10,+|         "degree": 1.000000,+
         "attributes": [ +|         "attributes": [    +
             1,          +|             1              +
             2           +|         ],                 +
         ]               +|         "dependency": 2    +
     }                   +|     }                      +
 ]                        | ]
(1 row)

2.5.9 ON CONFLICT Clause

In PostgreSQL 19, specifying an operator class in the INSERT ON CONFLICT statement causes an error. The following INSERT statement worked successfully in PostgreSQL 18. [630a937]

PDF page 14

Example 8 Specifying the ON CONFLICT clause and operator classes
postgres=> INSERT INTO data1 VALUES (1, 'data1') ON CONFLICT
(col1 INT4_OPS (fillfactor=10)) DO NOTHING ;
ERROR:  operator class options are not allowed in ON CONFLICT clause
LINE 1: INSERT INTO data1 VALUES (1, 'data1') ON CONFLICT (col1 INT4_OPS (fi...
                                                          ^

2.5.10 RADIUS Authentication

RADIUS/UDP authentication is no longer supported. [a1643d4]

2.5.11 Command psql

The output of the \d+ metacommand for partition tables and inheritance tables has been changed. [7bff9f1, 1a11405]

Example 9 PostgreSQL 18 Output Formats
postgres=> CREATE TABLE part1(col1 INT, col2 INT) PARTITION BY LIST (col1) ;
CREATE TABLE
postgres=> CREATE TABLE part1v1 PARTITION OF part1 FOR VALUES IN (100) ;
CREATE TABLE
postgres=> \d+ part1
                                    Partitioned table "public.part1"
 Column |  Type   | Collation | Nullable | Default | Storage | Compress  …
--------+---------+-----------+----------+---------+---------+---------  …
col1   | integer |           |          |         | plain   |          …
col2   | integer |           |          |         | plain   |          …
Partition key: LIST (col1)
Partitions: part1v1 FOR VALUES IN (100)

PDF page 15

Example 10 PostgreSQL 19 Output Formats
postgres=> CREATE TABLE part1(col1 INT, col2 INT) PARTITION BY LIST (col1) ;
CREATE TABLE
postgres=> CREATE TABLE part1v1 PARTITION OF part1 FOR VALUES IN (100) ;
CREATE TABLE
postgres=> \d+ part1
                                    Partitioned table "public.part1"
 Column |  Type   | Collation | Nullable | Default | Storage | Compress  …
--------+---------+-----------+----------+---------+---------+---------  …
col1   | integer |           |          |         | plain   |          …
col2   | integer |           |          |         | plain   |          …
Partition key: LIST (col1)
Partitions:
    part1v1 FOR VALUES IN (100)

2.5.12 MULE_INTERNAL Encoding

The MULT_INTERNAL character encoding has been removed. With this change, the pg_upgrade command now checks for unsupported encodings. [77645d4]

2.5.13 COPY FROM Statement

The COPY FROM statement that specifies a WHERE clause using a system column can no longer be executed. [21c69dc]

Example 11 WHERE clauses using system columns
postgres=# COPY data1 FROM stdin WHERE xmin = 100 ;
ERROR:  system columns are not supported in COPY FROM WHERE conditions
DETAIL:  Column "xmin" is a system column.

PDF page 16

2.5.14 JSON_ARRAY Function

In PostgreSQL 18 and earlier, the JSON_ARRAY function returned NULL when an empty result set was specified. In PostgreSQL 19, the JSON_ARRAY function returns an empty JSON array, in accordance with the SQL/JSON standard. [8d829f5]

Example 12 PostgreSQL 18 Output
postgres=> \pset null
Null display is "NULL".
postgres=> SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i)
WHERE i > 4) ;
 json_array
------------
 NULL
(1 row)
Example 13 PostgreSQL 19 Output
postgres=> SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i)
WHERE i > 4) ;
 json_array
------------
 []
(1 row)

PDF page 17