PostgreSQL 12

• Release notes (source file)
• Released: 2019-10-03 (release announcement)
• Current minor version: 12.6 (2021-02-11)
• EOL: 2024-11 (estimated)
• Documentation: PostgreSQL 12
• GIT branch: REL_12_STABLE

Minor version releases

• 12.6 (2021-02-11; release notes, announcement)
• 12.5 (2020-11-12; release notes, announcement)
• 12.4 (2020-08-13; release notes, announcement)
• 12.3 (2020-05-14; release notes)
• 12.2 (2020-02-13; release notes)
• 12.1 (2019-11-14; release notes)

Major features

• partitioning performance enhancements
• B-tree indexing enhancements
• REINDEX CONCURRENTLY
• generated columns
• automatic (but overrideable) inlining of common table expressions
• Progress reporting views for CREATE INDEX, REINDEX, CLUSTER, and VACUUM FULL
• SQL/JSON path support

Useful links

• PostgreSQL 12 observability (interactive chart from DataEgret)

SQL changes

General:

• Common table expressions (CTE) 
  • MATERIALIZED / NOT MATERIALIZED clauses to control materialization (commit 608b167f)

Modified:

• ABORT
  • [ AND [ NO ] CHAIN ] syntax added (commit 280a408b)
• ALTER FOREIGN TABLE
  • ALTER FOREIGN TABLE ... SET WITH OIDS syntax removed (commit 578b2297)
  • support for generated columns via the GENERATED ALWAYS AS ... STORED clause added (commit fc22b662)
• ALTER FUNCTION
  • option SUPPORT added (commit 1fb57af9)
• COMMIT
  • [ AND [ NO ] CHAIN ] syntax added (commit 280a408b)
• COPY
  • WHERE clause can now be added to COPY FROM, making it possible to filter incoming data (commit 31f38174)
  • insertion into partitioned tables speeded up through use of multi-inserts (commit 0d5f05cd)
• CREATE AGGREGATE
  • OR REPLACE syntax added (commit 01bde4fa)
• CREATE COLLATION
  • option DETERMINISTIC added (commit 5e1963fb)
• CREATE FOREIGN TABLE
  • support for generated columns via the GENERATED ALWAYS AS ... STORED clause added (commit fc22b662)
• CREATE FUNCTION
  • option SUPPORT added (commit 1fb57af9)
• CREATE TABLE AS
  • option USING added (commit 8586bf7e)
  • option WITH OIDS removed commit 578b2297)
• EXPLAIN
  • option SETTINGS added (commit ea569d64)
• REINDEX
  • option CONCURRENTLY added (5dc92b84)
• ROLLBACK
  • [ AND [ NO ] CHAIN ] syntax added (commit 280a408b)

General changes

Optimizer

• most-common-value statistics option for statistic objects added (commit 7300a699)

Performance

• ALTER TABLE ... SET DATA TYPE avoids a table rewrite when changing between timestamp and timestamptz when the session time zone is UTC (commit 3c592630)
• ALTER TABLE ... SET NOT NULL can avoid unnecessary table scans (commit bbb96c37)
• the planner can replace an empty FROM clause with a dummy RTE, improving performance (commit 4be058fe)

Partitioning changes

• ALTER TABLE ... ATTACH PARTITION is now performed with reduced locking requirements (commit 898e5e32)

Compatibility changes

• OIDs are no longer be available as row-level attributes; the OID column on system catalogue tables has become an ordinary column
• recovery.conf items have been integrated into the main PostgreSQL configuration file structure (commit 2dedf4d9)
• jit now on by default

Backend changes

• Following storage parameters added:
  
  • vacuum_index_cleanup (commit a96c41fe)
  • toast.vacuum_index_cleanup (commit ce59b75d)
  • vacuum_truncate, toast.vacuum_truncate (commit 119dcfad)

System catalogue changes

Tables

General

• The oid column of all system catalogue tables is now exposed as a normal column (commit 578b2297).
• ALTER TABLE can now be executed cleanly on system catalogue tables (commit 590a8702).

Added:

• pg_statistic_ext_data (commit 6cbfb784)

Modified:

• pg_am
  • supports table access methods in addition to index access methods (commit 8586bf7e)
• pg_attrdef
  • column adsrc removed (commit fe503823)
• pg_class
  • column relhasoids removed
• pg_constraint
  • column consrc removed (commit 96b00c43)
• pg_proc
  • column protransform renamed to prosupport (commit 74dfe58a)
• pg_statistic
  • following columns added (commit 5e092800)
    • stacoll1
    • stacoll2
    • stacoll3
    • stacoll4
    • stacoll5
• pg_statistic_ext
  • following columns removed (and incorporated into new table pg_statistic_ext_data, commit 6cbfb784):
    • stxndistinct
    • stxdependencies

Views

Added:

• pg_stats_ext (commit aa087ec6)
• pg_stat_gssapi (commit b0b39f72)
• pg_stat_progress_cluster (commit 6f97457e)
• pg_stat_progress_create_index (commit ab0dfc96)

Modified:

• pg_stat_database:
  • column checksum_last_failure added (commit 77bd49ad)
  • row for shared objects added (commit 77bd49ad)
  • column checksum_failures added (commit 6b9e875f)
• pg_stat_replication
  • column reply_time added (commit 7fee252f)
