PostgreSQL 数据rotate用法介绍

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介: 标签 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。

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

祝使用愉快。

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
27天前
|
SQL Oracle 关系型数据库
实时计算 Flink版操作报错之往GREENPLUM 6 写数据,用postgresql-42.2.9.jar 报 ON CONFLICT (uuid) DO UPDATE SET 语法有问题。怎么解决
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
8天前
|
DataWorks 安全 关系型数据库
DataWorks产品使用合集之使用Flink CDC读取PostgreSQL数据时如何指定编码格式
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
|
1月前
|
关系型数据库 数据库 数据安全/隐私保护
关系型数据库的数据完整性保障
【5月更文挑战第9天】关系型数据库的数据完整性保障
28 1
|
1天前
|
消息中间件 Java 关系型数据库
实时计算 Flink版操作报错合集之从 PostgreSQL 读取数据并写入 Kafka 时,遇到 "initial slot snapshot too large" 的错误,该怎么办
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
154 0
|
4天前
|
关系型数据库 5G PostgreSQL
postgreSQL 导出数据、导入
postgreSQL 导出数据、导入
17 1
|
8天前
|
分布式计算 DataWorks 关系型数据库
DataWorks操作报错合集之离线同步任务中,把表数据同步到POLARDB,显示所有数据都是脏数据,报错信息:ERROR JobContainer - 运行scheduler 模式[local]出错.是什么原因
DataWorks是阿里云提供的一站式大数据开发与治理平台,支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
|
9天前
|
存储 SQL 关系型数据库
使用关系型数据库三级模式存储数据的优缺点
【6月更文挑战第10天】数据模型是DBMS的核心,提供数据透明性和设计指导。包括概念、逻辑和物理三层:概念模型(如ER模型)用于理解和收集需求,逻辑模型(如关系模型)关注设计,物理模型涉及实际存储实现。
17 0
使用关系型数据库三级模式存储数据的优缺点
|
12天前
|
关系型数据库 分布式数据库 数据库
PolarDB产品使用合集之迁移 PolarDB 的数据并启动新的镜像的步骤是什么
PolarDB是阿里云推出的一种云原生数据库服务,专为云设计,提供兼容MySQL、PostgreSQL的高性能、低成本、弹性可扩展的数据库解决方案,可以有效地管理和优化PolarDB实例,确保数据库服务的稳定、高效运行。以下是使用PolarDB产品的一些建议和最佳实践合集。
|
15天前
|
NoSQL 关系型数据库 MongoDB
DTS支持哪些非关系型数据库的数据交互?
【6月更文挑战第4天】DTS支持哪些非关系型数据库的数据交互?
19 1
|
23天前
|
关系型数据库 数据库 数据库管理
关系型数据库数据唯一性保证
【5月更文挑战第17天】
31 1

相关产品

  • 云原生数据库 PolarDB