PostgreSQL rotate table 自动清理调度 - 约束,触发器

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

标签

PostgreSQL , rotate table , 自动清理 , 触发器 , insert into on conflict


背景

时序场景,这个需求很常见:

1、自动清理过期的历史数据,并且要保证清理速度够快,不产生WAL日志。

要么DROP,要么truncate。

2、我们知道PG支持分区表,如果是通过分区的方法来实现,最好不过了,但是需要不停的创建分区,这个目前pg_pathman能够满足自动创建分区。但是自动删除分区还是不够自动。

所以怎么做呢?

一个做法是这样的:

1、固定一个周期的所有分区表(类似list分区),比如以小时为结尾的表,只需要24个。以周为单位的表,只需要7个。以分钟为单位的表,需要60个。

2、自动根据数据插入的时间,触发,并清理(truncate)旧的数据。

这种方法,写入代码是固定的不需要变更。

数据表是一次性创建的,不需要后续再增加。

例子

1、创建周期内的所有明细分区表

以10分钟为单位,创建6个表,代表每个10分钟。

create table t1_0 (id int, info text, crt_time timestamp, check (substring(to_char(crt_time,'yyyymmddhh24miss'), 11, 1)='0'));    
create table t1_1 (id int, info text, crt_time timestamp, check (substring(to_char(crt_time,'yyyymmddhh24miss'), 11, 1)='1'));    
create table t1_2 (id int, info text, crt_time timestamp, check (substring(to_char(crt_time,'yyyymmddhh24miss'), 11, 1)='2'));    
create table t1_3 (id int, info text, crt_time timestamp, check (substring(to_char(crt_time,'yyyymmddhh24miss'), 11, 1)='3'));    
create table t1_4 (id int, info text, crt_time timestamp, check (substring(to_char(crt_time,'yyyymmddhh24miss'), 11, 1)='4'));    
create table t1_5 (id int, info text, crt_time timestamp, check (substring(to_char(crt_time,'yyyymmddhh24miss'), 11, 1)='5'));    

2、创建约束表,或者说调度表(因为我们不能每条记录都触发一个TRUNCATE事件)

以10分钟为周期,清除10分钟前的数据。

create table t_const(crt_time timestamp primary key);  

3、创建分区表的触发器,将数据按周期截断后,写入约束表。

create or replace function tg() returns trigger as $$  
declare  
begin  
  insert into t_const values (to_timestamp(substring(to_char(NEW.crt_time,'yyyymmddhh24miss'), 1, 11)||'000','yyyymmddhh24miss')) on conflict (crt_time) do nothing;  
  return null;  
end;  
$$ language plpgsql strict;  
  
create trigger tg1 after insert on t1_0 for each row execute procedure tg();  
create trigger tg1 after insert on t1_1 for each row execute procedure tg();  
create trigger tg1 after insert on t1_2 for each row execute procedure tg();  
create trigger tg1 after insert on t1_3 for each row execute procedure tg();  
create trigger tg1 after insert on t1_4 for each row execute procedure tg();  
create trigger tg1 after insert on t1_5 for each row execute procedure tg();  

4、创建约束表触发器,触发清除明细表分区的规则。

create or replace function tg_truncate() returns trigger as $$  
declare  
  suffix int := substring(to_char(NEW.crt_time,'yyyymmddhh24miss'), 11, 1)::int;  
begin  
  set lock_timeout = '1s';  
  
  for i in 0..5 loop  
    if i=suffix then  
      continue;  
    end if;  
     
    if suffix=0 and i=5 then  
      continue;  
    end if;  
     
    if i=suffix-1 then  
      continue;  
    end if;  
      
    execute 'truncate t1_'||i;  
  
    raise notice 'truncated %', 't1_'||i;  
  end loop;  
  
    
  return null;  
end;  
$$ language plpgsql strict;  
create trigger tg1 after insert on t_const for each row execute procedure tg_truncate();  

试一试

自动清除

postgres=# insert into t1_0 values (1,now(),'2018-01-02 10:00:40');  
NOTICE:  truncated t1_1  
NOTICE:  truncated t1_2  
NOTICE:  truncated t1_3  
NOTICE:  truncated t1_4  
INSERT 0 1  

如果后面再写入同一个区,不会触发自动清除,符合要求(仅第一条触发)。

