Contributions to PostgreSQL 9.6

Main Enhancements

Parallel execution of sequential scans, joins and aggregates – Robert Haas, Amit Kapila, David Rowley, many others

  • Parallel queries. With 9.6, PostgreSQL introduces initial support for parallel execution of large queries. Only strictly read-only queries where the driving table is accessed via a sequential scan can be parallelized. Hash joins and nested loops can be performed in parallel, as can aggregation (for supported aggregates). Much remains to be done, but this is already a useful set of features. Parallel query execution is not (yet) enabled by default. To allow it, set the new configuration parameter max_parallel_workers_per_gather to a value larger than zero. Additional control over use of parallelism is available through other new configuration parameters force_parallel_mode, parallel_setup_cost, parallel_tuple_cost, and min_parallel_relation_size.

Reduce interlocking on stanby servers – Simon Riggs

  • Reduce interlocking on standby servers during the replay of btree index vacuuming operations. This change avoids substantial replication delays that sometimes occurred while replaying such operations.

Other Contributions

  • Improve pg_restore's -t option to match all types of relations, not only plain tables – Craig Ringer
  • Ignore GROUP BY columns that are functionally dependent on other columns – David Rowley. If a GROUP BYclause includes all columns of a non-deferred primary key, as well as other columns of the same table, those other columns are redundant and can be dropped from the grouping. This saves computation in many common cases.
  • Allow use of an index-only scan on a partial index when the index’s WHERE clause references columns that are not indexed – Tomas Vondra / Kyotaro Horiguchi. For example, an index defined by CREATE INDEXtidx_partial ON t(b) WHERE a > 0 can now be used for an index-only scan by a query that specifies WHERE a > 0 and does not otherwise use a. Previously this was disallowed because a is not listed as an index column.
  • Improve aggregate-function performance by sharing calculations across multiple aggregates if they have the same arguments and transition functions – David Rowley. For example, SELECT AVG(x), VARIANCE(x) FROMtab can use a single per-row computation for both aggregates.
  • Improve speed of the output functions for timestamp, time, and date data types  – David Rowley / Andres Freund
  • Add function pg_current_xlog_flush_location() to expose the current transaction log flush location – Tomas Vondra
  • Raise the maximum allowed value of checkpoint_timeout to 24 hours – Simon Riggs / Julien Rouhaud. Allow effective_io_concurrency to be set per-tablespace to support cases where different tablespaces have different I/O characteristics
  • Merge the archive and hot_standby values of the wal_level configuration parameter into a single new value replica – Peter Eisentraut. Making a distinction between these settings is no longer useful, and merging them is a step towards a planned future simplification of replication setup. The old names are still accepted but are converted to replica internally.
  • Add a --slot option to pg_basebackup – Peter Eisentraut. This lets pg_basebackup use a replication slot defined for WAL streaming. After the base backup completes, selecting the same slot for regular streaming replication allows seamless startup of the new standby server.
  • Introduce ALTER object DEPENDS ON EXTENSION – Abhijit Menon-Sen. This command allows a database object to be marked as depending on an extension, so that it will be dropped automatically if the extension is dropped (without needing CASCADE). However, the object is not part of the extension, and thus will be dumped separately by pg_dump.
  • Reduce the lock strength needed by ALTER TABLE when setting fillfactor and autovacuum-related relation options – Fabrízio de Royes Mello, Simon Riggs
  • Introduce CREATE ACCESS METHOD to allow extensions to create index access methods – Alexander Korotkov, Petr Jelínek
  • Add a CASCADE option to CREATE EXTENSION to automatically create any extensions the requested one depends on – Petr Jelínek
  • Disallow creation of indexes on system columns, except for OID columns – David Rowley. Such indexes were never considered supported, and would very possibly misbehave since the system might change the system-column fields of a tuple without updating indexes. However, previously there were no error checks to prevent them from being created.
  • Allow pg_dump to dump non-extension-owned objects that are within an extension-owned schema – Martín Marqués. Previously such objects were ignored because they were mistakenly assumed to belong to the extension owning their schema.
  • Improve TAP testing infrastructure – Michael Paquier, Craig Ringer, Álvaro Herrera, Stephen Frost. Notably, it is now possible to test recovery scenarios using this infrastructure.
  • Support partial aggregation – David Rowley, Simon Riggs. This change allows the computation of an aggregate function to be split into separate parts, for example so that parallel worker processes can cooperate on computing an aggregate. In future it might allow aggregation across local and remote data to occur partially on the remote end.Add a generic interface for writing WAL records – Alexander Korotkov, Petr Jelínek, Markus Nullmeier. This change allows extensions to write WAL records for changes to pages using a standard layout. The problem of needing to replay WAL without access to the extension is solved by having generic replay code. This allows extensions to implement, for example, index access methods and have WAL support for them.Support generic WAL messages for logical decoding – Petr Jelínek, Andres Freund.  This feature allows extensions to insert data into the WAL stream that can be read by logical-decoding plugins, but is not connected to physical data restoration.

*The features listed above have been taken from the Release Notes for PostgreSQL 9.6 from www.postgresql.orgavailable here.