PostgreSQL 13

PostgreSQL 13 is the latest major PostgreSQL version.

• Release notes (source file)
• Released: 2020-09-24 (release announcement)
• Current minor version: 13.4 (2021-08-12)
• EOL: 2025-11 (estimated)
• Documentation: PostgreSQL 13
• GIT branch: REL_13_STABLE

Minor version releases

• 13.4 (2021-08-12; release notes, announcement)
• 13.3 (2021-05-13; release notes, announcement)
• 13.2 (2021-02-11; release notes, announcement)
• 13.1 (2020-11-12; release notes, announcement)

Useful links

• PostgreSQL 13 observability (interactive chart from DataEgret)

Backend changes

• promotion of a standby will take priority over paused recovery (commit 496ee647)

Data type changes

Added:

• regcollation object identifier type (commit a2b1faa0)
• xid8 (commit aeec457d)

Modified:

• pg_lsn
  • support for MIN() and MAX() aggregates added (commit 313f87a1)

Removed:

• support for the deprecated opaque pseudo-type removed (commit bb03010b)

Indexing changes

General

• incremental sorting added (commit d2d8a229)

btree

• btree index deduplication added (initial commit 0d861bbb)

GIN

• avoid full scan of GIN indexes when possible (commit 4b754d6c)

Vacuum changes

• VACUUM PARALLEL option added (commit 40d964ec)
• autovacuum can now be triggered by the number of INSERT operations (commit b07642db)
• additional information about errors encountered during a vacuum operation will be displayed (commit b61d161c)

Logging changes

• maximum length of parameters logged for prepared statement can now be set (commit 0b34e7d3)
• CSV log output includes the backend type (commit 70a7b477)

Partitioning changes

• ROW expressions can now be used to define partitions (commit bb4114a4)
• expanded range of cases where partition pruning can be applied (commit 4e85642d)
• expanded range of cases where partitionwise joins can be applied (commits c8434d64 and 981643dc)
• BEFORE row-level triggers permitted on partitioned tables (commit 487e9861)
• partitioned tables can be logically replicated via publications (commits 17b9e7f9 and 83fd4532)
• partitioned tables can now be replication targets in logical replication (commit f1ac27bf)

SQL changes

Modified:

• ALTER FOREIGN TABLE
  • ALTER FOREIGN TABLE ... RENAME COLUMN returns ALTER FOREIGN TABLE (instead of ALTER TABLE) on successful command completion (commit 979766c0)
• ALTER FUNCTION
  • NO DEPENDS ON EXTENSION syntax added (commit 5fc70394)
• ALTER INDEX
  • NO DEPENDS ON EXTENSION syntax added (commit 5fc70394)
• ALTER MATERIALIZED VIEW
  • ALTER MATERIALIZED VIEW ... RENAME COLUMN returns ALTER MATERIALIZED VIEW (instead of ALTER TABLE) on successful command completion (commit 979766c0)
  • NO DEPENDS ON EXTENSION syntax added (commit 5fc70394)
• ALTER PROCEDURE
  • NO DEPENDS ON EXTENSION syntax added (commit 5fc70394)
• ALTER ROUTINE
  • NO DEPENDS ON EXTENSION syntax added (commit 5fc70394)
• ALTER STATISTICS
  • ALTER STATISTICS … SET STATISTICS … clause added (commit d06215d0)
• ALTER TABLE
  • ALTER TABLE … ALTER COLUMN … DROP EXPRESSION clause added to convert a generated column to a normal column (commit f595117e)
  • support for foreign key constraints using pre-PostgreSQL 7.3 syntax removed (commit e58a5997)
• ALTER TRIGGER
  • NO DEPENDS ON EXTENSION syntax added (commit 5fc70394)
• ALTER TYPE
  • ALTER TYPE ... SET ( property = value [, ... ] ) syntax added (commit fe30e7eb)
• ALTER VIEW
  • ALTER VIEW ... RENAME COLUMN ... syntax added (commit 30840c92)
