PostgreSQL 数据rotate用法介绍-阿里云开发者社区

开发者社区> 德哥> 正文

PostgreSQL 数据rotate用法介绍

简介: 标签 PostgreSQL , 按时间覆盖历史数据 背景 在某些业务场景中,数据有冷热之分,例如业务只关心最近一天、一周或者一个月的数据。对于历史的数据可以丢弃。 比如某些指标的监控场景,保留一周的监控数据,历史的都可以丢弃。
+关注继续查看

标签

PostgreSQL , 按时间覆盖历史数据


背景

在某些业务场景中,数据有冷热之分,例如业务只关心最近一天、一周或者一个月的数据。对于历史的数据可以丢弃。

比如某些指标的监控场景,保留一周的监控数据,历史的都可以丢弃。

如何丢弃历史数据?或者说如何实现rotate?

1. 使用delete, 删除7天前的数据。

delete from table where crt_time<=now()-interval '7 day';  

这种方法会带来额外的开销,包括写REDO日志,垃圾回收等。如果删除的数据量很大,还需要重新收集统计信息,甚至收集不及时会导致统计信息不准确。

另一方面,还可能引入merge join的问题。

《PostgreSQL merge join 评估成本时可能会查询索引 - 硬解析务必引起注意 - 批量删除数据后, 未释放empty索引页导致mergejoin执行计划变慢 case》

2. 使用分区表,轮询使用,并且使用truncate清理分区。

这种方法可以避免DELETE带来的问题。

但是使用不当也可能引入新的问题: truncate是DDL操作,rename table也是DDL操作,建议对DDL加上锁超时,否则DDL等待会堵塞任意其他SQL。

交换表名时,需要一个临时表名,名字不能被占用。

下面就以分区表为例,讲一下数据rotate用法。

例子

以保留一周数据为例,看看第二种方法如何来实施。

得益于PostgreSQL支持DDL事务。

方法

一共9张表,一张主表,8张分区表,其中7个对应dow,还有一个对应默认分区(交换分区)。

dow分区使用约束,好处是查询时可以根据约束直接过滤分区。

1. 创建主表

create table test(id int primary key, info text, crt_time timestamp);  

2. 创建分区

create table test0(like test including all, constraint ck check(extract(dow from crt_time)=0)) inherits(test);  
create table test1(like test including all, constraint ck check(extract(dow from crt_time)=1)) inherits(test);  
create table test2(like test including all, constraint ck check(extract(dow from crt_time)=2)) inherits(test);  
create table test3(like test including all, constraint ck check(extract(dow from crt_time)=3)) inherits(test);  
create table test4(like test including all, constraint ck check(extract(dow from crt_time)=4)) inherits(test);  
create table test5(like test including all, constraint ck check(extract(dow from crt_time)=5)) inherits(test);  
create table test6(like test including all, constraint ck check(extract(dow from crt_time)=6)) inherits(test);  
  
create table test_def(like test including all) inherits(test);  

3. SELECT/UPDATE/DELETE数据时,直接操作主表,代入时间条件,可以过滤分区

postgres=#  explain select * from test where crt_time=now() and extract(dow from crt_time)=0; 
                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Append  (cost=0.00..65.20 rows=3 width=44)
   ->  Seq Scan on test  (cost=0.00..0.00 rows=1 width=44)
         Filter: ((crt_time = now()) AND (date_part('dow'::text, crt_time) = '0'::double precision))
   ->  Seq Scan on test0  (cost=0.00..32.60 rows=1 width=44)
         Filter: ((crt_time = now()) AND (date_part('dow'::text, crt_time) = '0'::double precision))
   ->  Seq Scan on test_def  (cost=0.00..32.60 rows=1 width=44)
         Filter: ((crt_time = now()) AND (date_part('dow'::text, crt_time) = '0'::double precision))
(7 rows)

4. INSERT时,建议程序根据crt_time的dow自动拼接表名,直接对分区表进行操作。

如果程序不想对分区进行操作,那么可以使用触发器或规则。

例子

create or replace function ins_tg() returns trigger as $$
declare
begin
  case extract(dow from NEW.crt_time)
  when 0 then insert into test0 values (NEW.*);
  when 1 then insert into test1 values (NEW.*);
  when 2 then insert into test2 values (NEW.*);
  when 3 then insert into test3 values (NEW.*);
  when 4 then insert into test4 values (NEW.*);
  when 5 then insert into test5 values (NEW.*);
  when 6 then insert into test6 values (NEW.*);
  end case;
  return null;
end;
$$ language plpgsql strict;

create trigger tg before insert on test for each row execute procedure ins_tg();

insert into test values (1,'test',now()+interval '1 day');
insert into test values (1,'test',now()+interval '2 day');
insert into test values (1,'test',now()+interval '3 day');
insert into test values (1,'test',now()+interval '4 day');
insert into test values (1,'test',now()+interval '5 day');
insert into test values (1,'test',now()+interval '6 day');
insert into test values (1,'test',now()+interval '7 day');

