PostgreSQL 14

PostgreSQL 14 is the next major PostgreSQL release, currently under development.

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

• PostgreSQL 14 Open Items

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)
• wraparound failsafe functionality added (commit 1e55e7d1)

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)

Indexing changes

General

• support for collation versioning added (initial commit 257836a7)

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)

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)
• ALTER TABLE
  • ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY syntax added (commit 71f4c8c6)
• 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 TYPE
  • SUBSCRIPT function handler option added (commit c7aba7c1)
• CREATE TRIGGER
  • OR REPLACE option added (commit 92bf7e2d)
• 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_depend
  • column refobjectversion added (commit cd6f479e)
• pg_range
  • column rngtypid added (commit 6df7a969)
• pg_statistic_ext_data
  • column stxdexpr added (commit a4d75c86)
• 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)
• 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_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)
• 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)
• string_to_table() (commit 66f16306)
• trim_array() (commit 0a687c8f)
• unistr() (commit f37fec83)

Modified:

• 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)

Configuration changes

Added:

• client_connection_check_interval (commit c30f54ad)
• compute_query_id (commit 5fd9dfa5)
• default_toast_compression (commit bbe0a81d)
• enable_parallel_insert (commit c8f78b61) withdrawn (commit 26acb54a)
• idle_session_timeout (commit 9877374b)
• in_hot_standby (commit bf8a662c)
• recovery_prefetch (commit 1d257577)
• recovery_prefetch_fpw (commit 1d257577)
• 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)
• 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)
• 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)

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)

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

• 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
  • --extension option added (commit 6568cef2)
• 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:

• hstore
  • support for subscripting of hstore values added (commit 0ec5f7e7)
• intarray
  • <@ operator removed from GiST operator classes (commit 20e7e1fe)
• pageinspect
  • 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)
• 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)

Removed:

• pg_standby (commit 514b411a)

Testing

Test modules

Added:

• PL/Sample (commit adbe62d0)