2 New Features Summary

PDF page 7

2.1 Overview

More than 160 new features have been added to PostgreSQL 11. Here are some typical new features and benefits. This version focuses to enhance of various new features added in PostgreSQL 10.

2.1 Improve analytic query performance

PostgreSQL 11 has been enhanced to improve the performance of long-running analytical queries. The scope to which parallel query is applied has been greatly expanded. This allows the more efficient use of multiple processors in analytical queries and better throughput. Parallel queries can be executed with the following conditions.

With Hash Join operator

With Append operator

On SELECT INTO statement

At the same time, CREATE TABLE AS SELECT, CREATE INDEX, and CREATE MATERIALIZED VIEW statements that are executed to maintain large tables can also be executed in parallel. For the parallel query, see Section “2.2 Enhancements of Parallel query”. Also, JIT compilation feature by LLVM is now available for SQL statements that occupy CPU for a long time. SQL statements with high expected cost are compiled and executed by LLVM. For details of JIT compilation, refer to “2.5.3 LLVM Integration”.

2.2 Improve maintenance task

The following features that can improve operability have been added.

Enhancement of partitioned table

A declarative partitioning feature that can manage a table storing a large amount of data by dividing it appeared in PostgreSQL 10. However, the partitioning feature of PostgreSQL 10 had many restrictions. In PostgreSQL 11, many restrictions have been resolved, and the following enhancements were implemented.

Provided HASH partition as a partitioning method

Create PRIMARY KEY / UNIQUE KEY for the partitioned table

Automatic creation of indexes for each partition

Create a FOREIGN KEY for the partitioned table

DEFAULT partition that stores value not included in partitions

PDF page 8

Partition-Wise Join, Partition-Wise Aggregation for joining and aggregating partition

Provided parameter to control partition elimination

Improvement of Logical Replication feature

TRUNCATE statement is now propagated in the Logical Decoding environment and the Logical Replication environment. This feature makes synchronization of replication data easier. In PostgreSQL 10, TRUNCATE statement was not transferred to the remote instance.

Improved pg_prewarm Contrib module

Pg_prewarm module saves automatically the information of the page cached in the shared buffer and automatically caches the page when restarting the instance.

2.3 Improve reliability

In PostgreSQL 11, integrity checking tools have been enriched to improve reliability.

Provides block consistency check tool

Pg_verify_checksums command to check the integrity of the block is provided. This command must be executed after stopping the instance.

Confirm block checksum at backup

Pg_basebackup command now checks the checksum of the backup blocks.

amcheck module

The Contrib module amcheck is provided. This module can check the consistency of the B-Tree index.

2.4 For application development

PostgreSQL 11 also implements many new features related to application development.

PROCEDURE object

A new object PROCEDURE has been added. It is an object similar to FUNCTION without a return value. PROCEDURE can control transactions within a program.

Enhancement of PL/pgSQL Language

A CONSTANT clause that defines constant value and a NOT NULL clause that can detect variable initialization misses have been added to PL/pgSQL language.

PDF page 9

2.5 Incompatibility

PostgreSQL 11 has changed the following specifications from PostgreSQL 10.

WITH clause of CREATE FUNCTION statement

WITH clause of CREATE FUNCTION has been made obsolete.

postgres=> CREATE FUNCTION func1() RETURNS INTEGER AS '
  …
' LANGUAGE plpgsql
WITH (isStrict) ;
ERROR:  syntax error at or near "WITH"
LINE 12: WITH (isStrict) ;

Contrib module

The chkpass module has been deleted.

Removed columns from some system catalogs

Some columns have been removed from the pg_class catalog and pg_proc catalog. Please refer to “2.5.1 Changing system catalog”.

Changed default value

The default value of ssl_compression in libpq connection string has been changed.

Incompatibility of TO_NUMBER function

The TO_NUMBER function has been changed to ignore the separator in the template.

PDF page 10

PostgreSQL 10
postgres=> SELECT to_number('1234', '9,999') ;
to_number
-----------
       134
(1 row)
PostgreSQL 11
postgres=> SELECT to_number('1234', '9,999') ;
to_number
-----------
      1234
(1 row)

Incompatibility of TO_DATE / TO_NUMBER / TO_TIMESTAMP functions

These functions now skip multibyte characters in templates by character.

PostgreSQL 10
postgres=> SELECT to_number('1234', 'あ 999') ;
to_number
-----------
         4
(1 row)
PostgreSQL 11
postgres=> SELECT to_number('1234', 'あ 999') ;
to_number
-----------
       234
(1 row)

PDF page 11