Allow faster partition elimination during query processing (Amit Langote, David Rowley, Dilip Kumar)

This speeds access to partitioned tables with many partitions.

Allow partition elimination during query execution (David Rowley, Beena Emerson)

Previously partition elimination could only happen at planning time, meaning many joins and prepared queries could not use partition elimination.

Allow the creation of partitions based on hashing a key (Amul Sul)

Allow updated rows to automatically move to new partitions based on the new row contents (Amit Khandekar)

Allow partitioned tables to have a default partition (Jeevan Ladhe, Beena Emerson, Ashutosh Bapat, Rahila Syed, Robert Haas)

The default partition can store rows that don't match any of the other defined partitions, and is searched accordingly.

Allow UNIQUE indexes on partitioned tables if the partition key guarantees uniqueness (Álvaro Herrera, Amit Langote)

Allow indexes on a partitioned table to be automatically created in any child partitions (Álvaro Herrera)

The new command ALTER INDEX ATTACH PARTITION allows indexes to be attached to partitions. This does not behave as a global index since the contents are private to each index. WARN WHEN USING AN EXISTING INDEX?

Allow foreign keys on partitioned tables (Álvaro Herrera)

Allow INSERT, UPDATE, and COPY on partitioned tables to properly route rows to foreign partitions (Etsuro Fujita, Amit Langote)

This is supported by postgres_fdw foreign tables.

Allow FOR EACH ROW triggers on partitioned tables (Álvaro Herrera)

Creation of a trigger on partitioned tables automatically creates triggers on all partition tables, and on newly-created ones. This also allows deferred unique constraints on partitioned tables.

Allow equality joins between partitioned tables with identically partitioned child tables to join the child tables directly (Ashutosh Bapat)

This features is disabled by default but can be enabled by changing enable_partitionwise_join.

Perform aggregation on each partition, and then merge the results (Jeevan Chalke, Ashutosh Bapat, Robert Haas)

This features is disabled by default but can be enabled by changing enable_partitionwise_aggregate.

Allow postgres_fdw to push down aggregates to foreign tables that are partitions (Jeevan Chalke)

Allow btree indexes to be built in parallel (Peter Geoghegan, Rushabh Lathia, Heikki Linnakangas)

Allow hash joins to be performed in parallel using a shared hash table (Thomas Munro)

Allow UNION to run each SELECT in parallel if the individual SELECTs cannot be parallelized (Amit Khandekar, Robert Haas, Amul Sul)

Allow partition scans to more efficiently use parallel workers (Amit Khandekar, Robert Haas, Amul Sul)

Allow LIMIT to be passed to parallel workers (Robert Haas, Tom Lane)

This allows workers to reduce returned results and use targeted index scans.

Allow single-evaluation queries, e.g. WHERE clause aggregate queries, and functions in the target list to be parallelized (Amit Kapila, Robert Haas)

Add server option parallel_leader_participation to control if the leader executes subplans (Thomas Munro)

The default is enabled, meaning the leader will execute subplans.

Allow parallelization of commands CREATE TABLE .. AS, SELECT INTO, and CREATE MATERIALIZED VIEW (Haribabu Kommi)

Improve performance of sequential scans with many parallel workers (David Rowley)

Add reporting of parallel worker sort activity to EXPLAIN (Robert Haas, Tom Lane)

Allow indexes to INCLUDE columns that are not part of the unique constraint but are available for index-only scans (Anastasia Lubennikova, Alexander Korotkov, Teodor Sigaev)

This is also useful for including columns that don't have btree support.

Remember the highest btree index page to optimize future monotonically increasing index additions (Pavan Deolasee, Peter Geoghegan)

Allow entire hash index pages to be scanned (Ashutosh Sharma)

Previously for each hash index entry, we need to refind the scan position within the page. This cuts down on lock/unlock traffic.

Add predicate locking for hash, GiST and GIN indexes (Shubham Barai)

