POSTGRESQL 分区 根据表的日期字段分区

一、PostgreSQL10之后的分区方式

10.0只支持范围分区和列表分区。11.0会支持hash分区。一旦创建了父表，就可以创建分区了-为此增加了PARTITION OF子句。

1.1创建主表

CREATE TABLE "test01"."animal" (
  "id" varchar NOT NULL,
  "name" varchar(255),
  "age" int4,
  "create_time" date,
  "update_time" date
) partition by range (create_time);

1.2 创建分区

 CREATE TABLE animal_01 PARTITION of animal for VALUES from ( '2019_01_01') to ( '2020_01_01');
 CREATE TABLE animal_02 PARTITION of animal for  VALUES  from ( '2020_01_01') to ( '2021_01_01');
 CREATE TABLE animal_03 PARTITION of animal for  VALUES  from ( '2021_01_01') to ( '2022_01_01');
 CREATE TABLE animal_04 PARTITION of animal for  VALUES  from ( '2022_01_01') to ( '2023_01_01');

1.3 在分区上创建索引-----通常分区键上的索引是必须的

create INDEX index_animal_create_time_01 on animal_01 (create_time);
 create INDEX index_animal_create_time_02 on animal_02 (create_time);
 create INDEX index_animal_create_time_03 on animal_03 (create_time);
 create INDEX index_animal_create_time_04 on animal_04 (create_time);

1.4 创建一个不分区的表做对比

CREATE TABLE "test01"."animal_all" (
  "id" varchar NOT NULL,
  "name" varchar(255),
  "age" int4,
  "create_time" date,
  "update_time" date
);

1.4造点数据做查询效率比对

INSERT into animal_all
select random()*1000000000000000,concat('animal_',random()*1000000000000000),18, generate_series('2019-01-01'::date, '2022-12-31'::date, '1 minute') ,generate_series('2019-01-01'::date, '2022-12-31'::date, '1 minute');

INSERT into animal
select random()*1000000000000000,concat('animal_',random()*1000000000000000),18, generate_series('2019-01-01'::date, '2022-12-31'::date, '1 minute') ,generate_series('2019-01-01'::date, '2022-12-31'::date, '1 minute');

1.5查询执行计划比对

EXPLAIN SELECT * from animal_all where create_time='2021-01-01';
EXPLAIN SELECT * from animal where create_time='2021-01-01';

二、10 以前的分区方式

2.1创建主表

CREATE TABLE t_data (id serial, t date, payload text);

2.2继承父表

CREATE TABLE t_data_2022 () INHERITS (t_data);
CREATE TABLE t_data_2021 () INHERITS (t_data);
CREATE TABLE t_data_2020 () INHERITS (t_data);
CREATE TABLE t_data_2019 (special text) INHERITS (t_data);

2.3添加约束

ALTER table t_data_2019 ADD CHECK (t <'2020-01-01');
alter table t_data_2020 add check (t >= '2020-01-01' AND t < '2021-01-01');
ALTER TABLE t_data_2021 ADD CHECK (t >= '2021-01-01' AND t < '2022-01-01');
ALTER TABLE t_data_2022 ADD CHECK (t >= '2022-01-01' AND t < '2023-01-01');

2.4 创建自动创建分区触发器及存储过程

CREATE OR REPLACE FUNCTION "test01"."auto_insert_into_table_partition"()
  RETURNS "pg_catalog"."trigger" AS $BODY$
DECLARE
    id	text ;
    time_column_name 	text ;			
    curMM 		varchar(32);		
    isExist 		boolean;		
    startTime 		text;
    endTime		text;
    strSQL  		text;
    
BEGIN
   
    time_column_name := TG_ARGV[0];
    id := TG_ARGV[1];
   
    EXECUTE 'SELECT $1.'||time_column_name INTO strSQL USING NEW;
-- 打印
		RAISE NOTICE 'strSQL %',strSQL;
    curMM := substr(strSQL,1, 4);
    select count(*) INTO isExist from pg_class where relname = (TG_RELNAME||'_'||curMM);
 
    -- 若不存在, 则插入前需 先创建子分区
    IF ( isExist = false ) THEN  
        -- 创建子分区表
        startTime := to_char( to_timestamp(curMM, 'yyyy'), 'YYYY-MM-dd 00:00:00');
        endTime := to_char( to_timestamp(curMM, 'yyyy') + interval '1 year', 'YYYY-MM-dd 00:00:00');
        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 '||TG_RELNAME||'_'||curMM||'_INDEX_'||id||' ON '
                  ||TG_RELNAME||'_'||curMM||' ('||id||');' ;
        EXECUTE strSQL;
       
    END IF;
 
    -- 插入数据到子分区!
    strSQL := 'INSERT INTO '||TG_RELNAME||'_'||curMM||' SELECT $1.*' ;
    EXECUTE strSQL USING NEW;
    RETURN NULL; 
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

2.5 触发器

CREATE TRIGGER insert_table_partition
  BEFORE INSERT
  ON t_data
  FOR EACH ROW
  EXECUTE PROCEDURE auto_insert_into_table_partition('t','id');

2.6 插入数据

INSERT INTO t_data (t, payload) VALUES ('2022-02-02', 'some data1112');

2.7 查看执行计划

EXPLAIN SELECT * FROM t_data WHERE t = '2022-02-02';