NewIn96

= What's New in PostgreSQL 9.6 =
This page is a work in progress which will include details of PostgreSQL 9.6 features and changes. Many thanks to Thom Brown for assembling the original list.
== Parallel Query ==
=== Parallel sequential scans ===
PostgreSQL can now execute a full table scan in multiple parallel processes, up to the limits set by the user.
Links: 
* Generate parallel sequential scan plans in simple cases. 
* Waiting for 9.6 – Generate parallel sequential scan plans in simple cases.
* pgCon and PostgreSQL 9.6
=== parallel joins ===
Links: 
* Support parallel joins, and make related improvements. 
* Waiting for 9.6 – Support parallel joins, and make related improvements.
=== parallel aggregates ===
Parallel Aggregates allow aggregate functions to be processed concurrently by multiple worker processes. This can significantly increase the response times for queries aggregating large amounts of tuples down to just a few aggregate groups, providing that the server has enough otherwise idle CPUs and would otherwise be bounded by the single CPU which the backend process is running on. Each worker process operates on a subset of tuples and creates a partially aggregated result to which it returns to the main backend process where each partial result will be combined with ones from the other worker processes and the final aggregate result produced.
Most internal aggregate functions support parallel mode. User defined aggregates will need to be changed to add a combine function, and possible a serialization and deserialization in the case of aggregate functions which return an INTERNAL state.
Links: 
* Support parallel aggregation. 
* Supported built-in aggregate functions. 
* Waiting for 9.6 – Support parallel aggregation.
== postgres_fdw ==
=== Sort pushdown ===
Links: 
* postgres_fdw: postgres_fdw: Consider requesting sorted data so we can do a merge join. 
=== join pushdown ===
Links: 
* postgres_fdw: Push down joins to remote servers. 
=== DML (UPDATE/DELETE) pushdown ===
Links: 
* Directly modify foreign tables. 
* Waiting for 9.6 – Directly modify foreign tables.
=== Operator and function pushdown ===
Links:
* Operator and function pushdown with postgres_fdw
== Replication ==
=== New remote_apply replication mode which waits for confirmation that a standby has applied changes ===
Links: 
* Add new replication mode synchronous_commit = 'remote_apply'. 
* Postgres 9.6 highlight - remote_apply
=== Support for multiple synchronous standbys ===
Links: 
* Support multiple synchronous standby servers. 
* Postgres 9.6 highlight - multi-sync standbys
=== pg_stat_wal_receiver ===
Links:
* WAL receiver status view
=== Replication slots can allocate WAL at creation ===
Links:
* Postgres 9.6 feature highlight - Replication slot improvements
== Text Search ==
=== Phrase full text search ===
Links: 
* Phrase full text search. 
* pgCon and PostgreSQL 9.6
=== Editable tsvector fields ===
Tsvector fields can now be modified in detail to fine-tune searchability.
Links:
* https://www.depesz.com/2016/03/21/waiting-for-9-6-tsvector-editing-functions/
== Transactions, VACUUM and the Visibility Map ==
=== pg_visibility extension for examining visibility maps ===
Links: 
* Add pg_visibility contrib module. 
* Postgres 9.6 highlight - pg_visibility
=== Frozen page data in visibility map for skipping vacuum on already-frozen data ===
Links: 
* Don't vacuum all-frozen pages. 
=== User-defined expiration of snapshots to control table bloat ===
Until now a long-running report or cursor displaying query results could block cleanup of dead rows, bloating all volatile tables in the database. The only options were to allow the bloat, suspend normal updates to other tables, or kill the long-running activity -- which might be completely unrelated to the bloating tables. Accumulation of bloat could cause performance problems and excessive use of storage space.
A new configuration option called old_snapshot_threshold allows the cluster to be configured to allow cleanup of a dead row when the transaction which updated or deleted it (causing it to be dead) has completed and all snapshots which can still see it have reached a certain age, without immediately terminating activities which are using such snapshots. If, for example, a large monthly report is running off of data in table etl_monthly (which is not currently changing), bloat in other tables will be limited, and the report can run to completion without error. If a snapshot has passed the threshold and a query uses that snapshot to attempt to read data from a page modified recently enough that it might not produce accurate results, then a "snapshot too old" error will be thrown. Normally the goal in configuring old_snapshot_threshold is to set it high enough that such an error is rarely seen, while preventing problems caused by bloat.
Links: 
* Add the "snapshot too old" feature 
== psql ==
=== \ev and \sv commands to edit views ===
Links:
* Waiting for 9.6 – Add psql \ev and \sv commands for editing and showing view definitions.
=== Prompt variable to show PId of backend ===
Links:
* Waiting for 9.6 – Add psql PROMPT variable showing the pid of the connected to backend.
=== Multiple -c and -f options ===
* support for multiple -c and -f options
=== Crosstabs in psql ===
\crosstabview is a completely different way to display results from a
query: instead of a vertical display of rows, the data values are placed
in a grid where the column and row headers come from the data itself,
similar to a spreadsheet.
* Waiting for 9.6: Crosstabs in psql
== SQL commands ==
=== ALTER TABLE ADD COLUMN IF NOT EXISTS ===
Links:
* Waiting for 9.6 – Add IF NOT EXISTS processing to ALTER TABLE ADD COLUMN
=== ALTER TABLE SET and its locks ===
Links:
* Lock reductions for ALTER TABLE SET
=== COPY and DML statements (CTEs) ===
Links:
* COPY and DML statements
== Performance and Monitoring ==
=== Detailed wait information in pg_stat_activity ===
Links: 
* Provide much better wait information in pg_stat_activity. 
* Postgres 9.6 highlight - Tracking of wait events
=== Index-only scans for partial indexes ===
Links: 
* Support using index-only scans with partial indexes in more case 
=== Performance improvements for external sort operations ===
Links:
* Use quicksort, not replacement selection, for external sorting 
* Improve memory management for external sorts 
== System Views and Administration ==
=== New system view pg_config ===
* Waiting for 9.6 – Add new system view, pg_config
=== pg_blocking_pids ===
Links:
* Postgres 9.6 highlight - pg_blocking_pids
=== Functions pg_get_* to return NULL on invalid objects ===
Up to 9.6, such functions could have easily bumped on "cache lookup" errors.
Links:
* Upcoming changes for pg_get functions on invalid objects
=== pg_notification_queue_usage to look at notify queue ===
Links:
 
