1. PostgreSQL 9.5-11 Highlights.md

PostgreSQL changes from 9.5 to 11

Full list of changes: https://why-upgrade.depesz.com/show?from=9.5.21&to=11.7

Highlights

• Parallel queries
• Declarative partitioning
• Covering indexes
• Improved performance

PostgreSQL 9.6

Release notes: https://www.postgresql.org/docs/9.6/release.html

Highlights

• Parallel execution of sequential scans, joins and aggregates
• Substantial performance improvements
• Improved observability
  • pg_stat_activity now includes information about what a process is waiting for.
  • Add pg_stat_progress_vacuum system view to provide progress reporting for VACUUM operations
  • Add pg_control_system(), pg_control_checkpoint(), pg_control_recovery(), and pg_control_init() functions to expose fields of pg_control to SQL
  • Add pg_config system view
  • Add pg_stat_wal_receiver system view to provide information about the state of a hot-standby server's WAL receiver process
  • Add pg_blocking_pids() function to reliably identify which sessions block which others

Breaking changes

• Remove the long-deprecated CREATEUSER/NOCREATEUSER options from CREATE ROLE and allied commands

• psql's -c option no longer implies --no-psqlrc

  Write --no-psqlrc (or its abbreviation -X) explicitly to obtain the old behavior. Scripts so modified will still work with old versions of psql.

Server configuration

