E.5. Release 12

Improve performance of many operations on partitioned tables (Amit Langote, David Rowley, Tom Lane, Álvaro Herrera)

Allow tables with thousands of child partitions to be processed efficiently by operations that only affect a small number of partitions.

Allow foreign keys to reference partitioned tables (Álvaro Herrera)

Improve speed of COPY into partitioned tables (David Rowley)

Allow partition bounds to be any expression (Kyotaro Horiguchi, Tom Lane, Amit Langote)

Such expressions are evaluated at partitioned-table creation time. Previously, only simple constants were allowed as partition bounds.

Allow CREATE TABLE 's tablespace specification for a partitioned table to affect the tablespace of its children (David Rowley, Álvaro Herrera)

Avoid sorting when partitions are already being scanned in the necessary order (David Rowley)

ALTER TABLE ATTACH PARTITION is now performed with reduced locking requirements (Robert Haas)

Add partition introspection functions (Michaël Paquier, Álvaro Herrera, Amit Langote)

The new function pg_partition_root() returns the top-most parent of a partition tree, pg_partition_ancestors() reports all ancestors of a partition, and pg_partition_tree() displays information about partitions.

Include partitioned indexes in the system view pg_indexes (Suraj Kharage)

Add psql command \dP to list partitioned tables and indexes (Pavel Stehule)

Improve psql \d and \z display of partitioned tables (Pavel Stehule, Michaël Paquier, Álvaro Herrera)

Fix bugs that could cause ALTER TABLE DETACH PARTITION to leave behind incorrect dependency state, allowing subsequent operations to misbehave, for example by not dropping a former partition child index when its table is dropped (Tom Lane)

Improve performance and space utilization of btree indexes with many duplicates (Peter Geoghegan, Heikki Linnakangas)

Previously, duplicate index entries were stored unordered within their duplicate groups. This caused overhead during index inserts, wasted space due to excessive page splits, and it reduced VACUUM 's ability to recycle entire pages. Duplicate index entries are now sorted in heap-storage order.

Indexes pg_upgrade 'd from previous releases will not have these benefits.

Allow multi-column btree indexes to be smaller (Peter Geoghegan, Heikki Linnakangas)

Internal pages and min/max leaf page indicators now only store index keys until the change key, rather than all indexed keys. This also improves the locality of index access.

Indexes pg_upgrade 'd from previous releases will not have these benefits.

Improve speed of btree index insertions by reducing locking overhead (Alexander Korotkov)

Support INCLUDE columns in GiST indexes (Andrey Borodin)

Add support for nearest-neighbor ( KNN ) searches of SP-GiST indexes (Nikita Glukhov, Alexander Korotkov, Vlad Sterzhanov)

Reduce the WAL write overhead of GiST , GIN , and SP-GiST index creation (Anastasia Lubennikova, Andrey V. Lepikhov)

Allow index-only scans to be more efficient on indexes with many columns (Konstantin Knizhnik)

Improve the performance of vacuum scans of GiST indexes (Andrey Borodin, Konstantin Kuznetsov, Heikki Linnakangas)

Delete empty leaf pages during GiST VACUUM (Andrey Borodin)

Reduce locking requirements for index renaming (Peter Eisentraut)

Allow CREATE STATISTICS to create most-common-value statistics for multiple columns (Tomas Vondra)

This improves optimization for queries that test several columns, requiring an estimate of the combined effect of several WHERE clauses. If the columns are correlated and have non-uniform distributions then multi-column statistics will allow much better estimates.

Allow common table expressions ( CTEs ) to be inlined into the outer query (Andreas Karlsson, Andrew Gierth, David Fetter, Tom Lane)

Specifically, CTE s are automatically inlined if they have no side-effects, are not recursive, and are referenced only once in the query. Inlining can be prevented by specifying MATERIALIZED , or forced for multiply-referenced CTE s by specifying NOT MATERIALIZED . Previously, CTE s were never inlined and were always evaluated before the rest of the query.

Allow control over when generic plans are used for prepared statements (Pavel Stehule)

This is controlled by the plan_cache_mode server parameter.

Improve optimization of partition and UNION ALL queries that have only a single child (David Rowley)

