PostgreSQL 14 contains many new features and enhancements, including:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

Stored procedures can now return data via OUT parameters.

The SQL-standard SEARCH and CYCLE options for common table expressions have been implemented.

Subscripting can now be applied to any data type for which it is a useful notation, not only arrays. In this release, the jsonb and hstore types have gained subscripting operators.

Range types have been extended by adding multiranges, allowing representation of noncontiguous data ranges.

Numerous performance improvements have been made for parallel queries, heavily-concurrent workloads, partitioned tables, logical replication, and vacuuming.

B-tree index updates are managed more efficiently, reducing index bloat.

VACUUM automatically becomes more aggressive, and skips inessential cleanup, if the database starts to approach a transaction ID wraparound condition.

Extended statistics can now be collected on expressions, allowing better planning results for complex queries.

libpq now has the ability to pipeline multiple queries, which can boost throughput over high-latency connections.

The above items and other new features of PostgreSQL 14 are explained in more detail in the sections below.

A dump/restore using pg_dumpall or use of pg_upgrade or logical replication is required for those wishing to migrate data from any previous release. See Section 19.6 for general information on migrating to new major releases.

Version 14 contains a number of changes that may affect compatibility with previous releases. Observe the following incompatibilities:

User-defined objects that reference certain built-in array functions along with their argument types must be recreated (Tom Lane)

Specifically, array_append(), array_prepend(), array_cat(), array_position(), array_positions(), array_remove(), array_replace(), and width_bucket() used to take anyarray arguments but now take anycompatiblearray. Therefore, user-defined objects like aggregates and operators that reference those array function signatures must be dropped before upgrading, and recreated once the upgrade completes.

Remove deprecated containment operators @ and ~ for built-in geometric data types and contrib modules cube, hstore, intarray, and seg (Justin Pryzby)

The more consistently named <@ and @> have been recommended for many years.

Fix to_tsquery() and websearch_to_tsquery() to properly parse query text containing discarded tokens (Alexander Korotkov)

Certain discarded tokens, like underscore, caused the output of these functions to produce incorrect tsquery output, e.g., both websearch_to_tsquery('"pg_class pg"') and to_tsquery('pg_class <-> pg') used to output ( 'pg' & 'class' ) <-> 'pg', but now both output 'pg' <-> 'class' <-> 'pg'.

Fix websearch_to_tsquery() to properly parse multiple adjacent discarded tokens in quotes (Alexander Korotkov)

Previously, quoted text that contained multiple adjacent discarded tokens was treated as multiple tokens, causing incorrect tsquery output, e.g., websearch_to_tsquery('"aaa: bbb"') used to output 'aaa' <2> 'bbb', but now outputs 'aaa' <-> 'bbb'.

Change EXTRACT() to return type numeric instead of float8 (Peter Eisentraut)

This avoids loss-of-precision issues in some usages. The old behavior can still be obtained by using the old underlying function date_part().

Also, EXTRACT(date) now throws an error for units that are not part of the date data type.

Change var_samp() and stddev_samp() with numeric parameters to return NULL when the input is a single NaN value (Tom Lane)

Previously NaN was returned.

Return false for has_column_privilege() checks on non-existent or dropped columns when using attribute numbers (Joe Conway)

Previously such attribute numbers returned an invalid-column error.

Fix handling of infinite window function ranges (Tom Lane)

Previously window frame clauses like 'inf' PRECEDING AND 'inf' FOLLOWING returned incorrect results.

Remove factorial operators ! and !!, as well as function numeric_fac() (Mark Dilger)

The factorial() function is still supported.

Disallow factorial() of negative numbers (Peter Eisentraut)

Previously such cases returned 1.

Remove support for postfix (right-unary) operators (Mark Dilger)

pg_dump and pg_upgrade will warn if postfix operators are being dumped.

Allow \D and \W shorthands to match newlines in regular expression newline-sensitive mode (Tom Lane)

Previously they did not match newlines in this mode, but that disagrees with the behavior of other common regular expression engines. [^[:digit:]] or [^[:word:]] can be used to get the old behavior.

Disregard constraints when matching regular expression back-references (Tom Lane)

For example, in (^\d+).*\1, the ^ constraint should be applied at the start of the string, but not when matching \1.

Disallow \w as a range start or end in regular expression character classes (Tom Lane)

This previously was allowed but produced unexpected results.

Require custom server parameter names to use only characters that are valid in unquoted SQL identifiers (Tom Lane)

Change the default of the password_encryption server parameter to scram-sha-256 (Peter Eisentraut)

Previously it was md5. All new passwords will be stored as SHA256 unless this server setting is changed or the password is specified in MD5 format. Also, the legacy (and undocumented) Boolean-like values which were previously synonyms for md5 are no longer accepted.

