Hash indexes must be rebuilt after pg_upgrade-ing from any previous major PostgreSQL version (Mithun Cy, Robert Haas, Amit Kapila) Major hash index improvements necessitated this requirement. pg_upgrade will create a script to assist with this.

Rename write-ahead log directory pg_xlog to pg_wal , and rename transaction status directory pg_clog to pg_xact (Michael Paquier) Users have occasionally thought that these directories contained only inessential log files, and proceeded to remove write-ahead log files or transaction status files manually, causing irrecoverable data loss. These name changes are intended to discourage such errors in future.

Rename SQL functions, tools, and options that reference “xlog” to “wal” (Robert Haas) For example, pg_switch_xlog() becomes pg_switch_wal() , pg_receivexlog becomes pg_receivewal, and --xlogdir becomes --waldir . This is for consistency with the change of the pg_xlog directory name; in general, the “xlog” terminology is no longer used in any user-facing places.

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 the implementation of set-returning functions appearing in a query's SELECT list (Andres Freund) Set-returning functions are now evaluated before evaluation of scalar expressions in the SELECT list, much as though they had been placed in a LATERAL FROM -clause item. This allows saner semantics for cases where multiple set-returning functions are present. If they return different numbers of rows, the shorter results are extended to match the longest result by adding nulls. Previously the results were cycled until they all terminated at the same time, producing a number of rows equal to the least common multiple of the functions' periods. In addition, set-returning functions are now disallowed within CASE and COALESCE constructs. For more information see Section 37.4.8.

Use standard row constructor syntax in UPDATE ... SET ( column_list ) = row_constructor (Tom Lane) The row_constructor can now begin with the keyword ROW ; previously that had to be omitted. If just one column name appears in the column_list , then the row_constructor now must use the ROW keyword, since otherwise it is not a valid row constructor but just a parenthesized expression. Also, an occurrence of table_name .* within the row_constructor is now expanded into multiple columns, as occurs in other uses of row_constructor s.

When ALTER TABLE ... ADD PRIMARY KEY marks columns NOT NULL , that change now propagates to inheritance child tables as well (Michael Paquier)

Prevent statement-level triggers from firing more than once per statement (Tom Lane) Cases involving writable CTEs updating the same table updated by the containing statement, or by another writable CTE, fired BEFORE STATEMENT or AFTER STATEMENT triggers more than once. Also, if there were statement-level triggers on a table affected by a foreign key enforcement action (such as ON DELETE CASCADE ), they could fire more than once per outer SQL statement. This is contrary to the SQL standard, so change it.

Move sequences' metadata fields into a new pg_sequence system catalog (Peter Eisentraut) 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. Also, sequences created for SERIAL columns now generate positive 32-bit wide values, whereas previous versions generated 64-bit wide values. This has no visible effect if the values are only stored in a column. The output of psql's \d command for a sequence has been redesigned, too.

Make pg_basebackup stream the WAL needed to restore the backup by default (Magnus Hagander) This changes pg_basebackup's -X / --wal-method default to stream . An option value none has been added to reproduce the old behavior. The pg_basebackup option -x has been removed (instead, use -X fetch ).

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

Change the default value of the log_directory server parameter from pg_log to log (Andreas Karlsson)

Add configuration option ssl_dh_params_file to specify file name for custom OpenSSL DH parameters (Heikki Linnakangas) This replaces the hardcoded, undocumented file name dh1024.pem . Note that dh1024.pem is no longer examined by default; you must set this option if you want to use custom DH parameters.

Increase the size of the default DH parameters used for OpenSSL ephemeral DH ciphers to 2048 bits (Heikki Linnakangas) The size of the compiled-in DH parameters has been increased from 1024 to 2048 bits, making DH key exchange more resistant to brute-force attacks. However, some old SSL implementations, notably some revisions of Java Runtime Environment version 6, will not accept DH parameters longer than 1024 bits, and hence will not be able to connect over SSL. If it's necessary to support such old clients, you can use custom 1024-bit DH parameters instead of the compiled-in defaults. See ssl_dh_params_file.

Remove the ability to store unencrypted passwords on the server (Heikki Linnakangas) The password_encryption server parameter no longer supports off or plain . The UNENCRYPTED option is no longer supported in CREATE/ALTER USER ... PASSWORD . Similarly, the --unencrypted option has been removed from createuser. Unencrypted passwords migrated from older versions will be stored encrypted in this release. The default setting for password_encryption is still md5 .

Add min_parallel_table_scan_size and min_parallel_index_scan_size server parameters to control parallel queries (Amit Kapila, Robert Haas) These replace min_parallel_relation_size , which was found to be too generic.

Don't downcase unquoted text within shared_preload_libraries and related server parameters (QL Zhuo) These settings are really lists of file names, but they were previously treated as lists of SQL identifiers, which have different parsing rules.

Remove sql_inheritance server parameter (Robert Haas) Changing this setting from the default value caused queries referencing parent tables to not include child tables. The SQL standard requires them to be included, however, and this has been the default since PostgreSQL 7.1.

Allow multi-dimensional arrays to be passed into PL/Python functions, and returned as nested Python lists (Alexey Grishchenko, Dave Cramer, Heikki Linnakangas) This feature requires a backwards-incompatible change to the handling of arrays of composite types in PL/Python. Previously, you could return an array of composite values by writing, e.g., [[col1, col2], [col1, col2]] ; but now that is interpreted as a two-dimensional array. Composite types in arrays must now be written as Python tuples, not lists, to resolve the ambiguity; that is, write [(col1, col2), (col1, col2)] instead.

Remove PL/Tcl's “module” auto-loading facility (Tom Lane) This functionality has been replaced by new server parameters pltcl.start_proc and pltclu.start_proc, which are easier to use and more similar to features available in other PLs.

Remove pg_dump/pg_dumpall support for dumping from pre-8.0 servers (Tom Lane) Users needing to dump from pre-8.0 servers will need to use dump programs from PostgreSQL 9.6 or earlier. The resulting output should still load successfully into newer servers.

Remove support for floating-point timestamps and intervals (Tom Lane) This removes configure's --disable-integer-datetimes option. Floating-point timestamps have few advantages and have not been the default since PostgreSQL 8.3.

Remove server support for client/server protocol version 1.0 (Tom Lane) This protocol hasn't had client support since PostgreSQL 6.3.

Remove contrib/tsearch2 module (Robert Haas) This module provided compatibility with the version of full text search that shipped in pre-8.3 PostgreSQL releases.

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.