• CREATE DATABASE
  • LOCALE option added, combining the existing LC_COLLATE and LC_CTYPE options (commit 06140c20)
• CREATE EXTENSION
  • CREATE EXTENSION … FROM … syntax removed (commit 70a77320)
• CREATE INDEX
  • support for opclass parameters added (commit 911e7020)
  • implicit support for long-deprecated pre-PostgreSQL 8.0 opclass names removed (commit 84eca14b)
  • deduplicate_items storage parameter added (commit 0d861bbb)
• CREATE LANGUAGE
  • removal of pg_pltemplate and creation of the concept of trusted extensions makes it possible for untrusted versions of non-core languages to be installed (commit 50fc694e)
• CREATE MATERIALIZED VIEW
  • USING method syntax added (commit 8586bf7e)
• CREATE PUBLICATION
  • parameter publish_via_partition_root added (commit 83fd4532)
• DROP DATABASE
  • can now take the option FORCE to drop a database even if other users are connected (commit 1379fd53)
• EXPLAIN
  • WAL option added (commit 33e05f89)
• SELECT
  • FETCH FIRST n ROWS [ WITH TIES ] syntax added (commit 357889eb)

System catalogue changes

Tables

Modified:

• pg_publication
  • column pubviaroot added (commit 83fd4532)
• pg_statistic_ext
  • column stxstattarget added (commit d06215d0)
• pg_trigger
  • column tgparentid added (commit b9b408c4)

Removed:

• pg_pltemplate (commit 50fc694e)

Views

Added:

• pg_shmem_allocations (commit ed10f32e)
• pg_stat_slru (commit 28cac71b)
• pg_stat_progress_analyze (commit a166d408)
• pg_stat_progress_basebackup (commit e65497df)

Modified:

• pg_available_extension_versions
  • column trusted added (commit eb67623c)
• pg_replication_slots
  • column wal_status added (commit c6550776)
  • column safe_wal_size added (commits c6550776 and a8aaa0c7))
• pg_stat_activity
  • column leader_pid added (commit b025f32e)
• pg_stat_all_tables
  • column n_ins_since_vacuum added; applies to pg_stat_sys_tables and pg_stat_user_tables as well (commit b07642db)
• pg_stat_gssapi
  • only shows processes with a connection, i.e. excluding various auxiliary processes which do not receive network connections (commit 5c46e7d8)
• pg_stat_replication
  • following columns added (commit 9290ad19):
    • spill_txns
    • spill_count
    • spill_bytes
• pg_stat_ssl
  • only shows processes with a connection, i.e. excluding various auxiliary processes which do not receive network connections (commit 5c46e7d8)

System function changes

Added:

