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 {#pg13-2-1-improve-analytic-query-performance} ```text The following features have been added that can be applied to large scale environments: ``` #### Enhancement of Parallel operations {#pg13-p7-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 {#pg13-p7-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 {#pg13-p7-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 {#pg13-2-2-improve-reliability} PostgreSQL 13 implements the following enhancements to improve reliability. #### Improve backup reliability {#pg13-p7-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 {#pg13-p7-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 {#pg13-2-3-improved-maintainability} The following features that can improve operability have been added. #### Disk-based Hash Aggregation {#pg13-p8-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 {#pg13-p8-logical-replication-of-partitioned-tables} Partitioned tables can now be used in logical replication environments. #### Enhanced monitoring features {#pg13-p8-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 {#pg13-p8-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 {#pg13-p8-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 {#pg13-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 {#pg13-p8-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 {#pg13-p8-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 {#pg13-p9-enhancement-of-publication} It is now ready to add non-table objects to PUBLICATION.PDF page 10
### 2.5 Incompatibility {#pg13-2-5-incompatibility} In PostgreSQL 13, the following specifications have been changed from PostgreSQL 12. #### 2.5.1 configure {#pg13-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 {#pg13-2-5-2-createuser} The --adduser and --no-adduser options have been removed from the createuser command. #### 2.5.3 CSV log format {#pg13-2-5-3-csv-log-format} Backend type is added at the end of CSV format log (log_destination = 'csvlog'). **Example 1.** CSV log \(part\) ```text 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 {#pg13-2-5-4-extension} Remove support for upgrading "unpackaged" extensions.PDF page 11
#### 2.5.5 opaque {#pg13-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 {#pg13-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 {#pg13-2-5-7-pg-regress} The --load-language option has been removed from the pg_regress command. #### 2.5.8 psql default prompt {#pg13-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. **Table 4.** Changing default settings | Variable name | PostgreSQL 12 | PostgreSQL 13 | Note | | --- | --- | --- | --- | | PROMPT1 | "%/%R%# " | "%/%R%x%# " | | | PROMPT2 | "%/%R%# " | "%/%R%x%# " | | | PROMPT3 | ">> " | ">> " | no change | **Example 2.** Default prompt ```text 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 {#pg13-2-5-9-to-date-to-timestamp} The output contents of the format string TM change according to the locale. Previously, it was ignored. **Example 3.** Execute the to\_date function ```text 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 {#pg13-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 {#pg13-2-5-11-partition-key} Disallow partition key expressions that return pseudo-types. This specification has been backported to PostgreSQL 12.2 and later. **Example 4.** Partition key with pseudo\-types ```text 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 {#pg13-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 eventsPDF page 13
#### 2.5.13 SIMILAR TO ESCAPE {#pg13-2-5-13-similar-to-escape} SIMILAR TO… ESCAPE NULL clause returns NULL. In previous versions, the ESCAPE NULL clause was ignored. **Example 5.** PostgreSQL 12 behavior ```text postgres=> SELECT 'ABC' SIMILAR TO 'ABC' ESCAPE NULL ; ?column? ---------- t (1 row) ``` **Example 6.** PostgreSQL 13 behavior ```text postgres=> \pset null null Null display is "null". postgres=> SELECT 'ABC' SIMILAR TO 'ABC' ESCAPE NULL ; ?column? ---------- null (1 row) ```PDF page 14