Postgres分区表一

背景

最近公司存储业务数据遇到了数据量过大的问题，对于单个数据表而言，性能点也就在千万级，查询起来在10s以上了，又不方便建索引（查询条件太复杂了），然后就想到了分区表，于是开始了我的实验之旅（写这段话的目的主要是显得有点仪式感，不然又要被某个人吐槽我了，分区表的创建在Postgresql V10 之后有较大的变化，本文主要讲述V10 版本之前的创建及使用方式，若Postgresql版本大于等于V10版本，请参考下一节《Postgres数据库分区表 二》

分区表管理

分区表概述

Postgres分区表分为一张主表和多个分区表，对主表的操作会自动下发到各个分区表，所以在做删改查的时候，对使用者是基本感知不到是否使用了分区表的（使用者也可以直接操作分区表），分区表的数据写入需要直接写入对应的分区表，不能直接写入主表，否则分区的效果就体现不出来了，如果当前系统已经实现了完整的CRUD逻辑，此时为了最小化改动，可以在通过使用触发器，将主表的写入分散到分区表。

创建分区表

创建主表

主表的创建，就是正常的表创建语句，下面是一个简单的示例：

CREATE TABLE partition_test (
  id int4,
  info text,
  c_time timestamp(6),
  day varchar(255),
  daymod int4
);

注：后面为了说明约束的使用，此处的daymod=cast(replace(day,’-’,’’) as int) % 31 根据日期，将数据打上31个分区的标签

创建分区表

分区表依赖主表进行创建，为了使用分区的特性，需要添加对应的分区约束，下面是一个简单的示例：

--创建分区表 --
CREATE TABLE partition_test_0 () inherits (partition_test);
-- 添加分区约束 --
ALTER TABLE partition_test_0 ADD CONSTRAINT partition_test_0_check CHECK (daymod = 0);

注：分区表对应的主表发生结构变化，分区表也会作出相同的变化

卸载分区表

分区表的卸载主要包括两种方式，一种是直接删除，另一种是解除分区表与主表的关联关系

直接删除

直接删除对应的分区表和分区数据

drop table partition_test_0;

解除关联关系

从分区表中移除分区，但是保留分区的访问权限，和直接DROP 相比，该方式仅仅是使子表脱离了原有的主表，而存储在子表中的数据仍然可以得到访问，因为此时该表已经被还原成一个普通的数据表了。这样对于数据库的DBA来说，就可以在此时对该表进行必要的维护操作，如数据清理、归档等，在完成诸多例行性的操作之后，就可以考虑是直接删除该表(DROP TABLE)，还是先清空该表的数据

alter table partition_test_0 inherit partition_test;

分区策略

分区表的分区策略主要是分为固定分区和动态分区，别查了这两个概念了，你查不到的，这是我定义的，区别在于分区的设立是否影响数据的日常维护，比如数据存储周期

固定分区

固定分区指的是在创建表的同时，就已经确定了分区的数量，需要考虑如何对数据进行散列。
优点：数据的写入变得易于操作，前面提到，使用分区表时，为了体现分区的优势，需要将数据落到分区表，而非主表。当分区表固定时，我们可以通过对主表创建触发器，来将所有的写入自动分散到分区表。使用者在使用时无需关心应该具体写入哪张分区表。
缺点：对失效数据的处理需要耗费大量时间，需要编写对应的sql脚本，对分区中的数据进行过滤删除。随着时间的延长，数据会逐渐增加，这也会导致性能下降

动态分区

动态分区指的是在创建表的同时，并不确定分区的数量，分区根据需要进行自动创建（业务侧实现）
优点：动态分区的使用方便对单个分区的数据进行批量操作，例如根据时间进行分区，那么对于某个时间点前的数据（失效数据），可以通过卸载分区的方式快速删除。动态分区也能避免时间过长导致数据累加过多，导致性能下降的问题

分区示例

我们以31个分区1860w数据来对比一下分区的所带来的性能提升

创建普通表partition_test_all

创建普通表，并写入1860w数据

--创建表--
CREATE TABLE partition_test_all (
	id int4,
	info text,
	c_time timestamp(6),
	day varchar(255),
	daymod int4
);
--写入数据 将下面的sql中的日期更换一下重复执行31次，即从2021-01-01	一直替换到2021-01-31即可生成1个月的数据，每月60w条--
insert into partition_test_all select 	generate_series(1,600000),md5(random()::text),clock_timestamp(),'2021-01-01',cast(replace('2021-01-01','-','') as int) % 31;

