TimescaleDB学习笔记

1 时序数据

时序数据（Time Series Data）是指按照时间顺序采集和记录的一系列数据点。通常，这类数据由时间戳和一个或多个与时间相关的度量值组成，反映某个对象或系统随时间变化的状态或行为。

1.1 特征

  • 时间依赖性: 每个数据点都与一个特定的时间戳相关联，这是时序数据的核心特征。
  • 连续性和顺序性: 数据点是按时间顺序记录的，时间间隔可能固定（如每秒、每分钟）或不固定。
  • 趋势与周期性: 时序数据通常展现长期趋势、周期性（如每日、每周）或季节性模式。

1.2 常见应用场景

  • 物联网（IoT）数据: 传感器数据，如温度、湿度、流量等。
  • 金融数据: 股票价格、交易量、市场指数等。
  • 系统监控: CPU 使用率、内存消耗、网络流量等系统性能指标。
  • 气象数据: 温度、降水量、风速等随时间变化的气象信息。
  • 工业监控: 生产设备的运行状态、功耗、振动等数据。

例如，记录某个服务器的 CPU 使用率的时序数据可能如下：

时间戳 CPU 使用率 (%)
2024-08-15 10:00:00 45
2024-08-15 10:01:00 47
2024-08-15 10:02:00 50
2024-08-15 10:03:00 46

在这个表中，数据按时间顺序记录，每个数据点都包含一个时间戳和对应的 CPU 使用率。

1.3 特点与区别

  • 高频采集: 数据点可能每秒、每分钟生成一次，通常会产生大量数据。
  • 以时间为主的查询: 查询通常基于时间范围，例如过去一小时的数据、过去一周的数据。
  • 数据老化: 随着时间推移，旧数据通常会被压缩、归档或删除。

由于时序数据的特性，专门的时序数据库（如 TimescaleDB、InfluxDB、OpenTSDB 等）被设计出来以更高效地存储、查询和分析这类数据。它们通常支持高效的写入、压缩、聚合和时间范围查询。

2 Hypertable

Hypertable 是 TimescaleDB 中用于存储和管理大规模时序数据的核心概念。Hypertable 是一个逻辑表，它在使用时看起来像一个标准的 PostgreSQL 表，但实际上在物理层面被划分为多个“分片”（chunks）。这种分片使得 Hypertable 能够高效地存储、查询和管理时序数据。

Hypertable 将数据按时间和可选的空间维度（如设备 ID、用户 ID）进行分片，每个分片存储特定时间范围或空间范围的数据。这种架构可以更高效地进行查询和数据管理。

2.1 创建Hypertable

创建 Hypertable 是将一个普通的 PostgreSQL 表转换为一个 TimescaleDB 的 Hypertable。Hypertable 必须包含一个时间列用于数据分片。

1
2
3
4
5
6
7
8
9
10
-- 创建一个普通的 PostgreSQL 表
CREATE TABLE metrics (
    time        TIMESTAMPTZ       NOT NULL,
    device_id   TEXT              NOT NULL,
    cpu_usage   DOUBLE PRECISION  NOT NULL,
    memory_usage DOUBLE PRECISION NOT NULL
);

-- 将这个表转换为 Hypertable
SELECT create_hypertable('metrics', 'time');

在这个例子中，metrics 表被转换为 Hypertable，其中 time 列是分片的时间维度。

2.2 添加空间分片维度

Chunks 是 Hypertable 在物理层面的数据分片。每个 chunk 是按照时间和可选的空间维度（如设备 ID）来划分的。这种分片可以提高查询和数据管理的效率。

例如，如果有多个设备，每个设备的数据可以单独分片。

1
SELECT create_hypertable('metrics', 'time', 'device_id');

这将根据 time 和 device_id 共同决定如何分片。

2.3 设置时间分片间隔

