PDF page 7
### 2.1 Overview {#pg11-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 {#pg11-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 {#pg11-p7-with-hash-join-operator} #### With Append operator {#pg11-p7-with-append-operator} #### On SELECT INTO statement {#pg11-p7-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 {#pg11-2-2-improve-maintenance-task} The following features that can improve operability have been added. #### Enhancement of partitioned table {#pg11-p7-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 {#pg11-p7-provided-hash-partition-as-a-partitioning-method} #### Create PRIMARY KEY / UNIQUE KEY for the partitioned table {#pg11-p7-create-primary-key-unique-key-for-the-partitioned-table} #### Automatic creation of indexes for each partition {#pg11-p7-automatic-creation-of-indexes-for-each-partition} #### Create a FOREIGN KEY for the partitioned table {#pg11-p7-create-a-foreign-key-for-the-partitioned-table} #### DEFAULT partition that stores value not included in partitions {#pg11-p7-default-partition-that-stores-value-not-included-in-partitions}PDF page 8
#### Partition-Wise Join, Partition-Wise Aggregation for joining and aggregating partition {#pg11-p8-partition-wise-join-partition-wise-aggregation-for-joining-and-aggregating-partition} #### Provided parameter to control partition elimination {#pg11-p8-provided-parameter-to-control-partition-elimination} #### Improvement of Logical Replication feature {#pg11-p8-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 {#pg11-p8-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 {#pg11-2-3-improve-reliability} In PostgreSQL 11, integrity checking tools have been enriched to improve reliability. #### Provides block consistency check tool {#pg11-p8-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 {#pg11-p8-confirm-block-checksum-at-backup} Pg_basebackup command now checks the checksum of the backup blocks. #### amcheck module {#pg11-p8-amcheck-module} The Contrib module amcheck is provided. This module can check the consistency of the B-Tree index. ### 2.4 For application development {#pg11-2-4-for-application-development} PostgreSQL 11 also implements many new features related to application development. #### PROCEDURE object {#pg11-p8-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 {#pg11-p8-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 {#pg11-2-5-incompatibility} PostgreSQL 11 has changed the following specifications from PostgreSQL 10. #### WITH clause of CREATE FUNCTION statement {#pg11-p9-with-clause-of-create-function-statement} WITH clause of CREATE FUNCTION has been made obsolete. **Example 1.** CREATE FUNCTION statement with WITH clause ```text postgres=> CREATE FUNCTION func1() RETURNS INTEGER AS ' … ' LANGUAGE plpgsql WITH (isStrict) ; ERROR: syntax error at or near "WITH" LINE 12: WITH (isStrict) ; ``` #### Contrib module {#pg11-p9-contrib-module} The chkpass module has been deleted. #### Removed columns from some system catalogs {#pg11-p9-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 {#pg11-p9-changed-default-value} The default value of ssl_compression in libpq connection string has been changed. #### Incompatibility of TO_NUMBER function {#pg11-p9-incompatibility-of-to-number-function} The TO_NUMBER function has been changed to ignore the separator in the template.PDF page 10
**Example 2.** TO\_NUMBER function ```text 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 {#pg11-p10-incompatibility-of-to-date-to-number-to-timestamp-functions} These functions now skip multibyte characters in templates by character. **Example 3.** TO\_NUMBER function ```text 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