postgres=# select tableoid::regclass , * from test;
 tableoid | id | info |          crt_time          
----------+----+------+----------------------------
 test0    |  1 | test | 2017-03-26 14:40:48.066905
 test1    |  1 | test | 2017-03-27 14:40:50.450942
 test2    |  1 | test | 2017-03-28 14:40:52.271922
 test4    |  1 | test | 2017-03-23 14:40:22.551928
 test5    |  1 | test | 2017-03-24 14:40:24.643933
 test6    |  1 | test | 2017-03-25 14:40:28.138913
 test3    |  1 | test | 2017-03-22 14:40:20.586945
(7 rows)

rotate用法(每天在空闲时间点处理一次即可,DDL支持事务,如果事务失败,可重新发起重试)

在一个事务中完成如下动作

1. 计算明天的dow

2. 清除test_def约束

3. 清除test_def数据

4. test_def重命名test_def_tmp(一个不存在的表名)

5. 明天的分区表,重命名为test_def

6. test_def_tmp添加约束

7. test_def_tmp重命名为明天的分区

例子

1. 开始事务  
begin;  
  
2. 设置锁超时  
set lock_timeout = '60s';  
  
3. 查询明天的dow  
select extract(dow from current_date+1);  
 date_part   
-----------  
         3  
(1 row)  
  
4. 清除test_def约束  
alter table test_def drop constraint IF EXISTS ck;  

5. 清除test_def数据  
truncate test_def;  
  
6. 重命名test_def  
alter table test_def rename to test_def_tmp;  
  
7. 重命名明天的分区表  
alter table test3 rename to test_def;  
  
8. test_def_tmp添加约束  
alter table test_def_tmp add constraint ck check(extract(dow from crt_time)=3);  
  
9. test_def_tmp重命名为明天的分区  
alter table test_def_tmp rename to test3;  
   
10. 提交或回滚
commit;
如果失败,回滚事务。  
rollback;

注意事项:

1. 锁超时

2. 事务失败注意回滚

3. 中间表名必须不存在

4. 约束名统一

小结

使用DELETE的方法清除历史数据,会带来额外的开销,包括写REDO日志,垃圾回收等。如果删除的数据量很大,还需要重新收集统计信息,甚至收集不及时会导致统计信息不准确。

另一方面,还可能引入merge join的问题。

《PostgreSQL merge join 评估成本时可能会查询索引 - 硬解析务必引起注意 - 批量删除数据后, 未释放empty索引页导致mergejoin执行计划变慢 case》

因为PostgreSQL支持DDL封装在事务中,所以也可以使用分区表,轮询使用,并且使用truncate清理分区。

这种方法可以避免DELETE带来的问题。

但是使用不当也可能引入新的问题: truncate是DDL操作,rename table也是DDL操作,建议对DDL加上锁超时,否则DDL等待会堵塞任意其他SQL。

交换表名时,需要一个临时表名,名字不能被占用。

祝使用愉快。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Fundebug支付宝小程序BUG监控插件更新至0.2.0,新增test()方法,报错增加Page数据
摘要: 0.2.0新增fundebug.test()方法,同时报错增加了Page数据。 Fundebug提供专业支付宝小程序BUG监控服务,可以第一时间为您捕获生存环境中小程序的异常、错误或者BUG,及时给开发者发送报警,帮助您快速修复BUG。
1088 0
保洁A/B test——用户数据分析的魔力
    在1990年代中期,宝洁公司的高管们开始一项研发全新除异味产品的秘密计划。宝洁公司花费了几百万美元开发了一种无色且成本低廉的液体,这种液体能够喷涂在有油烟味的衬衣、发臭的沙发、陈旧的夹克以及污损的汽车内饰上来除去异味。
1120 0
怎么设置阿里云服务器安全组?阿里云安全组规则详细解说
阿里云服务器安全组设置规则分享,阿里云服务器安全组如何放行端口设置教程
8453 0
MaxCompute(ODPS)上处理非结构化数据的Best Practice
随着MaxCompute(ODPS)2.0的上线,新增的非结构化数据处理框架也推出一系列的介绍文章,包括 MaxCompute上如何访问OSS数据, 基本功能用法和整体介绍,侧重介绍读取OSS数据进行计算处理; 本文:MaxCompute(ODPS)上处理非结构化数据的Best Practice。
4140 0
VB6.0 读取Excel文件并返还数据集RecordSet
读取Excel文件并返还数据集RecordSet 该方法适用于.xls,.xlsx类型的文件   读取Excel文件的Function: 1 '取得数据集 2 Function getRecordSetForExcels(sFilePath As String, _ 3 ...
989 0
+关注
德哥
公益是一辈子的事, I&#39;m digoal, just do it.
2153
文章
245
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载