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

This article is to share with you about how to use partition tables in PostgreSQL 10. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article.

Zoning introduction

PostgreSQL partitions need to create a master table and then a child table, using inherited features, but do not need to write triggers / rules manually. Currently, range and list partitions are supported. I wonder if there will be any other partitions when the 10 official version is released. I will introduce the hash partitions I added based on 10 Beta2 later.

Range partition

Partition syntax:

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 | R12 | 47ba81de41d71bd51b18c7861a594bdf | 2017-08-10 | R13 | 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 | August 15, 2017 | R1 8 | 6ea5a7dba4dfc6c81ca5932be86a9341 | August-16, 2017 | R9 | d3d4dcb9dc48e0629042ede7ed9c7a33 | August-17, 2017 | R1 10 | 248d6f3e072c6c137a3402d11fc5b1d7 | August-18, 2017 | R2 11 | ae3a671045ded43260bc4d0bbcb7e428 | 2017-08-19 | R2 12 | acdc89bb326d9f0caaeeb86bfeac3a76 | August 20, 2017 | 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 | August 26, 2017 | R219 | 35ba5892f3b88aa3254445fbf5267eea | August 27, 2017 | R2 20 | c92d1df47257784bb11d7bfbb52b5710 | August 28, 2017 | R3 21 | d076a5498d17ade8f317bf47cfa322c3 | August 29, 2017 | R3 22 | a66c2e83f1e54e1392964ed71d5b8e20 | August 30, 2017 | 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 | R3 28 | 95b5e5492591c38ddd864d83265e26c4 | 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分区范围>

= minimum, 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) ->

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.