PostgreSQL扩展之Timescaledb：时间序列数据

小知识：什么是时序数据？

时间序列数据是一系列按时间顺序排列的数据点，常见于各种应用中，如监控、物联网（IoT）、金融分析、日志记录等。这些数据点通常包含时间戳和与之相关联的值，例如某个传感器在特定时间点的读数。

简介

TimescaleDB是一个开源的分布式时序数据库，它构建在流行的关系数据库管理系统PostgreSQL之上。TimescaleDB专门设计用于存储和查询大规模时序数据，如传感器数据、监控数据、日志数据和其他时间相关的信息。

TimescaleDB具有以下特点：

1. 与PostgreSQL兼容：TimescaleDB是一个PostgreSQL扩展，因此它兼容PostgreSQL，这意味着您可以使用标准的SQL语法来查询和管理时序数据，同时还能够利用PostgreSQL的强大功能。

2. 水平扩展：TimescaleDB支持水平扩展，允许在需要时添加更多的节点，以处理大规模的时序数据，这使得它非常适合在云环境中构建弹性和高可用性的时序数据存储。

3. 优化的查询性能：TimescaleDB使用了分区和数据分片技术，将数据分散到多个分区中，从而可以并行处理查询操作，提供优化的查询性能。

4. 连续聚合：这是TimescaleDB的一个强大特性，它允许在数据插入的同时计算和维护聚合数据，从而大大减少了后续查询的计算成本。

5. 自动数据分层：TimescaleDB支持数据分层，可以将历史数据分为不同的层级，从而更有效地管理长期存储的数据。这有助于在保持查询性能的同时控制存储成本。

6. 高可用性和容错性：TimescaleDB支持在集群中复制数据以实现高可用性和容错性，确保数据的可靠性和持久性。

7. 丰富的时间序列函数和操作：TimescaleDB提供了许多针对时间序列数据的内置函数和操作，使您可以轻松地进行时间序列分析和操作。

总的来说，TimescaleDB是一个功能强大、可扩展且灵活的时序数据库解决方案，适用于各种需要处理大规模时序数据的应用场景。

接下来，我们来介绍如何使用 TimescaleDB。

准备工作

登录MemFire Cloud平台，创建一个新应用，如下图所示：

应用创建成功后，即可获得一个云端的Postgres数据库。

启用TimescaleDB扩展

1.通过控制台页面开启timescaledb扩展。

（1）转到仪表板中的数据库页面。

（2）点击侧边栏中的 扩展。

（3）搜索 "timescaledb" 并启用该扩展。

另外，也可以通过在SQL编辑器页面，执行如下SQL语句来启用/禁用扩展。

-- Enable the "timescaledb" extension
create extension timescaledb;
​
-- Disable the "timescaledb" extension
drop extension if exists timescaledb;

使用教程

创建超表（Hypertable）

在 TimescaleDB 中，超表是用于存储时序数据的特殊表。为了演示 timescaledb 如何工作，让我们考虑一个简单的例子，我们有一个表存储来自不同传感器的温度数据。我们将创建一个名为conditions的表，并为两个传感器存储数据。

首先，我们创建一个超级表，这是一个基于时间间隔划分为块的虚拟表。超级表作为实际表的代理，使查询和管理时间序列数据变得容易。

CREATE TABLE conditions (  
    time TIMESTAMPTZ NOT NULL,  
    device_id INT NOT NULL,  
    temperature FLOAT NOT NULL  
);  
  
-- 将普通表转换为超表  
SELECT create_hypertable('conditions', 'time');

插入数据

现在，您可以向超表中插入时序数据：

INSERT INTO conditions (time, device_id, temperature) VALUES  
('2023-10-23 09:00:00', 1, 23.5),
('2023-10-23 09:00:00', 2, 21.2),
('2023-10-23 09:05:00', 1, 24.5),
('2023-10-23 09:05:00', 2, 22.3),
('2023-10-23 09:10:00', 1, 25.1),
('2023-10-23 09:10:00', 2, 23.9),
('2023-10-23 09:15:00', 1, 24.9),
('2023-10-23 09:15:00', 2, 22.7),
('2023-10-23 09:20:00', 1, 24.7),
('2023-10-23 09:20:00', 2, 23.5);

在表编辑器中查看，如下：

查询数据

您可以使用标准的 SQL 查询语句来查询时序数据。以下是一些示例：

1.查询指定时间范围内的数据

SELECT * FROM conditions WHERE time BETWEEN '2023-10-23 09:00:00' AND '2023-10-23 11:00:00';

在SQL编辑器中执行上述SQL命令，如下：

2.按设备分组查询数据

SELECT device_id, AVG(temperature) AS avg_temperature  
FROM conditions  
GROUP BY device_id;

在SQL编辑器中执行上述SQL命令，如下：

3.使用时间桶（Time Buckets）查询数据

TimescaleDB 提供了时间桶函数，用于将数据按照指定的时间间隔进行分组。以下是一个示例：

SELECT   
    time_bucket('5 minutes', time) AS time_interval,  
    AVG(temperature) AS avg_temperature  
FROM conditions  
GROUP BY time_interval;

在SQL编辑器中执行上述SQL命令，如下：

总结

本教程介绍了如何使用 TimescaleDB 进行时序数据的存储、查询和分析。通过创建超表、插入数据、执行查询等功能，您可以高效地处理和管理大规模时序数据。希望这个教程对您有所帮助！

• 官方 timescaledb 文档：Timescale Documentation | Use Timescale