PostgreSQL 14

PostgreSQL 14 is the next major PostgreSQL release, currently under pre-release review.

Development work for PostgreSQL 14 began in June 2020. PostgreSQL 14 is likely to be released in late 2021.

• Release notes (work-in-progress; latest build)
• Documentation: PostgreSQL 14
• GIT branch: REL_14_STABLE
• PostgreSQL 14 Open Items

Release schedule and pre-release test versions

• beta2 (REL_14_BETA2) - commit bafad2c5 (2021-06-22); announcement (2021-06-25): PostgreSQL 14 Beta 2 Released!
• beta1 (REL_14_BETA1) - commit e4f9737f (2021-05-17); announcement (2021-05-20): PostgreSQL 14 Beta 1 Released!

Initial commit marking the start of PostgreSQL 14 development is d10b19e2 (2020-06-07).

CommitFests

Following CommitFests were for PostgreSQL 14 code submissions:

• CommitFest 32 (2021-03-01 - 2021-04-07; closed)
• CommitFest 31 (2021-01-01 - 2021-01-31; closed)
• CommitFest 30 (2020-11-01 - 2020-11-30; closed)
• CommitFest 29 (2020-09-01 - 2020-09-30; closed)
• CommitFest 28 (2020-07-01 - 2020-07-31; closed)

Backend changes

• enhanced COPY performance through improved buffering (commit 0a0727cc)
• improvements in snapshot scalability to handle larger number of connections (commits 5788e258, 1f51c17c, 941697c3, 73487a60, 623a9ba7, 07f32fcd, and dc7420c2)
• stored procedures now support OUT parameters (commit 2453ea14)
• support for abstract UNIX domain sockets (commit c9f0624b)
• improved client error messages for immediate-stop situations (commit 7e784d1d)
• key management system (initial commit 978f869b) withdrawn (commit 3187ef7c)
• new WAL segments now filled using vectored I/O (commit ce6a71fa)
• TID range scan support added (commit bb437f99)
• support for protocol version 2 removed (commit 3174d69f)
• configurable LZ4 TOAST compression (commit bbe0a81d)
• asynchrous execution support for foreign nodes added (commit 27e1f145)
• support for SQL-standard function bodies (commit e717a9a1)
• query ids previously generated by pg_stat_statements now generated in core (commit 5fd9dfa5)

SPI changes

• following functions added (commits ee895a65 and d5a83d79):
  • SPI_execute_extended()
  • SPI_cursor_parse_open()

Storage parameter changes

• parallel_insert_enabled added (commit c8f78b61)

Vacuum changes

• a vacuum following COPY FREEZE execution no longer needs to rewrite the entire table (commit 7db0cd21)
• per-index statistics information displayed in autovacuum verbose logs (commit 5aed6a1f)
• tuple compacting improvements (commit 19c60ad6)
• unnecessary index vacuuming skipped (commit 5100010e)
• index operations performed with the CONCURRENTLY option will be ignored (commit d9d07622)
• wraparound failsafe functionality added (commit 1e55e7d1)

Source configuration

• option --with-ssl added (commit fe61df7f)

Data type changes

General

• support for subscripting of arbitrary data types added (commit c7aba7c1)
• support for multirange types added (commit 6df7a969)

jsonb

• subscripting support added (commit 676887a3)

pg_lsn

• +(pg_lsn,numeric) and -(pg_lsn,numeric) operators added (commit 9bae7e4c)

Operator changes

Removed:

• factorial operators ! and !! (commit 76f412ab)
• deprecated containment operators @ and ~ for built-in geometry data types (commit 2f70fdb0)
• support for postfix (right-unary) operators removed (commit 1ed6b895)

Indexing changes

General

• support for collation versioning added (initial commit 257836a7)
• concurrent index creation can skip waits on other concurrent (re)index processes (commit c98763bf)
• concurrent reindexing can skip waits on other concurrent (re)index processes (commit f9900df5)

btree indexes

• "bottom-up deletion" for btree indexes (commit d168b666)

BRIN indexes

• support for BRIN bloom indexes added (commit 77b88cd1)
• support for BRIN minmax-multi indexes added (commit 77b88cd1)

SP-GiST indexes

• support for INCLUDE columns added (commit 09c1c6ab)

Partitioning changes

• partitions can be detatched concurrently (commit 71f4c8c6)

SQL changes

Modified:

• ALTER INDEX
  • ALTER INDEX ... ALTER COLLATION ... syntax added (commit 257836a7)
• ALTER ROLE
  • CURRENT_ROLE now also accepted in place of a user name (commit 45b98057)
• ALTER SUBSCRIPTION
  • ALTER SUBSCRIPTION ... [ ADD | DROP ] PUBLICATION syntax added (commit 82ed7748)
  • parameter binary added (commit 9de77b54)
