牧言 AI+ 低代码开发平台中文文档

# 时序数据库

本系统可以借助 PostgreSQL 的 TimescaleDB 扩展 (opens new window)（Timescale 公司现已更名为 TigerData）存储工业控制、IoT 设备的运行状态数据，或其他场景的海量时序数据。

平台本身不依赖 TimescaleDB，本页介绍的是数据库层面的可选配置：换用带 TimescaleDB 扩展的数据库镜像后，在插件或动态逻辑中通过 SQL 使用时序表。

# 目标读者

本文档的目标读者为：需要使用本系统存储大量时序数据的开发人员

# 关于 TimescaleDB

TimescaleDB 扩展了 PostgreSQL 的时间序列和分析功能：把普通表转换为按时间自动分区的“超表”（hypertable），并提供 time_bucket 等时序函数和持续聚合。

# 运行 TimescaleDB

默认镜像不带 TimescaleDB

按 Docker 部署 一文部署时，模板 docker-compose.yml 中的数据库镜像是 pgvector/pgvector:0.8.0-pg17，不包含 TimescaleDB 扩展。需要使用时序功能时，要把数据库镜像换成 timescale/timescaledb-ha:pg17。该镜像同时包含 vector 扩展，可以替代原来的 pgvector 镜像。

以下是 docker-compose.yml 中 database 服务的配置示例（数据库名沿用模板的 application）：

  database:
    image: timescale/timescaledb-ha:pg17
    restart: always
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=password
      - POSTGRES_DB=application
      - PGDATA=/home/postgres/pgdata/data
    volumes:
      # 注意：timescaledb-ha 镜像的数据目录与 pgvector 镜像不同，这里使用一个新的空目录
      - ./runtime/timescaledb:/home/postgres/pgdata
      - ./db-seed-data/initdb:/docker-entrypoint-initdb.d/
    healthcheck:
      test: ['CMD-SHELL', 'pg_isready -U postgres']
      interval: 10s
      timeout: 5s
      retries: 5
    networks:
      - scm
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

timescale/timescaledb-ha 镜像以 uid 1000 的 postgres 用户运行（pgvector/pgvector 镜像是 uid 999），启动前不会修改数据目录的属主。宿主机上的数据目录要先建好并交给 uid 1000，否则容器会报 mkdir: cannot create directory '/home/postgres/pgdata/data': Permission denied 后退出：

mkdir -p runtime/timescaledb
sudo chown 1000:1000 runtime/timescaledb
1
2

切换镜像前请备份

timescale/timescaledb-ha 镜像的数据目录（PGDATA）、挂载位置和运行用户都与 pgvector/pgvector 镜像不同，不能直接沿用旧的 ./runtime/database/data 目录。对已有数据的环境，按以下步骤切换：

  1. 切换前用 pg_dump -Fc 备份 application 库，并备份 ./runtime/database/data 目录；
  2. 按上面的配置改用新镜像和新的空数据目录后启动。新库初始化时会执行 ./db-seed-data/initdb 中的脚本，灌入模板自带的种子数据；
  3. 用 pg_restore --clean --if-exists -d application <备份文件> 恢复。--clean --if-exists 会先删除种子数据建出的同名对象，避免恢复时报“已存在”。

# 创建 TimescaleDB 扩展

timescaledb-ha 镜像自带的初始化脚本（/docker-entrypoint-initdb.d/000_install_timescaledb.sh 等）会创建 timescaledb 扩展。但上面的配置把 ./db-seed-data/initdb 挂载到了 /docker-entrypoint-initdb.d/，镜像自带的脚本被覆盖，不会执行，所以新库中没有 timescaledb 扩展，需要手动创建：

-- 连接到数据库
\c application

-- 创建 timescaledb 扩展
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- 显示已安装的扩展以确认
\dx
1
2
3
4
5
6
7
8

# 使用 TimescaleDB 创建表

TimescaleDB 提供函数 create_hypertable，用于把普通表转换为超表：

-- 创建表
CREATE TABLE stocks_real_time (
  time TIMESTAMPTZ NOT NULL,
  symbol TEXT NOT NULL,
  price DOUBLE PRECISION NULL,
  day_volume INT NULL
);

-- 创建 hypertable, 将 stocks_real_time 转换为时序表, time 列为时序列
SELECT create_hypertable('stocks_real_time','time');

-- 如果表中已经有数据，需要加上 migrate_data 参数，把已有数据迁移到时序分区中
-- SELECT create_hypertable('stocks_real_time','time', migrate_data => true);

-- 基于 symbol 和 time 列创建索引
CREATE INDEX ix_symbol_time ON stocks_real_time (symbol, time DESC);

