Contents

PostgreSQL 11

Minor version releases

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)

SQL changes

General:

  • support for all SQL:2011 options for window frame clauses added (commit 0a459cec)

Added:

Modified:

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)
  • estimation of pg_class.reltuples by vacuum and analyze operations made consistent (commit 7c91a036)

Source configuration

  • option --with-llvm added (commit 5b2526c8)

bootstrap

  • catalog data format converted to use Perl data structures (initial commit 372728b0)

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)

Executor

  • parallel-aware hash joins added (commit 18042840)
  • LIMIT can be passed to parallel workers (commit 3452dc52)
  • support for Parallel Append plan nodes (commit ab727167)
  • UNION [ALL] able to run each SELECT in parallel if the individual SELECTs cannot be parallelized (commit 88ba0ae2)

Optimizer

  • selection of the most common values (MCVs) for statistics improved (commit b5db1d93)

Storage parameters

Added:

Vacuum

  • orphaned temporary tables dropped more aggressively (commit 943576bd)

WAL

  • secondary checkpoint removed, meaning WAL is no longer retained beyond the most recent checkpoint (commit 4b0d28de)

System catalogue changes

Tables

Modified:

Views

Modified:

System function changes

Added:

Modified:

  • to_char()
    • TZH and TZM format patterns added for specifying the time zone's offset from UTC in hours and minutes (commit 11b623dd))
  • to_timestamp()
    • TZH and TZM format patterns added for specifying the time zone's offset from UTC in hours and minutes (commit 11b623dd))

Indexing changes

btree

  • support for parallel index builds added (commit 9da0cc35)

GIN

  • predicate locking added (commit 43d1ed60)

GiST

  • predicate locking added (commit 3ad55863)

hash

  • predicate locking added (commit b508a56f)

SP-GiST

  • ^@ operator ("starts with") added (commit 710d90da)
  • support for indexing polygons added (commit ff963b39)
  • support for lossy representation of leaf keys added (commit 854823fa)

Partitioning changes

  • support for default partitions added (commit 6f6b99d1)
  • [partition pruning|]] is able to handle any stable expression (commit 73b7f48f)
  • support for partition pruning at execution time added (commit 499be013)
  • hash partitioning support added (commit 1aba8e65)
  • UPDATE can now move tuples between partitions (initial commit 2f178441)
  • unique indexes allowed on partitioned tables (commit eb7ed3f3)
  • INSERT ... ON CONFLICT ... allowed on partitioned tables (commit 555ee77a)
  • foreign keys allowed on partitioned tables (commit 3de241db)
  • FOR EACH ROW triggers allowed on partitioned tables (commit 86f57594)
  • partition-wise joins enabled (commit f49842d1)
  • partition-wise grouping/aggregation enabled (commit e2f1eb0e)

Configuration changes

Added:

Modified:

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

Server utilities

Added:

Modified:

Core utilities

Modified:

  • pg_basebackup
    • option --create-slot added, to automatically create a replication slot specified with the --slot option (commit 3709ca1c)
  • pgbench
    • major scripting language improvements (commit bc7fa0c1)
    • pow() / power() function added (commit 7a727c18)
    • \if support added (commit f67b113a)
    • non-ASCII characters permitted in variable names (commit 9d36a386)
    • hashing functions hash(), hash_murmur2() and hash_fnv1a() added (commit e51a0484)
    • random_zipfian() function added (commit 1fcd0ade)
    • option --init-steps added (commit 591c504f)
    • option --random-seed added (commit 64f85894)
    • accuracy of statistics generated wth --latency-limit and --rate improved (commits c23bb6ba and16827d44)
  • pg_dump
    • option --load-via-partition-root added (commit 23d7680d)
    • option --no-comments added (commit 1368e92e)
    • now dumps database-level properties (commit b3f84012)
  • pg_dumpall
    • option -E/--encoding added (commit 84be6718)
    • option --no-comments added (commit 1368e92e)
    • option --no-role-passwords added (commit 9a83d56b)
    • option -g/--globals-only now emits only output related to roles and tablespaces (commit b3f84012)
  • 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:

Modified:

Removed:

Documentation

Testing

Test modules

Added:

Pre-release test versions

Initial commit marking the start of PostgreSQL 11 development is 9f14dc39 (2017-08-14).