Remove server parameter vacuum_cleanup_index_scale_factor (Peter Geoghegan)

This setting was ignored starting in PostgreSQL version 13.3.

Remove server parameter operator_precedence_warning (Tom Lane)

This setting was used for warning applications about PostgreSQL 9.5 changes.

Overhaul the specification of clientcert in pg_hba.conf (Kyotaro Horiguchi)

Values 1/0/no-verify are no longer supported; only the strings verify-ca and verify-full can be used. Also, disallow verify-ca if cert authentication is enabled since cert requires verify-full checking.

Remove support for SSL compression (Daniel Gustafsson, Michael Paquier)

This was already disabled by default in previous PostgreSQL releases, and most modern OpenSSL and TLS versions no longer support it.

Remove server and libpq support for the version 2 wire protocol (Heikki Linnakangas)

This was last used as the default in PostgreSQL 7.3 (released in 2002).

Disallow single-quoting of the language name in the CREATE/DROP LANGUAGE command (Peter Eisentraut)

Remove the composite types that were formerly created for sequences and toast tables (Tom Lane)

Process doubled quote marks in ecpg SQL command strings correctly (Tom Lane)

Previously 'abc''def' was passed to the server as 'abc'def', and "abc""def" was passed as "abc"def", causing syntax errors.

Prevent the containment operators (<@ and @>) for intarray from using GiST indexes (Tom Lane)

Previously a full GiST index scan was required, so just avoid that and scan the heap, which is faster. Indexes created for this purpose should be removed.

Remove contrib program pg_standby (Justin Pryzby)

Prevent tablefunc's function normal_rand() from accepting negative values (Ashutosh Bapat)

Negative values produced undesirable results.

Changes

Server

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

Add predefined roles pg_read_all_data and pg_write_all_data (Stephen Frost)

These non-login roles can be used to give read or write permission to all tables, views, and sequences.

Add predefined role pg_database_owner that contains only the current database's owner (Noah Misch)

This is especially useful in template databases.

Remove temporary files after backend crashes (Euler Taveira)

Previously, such files were retained for debugging purposes. If necessary, deletion can be disabled with the new server parameter remove_temp_files_after_crash.

Allow long-running queries to be canceled if the client disconnects (Sergey Cherkashin, Thomas Munro)

The server parameter client_connection_check_interval allows control over whether loss of connection is checked for intra-query. (This is supported on Linux and a few other operating systems.)

Add an optional timeout parameter to pg_terminate_backend() (Magnus Hagander)

Allow wide tuples to be always added to almost-empty heap pages (John Naylor, Floris van Nee)

Previously tuples whose insertion would have exceeded the page's fill factor were instead added to new pages.

Add Server Name Indication (SNI) in SSL connection packets (Peter Eisentraut)

This can be disabled by turning off client connection option sslsni.

Vacuuming

利用PostgreSQL新分区表attach功能，完成旧分区表替换成新分区表

PostgreSQL 13 Beta1版本已于2020-05-21发行，目前最新版为Beta2，尽管 PostgreSQL 13 版本没有像10、11、12版本新增重量级的功能，但13版本依然增加了不少新特性和功能提升，值得细细研究。

PostgreSQL 13 典型变化如下:

逻辑复制支持分区表
Btree索引优化(引入Deduplication技术)
增量排序(Incremental Sorting)
并行VACUUM索引
并行Reindexdb
手册新增术语(Glossary)附录

本文从新特性、性能提升、数据库管理、其它亮点四方面详细介绍 PostgreSQL 13的变化。

尽管现阶段 PostgreSQL 12 才出 Beta3 版本，但 12 版本的新特性和正式版不会有太大出入，最近抽时间对 12 版本的新特性进行了探索，整体上 12 版本的变化不小。

12 版本的典型新特性如下:

支持 SQL/JSON path
支持 Generated Columns
CTE 支持 Inlined With Queries
新增 Pluggable Table Storage Interface
分区表性能大辐提升
在线重建索引(Reindex Concurrently)

2018-10-18 PostgreSQL官网 宣布 PostgreSQL 11 正式版发行，PostgreSQL 11 重点对性能进行了提升和功能完善，特别是对大数据库和高计算负载的情况下进行了增强，主要包括以下:

对分区表进行了大幅的改进和增强。
增加了对存储过程的支持，存储过程支持事务。
增强了并行查询能力和并行数据定义能力。
增加了对 just-in-time (JIT) 编译的支持，加速SQL中的表达式执行效率。

最近对PostgreSQL以上新特性和其它功能完善做了演示，希望对PostgreSQL从业者有帮助，详见以下。
分区表的改进