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

Support indexes on partitioned tables (Álvaro Herrera, Amit Langote)

An « index » on a partitioned table is not a physical index across the whole partitioned table, but rather a template for automatically creating similar indexes on each partition of the table.

If the partition key is part of the index's column set, a partitioned index may be declared UNIQUE. It will represent a valid uniqueness constraint across the whole partitioned table, even though each physical index only enforces uniqueness within its own partition.

The new command ALTER INDEX ATTACH PARTITION causes an existing index on a partition to be associated with a matching index template for its partitioned table. This provides flexibility in setting up a new partitioned index for an existing partitioned table.

Allow foreign keys on partitioned tables (Álvaro Herrera)

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

Creation of a trigger on a partitioned table automatically creates triggers on all existing and future partitions. This also allows deferred unique constraints on partitioned tables.

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

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

UPDATE statements that change a partition key column now cause affected rows to be moved to the appropriate partitions (Amit Khandekar)

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. Since the ExecForeignInsert callback function is called for this in a different way than it used to be, foreign data wrappers must be modified to cope with this change.

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 only happened at planning time, meaning many joins and prepared queries could not use partition elimination.

In an equality join between partitioned tables, allow matching partitions to be joined directly (Ashutosh Bapat)

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

Allow aggregate functions on partitioned tables to be evaluated separately for each partition, subsequently merging the results (Jeevan Chalke, Ashutosh Bapat, Robert Haas)

This feature 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 parallel building of a btree index (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 parameter parallel_leader_participation to control whether the leader also 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 workers' sort activity in EXPLAIN (Robert Haas, Tom Lane)

Allow B-tree indexes to include columns that are not part of the search key or unique constraint, but are available to be read by index-only scans (Anastasia Lubennikova, Alexander Korotkov, Teodor Sigaev)

This is enabled by the new INCLUDE clause of CREATE INDEX. It facilitates building « covering indexes » that optimize specific types of queries. Columns can be included even if their data types don't have B-tree support.

Improve performance of monotonically increasing index additions (Pavan Deolasee, Peter Geoghegan)

Improve performance of hash index scans (Ashutosh Sharma)

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

This reduces the likelihood of serialization conflicts in serializable-mode transactions.

Improve selection of the most common values for statistics (Jeff Janes, Dean Rasheed)

Previously, the most common values (MCVs) were identified based on their frequency compared to all column values. Now, MCVs are chosen based on their frequency compared to the non-MCV values. This improves the robustness of the algorithm for both uniform and non-uniform distributions.

Improve selectivity estimates for >= and <= (Tom Lane)

Previously, such cases used the same selectivity estimates as > and <, respectively, unless the comparison constants are MCVs. This change is particularly helpful for queries involving BETWEEN with small ranges.

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

This leads to better selectivity estimates.

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

Make the optimizer account for evaluation costs and selectivity of HAVING clauses (Tom Lane)

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

This feature requires LLVM to be available. It is not currently enabled by default, even in builds that support it.

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

Improve the speed of aggregate computations (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.

Add support for large pages on Windows (Takayuki Tsunakawa, Thomas Munro)

This is controlled by the huge_pages configuration parameter.

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

Add column pg_stat_activity.backend_type to show the type of a background worker (Peter Eisentraut)

The type is also visible in ps output.

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

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 encrypted LDAP (Thomas Munro)

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

Improve logging of LDAP errors (Thomas Munro)

Add default roles that enable file system access (Stephen Frost)

Specifically, the new roles are: pg_read_server_files, pg_write_server_files, and pg_execute_server_program. These roles now also control who can use server-side COPY and the file_fdw extension. Previously, only superusers 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 superuser 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, only superusers were granted access to these functions.

The 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 superusers to access postgres_fdw tables without passwords, e.g., via peer. Previously, the session owner had to be a superuser 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 passphrase for SSL key files (Peter Eisentraut)

Also add ssl_passphrase_command_supports_reload to specify whether 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 tuple length before TOAST storage will be considered (Simon Riggs)

The default TOAST threshold has not been changed.

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

The new unit suffix is « B ». This is in addition to the existing units « kB », « MB », « GB » and « TB ».

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

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

Retain WAL data for only a single checkpoint (Simon Riggs)

Previously, WAL was retained for two checkpoints.

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

Add psql command \gdesc to display the names and types of the columns in a query result (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.

Allow environment variable 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.

Make psql's \d+ command always show the table's partitioning 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.

Ensure that psql reports the proper user name when prompting for a password (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 given with no prior input (Bruce Momjian)

Also print hints about how to exit when quit and exit are used alone on a line while the input buffer is not empty. Add a similar hint for help.

Make 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 adapt its tab completion queries based on the server version (Tom Lane)

Previously, tab completion queries could fail against older servers.

Add pgbench expression 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 an approximately 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 output 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 partition (Rushabh Lathia)

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

Add an option to suppress dumping and restoring database object comments (Robins Tharakan)

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