香港服务器部署TimescaleDB时序数据库：IoT设备数据存储 + 实时监控数据分析实战

传统 MySQL/PostgreSQL 存储时序数据（传感器读数、监控指标、股票行情）会遇到明显的性能瓶颈——随着数据量增长，查询越来越慢，且无法高效处理时间窗口聚合。TimescaleDB 是 PostgreSQL 的时序数据扩展，在完全兼容标准 SQL 的前提下，通过自动时间分区（Hypertable）将时序查询性能提升 10～1000 倍，是 IoT 平台和监控系统的理想数据库。

一、TimescaleDB vs InfluxDB vs 普通 PostgreSQL

二、安装 TimescaleDB

<code"># 方法一：Docker（推荐）
docker run -d \
  --name timescaledb \
  --restart unless-stopped \
  -p 127.0.0.1:5432:5432 \
  -e POSTGRES_PASSWORD=ts_db_pass \
  -e POSTGRES_DB=iotdb \
  -v /opt/timescaledb/data:/var/lib/postgresql/data \
  timescale/timescaledb:latest-pg16

# 连接并创建 TimescaleDB 扩展
docker exec -it timescaledb psql -U postgres iotdb

<code">-- 在数据库中启用 TimescaleDB
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- 验证安装
SELECT extversion FROM pg_extension WHERE extname = 'timescaledb';
\dx timescaledb

三、创建 Hypertable（IoT 传感器数据）

<code">-- 创建普通 PostgreSQL 表（先创建普通表，再转换为 Hypertable）
CREATE TABLE sensor_readings (
    time         TIMESTAMPTZ NOT NULL,
    device_id    TEXT NOT NULL,
    sensor_type  TEXT NOT NULL,          -- temperature / humidity / pressure / power
    value        DOUBLE PRECISION NOT NULL,
    unit         TEXT,
    location     TEXT,
    quality      SMALLINT DEFAULT 100,   -- 数据质量分（0-100）
    metadata     JSONB
);

-- 转换为 Hypertable（自动按时间分区）
SELECT create_hypertable(
    'sensor_readings',
    'time',
    chunk_time_interval => INTERVAL '1 day',   -- 每天一个分区（chunk）
    if_not_exists => TRUE
);

-- 添加设备索引（加速按设备查询）
CREATE INDEX ON sensor_readings (device_id, time DESC);
CREATE INDEX ON sensor_readings (sensor_type, time DESC);

-- 启用列式压缩（数据超过7天后自动压缩，节省约80%空间）
ALTER TABLE sensor_readings SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device_id, sensor_type'
);

-- 设置自动压缩策略（7天前的数据自动压缩）
SELECT add_compression_policy('sensor_readings', INTERVAL '7 days');

-- 设置数据保留策略（90天后自动删除）
SELECT add_retention_policy('sensor_readings', INTERVAL '90 days');

四、写入数据（Python）

<code">import psycopg2
from psycopg2.extras import execute_values
from datetime import datetime, timezone
import random

conn = psycopg2.connect(
    host="localhost", port=5432,
    database="iotdb", user="postgres", password="ts_db_pass"
)
cursor = conn.cursor()

def insert_sensor_batch(readings: list[dict]):
    """批量写入传感器数据（execute_values 比逐条插入快约 10 倍）"""
    data = [
        (
            r["time"],
            r["device_id"],
            r["sensor_type"],
            r["value"],
            r.get("unit"),
            r.get("location"),
            r.get("quality", 100),
        )
        for r in readings
    ]
    execute_values(
        cursor,
        """
        INSERT INTO sensor_readings (time, device_id, sensor_type, value, unit, location, quality)
        VALUES %s
        ON CONFLICT DO NOTHING
        """,
        data
    )
    conn.commit()

# 模拟批量写入（100个设备，每设备每分钟1条）
batch = []
for device_id in [f"device-{i:04d}" for i in range(100)]:
    batch.append({
        "time": datetime.now(timezone.utc),
        "device_id": device_id,
        "sensor_type": "temperature",
        "value": round(random.uniform(20, 30), 2),
        "unit": "°C",
        "location": "HK-DataCenter-A",
        "quality": 100,
    })

insert_sensor_batch(batch)
print(f"写入 {len(batch)} 条数据")

