2 New Features Summary

The source document’s overview of the release, benefits, and incompatibilities.

GASource pages: PDF 8-18Original PDF on GitHub

PDF page 8

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

Creating Virtual Columns

Allows the creation of virtual columns without data storage as columns of a table.

VACUUM / ANALYZE statements for partitioned tables

The VACUUM and ANALYZE statements can now specify an ONLY clause that does not recursively process partitions.

2.2 Improved reliability

PostgreSQL 18 implements the following enhancements to improve reliability.

Tar format support for pg_verifybackup command

The pg_verifybackup command can now be executed for tar format backups

2.3 Improved maintainability

The following features that can improve operability have been added.

Statistics Migration

Optimizer statistics can now be included in dump files for the pg_dump and pg_dumpall commands. Functions have also been added to set and remove optimizer statistics for tables and columns.

OAUTH SASL

Supports authentication by OAUTHBearer/SASL.

PDF page 9

EXPLAIN statement

Many options have been added to the EXPLAIN statement.

2.4 Improvements in Programming

The following functions have been added to SQL statements.

Access to Before Update Data

The RETURNING clause of the update DML can now specify an identifier that indicates the data before (OLD) and after (NEW) the update.

UUID v7 function

A function is provided that returns a UUID v7 value with a fixed leading portion.

2.5 Preparing for future new features

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

Asynchronous I/O Infrastructure-

The infrastructure for using asynchronous I/O has been implemented. The parameter io_method specifies the asynchronous I/O method to be used.

NUMA support

Functions and views to use the basic NUMA environment have been added.

PDF page 10

2.6 Incompatibility

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

2.6.1 Support Information

PostgreSQL 18 changes the supported versions for the following platforms and tools The following platforms and tools are no longer supported. [edadeb0, 972c2cd, 6c66b74, 45363fc]

PA-RISC processor
LLVM 13 and below
OpenSSL 1.1.0 and below
Python 3.2 The changes in component support versions required to build PostgreSQL 18 are as follows. [a70e01d, 6c66b74]
OpenSSL 1.1.1 or higher required
Python 3.6.8 or higher required The following components are now supported in PostgreSQL 18.
Tcl 9 support added [32a2aa7]

2.6.2 MD5 Password

MD5 passwords are now deprecated. Generating an MD5 password will output a warning by default. This warning can be suppressed by setting the parameter md5_password_warnings to ‘off’. [db6a4a98]

PDF page 11

Example 1 MD5 Password Warning

postgres=# SET password_encryption = md5 ;
SET
postgres=# SHOW md5_password_warnings ;
 md5_password_warnings
-----------------------
 on
(1 row)
postgres=# CREATE USER user1 PASSWORD 'user1' ;
WARNING:  setting an MD5-encrypted password
DETAIL:  MD5 password support is deprecated and will be removed in a future
release of PostgreSQL.
HINT:  Refer to the PostgreSQL documentation for details about migrating to
another password type.
CREATE ROLE

2.6.3 Configure command

The configure command has been changed as follows.

Option –disable-spin-locks

This option has been removed. [e256266]

Option –disable-atomics

This option has been removed. [8138526]

Command flex

  Version checks have been removed. [0869ea4]

2.6.4 Initdb command

The data checksum feature is enabled by default. To disable checksums, specify the –no-datachecksums option. [04bec89]

PDF page 12

Example 2 Checksum enabled by default

$ initdb data
The files belonging to this database system will be owned by user "postgres".
…
The default text search configuration will be set to "simple".
Data page checksums are enabled.
…

2.6.5 COPY statement

The following changes have been implemented for the COPY statement.

Prohibition of the COPY FREEZE statement for external tables

The COPY FREEZE statement for external tables is now explicitly prohibited. [401a695]

Example 3 Prohibition of COPY FREEZE statement for external tables

postgres=# CREATE FOREIGN TABLE data1(col1 INT, col2 VARCHAR(10))
SERVER remsvr1 ;
CREATE FOREIGN TABLE
postgres=# COPY data1 FROM stdin FREEZE ;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 1,data1
>> \.
ERROR:  cannot perform COPY FREEZE because the table was not created or
truncated in the current subtransaction

CSV mode specification change

When executing the COPY statement in CSV mode and reading data from a source other than STDIN, ‘.’ is no longer considered data completion. [7702337]

PDF page 13

Example 4 Contents of CSV file data1.csv

Data1
Data2
\.
Data3

Example 5 PostgreSQL 17 behavior

postgres=> COPY data1 FROM '/home/postgres/data1.csv' CSV ;
COPY 2
postgres=> SELECT * FROM copy1 ;
  col1
-------
 Data1
 Data2
(2 rows)

Example 6 PostgreSQL 17 behavior

postgres=> COPY data1 FROM '/home/postgres/data1.csv' CSV ;
COPY 4
postgres=> SELECT * FROM data1 ;
  col1
-------
 Data1
 Data2
 \.
 Data3
(4 rows)

