TimescaleDB和超表 介绍
Timescaledb是基于PostgreSQL数据库的时序数据库，提供了存储和管理时间序列数据的功能：
超表(Hypertable)是 TimescaleDB 中的核心概念,它实际上就是一张表,但是具有以下特点:
按时间自动分割 into 多个分区(chunks)。 每个分区是一个普通的 PostgreSQL 表,存储不同时间范围的数据。 超表提供统一的视图,可以轻松的查询超表中的所有数据。 分区可以根据需要自动增长,或者手动增加。 老的数据分区可以自动删除或者合并,方便数据的维护。
所以超表给我们带来的主要好处是:
存储大量时间序列数据,超出单个 PostgreSQL 表的限制。 按时间自动管理和删除老的数据。 提供整体的视图,可以轻松的查询超表中的所有数据,不用关心底层的分区表。 优化的存储格式可以大大提高查询效率。
总结来说,TimescaleDB 通过超表和其他时间序列优化功能,使 PostgreSQL 成为一个功能强大的时间序列数据库,可以方便的存储和管理大量的时间序列数据。
创建超表之前需要在相应数据库启用timescaledb 插件：
postgres=# CREATE EXTENSION IF NOT EXISTS timescaledb;
启用之后通过如下命令验证：
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
-------------+---------+------------+-------------------------------------------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
timescaledb | 2.9.1 | public | Enables scalable inserts and complex queries for time-series data
1、创建常规数据表
CREATE TABLE conditions ( time TIMESTAMPTZ NOT NULL, location TEXT NOT NULL, temperature DOUBLE PRECISION NULL, humidity DOUBLE PRECISION NULL );
2、常规数据表转换成超表
SELECT create_hypertable('conditions', 'time');
第一个参数是表名，第二个参数是用于分区的时间戳数据的列名
3、默认时间间隔为 7 天。修改时间间隔为 1 天
SELECT set_chunk_time_interval('conditions', INTERVAL '1 days');
数据写入：
insert
into
conditions(time,
location,
temperature,
humidity)
values ('2023-04-07 00:00:00',
'beijing',
20,
40),
('2023-04-08 12:00:00',
'shanghai',
25,
50);
查询超表chunk
select show_chunks('conditions');
显示一天以前的数据块
select show_chunks('conditions', older_than => interval '1 days');
显示4月8日前的数据块
select show_chunks('conditions', older_than => DATE '2023-04-08');
将非空的数据表转换为超表：
SELECT create_hypertable('conditions', 'time', migrate_data=>true);
数据压缩
ALTER TABLE conditions SET ( timescaledb.compress, timescaledb.compress_segmentby = 'time' );
2、创建压缩策略：添加压缩策略来压缩超过3天的块
SELECT add_compression_policy('conditions', INTERVAL '3 days');
chunks删除
（1）手动删除
select drop_chunks('conditions', older_than=> DATE '2023-04-08');
当且仅当块的时间范围完全落在指定的时间戳之前（或之后）时，块才会被删除，因此其余数据仍然可能包含在指定的时间戳之前（或之后）的时间戳
（2）创建自动数据保留策略
案例场景：删除超过7天的原始数据
select add_retention_policy('conditions',INTERVAL '7 days');
查看chunks大小
查看一个超表的大小
select hypertable_size('conditions'); // 默认是字节大小
select pg_size_pretty(hypertable_size('conditions')); // 进行单位转换后的大小
查看所有超表的大小
select hypertable_name, hypertable_size(format('%I.%I',hypertable_schema,hypertable_name)::regclass) from timescaledb_information.hypertables h ;
查看一个超表的详细大小：获取关于超表使用的磁盘空间相信信息，返回表本身的大小信息，表上的任何索引、任何toast tables以及所有toast tables的信息
select * from hypertable_detailed_size('conditions');
查看超表上的索引大小：
select pg_size_pretty(hypertable_index_size('conditions_time_idx')); // 注意这里传入的是索引名称
查看chunk的详细信息
select * from chunks_detailed_size('conditions') order by chunk_name;
转载请注明：西门飞冰的博客 » TimescaleDB时序数据库超表维护
