PostgreSQL 9.6

• Release notes (source file)
• Released: 2016-09-29 (release announcement)
• Final minor version: 9.6.24 (2021-11-11)
• EOL: 2021-11 (EOL notice)
• Documentation: PostgreSQL 9.6
• GIT branch: REL9_6_STABLE

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

Useful links

• PostgreSQL 9.6 observability (interactive chart from DataEgret)

SQL changes

Added:

• CREATE ACCESS METHOD (commit 473b9328)
• DROP ACCESS METHOD (commit 473b9328)

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)
  • PARALLEL { UNSAFE | RESTRICTED | SAFE } option added (commit 7aea8e4f)
• ALTER INDEX
  • DEPENDS ON EXTENSION syntax added (commit f2fcad27)
• ALTER MATERIALIZED VIEW
  • DEPENDS ON EXTENSION syntax added (commit f2fcad27)
• ALTER OPERATOR
  • options added for changing the selectivity functions associated with an existing operator (commit 321eed5f)
• ALTER ROLE
  • option CREATEUSER/NOCREATEUSER removed (commit d371bebd)
• 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)
• COMMENT ON
  • COMMENT ON ACCESS METHOD added (commit 4f04b66f)
• COPY
  • COPY can now copy the output of an INSERT/UPDATE/DELETE … RETURNING query (commit 92e38182)
• CREATE AGGREGATE
  • support for serialization and deserialization of aggregate transition states (commit 5fe5a2ce)
  • support for multi-stage aggregation (commit a7de3dc5)
  • PARALLEL option added (commit 5fe5a2ce)
• CREATE EXTENSION
  • CREATE EXTENSION … CASCADE syntax added (commit b67aaf21)
• CREATE FUNCTION
  • PARALLEL { UNSAFE | RESTRICTED | SAFE } option added (commit 7aea8e4f)
• CREATE GROUP
  • option CREATEUSER/NOCREATEUSER removed (commit d371bebd)
• CREATE ROLE
  • option CREATEUSER/NOCREATEUSER removed (commit d371bebd)
• CREATE USER
  • option CREATEUSER/NOCREATEUSER removed (commit d371bebd)
• INSERT
  • support for array subscripts and field selections in the target column list added (commit a3c7a993)
• VACUUM
  • option DISABLE_PAGE_SKIPPING added (commit ede62e56)

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

General

• support for systemd service notifications added (commit 7d17e683)

Storage parameters

Added:

• parallel_workers (initial commit 25fe8b5f)

Hooks

Following hook added:

• create_upper_paths_hook (commit 5864d6a4)

Locking

• locks now identified by name if trace_lwlocks is used (commit aa65de04)

WAL

• Generic WAL interface added (commit 65578341)

Source configuration

• option --with-systemd added (commit 7d17e683)

Data type changes

• arrays
  • one or both boundaries in an array slice specifier can be omitted (commit 6efbded6)

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:

• acosd() (commit e1bd684a)
• array_to_tsvector() (commit 6943a946)
• asind() (commit e1bd684a)
• atand() (commit e1bd684a)
• atan2d() (commit e1bd684a)
• cosd() (commit e1bd684a)
• cotd() (commit e1bd684a)
• gin_clean_pending_list() (commit 7f46eaf0)
• jsonb_insert() (commit 0b62fd03)
• parse_ident() (commit 3187d6de)
• pg_blocking_pids() (commit 52f5d578)
• pg_control_checkpoint() (commit dc7d70ea)
• pg_control_init() (commit dc7d70ea)
• pg_control_recovery() (commit dc7d70ea)
• pg_control_system() (commit dc7d70ea)
• pg_current_xlog_flush_location() (commit e63bb454)
• pg_index_column_has_property() (commit ed0097e4)
• pg_index_has_property() (commit ed0097e4)
• pg_indexam_has_property() (commit ed0097e4)
• pg_logical_emit_message() (commit 3fe3511d)
• pg_notification_queue_usage() (commit a04bb65f)
• pg_size_bytes() (commit 53874c52)
• pg_stat_get_backend_wait_event() (commit 53be0b1a)
• pg_stat_get_backend_wait_event_type() (commit 53be0b1a)
• scale() (commit abb17339)
• setweight() (commit 6943a946)
• sind() (commit e1bd684a)
• tand() (commit e1bd684a)
• ts_delete() (commit 6943a946)
• ts_filter() (commit 6943a946)
• tsvector_to_array() (commit 6943a946)
• unnest(tsvector) (commit 6943a946)

Modified

• current_setting()
  • option missing_ok added (commit 10fb48d6)
• extract()
  • handling of infinite datetimes improved (commit 647d87c5)
• pg_create_physical_replication_slot()
  • optional parameter immediately_reserve added to retain WAL from the point of slot creation (commit 6fcd8851)
• pg_stop_backup()
  • option exclusive boolean added (commit 71176854)
• to_char()
  • negative sign ("-") no longer counted as a digit for time values (commit 2d87eedc)

Configuration changes

Added:

• backend_flush_after (commit 428b1d6b)
• bgwriter_flush_after (commit 428b1d6b)
• checkpoint_flush_after (commit 428b1d6b)
• force_parallel_mode (commit 7c944bd9)
• idle_in_transaction_session_timeout (commit c6dda1f4)
• max_parallel_workers_per_gather (initial commit 80558c1f)
• min_parallel_relation_size (commit 75be6646)
• parallel_setup_cost (commit 3bd909b2)
• parallel_tuple_cost (commit 3bd909b2)
• old_snapshot_threshold (commit 848ef42b)
• syslog_sequence_numbers (commit f4c454e9)
• syslog_split_messages (commit fc201dfd)
• wal_writer_flush_after (commit 7975c5e0)

Modified:

• checkpoint_timeout
  • maximum value raised to 1d (commit f2dba881)
• log_line_prefix
  • %n parameter added to show the Unix epoch (commit f828654e)
• synchronous_commit
  • permissible value remote_apply added (commit 314cbfc5)

Indexing changes

GIN

• maintenance_work_mem values of more than 1GB can be used for GIN index builds (commit 30bb26b5)
• pages deleted from a GIN index's pending list to the free space map immediately (commits e9568083 and dc943ad9)

Full text search changes

• Phrase full text search via the <-> operator added (commit bb140506)
• improve support for leading digits in email addresses and hostnames (commit 61d66c44)

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)
• API support for parallel queries (commit 35746bc3)

Users/roles

• roles can no longer created with the pg_ prefix (commit 29300789)
• 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)
• --reuse-previous option added to the \connect command (commit 9d924e9a)

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)
• file_fdw
  • parallel query support added (commit 35746bc3)
• fuzzystrmatch
  • all functions marked parallel safe (commit ffab82fb)
• 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)
• pg_trgm
  • support for the triconsistent function for pg_trgm GIN opclass (commit 97f30146)
  • performance of similarity searches in GIN indexes improved (commit 25bfa7ef)
• 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)

Pre-release test versions

Initial commit marking the start of PostgreSQL 9.6 development is cf8d65de (2015-06-30).