postgresql之分区表

pg10之后有内置分区表，相对于之前传统分区表更好用

---传统分区表

　　--继承表

postgres=# create table tbl_log(id int4,create_date date,log_type text);
CREATE TABLE

创建一张子表

postgres=# create table tbl_log_sql (sql text ) inherits(tbl_log);
CREATE TABLE

父表子表都可以插入数据，查看表结构

postgres=# \d+ tbl_log
Table "public.tbl_log"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------+---------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
create_date | date | | | | plain | |
log_type | text | | | | extended | |
Child tables: tbl_log_sql
postgres=# \d tbl_log_sql
Table "public.tbl_log_sql"
Column | Type | Collation | Nullable | Default
-------------+---------+-----------+----------+---------
id | integer | | |
create_date | date | | |
log_type | text | | |
sql | text | | |
Inherits: tbl_log

查看数据
postgres=# insert into tbl_log values (1,'2021-10-19',null);
INSERT 0 1
postgres=# insert into tbl_log_sql values (1,'2021-10-18',null,'select 2');
INSERT 0 1
postgres=# select * from tbl_log;
id | create_date | log_type
----+-------------+----------
1 | 2021-10-19 |
1 | 2021-10-18 |
(2 rows)
postgres=# select * from tbl_log_sql;
id | create_date | log_type | sql
----+-------------+----------+----------
1 | 2021-10-18 | | select 2
(1 row)

通过pg_class确认哪张表

postgres=# select p.relname,p.oid,c.* from tbl_log c,pg_class p where c.tableoid=p.oid;
relname | oid | id | create_date | log_type
-------------+-------+----+-------------+----------
tbl_log | 49173 | 1 | 2021-10-19 |
tbl_log_sql | 49179 | 1 | 2021-10-18 |

只查询父表数据，需要在父表名称前加关键字only

postgres=# select * from only tbl_log;
id | create_date | log_type
----+-------------+----------
1 | 2021-10-19 |

如果没有加only会对父表和所有子表进行操作

postgres=# delete from tbl_log;
DELETE 2
postgres=# select * from tbl_log;
id | create_date | log_type
----+-------------+----------
(0 rows)

 创建传统分区表

注意：

1. 创建父表，如果父表定义约束，子表也会受到约束，因此除非是全局约束，否则不应该创建在父表上，另外父表不应该写入数据。


2. 用inherits的方式创建继承表，称之为子表或分区，子表的字段应该与父表保持一致。


3. 给所有子表创建约束，只有约束条件的数据才能写入对应分区或子表，注意：分区约束不能有重叠。


4. 给所有子表创建索引，由于继承操作不会继承父表上的索引，因此需要手工创建


5. 在父表上定义触发器insert，update,delete，将sql分发到对应分区（可选），从应用方可以根据分区处理


6. 启用consratint_exclusion参数，如果设置成off,则会父表上的sql性能会降低

创建父表

postgres=# create table log_ins(id serial,user_id int4,create_time timestamp(0) without time zone);
CREATE TABLE

创建子表以及给子表创建索引，父表不存储数据，可以不用在父表上创建

postgres=# create table log_ins_history(CHECK (create_time <'2017-01-01')) inherits(log_ins);
CREATE TABLE
postgres=# create table log_ins_201701(CHECK (create_time >= '2017-01-01' and create_time <'2017-02-01')) inherits(log_ins);
CREATE TABLE
postgres=# create table log_ins_201702(CHECK (create_time >= '2017-02-01' and create_time <'2017-03-01')) inherits(log_ins);
CREATE TABLE
postgres=# create table log_ins_201703(CHECK (create_time >= '2017-03-01' and create_time <'2017-04-01')) inherits(log_ins);
CREATE TABLE
postgres=# create table log_ins_201704(CHECK (create_time >= '2017-04-01' and create_time <'2017-05-01')) inherits(log_ins);
CREATE TABLE
postgres=# create table log_ins_201705(CHECK (create_time >= '2017-05-01' and create_time <'2017-06-01')) inherits(log_ins);
CREATE TABLE
postgres=# create table log_ins_201706(CHECK (create_time >= '2017-06-01' and create_time <'2017-07-01')) inherits(log_ins);
CREATE TABLE
postgres=# create table log_ins_201707(CHECK (create_time >= '2017-07-01' and create_time <'2017-08-01')) inherits(log_ins);
CREATE TABLE
postgres=# create table log_ins_201708(CHECK (create_time >= '2017-08-01' and create_time <'2017-09-01')) inherits(log_ins);
CREATE TABLE
postgres=# create table log_ins_201709(CHECK (create_time >= '2017-09-01' and create_time <'2017-10-01')) inherits(log_ins);
CREATE TABLE
postgres=# create table log_ins_201710(CHECK (create_time >= '2017-10-01' and create_time <'2017-11-01')) inherits(log_ins);
CREATE TABLE
postgres=# create table log_ins_201711(CHECK (create_time >= '2017-11-01' and create_time <'2017-12-01')) inherits(log_ins);
CREATE TABLE
postgres=# create table log_ins_201712(CHECK (create_time >= '2017-12-01' and create_time <'2018-01-01')) inherits(log_ins);
CREATE TABLE

