TimescaleDB的示例SQL

本文展示TimescaleDB的一个完整的示例，包括：

1. 自动建表 SQL

2. 初始化数据导入

3. 连续聚合（Continuous Aggregates）创建

4. 如何让容器启动时自动执行这些SQL

🧩 第一步：创建 SQL 脚本

在你的项目目录中，新建一个目录：

mkdir init

创建一个文件：init/init_timescale.sql

-- 启用 TimescaleDB 扩展
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- 创建时序表
CREATE TABLE sensor_data (
  time        TIMESTAMPTZ       NOT NULL,
  device_id   TEXT              NOT NULL,
  temperature DOUBLE PRECISION  NULL,
  humidity    DOUBLE PRECISION  NULL
);

-- 将其转为 hypertable
-- SELECT create_hypertable('sensor_data', 'time', 'device_id', if_not_exists => TRUE);
SELECT create_hypertable('sensor_data', 'time', 'device_id', if_not_exists => TRUE);

-- 插入一些测试数据
INSERT INTO sensor_data (time, device_id, temperature, humidity)
SELECT
  now() - (i || ' minutes')::interval,
  'device_' || (i % 5),
  round(CAST(random() * 10 + 20 AS numeric), 2),
  round(CAST(random() * 30 + 50 AS numeric), 2)
FROM generate_series(1, 500) AS i;

-- 创建连续聚合（每小时每设备平均温湿度）
CREATE MATERIALIZED VIEW IF NOT EXISTS device_hourly_avg
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', time) AS bucket,
  device_id,
  avg(temperature) AS avg_temp,
  avg(humidity) AS avg_humidity
FROM sensor_data
GROUP BY bucket, device_id;

-- 设置聚合刷新策略（可选）
SELECT add_continuous_aggregate_policy('device_hourly_avg',
  start_offset => INTERVAL '1 day',
  end_offset => INTERVAL '1 hour',
  schedule_interval => INTERVAL '15 minutes');

⚙️ 第二步：修改 docker-compose.yml

我们让 TimescaleDB 在初始化时自动执行上述 SQL 脚本。

version: '3.8'

services:
  timescaledb:
    image: timescale/timescaledb:latest-pg14
    container_name: timescaledb
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=your_password
      - POSTGRES_DB=timescaledb
    ports:
      - "5432:5432"
    volumes:
      - timescale_data:/var/lib/postgresql/data
      - ./init:/docker-entrypoint-initdb.d  # ✅ 添加初始化脚本目录挂载
    restart: unless-stopped

volumes:
  timescale_data:

📌 PostgreSQL（含 TimescaleDB）会在首次启动时执行挂载在 /docker-entrypoint-initdb.d 下的所有 .sql 脚本。

✅ 启动容器

docker-compose down -v  # 清理旧数据（首次测试建议执行）
docker-compose up -d

容器启动后，你可以连接数据库执行：

SELECT * FROM device_hourly_avg LIMIT 10;

就能看到每小时每设备的平均温度/湿度了。

🧠 可选增强功能

• 加入 add_compression_policy() 对旧数据压缩

• 加入 Grafana 做实时展示

• 按 device_id 创建索引提升查询效率

后续可以针对物联网设备的报警触发机制（例如温度 > 30度报警）或 Grafana 连接配置

👍补充SQL

下面是一个插入3000w测试数据的SQL

-- 基础表结构设计
-- 注意：data_time 字段作为分区键必须设置为 NOT NULL
CREATE TABLE t_power (
  instrument_code VARCHAR(50) NOT NULL,
  data_time TIMESTAMPTZ NOT NULL,
  P NUMERIC(10, 2),
  Q NUMERIC(10, 2)
);

-- 创建超表(Hypertable)
-- 该命令会将普通表自动分区为时间块(chunks)，显著提升数据插入和查询效率。
-- 提示：若表存在唯一约束或主键，需确保分区字段被包含其中
SELECT create_hypertable('t_power', 'data_time');


-- 索引优化策略
-- 1. 时间字段索引（优化范围查询）
CREATE INDEX idx_t_power_time ON t_power (data_time DESC);

-- 2. 复合索引（设备+时间查询场景）
CREATE INDEX idx_power_device_time ON t_power (instrument_code, data_time DESC);

-- 批量数据生成方案
CREATE OR REPLACE PROCEDURE write_t_power_data(target_rows BIGINT)
LANGUAGE plpgsql
AS $$
DECLARE
  i BIGINT := 0;
  batch_size INT := 10000;
  now_time TIMESTAMP := now();
BEGIN
  WHILE i < target_rows LOOP
    INSERT INTO t_power (instrument_code, data_time, P, Q)
    SELECT
      'DEV-' || floor(random()*1000)::int,
      now_time + (i + g)::int * interval '1 second',
      round((random()*1000)::numeric, 2),
      round((random()*1000)::numeric, 2)
    FROM generate_series(1, batch_size) AS g;

    i := i + batch_size;
    RAISE NOTICE 'Inserted % rows', i;
  END LOOP;
END;
$$;

-- 执行示例（生产3000w条数据）：
CALL write_t_power_data(30000000);
-- 性能优化建议:
-- 使用 UNLOGGED 表 + COPY 方式关闭 WAL
-- 采用并行写入工具
-- 启用 TimescaleDB 压缩功能

-- 查询优化实例
-- 设备历史数据查询:
SELECT * FROM t_power
WHERE instrument_code = 'DEV-42'
  AND data_time >= now() - interval '1 hour'
ORDER BY data_time DESC;

-- 时间窗口聚合:
SELECT time_bucket('5 minutes', data_time) AS bucket,
       AVG(P) as avg_p
FROM t_power
GROUP BY bucket
ORDER BY bucket DESC;

-- 总结
-- TimescaleDB 完美融合了 PostgreSQL 生态与专业时序数据处理能力。本文完整演示了从超表创建、索引优化到批量写入和聚合分析的实战流程，为各类高频数据采集场景提供了可靠解决方案。