Improve planning time for queries referencing partitioned tables (David Rowley)

Specifically this helps if only a small number of the many partitions are relevant.

Allow ordered scans of partitions to avoid sorting in more cases (David Rowley)

Previously, a partitioned table with a DEFAULT partition or a LIST partition containing multiple values could not be used for ordered partition scans. Now they can be used if these partitions are pruned.

Improve foreign key behavior of updates on partitioned tables that move rows between partitions (Amit Langote)

Previously, such updates ran delete actions on the source partition and insert actions on the target partition. PostgreSQL will now run update actions on the referenced partition root.

Allow CLUSTER on partitioned tables (Justin Pryzby)

Fix ALTER TRIGGER RENAME on partitioned tables to properly rename triggers an all partitions (Arne Roland, Álvaro Herrera)

Also prohibit cloned triggers from being renamed.

Enable system and TOAST btree indexes to efficiently store duplicates (Peter Geoghegan)

Previously de-duplication was disabled for these types of indexes.

Improve lookup performance of GiST indexes built using sorting (Aliaksandr Kalenik, Sergei Shoulbakov, Andrey Borodin)

Prevent changes to columns only indexed by BRIN indexes from disabling HOT updates (Josef Simanek)

Allow unique constraints and indexes to treat NULL values as not distinct (Peter Eisentraut)

Previously NULL values were always indexed as distinct values, but this can now be changed by creating constraints and indexes using UNIQUE NULLS NOT DISTINCT.

Allow ^@ and starts_with() to use btree indexes if using a C collation (Tom Lane)

Previously these could only use SP-GiST indexes.

Allow extended statistics to record statistics for a parent with all its children (Tomas Vondra, Justin Pryzby)

Regular statistics already tracked parent and parent/all-children statistics separately.

Allow GROUP BY sorting to optimize column order (Dmitry Dolgov, Teodor Sigaev, Tomas Vondra)

This optimization can be disabled using the server variable enable_group_by_reordering.

Add server variable recursive_worktable_factor to allow the user to specify the expected recursive query worktable size (Simon Riggs)

WHAT IS A WORKTABLE? NOT DEFINED.

Allow hash lookup for NOT IN clauses with many constants (David Rowley, James Coleman)

Previously the code always sequentially scanned the list of values.

Improve validation of UTF-8 text (even if only ASCII) by processing 16 bytes at a time (John Naylor, Heikki Linnakangas)

This will improve text-heavy operations like COPY FROM.

Improve performance for sorts that exceed work_mem (Heikki Linnakangas)

Specifically, switch to a batch sorting algorithm that uses more output streams internally.

Improve performance and reduce memory consumption of in-memory sorts (Ronan Dunklau, David Rowley, Thomas Munro, John Naylor)

Allow WAL full page writes to use LZ4 and Zstandard compression (Andrey Borodin, Justin Pryzby)

This is controlled by the wal_compression server setting.

Add direct I/O support to macOS (Thomas Munro)

This only works if max_wal_senders=0 and wal_level=minimal, and only for WAL.

Allow vacuum to be more aggressive in setting the oldest frozenxid (Peter Geoghegan)

Allow a query referencing multiple foreign tables to perform parallel foreign table scans in more cases (Andrey Lepikhov, Etsuro Fujita)

Improve the performance of window functions that use row_number(), rank(), and count() (David Rowley)

Improve the performance of spinlocks on high-core-count ARM64 systems (Geoffrey Blake)

Enable default logging of checkpoints and slow autovacuum operations (Bharath Rupireddy)

Specifically, this changes the default of log_checkpoints to on and log_autovacuum_min_duration to 10 minutes. This will cause idle servers to generate log output, which might cause problems on resource-constrained servers with insufficient log file removal. The defaults should be changed in such cases.

Generate periodic log message during slow server starts (Nitin Jadhav, Robert Haas)

Messages report the cause of the delay. The time interval for notification is controlled by the new server variable log_startup_progress_interval.

Store server-level statistics in shared memory (Kyotaro Horiguchi, Andres Freund, Melanie Plageman)

Previously this was updated via UDP packets, stored in the file system, and read by sessions. There is no longer a statistics collector process.

Add additional information to VACUUM VERBOSE and autovacuum logging messages (Peter Geoghegan)

