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

  • Remove PL/pgSQL's "feature" that suppressed the innermost line of CONTEXT for messages emitted by RAISE commands - Pavel Stehule. This ancient backwards-compatibility hack was agreed to have outlived its usefulness.

  • psql's -c option no longer implies --no-psqlrc- Pavel Stehule / Catalin Iacob.  Write --no-psqlrc (or its abbreviation -X) explicitly to obtain the old behavior. Scripts so modified will still work with old versions of psql.

  • 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 BY clause 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 INDEX tidx_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) FROM tab 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.

  • Add function parse_ident() to split a qualified, possibly quoted SQL identifier into its parts - Pavel Stehule

  • Add pg_size_bytes() function to convert human-readable size strings to numbers - Pavel Stehule, Vitaly Burovoy, Dean Rasheed. This function converts strings like those produced by pg_size_pretty() into bytes. An example usage is SELECT oid::regclass FROM pg_class WHERE pg_total_relation_size(oid) > pg_size_bytes('10 GB').

  • Extend PL/Python's error-reporting and message-reporting functions to allow specifying additional message fields besides the primary error message - Pavel Stehule

  • Introduce a feature in libpq whereby the CONTEXT field of messages can be suppressed, either always or only for non-error messages - Pavel Stehule. The default behavior of PQerrorMessage() is now to print CONTEXT only for errors. The new function PQsetErrorContextVisibility() can be used to adjust this.

  • Add a --strict-names option to pg_dump and pg_restore - Pavel Stehule. This option causes the program to complain if there is no match for a -t or -n option, rather than silently doing nothing.

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

  • Support multiple -c and -f command-line options - Pavel Stehule, Catalin Iacob. The specified operations are carried out in the order in which the options are given, and then psql terminates.

  • Introduce a feature whereby the CONTEXT field of messages can be suppressed, either always or only for non-error messages - Pavel Stehule. Printing CONTEXT only for errors is now the default behavior. This can be changed by setting the special variable SHOW_CONTEXT.

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

Stay in touch with us

Subscribe to our monthly newsletter to hear the latest developments from 2ndQuadrant and related technologies.

We’ll also send you any important news or updates that we think you’ll find useful.

We value your privacy and will not pass your details on to anyone else.