五、时序查询（TimescaleDB 特有函数）

<code">-- ── 基础查询：最近1小时的数据 ──
SELECT time, device_id, value
FROM sensor_readings
WHERE sensor_type = 'temperature'
  AND time > NOW() - INTERVAL '1 hour'
ORDER BY time DESC
LIMIT 100;

-- ── time_bucket：按时间窗口聚合（TimescaleDB 核心功能）──
-- 每5分钟的平均温度
SELECT
    time_bucket('5 minutes', time) AS bucket,
    device_id,
    AVG(value)   AS avg_temp,
    MIN(value)   AS min_temp,
    MAX(value)   AS max_temp,
    COUNT(*)     AS readings
FROM sensor_readings
WHERE sensor_type = 'temperature'
  AND time > NOW() - INTERVAL '24 hours'
GROUP BY bucket, device_id
ORDER BY bucket DESC, device_id;

-- ── first/last：获取时间窗口内的第一/最后一个值 ──
SELECT
    time_bucket('1 hour', time) AS hour,
    device_id,
    first(value, time) AS value_at_start,
    last(value, time)  AS value_at_end,
    last(value, time) - first(value, time) AS change
FROM sensor_readings
WHERE sensor_type = 'power'
  AND time > NOW() - INTERVAL '24 hours'
GROUP BY hour, device_id
ORDER BY hour DESC;

-- ── 连续聚合（Continuous Aggregates）：预计算加速 ──
-- 创建每小时聚合视图（实时自动更新，查询极快）
CREATE MATERIALIZED VIEW hourly_temperature
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', time) AS hour,
    device_id,
    AVG(value) AS avg_temp,
    MIN(value) AS min_temp,
    MAX(value) AS max_temp,
    COUNT(*)   AS count
FROM sensor_readings
WHERE sensor_type = 'temperature'
GROUP BY hour, device_id;

-- 设置自动刷新策略（每5分钟更新最近2小时的数据）
SELECT add_continuous_aggregate_policy('hourly_temperature',
    start_offset => INTERVAL '2 hours',
    end_offset   => INTERVAL '5 minutes',
    schedule_interval => INTERVAL '5 minutes'
);

-- 查询连续聚合视图（毫秒级响应）
SELECT * FROM hourly_temperature
WHERE device_id = 'device-0001'
  AND hour > NOW() - INTERVAL '7 days'
ORDER BY hour DESC;

六、Grafana 接入 TimescaleDB

<code">
Grafana → Connections → PostgreSQL

配置：
- Host: localhost:5432
- Database: iotdb
- User: postgres
- Password: ts_db_pass
- SSL Mode: disable

推荐面板查询（使用 Grafana 变量）：

-- 实时温度折线图
SELECT
    time_bucket('$__interval', time) AS "time",
    device_id AS metric,
    AVG(value)
FROM sensor_readings
WHERE
    sensor_type = 'temperature'
    AND device_id IN ($device_id)
    AND $__timeFilter(time)
GROUP BY 1, 2
ORDER BY 1;

-- 告警查询：温度超过阈值
SELECT COUNT(*) as alert_count
FROM sensor_readings
WHERE sensor_type = 'temperature'
  AND value > 35
  AND time > NOW() - INTERVAL '5 minutes';

七、数据压缩效果验证

<code">-- 查看压缩状态和压缩率
SELECT
    pg_size_pretty(before_compression_total_bytes) AS before,
    pg_size_pretty(after_compression_total_bytes) AS after,
    round(100 * (1 - after_compression_total_bytes::numeric
                   / before_compression_total_bytes), 1) AS "compression_%"
FROM hypertable_compression_stats('sensor_readings');

-- 典型输出：
-- before: 1200 MB  after: 120 MB  compression_%: 90.0
-- （时序数据列式压缩效果极佳，通常可达 80～95%）

八、总结

TimescaleDB 让 PostgreSQL 拥有了原生时序数据库的能力：自动分区避免大表扫描、连续聚合让复杂统计查询从秒级变为毫秒级、列式压缩节省 80%+ 存储空间。对于已经在用 PostgreSQL 的项目，添加 TimescaleDB 扩展是零迁移成本的性能升级。香港服务器 4核8G 配置可以舒适处理每秒数千条传感器数据写入和实时聚合查询。