postgresql 9.6 分区表测试方案与记录

一、 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;