3 Overview

PDF page 9

More than 100 new features have been added to PostgreSQL 10. Here are some typical new features and benefits.

3.1.1 For large amount data

Native Partition Table

Native Partition Table is provided as a method of physically partitioning a large scale table. 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 “3.3 Logical Replication”.

Enhancement of Parallel Query

In PostgreSQL 9.6, a parallel query feature was provided to improve the query performance for large tables. Parallel query was used only in “Seq Scan” in PostgreSQL 9.6, but parallel queries are now available in many situations such as “Index Scan”, “Merge Join”, “Bitmap Join” and so on. It is expected to improve query performance for large amount tables. Details are described in “3.4 Extended Parallel Query”.

3.1.2 For reliability improvement

Quorum-based synchronous replication for arbitrarily selecting instances for synchronous replication is now available (3.7 Quorum-base synchronous replication). Hash index that did not output WAL before PostgreSQL 9.6 now output WAL. For this reason, hash index is also available in replication environments (3.5.7 Hash index WAL).

PDF page 10

3.1.3 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 (3.5.2 catalog change). A dedicated role to check the system load has been added (3.5.8 Addition of role).

3.1.4 Incompatibility

Unfortunately, some features of PostgreSQL 10 are incompatible with previous versions.

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 “3.5.4 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 “3.11.3 pg_basebackup”.

Wait mode of pg_ctl utility

By default, the behavior has been changed to wait for processing to complete on all operations. In the previous version, pg_ctl command did not wait for the completion of processing in instance startup processing etc. Details are described in “3.11.2 pg_ctl”.

Deprecated of plain password store

It is no longer possible to save the password without encrypting it. This will improve security. Details are described in “3.9.12 CREATE USER statement” and “3.10.2 Changed parameters”.

Deprecated parameters

The parameter min_parallel_relation_size has been changed to min_parallel_table_scan_size. The parameter sql_inheritance has been deprecated. Details are described in “3.10.4 Obsolete parameters”.

PDF page 11

Functions behavior changed

The to_date and to_timestamp functions have changed behavior. As a result of strict checking of the numerics of each element part of the date/time, errors will occur with values that were not problematic in the previous version. Also, the make_date function can now specify a date in BC. Details are described in “3.9.13 Function”.