• ALTER TABLE
  • ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY syntax added (commit 71f4c8c6)
  • ALTER TABLE ... ALTER [ COLUMN ] ... SET COMPRESSION syntax added (commit bbe0a81d)
• ALTER TYPE
  • ALTER TYPE ... SET (SUBSCRIPT = ...) property added (commit 8c15a297)
• ALTER USER
  • CURRENT_ROLE now also accepted in place of a user name (commit 45b98057)
• CREATE FUNCTION
  • support for SQL-standard function bodies with purse SQL functions (commit e717a9a1)
• CREATE LANGUAGE
  • obsolete option to specify the language name as a single-quoted string removed (commit 5333e014)
• CREATE PROCEDURE
  • support for SQL-standard function bodies with purse SQL functions (commit e717a9a1)
• CREATE STATISTICS
  • support for extended statistics on expressions added (commit a4d75c86)
• CREATE SUBSCRIPTION
  • parameter binary added (commit 9de77b54)
• CREATE TYPE
  • SUBSCRIPT function handler option added (commit c7aba7c1)
• CREATE TRIGGER
  • OR REPLACE option added (commit 92bf7e2d)
• EXPLAIN
  • EXPLAIN VERBOSE will now display the query identifier, if compute_query_id is not disabled (commit 4f0b0966)
• GRANT
  • GRANTED BY clause added (commit 6aaaa76b)
• INSERT
  • support for parallel SELECT with INSERT INTO ... SELECT ... (commit 05c8482f) withdrawn (commit 26acb54a)
• REINDEX
  • TABLESPACE clause added (commit c5b28604)
  • support for partitioned tables and indexes added (commit a6642b3a)
• REVOKE
  • GRANTED BY clause added (commit 6aaaa76b)
• SELECT
  • support for GROUP BY DISTINCT added (commit be45be9c)
  • support for SEARCH and CYCLE clauses added (commit 3696a600)
  • most keywords can be used as column labels without requiring AS (commit 06a7c315)
  • support for aliases with JOIN ... USING, e.g. SELECT ... FROM t1 JOIN t2 USING (a, b, c) AS x (commit 055fee7e)
• TRUNCATE
  • now possible to truncate foreign tables (commit 8ff1c946)
• VACUUM
  • option PROCESS_TOAST added (commit 7cb3048f)
  • in VERBOSE mode, reports on index pages deleted by the current operation (commit 23763618)

System catalogue changes

Tables

General

• primary keys and unique constraints added to system catalogue tables (commit dfb75e47)

Modified:

• pg_attribute
  • column attcompression added (commit bbe0a81d)
• pg_depend
  • column refobjectversion added (commit cd6f479e)
• pg_proc
  • column prosqlbody added (commit e717a9a1)
• pg_range
  • column rngtypid added (commit 6df7a969)
• pg_statistic_ext_data
  • column stxdexpr added (commit a4d75c86)
• pg_subscription
  • column subbinary added (commit 9de77b54)
  • column substream added (commit 46482432)
• pg_type
  • column typsubscript added (commit c7aba7c1)
  • column typtype can contain value m to denote a multirange type (commit 6df7a969)

Views

Added:

• pg_backend_memory_contexts (commit 3e98c0ba)
• pg_stat_prefetch_recovery (commit 1d257577) withdrawn (commit c2dc1934)
• pg_stat_progress_copy (commit 8a4f618e)
• pg_stat_replication_slots  (commit 98681675)
• pg_stat_wal (commit 8d9a9359)
• pg_stats_ext_exprs (commit a4d75c86)

Modified:

• pg_locks
  • column waitstart added (commit 46d6e5f5)
• pg_prepared_statements
  • column generic_plans added (commit d05b172a)
  • column custom_plans added (commit d05b172a)
• pg_stat_activity
  • column query_id added (commit 4f0b0966)
• pg_stat_database
  • following columns added (commit 960869da):
    • session_time
    • active_time
    • idle_in_transaction_time
    • sessions
    • sessions_abandoned
    • sessions_fatal
    • sessions_killed

System function changes

Added:

• bit_xor() (commit f9a0392e)
• date_bin() (commit 49ab61f0)
• isempty() (commit 6df7a969)
• lower(anymultirange) (commit 6df7a969)
• lower_inc() (commit 6df7a969)
• lower_inf() (commit 6df7a969)
• multirange() (commit 6df7a969)
• range_merge() (commit 6df7a969)
• upper(anymultirange) (commit 6df7a969)
• upper_inc() (commit 6df7a969)
• upper_inf() (commit 6df7a969)
• pg_column_compression() (commit bbe0a81d)
• pg_get_wal_replay_pause_state() (commit 32fd2b57)
• pg_log_backend_memory_contexts() (commit 43620e32)
• pg_relation_check_pages() (commit f2b88396) feature withdrawn
• pg_stat_reset_replication_slot() (commit 98681675)
• pg_wait_for_backend_termination() (commit aaf04325) withdrawn (commit 5f1df62a)
• string_to_table() (commit 66f16306)
• trim_array() (commit 0a687c8f)
• unistr() (commit f37fec83)

