PostgreSQL 12

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

Minor version releases

• 12.7 (2021-05-13; release notes, announcement)
• 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 9f06d79e)

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)
• pgbench
  • \gset command added (initial commit 6260cc55)
  • --rate option precision improved (commit 5b7e0367)
  • error reporting improved (commit 5b75a4f8)
  • maximum number of variables/arguments increased from 10 to 256 (commit a4784152)
• 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)