工作中,遇到如下业务需求:用户可以自定义历史数据的保存期限,且对过期数据不作保留(即定期删除),该类数据表的数量级在6千万/年。
前期处理方案:按月分表,由于在项目前期,限制用户只能保存一年的数据量,通过PG的Rule分表,并通过一张中间表,进行新月份数据的录入更替。简单Rotate用法,在月份更替零点执行即可,因DDL支持事务,可以对失败操作进行回滚。
使用Rule创建分区表的操作不再累述,因为每年有固定的12个月,举例,当前5月份数据存放在table_xxx_5表中,那么去年6月份的数据就存放在table_xxx_5表中,而去年5月份的数据,存放在table_xxx_med表中,以便查询5月剩余天数在去年的历史数据,轮询数据的方法如下:
1. 开始事务
BEGIN;
SET LOCK_TIMEOUT = '30's;
2. 查询NextMonth
SELECT EXTRACT(MON FROM NOW()) + 1;
3. 删除table_xxx_med表约束
ALTER TABLE table_xxx_med DROP CONSTRAINT IF EXISTS ck_mon;
4. 删除table_xxx_med数据
TRUNCATE table_xxx_med;
5. 重命名table_xxx_med,以便于将table_xxx_05重名为table_xxx_med
ALTER TABLE table_xxx_med RENAME TO table_xxx_med_temp;
ALTER TABLE table_xxx_05 RENAME TO table_xxx_med;
6. 添加约束,重命名表
ALTER TABLE table_xxx_med_temp ADD CONSTRAINT ck_mon CHECK ( EXTRACT ( MON FROM crt_time ) = 5 );
ALTER TABLE table_xxx_med_temp RENAME TO table_xxx_05;
7. 提交或回滚
COMMIT;
ROLLBACK;
如果对于数据查询的实时性要求很高,即不能容忍零点时事务执行存在对业务的Delay时长,那么可以再添加一张中间表,为下月数据平滑切换提供存储空间,在任意业务空闲时间点执行即可。
用户自定义数据保存期限方案:
在主表上触发器,每当有数据插入时,判断是否存在对应年份+月份的分区表,如果存在,则直接插入,如果不存在,则重新创建年份+月份分区表,并创建对应索引;对过期月份的分区表,直接DROP。
不足之处:触发器是针对EACH ROW的,在数据插入方面的性能不及Rule形式的表分区,但测试得到E3下的插入效率远大于1K/s,满足业务场景需求。
具体实现如下:
CREATE OR REPLACE FUNCTION dynamic_create_table_func () RETURNS TRIGGER AS $BODY$
DECLARE
date_catalog TEXT;
sql_statement TEXT;
sql_create TEXT;
sql_index TEXT;
BEGIN
SELECT to_char( NEW.dp_crt, 'YY_MM' ) INTO date_catalog;
sql_statement := 'insert into dynamic_partition_table_' || date_catalog || ' (dp_id, dp_content, dp_crt, part_x) values (nextval(''dynamic_partition_table_dp_id_seq''), $1.dp_content, $1.dp_crt, ' || to_char( NEW.dp_crt, 'YYMM' ) || ')';
EXECUTE sql_statement USING NEW;
RETURN NULL;
EXCEPTION
WHEN UNDEFINED_TABLE THEN
sql_create := 'create table if not exists dynamic_partition_table_' || date_catalog || ' (check(part_x = ' || to_char( NEW.dp_crt, 'YYMM' ) || ')) inherits (dynamic_partition_table);';
EXECUTE sql_create;
sql_index := 'create index dynamic_partition_table_' || date_catalog || '_dp_id_idx on dynamic_partition_table_' || date_catalog || '(dp_id);';
EXECUTE sql_index;
sql_index := 'create index dynamic_partition_table_' || date_catalog || '_dp_crt_idx on dynamic_partition_table_' || date_catalog || '(dp_crt);';
EXECUTE sql_index;
EXECUTE sql_statement USING NEW;
RETURN NULL;
END;
$BODY$
LANGUAGE'plpgsql';
CREATE TRIGGER dynamic_partition_table_insert_trg BEFORE INSERT ON dynamic_partition_table FOR EACH ROW EXECUTE PROCEDURE dynamic_create_table_func ();
创建的表名格式,table_xxx_18_05。
各位前辈,晚辈初学PG不才,如有更优的方案,望不吝明示。