Improve processing of domains that have no check constraints (Tom Lane)

Domains that are being used purely as type aliases no longer cause optimization difficulties.

Pre-evaluate calls of LEAST and GREATEST when their arguments are constants (Vik Fearing)

Improve optimizer's ability to verify that partial indexes with IS NOT NULL conditions are usable in queries (Tom Lane, James Coleman)

Usability can now be recognized in more cases where the calling query involves casts or large x IN ( array ) clauses.

Compute ANALYZE statistics using the collation defined for each column (Tom Lane)

Previously, the database's default collation was used for all statistics. This potentially gives better optimizer behavior for columns with non-default collations.

Improve selectivity estimates for inequality comparisons on ctid columns (Edmund Horner)

Improve optimization of joins on columns of type tid (Tom Lane)

These changes primarily improve the efficiency of self-joins on ctid columns.

Fix the leakproofness designations of some btree comparison operators and support functions (Tom Lane)

This allows some optimizations that previously would not have been applied in the presence of security barrier views or row-level security.

Enable Just-in-Time ( JIT ) compilation by default, if the server has been built with support for it (Andres Freund)

Note that this support is not built by default, but has to be selected explicitly while configuring the build.

Speed up keyword lookup (John Naylor, Joerg Sonnenberger, Tom Lane)

Improve search performance for multi-byte characters in position() and related functions (Heikki Linnakangas)

Allow toasted values to be minimally decompressed (Paul Ramsey)

This is useful for routines that only need to examine the initial portion of a toasted field.

Allow ALTER TABLE ... SET NOT NULL to avoid unnecessary table scans (Sergei Kornilov)

This can be optimized when the table's column constraints can be recognized as disallowing nulls.

Allow ALTER TABLE ... SET DATA TYPE changing between timestamp and timestamptz to avoid a table rewrite when the session time zone is UTC (Noah Misch)

In the UTC time zone, these two data types are binary compatible.

Improve speed in converting strings to int2 or int4 integers (Andres Freund)

Allow parallelized queries when in SERIALIZABLE isolation mode (Thomas Munro)

Previously, parallelism was disabled when in this mode.

Use pread() and pwrite() for random I/O (Oskari Saarenmaa, Thomas Munro)

This reduces the number of system calls required for I/O.

Improve the speed of setting the process title on FreeBSD (Thomas Munro)

Allow logging of statements from only a percentage of transactions (Adrien Nayrat)

The parameter log_transaction_sample_rate controls this.

Add progress reporting to CREATE INDEX and REINDEX operations (Álvaro Herrera, Peter Eisentraut)

Progress is reported in the pg_stat_progress_create_index system view.

Add progress reporting to CLUSTER and VACUUM FULL (Tatsuro Yamada)

Progress is reported in the pg_stat_progress_cluster system view.

Add progress reporting to pg_checksums (Michael Banck, Bernd Helmle)

This is enabled with the option --progress .

Add counter of checksum failures to pg_stat_database (Magnus Hagander)

Add tracking of global objects in system view pg_stat_database (Julien Rouhaud)

Global objects are shown with a pg_stat_database . datid value of zero.

Add the ability to list the contents of the archive directory (Christoph Moench-Tegeder)

The function is pg_ls_archive_statusdir() .

Add the ability to list the contents of temporary directories (Nathan Bossart)

The function, pg_ls_tmpdir() , optionally allows specification of a tablespace.

Add information about the client certificate to the system view pg_stat_ssl (Peter Eisentraut)

The new columns are client_serial and issuer_dn . Column clientdn has been renamed to client_dn for clarity.

Restrict visibility of rows in pg_stat_ssl for unprivileged users (Peter Eisentraut)

At server start, emit a log message including the server version number (Christoph Berg)

Prevent logging " incomplete startup packet " if a new connection is immediately closed (Tom Lane)

This avoids log spam from certain forms of monitoring.

Include the application_name , if set, in log_connections log messages (Don Seiler)

Make the walreceiver set its application name to the cluster name, if set (Peter Eisentraut)

Add the timestamp of the last received standby message to pg_stat_replication (Lim Myungkyu)

Add a wait event for fsync of WAL segments (Konstantin Knizhnik)