创建分区表partition_test，使用固定分区

--创建分区主表--
CREATE TABLE partition_test (
 id int4,
 info text,
 c_time timestamp(6),
 day varchar(255),
 daymod int4
);
--创建31个分区--
CREATE TABLE partition_test_0 () inherits (partition_test);
CREATE TABLE partition_test_1 () inherits (partition_test);
.....
CREATE TABLE partition_test_29 () inherits (partition_test);
CREATE TABLE partition_test_30 () inherits (partition_test);
-- 分别为31个分区添加分区约束 --
ALTER TABLE partition_test_0 ADD CONSTRAINT partition_test_0_check CHECK (daymod = 0);
ALTER TABLE partition_test_1 ADD CONSTRAINT partition_test_1_check CHECK (daymod = 1);
.....
ALTER TABLE partition_test_29 ADD CONSTRAINT partition_test_29_check CHECK (daymod = 29);
ALTER TABLE partition_test_30 ADD CONSTRAINT partition_test_30_check CHECK (daymod = 30);
--创建触发器（此处是为了通过触发器，将分区表数据写入改为统一通过向主表写入）--
CREATE OR REPLACE FUNCTION tbl_partition_insert_trigger()                      
RETURNS TRIGGER AS $$  
BEGIN  
 IF (cast(replace(NEW.day,'-','') as int) % 31=0 ) THEN  
     INSERT INTO partition_test_0 VALUES (NEW.*);  
 ELSIF (cast(replace(NEW.day,'-','') as int) % 31=1 ) THEN  
     INSERT INTO partition_test_1 VALUES (NEW.*);   
......
 ELSIF (cast(replace(NEW.day,'-','') as int) % 31=29 ) THEN  
     INSERT INTO partition_test_29 VALUES (NEW.*);   
 ELSIF (cast(replace(NEW.day,'-','') as int) % 31=30 ) THEN
     INSERT INTO partition_test_30 VALUES (NEW.*); 
 ELSE  
     RAISE EXCEPTION 'Date out of range. Fix the tbl_partition_insert_trigger() function!';  
 END IF;  
 RETURN NULL;  
END;  
$$  
LANGUAGE plpgsql;
--给主表绑定触发器--
CREATE TRIGGER insert_tbl_partition_trigger
BEFORE INSERT ON partition_test
FOR EACH ROW EXECUTE PROCEDURE tbl_partition_insert_trigger();
--写入数据 从partition_test_all copy过来--
insert into partition_test select * from partition_test_all;

性能测试

1.测试统计全表数据

--统计全表数据--
select count(1) from partition_test_all; --0.647s--
select count(1) from partition_test; --0.658s--
--查询某天的数据--
select * from partition_test_all where daymod = 0 and day='2021-01-09'; --6.675s--
select * from partition_test where daymod = 0 and day='2021-01-09'; --5.476s--
--统计某天的数据--
select count(1) from partition_test_all where daymod = 0 and day='2021-01-09'; --1.044s--
select count(1) from partition_test where daymod = 0 and day='2021-01-09'; --0.764s--
select count(1) from partition_test_0 where daymod = 0 and day='2021-01-09'; --0.05s--
--查询某天的数据-返回10条--
select * from partition_test_all where day='2021-01-09' limit 10; --0.481s--
select * from partition_test where day='2021-01-09' limit 10;--0.011s--
select * from partition_test_0 where day='2021-01-09' limit 10;--0.006s--

从上面的查询时间上，我们可以看出当过滤条件中包含分区字段时，是能够显著提升查询效率的，有兴趣的可以看下执行计划（直接定位到分区表，不再扫描全量数据）

注意事项

1.添加分区约束（分区约束条件之间不要包含重合的条件/数据）
2.分区表在设计分区约束时，请使用分区字段，不要对字段使用计算操作，方便快速进行分区定位
正例：此约束能够只扫描单个分区

ALTER TABLE partition_test_0 ADD CONSTRAINT partition_test_0_check CHECK (daymod = 0);

反例：此约束会扫描所有分区

ALTER TABLE partition_test_0 ADD CONSTRAINT partition_test_0_check CHECK (cast(replace(day,'-','') as int) % 31=0);

3.分区表在查询的时候，如果存在某个分区未设置分区约束，则所有对主表的操作都会扫描该表