Modified:

• extract()
  • return type changed from double precision to numeric (commit a2da77cd)
• factorial()
  • factorial of negative numbers disallowed (commit 0a40563e)
• inet_server_addr()
  • marked as as parallel-restricted (commit 5a6f9bce)
• inet_server_port()
  • marked as as parallel-restricted (commit 5a6f9bce)
• ltrim()
  • support for bytea added (commit a6cf3df4)
• pg_terminate_backend()
  • parameter timeout added (commit aaf04325)
• pg_create_logical_replication_slot()
  • option two_phase added (commit 19890a06)
• pg_stat_reset_shared()
  • option wal added (commit 8d9a9359)
• rtrim()
  • support for bytea added (commit a6cf3df4)
• split_part()
  • support for negative indexes added (commit ec0294fb)
• stddev_samp()
  • returns NULL (previously: NaN) if a NaN value was provided (commit 23cbeda5)
• to_tsquery()
  • words residing adjacent positions now connected with with OP_PHRASE (commit 0c4f355c)
• unnest()
  • support for multirange argument added (commit 9e3c217b)
• var_samp()
  • returns NULL (previously: NaN) if a NaN value was provided (commit 23cbeda5)
• websearch_to_tsquery()
  • parses text in quotes as a single token (commit eb086056)
  • words residing adjacent positions now connected with with OP_PHRASE (commit 0c4f355c)

Removed:

• numeric_fac() (commit 76f412ab)

Configuration changes

General:

• custom configuration parameter names may only use names which are valid for unquoted SQL identifiers (commit 3db826bd)

Added:

• client_connection_check_interval (commit c30f54ad)
• compute_query_id (commit 5fd9dfa5)
• debug_discard_caches (commit 4656e3d6)
• default_toast_compression (commit bbe0a81d)
• enable_memoize (initial commit 9eacee2e)
• enable_parallel_insert (commit c8f78b61) withdrawn (commit 26acb54a)
• idle_session_timeout (commit 9877374b)
• in_hot_standby (commit bf8a662c)
• recovery_prefetch (commit 1d257577) withdrawn (commit c2dc1934)
• recovery_prefetch_fpw (commit 1d257577) withdrawn (commit c2dc1934)
• ssl_crl_dir (commit f5465fad)
• track_wal_io_timing (commit ff99918c)
• vacuum_failsafe_age (commit 1e55e7d1)
• vacuum_multixact_failsafe_age (commit 1e55e7d1)
• wal_decode_buffer_size (commit 1d257577)

Modified:

• checkpoint_completion_target
  • default value changed to 0.9 (commit bbcc4eb2)
• default_transaction_read_only
  • marked as GUC_REPORT (commit d16f8c8e)
• log_connections
  • authenticated identify details will also be logged (commit 9afffcb8)
• log_line_prefix
  • %P parameter added to show the parallel group leader (commit b8fdee7d)
  • %Q parameter added to show the query ID if available, otherwise 0 (commit 4f0b0966)
• password_encryption
  • default changed from md5 to scram-sha-256 (commit c7eab0e9)
  • legacy values on/true/yes/1 as aliases for md5 no longer valid (commit c7eab0e9)
• restore_command
  • can now be changed with a configuration reload (SIGHUP) rather than a server restart (commit 942305a3)
• vacuum_cost_page_miss
  • default value changed from 10 to 2 (commit e19594c5)

Removed:

• operator_precedence_warning (commit a676386b)
• vacuum_cleanup_index_scale_factor (commit 9f3665fb)

Users / roles

• pg_database_owner default role added (commit a14a0118)
• pg_read_all_data default role added (commit 6c3ffd69)
• pg_write_all_data default role added (commit 6c3ffd69)

Authentication changes

pg_hba.conf

• authentication option clientcert no longer supports the options 0, 1 and no-verify (commit 253f1025)

Regular expression changes

• \w now maps to [​[:word:]​​] (previously: [​[:alnum:]_]) and disallowed as range start/end in character classes (commit 2a0af7fe)
• \D, \S and \W are now allowed within bracket expressions (commit 2a0af7fe)
• handling of handling of regular expression back-references improved (commit 4aea704a)

Procedual language changes

PL/pgSQL

• performance of repeated CALL invocations improved (commit ee895a65)

Replication changes

Streaming replication

