PostgreSQL 10

• Release notes (source file)
• Released: 2017-10-05 (release announcement)
• Current minor version: 10.16 (2021-02-11)
• EOL: 2022-11 (estimated)
• Documentation: PostgreSQL 10

Major features

• Logical replication using publish/subscribe
• Declarative table partitioning
• Improved query parallelism
• Stronger password authentication based on SCRAM-SHA-256 (commit 818fd4a6)
• Significant general performance improvements
• Improved monitoring and control
• SQL standard identity columns (commit 32173270)

Useful links

• PostgreSQL 10 observability (interactive chart from DataEgret)

Backend changes

Data directory

• pg_xlog renamed to pg_wal (commit f82ec32a)

Process management

• postmaster.pid
  • additional line with postmaster status added (commit f13ea95f)

Optimizer

• optional multi-column extended statistics object configurable via the CREATE STATISTICS command (initial commit 7b504eb2)

Statistics

• multivariate n-distinct coefficient support implemented (initial commit 7b504eb2)

Triggers

• transition tables in AFTER triggers (initial commit 8c48375e)

Data type changes

Modified:

• ENUM: values can be renamed with ALTER TYPE (commit 0ab9c56d)

Indexing changes

• auto-summarization added to BRIN indexes (commit 7526e102)
• hash indexes now have WAL support and are crash-safe (main commit c11453ce)

SQL changes

General changes

• XMLTABLE expression support added (commit fcec6caa)

Command changes

Added:

• ALTER PUBLICATION (commit 665d1fad)
• ALTER SUBSCRIPTION (commit 665d1fad)
• ALTER STATISTICS (commit 7b504eb2)
• CREATE PUBLICATION (commit 665d1fad)
• CREATE STATISTICS (commit 7b504eb2)
• CREATE SUBSCRIPTION (commit 665d1fad)
• DROP PUBLICATION (commit 665d1fad)
• DROP STATISTICS (commit 7b504eb2)
• DROP SUBSCRIPTION (commit 665d1fad)

Modified:

• ALTER COLLATION
  • ALTER COLLATION ... REFRESH VERSION  syntax added (commit eccfef81)
• ALTER DEFAULT PRIVILEGES
  • support for altering privileges on schemas added (commit ab89e465)
• ALTER ROLE
  • option UNENCRYPTED PASSWORD removed (eb61136d)
• ALTER SEQUENCE
  • ALTER SEQUENCE command now fully transactional (commit 3d79013b)
  • ALTER SEQUENCE ... AS data_type clause added (commit 2ea5b06c)
• ALTER TYPE
  • ALTER TYPE ... RENAME VALUE 'foo' TO 'bar'  syntax added for renaming ENUM values (commit 0ab9c56d)
• ALTER USER
  • option UNENCRYPTED PASSWORD removed (eb61136d)
• COMMENT ON
  • COMMENT ON PUBLICATION added (commit 87dee41f)
  • COMMENT ON STATISTICS added (commit 7b504eb2)
  • COMMENT ON SUBSCRIPTION added (commit 87dee41f)
• COPY
  • COPY view FROM can now insert into views defined with INSTEAD OF INSERT triggers  (commit 279c439c)
• CREATE COLLATION
  • options PROVIDER and VERSION added (commit eccfef81))
  • IF NOT EXISTS syntax added (commit 6d16ecc6)
• CREATE FOREIGN TABLE
  
  • support for table partitioning via the CREATE FOREIGN TABLE ... PARTITION OF syntax added (commit f0e44751)
• CREATE POLICY
  • AS { PERMISSIVE | RESTRICTIVE } syntax added (commit 093129c9)
• CREATE ROLE
  • option UNENCRYPTED PASSWORD removed (eb61136d)
• CREATE SEQUENCE
  • CREATE SEQUENCE ... AS data_type clause added (commit 2ea5b06c)
• CREATE SERVER
  • IF NOT EXISTS clause added (commit b6fb534f)
• CREATE TRIGGER
  • REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } syntax added (initial commit 8c48375e)
• CREATE USER
  • option UNENCRYPTED PASSWORD removed (eb61136d)
• CREATE USER MAPPING
  • IF NOT EXISTS clause added (commit b6fb534f)
• EXPLAIN
  • SUMMARY option added (commit f9b1a0dd)
• SELECT
  • Set-returning functions| disallowed inside CASE or COALESCE clauses (commit 0436f6bd)

Configuration changes

Added:

• min_parallel_index_scan_size (commit 51ee6f31)
• min_parallel_table_scan_size (commit 51ee6f31)

Modified:

• hot_standby
  • default value changed from off to on (commit 34fc6167)
• log_directory
  • default changed from pg_log to log (commit 3371e4d9)
• log_line_prefix
  • default changed from empty string to '%m [%p] ' (commit 7d3235ba)
• max_parallel_workers_per_gather
  • default changed to 2 (commit 77cd477c)
• max_replication_slots
  • default value changed from 0 to 10 (commit f6d6d292)
• max_wal_senders
  • default value changed from 0 to 10 (commit f6d6d292)
• password_encryption
  • changed from boolean to enum (commit babe05bc)
  • option scram-sha-256 added (commit 818fd4a6)
  • support for plain-text passwords (options plain and off) removed (commit eb61136d)
• wal_level
  • archive and hot_standby settings merged into replica (commit b555ed81)
  • default value changed from minimal to replica (commit f6d6d292)

Removed:

• min_parallel_relation_size (commit 51ee6f31)

Partitioning changes

• Declarative table partitioning added (initial commit f0e44751)
• ALTER TABLE ... ADD PRIMARY KEY propagates any NOT NULL change to inheritance child tables (commit c30f1770)

System catalogue changes

Tables

Added:

