这篇文章主要介绍“PostgreSQL11有哪些新特性”，在日常操作中，相信很多人在PostgreSQL11有哪些新特性问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”PostgreSQL11有哪些新特性”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

Parallel Hash
Hash Join执行时，在构造Hash表和进行Hash连接时，PG 11可使用并行的方式执行。
测试脚本：

testdb=#createtablet1(c1int,c2varchar(40),c3varchar(40));CREATETABLEtestdb=#testdb=#insertintot1selectgenerate_series(1,5000000),'TEST'||generate_series(1,1000000),generate_series(1,1000000)||'TEST';INSERT05000000testdb=#droptableifexistst2;DROPTABLEtestdb=#createtablet2(c1int,c2varchar(40),c3varchar(40));CREATETABLEtestdb=#testdb=#insertintot2selectgenerate_series(1,1000000),'T2'||generate_series(1,1000000),generate_series(1,1000000)||'T2';INSERT01000000testdb=#explainverbosetestdb-#selectt1.c1,t2.c1testdb-#fromt1innerjoint2ont1.c1=t2.c1;QUERYPLAN---------------------------------------------------------------------------------------------Gather(cost=18372.00..107975.86rows=101100width=8)Output:t1.c1,t2.c1WorkersPlanned:2--2Workers->ParallelHashJoin(cost=17372.00..96865.86rows=42125width=8)--ParallelHashJoinOutput:t1.c1,t2.c1HashCond:(t1.c1=t2.c1)->ParallelSeqScanonpublic.t1(cost=0.00..45787.33rows=2083333width=4)Output:t1.c1->ParallelHash(cost=10535.67..10535.67rows=416667width=4)--ParallelHashOutput:t2.c1->ParallelSeqScanonpublic.t2(cost=0.00..10535.67rows=416667width=4)Output:t2.c1

除了Parallel Hash外,PG 11在执行Parallel Append(执行UNION ALL等集合操作)/CREATE TABLE AS SELECT/CREATE MATERIALIZED VIEW/SELECT INTO/CREATE INDEX等操作时以并行的方式执行.

Hash Partition
PG 在11.x引入了Hash分区,关于Hash分区,官方文档有如下说明:

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.

每个Hash分区需指定"模"(modulus)和"余"(remainder),数据在哪个分区(partition index)的计算公式:
partition index = abs(hashfunc(key)) % modulus

droptableifexistst_hash2;createtablet_hash2(c1int,c2varchar(40),c3varchar(40))partitionbyhash(c1);createtablet_hash2_1partitionoft_hash2forvalueswith(modulus6,remainder0);createtablet_hash2_2partitionoft_hash2forvalueswith(modulus6,remainder1);createtablet_hash2_3partitionoft_hash2forvalueswith(modulus6,remainder2);createtablet_hash2_4partitionoft_hash2forvalueswith(modulus6,remainder3);createtablet_hash2_5partitionoft_hash2forvalueswith(modulus6,remainder4);createtablet_hash2_6partitionoft_hash2forvalueswith(modulus6,remainder5);testdb=#insertintot_hash2testdb-#selectgenerate_series(1,1000000),'HASH'||generate_series(1,1000000),generate_series(1,1000000)||'HASH';INSERT01000000

数据在各分区上的分布大体均匀.
2018-9-19 注:由于插入数据时语句出错,昨天得出的结果有误(但数据在各个分区的分布上不太均匀,t_hash2_1分区行数明显的比其他分区的要多很多),请忽略

testdb=#selectcount(*)fromonlyt_hash2;;count-------0(1row)testdb=#selectcount(*)fromonlyt_hash2_1;count--------166480(1row)testdb=#selectcount(*)fromonlyt_hash2_2;count--------166904(1row)testdb=#selectcount(*)fromonlyt_hash2_3;count--------166302(1row)testdb=#selectcount(*)fromonlyt_hash2_4;count--------166783(1row)testdb=#selectcount(*)fromonlyt_hash2_5;count--------166593(1row)testdb=#selectcount(*)fromonlyt_hash2_6;count--------166938(1row)

Hash分区键亦可以创建在字符型字段上

testdb=#droptableifexistst_hash4;DROPTABLEtestdb=#createtablet_hash4(c1int,c2varchar(40),c3varchar(40))partitionbyhash(c2);CREATETABLE--需创建相应的"Partition"用于存储相应的数据testdb=#insertintot_hash4testdb-#selectgenerate_series(1,100000),'HASH'||generate_series(1,1000000),generate_series(1,1000000)||'HASH';ERROR:nopartitionofrelation"t_hash4"foundforrowDETAIL:Partitionkeyofthefailingrowcontains(c2)=(HASH1).--6个分区,3个sub-table,插入数据会出错testdb=#testdb=#createtablet_hash4_1partitionoft_hash4forvalueswith(modulus6,remainder0);CREATETABLEtestdb=#createtablet_hash4_2partitionoft_hash4forvalueswith(modulus6,remainder1);CREATETABLEtestdb=#createtablet_hash4_3partitionoft_hash4forvalueswith(modulus6,remainder2);CREATETABLEtestdb=#insertintot_hash4testdb-#selectgenerate_series(1,10000),'HASH'||generate_series(1,10000),generate_series(1,10000)||'HASH';ERROR:nopartitionofrelation"t_hash4"foundforrowDETAIL:Partitionkeyofthefailingrowcontains(c2)=(HASH1).--3个分区,3个sub-table,正常testdb=#droptableifexistst_hash4;DROPTABLEtestdb=#createtablet_hash4(c1int,c2varchar(40),c3varchar(40))partitionbyhash(c2);CREATETABLEtestdb=#createtablet_hash4_1partitionoft_hash4forvalueswith(modulus3,remainder0);CREATETABLEtestdb=#createtablet_hash4_2partitionoft_hash4forvalueswith(modulus3,remainder1);CREATETABLEtestdb=#createtablet_hash4_3partitionoft_hash4forvalueswith(modulus3,remainder2);CREATETABLEtestdb=#insertintot_hash4testdb-#selectgenerate_series(1,10000),'HASH'||generate_series(1,10000),generate_series(1,10000)||'HASH';INSERT010000

考察分区的数据分布,还比较均匀:

testdb=#testdb=#selectcount(*)fromonlyt_hash4;count-------0(1row)testdb=#selectcount(*)fromonlyt_hash4_1;count-------3378(1row)testdb=#selectcount(*)fromonlyt_hash4_2;count-------3288(1row)testdb=#selectcount(*)fromonlyt_hash4_3;count-------3334(1row)

Default Partition
List和Range分区可指定Default Partition(Hash分区不支持).

Update partition key
PG 11可Update分区键,这会导致数据的"迁移".

Create unique constraint
PG 11在分区表上创建主键和唯一索引(注:Oracle在很早的版本已支持此特性).
在普通字段上可以创建BTree索引.

testdb=#altertablet_hash2addprimarykey(c1);ALTERTABLEtestdb=#createindexidx_t_hash2_c2ont_hash2(c2);CREATEINDEX

FOREIGN KEY support
PG 11支持在分区上创建外键.

除了上述几个新特性外,分区上面,PG 11在Automatic index creation/INSERT ON CONFLICT/Partition-Wise Join / Partition-Wise Aggregate/FOR EACH ROW trigger/Dynamic Partition Elimination/Control Partition Pruning上均有所增强.

到此，关于“PostgreSQL11有哪些新特性”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！