利用TimescaleDB中的时序表实现高效的数据保留(rete

我的博客： 菱歌's Blog | 听见美好
笔记原文地址：利用TimescaleDB中的时序表实现高效的数据保留(retention/rotation)

TimescaleDB是一个开源的基于PostgreSQL的时间序列数据库，因为其基于PostgreSQL（其实相当于在PostgreSQL中安装一个插件），所以可以使用大家非常熟悉的SQL语句进行查询，同时一些PostgreSQL上的优化策略也可以使用。总之，在需要进行时间序列数据存储、查询时，你可以比较平滑地从传统关系型数据库迁移、过渡到TimescaleDB。TimescaleDB虽然在2015年才成立，但是这几年发展非常快，而且还拿到了Two Sigma的投资，相信也是看中了其强大的金融数据存储处理能力。

需求

在传感数据、金融数据应用场景下，每天的产生数据量非常大，我们常常有在数据库中保留某一段/近期数据，而将比较老的数据以其他形式、介质存档的需求。

在传统数据库中，比较直接的想法是用时间范围作为查询条件，使用delete命令删除相应的数据。但是时序数据量非常大，行删除的开销非常大。而使用TimescaleDB中的时序表（Hypertable）可以高效、快捷地在块（chunk）级别进行数据保留。

安装使用

安装过程在官网Doc上非常清楚了，而且支持多种安装部署方式，比如Docker。在使用中需要注意的是，在安装后要在需要使用TimescaleDB的数据库（要切换到对应数据库）中安装插件：

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

这样才能使用TimescaleDB的诸多功能，否则会报如下错误：

HINT: No function matches the given name and argument types. You might need to add explicit type casts.

创建时序表Hypertable

首先创建普通的数据表，这与PostgreSQL的创建方法无异

CREATE TABLE conditions (
 time        TIMESTAMPTZ       NOT NULL,
 location    TEXT              NOT NULL,
 temperature DOUBLE PRECISION  NULL
);

然后再创建时序表，此时为了满足我们进行数据保留的需求，我们要设置chunk_time_interval参数，在例子中设置为了10分钟，这里实际需要根据业务需求（数据流量、chunk size等）进行调整。

SELECT create_hypertable('conditions', 'time', chunk_time_interval => interval '10 minutes');

随后就可以使用SQL语句进行插入、查询等操作，时序表对于你几乎是透明的。此外，使用时序表后，时间条件查询性能也会有较大的提升。

数据保留

最后，我们可以使用drop_chunks来高效地根据过期时间删除块数据。

SELECT drop_chunks(interval '24 hours', 'conditions');

如果需要定期进行数据清理，可以在crontab中加入计划任务实现自动、定时处理。