PostgreSQL 数据rotate用法介绍 - 按时间覆盖历史数据
背景
在某些业务场景中，数据有冷热之分，例如业务只关心最近一天、一周或者一个月的数据。对于历史的数据可以丢弃。
比如某些指标的监控场景，保留一周的监控数据，历史的都可以丢弃。
如何丢弃历史数据？或者说如何实现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。
交换表名时，需要一个临时表名，名字不能被占用。
祝使用愉快。