Chunk Interval 是 Hypertable 的一个重要配置参数，用于定义每个 chunk（数据分片）涵盖的时间范围。调整 chunk interval 可以对数据的存储和查询性能产生显著影响。合适的 chunk interval 设置能够优化数据管理和查询效率。

  1. 数据管理: 适当的 chunk interval 可以使得数据的分片更均匀，有利于数据的管理和维护。比如，可以通过调整 chunk interval 来优化数据的压缩、保留和删除策略。
  2. 查询性能: 合理设置 chunk interval 可以减少每次查询时需要扫描的数据量，从而提高查询性能。较小的 chunk interval 可能会导致更多的分片，从而增加查询的复杂性，而较大的 chunk interval 可能会导致单个分片过大，从而影响查询效率。
  3. 数据归档: 通过调整 chunk interval，可以更方便地进行数据归档和清理操作。较大的 chunk interval 可能需要较长时间来删除旧数据，而较小的 chunk interval 可以更精细地控制数据的保留。
  • 查看当前chunk interval
1
2
3
4
SELECT h.table_name, c.interval_length
  FROM _timescaledb_catalog.dimension c
  JOIN _timescaledb_catalog.hypertable h
    ON h.id = c.hypertable_id;
  • 设置chunk interval
1
SELECT set_chunk_time_interval('metrics', INTERVAL '1 day');

这会将每个分片的时间范围设置为 1 天。

2.4 删除Hypertable

可以像删除普通表一样删除 Hypertable。

1
DROP TABLE metrics;

3 增删改

向 Hypertable 插入、删除和修改数据的方式与向普通 PostgreSQL 表相同。

以增加数据为例：

  • 插入一行：
1
insert into test (time, device_id, usage) values (NOW(), '1', '80%');
  • 插入多行
1
2
3
4
insert into test values 
	(NOW(), '2', '80%'),
	(NOW(), '3', '90%'),
	(NOW(), '4', '75%');

4 查询

Timescale支持PostgreSQL的所有查询语法，因此可以像使用pg一样使用Timescale。

4.1 简单select查询

  • 从表中查询最近100条数据
1
SELECT * FROM conditions ORDER BY time DESC LIMIT 100;
  • 查询过去12小时写入到表中的数据
1
2
SELECT COUNT(*) FROM conditions
  WHERE time > NOW() - INTERVAL '12 hours';
  • 统计conditions表中过去一天的数据中有空调的地方的数量
1
2
3
4
5
SELECT COUNT(DISTINCT location) FROM conditions
  JOIN locations
    ON conditions.location = locations.location
  WHERE locations.air_conditioning = True
    AND time > NOW() - INTERVAL '1 day';

4.2 time_bucket

time_bucket 是 TimescaleDB 中一个强大的函数，用于将时间数据聚合到固定的时间间隔（例如每分钟、每小时、每天）内。这类似于 SQL 中的 GROUP BY，但特别适用于时间序列数据。

语法：

1
time_bucket(bucket_width, time_column [, offset]) → timestamp
  • bucket_width: 时间间隔，如 '1 minute'， '1 hour'， '1 day'。
  • time_column: 用于分组的时间列。
  • offset（可选）: 时间偏移，用于调整时间分组的起点。

假设我们有一个名为 device_metrics 的 Hypertable，存储设备的 CPU 和内存使用率：

1
2
3
4
5
6
7
8
9
CREATE TABLE device_metrics (
    time        TIMESTAMPTZ       NOT NULL,
    device_id   TEXT              NOT NULL,
    cpu_usage   DOUBLE PRECISION  NOT NULL,
    memory_usage DOUBLE PRECISION NOT NULL
);

-- 将表转换为 Hypertable
SELECT create_hypertable('device_metrics', 'time');

表中的示例数据如下：

time device_id cpu_usage memory_usage
2024-08-15 10:01:00 device_1 45.3 1024
2024-08-15 10:02:15 device_1 55.1 1036
2024-08-15 10:03:30 device_1 50.7 1040
2024-08-15 10:01:30 device_2 60.4 2024
2024-08-15 10:02:45 device_2 62.2 2048
  • 按 1 分钟时间间隔聚合 CPU 使用率的平均值：
1
2
3
4
5
6
7
SELECT
    time_bucket('1 minute', time) AS bucket,
    device_id,
    AVG(cpu_usage) AS avg_cpu_usage
FROM device_metrics
GROUP BY bucket, device_id
ORDER BY bucket, device_id;

查询结果：