• WAL can optionally be pre-fetched by standbys (commit 1d257577)
• standby servers will now pause rather than shut down if critical standby configuration item values fall below those on the primary (commit 15251c0a)

Logical replication

• data can be transferred in binary format (commit 9de77b54)
• support for streaming of in-progress transactions added (commit 46482432)
• output plugin API is now able to decode prepared transactions (commit 0aa8a01d)

Foreign data wrapper changes

• bulk insert API added (commit b663a413)
• table truncation API added (commit 8ff1c946)

information schema changes

• routine usage tables added as placeholders (commit f40c6969)

libpq changes

General

• in_hot_standby now reported by PQparameterStatus() (commit bf8a662c)
• pipeline mode added (commit acb7e4eb)
• PQtrace() output format improved (commit 198b3716)
• Server Name Indication (SNI) set by default for SSL connections (commit 5c55dc8b)

Connection parameters

Modified:

• target_session_attrs
  • options read-only, primary, standby, and prefer-standby added (commit ee28cacf)

Removed:

• authtype (commit 14d9b376)
• tty (commit 14d9b376)

ECPG changes

• DECLARE STATEMENT command added (commit ad8305a4)

psql changes

• \dX command added to display extended statistics objects added (commit ad600bba)
• \di, \dm and \dt commands now display access method name (commit 07f386ed)
• all query results are displayed by default if a command string contains multiple queries (commit 3a513067)
• SHOW_ALL_RESULTS variable added (commit 3a513067)
• \df and \do commands now accept specification of argument types (commit a3027e1e)

Server utilities

Added:

• pg_alterckey (initial commit 62afb42a) withdrawn (commit 3187ef7c)

Modified:

• initdb
  • option --no-instructions added (commit e09155bd)
• pg_waldump
  • contents of logical messages will now also be included in the output (commit 9f1cf97b)

Core utilities

Added:

• pg_amcheck (commit 97060928)

Modified:

• pgbench
  • libpq pipeline support added via new commands \startpipeline and \endpipeline (commit 9aa491ab)
• pg_dump
  • option --extension added (commit 6568cef2)
  • option --no-toast-compression added (commit bbe0a81d)
• pg_dumpall
  • option --no-toast-compression added (commit 694da198)
• reindexdb
  • option --tablespace added (commit 57e6db70)
• vacuumdb
  • option --no-index-cleanup added (commit 9550ea30)
  • option --no-truncate added (commit 9550ea30)
  • option --no-process-toast added (commit 7cb3048f)

Contrib modules

Added:

• old_snapshot (commit aecf5ee2)
• pg_surgery (commit 34a947ca)
• bool_plperlu (commit 36058a3c)

Modified:

• amcheck
  • now able to check heap pages (commit 866e24d4)
• cube
  • binary I/O capability support added to the cube (commit 1265a9c8)
• btree_gist
  • marked as parallel safe (commit d98c08cd)
• hstore
  • support for subscripting of hstore values added (commit 0ec5f7e7)
• intarray
  • <@ operator removed from GiST operator classes (commit 20e7e1fe)
• pageinspect
  • data type of function arguments pertaining to block numbers changed from int to bigint (commit f18aa1b2)
  • functions for inspecting GiST indexes added (commit 756ab291)
• pg_stat_statements
  • now tracks the total number of rows retrieved or affected by CREATE TABLE AS, SELECT INTO, CREATE MATERIALIZED VIEW and FETCH commands (commit 6023b7ea)
  • now tracks the total number of rows processed by REFRESH MATERIALIZED VIEW (commit b62e6056)
  • view pg_stat_statements_info added to display statistics about pg_stat_statements operations (initial commit 9fbc3f31)
  • column toplevel added to view view pg_stat_statements indicating whether the tracked statement is a top-level or nested statement (commit 6b4d23fe)
• pgstattuple
  • function pgstattuple_approx() can report on TOAST tables (commit ee0202d5)
• pg_trgm
  • GiST/GIN pg_trgm indexes can perform equality lookups (commit 935f6666)
• postgres_fdw
  • support for bulk inserts (commit b663a413)
  • function postgres_fdw_get_connections() added (commit 708d165d)
  • function postgres_fdw_disconnect() added (commit 411ae649)
  • function postgres_fdw_disconnect_all() added (commit 411ae649)t
  • table/server option async_capable added to enable asynchronous execution support (commit 27e1f145)
  • server option keep_connections added (commit b1be3074)
  • partitions specified in the IMPORT FOREIGN SCHEMA ... LIMIT clause can now be imported (commit a3740c48)
  • support for truncating tables on the remote server added (commit 8ff1c946)
• tablefunc
  • function normal_rand() no longer accepts negative values (commit f7399926)

Removed:

• pg_standby (commit 514b411a)

Testing

Test modules

Added:

• PL/Sample (commit adbe62d0)