postgres=# create index idx_his_ctime on log_ins_history using btree (create_time);
CREATE INDEX
postgres=# create index idx_log_ins_201701_ctime on log_ins_201701 using btree (create_time);
CREATE INDEX
postgres=# create index idx_log_ins_201702_ctime on log_ins_201702 using btree (create_time);
CREATE INDEX
postgres=# create index idx_log_ins_201703_ctime on log_ins_201703 using btree (create_time);
CREATE INDEX
postgres=# create index idx_log_ins_201704_ctime on log_ins_201704 using btree (create_time);
CREATE INDEX
postgres=# create index idx_log_ins_201705_ctime on log_ins_201705 using btree (create_time);
CREATE INDEX
postgres=# create index idx_log_ins_201706_ctime on log_ins_201706 using btree (create_time);
CREATE INDEX
postgres=# create index idx_log_ins_201707_ctime on log_ins_201707 using btree (create_time);
CREATE INDEX
postgres=# create index idx_log_ins_201708_ctime on log_ins_201708 using btree (create_time);
CREATE INDEX
postgres=# create index idx_log_ins_201709_ctime on log_ins_201709 using btree (create_time);
CREATE INDEX
postgres=# create index idx_log_ins_201710_ctime on log_ins_201710 using btree (create_time);
CREATE INDEX
postgres=# create index idx_log_ins_201711_ctime on log_ins_201711 using btree (create_time);
CREATE INDEX
postgres=# create index idx_log_ins_201712_ctime on log_ins_201712 using btree (create_time);
CREATE INDEX

创建触发器函数设置数据插入父表时的路由规则

create or replace function log_ins_insert_trigger()
returns trigger
language plpgsql
AS $function$
begin
if (NEW.create_time <'2017-01-01') THEN
insert into log_ins_history values (NEW.*);
elsif (NEW.create_time >= '2017-01-01' and NEW.create_time<'2017-02-01') THEN insert into log_ins_201701 values (NEW.*);
elsif (NEW.create_time >= '2017-02-01' and NEW.create_time<'2017-03-01') THEN insert into log_ins_201702 values (NEW.*);
elsif (NEW.create_time >= '2017-03-01' and NEW.create_time<'2017-04-01') THEN insert into log_ins_201703 values (NEW.*);
elsif (NEW.create_time >= '2017-04-01' and NEW.create_time<'2017-05-01') THEN insert into log_ins_201704 values (NEW.*);
elsif (NEW.create_time >= '2017-05-01' and NEW.create_time<'2017-06-01') THEN insert into log_ins_201705 values (NEW.*);
elsif (NEW.create_time >= '2017-06-01' and NEW.create_time<'2017-07-01') THEN insert into log_ins_201706 values (NEW.*);
elsif (NEW.create_time >= '2017-07-01' and NEW.create_time<'2017-08-01') THEN insert into log_ins_201707 values (NEW.*);
elsif (NEW.create_time >= '2017-08-01' and NEW.create_time<'2017-09-01') THEN insert into log_ins_201708 values (NEW.*);
elsif (NEW.create_time >= '2017-09-01' and NEW.create_time<'2017-10-01') THEN insert into log_ins_201709 values (NEW.*);
elsif (NEW.create_time >= '2017-10-01' and NEW.create_time<'2017-11-01') THEN insert into log_ins_201710 values (NEW.*);
elsif (NEW.create_time >= '2017-11-01' and NEW.create_time<'2017-12-01') THEN insert into log_ins_201711 values (NEW.*);
elsif (NEW.create_time >= '2017-12-01' and NEW.create_time<'2018-01-01') THEN insert into log_ins_201712 values (NEW.*);
else
raise exception 'create_time out of range. fix the log_ins_insert_tigger() function!';
END if;
return null;
end;
$function$;

函数中的NEW.*是要指向插入的数据行，在父表上定义插入触发器

postgres=# create trigger insert_log_ins_trigger before insert on log_ins for each row execute procedure log_ins_insert_trigger();
CREATE TRIGGER

 触发器知识：

 使用分区表

插入数据

postgres=# insert into log_ins(user_id,create_time) select round(100000000*random()),generate_series('2016-12-01'::date,'2017-12-01'::date, '1 minute');;
INSERT 0 0

查看父表数据，子表数据

postgres=# select * from only log_ins limit 2;
id | user_id | create_time
----+---------+-------------
(0 rows)
postgres=# select * from log_ins_201703 limit 2;
id | user_id | create_time
--------+----------+---------------------
129601 | 50525906 | 2017-03-01 00:00:00
129602 | 6842102 | 2017-03-01 00:01:00
(2 rows)