Add EXPLAIN (BUFFERS) output for temporary file block I/O (Masahiko Sawada)

Allow log output in JSON format (Sehrope Sarkuni, Michael Paquier)

The new setting is log_destination=jsonlog.

Allow pg_stat_reset_single_table_counters() to reset the counters of relations shared across all databases (B Sadhu, Prasad Patro)

Add wait events for local shell commands (Fujii Masao)

Specifically the new wait events are related to commands archive_command, archive_cleanup_command, restore_command and recovery_end_command.

Allow view access to be controlled by privileges of the view user (Christoph Heiss)

Previously, view access could only be based on the view owner.

Allow members of the pg_write_server_files predefined role to perform server-side base backups (Dagfinn Ilmari Mannsåker)

Previously only the superusers could perform such backups.

Allow GRANT to assign permission to change server variables via SET and ALTER SYSTEM (Mark Dilger)

New function has_parameter_privilege() reports on this privilege.

Add predefined role pg_checkpointer that allows members to run CHECKPOINT (Jeff Davis)

Previously checkpoints could only be run by superusers.

Allow members of the pg_read_all_stats predefined role to access the views pg_backend_memory_contexts and pg_shmem_allocations (Bharath Rupireddy)

Previously these views could only be accessed by superusers.

Allow GRANT to assign permissions on pg_log_backend_memory_contexts() (Jeff Davis)

Previously this function could only be run by superusers.

Add server variable shared_memory_size to report the size of allocated shared memory (Nathan Bossart)

Add server variable shared_memory_size_in_huge_pages to report the number of huge memory pages required (Nathan Bossart)

This is only supported on Linux.

Allow postgres -C to properly report runtime-computed values (Nathan Bossart)

Previously runtime-computed values data_checksums, wal_segment_size, and data_directory_mode would report values that would not be accurate on the running server. However, this does not work on a running server.

Allow publication of all tables in a schema (Vignesh C, Hou Zhijie, Amit Kapila)

For example, this syntax is now supported: CREATE PUBLICATION pub1 FOR ALL TABLES IN SCHEMA s1,s2; ALTER PUBLICATION supports a similar syntax. Tables added to the listed schemas in the future will also be replicated.

Allow publication content to be filtered using a WHERE clause (Hou Zhijie, Euler Taveira, Peter Smith, Ajin Cherian, Tomas Vondra, Amit Kapila)

Allow publications to be restricted to specific columns (Tomas Vondra, Álvaro Herrera, Rahila Syed)

Allow skipping of transactions on a subscriber using ALTER SUBSCRIPTION ... SKIP (Masahiko Sawada)

Add support for prepared transactions to built-in logical replication (Peter Smith, Ajin Cherian, Amit Kapila, Nikhil Sontakke, Stas Kelvich)

Add two-phase information to the logical replication stream (Ajin Cherian)

The new CREATE_REPLICATION_SLOT option is called TWO_PHASE. pg_recvlogical now supports a new --two-phase option during slot creation.

Prevent logical replication of empty transactions (Ajin Cherian, Hou Zhijie, Euler Taveira)

Previously, write transactions would send empty transactions to subscribers if subscribed tables were not modified.

Add SQL functions to monitor the directory contents of logical replication slots (Bharath Rupireddy)

Specifically, the functions are pg_ls_logicalsnapdir(), pg_ls_logicalmapdir(), and pg_ls_replslotdir(). They can be run by members of the predefined pg_monitor role.

Allow subscribers to stop logical replication application on error (Osumi Takamichi, Mark Dilger)

This is enabled with the subscriber option disable_on_error and avoids possible infinite error loops during stream application.

Adjust subscriber server variables to match the publisher so datetime and float8 values are interpreted consistently (Japin Li)

Some publishers might be relying on inconsistent behavior.

Add system view pg_stat_subscription_stats to report on subscriber activity (Masahiko Sawada)

New function pg_stat_reset_subscription_stats() allows the resetting of subscriber statistics.

Remove incorrect duplicate partitions in system view pg_publication_tables (Hou Zhijie)

Add SQL/JSON-standard JSON constructors (Nikita Glukhov)

The construction functions are json(), json_scalar(), and json_serialize(), json_array(), json_arrayagg(), json_object(), and json_objectagg(). They have a few functional advantages over the existing JSON functions.

