Contributions to PostgreSQL 10

New Features

Logical Replication – Petr Jelínek

  • Add the ability to logically replicate tables to standby servers.  Logical replication allows more flexibility than physical replication does, including replication between different major versions of PostgreSQL and selective-table replication.
  • Allow creation of temporary replication slots.  Temporary slots are automatically removed on session exit or error.

Optimization using multi-column statistics – Tomas Vondra, David Rowley, Álvaro Herrera

  • Add multi-column optimizer statistics to compute the correlation ratio and number of distinct values. New commands are CREATE STATISTICSALTER STATISTICS, and DROP STATISTICS. This feature is helpful in estimating query memory usage and when combining the statistics from individual columns.

Major Enhancements

IDENTITY columns and Sequence improvements – Peter Eisentraut

  • Move sequences’ metadata fields into a new pg_sequence system catalog.  A sequence relation now stores only the fields that can be modified by nextval(), that is last_value, log_cnt, and is_called. Other sequence properties, such as the starting value and increment, are kept in a corresponding row of the pg_sequence catalog. ALTER SEQUENCE updates are now fully transactional, implying that the sequence is locked until commit. The nextval() and setval() functions remain nontransactional. The main incompatibility introduced by this change is that selecting from a sequence relation now returns only the three fields named above. To obtain the sequence’s other properties, applications must look into pg_sequence. The new system view pg_sequences can also be used for this purpose; it provides column names that are more compatible with existing code.
  • Add identity columns for assigning a numeric value to columns on insert. These are similar to SERIAL columns, but are SQL standard compliant.
  • Add CREATE SEQUENCE AS command to create a sequence matching an integer data type. This simplifies the creation of sequences matching the range of base columns.

ICU support for better national language support – Peter Eisentraut

  • Allow PL/Python’s cursor() and execute() functions to be called as methods of their plan-object arguments.This allows a more object-oriented programming style.
  • Allow the ICU library to optionally be used for collation support. The ICU library has versioning that allows detection of collation changes between versions. It is enabled via configure option --with-icu. The default still uses the operating system’s native collation library.

BRIN index improvements – Álvaro Herrera

  • Add option to allow BRIN index summarization to happen more aggressively. Specifically, a new CREATE INDEXoption allows auto-summarization of the previous BRIN page range when a new page range is created
  • Add functions to remove and re-add BRIN summarization for BRIN index ranges. The new SQL function brin_summarize_range() updates BRIN index summarization for a specified range and brin_desummarize_range() removes it. This is helpful to update summarization of a range that is now smaller due to UPDATEs and DELETEs.

Reduced locking for changing column parameters – Simon Riggs, Fabrízio Mello

  • Reduce locking required to change table parameters. For example, changing a table’s effective_io_concurrency setting can now be done with a more lightweight lock.

Improvements in JSONB and XML handling – Álvaro Herrera, Andrew Dunstan, Pavel Stehule

  • Add XMLTABLE function that converts XML-formatted data into a row set.
  • Properly treat array pseudotypes (anyarray) as arrays in to_json() and to_jsonb().  Previously columns declared as anyarray (particularly those in the pg_stats view) were converted to JSON strings rather than arrays.

Improvements to Money datatypes –  Peter Eisentraut

  • Check for overflow in the money type’s input function
  • Add operators for multiplication and division of money values with int8 values. Previously such cases would result in converting the int8 values to float8 and then using the money-and-float8 operators. The new behavior avoids possible precision loss. But note that division of money by int8 now truncates the quotient, like other integer-division cases, while the previous behavior would have rounded.

Improvements to server startup/shutdown (pg_ctl) – Peter Eisentraut

  • Add wait option for pg_ctl‘s promote operation
  • Add long option for pg_ctl server options (--options)
  • Ensure that pg_ctl exits with nonzero status if an operation being waited for does not complete within the timeout. The start and promote operations now return exit status 1, not 0, in such cases. The stop operation has always done that.

Allow parallel backup with pg_dump from a standby server – Petr Jelínek

  • Support using synchronized snapshots when dumping from a standby server

VACUUM speed enhancements – Claudio Freire, Álvaro Herrera

  • Improve speed of VACUUM’s removal of trailing empty heap pages

Replication speed enhancements – Simon Riggs, David Rowley

  • Improve performance of hot standby replay with better tracking of Access Exclusive locks

New test infrastructure for Replication & Recovery – Craig Ringer

  • Add pg_recvlogical option --endpos to specify the ending position. This complements the existing --startpos option.

