PostgreSQL DBA(4) - PG 11 New Features#1

PG 11即將正式釋出，本節簡單了PG 11的一些新特性，包括並行查詢的效能提升和資料表分割槽的功能增強。

一、並行查詢

Parallel Hash
Hash Join執行時，在構造Hash表和進行Hash連線時，PG 11可使用並行的方式執行。
測試指令碼：

testdb=# create table t1 (c1 int,c2 varchar(40),c3 varchar(40));
CREATE TABLE
testdb=# 
testdb=# insert into t1 select generate_series(1,5000000),'TEST'||generate_series(1,1000000),generate_series(1,1000000)||'TEST';
INSERT 0 5000000

testdb=# drop table if exists t2;
DROP TABLE
testdb=# create table t2 (c1 int,c2 varchar(40),c3 varchar(40));
CREATE TABLE
testdb=# 
testdb=# insert into t2 select generate_series(1,1000000),'T2'||generate_series(1,1000000),generate_series(1,1000000)||'T2';
INSERT 0 1000000

testdb=# explain verbose
testdb-# select t1.c1,t2.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

除了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

drop table if exists t_hash1;
create table t_hash1 (c1 int,c2  varchar(40),c3 varchar(40)) partition by hash(c1);
create table t_hash1_1 partition of t_hash1 for values with (modulus 6,remainder 0);
create table t_hash1_2 partition of t_hash1 for values with (modulus 6,remainder 1);
create table t_hash1_3 partition of t_hash1 for values with (modulus 6,remainder 2);
create table t_hash1_4 partition of t_hash1 for values with (modulus 6,remainder 3);
create table t_hash1_5 partition of t_hash1 for values with (modulus 6,remainder 4);
create table t_hash1_6 partition of t_hash1 for values with (modulus 6,remainder 5);

testdb=# insert into t_hash1 
testdb-# select generate_series(1,1000000),'HASH'||generate_series(1,1000000),generate_series(1,1000000)||'HASH';
INSERT 0 1000000

資料在各分割槽上的分佈大體均勻.
2018-9-19 注:由於插入資料時語句出錯,昨天得出的結果有誤(但資料在各個分割槽的分佈上不太均勻,t_hash1_1分割槽行數明顯的比其他分割槽的要多很多),請忽略

testdb=# select count(*) from only t_hash1;
; count 
-------
     0
(1 row)

testdb=# select count(*) from only t_hash1_1;
 count  
--------
 166480
(1 row)

testdb=# select count(*) from only t_hash1_2;
 count  
--------
 166904
(1 row)

testdb=# select count(*) from only t_hash1_3;
 count  
--------
 166302
(1 row)

testdb=# select count(*) from only t_hash1_4;
 count  
--------
 166783
(1 row)

testdb=# select count(*) from only t_hash1_5;
 count  
--------
 166593
(1 row)

testdb=# select count(*) from only t_hash1_6;
 count  
--------
 166938
(1 row)

Hash分割槽鍵亦可以建立在字元型欄位上

testdb=# drop table if exists t_hash3;
DROP TABLE
testdb=# create table t_hash3 (c1 int,c2  varchar(40),c3 varchar(40)) partition by hash(c2);
CREATE TABLE

-- 需建立相應的"Partition"用於儲存相應的資料
testdb=# insert into t_hash3 
testdb-# select generate_series(1,100000),'HASH'||generate_series(1,1000000),generate_series(1,1000000)||'HASH';
ERROR:  no partition of relation "t_hash3" found for row
DETAIL:  Partition key of the failing row contains (c2) = (HASH1).

-- 6個分割槽,3個sub-table,插入資料會出錯
testdb=# 
testdb=# create table t_hash3_1 partition of t_hash3 for values with (modulus 6,remainder 0);
CREATE TABLE
testdb=# create table t_hash3_2 partition of t_hash3 for values with (modulus 6,remainder 1);
CREATE TABLE
testdb=# create table t_hash3_3 partition of t_hash3 for values with (modulus 6,remainder 2);
CREATE TABLE
testdb=# insert into t_hash3 
testdb-# select generate_series(1,10000),'HASH'||generate_series(1,10000),generate_series(1,10000)||'HASH';
ERROR:  no partition of relation "t_hash3" found for row
DETAIL:  Partition key of the failing row contains (c2) = (HASH1).

-- 3個分割槽,3個sub-table,正常
testdb=# drop table if exists t_hash3;
DROP TABLE
testdb=# create table t_hash3 (c1 int,c2  varchar(40),c3 varchar(40)) partition by hash(c2);
CREATE TABLE
testdb=# create table t_hash3_1 partition of t_hash3 for values with (modulus 3,remainder 0);
CREATE TABLE
testdb=# create table t_hash3_2 partition of t_hash3 for values with (modulus 3,remainder 1);
CREATE TABLE
testdb=# create table t_hash3_3 partition of t_hash3 for values with (modulus 3,remainder 2);
CREATE TABLE
testdb=# insert into t_hash3 
testdb-# select generate_series(1,10000),'HASH'||generate_series(1,10000),generate_series(1,10000)||'HASH';
INSERT 0 10000

考察分割槽的資料分佈,還比較均勻:

testdb=# 
testdb=# select count(*) from only t_hash3;
 count 
-------
     0
(1 row)

testdb=# select count(*) from only t_hash3_1;
 count 
-------
  3378
(1 row)

testdb=# select count(*) from only t_hash3_2;
 count 
-------
  3288
(1 row)

testdb=# select count(*) from only t_hash3_3;
 count 
-------
  3334
(1 row)

Default Partition
List和Range分割槽可指定Default Partition(Hash分割槽不支援).

Update partition key
PG 11可Update分割槽鍵,這會導致資料的"遷移".

Create unique constraint
PG 11在分割槽表上建立主鍵和唯一索引(注:Oracle在很早的版本已支援此特性).
在普通欄位上可以建立BTree索引.

testdb=# alter table t_hash1 add primary key(c1);
ALTER TABLE
testdb=# create index idx_t_hash1_c2 on t_hash1(c2);
CREATE INDEX

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上均有所增強.

三、參考資料

PostgreSQL 11 New Features With Examples(Beta 1)
PostgreSQL 11 Table Partitioning