PostgreSQL教程

postgresql之分区表

本文主要是介绍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 width=16) (actual time=0.009..0.166 rows=1439 loops=1)
   ->  Seq Scan on log_ins  (cost=0.00..0.00 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=1)
         Filter: ((create_time > '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 width=16) (actual time=0.007..0.111 rows=1439 loops=1)
         Index Cond: ((create_time > '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 width=16) (actual time=0.009..0.144 rows=1439 loops=1)
   Index Cond: ((create_time > '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

 

这篇关于postgresql之分区表的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!