PostgreSQL 10 New Features With Examples

1 2016-2017 Hewlett-Packard Enterprise Japan Co, Ltd. 1 May 22, 2017 PostgreSQL 10 New Features with Examples Hewlett-Packard Enterprise Japan Co, Ltd. Noriyoshi Shinoda 2016-2017 Hewlett-Packard Enterprise Japan Co, Ltd. 2 Index Index .. 2 1. About This Document .. 6 Purpose .. 6 Audience .. 6 Scope .. 6 Software Version .. 6 Question, Comment, and Responsibility .. 6 Notation .. 7 2. Version notation .. 8 3. New Features .. 9 Overview .. 9 For large amount data .. 9 For reliability improvement .. 9 For maintenance task .. 10 Incompatibility .. 10 Native Partition Table .. 12 Overview .. 12 List Partition Table .. 13 Range Partition Table .. 15 Existing tables and partitions .. 18 Operation on partition table .. 19 Execution Plan .. 22 Catalog .. 23 Restriction.

2 24 Logical Replication .. 29 Overview .. 29 Related resources .. 33 Examples .. 35 Collision and inconsistency .. 36 Restriction .. 37 Enhancement of Parallel Query .. 40 PREPARE / EXECUTE statement .. 40 Parallel Index Scan .. 41 SubPlan .. 42 2016-2017 Hewlett-Packard Enterprise Japan Co, Ltd. 3 Parallel Merge Join / Gather Merge .. 42 Parallel bitmap heap scan .. 43 Architecture .. 44 Added Catalogs .. 44 Modified catalogs .. 51 Enhancement of libpq library .. 52 Change from XLOG to WAL .. 53 Temporary replication slot .. 54 Change instance startup log .. 54 WAL of hash index .. 55 Added roles .. 55 Custom Scan Callback .. 56 Size of WAL file .. 56 56 EUI-64 data type .. 56 Unique Join .. 56 Shared Memory Address .. 57 Monitoring .. 58 Monitor wait events.

3 58 EXPLAIN SUMMARY statement .. 58 VACUUM VERBOSE statement .. 58 Quorum-based synchronous replication .. 60 Enhancement of Row Level Security .. 62 Overview .. 62 Validation of multiple POLICY setting .. 62 Enhancement of SQL 66 UPDATE statement and ROW keyword .. 66 CREATE STATISTICS statement .. 66 GENERATED AS IDENTITY column .. 68 ALTER TYPE statement .. 70 CREATE SEQUENCE statement .. 70 COPY statement .. 71 CREATE INDEX statement .. 71 CREATE TRIGGER statement .. 72 DROP FUNCTION statement .. 72 ALTER DEFAULT PRIVILEGE statement .. 73 2016-2017 Hewlett-Packard Enterprise Japan Co, Ltd. 4 CREATE SERVER 73 CREATE USER statement .. 73 Functions .. 73 Procedural language .. 79 Change of configuration parameters .. 81 Added parameters .. 81 Changed parameters .. 82 Parameters with default values changed.

4 83 Deprecated parameters .. 84 New function of authentication method .. 84 Default value of authentication setting .. 85 Other parameter change .. 85 Change of utility .. 86 psql .. 86 pg_ctl .. 88 pg_basebackup .. 88 pg_dump .. 91 pg_dumpall .. 91 pg_recvlogical .. 92 pgbench .. 92 initdb .. 92 pg_receivexlog .. 92 pg_restore .. 92 pg_upgrade .. 92 createuser .. 93 createlang / droplang .. 93 Contrib modules .. 94 postgres_fdw .. 94 file_fdw .. 95 amcheck .. 96 pageinspect .. 96 pgstattuple .. 97 btree_gist / btree_gin .. 97 pg_stat_statements .. 98 98 URL list .. 99 2016-2017 Hewlett-Packard Enterprise Japan Co, Ltd. 5 Change history .. 100 2016-2017 Hewlett-Packard Enterprise Japan Co, Ltd. 6 1. About This Document Purpose The purpose of this document is to provide information of the major new Features of PostgreSQL 10, the Beta 1 version being published.

