一、 pg 9 准备分区表测试环境
在测试环境创建好 pg 9 测试环境,连接到pg 9 实例上:
/usr/local/pgsql101/bin/psql -h 127.0.0.1 -p 5432 -U postgres -d postgres
-- 创建测试库
\l
CREATE DATABASE pg_9_db;
\l
\c pg_9_db
一、 pg 9 创建新分区表
1. 定义父表
CREATE TABLE pg_9_tab( id serial,uid int4,username varchar,create_time bigint);
CREATE INDEX idx_pg_9_tab_ctime ON pg_9_tab USING btree (create_time);
2. 定义子表: 用 inherits 创建分区表
3. 定义子表约束 :约束数据对应分区的规则
-- 按照时间戳分区,对应分区表与时间戳
2019-09-15 00:00:00 1568476800
2019-10-01 00:00:00 1569859200
2019-11-01 00:00:00 1572537600
2019-12-01 00:00:00 1575129600
2019-12-15 00:00:00 1576339200
2020-01-01 00:00:00 1577808000
2020-02-01 00:00:00 1580486400
上面第2步和第3步,两步合并在一起,创建分区表
CREATE TABLE pg_9_tab_p_hisotry(CHECK ( create_time < 1569859200 ) ) INHERITS(pg_9_tab);
CREATE TABLE pg_9_tab_p_201910(CHECK ( create_time >= 1569859200 and create_time < 1572537600 ) ) INHERITS(pg_9_tab);
CREATE TABLE pg_9_tab_p_201911(CHECK ( create_time >= 1572537600 and create_time < 1575129600 ) ) INHERITS(pg_9_tab);
CREATE TABLE pg_9_tab_p_201912(CHECK ( create_time >= 1575129600 and create_time < 1577808000 ) ) INHERITS(pg_9_tab);
4. 创建子表索引 :子表不会继承父表的索引
CREATE INDEX idx_pg_9_tab_p_hisotry_ctime ON pg_9_tab_p_hisotry USING btree (create_time);
CREATE INDEX idx_pg_9_tab_p_201910_ctime ON pg_9_tab_p_201910 USING btree (create_time);
CREATE INDEX idx_pg_9_tab_p_201911_ctime ON pg_9_tab_p_201911 USING btree (create_time);
CREATE INDEX idx_pg_9_tab_p_201912_ctime ON pg_9_tab_p_201912 USING btree (create_time);
5. 创建分区插入、修改、删除函数和触发器
-- 创建分表insert的路由函数
CREATE OR REPLACE FUNCTION pg_9_tab_insert_trigger()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
IF ( NEW.create_time < 1569859200 ) THEN
INSERT INTO pg_9_tab_p_hisotry VALUES (NEW.*);
ELSIF ( NEW.create_time >= 1569859200 and NEW.create_time < 1572537600 ) THEN
INSERT INTO pg_9_tab_p_201910 VALUES (NEW.*);
ELSIF ( NEW.create_time >= 1572537600 and NEW.create_time < 1575129600 ) THEN
INSERT INTO pg_9_tab_p_201911 VALUES (NEW.*);
ELSIF ( NEW.create_time >= 1575129600 and NEW.create_time < 1577808000 ) THEN
INSERT INTO pg_9_tab_p_201912 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'create_time out of range. Fix the pg_9_tab_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$function$;
函数中的 new.* 是指要插入的数据航,在父表上定义插入触发器:
CREATE TRIGGER insert_pg_9_tab_trigger BEFORE INSERT ON pg_9_tab FOR EACH ROW
EXECUTE PROCEDURE pg_9_tab_insert_trigger();
6. 启用分区查询参数:设置 constraint_exclusion 参数
show constraint_exclusion;
SET constraint_exclusion = off; ##所有表都不通过约束优化查询
SET constraint_exclusion = on; ##所有表都通过约束优化查询
SET constraint_exclusion = partition; ##之对继承表和UNION ALL 子查询通过检索约束来优化查询
-- 执行计划查看父表还是子表
EXPLAIN ANALYZE SELECT * from pg_9_tab WHERE create_time > 1575129600 AND create_time < 1576339200;
EXPLAIN ANALYZE SELECT * from pg_9_tab_p_201910 WHERE create_time > 1575129600 AND create_time < 1576339200;
二、 pg 9 插入数据与数据分部确认
-- 插入数据
INSERT INTO pg_9_tab(uid,username,create_time)
SELECT round(1000*random()),chr(int4(random()*26)+65),generate_series( 1568476800, 1576339200, 360);
INSERT INTO pg_9_tab(uid,username,create_time) SELECT 346,'F',1292083200;
-- 查看表
SELECT count(*) FROM pg_9_tab;
SELECT count(*) FROM ONLY pg_9_tab;
\d+ pg_9_tab*
SELECT * FROM pg_9_tab LIMIT 2;
SELECT * FROM pg_9_tab_p_201911 LIMIT 2;
三、 pg 9 分区表添加新分区
-- 添加分区
1. 创建分区表
CREATE TABLE pg_9_tab_p_202001(LIKE pg_9_tab INCLUDING ALL );
2. 添加约束
ALTER TABLE pg_9_tab_p_202001 ADD CONSTRAINT pg_9_tab_create_time_check
CHECK (create_time >= 1577808000 and create_time < 1580486400);
3. 刷新触发器
CREATE OR REPLACE FUNCTION pg_9_tab_insert_trigger()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
IF ( NEW.create_time < 1569859200 ) THEN
INSERT INTO pg_9_tab_p_hisotry VALUES (NEW.*);
ELSIF ( NEW.create_time >= 1569859200 and NEW.create_time < 1572537600 ) THEN
INSERT INTO pg_9_tab_p_201910 VALUES (NEW.*);
ELSIF ( NEW.create_time >= 1572537600 and NEW.create_time < 1575129600 ) THEN
INSERT INTO pg_9_tab_p_201911 VALUES (NEW.*);
ELSIF ( NEW.create_time >= 1575129600 and NEW.create_time < 1577808000 ) THEN
INSERT INTO pg_9_tab_p_201912 VALUES (NEW.*);
ELSIF ( NEW.create_time >= 1577808000 and NEW.create_time < 1580486400 ) THEN
INSERT INTO pg_9_tab_p_202001 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'create_time out of range. Fix the pg_9_tab_insert_trigger() function!';
END IF;
RETURN NULL;