postgreSQL数据自动分区分表

postgreSQL数据自动分区分表

今天客户反馈说，系统有个模块访问有点慢。于是乎点进去瞅一瞅；发现有一个表是存储同步过来的实时数据；数据量已经到了千万的级别了。因此想说给这个表做个分区。这个项目用的数据库是PG；但由于我之前基本都是在用mysql的数据库；所以度年了一下PG的分表。网络上还是有蛮多的。发帖只是为了做个笔记，便于自己以后查阅！！！

分表过程可以参考：

PostgreSQL分区表（Table Partitioning）应用

贴上代码

-- my_table为要分区的源表
-- create_time 为分表字段（此处，根据这个时间字段，按年分表）
CREATE TRIGGER insert_my_table_trigger
BEFORE INSERT
ON my_table
FOR EACH ROW
EXECUTE PROCEDURE auto_insert_into_my_table('create_time');


-- 触发器函数
CREATE OR REPLACE FUNCTION auto_insert_into_my_table()
  RETURNS trigger AS 								-- 触发器函数必须是无参的，并且返回值为trigger
$$
DECLARE
    colunmName		text;			-- 父表中用于分区的时间字段的名称[必须首先初始化!!!]，此处是create_time
    yearStr 		varchar(6);		-- 'YYYY'字串,用做分区子表的后缀
    tbExist 		boolean;		-- 分区子表 是否存在标志位
    stm 			text;			-- 分区开始时间（一年的01月01日）
    etm 			text;			-- 分区截止时间（一年的12月31日）
    sqlStr 			text;			-- sql语句字符串变量
    
BEGIN
	-- 当一个PL/pgSQL 函数作为一个触发器被调用时，系统自动在最外层的块创建一些[特殊的变量]。
    -- 调用前,必须首先初始化(时间字段名):colunmName [直接从调用参数中获取!!！]；
	-- TG_ARGV：为[特殊的变量]之一，数据类型是text类型的数组。
	-- 表示CREATE TRIGGER命令传给触发器过程的所有参数。
	-- 下标从0开始。TG_ARGV[0]表示第一个参数，TG_ARGV[1]表示第二个参数，以此类推。 
	-- 如果下标小于0或大于等于CREATE TRIGGER命令传给触发器过程的参数的个数，将会返回一个空值。
	-- 此处取第一个参数，既create_time
    colunmName := TG_ARGV[0];
   
    -- 判断对应分区表 是否已经存在?
	-- NEW：为[特殊的变量]之一;数据类型是RECORD。
	-- 对于行级触发器，它存有INSERT或UPDATE操作产生的新的数据行。
    EXECUTE 'SELECT NEW.'||colunmName INTO sqlStr;
    yearStr := to_char(sqlStr::timestamp , 'YYYY');
	-- pg_class：postgreSQL内部表;记录表和几乎所有具有列或者像表的东西
	-- TG_TABLE_NAME：数据类型是name，表示触发器作用的表的名字。它与变量TG_RELNAME的作用是一样的。
    select count(*) INTO tbExist from pg_class where relname = (TG_TABLE_NAME||'_'||yearStr);
 
    -- 若不存在, 则插入前需 先创建子分区
    IF ( tbExist = false ) THEN  
        -- 创建子分区表
        stm := yearStr||'-01-01 00:00:00.000';
        etm := to_char(stm::timestamp + interval '1 year', 'yyyy-MM-dd HH24:mi:ss.ms');
        sqlStr := 'CREATE TABLE IF NOT EXISTS '||TG_TABLE_NAME||'_'||yearStr||
                  ' ( 
											CHECK('||colunmName||'>='''|| stm ||''' AND '||colunmName||'< '''|| etm |''' )
                     ) INHERITS ('||TG_TABLE_NAME||') ;'  ;  
        EXECUTE sqlStr;
 
        -- 创建索引
        sqlStr := 'CREATE INDEX IDX_'||TG_TABLE_NAME||'_'||yearStr||'_'||colunmName||' ON '
                  ||TG_TABLE_NAME||'_'||yearStr||' ('||colunmName||');' ;
        EXECUTE sqlStr;
       
    END IF;
 
    -- 插入数据到对应的分表中
    sqlStr := 'INSERT INTO '||TG_TABLE_NAME||'_'||yearStr||' SELECT NEW.*' ;
    EXECUTE sqlStr;
    RETURN NULL; 
END
$$ LANGUAGE plpgsql;