说说PostgreSql手动分区与自动分区

分区描述

分区方式：postgresql10支持list和range分区，postgresql10以上支持list、range和hash分区
分区原理：分区表继承主表，但不完全继承主表属性，如唯一性约束、索引、主键、外键，而检查约束和非空约束可以继承
创建方法：①手动方法：创建主表时定义分区方式和分区键，再创建分区表②自动方法：创建主表，创建触发函数，再定义分区键为触发器，不需要手动创建
优劣：分区键值固定时，手动方法更好，插入数据时不需要触发判断，再分配到相应分区;分区键不固定时，自动方法更好，但性能受到影响

创建方法

本文以用户表为例，以创建时间年月为分区键
1、手动分区
手动分区定义part_time字段，保存创建时间年月，如’202008’，以list方式进行分区，分区键为part_time，创建主表末尾加入以下语句：PARTITION BY list(part_time)
创建主表

CREATE TABLE "t_user" (
  "uuid" varchar(36) COLLATE "pg_catalog"."default" NOT NULL,
  "dept_id" varchar(32) COLLATE "pg_catalog"."default",
  "user_name" varchar(32) COLLATE "pg_catalog"."default",
  "phone" varchar(32) COLLATE "pg_catalog"."default",
  "create_time" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "part_time" varchar(6) COLLATE "pg_catalog"."default",
) PARTITION BY list(part_time);
COMMENT ON COLUMN "t_user"."uuid" IS '主键id';
COMMENT ON COLUMN "t_user"."dept_id" IS '部门id';
COMMENT ON COLUMN "t_user"."user_name" IS '姓名;
COMMENT ON COLUMN "t_user"."phone" IS '手机号';
COMMENT ON COLUMN "t_user"."create_time" IS '创建时间';
COMMENT ON COLUMN "t_user"."part_time" IS '创建时间年月';

创建2020年08月分区表，以part_time为分区规则

CREATE TABLE t_user_202008 PARTITION OF his_hsptzd_charge FOR VALUES in ('202008');
--建立创建时间为索引
CREATE INDEX idx_tu_ctime_202008 on t_user_202008(create_time);
--建立创建年月为索引
CREATE INDEX idx_tu_ptime_202008 on t_user_202008(part_time);
--建立部门ID为索引
CREATE INDEX idx_tu_deptid_202008 on t_user_202008(dept_id);
--创建手机号为唯一性约束
alter table t_user_202008 add constraint uk_tu_phone_202008 unique("phone");
--定义uuid为主键
alter table t_user add constraint pk_tu_202008 PRIMARY KEY ("uuid");

创建2020年07月分区表，以part_time为分区规则

CREATE TABLE t_user_202007 PARTITION OF his_hsptzd_charge FOR VALUES in ('202007');
--建立创建时间为索引
CREATE INDEX idx_tu_ctime_202007 on t_user_202007(create_time);
--建立创建年月为索引
CREATE INDEX idx_tu_ptime_202007 on t_user_202007(part_time);
--建立部门ID为索引
CREATE INDEX idx_tu_deptid_202007 on t_user_202007(dept_id);
--创建手机号为唯一性约束
alter table t_user_202007 add constraint uk_tu_phone_202007 unique("phone");
--定义uuid为主键
alter table t_user add constraint pk_tu_202007 PRIMARY KEY ("uuid");

2、自动分区
以create_time字段为分区规则，所有分区表继承主表，插入数据将分配到相应分区中，若分区表不存在会触发调用函数自动创建
创建主表

CREATE TABLE "t_user" (
  "uuid" varchar(36) COLLATE "pg_catalog"."default" NOT NULL,
  "dept_id" varchar(32) COLLATE "pg_catalog"."default",
  "user_name" varchar(32) COLLATE "pg_catalog"."default",
  "phone" varchar(32) COLLATE "pg_catalog"."default",
  "create_time" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "part_time" varchar(6) COLLATE "pg_catalog"."default",
) PARTITION BY list(part_time);
COMMENT ON COLUMN "t_user"."uuid" IS '主键id';
COMMENT ON COLUMN "t_user"."dept_id" IS '部门id';
COMMENT ON COLUMN "t_user"."user_name" IS '姓名;
COMMENT ON COLUMN "t_user"."phone" IS '手机号';
COMMENT ON COLUMN "t_user"."create_time" IS '创建时间';

定义自动插入函数

CREATE OR REPLACE FUNCTION auto_insert_t_user_fun()
  RETURNS trigger AS
$BODY$
DECLARE
    time_column_name 	text ;			-- 父表中用于分区的时间字段的名称[必须首先初始化!!]
    curMM 		        varchar(6);		-- 'YYYYMM'字串,用做分区子表的后缀
    isExist 		    boolean;		-- 分区子表,是否已存在
    startTime 		    text;
    endTime		        text;
    strSQL  		    text;

BEGIN
    -- 调用前,必须首先初始化(时间字段名):time_column_name [直接从调用参数中获取!!]
    time_column_name := TG_ARGV[0];

    -- 判断对应分区表是否已经存在
    EXECUTE 'SELECT $1.'||time_column_name INTO strSQL USING NEW;
    curMM := to_char( strSQL::timestamp , 'YYYYMM' );
    select count(*) INTO isExist from pg_class where relname = (TG_RELNAME||'_'||curMM);

    -- 若不存在, 则插入前需先创建子分区
    IF ( isExist = false ) THEN  
        -- 创建子分区表
        startTime := curMM||'01 00:00:00.000';
        endTime := to_char( startTime::timestamp + interval '1 month', 'YYYY-MM-DD HH24:MI:SS.MS');
        strSQL := 'CREATE TABLE IF NOT EXISTS '||TG_RELNAME||'_'||curMM||
                  ' ( CHECK('||time_column_name||'>='''|| startTime ||''' AND '
                             ||time_column_name||'< '''|| endTime ||''' )
                          ) INHERITS ('||TG_RELNAME||') ;'  ;  
        EXECUTE strSQL;

        -- 建立创建时间为索引
		strSQL := 'CREATE INDEX idx_tu_ctime_'||curMM||' ON '
						  ||TG_RELNAME||'_'||curMM||' ('||time_column_name||');' ;
				EXECUTE strSQL;
       	-- 建立部门ID为索引
		strSQL := 'CREATE INDEX idx_tu_deptid_'||curMM||' ON '
						  ||TG_RELNAME||'_'||curMM||' (dept_id);' ;
				EXECUTE strSQL;
		-- 定义uuid为主键		
		strSQL := 'ALTER TABLE '||TG_RELNAME||'_'||curMM||' add constraint 
						pk_hhc_'||curMM||' PRIMARY KEY(uuid);' ;
				EXECUTE strSQL;	 
		-- 定义手机号为唯一约束		
		strSQL := 'ALTER TABLE '||TG_RELNAME||'_'||curMM||' add constraint 
						uk_tu_phone_'||curMM||' unique(phone);' ;
				EXECUTE strSQL;					
    END IF;

    -- 插入数据到子分区表
    strSQL := 'INSERT INTO '||TG_RELNAME||'_'||curMM||' SELECT $1.*' ;
    EXECUTE strSQL USING NEW;
    RETURN NULL; 
END
$BODY$
  LANGUAGE plpgsql;

定义触发器

CREATE TRIGGER insert_t_user_trigger
BEFORE INSERT
ON t_user
FOR EACH ROW
EXECUTE PROCEDURE auto_insert_t_user_fun('create_time');