Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the new features of PostgreSQL11". In daily operation, I believe many people have doubts about the new features of PostgreSQL11. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubts about "what are the new features of PostgreSQL11?" Next, please follow the editor to study!

1. Parallel query

Parallel Hash

When Hash Join is executed, PG 11 can be executed in parallel when constructing Hash tables and making Hash connections.

Test script:

Testdb=# create table T1 (C1 int,c2 varchar (40), c3 varchar (40)); CREATE TABLEtestdb=# testdb=# insert into T1 select generate_series (1Magne 5000000), 'TEST' | | generate_series (1Magne 1000000), generate_series (1Magazine 1000000) | |' TEST';INSERT 0 5000000testdb=# drop table if exists T2 / drop TABLEtestdb=# create table T2 (C1 int,c2 varchar (40), c3 varchar (40)); CREATE TABLEtestdb=# testdb=# insert into T2 select generate_series (1prim 1000000),'T2'| | generate_series (1Mae 1000000), generate_series (1Magne1000000) | | "T2'" INSERT 0 1000000testdb=# explain verbosetestdb-# select t1.c1 testdb-# from T1 inner join T2 on t1.c1 = t2.c1 QUERY PLAN -Gather (cost=18372.00..107975.86 rows=101100 width=8) Output: t1.c1 T2.c1 Workers Planned: 2-- 2 Workers-> Parallel Hash Join (cost=17372.00..96865.86 rows=42125 width=8)-- Parallel Hash Join Output: t1.c1 T2.c1 Hash Cond: (t1.c1 = t2.c1)-> Parallel Seq Scan on public.t1 (cost=0.00..45787.33 rows=2083333 width=4) Output: t1.c1-> Parallel Hash (cost=10535.67..10535.67 rows=416667 width=4)-- Parallel Hash Output: t2.c1-> Parallel Seq Scan on public.t2 (cost=0.00.. 10535.67 rows=416667 width=4) Output: t2.c1

In addition to Parallel Hash, PG 11 performs operations such as Parallel Append (performing set operations such as UNION ALL) / CREATE TABLE AS SELECT/CREATE MATERIALIZED VIEW/SELECT INTO/CREATE INDEX in a parallel manner.

2. Data table partition

Hash Partition

PG introduced Hash partition in 11.x. The official document on Hash partition is as follows:

The table is partitioned by specifying a modulus and a remainder for each partition. Each partition will hold the rows for which the hash value of the partition key divided by the specified modulus will produce the specified remainder.

Each Hash partition needs to specify "modulus" and "remainder", and the formula for calculating the data in which partition (partition index):

Partition index = abs (hashfunc (key))% modulus

Drop table if exists tweak hash2: create table t_hash2 (C1 int,c2 varchar (40), c3 varchar (40)) partition by hash (C1); create table t_hash2_1 partition of t_hash2 for values with (modulus 6); create table t_hash2_2 partition of t_hash2 for values with (modulus 6); create table t_hash2_3 partition of t_hash2 for values with (modulus 6); create table t_hash2_4 partition of t_hash2 for values with (modulus 6) Create table t_hash2_5 partition of t_hash2 for values with (modulus 6); create table t_hash2_6 partition of t_hash2 for values with (modulus 6); testdb=# insert into t_hash2 testdb-# select generate_series (1 million), 'HASH' | | generate_series (1 million), generate_series (1 million) | |' HASH';INSERT 0 1000000'

The distribution of data in each partition is generally uniform.

2018-9-19 Note: due to a statement error when inserting data, the result obtained yesterday is incorrect (but the data is not evenly distributed in each partition, and the number of rows in t_hash2_1 partition is obviously much more than that in other partitions), please ignore

Testdb=# select count (*) from only tincture hash2; count-0 (1 row) testdb=# select count (*) from only tincture hash2; count-166480 (1 row) testdb=# select count (*) from only tincture hash2; count-166904 (1 row) hash2 (*) from only t_hash2_3 Count-166302 (1 row) testdb=# select count (*) from only tweets hash2: 4; count-166783 (1 row) testdb=# select count (*) from only tweehash2: 5; count-166593 (1 row) testdb=# select count (*) from only tweets hash2: 4; count-166938 (1 hash2)

The Hash partition key can also be created on a character field

Testdb=# drop table if exists tweak hash4: drop TABLEtestdb=# create table t_hash4 (C1 int,c2 varchar (40), c3 varchar (40)) partition by hash (c2); CREATE TABLE-- needs to create a corresponding "Partition" to store the corresponding data testdb=# insert into t_hash4 testdb-# select generate_series (1Mague 100000), 'HASH' | | generate_series (1Mague 1000000), generate_series (1Lore1 000000) | |' HASH' | ERROR: no partition of relation "t_hash4" found for rowDETAIL: Partition key of the failing row contains (c2) = (HASH1).-- 6 partitions, 3 sub-table, inserting data will make an error testdb=# testdb=# create table t_hash4_1 partition of t_hash4 for values with (modulus 6); CREATE TABLEtestdb=# create table t_hash4_2 partition of t_hash4 for values with (modulus 6); CREATE TABLEtestdb=# create table t_hash4_3 partition of t_hash4 for values with (modulus 6) CREATE TABLEtestdb=# insert into t_hash4 testdb-# select generate_series (110000), 'HASH' | | generate_series (110000), generate_series (110000) | |' HASH';ERROR: no partition of relation "t_hash4" found for rowDETAIL: Partition key of the failing row contains (c2) = (HASH1).-- 3 partitions, 3 sub-table, normal testdb=# drop table if exists tweehash4) drop TABLEtestdb=# create table t_hash4 (C1 int,c2 varchar (40), c3 varchar (40)) partition by hash (c2) CREATE TABLEtestdb=# create table t_hash4_1 partition of t_hash4 for values with (modulus 3); CREATE TABLEtestdb=# create table t_hash4_2 partition of t_hash4 for values with (modulus 3); CREATE TABLEtestdb=# create table t_hash4_3 partition of t_hash4 for values with (modulus 3); CREATE TABLEtestdb=# insert into t_hash4 testdb-# select generate_series (1), 'HASH' | | generate_series (1), generate_series (1) |' HASH';INSERT 0 10000]

Examine the data distribution of the partition, and it is relatively uniform:

Testdb=# testdb=# select count (*) from only tweehash4; count-0 (1 row) testdb=# select count (*) from only tweehash4; count-3378 (1 row) testdb=# select count (*) from only tweehash4; count-3288 (1 row) testdb=# select count (*) from only tweehash4; count-3334 (1 hash4)

Default Partition

List and Range partitions can specify Default Partition (not supported by Hash partitions).

Update partition key

PG 11 can Update partitioning keys, which can lead to data migration.

Create unique constraint

PG 11 creates primary keys and unique indexes on partitioned tables (Note: Oracle supports this feature in early versions).

You can create a BTree index on a normal field.

Testdb=# alter table t_hash2 add primary key (C1); ALTER TABLEtestdb=# create index idx_t_hash2_c2 on t_hash2 (c2); CREATE INDEX

FOREIGN KEY support

PG 11 supports the creation of foreign keys on partitions.

In addition to the above new features, PG 11 has been enhanced on Automatic index creation/INSERT ON CONFLICT/Partition-Wise Join / Partition-Wise Aggregate/FOR EACH ROW trigger/Dynamic Partition Elimination/Control Partition Pruning above the partition.

At this point, the study on "what are the new features of PostgreSQL11" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.