Contributions to PostgreSQL 12

• Move recovery.conf settings into postgresql.conf (Fujii Masao, Simon Riggs, Abhijit Menon-Sen, Sergei Kornilov) recovery.conf is no longer used, and the server will not start if the file exists. recovery.signal and standby.signal are now used to switch into non-primary mode. trigger_file has been renamed to promote_trigger_file. The standby_mode setting has been removed.

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

• Add partition introspection functions (Michaël Paquier, Álvaro Herrera, Amit Langote). 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.

• Inline calls to LEAST and GREATEST to improve optimization (Vik Fearing)

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

• Report progress of CREATE INDEX and REINDEX operations (Álvaro Herrera, Peter Eisentraut). Progress is reported in the pg_stat_progress_create_index system view

• Add REINDEX CONCURRENTLY to allow reindexing concurrently (Michaël Paquier, Andreas Karlsson, Peter Eisentraut). This is also controlled by the reindexdb –concurrently option.
• Add support for generated columns (Peter Eisentraut). The content of generated columns are computed from expressions (including references to other columns in the same table) rather than being specified by INSERTor UPDATE commands.
• Allow enumerated values to be added in more transaction types (Andrew Dunstan, Tom Lane, Thomas Munro). Previously, ALTER TYPE … ADD VALUE could only be called in a transaction that created the enumerated type. Now, the only restriction is that the new enumerated value cannot be referenced until the transaction commits.
• Allow the creation of collations that report string equality for strings that are not byte-wise equal (Peter Eisentraut). This feature supports “nondeterministic” collations that can define case- and accent-agnostic equality comparisons. Thus, for example, a case-insensitive uniqueness constraint on a text column can be made more easily than before. This is only supported for ICU collations.
• Add EXPLAIN option SETTINGS to output non-default optimizer settings (Tomas Vondra). This can also be output in auto-explain via auto_explain.log_settings.

• Add colorization to the output of command-line utilities (Peter Eisentraut). This is enabled by setting the environment variable PG_COLOR to always or auto. The specific colors used can be adjusted by setting the environment variable PG_COLORS, using ANSI escape codes for colors.

• Show the manual page URL in psql’s \help output (Peter Eisentraut)

• Allow pg_upgrade to use the file system cloning feature (Peter Eisentraut). The –clone option has the advantages of –link, while preventing the old cluster from being changed after the new cluster has started.

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

• Add CREATE ACCESS METHOD command to create new table types (Andres Freund, Haribabu Kommi, Álvaro Herrera, Alexander Korotkov, Dmitry Dolgov). This enables the development of new table access methods, which can optimize storage for different use-cases. The existing heap access method remains the default.

Note: The features listed above have been taken from the Release Notes for PostgreSQL 12 from www.postgresql.org available here.

The following 2ndQuadrant team members, in alphabetical order, have contributed to this release as patch authors, committers, reviewers, testers, or reporters of issues:

• Abhijit Menon-Sen
• Álvaro Herrera
• Andrew Dunstan
• Christoph Moench-Tegeder
• Craig Ringer
• David Rowley
• Gianni Ciolli
• Ian Barwick
• Jaime Casanova
• John Naylor
• Martín Marqués
• Pavan Deolasee
• Peter Eisentraut
• Rafael Castro
• Rahila Syed
• Simon Riggs
• Tomas Vondra
• Vik Fearing