Pg分区表配置与实现

数据库加速查询的方法，包括添加索引，调整数据库引擎的参数，将日志目录放单独的磁盘分区等。实际应用中，历史数据会随着时间推移逐渐增多，且重要性逐渐降低，但又不能轻易删除。此时可考虑将该表按某个时间字段配置为分区表， 并为该时间字段在每个分区表建立索引，这对于过滤条件中包含该时间字段，查询特定时间范围(特定分区表)的数据，会比直接查询原表快很多----原表数据量很大时。

当前PgSQL支持继承式和声明式的分区表。这里主要介绍继承式分区表，继承式分区表安装分区字段的内容，可分为：范围分区 每个分区表包含一个或多个字段组合的一部分，并且每个分区表的范围互不重叠。比如可近日期范围分区；列表分区　分区表显示列出其所包含的key值；父表是普通表并且正常情况下并不存储数据。

创建分区表，包含如下步骤：
– 创建父表，父表中不需要创建主键、索引、约束等；
– 创建分区表，分区表必须继承父表，不新加字段，并手动创建索引；
– 定义一个规则（rule） 或触发器（trigger），把对父表的写入重定向到对应分区表；

1、创建父表

DROP TABLE IF EXISTS "aggregate_data"."alarm_data";
CREATE TABLE "aggregate_data"."alarm_data" (
  "id" 						bigserial,
  "stat_id" 				varchar(32) 	COLLATE "pg_catalog"."default",
  "sid" 					varchar(32) 	COLLATE "pg_catalog"."default",
  "cared" 					int4 			DEFAULT 0,
  "open_time" 				timestamp(0),
  "close_time" 				timestamp(0),
  "alm_count" 				int4,
  "close_count" 			int4 			DEFAULT 0,
  "create_time" 			timestamp(0),
  "recover_time" 			timestamp(0),
......
);

2、插入数据重定向函数（包括动态创建分区表+插入数据：建限制 + 建索引）

DROP FUNCTION IF EXISTS "aggregate_data"."alarm_data_partition_trigger_add"();
CREATE OR REPLACE FUNCTION "aggregate_data"."alarm_data_partition_trigger_add"()
  RETURNS "pg_catalog"."trigger" AS $BODY$  
DECLARE date_text TEXT;
DECLARE date_text2 TEXT; 
DECLARE insert_statement TEXT;  
BEGIN  
    SELECT to_char(NEW.create_time, 'YYYYMMDD') INTO date_text;  
    SELECT to_char(NEW.create_time + '1 day', 'YYYYMMDD') INTO date_text2;  
    insert_statement := 'INSERT INTO aggregate_data.alarm_data_' || date_text ||' VALUES ($1.*)';  
    EXECUTE insert_statement USING NEW;  
    RETURN NULL;  
    EXCEPTION  
    WHEN UNDEFINED_TABLE  
    THEN  
        EXECUTE  
            'CREATE TABLE IF NOT EXISTS aggregate_data.alarm_data_' || date_text  
            || '(CHECK (''' || date_text || '''::date <= create_time and create_time < ''' || date_text2 || '''::date) ) ' 
			|| 'INHERITS (aggregate_data.alarm_data)';  
        RAISE NOTICE 'CREATE NON-EXISTANT TABLE aggregate_data.alarm_data_%', date_text;  
        EXECUTE  
            'CREATE INDEX alarm_datadate_key_' || date_text  
            || ' ON aggregate_data.alarm_data_' || date_text || '(create_time)';  
        EXECUTE insert_statement USING NEW;  
    RETURN NULL;  
END;  
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

3、触发器，调用插入重定向函数

CREATE TRIGGER "alarm_data_partition_trigger_add" 
BEFORE INSERT ON "aggregate_data"."alarm_data"
FOR EACH ROW EXECUTE PROCEDURE "aggregate_data"."alarm_data_partition_trigger_add"();

注意事项

1、确保配置文件postgresql.conf中的constraint_exclusion参数设置为on或partition(默认值)。如果该参数被disable，则基于分区表的查询性能不会得到优化，甚至比原表直接使用索引性能更低；当constraint_exclusion为on或者partition时，查询计划器会根据分区表的检查限制将对主表的查询限制在符合检查限制条件的分区表上，避免对不符合条件的分区表的扫描。

2、分区表的约束检查里的分区字段比须直接与常量比较，否则无法排除对不符合条件的分区表的扫描，即无法优化查询性能。如：CHECK(to_char(create_time,'YYYYMMDD') = '20240126') 和 CHECK(to_timestamp('20240126', 'YYYYMMDD') <= create_time and create_time < to_timestamp('20240127', 'YYYYMMDD'))
都不能使constraint_exclusion生效，CHECK('20240126'::date <= create_time and create_time < '20240127'::date) 可使constraint_exclusion生效。

3、分区字段被用作过滤条件时，where语句只能包含常量而不能使用参数化的表达式，因为这些表达式只有在运行时才能确定其值，而planner在真正执行query之前无法判定哪些分区表应该被使用。跳过不符合条件分区表是通过planner根据分区表的检查限制条件实现的，而非通过索引；

4、使用分区表后，每个分区表独立建立索引，比原表的索引小，查询和更新都快；

5、大量的分区表会极大地增加查询计划时间。表分区在多达几百个分区表时能很好地发挥优势，但不要使用多达几千个分区表。

---

其他

-- 删除触发器
DROP TRIGGER alarm_data_partition_trigger_add ON alarm_data;

-- 删除函数
DROP FUNCTION IF EXISTS "aggregate_data"."alarm_data_partition_trigger_add"();

-- 分表删除约束
ALTER TABLE aggregate_data.alarm_data_20240101 drop CONSTRAINT alarm_data_20240101_create_time_check;

-- 分表删除索引

-- 分表删除继承关系
ALTER TABLE tbl_partition_2016_01 NO INHERIT tbl_partition;

-- 分表添加继承关系
ALTER TABLE test1 INHERIT tbl_partition;

-- 其它配置
set constraint_exclusion = partition;
set constraint_exclusion = on;
set constraint_exclusion = off;