查询父表还是子表，假如检索2017-01-01这一天的数据，查询父表和子表之间的差异

postgres=# explain analyze select * from log_ins where create_time > '2017-01-01' and create_time <'2017-01-02';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..62.21 rows=1497 2017-01-01 00:00:00'::timestamp without time zone) AND (create_time <'2017-01-02 00:00:00'::timestamp without time zone))
-> Index Scan using idx_log_ins_201701_ctime on log_ins_201701 (cost=0.29..62.21 rows=1496 2017-01-01 00:00:00'::timestamp without time zone) AND (create_time <'2017-01-02 00:00:00'::timestamp without time zone))
Planning time: 0.270 ms
Execution time: 0.224 ms
(7 rows)
postgres=# explain analyze select * from log_ins_201701 where create_time > '2017-01-01' and create_time <'2017-01-02';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_log_ins_201701_ctime on log_ins_201701 (cost=0.29..62.21 rows=1496 2017-01-01 00:00:00'::timestamp without time zone) AND (create_time <'2017-01-02 00:00:00'::timestamp without time zone))
Planning time: 0.072 ms
Execution time: 0.191 ms
(4 rows)

可以看出直接访问子表性能有提升，并发量上去的话效果更明显

constraint_exclusion参数

postgres=# show constraint_exclusion;
constraint_exclusion
----------------------
partition

on:所有表都通过约束优化查询；

off：所有表不通过约束优化查询；

partition：只对继承表和union all子查询通过检索约束来优化查询（建议）

添加分区，严格按照以下步骤

--创建分区

postgres=# create table log_ins_201801(like log_ins including all);
CREATE TABLE

--添加约束

postgres=# alter table log_ins_201801 add constraint log_ins_201801_create_time_check CHECK(create_time >='2018-01-01' and create_time<'2018-02-01');
ALTER TABLE

--刷新触发器函数

postgres=# create or replace function log_ins_insert_trigger()
postgres-# returns trigger
postgres-# language plpgsql
postgres-# AS $function$
postgres$# begin
postgres$# if (NEW.create_time <'2017-01-01') THEN
postgres$# insert into log_ins_history values (NEW.*);
postgres$# elsif (NEW.create_time >= '2017-01-01' and NEW.create_time<'2017-02-01') THEN insert into log_ins_201701 values (NEW.*);
postgres$# elsif (NEW.create_time >= '2017-02-01' and NEW.create_time<'2017-03-01') THEN insert into log_ins_201702 values (NEW.*);
postgres$# elsif (NEW.create_time >= '2017-03-01' and NEW.create_time<'2017-04-01') THEN insert into log_ins_201703 values (NEW.*);
postgres$# elsif (NEW.create_time >= '2017-04-01' and NEW.create_time<'2017-05-01') THEN insert into log_ins_201704 values (NEW.*);
postgres$# elsif (NEW.create_time >= '2017-05-01' and NEW.create_time<'2017-06-01') THEN insert into log_ins_201705 values (NEW.*);
postgres$# elsif (NEW.create_time >= '2017-06-01' and NEW.create_time<'2017-07-01') THEN insert into log_ins_201706 values (NEW.*);
postgres$# elsif (NEW.create_time >= '2017-07-01' and NEW.create_time<'2017-08-01') THEN insert into log_ins_201707 values (NEW.*);
postgres$# elsif (NEW.create_time >= '2017-08-01' and NEW.create_time<'2017-09-01') THEN insert into log_ins_201708 values (NEW.*);
postgres$# elsif (NEW.create_time >= '2017-09-01' and NEW.create_time<'2017-10-01') THEN insert into log_ins_201709 values (NEW.*);
postgres$# elsif (NEW.create_time >= '2017-10-01' and NEW.create_time<'2017-11-01') THEN insert into log_ins_201710 values (NEW.*);
postgres$# elsif (NEW.create_time >= '2017-11-01' and NEW.create_time<'2017-12-01') THEN insert into log_ins_201711 values (NEW.*);
postgres$# elsif (NEW.create_time >= '2017-12-01' and NEW.create_time<'2018-01-01') THEN insert into log_ins_201712 values (NEW.*);
postgres$# elsif (NEW.create_time >= '2018-01-01' and NEW.create_time<'2018-02-01') THEN insert into log_ins_201801 values (NEW.*);
postgres$# else
postgres$# raise exception 'create_time out of range. fix the log_ins_insert_tigger() function!';
postgres$# END if;
postgres$# return null;
postgres$# end;
postgres$# $function$;

--所有步骤完成之后，将新分区log_ins_201801继承到父表log_ins

postgres=# alter table log_ins_201801 inherit log_ins;
ALTER TABLE