Contributions to PostgreSQL 11

Partitioning  – David Rowley, Rahila Syed, Álvaro Herrera.

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

Parallel Queries – David Rowley

• Improve performance of sequential scans with many parallel workers

Indexes – Pavan Deolasee

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

Monitoring – Peter Eisentraut

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

• Add bgw_type to the background worker C structure. This is displayed to the user in pg_stat_activity.backend_type and ps output.

• Add information_schema columns related to table constraints and triggers. Specifically, table_constraints.enforced, triggers.action_order, triggers.action_reference_old_table, and triggers.action_reference_new_table.

Server Configuration – Peter Eisentraut, Simon Riggs

• 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 the SSL configuration should be reloaded and ssl_passphrase_commandcalled 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.

Write-Ahead Log (WAL) – Simon Riggs

• No longer retain WAL that spans two checkpoints. The retention of WAL records for only one checkpoint is required.

Simon Riggs, Marco Nenciarini, Peter eisentraut, Nikhil Sontakke, Petr Jelinek

• Replicate TRUNCATE activity when using logical replication – Simon Riggs / Marco Nenciarini / Peter Eisentraut

• Pass prepared transaction information to logical replication subscribers – Nikhil Sontakke / Stas Kelvich

• Allow replication slots to be advanced programmatically, rather than be consumed by subscribers – Petr Jelínek. This allows efficient advancement replication slots when the contents do not need to be consumed. This is performed by pg_replication_slot_advance().

Andrew Dunstan

• Allow ALTER TABLE to add a column with a non-null default without a table rewrite – Andrew Dunstan / Serge Rielau

Peter Eisentraut, Andrew Dunstan

• Add SHA-2 family of hash functions – Peter Eisentraut. Specifically, sha224(), sha256(), sha384(), sha512() were added.

• Allow to_char() and to_timestamp() to specify the time zone’s hours and minutes from UTC – Nikita Glukhov / Andrew Dunstan. This is done with format specifications TZH and TZM.

Peter Eisentraut

• Add SQL procedures, which can start and commit their own transactions. They are created with the new CREATE PROCEDURE command and invoked via CALL. The new ALTER/DROP ROUTINE commands allows altering/dropping of procedures, functions, and aggregates.

• Add transaction control to PL/pgSQL, PL/Perl, PL/Python, PL/Tcl, and SPI server-side languages. Transaction control is only available to top-transaction-level CALLs or in nested PL/pgSQL DO and CALL blocks that only contain other PL/pgSQL DO and CALLblocks.

Peter Eisentraut.

• Change libpq to disable compression by default. Compression is already disabled in modern OpenSSL versions and the libpq setting had no effect in that case.

Peter Eisentraut.

• Add long options to pg_resetwal and pg_controldata – Nathan Bossart / Peter Eisentraut

Peter Eisentraut, Tomas Vondra.

• Convert documentation to DocBook XML – Peter Eisentraut / Alexander Lakhin / Jürgen Purtz. The file names still use an sgml extension for compatibility with back branches.

• Use stdbool.h to define type bool on platforms where it’s suitable, which is most – Peter Eisentraut. This eliminates a coding hazard for extension modules that need to include stdbool.h.

• Add a generational memory allocator which is optimized for serial allocation/deallocation – Tomas Vondra. This reduces memory usage for logical decoding.

• Make the computation of system column pg_class.reltuples consistent – Tomas Vondra

• Update to use perltidy version 20170521 – Tom Lane / Peter Eisentraut

Peter Eisentraut.

• Remove extension chkpass. This extension no longer served as a usable security tool or example of how to write an extension.

*The features listed above have been taken from the Release Notes for PostgreSQL 10 from www.postgresql.org available here .[LINK].

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
Craig Ringer
David Rowley
Gianni Ciolli
Jaime Casanova
Leonardo Cecchi
Marco Nenciarini
Martín Marqués
Nikhil Sontakke
Pavan Deolasee
Peter Eisentraut
Petr Jelínek
Rahila Syed
Simon Riggs
Tomas Vondra
Vik Fearing