bucket device_id avg_cpu_usage
2024-08-15 10:01:00 device_1 45.3
2024-08-15 10:02:00 device_1 55.1
2024-08-15 10:03:00 device_1 50.7
2024-08-15 10:01:00 device_2 60.4
2024-08-15 10:02:00 device_2 62.2

在这个例子中，每分钟的数据被聚合为一个时间段，并计算每个设备在该时间段内的 CPU 使用率的平均值。

  • 按 5 分钟间隔聚合内存使用量的总和：
1
2
3
4
5
6
SELECT
    time_bucket('5 minutes', time) AS bucket,
    SUM(memory_usage) AS total_memory_usage
FROM device_metrics
GROUP BY bucket
ORDER BY bucket;

查询结果（假设数据更多）可能是这样的：

bucket total_memory_usage
2024-08-15 10:00:00 6100
2024-08-15 10:05:00 7520

在这个查询中，每5分钟的数据被汇总，并计算了该时间段内所有设备的内存使用量总和。

  • 使用偏移量进行聚合：

可以使用偏移量来调整时间桶的起始位置，例如从半点开始聚合：

1
2
3
4
5
6
7
SELECT
    time_bucket('1 hour', time, '30 minutes') AS bucket,
    device_id,
    AVG(cpu_usage) AS avg_cpu_usage
FROM device_metrics
GROUP BY bucket, device_id
ORDER BY bucket, device_id;

这个查询将数据按每小时聚合，但起始时间从每小时的半点开始（如 10:30, 11:30）。

4.3 SkipScan

SkipScan 是 TimescaleDB 中一种优化技术，用于提高在多列索引上执行特定类型查询的效率，尤其是在涉及唯一值或分组的查询中。

SkipScan 通过跳过不相关的索引条目，直接定位下一个唯一值，从而避免了全索引扫描。这在执行以下类型的查询时尤其有效：

  • DISTINCT 查询：当使用 SELECT DISTINCT 查询时，SkipScan 可以快速获取唯一值，而不需要遍历索引中的每一行。
  • GROUP BY 查询：当使用 GROUP BY 对结果进行分组时，SkipScan 可以有效地识别分组的起始位置，跳过不必要的扫描。

例如，考虑一个有多列索引的表：

1
CREATE INDEX ON metrics (device_id, time);

对于以下查询：

1
SELECT DISTINCT device_id FROM metrics;

传统的索引扫描会逐条检查索引中的所有条目，即使它们的 device_id 是重复的。而 SkipScan 通过识别相同 device_id 值的起始和结束位置，可以直接跳到下一个唯一的 device_id，从而显著减少扫描的工作量。

同样，以下查询中，SkipScan 也会发挥作用：

1
2
3
SELECT device_id, MAX(cpu_usage)
FROM device_metrics
GROUP BY device_id;

5 数据保留

在时序应用程序中，数据往往随着时间的增长而变得不那么有用。如果不需要过期的历史数据，可以在它达到一定年龄后将其删除。

  • 设置数据保留策略，删除超过 30 天的数据：
1
SELECT add_retention_policy('metrics', INTERVAL '30 days');

这会自动删除 metrics 表中超过 30 天的数据。

  • 删除数据保留策略
1
SELECT remove_retention_policy('metrics');

Timescale可以设置自动数据保留策略以丢弃旧数据，也可以通过手动删除数据块来微调数据，可以使用 drop_chunks 函数：

1
SELECT drop_chunks(INTERVAL '30 days', 'metrics');

这个命令会删除所有超过 30 天的数据 chunk。

6 数据压缩

6.1 简介

数据压缩 是 TimescaleDB 的一个关键功能，用于减少存储时序数据所需的磁盘空间，同时保持良好的查询性能。它通过应用专门设计的压缩算法，极大地降低了存储需求，尤其在存储长期历史数据时非常有效。

TimescaleDB 的压缩是基于 chunk（分块）进行的。每个 Hypertable 的数据会按照时间和空间分块，当某个 chunk 的数据不再频繁更新时，可以将其压缩以节省空间。

主要特性：

  • 列级压缩: TimescaleDB 支持对单独的列应用不同的压缩算法，比如差值编码、字典编码等。
  • 透明查询: 压缩后的数据仍然可以被查询，TimescaleDB 会自动解压需要的数据块，无需用户额外操作。
  • 自动化压缩: 你可以设置策略，让 TimescaleDB 在数据达到一定条件时自动进行压缩。

