Contents
- Major features
- Useful links
- Backend changes
- Data type changes
- Indexing changes
- SQL changes
- Configuration changes
- Partitioning changes
- System catalogue changes
- System functions
- libpq changes
- Users/roles
- Replication changes
- Full-text search changes
- Foreign data wrapper changes
- Server utilities
- Core utilities
- psql changes
- Contrib modules
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
Process management
postmaster.pid
- additional line with postmaster status added (commit f13ea95f)
Optimizer
- optional multi-column
extended statistics object
configurable via theCREATE 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 withALTER 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)
- option
ALTER SEQUENCE
ALTER TYPE
ALTER USER
- option
UNENCRYPTED PASSWORD
removed (eb61136d)
- option
COMMENT ON
COPY
COPY view FROM
can now insert into views defined withINSTEAD OF INSERT
triggers (commit 279c439c)
CREATE COLLATION
CREATE FOREIGN TABLE
- support for table partitioning via the
CREATE FOREIGN TABLE ... PARTITION OF
syntax added (commit f0e44751)
- support for table partitioning via the
CREATE POLICY
AS { PERMISSIVE | RESTRICTIVE }
syntax added (commit 093129c9)
CREATE ROLE
- option
UNENCRYPTED PASSWORD
removed (eb61136d)
- option
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)
- option
CREATE USER MAPPING
IF NOT EXISTS
clause added (commit b6fb534f)
EXPLAIN
SUMMARY
option added (commit f9b1a0dd)
SELECT
- Set-returning functions| disallowed inside
CASE
orCOALESCE
clauses (commit 0436f6bd)
- Set-returning functions| disallowed inside
Configuration changes
Added:
Modified:
hot_standby
- default value changed from
off
toon
(commit 34fc6167)
- default value changed from
log_directory
- default changed from
pg_log
tolog
(commit 3371e4d9)
- default changed from
log_line_prefix
- default changed from empty string to '
%m [%p]
' (commit 7d3235ba)
- default changed from empty string to '
max_parallel_workers_per_gather
- default changed to
2
(commit 77cd477c)
- default changed to
max_replication_slots
- default value changed from
0
to10
(commit f6d6d292)
- default value changed from
max_wal_senders
- default value changed from
0
to10
(commit f6d6d292)
- default value changed from
password_encryption
wal_level
Removed:
min_parallel_relation_size
(commit 51ee6f31)
Partitioning changes
- Declarative table partitioning added (initial commit f0e44751)
ALTER TABLE ... ADD PRIMARY KEY
propagates anyNOT 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)
- column
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)
- column
pg_replication_slots
- column
temporary
added (commit a924c327)
- column
pg_stat_activity
- column
backend_type
added (commit fc70a4b0) - auxiliary processes and
background workers
shown (commit fc70a4b0)
- column
pg_stat_replication
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)
- option
pg_xlog_location_diff()
renamed topg_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
andjsonb
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)
- options
pg_ctl
- defaults to waiting (command line option
-w
/--wait
) for all actions, includingstart
(commit 05cd12ed)
- defaults to waiting (command line option
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
pg_dump
pg_receivewal
pg_recvlogical
- option
--endpos
added (commit 7c030783)
- option
pg_restore
- option
-N
/--exclude-schema
added (commit 46b55e7f)
- option
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:
Modified:
btree_gin
btree_gist
citext
- support for
regexp_match()
added (commit f9d747a4)
- support for
cube
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)
- functions provided can be executed by members of the default role
pgrowlocks
- members of the new
default role
"pg_stat_scan_tables
" may now executepgrowlocks()
(commit 25fff407)
- members of the new
pgstattuple
pg_visibility
postgres_fdw
seg
sepgsql
- partitioned table support added (commit 25542d77)
Removed: