PostgreSQL 10 分區表探密
作者：yonj1e
PostgreSQL的分區需要先建立主表，然后再建立子表，使用繼承的特性，但不需要手動寫觸發器/規則了，目前支持range、list分區，10 正式版發布時不知道會不會有其他的，后面我會介紹我基于10 Beta2添加的hash分區。
分區介紹
PostgreSQL的分區需要先建立主表，然后再建立子表，使用繼承的特性，但不需要手動寫觸發器/規則了，目前支持range、list分區，10 正式版發布時不知道會不會有其他的，后面我會介紹我基于10 Beta2添加的hash分區。
range分區
分區語法：
- postgres=# create table r (r_id int, r_name name, r_date date) partition by range (r_id);
- CREATE TABLE
- postgres=# create table r1 partition of r for values from (1) to (10);
- CREATE TABLE
- postgres=# create table r2 partition of r for values from (10) to (20);
- CREATE TABLE
- postgres=# create table r3 partition of r for values from (20) to (30);
- CREATE TABLE
- postgres=# insert into r select id, md5(random()::text), now() + (id||'day')::interval from generate_series(1, 29) t(id);
- INSERT 0 29
- postgres=# select *, tableoid::regclass from r;
- r_id | r_name | r_date | tableoid
- ------+----------------------------------+------------+----------
- 1 | 1d0d0680930198d2962b3b5f9cf82083 | 2017-08-09 | r1
- 2 | 47ba81de41d71bd51b18c7861a594bdf | 2017-08-10 | r1
- 3 | 820b0b1affe3bf0e5705aee3e77b0b29 | 2017-08-11 | r1
- 4 | 0cc06451bd0652d2583a733374d787b3 | 2017-08-12 | r1
- 5 | 642108381b2fc203b830f1215a0d7c6a | 2017-08-13 | r1
- 6 | 57e3869b2ab8ee1c0bca96b1cf022a5d | 2017-08-14 | r1
- 7 | 5357fa6de3c1c559edb78cddb4eae902 | 2017-08-15 | r1
- 8 | 6ea5a7dba4dfc6c81ca5932be86a9341 | 2017-08-16 | r1
- 9 | d3d4dcb9dc48e0629042ede7ed9c7a33 | 2017-08-17 | r1
- 10 | 248d6f3e072c6c137a3402d11fc5b1d7 | 2017-08-18 | r2
- 11 | ae3a671045ded43260bc4d0bbcb7e428 | 2017-08-19 | r2
- 12 | acdc89bb326d9f0caaeeb86bfeac3a76 | 2017-08-20 | r2
- 13 | 147b6e975d7299db66e170874b913b25 | 2017-08-21 | r2
- 14 | 6041a6b84b1af615bdb34a5926d72a33 | 2017-08-22 | r2
- 15 | 3d96e08395af120dd36e10a0252ce29c | 2017-08-23 | r2
- 16 | 5e613d10c9cac126453413ddfc17c210 | 2017-08-24 | r2
- 17 | e92fc34d180be652e72a63b92d327f1b | 2017-08-25 | r2
- 18 | 3109c4e8f4da701721151df11a4d266f | 2017-08-26 | r2
- 19 | 35ba5892f3b88aa3254445fbf5267eea | 2017-08-27 | r2
- 20 | c92d1df47257784bb11d7bfbb52b5710 | 2017-08-28 | r3
- 21 | d076a5498d17ade8f317bf47cfa322c3 | 2017-08-29 | r3
- 22 | a66c2e83f1e54e1392964ed71d5b8e20 | 2017-08-30 | r3
- 23 | 6a94df0f08921728aa0af9455d05c9f8 | 2017-08-31 | r3
- 24 | 248c46d80b926c66c093c500f309614d | 2017-09-01 | r3
- 25 | 4da3be147fd1831e8605fc400e7a7503 | 2017-09-02 | r3
- 26 | 3029d7e22b7c963e8983200a93894669 | 2017-09-03 | r3
- 27 | 720d6d04249e9f3595a19cf59f075332 | 2017-09-04 | r3
- 28 | 95b5e5492591c38ddd864d83265e26c4 | 2017-09-05 | r3
- 29 | 2628c14bd3f67699ab0411b6fd402460 | 2017-09-06 | r3
- (29 rows)
- postgres=# explain select * from r where id = 20;
- ERROR: column "id" does not exist
- LINE 1: explain select * from r where id = 20;
- ^
- postgres=# explain select * from r where r_id = 20;
- QUERY PLAN
- ----------------------------------------------------------
- Append (cost=0.00..20.12 rows=4 width=72)
- -> Seq Scan on r3 (cost=0.00..20.12 rows=4 width=72)
- Filter: (r_id = 20)
- (3 rows)
- postgres=# set constraint_exclusion = off;
- SET
- postgres=# explain select * from r where r_id = 20;
- QUERY PLAN
- ----------------------------------------------------------
- Append (cost=0.00..60.38 rows=12 width=72)
- -> Seq Scan on r1 (cost=0.00..20.12 rows=4 width=72)
- Filter: (r_id = 20)
- -> Seq Scan on r2 (cost=0.00..20.12 rows=4 width=72)
- Filter: (r_id = 20)
- -> Seq Scan on r3 (cost=0.00..20.12 rows=4 width=72)
- Filter: (r_id = 20)
- (7 rows)
- postgres=#
- postgres=# create index on r1 (r_id);
- CREATE INDEX
- postgres=# explain select * from r where r_id = 5;
- QUERY PLAN
- ----------------------------------------------------------------------------------
- Append (cost=5.53..25.54 rows=161 width=72)
- -> Bitmap Heap Scan on r1 (cost=5.53..25.54 rows=161 width=72)
- Recheck Cond: (r_id = 5)
- -> Bitmap Index Scan on r1_r_id_idx (cost=0.00..5.48 rows=161 width=0)
- Index Cond: (r_id = 5)
- (5 rows)
- postgres=# \d+ r*
- Table "public.r"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
- --------+---------+-----------+----------+---------+---------+--------------+-------------
- r_id | integer | | | | plain | |
- r_name | name| | | | plain | |
- r_date | date| | | | plain | |
- Partition key: RANGE (r_id)
- Partitions: r1 FOR VALUES FROM (1) TO (10),
- r2 FOR VALUES FROM (10) TO (20),
- r3 FOR VALUES FROM (20) TO (30)
- Table "public.r1"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
- --------+---------+-----------+----------+---------+---------+--------------+-------------
- r_id | integer | | | | plain | |
- r_name | name| | | | plain | |
- r_date | date| | | | plain | |
- Partition of: r FOR VALUES FROM (1) TO (10)
- Partition constraint: ((r_id IS NOT NULL) AND (r_id >= 1) AND (r_id < 10))
- Indexes:
- "r1_r_id_idx" btree (r_id)
- Index "public.r1_r_id_idx"
- Column | Type | Definition | Storage
- --------+---------+------------+---------
- r_id | integer | r_id | plain
- btree, for table "public.r1"
- Table "public.r2"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
- --------+---------+-----------+----------+---------+---------+--------------+-------------
- r_id | integer | | | | plain | |
- r_name | name| | | | plain | |
- r_date | date| | | | plain | |
- Partition of: r FOR VALUES FROM (10) TO (20)
- Partition constraint: ((r_id IS NOT NULL) AND (r_id >= 10) AND (r_id < 20))
- Table "public.r3"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
- --------+---------+-----------+----------+---------+---------+--------------+-------------
- r_id | integer | | | | plain | |
- r_name | name| | | | plain | |
- r_date | date| | | | plain | |
- Partition of: r FOR VALUES FROM (20) TO (30)
- Partition constraint: ((r_id IS NOT NULL) AND (r_id >= 20) AND (r_id < 30))
說明：
- 創建分區時必須指定主表
- 分區表和主表列數量、定義必須完全一致
- 分區表的列可以單獨添加約束、索引
- 向主表插入數據，自動插入到對應分區，如果找不到對應分區，拋出錯誤
- range分區范圍>=最小值、<***值
- 修改主表字段名、字段類型，會自動修改所有分區
- truncate主表會清除所有分區表數據
- drop主表會把所有子表一起drop
- \d、\d+ 可查看分區表詳細定義
在PostgreSQL10的分區表功能中， 范圍分區的KEY支持由多個字段組成，多列組成的KEY可看做是范圍分區表的組合約束。
- [postgres@localhost bin]$ ./psql
- psql (10beta2)
- Type "help" for help.
- postgres=# create table r(a int, b int) partition by range (a, b);
- CREATE TABLE
- postgres=# create table r1 partition of r for values from (1, 60) to (10, 80);
- CREATE TABLE
- postgres=# create table r2 partition of r for values from (10, 80) to (20, 60);
- CREATE TABLE
- postgres=# \d+ r*
- Table "public.r"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
- --------+---------+-----------+----------+---------+---------+--------------+-------------
- a | integer | | | | plain | |
- b | integer | | | | plain | |
- Partition key: RANGE (a, b)
- Partitions: r1 FOR VALUES FROM (1, 60) TO (10, 80),
- r2 FOR VALUES FROM (10, 80) TO (20, 60)
- Table "public.r1"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
- --------+---------+-----------+----------+---------+---------+--------------+-------------
- a | integer | | | | plain | |
- b | integer | | | | plain | |
- Partition of: r FOR VALUES FROM (1, 60) TO (10, 80)
- Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND ((a > 1) OR ((a = 1) AND (b >= 60))) AND ((a < 10) OR ((a = 10) AND (b < 80))))
- Table "public.r2"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
- --------+---------+-----------+----------+---------+---------+--------------+-------------
- a | integer | | | | plain | |
- b | integer | | | | plain | |
- Partition of: r FOR VALUES FROM (10, 80) TO (20, 60)
- Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND ((a > 10) OR ((a = 10) AND (b >= 80))) AND ((a < 20) OR ((a = 20) AND (b < 60))))
- postgres=# insert into r values (10, 70);
- INSERT 0 1
- postgres=# insert into r values (10, 80);
- INSERT 0 1
- postgres=# insert into r values (10, 90);
- INSERT 0 1
- postgres=# select tableoid::regclass, * from r;
- tableoid | a | b
- ----------+----+----
- r1 | 10 | 70
- r2 | 10 | 80
- r2 | 10 | 90
- (7 rows)
- postgres=#
這里需要注意它的分區約束，from (10, 80) to (20, 60)，最初還以為是有bug，其實不是，Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND ((a > 10) OR ((a = 10) AND (b >= 80))) AND ((a < 20) OR ((a = 20) AND (b < 60))))
list分區
語法：
- postgres=# create table l (l_id int, l_name name, l_date date) partition by list (l_id);
- CREATE TABLE
- postgres=# create table l1 partition of l for values in (1);
- CREATE TABLE
- postgres=# create table l2 partition of l for values in (2);
- CREATE TABLE
- postgres=# create table l3 partition of l for values in (3);
- CREATE TABLE
- postgres=# create table l4 partition of l for values in (4);
- CREATE TABLE
- postgres=# insert into l select id, md5(random()::text), now() + (id||'day')::interval from generate_series(1, 5) t(id);
- ERROR: no partition of relation "l" found for row
- DETAIL: Partition key of the failing row contains (l_id) = (5).
- postgres=# insert into l select id, md5(random()::text), now() + (id||'day')::interval from generate_series(1, 4) t(id);
- INSERT 0 4
- postgres=# explain select * from l where l_id = 2;
- QUERY PLAN
- ----------------------------------------------------------
- Append (cost=0.00..20.12 rows=4 width=72)
- -> Seq Scan on l2 (cost=0.00..20.12 rows=4 width=72)
- Filter: (l_id = 2)
- (3 rows)
- postgres=#
hash分區
語法：
- [postgres@localhost bin]$ ./psql yonj1e
- psql (10beta2)
- Type "help" for help.
- yonj1e=# create table h (h_id int, h_name name, h_date date) partition by hash(h_id);
- CREATE TABLE
- yonj1e=# create table h1 partition of h;
- CREATE TABLE
- yonj1e=# create table h2 partition of h;
- CREATE TABLE
- yonj1e=# create table h3 partition of h;
- CREATE TABLE
- yonj1e=# create table h4 partition of h;
- CREATE TABLE
- yonj1e=# insert into h select id, md5(random()::text), now() + (id||'day')::interval from generate_series(1, 50) t(id);
- INSERT 0 50
- yonj1e=# select *,tableoid::regclass from h;
- h_id | h_name | h_date | tableoid
- ------+----------------------------------+------------+----------
- 5 | 21fe9a616ce20868769904bbda56aa3e | 2017-08-14 | h1
- 6 | 8fa0f42bf4239c05c1cd46a814f71eaa | 2017-08-15 | h1
- 8 | 858e324311506fb5c5000a4741b9af3c | 2017-08-17 | h1
- 12 | ef4ce7a0f6168605a7c243a709f28bc3 | 2017-08-21 | h1
- 13 | 2273522a7b3c286e214a8f57e010568e | 2017-08-22 | h1
- 17 | 8bca453f60f13278d3a02149b30394d2 | 2017-08-26 | h1
- 19 | 0c2f14a6a8e675341b4e7bdb6ed161de | 2017-08-28 | h1
- 23 | f10fff43558393b577d417127bf6a163 | 2017-09-01 | h1
- 26 | 1dd0851728458b67a053d500bbb837ae | 2017-09-04 | h1
- 28 | 00f67b8636b4d225d3b62bcca9c6d527 | 2017-09-06 | h1
- 40 | d3a217d39b6808ff5e37ed3977513e05 | 2017-09-18 | h1
- 41 | 0f4c765d809c3db3fa608e986aed1247 | 2017-09-19 | h1
- 42 | 022ff983201352092d5d7cb735e9f531 | 2017-09-20 | h1
- 44 | c3dba31501b3625aac7f3d4f41512855 | 2017-09-22 | h1
- 49 | 21c697e92f936982840b928e03151204 | 2017-09-27 | h1
- 11 | 625ad3b0c9d40f7cae26be84a7ae054d | 2017-08-20 | h2
- 14 | 7ee39c8df46d7ec61923dffe6f58ec07 | 2017-08-23 | h2
- 22 | 77c9230f9eeeb9faaa5c30ff518bbf60 | 2017-08-31 | h2
- 29 | 5a6e0895b2477026bcfa4996650797a8 | 2017-09-07 | h2
- 31 | 37d84c0c0956df75407e0bfc67a782ed | 2017-09-09 | h2
- 34 | f43f07545fba020b47c84952c6af6cc7 | 2017-09-12 | h2
- 35 | 2fa08f1311c20ac45a6726bfbc8a4f05 | 2017-09-13 | h2
- 36 | d01940a876b86c2de8d67a37813ab89d | 2017-09-14 | h2
- 38 | f21e401cb38c6d625b264f53fb59fb8b | 2017-09-16 | h2
- 43 | c42fcb14c2d5e5c067db8231d502daae | 2017-09-21 | h2
- 45 | cc5670020ba35ae2c324dd33a6efff98 | 2017-09-23 | h2
- 1 | 2881b2aadddd2dfef14477369a107319 | 2017-08-10 | h3
- 2 | e22e0bba2716e6d969a62502d34fc518 | 2017-08-11 | h3
- 9 | a933091df7f51f5b0b6ab43816e5d765 | 2017-08-18 | h3
- 15 | 92ee6dc6670ea8e02e746ee508b51022 | 2017-08-24 | h3
- 21 | 67b7140105e81730f364ee9de195e0a0 | 2017-08-30 | h3
- 46 | f4c47b9e055f6c732dff55cb4fd152b3 | 2017-09-24 | h3
- 50 | 1b419faea293d3edab2cfb7f8efb55f8 | 2017-09-28 | h3
- 3 | 6ea55fe46f2119f084edd66abe486d91 | 2017-08-12 | h4
- 4 | 094b16011f0e9b34c878caa7d95e067f | 2017-08-13 | h4
- 7 | c916c264c77ba90b3463143b9513bc15 | 2017-08-16 | h4
- 10 | d333ce15f3a8d01a39df9ae317bf64b7 | 2017-08-19 | h4
- 16 | b136e8466bbafc58f917e14919b1edf1 | 2017-08-25 | h4
- 18 | 6c3fb7b3e473f793575407299006e6a3 | 2017-08-27 | h4
- 20 | bac12655b2d54855c58d19c9facc1579 | 2017-08-29 | h4
- 24 | ebcafb42d26654eff04bb5f8b35fdd69 | 2017-09-02 | h4
- 25 | 494e25facb9fe46e00037c716e2052e7 | 2017-09-03 | h4
- 27 | 961b1728893e7f6d46ed827ee9b4809e | 2017-09-05 | h4
- 30 | ebf840a36af46cc3dd8f29c94013cb71 | 2017-09-08 | h4
- 32 | 7c5083fed360079bcbc23c6ee803a4d6 | 2017-09-10 | h4
- 33 | 707e98eac5ba349c80df6f9d8f062676 | 2017-09-11 | h4
- 37 | e1cb546e66cd45b00441493100fb7752 | 2017-09-15 | h4
- 39 | 0bfa7e7ccb00a477add00df4d0327c52 | 2017-09-17 | h4
- 47 | d894969c3cdbf00fe7dce9683c6f9a17 | 2017-09-25 | h4
- 48 | 09ca86c5e5bd87ba786533f34c4ebf25 | 2017-09-26 | h4
- (50 rows)
- yonj1e=# explain select * from h where h_id = 20;
- QUERY PLAN
- ----------------------------------------------------------
- Append (cost=0.00..20.12 rows=4 width=72)
- -> Seq Scan on h4 (cost=0.00..20.12 rows=4 width=72)
- Filter: (h_id = 20)
- (3 rows)
- yonj1e=# \d+ h*
- Table "public.h"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
- --------+---------+-----------+----------+---------+---------+--------------+-------------
- h_id | integer | | | | plain | |
- h_name | name| | | | plain | |
- h_date | date| | | | plain | |
- Partition key: HASH (h_id)
- Partitions: h1 SERIAL NUMBER 0,
- h2 SERIAL NUMBER 1,
- h3 SERIAL NUMBER 2,
- h4 SERIAL NUMBER 3
- Table "public.h1"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
- --------+---------+-----------+----------+---------+---------+--------------+-------------
- h_id | integer | | | | plain | |
- h_name | name| | | | plain | |
- h_date | date| | | | plain | |
- Partition of: h SERIAL NUMBER 0
- Partition constraint: (h_id IS NOT NULL)
- Table "public.h2"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
- --------+---------+-----------+----------+---------+---------+--------------+-------------
- h_id | integer | | | | plain | |
- h_name | name| | | | plain | |
- h_date | date| | | | plain | |
- Partition of: h SERIAL NUMBER 1
- Partition constraint: (h_id IS NOT NULL)
- Table "public.h3"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
- --------+---------+-----------+----------+---------+---------+--------------+-------------
- h_id | integer | | | | plain | |
- h_name | name| | | | plain | |
- h_date | date| | | | plain | |
- Partition of: h SERIAL NUMBER 2
- Partition constraint: (h_id IS NOT NULL)
- Table "public.h4"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
- --------+---------+-----------+----------+---------+---------+--------------+-------------
- h_id | integer | | | | plain | |
- h_name | name| | | | plain | |
- h_date | date| | | | plain | |
- Partition of: h SERIAL NUMBER 3
- Partition constraint: (h_id IS NOT NULL)
- yonj1e=#
HASH分區語法還不支持，以后或許會支持。
責任編輯：龐桂玉
來源：
yonj1e的博客