• pg_partitioned_table (commit f0e44751)
• pg_publication (commit 665d1fad)
• pg_publication_rel (commit 665d1fad)
• pg_sequence (commit 1753b1b0)
• pg_statistic_ext (commit 7b504eb2)
• pg_subscription (commit 665d1fad)
• pg_subscription_rel (commit 7c4f5240)

Modified:

• pg_attribute
  • column attidentity added (commit 32173270)
• pg_policy
  • column polpermissive added (commit 093129c9)
• pg_trigger - following columns added (commit 8c48375e):
  • tgoldtable
  • tgnewtable

Views

Added:

• pg_hba_file_rules (commit de16ab72)
• pg_publication_tables (commit 665d1fad)
• pg_sequences (commit 1753b1b0)
• pg_stat_subscription (commit 665d1fad)

Modified:

• pg_policies
  • column permissive added (commit 093129c9)
• pg_replication_slots
  • column temporary added (commit a924c327)
• pg_stat_activity
  • column backend_type added (commit fc70a4b0)
  • auxiliary processes and background workers shown (commit fc70a4b0)
• pg_stat_replication
  • following columns renamed (commit d10c626d):
    
    • sent_location to sent_lsn
    • write_location to write_lsn
    • flush_location to flush_lsn
    • replay_location to replay_lsn
  • following columns added (commit 6912acc0):
    • write_lag
    • flush_lag
    • replay_lag

System functions

Added:

• pg_collation_actual_version() (commit eccfef81)
• pg_current_logfile() (commit 19dc233c)
• pg_import_system_collations() (commit aa17c06f)
• pg_ls_logdir() (commit befd73c5)
• pg_ls_waldir() (commit befd73c5)
• pg_safe_snapshot_blocking_pids() (commit 511540da)

Modified:

• pg_stop_backup()
  • option wait_for_archive boolean added (commit 017e4f25)
• pg_xlog_location_diff() renamed to pg_wal_lsn_diff() (commit d10c626d)

libpq changes

• passfile connection parameter added (commit ba005f19)
• target_session_attrs connection parameter added (commit 721f7bd3)

Users/roles

Following default roles added (commit 0fdc8495):

• pg_read_all_settings
• pg_read_all_stats
• pg_stat_scan_tables
• pg_monitor

Replication changes

Logical replication

• new subscriptions can synchronize data (commit 7c4f5240)
• logical replication support added (initial commit 665d1fad)

Configuration

Added:

• recovery_target_lsn (commit 35250b6a)

Full-text search changes

• support for json and jsonb datatypes added (commit e306df7f)

Foreign data wrapper changes

• Foreign tables can participate in table partitioning (commit f0e44751)
• support for pushing aggregate functions to the remote server (commit 7012b132)

Server utilities

Modified:

• initdb
  • options --noclean and --nosync can now be spelled --no-clean and --no-sync (commit 5d58c07a)
• pg_ctl
  • defaults to waiting (command line option -w/--wait) for all actions, including start (commit 05cd12ed)
• pg_upgrade
  • hash indexes from older PostgreSQL versions will be invalidated and a script generated for recreating them (commit a95410e2)

Core utilities

Modified:

• pg_basebackup
  • option -X/--wal-method defaults to stream; option -x removed (both commit 9a4d5107)
  • option --no-slot added (commit e7b020f7)
• pg_dump
  • support for dumping from pre-8.0 versions removed (commit 64f3524e)
• pg_receivewal
  • renamed from pg_receivexlog to pg_receivewal (commit 85c11324)
  • option -Z/--compress added (commit cada1af3)
• pg_recvlogical
  • option --endpos added (commit 7c030783)
• pg_restore
  • option -N/--exclude-schema added (commit 46b55e7f)

psql changes

• variables showing psql version and server version added (commit 9ae9d8c1)
• meta-commands \if, \elif, \else, and \endif added (commit e984ef58)
• \gx command added to execute a query in expanded mode (commit b2678efd)

Contrib modules

Added:

• amcheck (commit 3717dc14)

Modified:

• btree_gin
  • support for indexing ENUM datatypes added (commit 4ad0f88c)
  • support for indexing the MACADDR8 datatype added (commit c7a9fa39)
• btree_gist
  • support for indexing ENUM datatypes added (commit f7946a92)
  • support for indexing the UUID datatype added (commit 11da83a0)
  • support for indexing the MACADDR8 datatype added (commit c7a9fa39)
• citext
  • support for regexp_match() added (commit f9d747a4)
• cube
  • GIN/GiST support for parallel queries (commit 2910fc82)
• file_fdw
  • able to read output from programs as well as from files (commit 8e91e12b)
• pg_buffercache
  • modified to run with fewer locks (commit 6e654546)
• pg_freespacemap
  • functions provided can be executed by members of the default role pg_stat_scan_tables (commit 25fff407)
• pgrowlocks
  • members of the new default role "pg_stat_scan_tables" may now execute pgrowlocks() (commit 25fff407)
• pgstattuple
  • function pgstathashindex() added (commit e759854a)
  • reduce locking when examining a hash index (commit d4b5d4ca)
  • GRANT permissions used to control pgstattuple function usage (commit fd321a1d)
• pg_visibility
  • All functions (with the exception of pg_truncate_visibility_map()) can be executed by members of the pg_stat_scan_tables role (commit
  • 25fff407)
• postgres_fdw
  • aggregate functions can be pushed to the remote server (commit 7012b132)
  • additional types of join can be pushed to the remote server (commits b30fb56b and 332bec1e)
  • OID columns will now be fetched from the remote server (commit ae025a15)
• seg
  • GIN/GiST support for parallel queries (commit 2910fc82)
• sepgsql
  • partitioned table support added (commit 25542d77)

Removed:

• tsearch2 (commit 7ada2d31)