This reduces the likelihood of serialization conflicts. ACCURATE?

Allow heap-only-tuple (HOT) updates for expression indexes when the values of the expressions are unchanged (Konstantin Knizhnik)

Improve the selection of the optimizer statistics' most-common-values (Jeff Janes, Dean Rasheed)

Previously most-common-values (MCV) were chosen based on their significance compared to all column values. Now, MCV are chosen based on their significance compared to the non-MCV values. This improves the statistics for uniform (fewer) and non-uniform (more) distributions.

Improve selectivity estimates for >= and <= when the constants are not common values (Tom Lane)

Previously such cases used the same selectivity as > and <, respectively. This change is particularly useful for BETWEEN with small ranges.

Optimize var = var to var IS NOT NULL where equivalent (Tom Lane)

This leads to better selectivity estimates.

Improve row count optimizer estimates for EXISTS and NOT EXISTS queries (Tom Lane)

Add optimizer selectivity costs for HAVING clauses (Tom Lane)

Add Just-In-Time (JIT) compilation of some parts of query plans to improve execution speed (Andres Freund)

Allow bitmap scans to perform index-only scans when possible (Alexander Kuzmenkov)

Update the free space map during vacuum (Claudio Freire)

This allows free space to be reused more quickly.

Allow vacuum to avoid unnecesary index scans (Masahiko Sawada, Alexander Korotkov)

Improve performance of committing multiple concurrent transactions (Amit Kapila)

Reduce memory usage for queries using set-returning functions in their target lists (Andres Freund)

Allow postgres_fdw to push UPDATEs and DELETEs using joins to foreign servers (Etsuro Fujita)

Previously only non-join UPDATEs and DELETEs were pushed.

Show memory usage in log_statement_stats, log_parser_stats, log_planner_stats, log_executor_stats (Justin Pryzby, Peter Eisentraut)

Add pg_stat_activity.backend_type now shows the type of background worker (Peter Eisentraut)

Add bgw_type to the background worker C structure (Peter Eisentraut)

This is displayed to the user in pg_stat_activity.backend_type and ps output.

Have log_autovacuum_min_duration log skipped tables that are concurrently being dropped (Nathan Bossart)

Add libpq option to support channel binding when using SCRAM authentication (Michael Paquier)

While SCRAM always prevents the replay of transmitted hashed passwords in a later session, SCRAM with channel binding also prevents man-in-the-middle attacks. The options are scram_channel_binding=tls-unique and scram_channel_binding=tls-server-end-point.

Allow the server to specify more complex LDAP specifications in search+bind mode (Thomas Munro)

Specifically, "ldapsearchfilter" allows pattern matching using combinations of LDAP attributes.

Allow LDAP authentication to use ldaps (Thomas Munro)

We already supported LDAP over TLS by using ldaptls=1. This new TLS LDAP method of encrypted LDAP is enabled with ldapscheme=ldaps or ldapurl=ldaps://.

Improve LDAP logging of errors (Thomas Munro)

Add default roles which controls file system access (Stephen Frost)

Specifically, the new roles are: pg_read_server_files, pg_write_server_files, pg_execute_server_program. These roles now also control who can use COPY and extension file_fdw. Previously only super-users could use these functions, and that is still the default behavior.

Allow access to file system functions to be controlled by GRANT/REVOKE permissions, rather than super-user checks (Stephen Frost)

Specifically, these functions were modified: pg_ls_dir(), pg_read_file(), pg_read_binary_file(), pg_stat_file().

Use GRANT/REVOKE to control access to lo_import() and lo_export() (Michael Paquier, Tom Lane)

Previously super users were exclusively granted to access these functions.

Compile-time option ALLOW_DANGEROUS_LO_FUNCTIONS has been removed.

Use view owner not session owner when preventing non-password access to postgres_fdw tables (Robert Haas)

PostgreSQL only allows super-users to access postgres_fdw tables without passwords, e.g. via peer. Previously the session owner had to be a super-user to allow such access; now the view owner is checked instead.

