数据库

timescaledb: 时序数据

timescaledb 是一个专为改进时间序列数据处理而设计的 Postgres 扩展。它在标准 Postgres 数据库基础上提供了可扩展、高性能的时间序列数据存储和查询解决方案。

timescaledb 采用时间序列感知的存储模型和索引技术来提升 Postgres 处理时间序列数据的性能。该扩展根据时间间隔将数据分块存储，使其能够高效扩展，特别适用于大型数据集。数据会被压缩，针对写入密集型工作负载进行优化，并通过分区实现并行处理。timescaledb 还包含一系列专为时间序列数据设计的函数、操作符和索引，可缩短查询时间并简化数据处理。

启用扩展

  1. 进入仪表板的数据库页面
  2. 在侧边栏点击扩展选项
  3. 搜索 timescaledb 并启用该扩展

虽然SQL代码使用的是create extension，但这等同于"启用扩展"。要禁用扩展可以调用drop extension。

最佳实践是在单独的schema（如extensions）中创建扩展，以保持public schema的整洁。

使用方法

为了演示 timescaledb 的工作原理，我们以一个简单的温度传感器数据存储为例。我们将创建一个名为 "temperatures" 的表，存储两个传感器的数据。

首先我们创建一个超表(hypertable)，这是一个基于时间间隔进行分区的虚拟表。超表作为实际表的代理，可以方便地查询和管理时间序列数据。

1
2
3
4
5
6
7
create table temperatures (  time timestamptz not null,  sensor_id int not null,  temperature double precision not null);select create_hypertable('temperatures', 'time');

接下来，我们可以插入一些数据

1
2
3
4
5
6
7
8
9
10
11
12
insert into temperatures (time, sensor_id, temperature)values    ('2023-02-14 09:00:00', 1, 23.5),    ('2023-02-14 09:00:00', 2, 21.2),    ('2023-02-14 09:05:00', 1, 24.5),    ('2023-02-14 09:05:00', 2, 22.3),    ('2023-02-14 09:10:00', 1, 25.1),    ('2023-02-14 09:10:00', 2, 23.9),    ('2023-02-14 09:15:00', 1, 24.9),    ('2023-02-14 09:15:00', 2, 22.7),    ('2023-02-14 09:20:00', 1, 24.7),    ('2023-02-14 09:20:00', 2, 23.5);

最后，我们可以使用 timescaledb 的 time_bucket 函数来查询表格，该函数将时间序列按指定大小(本例中为1小时)划分，并计算每组内 temperature 读数的平均值。

1
2
3
4
5
6
7
8
9
10
select    time_bucket('1 hour', time) AS hour,    avg(temperature) AS average_temperaturefrom    temperatureswhere    sensor_id = 1    and time > NOW() - interval '1 hour'group by    hour;

相关资源