PostgreSQL 9.6

• Release notes (source file)
• Released: 2016-09-29 (release announcement)
• Current minor version: 9.6.20 (2020-11-12)
• EOL: 2021-11 (estimated)

Major features

• Parallel execution of sequential scans, joins and aggregates
• VACUUM FREEZE avoids unnecessary page scans
• synchronous replication can use multiple standby servers for increased reliability
• full text search able to search for phrases (multiple adjacent words)
• postgres_fdw supports remote joins, sorts, UPDATEs, and DELETEs

SQL changes

Modified:

• ALTER EXTENSION
  • support for ACCESS METHOD member objects added (commit e8bdee27; note this was actually added in PostgreSQL 9.6.1)
• ALTER FUNCTION
  • DEPENDS ON EXTENSION syntax added (commit f2fcad27)
• ALTER INDEX
  • DEPENDS ON EXTENSION syntax added (commit f2fcad27)
• ALTER MATERIALIZED VIEW
  • DEPENDS ON EXTENSION syntax added (commit f2fcad27)
• ALTER ROLE
  • option CREATEUSER/NOCREATEUSER removed (commit commit|d371bebd3d16949171282c8252dfd6f82d2e73
• ALTER TABLE
  • ALTER TABLE ... ADD COLUMN ... IF NOT EXISTS syntax added (commit 2cd40adb)
• ALTER TRIGGER
  • DEPENDS ON EXTENSION syntax added (commit f2fcad27)
• ALTER USER
  • option CREATEUSER/NOCREATEUSER removed (commit d371bebd)
• COPY
  • COPY can now copy the output of an INSERT/UPDATE/DELETE … RETURNING query (commit 92e38182)
• CREATE EXTENSION
  • CREATE EXTENSION … CASCADE syntax added (commit b67aaf21)

Performance changes

• ALTER TABLE ... SET (fillfactor = ...) can be performed at a reduced lock level (commit fcb4bfdd)
• ALTER TABLE ... SET (autovacuum* = ...) can be performed at a reduced lock level (commit 47167b79)

Backend changes

Storage parameters

Added:

• parallel_workers (initial commit 25fe8b5f)

Hooks

Following hook added:

• create_upper_paths_hook (commit 5864d6a4)

System catalogue changes

Tables

Added:

• pg_init_privs (commit 6c268df1)

Modified:

• pg_aggregate
  • column aggcombinefn added (commit a7de3dc5)
  • column aggserialfn added (commit 5fe5a2ce)
  • column aggdeserialfn added (commit 5fe5a2ce)
• pg_am
  • many columns removed (commit 65c5fcd3) and replaced by SQL-accessible functions (commit ed0097e4; see reference page for details)
  • column amhandler added (commit 65c5fcd3)
• pg_proc
  • column proparallel added (commit 7aea8e4f)

Views

Added:

• pg_config (commit a5c43b88)
• pg_stat_wal_receiver (commit b1a9bad9)
• pg_stat_progress_vacuum (commit c16dc1ac)

Modified:

• pg_replication_slots
  • column confirmed_flush_lsn added (commit 3f811c2d)
• pg_stat_activity
  • column wait_event added (commit 53be0b1a)
  • column wait_event_type added (commit 53be0b1a)

System function changes

Added:

• pg_control_checkpoint() (commit dc7d70ea)
• pg_control_init() (commit dc7d70ea)
• pg_control_recovery() (commit dc7d70ea)
• pg_control_system() (commit dc7d70ea)
• pg_index_column_has_property() (commit ed0097e4)
• pg_index_has_property() (commit ed0097e4)
• pg_indexam_has_property() (commit ed0097e4)

Modified

• pg_stop_backup()
  • option exclusive boolean added (commit 71176854)

Vacuum changes

• VACUUM option DISABLE_PAGE_SKIPPING added (commit ede62e56)

Configuration changes

Added:

• idle_in_transaction_session_timeout (commit c6dda1f4)
• max_parallel_workers_per_gather (initial commit 80558c1f)
• min_parallel_relation_size (commit 75be6646)
• old_snapshot_threshold (commit 848ef42b)

Modified:

• log_line_prefix
  • %n parameter added to show the Unix epoch (commit f828654e)

Full text search changes

• Phrase full text search via the <-> operator added (commit bb140506)

Foreign data wrapper changes

• UPDATE or DELETE commands can be executed entirely on the remote server (commit 0bf3ae88)
• remote foreign joins performed only when the affected tables will be accessed under the same role ID (commit 45639a05)

Users/roles

• Initial default_role (pg_signal_backend) added (commit 7a542700)

Server utilities

Modified:

• initdb
  • speed up by using only one backend for post-bootstrap steps (commit c4a8812c)
• pg_rewind
  • supports resynchronisation with a lower target timeline (commit e50cda78)

Core utilities

Modified:

• pg_basebackup
  • option --slot added to reserve a replication slot before the backup is started (commit 0dc848b0)
• pg_restore
  • option -t/--table now matches all types of relations (commit 5671aaca)
  • option --strict-names added (commit d0242602)

psql changes

• support for multiple and mixed -c and -f options added (commit d5563d7d)
• \crosstabview added which prints the results of a query in a cross-tabulated display (initial commit c09b18f2)
• \errverbose command added which shows the last server error at full verbosity (commit 3cc38ca7)
• \ev and \sv commands added for editing/showing view definitions (commit 8eb6407a)
• \gexec command added which executes a query and re-submits the result(s) as new queries (commit 2bbe9112)
• tab completion now considers the entire query, not just the current line (commit d854118c)
• prompt option %p added, which displays the PID of the connected backend (commit 275f05c9)
• display of the CONTEXT field of messages can be controlled by the new variable SHOW_CONTEXT (commit 0426f349)

Contrib modules

Added:

• bloom (commit 9ee014fc)
• pg_visibility (commit ba0a198f)

Modified:

• auto_explain
  • option auto_explain.sample_rate added (commit 92f03fe7)
• citext
  • parallel query support added (commit a89b4b1b)
• cube
  • kNN support added (commit 33bd250f)
• earthdistance
  • parallel query support added (commit 50e5226b)
• lo
  • parallel query support added (e7880e5d)
• pgcrypto
  • parallel query support added (commit 0dbf3ce0)
• pg_freespacemap
  • parallel query support added (commit 42d4257a)
• pg_prewarm
  • parallel query support added (commit 6b3586ca)
• pgrowlocks
  • parallel query support added (commit 9164deea)
• postgres_fdw
  • basic support added for remote joins (commits e4106b25 and aa09cd24)
  • support added for remote sorts (commits f18c944b and ccd8f979)
  • support added for directly modifying foreign tables with remote UPDATEs or DELETEs (commit 0bf3ae88)
  • the fetch_size option can now be applied to individual tables as well as the foreign server (commit dc203dc3)
  • query cancellation requests will be transmitted to the remote server (commits f039eaac and 1b812afb)
  • user mappings which map to the same user on the remote server will be consolidated into a single remote connection (commit 96198d94)
  • extension functions and operators, if specified in the extensions option, can be pushed down to the remove server (commit d8949416)
• seg
  • GIN/GiST support for parallel queries (commit 2910fc82)
• sslinfo
  • function ssl_extension_info() added (commit 49124613)

Testing

Test modules

Added:

• snapshot_too_old (commit 848ef42b)
• test_extensions (commit b67aaf21)
• test_pg_dump (commit 6bd356c3)