如何根据行号高效率的清除过期数据 - 非分区表，数据老化实践

1 minute read

背景

数据按时间维度老化，删除或转移，是很多业务都有的需求。

例如业务的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毫秒。