PostgreSQL 11

• Release notes (source file)
• Released: 2018-10-18 (release announcement)
• Current minor version: 11.10 (2020-11-12)
• EOL: 2023-11 (estimated)

Major features

• Improvements to partitioning functionality
• Improvements to parallelism
• SQL stored procedures that support embedded transactions (initial commit e4128ee7)
• Optional Just-in-Time (JIT) compilation for some SQL code
• Expanded support for window functions
• Covering indexes can now be created (commit 8224de4f)
• ALTER TABLE ... ADD COLUMN with a non-null constant default no longer requires a table rewrite (initial commit 16828d5c)

Useful links

• PostgreSQL 11 observability (interactive chart from DataEgret)

SQL changes

Added:

• ALTER PROCEDURE (commit e4128ee7)
• ALTER ROUTINE (commit e4128ee7)
• CALL (commit e4128ee7)
• CREATE PROCEDURE (commit e4128ee7)
• DROP PROCEDURE (commit e4128ee7)
• DROP ROUTINE (commit e4128ee7)

Modified:

• ALTER EXTENSION
  • support for PROCEDURE and ROUTINE member objects added (commit e4128ee7)
• ALTER INDEX
  • ALTER INDEX ... ATTACH PARTITION ... syntax added (commit 8b08f7d4)
  • ALTER INDEX ... ALTER COLUMN ... SET STATISTICS syntax added (commit 5b6d13ee)
• ANALYZE
  • syntax ANALYZE (option [, ...] ) added (commit 854dd8cf)
• COMMENT ON
  • COMMENT ON PROCEDURE added (commit e4128ee7)
  • COMMENT ON ROUTINE added (commit e4128ee7)
• COPY
  • COPY available for foreign tables and foreign partitions, if the underlying foreign data wrapper provides support (commit 3d956d95)
  • COPY FREEZE disallowed on partitioned tables (commit a4db7fe0)
  • COPY can also be executed by members of the default roles pg_read_server_files, pg_write_server_files and pg_execute_server_program (commit 0fdc8495)
• CREATE AGGREGATE
  • support for tracking whether aggregate final functions modify transition state (commit 4de2d4fb)
• CREATE INDEX
  • support for covering indexes via the INCLUDE clause added (commit 8224de4f)

Backend changes

General

• the data directory can now be made group-readable (commit c37b3d08)
• WAL segment size is now configurable when initdb is run (commit fc49e24f)

background workers

• bgw_type field added; the value set here will be visible in pg_stat_activity's backend_type column instead of the generic value "background worker", and also shown as the ps process display label (commit 5373bc2a)
• background workers can attach to databases which normally disallow connections (commit eed1ce72)

Storage parameters

Added:

• toast_tuple_target (commit c2513365)

Internals

• Initial catalog data format substantially refactored (commit 372728b0)

System catalogue changes

Tables

Modified:

• pg_aggregate - following columns added:
  • aggfinalmodify (commit 4de2d4fb)
  • aggmfinalmodify (commit 4de2d4fb)
• pg_class
  • column relhaspkey removed (commit f66e8bf8)
• pg_index
  • column indnkeyatts added (commit 8224de4f)
• pg_partitioned_table
  • column partdefid added (commit 6f6b99d1)
• pg_proc
  • columns proisagg and proiswindow replaced by prokind (commit fd1a421f)
• pg_publication
  • column pubtruncate added (commit 039eb6e9)

Views

Modified:

• pg_stat_activity
  • in column backend_type, value shown for background workers is defined by the background worker itself (commit 5373bc2a)
• pg_stat_wal_receiver - following columns added (commit 9a895462):
  • sender_host
  • sender_port

System function changes

Added:

• starts_with() (commit 710d90da)

Indexing changes

SP-GiST

• ^@ operator ("starts with") added (commit 710d90da)

Configuration changes

Added:

• enable_partition_pruning (commit 055fb8d3)
• enable_partitionwise_join (commit f49842d1)
• enable_partitionwise_aggregate (commit e2f1eb0e)
• ssl_passphrase_command (commit 8a3d9425)
• ssl_passphrase_command_supports_reload (commit 8a3d9425)

Removed:

• replacement_sort_tuples (commit 8b304b8b)

Users/roles

• Following default roles added (commit 0fdc8495):
  • pg_read_server_files
  • pg_write_server_files
  • pg_execute_server_program

Foreign data wrapper changes

• INSERT, UPDATE, and COPY on partitioned tables can now properly route rows to foreign partitions (commit 3d956d95)

Server utilities

Added:

• pg_verify_checksums introduced

Modified:

• initdb
  • options -g/--allow-group-access added (commit c37b3d08)
  • option --wal-segsize added (commit fc49e24f)
• pg_ctl
  • kill option added (commit 2e83db3a)
• pg_resetwal
  • long options added (commit e22b27f0)

Core utilities

Modified:

• pg_basebackup
  • option --create-slot added, to automatically create a replication slot specified with the --slot option (commit 3709ca1c)
• pg_dump
  • option --no-comments added (commit 1368e92e)
• pg_dumpall
  • option --no-comments added (commit 1368e92e)
• pg_receivewal
  • option -E/--endpos added (commit 6d9fa526)
  • option --no-sync added (commit 5f397129)
• pg_restore
  • option --no-comments added (commit 1368e92e)

psql changes

• \gdesc command added (commit 49ca462e)
• variables showing success/failure of SQL queries added (commit 69835bc8)
• add test for the existence of a variable (commit d57c7a7c)
• environment variable PSQL_PAGER added, to control psql's pager (commit 5e8304fd)
• tab completion functionality made server-independent (commit 722408bc)
• quit and exit commands added, similar to MySQL's command line client (commit df9f599b)

Contrib modules

Added:

• jsonb_plperl (commit 341e1661)
• jsonb_plpython (commit 3f44e3db)

Modified:

• adminpack
  • support for default roles added (commit 11523e86)
• amcheck
  • verification of heap relations belonging to btree indexes added (commit 7f563c09)
• btree_gist
  • support for indexing BOOL, BPCHAR, NAME and UUID datatypes added (commit f4cd7102)
• citext
  • citext_pattern_ops added (commit f2464997)
• cube
  • support for index-only scans (commit de1d042f)
  • operator selectivity improvements (commit 7d08ce28)
• pg_prewarm
  • automatic prewarm feature added (commit 79ccd7cb)
• postgres_fdw
  • support for pushing down partition-level aggregates to partitioned tables added (commit 7e0d64c7)
  • support for pushing UPDATEs and DELETEs using joins to foreign tables added (commit 1bc0100d)
  • support for COPY and foreign partition routing added (commit 3d956d95)
• seg
  • support for index-only scans (commit de1d042f)

Removed:

• chkpass (commit 5d3cad56)

Testing

Test modules

Added:

• test_bloomfilter (commit 51bc2717)
• test_predtest (commit 44468f49)
• test_rbtree (commit 610bbdd8)