2.6.6 CREATE OPERATOR CLASS statement

The RECHECK clause in the CREATE OPERATOR CLASS and ALTER OPERATOR FAMILY statements is no longer accepted. The RECHECK clause in these statements was valid prior to PostgreSQL 8.4 and did not cause an error if specified until PostgreSQL 17. [7da1bdc]

PDF page 14

2.6.7 CREATE SUBSCRIPTION statement

The default value of the ‘streaming’ option when creating SUBSCRIPTION has been changed from ‘off’ to ‘parallel’. [1bf1140]

Example 7 Change default value when creating SUBSCRIPTION

postgres=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=dbsvr1 port=5432
dbname=postgres' PUBLICATION pub1 ;
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION
postgres=# SELECT subname, substream FROM pg_subscription ;
 subname | substream
---------+-----------
 sub1    | p
(1 row)

2.6.8 CREATE TABLE statement

Partition tables can no longer be created as UNLOGGED tables. Partitions can be created as UNLOGGED tables. [e2bab2d]

Example 8 Error creating UNLOGGED partition table

postgres=> CREATE UNLOGGED TABLE part1(col1 INT, col2 VARCHAR(10))
PARTITION BY RANGE(col1) ;
ERROR:  partitioned tables cannot be unlogged
postgres=> CREATE TABLE part1(col1 INT, col2 VARCHAR(10)) PARTITION BY
RANGE(col1) ;
CREATE TABLE
postgres=> CREATE UNLOGGED TABLE part1v1 PARTITION OF part1 FOR VALUES
FROM (0) TO (10) PARTITION BY LIST(col2) ;
ERROR:  partitioned tables cannot be unlogged

2.6.9 EXPLAIN statement

The BUFFERS clause is now enabled by default. [c2a4078]

PDF page 15

Example 9 PostgreSQL 17 behavior

postgres=> EXPLAIN ANALYZE SELECT * FROM data1 WHERE col1=1000 ;
                           QUERY PLAN
----------------------------------------------------------------------------…
 Index Scan using data1_pkey on data1  (cost=0.42..8.44 rows=1 width=10) (act…
   Index Cond: (col1 = 1000)
 Planning Time: 0.062 ms
 Execution Time: 0.018 ms
(4 rows)

Example 10 PostgreSQL 18 behavior

postgres=> EXPLAIN ANALYZE SELECT * FROM data1 WHERE col1=1000 ;
                            QUERY PLAN
----------------------------------------------------------------------------…
 Index Scan using data1_pkey on data1  (cost=0.42..8.44 rows=1 width=10) (act…
   Index Cond: (col1 = 1000)
   Buffers: shared hit=4
 Planning Time: 0.065 ms
 Execution Time: 0.023 ms
(5 rows)

2.6.10 GRANT statement

The RULE privilege on objects has been removed since PostgreSQL 8.2, but the GRANT statement did not raise an error. The RULE privilege has been completely removed from this version. [fefa76f]

Example 11 Remove RULE privileges

postgres=> CREATE TABLE data1(col1 INT PRIMARY KEY, col2 VARCHAR(10)) ;
CREATE TABLE
postgres=> GRANT RULE ON data1 TO PUBLIC ;
ERROR:  unrecognized privilege type "rule"

PDF page 16

2.6.11 Psql

The output format of the \conninfo metacommand has been changed. [bba2fbc]

Example 12 PostgreSQL 17 psql command output

postgres=> \conninfo
You are connected to database "postgres" as user "demo" on host "dbsvr1" (address
"192.168.1.100") at port "5432".

Example 13 PostgreSQL 18 psql command output (without SSL)

postgres=# \conninfo
     Connection Information
      Parameter       |  Value
----------------------+----------
 Database             | postgres
 Client User          | postgres
 Socket Directory     | /tmp
 Server Port          | 5432
 Options              |
 Protocol Version     | 3.0
 Password Used        | false
 GSSAPI Authenticated | false
 Backend PID          | 27757
 SSL Connection       | false
 Superuser            | on
 Hot Standby          | off
(12 rows)

2.6.12 Pg_backend_memory_contexts view

The starting number of the level column in the pg_backend_memory_contexts view has been changed from zero to one. The same change has been implemented for the output of the pg_log_backend_memory_contexts function. [d9e0386]

PDF page 17

Example 14 PostgreSQL 17 pg_backend_memory_contexts view

postgres=# SELECT level,name FROM pg_backend_memory_contexts order by 1
LIMIT 2 ;
 level |         name
-------+-----------------------
     0 | TopMemoryContext
     1 | TopTransactionContext
(2 rows)

Example 15 PostgreSQL 18 pg_backend_memory_contexts view

postgres=# SELECT level,name FROM pg_backend_memory_contexts order by 1
LIMIT 2 ;
 level |                      name
-------+------------------------------------------------
     1 | TopMemoryContext
     2 | Map from relid to OID of cached composite type
(2 rows)

PDF page 18