• pg_stat_ssl
  • column client_serial added (commit f60a0e96)
  • column issuer_dn added (commit f60a0e96)
  • column clientdn renamed to client_dn (commit f60a0e96)
  • unprivileged users now only able to see their own rows (commit f9692a76)

System functions

Added:

• pg_copy_logical_replication_slot() (commit 9f06d79e)
• pg_copy_physical_replication_slot() (commit 9f06d79e)
• pg_ls_archive_statusdir() (commit c4810162)
• pg_ls_tmpdir() (commit 9cd92d1a)
• pg_partition_ancestors() (commit b96f6b19)
• pg_partition_root() (commit 3677a0b2)
• pg_partition_tree() (commit d5eec4ee)
• pg_promote() (commit 10074651)

Changed:

• pg_config()
  • function behavior marked as stable (previously: immutable; commit 290e3b77)

Data types

Added:

• jsonpath (initial commit 72b64603)

Removed:

• abstime (commit cda6a8d0)
• reltime (commit cda6a8d0)
• tinterval (commit cda6a8d0)

Configuration changes

Added:

• default_table_access_method (commit 8586bf7e)
• log_transaction_sample_rate (commit 799e2203)
• plan_cache_mode (commit f7cb2842)
• wal_init_zero (commit 475861b2)
• wal_recycle (commit 475861b2)

Modified:

• The following items (former recovery.conf parameters) can now be changed with a reload (SIGHUP) rather than a server restart (commit 13b89f96):
  • archive_cleanup_command
  • promote_trigger_file
  • recovery_end_command
  • recovery_min_apply_delay
• autovacuum_vacuum_cost_delay
  • default reduced from 20ms to 2ms (commit cbccac37)
• max_wal_senders
  • this must now have the same or greater value on a standby as on its upstream (commit ea92368c)
• recovery_target_timeline
  • now defaults to latest rather than current (commit 0acb3bc3)

Removed:

• default_with_oids (commit 578b2297)
• standby_mode
  • (former recovery.conf parameter) replaced by recovery.signal and standby.signal files (commit 2dedf4d9)

Vacuum changes

• VACUUM option INDEX_CLEANUP added (commit a96c41fe)
• VACUUM / ANALYZE option SKIP_LOCKED added (commit 803b1301)
• VACUUM option TRUNCATE added (commit b84dbc8e)

Replication changes

Replication slots

• Replication slots (both physical and logical) can now be copied (commit commit tag must have valid commit value (provided: 49f06d79ef831ffa333f908f6d3debdb654292414))

Replication configuration changes

recovery.conf items have been integrated into the main PostgreSQL configuration file structure (commit 2dedf4d9).

Server utilities

Added:

• pg_checksums (replacing pg_verify_checksums; initial commit 6dd263cf)

Modified:

• pg_ctl
  • logrotate option added (commit ec743699)
  • PostgreSQL server process will properly detatch after pg_ctl start (commit bb24439c)
• pg_upgrade
  • --clone option added (commit 3a769d82)
  • --socketdir option added (commit 2d34ad84)
• pg_rewind
  • option -N/--no-sync added (commit 8a00b96a)

Core utilties

Modified:

• pg_basebackup
  • option --write-recovery-conf will now write its configuration settings to postgresql.auto.conf (commit 2dedf4d9)
• pg_restore
  • output no longer directed to STDOUT by default; "-f -" must be specified explicitly (commit 413ccaa7)
• reindexdb
  • option --concurrently added (commit 5dc92b84)
• vacuumdb
  • options --min-xid-age and --min-mxid-age added (commit 00d1e88d)
  • options --disable-page-skipping and --skip-locked added (commit 354e95d1)

psql changes

• \dP command to list partitioned tables and indexes added (commit 1c5d9270)
• CSV table output mode added (commit aa2ba50c)
• link to online manual page displayed in psql's \help output (commit 27f3dea6)

Contrib modules

Modified:

• auto_explain
  • option auto_explain.log_level added (commit 2d36a5e9)
• citext
  • 64-bit hash function for type citext added (commit 48c41fa9)
• postgres_fdw
  • expand the number of cases where ORDER BY sorts and LIMIT clauses can be pushed to remote servers (commits d50d172e and ffab494a)
  • Improve optimizer cost accounting for postgres_fdw queries (commits 08d2d58a, 8b6da83d and edbcbe27)
  • Properly honour WITH CHECK OPTION on views that reference postgres_fdw tables (a45adc74)
• pg_stat_statements
  • statistics can now be reset for specific databases, users, and queries (commit 43cbedab)
• vacuumlo
  • options standardized to match those of core utilities (commit bfea331a)

Removed:

• timetravel example extension removed from the spi contrib module (commit 2d10defa)

Source code

• C99 support required (commit d9dd406f)

Testing

Test modules

Added:

• test_integerset (commit df816f6a)
• test_misc (commit 121e3cee)
• unsafe_tests (commit c91504b9)

Pre-release test versions

• rc1 - commit: 17822c0e (2019-09-23)
• beta4 - commit: 84bb33c4 (2019-09-09): announcement (2019-09-12)
• beta3 - commit: 7c45b994 (2019-08-05); announcement (2019-08-08)
• beta2 - commit: 0ab7110b (2019-06-17); announcement (2019-06-20)
• beta1 - commit: a240570b (2019-05-20); announcement (2019-05-23)