6.2 设置

在 TimescaleDB 中，数据压缩需要以下几个步骤：

  • 启用表的压缩功能，需要为 Hypertable 启用压缩：
1
ALTER TABLE metrics SET (timescaledb.compress, true);

这个命令为指定的表开启压缩功能，使得后续可以对表的 chunk 进行压缩。

  • 定义哪些列将被压缩

可以指定不同的列使用不同的压缩算法：

1
2
3
4
5
ALTER TABLE metrics
SET (timescaledb.compress_segmentby = 'device_id');

ALTER TABLE metrics
ALTER COLUMN time SET (timescaledb.compress);

compress_segmentby: 用于分段的列，通常是唯一标识符或分组键。

compress: 指定列是否参与压缩。

  • 可以手动压缩

可以手动压缩那些不再更新的 chunk：

1
2
3
SELECT compress_chunk(m.chunk_name)
FROM show_chunks('metrics') m
WHERE m.range_end < NOW() - INTERVAL '7 days';

这个查询会压缩所有 7 天前的数据块。

  • 自动化压缩策略

TimescaleDB 支持自动化的压缩策略，可以在数据达到一定条件后自动压缩：

1
SELECT add_compression_policy('metrics', INTERVAL '7 days');

7 持续聚合

7.1 简介

持续聚合（Continuous Aggregates）是 TimescaleDB 的一项功能，用于自动化和高效地对大规模时序数据进行聚合计算。它允许定义一个物化视图，定期更新聚合结果，从而避免每次查询都要重新计算大量数据。持续聚合特别适合处理长时间跨度的大规模数据，例如对日常、每周或每月数据进行汇总分析。

  • 持续聚合的特点
  1. 增量更新: 只更新有新数据的部分，减少计算开销。
  2. 自动化刷新: 支持自动更新聚合结果，确保结果的实时性。
  3. 优化查询性能: 预计算的聚合数据存储在物化视图中，查询速度显著提高。

7.2 设置

假设我们有一个存储 IoT 传感器数据的表：

1
2
3
4
5
6
7
8
CREATE TABLE sensor_data (
    time        TIMESTAMPTZ       NOT NULL,
    device_id   TEXT              NOT NULL,
    temperature DOUBLE PRECISION  NOT NULL,
    humidity    DOUBLE PRECISION  NOT NULL
);

SELECT create_hypertable('sensor_data', 'time');

我们希望定期计算每小时的平均温度和湿度。可以使用持续聚合来实现：

1
2
3
4
5
6
7
8
CREATE MATERIALIZED VIEW daily_avg_temp_humidity -- 创建物化视图
WITH (timescaledb.continuous) AS -- 指定这是一个持续聚合视图
SELECT time_bucket('1 hour', time) AS bucket,
       device_id,
       AVG(temperature) AS avg_temperature,
       AVG(humidity) AS avg_humidity
FROM sensor_data
GROUP BY bucket, device_id;

注：MATERIALIZED 是创建物化视图（Materialized View）时使用的关键字。物化视图是一种特殊的视图，它将查询的结果存储在磁盘上，而不是每次查询时都重新计算结果。因此，物化视图可以大幅提高查询性能，特别是在处理复杂的聚合查询时。

一旦创建了持续聚合视图，查询就变得非常高效：

1
2
3
SELECT bucket, device_id, avg_temperature, avg_humidity
FROM daily_avg_temp_humidity
WHERE bucket > NOW() - INTERVAL '7 days';

由于聚合结果已经预计算并存储，查询性能大大提升。

7.3 刷新策略

TimescaleDB 支持自动化和手动刷新策略：

  • 自动刷新
1
2
ALTER MATERIALIZED VIEW daily_avg_temp_humidity
SET (timescaledb.refresh_lag = '1 hour');

这里设置了一个 refresh_lag 为 1 小时，意味着数据会每小时自动刷新。

  • 手动刷新
1
CALL refresh_continuous_aggregate('daily_avg_temp_humidity', NULL, NOW());

手动刷新允许控制刷新时机和范围。