Fix invalid locking permission check in SELECT FOR UPDATE on views (Tom Lane)

Add server setting ssl_passphrase_command to allow supplying of the the passphrase for SSL key files (Peter Eisentraut)

Also add ssl_passphrase_command_supports_reload to specify whether the the SSL configuration should be reloaded and ssl_passphrase_command called during a server configuration reload.

Add storage parameter toast_tuple_target to control the minimum length before TOAST storage will be considered for new rows (Simon Riggs)

The default TOAST threshold has not been changed.

Allow server options related to memory and file sizes to be specified as number of bytes (Beena Emerson)

The new unit is "B". This is in addition to "kB", "MB", "GB" and "TB", which were accepted previously.

Allow the WAL file size to be set via initdb (Beena Emerson)

Previously the 16MB default could only be changed at compile time.

No longer retain WAL that spans two checkpoints (Simon Riggs)

The retention of WAL records for only one checkpoint is required.

Fill the unused portion of force-switched WAL segment files with zeros for improved compressibility (Chapman Flack)

Add psql command \gdesc to display the column names and types of the query output (Pavel Stehule)

Add psql variables to report query activity and errors (Fabien Coelho)

Specifically, the new variables are ERROR, SQLSTATE, ROW_COUNT, LAST_ERROR_MESSAGE, and LAST_ERROR_SQLSTATE.

Allow psql to test for the existence of a variable (Fabien Coelho)

Specifically , the syntax :{?variable_name} allows a variable's existence to be tested in an \if statement.

Add PSQL_PAGER to control psql's pager (Pavel Stehule)

This allows psql's default pager to be specified as a separate environment variable from the pager for other applications. PAGER is still honored if PSQL_PAGER is not set.

Have psql \d+ always show the partition information (Amit Langote, Ashutosh Bapat)

Previously partition information would not be displayed for a partitioned table if it had no partitions. Also indicate which partitions are themselves partitioned.

Have psql report the proper user name before the password prompt (Tom Lane)

Previously, combinations of -U and a user name embedded in a URI caused incorrect reporting. Also suppress the user name before the password prompt when --password is specified.

Allow quit and exit to exit psql when used in an empty buffer (Bruce Momjian)

Also add hints of how to exit when quit and exit are used alone on a line in a non-empty buffer. Add a similar hint for help.

Have psql hint at using control-D when \q is entered alone on a line but ignored (Bruce Momjian)

For example, \q does not exit when supplied in character strings.

Improve tab-completion for ALTER INDEX RESET/SET (Masahiko Sawada)

Add infrastructure to allow psql to customize tab completion queries based on the server version (Tom Lane)

Previously tab completion queries could fail.

Add pgbench expressions support for NULLs, booleans, and some functions and operators (Fabien Coelho)

Add \if conditional support to pgbench (Fabien Coelho)

Allow the use of non-ASCII characters in pgbench variable names (Fabien Coelho)

Add pgbench option --init-steps to control the initialization steps performed (Masahiko Sawada)

Add approximated Zipfian-distributed random generator to pgbench (Alik Khilazhev)

Allow the random seed to be set in pgbench (Fabien Coelho)

Allow pgbench to do exponentiation with pow() and power() (Raúl Marín Rodríguez)

Add hashing functions to pgbench (Ildar Musin)

Make pgbench statistics more accurate when using --latency-limit and --rate (Fabien Coelho)

Add pg_dumpall option --encoding to control encoding (Michael Paquier)

pg_dump already had this option.

Add pg_dump option --load-via-partition-root to force loading of data into the partition's root table, rather than the original partitions (Rushabh Lathia)

This is useful if the system to be loaded has a different collation definitions or endianness, requiring the rows to be stored in different partitions.

Add ability to suppress dumping and restoring of comments (Robins Tharakan)

The new pg_dump, pg_dumpall, and pg_restore option is --no-comments.