第十二章时间序列数据处理

第一节 TimescaleDB 插件集成与使用

目标：了解时间序列数据的特点和挑战，并学习如何在 PostgreSQL 中集成和使用 TimescaleDB 扩展，将其转变为一个世界级的时序数据库。

时间序列数据（Time-Series Data） 是指按时间顺序记录的一系列数据点。它是当今世界增长最快、最普遍的数据类型之一，广泛应用于以下领域：

物联网（IoT）：传感器读数、设备状态。
金融：股票价格、交易记录。
DevOps & 监控：CPU 使用率、内存、网络流量。
物流：车辆轨迹、包裹状态更新。

时序数据的挑战：

写入量巨大：数据以极高的速率持续不断地产生。
以时间为核心：查询通常以时间范围为主要过滤条件。
数据保留策略：近期数据需要频繁访问，而远期数据可能需要降采样或归档删除。

虽然可以使用原生表分区来处理时序数据，但管理起来非常繁琐。TimescaleDB 是一个开源的 PostgreSQL 扩展，它将 PostgreSQL 变成了一个功能全面、性能卓越的时序数据库，并完美地解决了上述所有挑战。

TimescaleDB 的核心概念：Hypertable

TimescaleDB 的核心是 Hypertable。从用户的角度看，Hypertable 就是一个普通的 PostgreSQL 表，你可以对它进行 INSERT, UPDATE, DELETE, SELECT 等所有标准操作。

但在底层，TimescaleDB 会自动地、透明地将这个 Hypertable 按时间和空间维度进行分区，分割成许多子表，这些子表被称为 Chunks。

Hypertable Architecture 图片来源: TimescaleDB 官方网站

这种架构的优势：

自动分区：你只需创建一次 Hypertable，TimescaleDB 会在后台自动创建和管理新的 Chunks，无需手动干预。
查询优化：查询时，TimescaleDB 的查询规划器会自动进行“分区裁剪”，只扫描与时间范围相关的 Chunks，极大地提升了查询性能。
全 SQL 支持：你可以在 Hypertable 上使用 PostgreSQL 的全部功能，包括 JOIN、窗口函数、GIS 地理空间函数等，无需学习新的查询语言。

安装和配置 TimescaleDB

TimescaleDB 是一个第三方扩展，需要单独安装。

1. 添加 TimescaleDB 软件源

1
2
3
# Debian/Ubuntu
sudo add-apt-repository ppa:timescale/timescaledb-ppa
sudo apt-get update

2. 安装 TimescaleDB

1
2
# 安装与你的 PostgreSQL 版本匹配的包
sudo apt-get install timescaledb-2-postgresql-15

3. 配置 PostgreSQL 与 Apache AGE 类似，TimescaleDB 也需要被添加到 shared_preload_libraries。

编辑 postgresql.conf 文件：

1
2
# 如果已有其他库，用逗号分隔
shared_preload_libraries = 'timescaledb'

重启 PostgreSQL 服务以使配置生效。

4. 运行 timescaledb-tune TimescaleDB 提供了一个方便的命令行工具 timescaledb-tune，它可以根据你的硬件配置自动优化 PostgreSQL 的参数，以达到最佳性能。

1
2
sudo timescaledb-tune --yes --quiet
# 该命令会自动修改 postgresql.conf 并建议重启

再次重启 PostgreSQL 服务。

5. 在数据库中创建扩展

1
CREATE EXTENSION IF NOT EXISTS timescaledb;

第一个 Hypertable

让我们创建一个用于存储设备温度读数的 Hypertable。

第一步：创建标准的关系表

1
2
3
4
5
CREATE TABLE device_temps (
    time TIMESTAMPTZ NOT NULL,
    device_id TEXT NOT NULL,
    temperature NUMERIC NOT NULL
);

第二步：将其转换为 Hypertable 使用 create_hypertable 函数，指定时间列和分区键。

1
SELECT create_hypertable('device_temps', 'time');

默认情况下，TimescaleDB 会为每 7 天的数据创建一个新的 Chunk。

第三步：插入和查询数据 现在，你可以像操作普通表一样操作 device_temps。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
-- 插入数据
INSERT INTO device_temps (time, device_id, temperature)
SELECT
    NOW() - (s * INTERVAL '1 second'),
    'device-' || (s % 4),
    random() * 100
FROM generate_series(1, 100000) AS s;

-- 查询某个设备最近一小时的数据
SELECT * FROM device_temps
WHERE device_id = 'device-1' AND time > NOW() - INTERVAL '1 hour'
ORDER BY time DESC;

这个查询会非常高效，因为它只会扫描最近的那个 Chunk。

📌 小结

TimescaleDB 通过巧妙的 Hypertable 和 Chunk 机制，将 PostgreSQL 的可靠性和强大的 SQL 功能与专业时序数据库的高性能写入和查询能力完美结合。

它自动化了最繁琐的分区管理工作。
它对用户完全透明，你仍然在使用你熟悉的 PostgreSQL 和 SQL。
它提供了丰富的时序分析函数和数据管理策略（如压缩和数据保留），我们将在后续章节中探讨。

对于任何涉及时间序列数据的 PostgreSQL 项目，TimescaleDB 都应该是你的首选方案。

文章目录