Other Contributions

  • Rename WAL-related functions and views to use lsn instead of location – David Rowley. There was previously an inconsistent mixture of the two terminologies.
  • Change how logical replication uses pg_hba.conf – Peter Eisentraut. In previous releases, a logical replication connection required the replication keyword in the database column. As of this release, logical replication matches a normal entry with a database name or keywords such as all. Physical replication continues to use the replication keyword. Since built-in logical replication is new in this release, this change only affects users of third-party logical replication plugins.
  • Make all pg_ctl actions wait for completion by default – Peter Eisentraut. Previously some pg_ctl actions didn’t wait for completion, and required the use of -w to do
  • Remove createlang and droplang command-line applications – Peter Eisentraut. These had been deprecated since PostgreSQL 9.1. Instead, use CREATE EXTENSION and DROP EXTENSION directly.
  • Improve accuracy in determining if a BRIN index scan is beneficial – David Rowley /Emre Hasegeli
  • Use uniqueness guarantees to optimize certain join types – David Rowley
  • Speed up two-phase commit recovery performance – Stas Kelvich / Nikhil Sontakke / Michael Paquier
  • Allow the specification of a function name without arguments in DDL commands, if it is unique – Peter Eisentraut. For example, allow DROP FUNCTION on a function name without arguments if there is only one function with that name. This behavior is required by the SQL standard.
  • Allow multiple functions, operators, and aggregates to be dropped with a single DROP command – Peter Eisentraut
  • Support IF NOT EXISTS in CREATE SERVER, CREATE USER MAPPING, and CREATE COLLATION – Anastasia Lubennikova / Peter Eisentraut
  • Make VACUUM VERBOSE report the number of skipped frozen pages and oldest xmin – Masahiko Sawada / Simon Riggs. This information is also included in log_autovacuum_min_duration output.
  • Add function txid_current_ifassigned() to return the current transaction ID or NULL if no transaction ID has been assigned – Craig Ringer. This is different from txid_current(), which always returns a transaction ID, assigning one if necessary. Unlike that function, this function can be run on standby servers.
  • Add function txid_status() to check if a transaction was committed – Craig Ringer. This is useful for checking after an abrupt disconnection whether your previous transaction committed and you just didn’t receive the acknowledgement.
  • Allow make_date() to interpret negative years as BC years – Álvaro Herrera
  • Improve psql’s d (display relation) and dD (display domain) commands to show collation, nullable, and default properties in separate columns – Peter Eisentraut. Previously they were shown in a single “Modifiers” column.
  • Improve psql’s tab completion – Jeff Janes / Ian Barwick / Andreas Karlsson / Sehrope Sarkuni / Thomas Munro / Kevin Grittner / Dagfinn Ilmari Mannsåker
  • Rename initdb options --noclean and --nosync to be spelled --no-clean and --no-sync – Vik Fearing / Peter Eisentraut. The old spellings are still supported.
  • Add pg_dumpall option --no-role-passwords to omit role passwords – Robins Tharakan / Simon Riggs. This allows use of pg_dumpall by non-superusers; without this option, it fails due to inability to read passwords.
  • Change to two-part release version numbering – Peter Eisentraut / Tom Lane. Release numbers will now have two parts (e.g., 10.1) rather than three (e.g., 9.6.3). Major versions will now increase just the first number, and minor releases will increase just the second number. Release branches will be referred to by single numbers (e.g., 10 rather than 9.6). This change is intended to reduce user confusion about what is a major or minor release of PostgreSQL.
  • Add slab-like memory allocator for efficient fixed-size allocations – Tomas VondraUse XSLT to build the PostgreSQL documentation – Peter Eisentraut. Previously Jade, DSSSL, and JadeTex were used.
  • Build HTML documentation using XSLT stylesheets by default – Peter Eisentraut
  • In postgres_fdw, push joins to the remote server in more cases – David Rowley / Ashutosh Bapat / Etsuro Fujita
  • Allow btree_gist and btree_gin to index enum types – Andrew Dunstan. This allows enums to be used in exclusion constraints.
  • Add pageinspect function page_checksum() to show a page’s checksum – Tomas Vondra
  • Add pageinspect function bt_page_items() to print page items from a page image – Tomas Vondra

Our Team

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

  • Álvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • David Rowley
  • Gabriele Bartolini
  • Gianni Ciolli
  • Ian Barwick
  • Jaime Casanova
  • Leonardo Cecchi
  • Martín Marqués
  • Nikhil Sontakke
  • Pavan Deolasee
  • Peter Eisentraut
  • Petr Jelínek
  • Simon Riggs
  • Tomas Vondra
  • Vik Fearing