Add GSSAPI encryption support (Robbie Harwood, Stephen Frost)

This feature allows TCP/IP connections to be encrypted when using GSSAPI authentication, without having to set up a separate encryption facility such as SSL. In support of this, add hostgssenc and hostnogssenc record types in pg_hba.conf for selecting connections that do or do not use GSSAPI encryption, corresponding to the existing hostssl and hostnossl record types. There is also a new gssencmode libpq option, and a pg_stat_gssapi system view.

Allow the clientcert pg_hba.conf option to check that the database user name matches the client certificate's common name (Julian Markwort, Marius Timmer)

This new check is enabled with clientcert=verify-full .

Allow discovery of an LDAP server using DNS SRV records (Thomas Munro)

This avoids the requirement of specifying ldapserver . It is only supported if PostgreSQL is compiled with OpenLDAP .

Add ability to enable/disable cluster checksums using pg_checksums (Michael Banck, Michaël Paquier)

The cluster must be shut down for these operations.

Reduce the default value of autovacuum_vacuum_cost_delay to 2ms (Tom Lane)

This allows autovacuum operations to proceed faster by default.

Allow vacuum_cost_delay to specify sub-millisecond delays, by accepting fractional values (Tom Lane)

Allow time-based server parameters to use units of microseconds ( us ) (Tom Lane)

Allow fractional input for integer server parameters (Tom Lane)

For example, SET work_mem = '30.1GB' is now allowed, even though work_mem is an integer parameter. The value will be rounded to an integer after any required units conversion.

Allow units to be defined for floating-point server parameters (Tom Lane)

Add wal_recycle and wal_init_zero server parameters to control WAL file recycling (Jerry Jelinek)

Avoiding file recycling can be beneficial on copy-on-write file systems like ZFS .

Add server parameter tcp_user_timeout to control the server's TCP timeout (Ryohei Nagaura)

Allow control of the minimum and maximum SSL protocol versions (Peter Eisentraut)

The server parameters are ssl_min_protocol_version and ssl_max_protocol_version .

Add server parameter ssl_library to report the SSL library version used by the server (Peter Eisentraut)

Add server parameter shared_memory_type to control the type of shared memory to use (Andres Freund)

This allows selection of System V shared memory, if desired.

Add CSV table output mode in psql (Daniel Vérité)

This is controlled by \pset format csv or the command-line --csv option.

Show the manual page URL in psql 's \help output for a SQL command (Peter Eisentraut)

Display the IP address in psql 's \conninfo (Fabien Coelho)

Improve tab completion of CREATE TABLE , CREATE TRIGGER , CREATE EVENT TRIGGER , ANALYZE , EXPLAIN , VACUUM , ALTER TABLE , ALTER INDEX , ALTER DATABASE , and ALTER INDEX ALTER COLUMN (Dagfinn Ilmari Mannsåker, Tatsuro Yamada, Michaël Paquier, Tom Lane, Justin Pryzby)

Allow values produced by queries to be assigned to pgbench variables (Fabien Coelho, Álvaro Herrera)

The command for this is \gset .

Improve precision of pgbench 's --rate option (Tom Lane)

Improve pgbench 's error reporting with clearer messages and return codes (Peter Eisentraut)

When pg_dump emits data with INSERT commands rather than COPY , allow more than one data row to be included in each INSERT (Surafel Temesgen, David Rowley)

The option controlling this is --rows-per-insert .

Allow pg_dump to emit INSERT ... ON CONFLICT DO NOTHING (Surafel Temesgen)

This avoids conflict failures during restore. The option is --on-conflict-do-nothing .

Decouple the order of operations in a parallel pg_dump from the order used by a subsequent parallel pg_restore (Tom Lane)

This allows pg_restore to perform more-fully-parallelized parallel restores, especially in cases where the original dump was not done in parallel. Scheduling of a parallel pg_dump is also somewhat improved.

Allow the extra_float_digits setting to be specified for pg_dump and pg_dumpall (Andrew Dunstan)

This is primarily useful for making dumps that are exactly comparable across different source server versions. It is not recommended for normal use, as it may result in loss of precision when the dump is restored.

Add --exclude-database option to pg_dumpall (Andrew Dunstan)