5 Audience This document is written for engineers who already have knowledge of PostgreSQL , such as installation, basic management, etc. Scope This document describes the major difference between PostgreSQL and PostgreSQL 10 Beta 1. As a general rule, this document examines the functions that users can see when they see changes. It does not describe and verify all new Features . In particular, the following new functions are not included. Bug fix Performance improvement by changing internal behavior Improvement of regression test Operability improvement by psql command tab input Improvement of pgbench command (partly described) Improve documentation, modify typo in the sources Software Version This document is being verified for the following versions and platforms. Table 1 Version Software Versions PostgreSQL PostgreSQL (for comparison) PostgreSQL 10 Beta 1 (May 15, 2017 21:27:43) Operating System Red Hat Enterprise Linux 7 Update 1 (x86-64) Question, Comment, and Responsibility The contents of this document are not an official opinion of the Hewlett-Packard Enterprise Japan Co, Ltd.

6 The author and affiliation company do not take any responsibility about the problem caused by the mistake of contents. If you have any comments for this document, please contact to Noriyoshi Shinoda Hewlett-Packard Enterprise Japan Co, Ltd. 2016-2017 Hewlett-Packard Enterprise Japan Co, Ltd. 7 Notation This document contains Examples of the execution of the command or SQL statement. Execution Examples are described according to the following rules: Table 2 Examples notation Notation Description # Shell prompt for Linux root user $ Shell prompt for Linux general user bold User input string postgres=# psql command prompt for PostgreSQL administrator postgres=> psql command prompt for PostgreSQL general user underline Important output items The syntax is described in the following rules: Table 3 Syntax rules Notation Description Italic Replaced by the name of the object which users use, or the other syntax [ ABC ] Indicate that it can be omitted { A | B } Indicate that it is possible to select A or B.

7 General syntax, it is the same as the previous version 2016-2017 Hewlett-Packard Enterprise Japan Co, Ltd. 8 2. Version notation The notation of major version and minor version is changed from PostgreSQL 10. In the past, the first two numbers indicated major versions, but only the first number will indicate the major version in the future. Figure 1 Version notation Old Version ( is major version, 1 is minor version) . From now on (10 is major version, 0 is minor version) 9 6 1 10 0 2016-2017 Hewlett-Packard Enterprise Japan Co, Ltd. 9 3. New Features Overview More than 100 new Features have been added to PostgreSQL 10. Here are some typical new Features and benefits. For large amount data Native Partition Table Native Partition Table is provided as a method of physically partitioning a large scale table.

8 Unlike table partitioning using conventional inheritance table, performance during data insertion has been greatly improved. Providing the Native Partition Table makes building a large database easier. Logical Replication with the Logical Replication feature , it is possible to replicate only some tables between multiple instances. In traditional streaming replication, slave side instances were read-only, but tables synchronized by Logical Replication are updatable. Therefore, it is possible to create an index for analysis query to the slave side instance. Details are described in " Logical Replication". Enhancement of Parallel Query In PostgreSQL , a parallel query feature was provided to improve the query performance for large tables. Parallel query was used only in "Seq Scan" in PostgreSQL , but parallel queries are now available in many situations such as "Index Scan", "Merge Join", "Bitmap Join" and so on.

9 It is expected to improve query performance for large amount tables. Details are described in " Extended Parallel Query". For reliability improvement Quorum-based synchronous replication for arbitrarily selecting instances for synchronous replication is now available ( Quorum-base synchronous replication). Hash index that did not output WAL before PostgreSQL now output WAL. For this reason, hash index is also available in replication environments ( Hash index WAL). 2016-2017 Hewlett-Packard Enterprise Japan Co, Ltd. 10 For maintenance task Wait events that are output in the pg_stat_activity catalog has been increased significantly. Information of all backend processes can now be referred ( catalog change). A dedicated role to check the system load has been added ( Addition of role). Incompatibility Unfortunately, some Features of PostgreSQL 10 are incompatible with previous versions.

10 Change name All the name XLOG was unified to WAL. For this reason, directory names in the database cluster, utility command names, function names, parameter names, and error messages named XLOG have been changed. For example, the pg_xlog directory in the database cluster has been changed to the pg_wal directory. The pg_receivexlog command has been changed to the pg_receivewal command. The default value of the directory where the log file is output has been changed from pg_log to log. Details are described in " Change from XLOG to WALL". Default behavior of pg_basebackup utility By default WAL streaming is used in PostgreSQL 10. Also, the -x parameter has been deprecated. Details are described in " pg_basebackup". Wait mode of pg_ctl utility By default, the behavior has been changed to wait for processing to complete on all operations.