PostgreSQL中按时间月份自动创建分区表
出处https://blog.csdn.net/xgb2018/article/details/109244096
PostgreSQL中按时间月份自动创建分区表
前言
1. 创建主表
2. 创建存储过程
3. 创建触发器
4. 踩过的坑
（1）constraint_exclusion属性设置
（2）linux如何执行postgresql的sql脚本
方法一：首先通过psql连接到对应的db：
方法二：直接通过psql命令执行SQL文件
（3）如何查询表分区
前言
工作中遇到千万级单表数据查询优化，博主第一时间想到表分区，特此记录问题的解决过程，避免大家采坑。
数据库表分区把一个大的物理表分成若干个小的物理表，并使得这些小物理表在逻辑上可以被当成一张表来使用。
PostgreSQL数据库中对表做分区，与11g之前的Oracle类似，需要手动创建分区表、索引等。通过继承，比如按时间，每月创建一个表分区，数据记录到对应分区中。
1. 创建主表
创建主表结构, 表名称 three_height_data_value, 其中的时间字段名: data_time
CREATE TABLE "public"."three_height_data_value" (
"data_id" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
"data_time" timestamp(6) NOT NULL,
"data_value" float8 NOT NULL
);
2. 创建存储过程
创建存储过程，使用动态SQL, 将大表分区的运维操作实现自动化。
CREATE OR REPLACE FUNCTION auto_insert_into_three_height_data_value()
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';
endTime := to_char( startTime::timestamp + interval '1 month', 'YYYY-MM-DD HH24:MI:SS');
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_'||time_column_name||' ON '
||TG_RELNAME||'_'||curMM||' ('||time_column_name||');' ;
EXECUTE strSQL;
END IF;
-- 插入数据到子分区!
strSQL := 'INSERT INTO '||TG_RELNAME||'_'||curMM||' SELECT $1.*' ;
EXECUTE strSQL USING NEW;
RETURN NULL;
END
$BODY$
LANGUAGE plpgsql;
说明:
(1) 代码中使用了 TG_ARGV[0] 来获取调用时传入的参数: 用于分区的时间字段名.
(2) 代码中,通过内置参数 TG_RELNAME 获得了父表的表名称.
(3) 首先根据插入时间, 判断对应分区表是否存在? 若存在, 直接插入对应分区子表
(4) 若分区表还不存在, 先创建分区子表和索引, 然后插入数据到所建的子表中.
以上代码, 在PostgreSQL v9.4 中调试通过. 理论上, v8.4以上均支持.
3. 创建触发器
CREATE TRIGGER insert_three_height_data_value_trigger
BEFORE INSERT
ON three_height_data_value
FOR EACH ROW
EXECUTE PROCEDURE auto_insert_into_three_height_data_value('data_time');
4. 踩过的坑
（1）constraint_exclusion属性设置
确保postgresql.conf中的constraint_exclusion配置项没有被disable 。这一点非常重要，如果该参数项被disable，则基于分区表的查询性能无法得到优化，甚至比不使用分区表直接使用索引性能更低。
（2）linux如何执行postgresql的sql脚本
方法一：首先通过psql连接到对应的db：
psql -d db1 -U userA
接着输入密码，进入数据库后，输入：
\i /pathA/xxx.sql
方法二：直接通过psql命令执行SQL文件
这种方式无需先登录数据库，直接用一个命令就可以了
psql -d db1 -U userA -f /pathA/xxx.sql
（3）如何查询表分区
–查询指定分区表
SELECT nmsp_parent.nspname AS parent_schema , parent.relname AS parent , nmsp_child.nspname AS child , child.relname AS child_schema FROM pg_inherits JOIN pg_class parent ON pg_inherits.inhparent = parent.oid JOIN pg_class child ON pg_inherits.inhrelid = child.oid JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace WHERE parent.relname = 'plc_import_data_value';
查询所有区表
SELECT nspname , relname ,COUNT(*) AS partition_num FROM pg_class c , pg_namespace n , pg_inherits i WHERE c.oid = i.inhparent AND c.relnamespace = n.oid AND c.relhassubclass AND c.relkind = 'r' GROUP BY 1,2 ORDER BY partition_num DESC;
如果感觉有帮助，请“点赞”、“关注”、“收藏”哈！
————————————————
版权声明：本文为CSDN博主「肖广彪」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/xgb2018/article/details/109244096