• gen_random_uuid() (commit 5925e554)
• gcd() (commit 13661ddd)
• jsonb_set_lax() (commit a83586b5)
• jsonb_path_exists_tz() (commit bffe1bd6)
• jsonb_path_match_tz() (commit bffe1bd6)
• jsonb_path_query_array_tz() (commit bffe1bd6)
• jsonb_path_query_first_tz() (commit bffe1bd6)
• jsonb_path_query_tz() (commit bffe1bd6)
• lcm() (commit 13661ddd)
• min_scale() (commit 20d6225d)
• pg_current_snapshot() (commit 4c04be9b)
• pg_current_xact_id() (commit 4c04be9b)
• pg_current_xact_id_if_assigned() (commit 4c04be9b)
• pg_snapshot_xip() (commit 4c04be9b)
• pg_snapshot_xmax() (commit 4c04be9b)
• pg_snapshot_xmin() (commit 4c04be9b)
• pg_stat_reset_slru() (commit 28cac71b)
• pg_visible_in_snapshot() (commit 4c04be9b)
• pg_xact_status() (commit 4c04be9b
• trim_scale() (commit 20d6225d)

Configuration changes

Added:

• autovacuum_vacuum_insert_scale_factor (commit b07642db)
• autovacuum_vacuum_insert_threshold (commit b07642db)
• backtrace_functions (commit 71a8a4f6)
• enable_incrementalsort (commit d2d8a229)
• hash_mem_multiplier (commit d6c08e29)
• ignore_invalid_pages (commit 41c184bc)
• logical_decoding_work_mem (commit cec2edfa)
• log_min_duration_sample (commit 6e3e6cc0)
• log_parameter_max_length (commit 0b34e7d3)
• log_parameter_max_length_on_error (commit 0b34e7d3)
• log_statement_sample_rate (commit 6e3e6cc0)
• maintenance_io_concurrency (commit fc34b0d9)
• max_slot_wal_keep_size (commit c6550776)
• wal_receiver_create_temp_slot (commit 32973082)
• wal_skip_threshold (commit c6b92041)

Modified:

• allow_system_table_mods
  •  can now be be set after server start (commit c4a7a392)
  • non-superusers prevented from modifiying the contents of system catalog tables (commit 508bf95b)
• effective_io_concurrency
  • value now used directly instead of being automatically adjusted (commit b09ff536)
• log_line_prefix
  • %b parameter added to show the backend type (commit 70a7b477)
• max_files_per_process
  • minimum value changed from 25 to 64 (commit 3d475515)
• ssl_passphrase_command
  • setting now only available to superusers and members of the default role pg_read_all_settings (commit d9249441)
• track_activity_query_size
  • maximum value raised to 1MB (commit 81472785)
• wal_keep_segments
  • renamed to wal_keep_size (commit f5dff459)

Replication changes

• the amount of WAL reserved by replication slots can now be restricted with the new GUC parameter max_slot_wal_keep_size (commit c6550776)
• walreceiver will now use a temporary replication slot if wal_receiver_create_temp_slot is set (initial commit 32973082)
• primary_conninfo and primary_slot_name (as well as the newly added wal_receiver_create_temp_slot parameter) can be altered with a configuration reload, meaning a standby no longer has to be restarted in order to follow a new primary or upstream node (commit 1e614803)
• recovery will now fail if a defined recovery target is not reached (commit dc788668)
• partitioned child tables can now be added automatically to logical replication publications (initial commit 17b9e7f9)
• partitioned child tables can now be added as logical replication targets (f1ac27bf)

Full text search changes

• word stemming support for Greek, and improvements to Danish and French stemmers added (commit 7b925e12)

psql changes

• \warn command added (commit 02e95a50)
• following operator-related slash commands added (commit b0b5e20c):
  
  • \dAc - list operator classes
  • \dAf - list operator families
  • \dAo - list operators of operator families
  • \dAp - list procedures of operator families
• \d+ now shows whether an object is permanent, temporary or unlogged (commit 9a2ea618)
• \d output for TOAST tables improved (commit eb5472da)
• \e now inserts an unterminated query into psql's query buffer, making it possible to view or further edit the query before submission  (commit d1c866e5)
• \g and \gx can accept additional formatting options, e.g. \g (format=csv csv_fieldsep=',') (commit b63c293b)
• "invisible" PROMPT2 prompt configuration option %w added (commit 7f338369)
• %x included by default in PROMPT1 and PROMPT2 to show transaction status (commit dcdbb5a5)

Server utilities

• pg_rewind
  • option --write-recovery-conf added (commit 927474ce)
  • option -c/--restore-target-wal to use the target server's restore_command to retrieve required WAL (commit a7e8ece4)
  • crash recovery will now be run automatically before rewinding, option --no-ensure-shutdown can disable this (commit 5adafaf1)
• pg_upgrade
  • default new bindir is now the same location as the pg_upgrade binary being executed, removing the need to explicitly specify -B newbindir in most cases (commit 959f6d6a)
• pg_waldump
  • option --quiet added to suppress non-error output (commit ac44367e)
  • reports more detailed information about PREPARE TRANSACTION records (commit 7b8a899b)

Core utilities

• createuser
  • deprecated options --adduser and --no-adduser removed (commit 4fa5edcd)
• dropdb
  • option --force added (commit 80e05a08)
• pg_basebackup
  • backup manifests generated for base backups, which can be verified with the new utility pg_verifybackup (commit 0d8c9c12) 
  • total estimated backup size displayed by default (commit fab13dc5)
  • option --no-estimate-size added (commit fab13dc5)
• pgbench
  • options --partitions and --partition-method added (commit b1c1aa53)
  • --init-steps option accepts G to generate data on the server side, rather than have it generated by the client (commit a386942b)
  • --show-script option added to dump contents of built-in scripts (commit 5823677a)
  • command \aset added, like \gset but able  to store all results from combined SQL queries into separate variables (commit 9d8ef988)
• pg_dump
  • option --include-foreign-data added to dump data from foreign tables (commit 2f9eb313)
• reindexdb
  • option --jobs added (commit 5ab892c3)
• vacuumdb
  • option --parallel added (commit 47bc9ced)

Contrib module changes

Modified:

• adminpack
  • function pg_file_sync() added (commit d694e0bb)
• dict_int
  • ABSVAL parameter added (commit 806eb92c)
• ltree
  • support for binary I/O of ltree, lquery, and ltxtquery types (commit 949a9f04)
  • fixes for NOT handling (commit 70dc4c50)
  • ability to quantify non-'*' items to use a numeric range ({}) of matches (commit 70dc4c50)
• pageinspect
  • some types returned by the bt_metap() function fixed to prevent overflows (commit 691e8b2e)
  • btree index deduplication processing columns added to function output (commit 93ee38ea)
• pg_stat_statements
  • configuration option pg_stat_statements.track_utility to track WAL usage statistics (commit 6b466bf5)
  • configuration option pg_stat_statements.track_planning to track planning statistics (commit 17e03282)
  • support for tracking of planning statistics added(commit 17e03282)
    • following pg_stat_statements columns renamed:
      • total_time → total_exec_time
      • min_time →min_exec_time
      • max_time → max_exec_time
      • mean_time →mean_exec_time
      • stddev_time →stddev_exec_time
    • following pg_stat_statements columns added:
      • plans
      • total_plan_time
      • min_plan_time
      • max_plan_time
      • mean_plan_time
      • stddev_plan_time
  • support for WAL usage stastics tracking added (commit 6b466bf5)
    • following additional columns added to the pg_stat_statements view:
      • wal_records
      • wal_fpi
      • wal_bytes
• postgres_fdw
  • options sslkey and sslcert added at server and user mapping level to enable different users to authenticate with different certificates (commit f5fd995a)
  • passwordless non-superuser connections permitted, mainly to facilitate passwordless SSL authentication (commit 6136e94d)
• sepgsql
  • mandatory access control for TRUNCATE added (commit 4f66c93f)

Following contrib modules marked as trusted extensions (commit eb67623c):

• btree_gin
• btree_gist
• citext
• cube
• dict_int
• earthdistance
• fuzzystrmatch
• hstore
• hstore_plperl
• intarray
• isn
• jsonb_plperl
• lo
• ltree
• pg_trgm
• pgcrypto
• seg
• tablefunc
• tcn
• tsm_system_rows
• tsm_system_time
• unaccent
• uuid-ossp

Other changes

• support for non-ELF BSD systems removed (commit c72f9b95)

Testing

Test modules

Added:

• dummy_index_am (commit 640c1986)
• test_ginpostinglist (commit bde7493d)

Pre-release test versions

• rc1 (REL_13_RC1) - commit efea2b85 (2020-09-14); announcement (2020-09-17)
• beta3 (REL_13_BETA3) - commit 1754a719 (2020-08-10); announcement (2020-08-13)
• beta2 (REL_13_BETA2) - commit bc4d7817 (2020-06-22);  announcement (2020-06-25)
• beta1 (REL_13_BETA1) - commit 7966b798 (2020-05-18);  announcement (2020-05-21)

Initial commit marking the start of PostgreSQL 13 development is 615cebc9 (2019-07-01).