-- 创建另一个普通表
CREATE TABLE company (
  symbol TEXT NOT NULL,
  name TEXT NOT NULL
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

# 查询数据

TimescaleDB 使用标准的 PostgreSQL 语法进行查询，此外还提供了一些查询时序数据的函数，例如 time_bucket。

# 插入测试数据

  1. 下载 样本数据 real_time_stock_data.zip (opens new window)，使用命令 unzip real_time_stock_data.zip 解压缩。

  2. 将示例数据导入数据库：把文件复制到数据库容器中，并进入 psql 控制台。

# 将示例数据复制到正在运行的数据库容器中
docker cp tutorial_sample_tick.csv <数据库容器 ID>:/tmp/tutorial_sample_tick.csv
docker cp tutorial_sample_company.csv <数据库容器 ID>:/tmp/tutorial_sample_company.csv
# 进入 psql 控制台
docker exec -it <数据库容器 ID> psql -U postgres -d application
1
2
3
4
5
-- 将 CSV 文件导入 stocks_real_time 和 company 表
\COPY stocks_real_time from '/tmp/tutorial_sample_tick.csv' CSV HEADER;
\COPY company from '/tmp/tutorial_sample_company.csv' CSV HEADER;
1
2
3

# 使用原始 SQL 查询数据

样本数据是一段固定时间范围内的历史数据（2026-09 下载的版本是 2025-12-29 至 2026-01-29），官方可能会更新。导入后先查一下实际范围：

SELECT min(time), max(time) FROM stocks_real_time;
1

下面按“最近 N 天”过滤的查询要换成样本数据的时间范围，例如把 now() 换成 (SELECT max(time) FROM stocks_real_time)，否则查不到数据。

-- 选择过去四天的所有股票数据
SELECT * FROM stocks_real_time srt
 WHERE time > now() - INTERVAL '4 days';

-- 按顺序选择 Amazon 最近的 10 笔交易
SELECT * FROM stocks_real_time srt
 WHERE symbol='AMZN'
 ORDER BY time DESC, day_volume desc
 LIMIT 10;

-- 计算过去四天内 Apple 的平均交易价格
SELECT
  avg(price)
  FROM stocks_real_time srt
       JOIN company c ON c.symbol = srt.symbol
 WHERE c.name = 'Apple' AND time > now() - INTERVAL '4 days';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 时序函数

  • first()：在聚合组内基于时间找到最早的值
  • last()：在聚合组内基于时间找到最新的值
  • time_bucket()：按任意时间间隔分桶数据并计算这些间隔内的聚合值
-- 计算过去一周内每个交易代码的每日平均价格
SELECT
  time_bucket('1 day', time) AS bucket,
  symbol,
  avg(price)
  FROM stocks_real_time srt
 WHERE time > now() - INTERVAL '1 week'
 GROUP BY bucket, symbol
 ORDER BY bucket, symbol;
1
2
3
4
5
6
7
8
9

# 持续聚合

TimescaleDB 的持续聚合（continuous aggregate）把聚合结果物化保存下来，并按刷新策略在后台增量更新，查询聚合数据时响应更快。它不需要在写入时同步计算，但后台刷新本身也要消耗数据库资源。

创建物化视图时，使用 WITH (timescaledb.continuous) 选项即表示创建持续聚合。

# 创建持续聚合

-- 查询每天的股票交易数据中的最高价、最低价、开盘价和收盘价
SELECT
  time_bucket('1 day', "time") AS day,
  symbol,
  max(price) AS high,
  first(price, time) AS open,
  last(price, time) AS close,
  min(price) AS low
FROM stocks_real_time srt
GROUP BY day, symbol
ORDER BY day DESC, symbol;

-- 使用上述 SQL 创建持续聚合，将结果存储在 stock_candlestick_daily 视图中
CREATE MATERIALIZED VIEW stock_candlestick_daily
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 day', "time") AS day,
  symbol,
  max(price) AS high,
  first(price, time) AS open,
  last(price, time) AS close,
  min(price) AS low
FROM stocks_real_time srt
GROUP BY day, symbol;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

# 设置自动刷新

持续聚合创建时会按当时的数据计算一次，之后不会自动更新，需要添加刷新策略，后台才会定期刷新：

-- 每小时刷新一次，刷新范围是 1 个月前到 1 小时前的数据
SELECT add_continuous_aggregate_policy('stock_candlestick_daily',
  start_offset      => INTERVAL '1 month',
  end_offset        => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour');

-- 也可以随时手动刷新
CALL refresh_continuous_aggregate('stock_candlestick_daily', NULL, NULL);
1
2
3
4
5
6
7
8

另外，TimescaleDB 2.13 起 (opens new window)持续聚合默认只返回已物化的结果（materialized_only = true），还没刷新进来的新数据查不到。需要把最新数据也实时合并进查询结果时，执行：

ALTER MATERIALIZED VIEW stock_candlestick_daily SET (timescaledb.materialized_only = false);
1

提示

以上行为在 timescale/timescaledb-ha:pg17（TimescaleDB 2.28.3）中实测：创建后默认 materialized_only 为 true、没有刷新任务；新写入的数据在执行 refresh_continuous_aggregate 或刷新策略运行之前不会出现在视图中。

# 在代码中查询

在插件或动态逻辑中执行原始 SQL，可以使用平台 API 中的 QueryHelper.withSql（见 平台 API），它会传入一个 groovy.sql.Sql 对象：

import groovy.sql.GroovyRowResult
import groovy.sql.Sql
import tech.muyan.utils.QueryHelper

List<GroovyRowResult> rows = QueryHelper.withSql { Sql sql ->
  sql.rows("SELECT * FROM stock_candlestick_daily WHERE symbol = ? ORDER BY day DESC LIMIT 30", ['AAPL'])
}
1
2
3
4
5
6
7

Sql 类的详细用法请参考 groovy.sql.Sql 的 API 文档 (opens new window)。

# 进一步阅读

以上只是 TimescaleDB 的简单介绍，更多功能请参考官方文档：

Edit (opens new window)
Last Updated: 2026/9/24 14:27:35

← 🏭 MES 系统说明 →