* Waiting for 9.6 – Add new function pg_notification_queue_usage.
== Backups ==
=== pg_basebackup extended with replication slots ===
Links:
* Postgres 9.6 feature highlight - pg_basebackup and replication slots
=== New API for hot physical backups ===
Prior to PostgreSQL 9.6, the only way to perform concurrent physical backups was through pg_basebackup, via the streaming replication protocol. Low-level file system copy was only available in an exclusive mode, by calling pg_start_backup(), initiating the copy of data files, then finally calling pg_stop_backup().
The limitation of taking one backup at a time has been overcome by a new native API which redefines the pg_start_backup() method and adds a variant to pg_stop_backup(). For back-compatibility, the older signatures have been kept.
The new pg_start_backup function now accepts a third optional parameter, called 'exclusive', which is set by default to 'true' for backward compatibility.
Initiating a concurrent (or if you prefer, a non-exclusive) backup is rather simple:
 SELECT pg_start_backup('my_label', false, false);
If you are not familiar with pg_start_backup(), the first argument is a label for the backup, while the second is a request for a fast checkpoint operation. The third parameter, when set to false, requests a concurrent backup:
 postgres=# SELECT pg_start_backup('my_label', true, false);
 -[ RECORD 1 ]---+----------
 pg_start_backup | 0/F000028
The PostgreSQL connection requesting the concurrent backup needs to remain active for the whole duration of the physical copy of data files (performed with your favourite tools such as rsync, cp, tar, SAN or LVM snapshots, ...). Note: this operation follows the same procedure as with prior versions of PostgreSQL (for detailed information, please refer to the documentation about continuous archiving and Point-In-Time-Recovery).
When finished, we need to use the new version of pg_stop_backup() to specify that we are closing the current non-exclusive backup:
 SELECT * FROM pg_stop_backup(false);
The different signature has a mandatory parameter, called 'exclusive', which
needs to be set to 'false' for concurrent backup. The reason is that the function returns a different result, a row made up of three fields:
# LSN, for backup consistency, returned at pg_stop_backup() time: identical to the previous version
# the content of the label file: this needs to be saved as 'backup_label' in the main backup directory (new in 9.6)
# the list of tablespaces: if not empty, this needs to be saved as 'tablespace_map' in the main backup directory (new in 9.6)
For example:
 postgres=# SELECT * FROM pg_stop_backup(false);
 NOTICE: pg_stop_backup complete, all required WAL segments have been archived
 -[ RECORD 1 ]-------------------------------------------------------------
 lsn | 0/F000130
 labelfile | START WAL LOCATION: 0/F000028 (file 00000001000000000000000F)+
 | CHECKPOINT LOCATION: 0/F000060 +
 | BACKUP METHOD: streamed +
 | BACKUP FROM: master +
 | START TIME: 2016-05-16 09:19:44 CEST +
 | LABEL: my_label +
 | 
 spcmapfile | 16386 /Users/gabriele/pg96/tbs1 +
 | 16388 /Users/gabriele/pg96/tbs2 +
 | 
As you can see, these two steps require users to change their customised backup scripts. However, users should expect common disaster recovery and backup external solutions such as Barman, OmniPITR, PgBackRest, pghoard, WAL-E to transparently manage this new behaviour introduced in 9.6.
This new in-core API replaces the pgespresso package that was previously used with Barman 1.3.1 and above. Barman 2.0 now supports both pgespresso for PG9.2-PG9.5 and the new 9.6 API.
IMPORTANT: this new API will become the only supported way in the future to coordinate low level backup operations with PostgreSQL.
Links:
* Implement backup API functions for non-exclusive backups
* Postgres 9.6 highlight - Non-exclusive base backups
== Extensions and Contrib Modules ==
=== Extension cascade support to install dependencies ===
Links: 
* Add CASCADE support for CREATE EXTENSION. 
* Waiting for 9.6 – Add CASCADE support for CREATE EXTENSION
=== Cube extension kNN support ===
Links: 
* Cube extension kNN support 
== Other Features ==
=== Command progress reporting ===
Links: 
* Add a generic command progress reporting facility. 
* Add simple VACUUM progress reporting. 
* Waiting for 9.6 – Add a generic command progress reporting facility.
* Waiting for 9.6 – Add simple VACUUM progress reporting.
=== Generic WAL facility ===
Links:
* Postgres 9.6 highlight - Generic WAL interface
=== Trigonometric functions in degrees ===
* Waiting for 9.6 – Add trigonometric functions that work in degrees.