• Allow sessions to be terminated automatically if they are in idle-in-transaction state for too long

  This behavior is controlled by the new configuration parameter [idle_in_transaction_session_timeout](https://www.postgresql.org/docs/9.6/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT). It can be useful to prevent forgotten transactions from holding locks or preventing vacuum cleanup for too long.

  TODO: enable

• Merge the archive and hot_standby values of the [wal_level](https://www.postgresql.org/docs/9.6/runtime-config-wal.html#GUC-WAL-LEVEL) configuration parameter into a single new value replica

  TODO: Change in config

General performance

• Allow use of an index-only scan on a partial index when the index's WHERE clause references columns that are not indexed.

  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.

  For example, SELECT AVG(x), VARIANCE(x) FROM tab can use a single per-row computation for both aggregates.

Replication and recovery

• Allow synchronous replication to support multiple simultaneous synchronous standby servers, not just one.

• Add a --slot option to pg_basebackup.

  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.

Data types

• Allow omitting one or both boundaries in an array slice specifier, e.g. array_col[3:]
• Implement look-behind constraints in regular expressions

Functions

• Add jsonb_insert() function to insert a new element into a jsonb array, or a not-previously-existing key into a jsonb object

• Add variadic functions num_nulls() and num_nonnulls() that count the number of their arguments that are null or non-null

  An example usage is CHECK(num_nonnulls(a,b,c) = 1) which asserts that exactly one of a,b,c is not NULL. These functions can also be used to count the number of null or nonnull elements in an array.

• Add pg_size_bytes() function to convert human-readable size strings to numbers

psql

• Support multiple -c and -f command-line options.

  The specified operations are carried out in the order in which the options are given, and then psql terminates.

Additional modules

• Add configuration parameter auto_explain.sample_rate to allow contrib/auto_explain to capture just a configurable fraction of all queries.

  This allows reduction of overhead for heavy query traffic, while still getting useful information on average.

• Add support for "word similarity" to contrib/pg_trgm

  These functions and operators measure the similarity between one string and the most similar single word of another string.

• Add contrib/pg_visibility module to allow examining table visibility maps

PostgreSQL 10

Release notes: https://www.postgresql.org/docs/10/release.html

Highlights

• Logical replication publish/subscribe
• Declarative table partitioning
• Improved performance
• Improved monitoring and control

Breaking changes

• Change the implementation of set-returning functions appearing in a query's SELECT list

  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.

• Rename write-ahead log directory pg_xlog to pg_wal, and rename transaction status directory pg_clog to pg_xact

• Rename SQL functions, tools, and options that reference “xlog” to “wal”

  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

• Move sequences' metadata fields into a new pg_sequence system catalog

  TODO: Monitor auto-increment integer primary keys

Parallel queries

• Enable parallelism by default by changing the default setting of max_parallel_workers_per_gather to 2

Optimizer

• Add multi-column optimizer statistics to compute the correlation ratio and number of distinct values.

  New commands are CREATE STATISTICS, ALTER STATISTICS, and DROP STATISTICS. This feature is helpful in estimating query memory usage and when combining the statistics from individual columns.

Monitoring

• Add default monitoring roles

  New roles pg_monitor, pg_read_all_settings, pg_read_all_stats, and pg_stat_scan_tables allow simplified permission configuration.

  See: https://www.postgresql.org/docs/11/default-roles.html

  TODO: Datadog agent privileges

• Add functions to return the log and WAL directory contents

  The new functions are pg_ls_logdir() and pg_ls_waldir() and can be executed by non-superusers with the proper permissions.

• Add function pg_current_logfile() to read logging collector's current stderr and csvlog output file names

Replication and recovery

• New configuration defaults:

  • wal_level = replica
  • max_wal_senders = 10
  • max_replication_slots = 10
  • hot_standby = on

• Add columns to pg_stat_replication to report replication delay times

  The new columns are write_lag, flush_lag, and replay_lag.

psql

• Add conditional branch support to psql

  This feature adds psql meta-commands \if, \elif, \else, and \endif. This is primarily helpful for scripting.

PostgreSQL 11

Release notes: https://www.postgresql.org/docs/11/release-11.html

Highlights

• SQL stored procedures that support embedded transactions
• Optional Just-in-Time (JIT) compilation for some SQL code, speeding evaluation of expressions
• Covering indexes can now be created, using the INCLUDE clause of CREATE INDEX
• Ability to avoid a table rewrite for ALTER TABLE ... ADD COLUMN with a non-null column default
• Allow btree_gin to index bool, bpchar, name and uuid data types

Partitioning

• Allow aggregate functions on partitioned tables to be evaluated separately for each partition, subsequently merging the results.

  TODO: This feature is disabled by default but can be enabled by changing enable_partitionwise_aggregate

Data Types

• Add casts from JSONB scalars to numeric and boolean data types

psql

• Add psql command \gdesc to display the names and types of the columns in a query result

2. PostgreSQL 12 Highlights.md

PostgreSQL 12 Changes

Release notes: https://www.postgresql.org/docs/12/release-12.html Full list of changes: https://why-upgrade.depesz.com/show?from=11.7&to=12.2&keywords=

Highlights

• Support for the SQL/JSON path language
• Automatic (but overridable) inlining of common table expressions (CTEs)
• Support for covering GiST indexes, via the INCLUDE clause
• Multi-column most-common-value (MCV) statistics can be defined via CREATE STATISTICS, to support better plans for queries that test several non-uniformly-distributed columns
• REINDEX CONCURRENTLY can rebuild an index without blocking writes to its table
• Stored generated columns

Breaking changes

• Move recovery.conf settings into postgresql.conf.

  recovery.conf is no longer used, and the server will not start if that file exists. recovery.signal and standby.signal files are now used to switch into non-primary mode. The trigger_file setting has been renamed to promote_trigger_file. The standby_mode setting has been removed.

• Cause recovery to advance to the latest timeline by default.

  Specifically, recovery_target_timeline now defaults to latest. Previously, it defaulted to current.

• In new btree indexes, the maximum index entry length is reduced by eight bytes, to improve handling of duplicate entries.

  This means that a REINDEX operation on an index pg_upgrade'd from a previous release could potentially fail.

Partitioning

• Allow foreign keys to reference partitioned tables.

• Allow partition bounds to be any expression.

  Such expressions are evaluated at partitioned-table creation time. Previously, only simple constants were allowed as partition bounds.

Indexes

• Improve performance and space utilization of btree indexes with many duplicates.

  Previously, duplicate index entries were stored unordered within their duplicate groups. This caused overhead during index inserts, wasted space due to excessive page splits, and it reduced VACUUM's ability to recycle entire pages. Duplicate index entries are now sorted in heap-storage order.

  Indexes pg_upgrade'd from previous releases will not have these benefits.

• Allow multi-column btree indexes to be smaller.

  Internal pages and min/max leaf page indicators now only store index keys until the change key, rather than all indexed keys. This also improves the locality of index access.

  Indexes pg_upgrade'd from previous releases will not have these benefits.

Optimizer

• Allow CREATE STATISTICS to create most-common-value statistics for multiple columns.

  This improves optimization for queries that test several columns, requiring an estimate of the combined effect of several WHERE clauses. If the columns are correlated and have non-uniform distributions then multi-column statistics will allow much better estimates.

• Allow common table expressions (CTEs) to be inlined into the outer query.

  Specifically, CTEs are automatically inlined if they have no side-effects, are not recursive, and are referenced only once in the query. Inlining can be prevented by specifying MATERIALIZED, or forced for multiply-referenced CTEs by specifying NOT MATERIALIZED. Previously, CTEs were never inlined and were always evaluated before the rest of the query.

Monitoring

• Allow logging of statements from only a percentage of transactions.

  The parameter log_transaction_sample_rate controls this.

• Add progress reporting to CREATE INDEX and REINDEX operations.

  Progress is reported in the pg_stat_progress_create_index system view.