标签
PostgreSQL , 数据老化 , 数据过期 , 行号 , array in , oss外部表
背景
数据按时间维度老化，删除或转移，是很多业务都有的需求。
例如业务的FEED数据，CDN的日志数据，物联网的跟踪数据等，有时间维度，可能再有状态值（标记最终状态）。
阿里云RDS PG， HDB PG都对接了OSS存储，可以在OSS中存储冷数据。
我们可以将老化数据直接删除，也可以将老化数据删除并写入OSS外部表。
而假如我们的表是按老化字段分区的，那么我们可以通过DROP 分区表的方式来老化。
按分区老化
postgres=# create table t_range(id int, info text, crt_time timestamp) partition by range (crt_time);
CREATE TABLE
分区
postgres=# create table t_range_0_201610 partition of t_range (id primary key, info , crt_time ) for values from ('2016-10-01') to ('2016-11-01'); -- >= 20161001 and < 20161101
CREATE TABLE
postgres=# create table t_range_0_201611 partition of t_range (id primary key, info , crt_time ) for values from ('2016-11-01') to ('2016-12-01'); -- >= 20161101 and < 20161201
CREATE TABLE
按分区老化
postgres=# drop table t_range_0_201610;
DROP TABLE
按分区老化并转换为oss表
1、创建OSS外部表
create foreign table ft_xxx ..... ;
https://help.aliyun.com/knowledge_detail/43352.html
2、将需要老化的数据写入外部表
insert into ft_xxx select * from 老化分区;
3、修改外部表的约束（指定老化分区的时间区间）
alter foreign table ft_xxx add constraint ck1 check(xx between x and x);
4、将外部表挂到主表下面继承（可选，如果业务还需要直接查询）
alter foreign table inherit 父表;
5、删掉老化分区
drop table t_range_0_201610;
按字段老化
按老化字段来老化，指表没有分区时，通过DELETE语句来老化。
创建测试表，假设STATE字段用来标记记录的最终状态，只有达到最终状态，并且时间大于老化时间的记录可以被老化。
create table tbl (id int , info text, state int, crt_time timestamp);
创建索引
create index idx_tbl_1 on tbl (crt_time) where state=?;
一次老化N条数据
delete from tbl where ctid = any (
array
(
select ctid from tbl where crt_time < ? and state=? order by crt_time limit 1000000 -- 一次老化100万条
)
);
vacuum tbl;
一次老化N条数据，并迁移到OSS外部表
使用CTE语法，删除并写入。
with tmp as (
delete from tbl where ctid = any (
array
(
select ctid from tbl where crt_time < ? and state=? order by crt_time limit 1000000 -- 一次老化100万条
)
) returning *
)
insert into oss外部表 select * from tmp;
vacuum tbl;
老化速度，一亿记录，老化100万条，约380毫秒。
调度
《PostgreSQL Oracle 兼容性之 - DBMS_JOBS - Daily Maintenance - Timing Tasks(pgagent)》