Add SQL/JSON query functions json_exists(), json_query(), and json_value() (Nikita Glukhov)

Add JSON predicates to test JSON/JSONB values (Nikita Glukhov)

The clauses are IS JSON [ VALUE | ARRAY | OBJECT | SCALAR | [WITH | WITHOUT ] UNIQUE KEYS ].

Add function JSON_TABLE() to cause JSON data to be treated as a table (Nikita Glukhov)

Improve performance of psql's \copy command (Heikki Linnakangas)

Add psql command \getenv to assign the value of an environment variable to a psql variable (Tom Lane)

Add '+' option to psql's \lo_list/\dl to show object privileges (Pavel Luzanov)

Add psql \dconfig to report server variables (Mark Dilger, Tom Lane)

This is similar to the server-side SHOW command but can process patterns.

Add pager option for psql's \watch command (Pavel Stehule, Thomas Munro)

This is only supported on Unix, and is controlled by PSQL_WATCH_PAGER.

Have psql send intra-query double-hyphen comments to the server (Tom Lane, Greg Nancarrow)

Previously such comments were removed from the query before being sent. Double-hyphen comments that are before query text are not sent, and are not recorded as separate psql history entries.

Adjust psql's readline meta-# to insert a double-hyphen comment marker (Tom Lane)

Previously an unhelpful pound marker was inserted.

Have psql output all results if multiple queries are passed to the server at once (Fabien Coelho)

This can be disabled by setting SHOW_ALL_RESULTS.

Improve psql's tab completion (Shinya Kato, Dagfinn Ilmari Mannsåker, Peter Smith, Koyu Tanigawa, Ken Kato, David Fetter, Haiying Tang, Peter Eisentraut, Álvaro Herrera, Tom Lane, Masahiko Sawada)

Limit support of psql to servers running PostgreSQL 9.2 and later (Tom Lane)

Have pg_dump dump public schema ownership changes and security labels (Noah Misch)

It also dumps public schema comments.

Improve performance of dumping databases with many objects (Tom Lane)

This will also improve the performance of pg_upgrade.

Improve the parallel pg_dump performance of TOAST tables (Tom Lane)

Add dump/restore option --no-table-access-method to force restore to only use the default table access method (Justin Pryzby)

Limit support of pg_dump and pg_dumpall to servers running PostgreSQL 9.2 and later (Tom Lane)

Disable default status reporting during pg_upgrade operation if the output is not a terminal (Andres Freund)

The status reporting output can be enabled for non-tty usage by using --verbose.

Have pg_upgrade report all databases with invalid connection settings (Jeevan Ladhe)

Previously only the first database with an invalid connection setting was reported.

Store pg_upgrade temporary files in a new cluster subdirectory called pg_upgrade_output.d (Justin Pryzby)

Previously temporary files were stored in the current directory.

Have pg_upgrade preserve relfilenodes, tablespace, and database OIDs between old and new clusters (Shruthi KC, Antonin Houska)

Add a --no-sync option to pg_upgrade (Michael Paquier)

This useful only for testing.

Limit support of pg_upgrade to old servers running PostgreSQL 9.2 and later (Tom Lane)

Allow pg_waldump to be filtered by relation file node, block number, fork number, and full page images (David Christensen, Thomas Munro)

Have pg_waldump report statistics before an interrupted exit (Bharath Rupireddy)

For example, issuing a control-C in a terminal running pg_waldump --stats --follow will report the current statistics before exiting. This does not work on Windows.

Improve descriptions of some transaction WAL records reported by pg_waldump (Masahiko Sawada, Michael Paquier)

Allow pg_waldump to dump information about multiple resource managers (Heikki Linnakangas)

This is enabled by specifying the --rmgr option multiple times.

Allow postgres_fdw to push down CASE expressions (Alexander Pyhalov)

Add server variable postgres_fdw.application_name to control the application name of postgres_fdw connections (Hayato Kuroda)

Previously the remote application_name could only be set on the remote server or via postgres_fdw connection specification. postgres_fdw.application_name also supports escape sequences for customization.

Allow parallel commit on postgres_fdw servers (Etsuro Fujita)

This is enabled with the CREATE SERVER option parallel_commit when using postgres_fdw.