postgres=# insert into t1_0 values (1,now(),'2018-01-02 10:00:40');  
INSERT 0 1  
postgres=# insert into t1_0 values (1,now(),'2018-01-02 10:01:50');  
INSERT 0 1  

小结

使用分区,实现了数据的分区存放。(目前如果使用原生分区表的话,写入会对主表和所有子分区加共享锁,导致无法truncate。建议方法:使用pg_pathman分区,或者直接写子分区。)

《分区表锁粒度差异 - pg_pathman VS native partition table》

使用TRUNCATE,使得清理数据时不会产生WAL日志。

使用触发器,实现了自动的数据清理。

参考

《PostgreSQL 数据rotate用法介绍 - 按时间覆盖历史数据》

《PostgreSQL APP海量FEED LOG实时质量统计CASE(含percentile_disc) - 含rotate 分区表》

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
1月前
|
存储 关系型数据库 数据库
关系型数据库的数据完整性约束
【5月更文挑战第12天】关系型数据库的数据完整性约束
38 2
|
11月前
|
存储 关系型数据库 Java
postgresql清理表空间
postgresql清理表空间
231 0
|
Oracle 安全 关系型数据库
如何在openGauss/PostgreSQL手动清理XLOG/WAL 文件?
openGauss/PostgreSQL中的预写式日志WAL(Write Ahead Log),又名Xlog或redo log,相当于oracle的online redo log, 不同的是oracle online redo log是提前创建几组滚动使用,但在opengauss中只需要本配置参数控制WAL日志的周期,数据库会一直的创建并自动清理,但存在一些情况WAL日志未清理导致目录空间耗尽,或目录空间紧张时手动删除wal日志时,比如如何确认在非归档模式下哪些WAL日志文件可以安全删除?
657 0
|
9月前
|
算法 关系型数据库 MySQL
【Mysql-InnoDB 系列】幻读、死锁与事务调度
本篇继续分析Mysql InnoDB引擎中的幻读、死锁和事务调度的相关问题
68 0
|
关系型数据库 分布式数据库 PolarDB
PolarDB 物理复制刷脏约束问题和解决
PolarDB 目前物理复制到了ro 开始刷120s apply_lsn 不推进的信息以后, 即使压力停下来也无法恢复, 为什么? 如下图所示: ![image-20230410025547807](https://raw.githubusercontent.com/baotiao/bb/main/uPic/image-20230410025547807.png)
94 0
|
SQL 移动开发 关系型数据库
PostgreSQL 执行计划,成本公式解说,代价因子校准,自动跟踪SQL执行计划(三)|学习笔记
快速学习PostgreSQL 执行计划,成本公式解说,代价因子校准,自动跟踪SQL执行计划(三)
807 0
PostgreSQL 执行计划,成本公式解说,代价因子校准,自动跟踪SQL执行计划(三)|学习笔记
|
SQL 存储 关系型数据库
9 PostgreSQL 点对点多主表级复制-触发器篇|学习笔记
快速学习9 PostgreSQL 点对点多主表级复制-触发器篇
363 0
9 PostgreSQL 点对点多主表级复制-触发器篇|学习笔记
|
弹性计算 容灾 关系型数据库
PostgreSQL PITR 任意时间点恢复过程中如何手工得到recovery需要的下一个WAL文件名 - 默认情况下restore_command自动获取
标签 PostgreSQL , recovery , recovery.conf , restore_command , timeline , 时间线 , next wal , PITR , 时间点恢复 背景 PostgreSQL数据库支持PITR时间点恢复。默认情况下,只需要配置目标是时间点,resotre_command即可,PG会自动调用resotre_command去找需要的WA
1387 0
|
关系型数据库 数据库 PostgreSQL
开发踩坑记录之三:PostgreSQL数据库表唯一性约束失效
在设计数据库表过程中,我们通常会对数据库表进行唯一性约束,以防止事务不一致导致的相同数据的重复插入问题。但是在实际开发中发现,即使设置了数据库表的唯一性约束,仍然出现了相同数据重复插入的问题。
|
SQL 数据可视化 关系型数据库
postgresql如何设置自动增长
postgresql如何设置自动增长
394 0

相关产品

  • 云原生数据库 PolarDB