2 New Features Summary

PDF page 7

More than 200 new features have been added to PostgreSQL 13. Here are some major new features and benefits.

2.1 Improve analytic query performance

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

Enhancement of Parallel operations

If more than one of the indexes has been created in the table, now VACUUM processing is performed in parallel.

Deduplication of B-Tree index

Deduplication is now executed by default for B-Tree indexes. Storage capacity and I/O can be reduced.

Reduce WAL output

The statement that suppresses WAL output when the wal_level parameter is set to ‘minimal’ has been increased.

2.2 Improve reliability

PostgreSQL 13 implements the following enhancements to improve reliability.

Improve backup reliability

Backup consistency can now be checked. Base backups, such as the pg_basebackup command, take the size and checksum of each file and save it to the backup destination. The consistency of the obtained backup can be checked with the pg_verifybackup command.

Dynamic configuration changes for streaming replication

Various parameters used on the standby instance of streaming replication can now be changed dynamically. This new feature eliminates the need to restart the standby instance if the primary instance fails.

PDF page 8

2.3 Improved maintainability

The following features that can improve operability have been added.

Disk-based Hash Aggregation

Operations that manipulate hash tables now are performed on storage when the memory usage limit is exceeded. Previously, there was no upper limit on memory usage, which sometimes led to OOM Killer.

Logical replication of partitioned tables

Partitioned tables can now be used in logical replication environments.

Enhanced monitoring features

Catalogs that can check the execution status of the ANALYZE statement or pg_basebackup command in real-time have been added. Also, the cache hit ratio and I/O status can be checked.

Execution plan creation and WAL output tracking

The amount of WAL output can now be tracked when creating an execution plan or executing an SQL statement. It can be checked by the pg_stat_statements module and EXPLAIN statement.

More wait events

Several wait events have been added that can be monitored. Wait events can be checked in the pg_stat_activity catalog.

2.4 Preparing for future new features

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

Supports 64-bit transaction ID

The xid8 data type that indicates a 64-bit transaction ID and various functions that handle this data type has been added.

Lock contention

Page locks and extended locks can now conflict between parallel worker processes. With this implementation, it is now possible to implement parallelization such as COPY, INSERT, VACUUM, etc.

PDF page 9

Enhancement of PUBLICATION

It is now ready to add non-table objects to PUBLICATION.

PDF page 10

2.5 Incompatibility

In PostgreSQL 13, the following specifications have been changed from PostgreSQL 12.

2.5.1 configure

The –disable-float4-byval option has been removed from the ‘configure’ command. Also, the pkgconfig command is now used to search for the libxml2 library.

2.5.2 createuser

The –adduser and –no-adduser options have been removed from the createuser command.

2.5.3 CSV log format

Backend type is added at the end of CSV format log (log_destination = ‘csvlog’).

2020-09-24 23:27:12.495 JST,,,30744,,5ec6194a.7818,1,,2020-05-21
15:01:46 JST,,0,LOG,00000,"database system was shut down at 2020-
05-21 15:01:29 JST",,,,,,,,,"","startup"
2020-09-24 23:27:12.495 JST,,,30734,,5ec61949.780e,6,,2020-05-21
15:01:45 JST,,0,LOG,00000,"database system is ready to accept
connections",,,,,,,,,"","postmaster"
2020-09-24
23:27:12.495
JST,"postgres","postgres",30753,"[local]",5ec61953.7821,1,"SELECT"
,2020-05-21 15:01:55 JST,3/2,0,ERROR,42P01,"relation ""notexists""
does not exist",,,,,,"SELECT * FROM notexists;",15,,"psql","client
backend"

2.5.4 Extension

Remove support for upgrading “unpackaged” extensions.

PDF page 11

2.5.5 opaque

The opaque pseudo-data type has been removed. This data type was used for compatibility from PostgreSQL 7.3 and earlier.

2.5.6 Require version of OpenSSL

OpenSSL 1.0.0 and below are no longer supported. OpenSSL 1.0.1 or higher is required. With this fix, the minimum value of the parameter ssl_min_protocol_version has been changed from TLSv1 to TLSv1.2.

2.5.7 pg_regress

The –load-language option has been removed from the pg_regress command.

2.5.8 psql default prompt

The default prompt settings PROMPT1 and PROMPT2 of the psql command now include %x to indicate the status of the transaction.

postgres=> BEGIN ;
BEGIN
postgres=*> -- In the active transaction
postgres=*> ERROR ;
ERROR: syntax error at or near "ERROR"
LINE 1: ERROR
        ^
postgres=!> -- In the failed transaction
postgres=!> ROLLBACK ;
ROLLBACK

PDF page 12

2.5.9 to_date/to_timestamp

The output contents of the format string TM change according to the locale. Previously, it was ignored.

postgres=> SET lc_time='ru_RU' ;
SET
postgres=> SELECT to_date('01 фев 2020', 'DD TMMON YYYY') ;
  to_date
------------
 2020-02-01
(1 row)

2.5.10 Promotion during recovery

Previously, if a promotion was made while the recovery was paused, the paused state would continue. In PostgreSQL 13, if a promotion is performed while recovery is paused, the promotion takes precedence.

2.5.11 Partition key

Disallow partition key expressions that return pseudo-types. This specification has been backported to PostgreSQL 12.2 and later.

postgres=> CREATE TABLE part1 (c1 INT, c2 INT)
PARTITION BY RANGE(((c1, c2))) ;
ERROR:  partition key column 1 has pseudo-type record

2.5.12 Wait Event

Many wait events have been renamed. The event_name column in the pg_stat_activity catalog and the output string in the locktype column in the pg_locks catalog has been changed1.

1 See 3.1.8 Wait events

PDF page 13

2.5.13 SIMILAR TO ESCAPE

SIMILAR TO… ESCAPE NULL clause returns NULL. In previous versions, the ESCAPE NULL clause was ignored.

postgres=> SELECT 'ABC' SIMILAR TO 'ABC' ESCAPE NULL ;
 ?column?
----------
 t
(1 row)

postgres=> \pset null null
Null display is "null".
postgres=> SELECT 'ABC' SIMILAR TO 'ABC' ESCAPE NULL ;
?column?
----------
 null
(1 row)

PDF page 14