UXDB-T 时序数据库

1. 概述

1.1 什么是 UXDB-T

优炫时序数据库管理系统（简称UXDB-T），基于UXDB内核，面向海量时序数据的高效存储与管理，实时分析、时序索引和 AI 融合计算，兼具国产化与安全特性，满足智慧能源、工业互联网及金融量化运维典型时序场景，为企业提供端到端的时序数据解决方案。

• 超表（Hypertables）：自动按时间对数据进行分区，无需更改 SQL 即可显著提升插入和查询性能；
• Hypercore 存储引擎：混合行列存储，查询速度最高提升 350 倍，存储压缩最高达 98%；
• 连续聚合（Continuous Aggregates）：预计算并自动刷新分析结果，实现仪表盘和报表的即时响应；
• 分层存储（Tiered Storage）：将冷数据无缝迁移到低成本对象存储，同时保持完全可查询。

2. 快速入门

以下五步完成从安装到查询的完整流程，各步骤细节见后续章节。

① 安装并加载扩展（详见第 3 章）：

-- 配置 uxsinodb.conf: shared_preload_libraries = 'timescaledb'，重启后执行
CREATE EXTENSION IF NOT EXISTS timescaledb;

② 建表并转为超表（详见第 6 章）：

CREATE TABLE conditions (
    time        TIMESTAMPTZ NOT NULL,
    location    TEXT NOT NULL,
    device      TEXT NOT NULL,
    temperature DOUBLE PRECISION NULL,
    humidity    DOUBLE PRECISION NULL
);
SELECT create_hypertable('conditions', by_range('time'));

③ 写入数据（详见第 7 章）：

INSERT INTO conditions (time, location, device, temperature, humidity)
VALUES (NOW(), 'office-1', 'sensor-a', 22.5, 60.0);

④ 按时间桶查询（详见第 8 章）：

SELECT time_bucket('1 hour', time) AS bucket,
       AVG(temperature) AS avg_temp
FROM conditions
GROUP BY bucket
ORDER BY bucket DESC;

⑤ 配置生命周期策略（详见第 12 章）：

-- 7 天后自动转为列存储
CALL add_columnstore_policy('conditions', INTERVAL '7 days');
-- 6 个月后自动删除原始数据
SELECT add_retention_policy('conditions', drop_after => INTERVAL '6 months');

3. 安装与卸载

3.1 安装

第一步：安装软件包

获取 UXDB-T V2.0 安装包，参考《优炫数据库管理系统安装手册》完成安装。

第二步：配置实例参数

编辑数据库实例目录下的配置文件 uxsinodb.conf：

shared_preload_libraries = 'timescaledb'   # 如有多个插件，用逗号分开，如 'timescaledb,orafce'
timescaledb.telemetry_level = 'off'        # 可选：关闭遥测

然后重启 UXDB-T。

第三步：在数据库中启用扩展

uxsql -h hostip -U username -d dbname -p port

-- 通过 UXDB-T 扩展机制加载插件
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- 查看插件版本
SELECT extname, extversion FROM ux_extension WHERE extname = 'timescaledb';

注意：扩展是按数据库生效的。如果切换到其它数据库，需要再次执行 CREATE EXTENSION 命令加载该插件。

3.2 卸载

卸载TimescaleDB 扩展需要先删除依赖对象、在数据库中执行删除命令，再修改配置文件并重启UXDB。

1. 删除依赖与扩展在 终端连接到目标数据库，删除超表（Hypertable）或直接使用级联删除命令：sql-- 如果有超表且不需要保留数据，先删除超表

DROP TABLE table_name CASCADE;

-- 从数据库中移除扩展
DROP EXTENSION timescaledb CASCADE;

2. 修改配置文件打开 uxsql 的配置文件 uxsinodb.conf，将 shared_preload_libraries 项中的 timescaledb 移除。
3. 保存配置文件后，重启UXDB服务使更改生效。

4. 核心概念

4.1 超表（Hypertables）

超表是 UXDB-T 的核心抽象，是对普通数据表的扩展。它在内部自动按时间（及可选的其它维度）将数据分区存储到称为**数据块（Chunks）**的物理表中。

超表的优势：

• 自动时间分区：将数据按时间范围分割到不同数据块，可配置块间隔（默认 7 天）；
• 智能索引：创建超表时自动在时间列上创建降序索引；
• 块跳过（Chunk Skipping）：查询时自动跳过不包含相关时间范围的数据块；
• 超函数支持：内置时间序列分析专用函数。

4.2 数据块（Chunks）

数据块是超表的物理分区，每个数据块包含特定时间范围（以及可选的特定空间分区）的数据行。

为什么数据块重要：

• 查询性能：通过块跳过机制，查询规划器可以跳过不包含目标时间范围的数据块；
• 维护与生命周期：基于数据块的列式存储转换（Hypercore）、数据保留（整块删除）、分层存储；
• 并行处理：多个数据块可被并行处理，提升查询性能；
• 索引：数据块级别的索引更小、更高效。

4.3 Hypercore 混合存储引擎

Hypercore 是 UXDB-T 的混合行列存储引擎，结合了行存储和列存储的优点：

• 行存储（Rowstore）：新数据首先写入行存储，优化高速插入和更新；
• 列存储（Columnstore）：随着数据冷却，自动转换为列存储，实现高效扫描和聚合。

列存储性能机制：

• 块跳过：跳过与查询无关的整个数据块；
• 向量化执行：直接在列存储批次上评估聚合函数；
• 稀疏索引：Bloom（等值过滤）和 MinMax（范围过滤）跳过单个批次；
• 元数据聚合：COUNT、MIN、MAX 从批次元数据直接读取。

4.4 连续聚合（Continuous Aggregates）

连续聚合是 UXDB-T 特有的物化视图，能够在后台自动增量刷新。当新数据添加或旧数据修改时，只有受影响的部分会被重新计算，而非重建整个视图。

• 实时聚合：将预聚合数据与最新原始数据合并，返回实时结果；
• 分层连续聚合：可在连续聚合之上再创建连续聚合，实现多粒度汇总；
• ACID 保证：完整的事务支持。

4.5 数据生命周期

UXDB-T 将数据生命周期分为五个阶段：

5. 数据建模

5.1 表布局选择

UXDB-T 支持三种表布局：

窄表（Narrow） — 每种指标一张表：

CREATE TABLE cpu_usage (
    time    TIMESTAMPTZ NOT NULL,
    host_id INT,
    usage   DOUBLE PRECISION
);

优点：每种指标有独立数据类型，压缩率高。缺点：表数量多，需要 JOIN。

宽表（Wide） — 每列一种指标：

CREATE TABLE host_metrics (
    time         TIMESTAMPTZ NOT NULL,
    host_id      INT,
    cpu_usage    DOUBLE PRECISION,
    mem_bytes    BIGINT,
    disk_io_read BIGINT
);

优点：查询简单，列访问高效。缺点：新增指标需 ALTER TABLE。

中表（Medium） — 按数据类型分列，带元数据：

CREATE TABLE metrics (
    time          TIMESTAMPTZ NOT NULL,
    host_id       INT,
    metric_name   TEXT,
    value_double  DOUBLE PRECISION,
    value_bigint  BIGINT
);

优点：新增指标无需改表结构。缺点：需按 metric_name 过滤。

选择建议：已知稳定的指标用宽表；固定小量类型用窄表；动态/用户定义指标用中表。

5.2 主键、时间列与唯一性

UXDB-T 要求每个唯一约束或主键必须包含分区列：

-- 正确：主键包含分区列
CREATE TABLE sensor_data (
    time        TIMESTAMPTZ NOT NULL,
    device_id   INT,
    temperature DOUBLE PRECISION,
    PRIMARY KEY (device_id, time)
);

-- 错误：主键不包含分区列
-- PRIMARY KEY (id) -- 这是不允许的

建模检查清单：

• 分区列：选择一个明确的时间列或整数列；
• 唯一性：确保唯一键包含所有分区列；
• 时间列建议使用 timestamptz（而非 timestamp）；
• 分区列自动为 NOT NULL。

5.3 模式优化最佳实践

• 使用 timestamptz 而非 timestamp；
• 仅对经常过滤的列添加二级索引；
• 对半结构化数据使用 JSONB 而非 JSON；
• 唯一键/主键必须包含分区列；
• 利用 CHECK、NOT NULL 等约束保证数据完整性。

6. 创建与管理超表

6.1 创建超表

创建普通表：

CREATE TABLE conditions (
    time        TIMESTAMPTZ NOT NULL,
    location    TEXT NOT NULL,
    device      TEXT NOT NULL,
    temperature DOUBLE PRECISION NULL,
    humidity    DOUBLE PRECISION NULL
);

转为超表：

SELECT create_hypertable('conditions', by_range('time'));

将已有数据的普通表转换为超表：

SELECT create_hypertable('existing_table', by_range('time'), migrate_data => true);

更安全的大表转换方案：

CREATE TABLE new_table (LIKE existing_table INCLUDING ALL);
INSERT INTO new_table SELECT * FROM existing_table;

6.2 更改表

通过 ALTER TABLE ... SET (选项 = 值) 修改超表选项：

ALTER TABLE conditions SET (
    timescaledb.segmentby = 'device',
    timescaledb.orderby   = 'time'
);

ALTER TABLE conditions SET (
    timescaledb.enable_columnstore         = true,
    timescaledb.compress_orderby           = 'time',
    timescaledb.compress_segmentby         = 'device',
    timescaledb.compress_chunk_time_interval = '1d'
);

6.3 分区

时间分区（默认）

CREATE TABLE sensor_data (
    time        TIMESTAMPTZ NOT NULL,
    device_id   INT,
    temperature DOUBLE PRECISION
);

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

整数分区

CREATE TABLE events (
    epoch_ms  BIGINT NOT NULL,
    device_id INT,
    payload   JSONB
);

SELECT create_hypertable('events', by_range('epoch_ms'));

使用整数分区时，需要设置 integer_now_func：

CREATE OR REPLACE FUNCTION epoch_ms_now() RETURNS BIGINT
LANGUAGE SQL STABLE AS $$
    SELECT (extract(epoch FROM now()) * 1000)::BIGINT
$$;

SELECT set_integer_now_func('events', 'epoch_ms_now');

空间分区（哈希维度）

-- 添加哈希维度分区（按 device_id 分为 4 个分区）
SELECT add_dimension('my_table', by_hash('device_id', 4));

警告：空间分区会增加数据块数量，增加查询规划开销，且所有唯一约束必须包含哈希列。

6.4 数据块间隔调优

数据块大小直接影响插入和查询性能。目标是将活跃数据块保持在内存中。

调优指导：

• 默认：时间列 7 天；整数列根据类型不同（smallint: 10,000，integer: 100,000，bigint: 1,000,000）；
• 设置数据块间隔使正在写入的数据块索引约占用 25% 的 shared_buffers；
• 例如：64GB 内存的系统，shared_buffers 约 16GB，若索引每日增长约 2GB，则 7 天间隔合适；若每日增长约 10GB，则使用 1 天间隔。

修改数据块间隔：

-- 仅影响修改后创建的新数据块
SELECT set_chunk_time_interval('sensor_data', INTERVAL '1 day');

数据块过多的症状：

• 超表超过 1000 个数据块；
• 大范围扫描时出现间歇性 OOM 错误；
• 历史数据的列存储压缩率低。

6.5 管理数据块

-- 查看超表的所有数据块
SELECT show_chunks('conditions');

-- 查看指定时间范围内的数据块
SELECT show_chunks('conditions', older_than => INTERVAL '3 months');

-- 获取数据块大小信息
SELECT * FROM chunks_detailed_size('conditions');

-- 合并数据块
CALL merge_chunks('_timescaledb_internal._hyper_1_1_chunk',
                  '_timescaledb_internal._hyper_1_2_chunk');

6.6 超表大小信息

-- 总大小
SELECT hypertable_size('conditions');

-- 详细大小
SELECT * FROM hypertable_detailed_size('conditions');

-- 索引大小
SELECT hypertable_index_size('conditions');

-- 近似大小（快速）
SELECT hypertable_approximate_size('conditions');

6.7 超表高级操作

-- 删除超表
DROP TABLE conditions;

-- 重新排序数据块
SELECT reorder_chunk('_timescaledb_internal._hyper_1_2_chunk', 'conditions_time_idx');

启用非分区列的数据块跳过：

-- 1. 实例级启用（uxsinodb.conf 或 ALTER SYSTEM），并重启数据库
ALTER SYSTEM SET timescaledb.enable_chunk_skipping TO on;

-- 2. 为具体超表的指定列启用块跳过
SELECT enable_chunk_skipping('conditions', 'device_id');

7. 数据写入

7.1 插入数据

UXDB-T 使用标准的 INSERT 语法：

INSERT INTO conditions (time, location, device, temperature, humidity)
VALUES
    (NOW(), 'office-1', 'sensor-a', 22.5, 60.0),
    (NOW(), 'office-1', 'sensor-b', 23.1, 58.5),
    (NOW(), 'warehouse', 'sensor-c', 19.8, 65.2);

7.2 批量写入

-- 使用单条 INSERT 插入多行
INSERT INTO sensor_data (time, device_id, temperature)
SELECT generate_series(
        NOW() - INTERVAL '1 hour',
        NOW(),
        INTERVAL '1 second'
    ) AS time,
    floor(random() * 100)::int AS device_id,
    random() * 40 + 10 AS temperature;

7.3 UPSERT（插入更新）

INSERT INTO sensor_data (time, device_id, temperature, humidity)
VALUES (NOW(), 1, 25.0, 60.0)
ON CONFLICT (device_id, time)
DO UPDATE SET temperature = EXCLUDED.temperature,
              humidity    = EXCLUDED.humidity;

注意：唯一约束必须包含分区列。

8. 数据查询

8.1 基本查询

-- 查询最近一小时的数据
SELECT time, device_id, temperature
FROM sensor_data
WHERE time > NOW() - INTERVAL '1 hour'
ORDER BY time DESC;

8.2 时间桶（Time Bucket）

time_bucket 是 UXDB-T 的核心函数，用于将数据按固定时间间隔分组。

基本用法：

SELECT time_bucket('1 day', time) AS bucket,
       AVG(temperature) AS avg_temp
FROM weather_conditions
GROUP BY bucket
ORDER BY bucket ASC;

常用时间间隔： 微秒、毫秒、秒、分钟、小时、天、周、月、年。

显示桶的结束时间：

SELECT time_bucket('5 min', time) + '5 min'::interval AS bucket_end,
       MIN(cpu),
       MAX(cpu)
FROM metrics
GROUP BY bucket_end
ORDER BY bucket_end DESC;

偏移起始时间：

SELECT time_bucket('5 hours', time, '1 hour'::INTERVAL) AS bucket,
       AVG(cpu)
FROM metrics
GROUP BY bucket
ORDER BY bucket DESC;

计算单个值的时间桶：

SELECT time_bucket(INTERVAL '1 week', TIMESTAMP '2021-01-05');

8.3 高级分析查询

最新数据点（Last Observation Carried Forward）：

SELECT time_bucket_gapfill('1 hour', time,
                            NOW() - INTERVAL '1 day',   -- 起始时间
                            NOW()) AS bucket,            -- 结束时间
       device_id,
       LOCF(AVG(temperature)) AS filled_avg_temp
FROM readings
WHERE time >= NOW() - INTERVAL '1 day'
GROUP BY bucket, device_id
ORDER BY bucket DESC;

百分位分析：

SELECT
    percentile_cont(0.5)  WITHIN GROUP (ORDER BY response_time) AS median,
    percentile_cont(0.95) WITHIN GROUP (ORDER BY response_time) AS p95,
    percentile_cont(0.99) WITHIN GROUP (ORDER BY response_time) AS p99
FROM api_requests
WHERE time > NOW() - INTERVAL '1 day';

注意：response_time 应为 double precision 类型。

直方图：

SELECT histogram(response_time_ms, 0, 1000, 10)
FROM api_requests
WHERE time > NOW() - INTERVAL '1 day';

近似行计数：

SELECT approximate_row_count('readings');

9. 超函数（Hyperfunctions）

UXDB-T 提供一组专为时间序列分析设计的内置函数，称为超函数。

9.1 通用时间序列函数

10. 连续聚合

10.1 创建连续聚合

CREATE MATERIALIZED VIEW conditions_hourly
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', time) AS bucket,
    location,
    AVG(temperature) AS avg_temp,
    MAX(temperature) AS max_temp,
    MIN(temperature) AS min_temp
FROM conditions
GROUP BY bucket, location;

10.2 刷新策略

-- 添加刷新策略：每小时刷新一次，刷新 3 小时前到 1 小时前的数据
SELECT add_continuous_aggregate_policy('conditions_hourly',
    start_offset      => INTERVAL '3 hours',
    end_offset        => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour'
);

-- 移除刷新策略
SELECT remove_continuous_aggregate_policy('conditions_hourly');

10.3 手动刷新

-- 手动刷新指定时间范围
CALL refresh_continuous_aggregate('conditions_hourly',
    '2024-01-01', '2024-02-01');

10.4 查询连续聚合

-- 与普通表完全相同的查询方式
SELECT bucket, location, avg_temp
FROM conditions_hourly
WHERE bucket >= NOW() - INTERVAL '7 days'
  AND location = 'office'
ORDER BY bucket DESC;

10.5 实时聚合

默认情况下，连续聚合不包含最新的未物化数据。实时聚合将预聚合数据与最新原始数据合并。

-- 启用实时聚合
ALTER MATERIALIZED VIEW conditions_hourly
SET (timescaledb.materialized_only = false);

-- 禁用实时聚合
ALTER MATERIALIZED VIEW conditions_hourly
SET (timescaledb.materialized_only = true);

10.6 分层连续聚合

可以在连续聚合之上创建新的连续聚合，实现多粒度汇总：

-- 1. 小时级聚合
CREATE MATERIALIZED VIEW response_times_hourly
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 h'::interval, ts) AS bucket,
    api_id,
    AVG(response_time_ms),
    percentile_agg(response_time_ms) AS percentile_hourly
FROM response_times
GROUP BY 1, 2;

-- 2. 日级聚合（基于小时级聚合）
CREATE MATERIALIZED VIEW response_times_daily
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 d'::interval, bucket) AS bucket_daily,
    api_id,
    MEAN(ROLLUP(percentile_hourly)) AS mean,
    ROLLUP(percentile_hourly) AS percentile_daily
FROM response_times_hourly
GROUP BY 1, 2;

分层限制：

• 只能在已 finalized 的连续聚合上创建；
• 时间桶必须 >= 底层桶且为其整数倍；
• 固定宽度桶不能叠加在可变宽度桶上（如月叠周）。

10.7 管理物化超表

-- 查看连续聚合的物化超表名称
SELECT view_name,
       format('%I.%I', materialization_hypertable_schema,
              materialization_hypertable_name) AS materialization_hypertable
FROM timescaledb_information.continuous_aggregates;

10.8 连续聚合与 JOIN

连续聚合支持 INNER JOIN、LEFT JOIN 和 LATERAL JOIN（版本相关），可在超表和一个或多个普通表之间进行：

CREATE MATERIALIZED VIEW device_summary
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', r.time) AS bucket,
    r.device_id,
    d.location,
    AVG(r.value) AS avg_value
FROM readings r
JOIN devices d ON r.device_id = d.id
GROUP BY bucket, r.device_id, d.location;

10.9 连续聚合与时区

在连续聚合函数中使用显式时区而非依赖会话时区：

CREATE MATERIALIZED VIEW device_summary
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', observation_time) AS bucket,
    MIN(observation_time AT TIME ZONE 'EST') AS min_time,
    device_id,
    AVG(metric) AS metric_avg
FROM device_readings
GROUP BY bucket, device_id;

10.10 为连续聚合启用列存储

ALTER MATERIALIZED VIEW assets_candlestick_daily SET (
    timescaledb.enable_columnstore = true,
    timescaledb.segmentby = 'symbol'
);

CALL add_columnstore_policy('assets_candlestick_daily', INTERVAL '1d');

10.11 管理连续聚合

-- 修改连续聚合
ALTER MATERIALIZED VIEW conditions_hourly
SET (timescaledb.materialized_only = true);

-- 删除连续聚合
DROP MATERIALIZED VIEW conditions_hourly;

-- 迁移旧格式到新格式
SELECT cagg_migrate('conditions_hourly');

11. Hypercore 列式存储与压缩

11.1 启用列存储

现有超表启用：

ALTER TABLE crypto_ticks SET (
    timescaledb.enable_columnstore,
    timescaledb.segmentby = 'symbol',
    timescaledb.orderby   = 'time DESC'
);

CALL add_columnstore_policy('crypto_ticks', INTERVAL '7d');

11.2 列存储策略

-- 添加列存储策略：数据写入 7 天后自动转换为列存储
CALL add_columnstore_policy('conditions', INTERVAL '7d');

-- 基于数据块创建时间的策略
CALL add_columnstore_policy('conditions', created_before => INTERVAL '7d');

-- 移除列存储策略（保持现有列存储数据不变）
CALL remove_columnstore_policy('conditions');

-- 禁用列存储（需先将数据块转回行存储）
ALTER TABLE conditions SET (timescaledb.enable_columnstore = false);

11.3 手动转换数据块

批量并行转换（适合历史大回填）：

-- 列出待转换的数据块
SELECT show_chunks('crypto_ticks', older_than => INTERVAL '7d');

-- 会话1：转换数据块 A
CALL convert_to_columnstore('_timescaledb_internal._hyper_1_2_chunk');

-- 会话2（并发）：转换数据块 B（不阻塞）
CALL convert_to_columnstore('_timescaledb_internal._hyper_1_3_chunk');

-- 将数据块转回行存储
CALL convert_to_rowstore('_timescaledb_internal._hyper_1_2_chunk');

11.4 数据块转换时执行回填

如果需要在列存储策略运行的同时回填历史数据，应暂停策略以避免锁竞争：

-- 1. 查找策略的 job_id
SELECT job_id FROM timescaledb_information.jobs
WHERE proc_name = 'policy_compression' AND hypertable_name = 'crypto_ticks';

-- 2. 暂停策略
SELECT alter_job(<JOB_ID>, scheduled => false);

-- 3. 回填数据
-- 4. 转换受影响的数据块
CALL convert_to_columnstore('_timescaledb_internal._hyper_1_2_chunk');

-- 5. 重新启用策略
SELECT alter_job(<JOB_ID>, scheduled => true);

11.5 查看压缩统计信息

-- 查看超表压缩率
SELECT
    ux_size_pretty(before_compression_total_bytes) AS before,
    ux_size_pretty(after_compression_total_bytes)  AS after
FROM hypertable_columnstore_stats('crypto_ticks');

-- 查看每个数据块的压缩信息
SELECT * FROM chunk_columnstore_stats('crypto_ticks');

11.6 压缩方法

根据数据类型不同，UXDB-T 使用不同的压缩算法：

11.7 限制

• 列存储数据块不支持 ROW LEVEL SECURITY；
• 列存储中修改数据使用标准 SQL（INSERT、UPDATE、DELETE、UPSERT 均支持，有高性能实现）。

12. 数据生命周期管理

12.1 数据保留策略

数据保留基于数据块级别操作，而非行级 DELETE，速度更快且不需要垃圾回收。

添加保留策略：

-- 基于数据时间：丢弃 6 个月前的数据
SELECT add_retention_policy('conditions', drop_after => INTERVAL '6 months');

-- 基于数据块创建时间
SELECT add_retention_policy('conditions', drop_created_before => INTERVAL '6 months');

移除保留策略：

SELECT remove_retention_policy('conditions');

12.2 手动删除数据块

-- 删除 24 小时前的所有数据块
SELECT drop_chunks('conditions', INTERVAL '24 hours');

-- 删除 3 个月前到 4 个月前的数据
SELECT drop_chunks(
    'conditions',
    older_than => INTERVAL '3 months',
    newer_than => INTERVAL '4 months'
);

-- 删除未来的数据块
SELECT drop_chunks(
    'conditions',
    newer_than => NOW() + INTERVAL '3 months'
);

12.3 连续聚合与数据保留

将保留策略与连续聚合结合，可以在保留汇总数据的同时删除原始数据：

原始数据保留 30 天 → 小时聚合保留 600 天 → 日聚合保留更长时间

注意：刷新连续聚合时，UXDB-T 会根据原始数据的变更来更新聚合数据。如果原始数据已被删除，刷新时会同时删除对应的聚合数据。因此，保留策略的周期应长于连续聚合的刷新窗口。

-- 创建日聚合
CREATE MATERIALIZED VIEW conditions_daily
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 day', time) AS day,
       device_id,
       AVG(temperature),
       MIN(temperature),
       MAX(temperature)
FROM conditions
GROUP BY day, device_id;

-- 刷新策略：每天刷新 7 天前到 1 天前的数据
SELECT add_continuous_aggregate_policy('conditions_daily',
    start_offset      => INTERVAL '7 days',
    end_offset        => INTERVAL '1 day',
    schedule_interval => INTERVAL '1 day');

ALTER TABLE conditions SET (timescaledb.enable_columnstore = true);

-- 原始数据保留 30 天
SELECT add_retention_policy('conditions', drop_after => INTERVAL '30 days');

-- 聚合数据保留 600 天
SELECT add_retention_policy('conditions_daily', drop_after => INTERVAL '600 days');

12.4 完整生命周期示例

-- 阶段 1：创建超表
CREATE TABLE conditions (
    time        TIMESTAMPTZ NOT NULL,
    device_id   INT,
    temperature DOUBLE PRECISION,
    humidity    DOUBLE PRECISION
);
SELECT create_hypertable('conditions', by_range('time'));

-- 阶段 3：创建连续聚合
CREATE MATERIALIZED VIEW conditions_daily
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 day', time) AS day,
       device_id,
       AVG(temperature),
       MIN(temperature),
       MAX(temperature)
FROM conditions
GROUP BY day, device_id;

SELECT add_continuous_aggregate_policy('conditions_daily',
    start_offset      => INTERVAL '7 days',
    end_offset        => INTERVAL '1 day',
    schedule_interval => INTERVAL '1 day');

ALTER TABLE conditions SET (timescaledb.enable_columnstore = true);

-- 阶段 4a：7 天后自动转为列存储
CALL add_columnstore_policy('conditions', INTERVAL '7 days');

-- 阶段 5：12 个月后删除原始数据
SELECT add_retention_policy('conditions', drop_after => INTERVAL '12 months');

13. 性能优化

13.1 索引策略

默认索引：创建超表时自动在分区列（时间降序）上创建 B-tree 索引；如有空间维度，额外创建复合索引。

支持的索引类型：

创建索引：

-- 常规索引
CREATE INDEX ON sensor_data (device_id, time DESC);

-- 逐数据块构建索引（避免长锁）
CREATE INDEX idx_device_time ON sensor_data (device_id, time DESC)
WITH (timescaledb.transaction_per_chunk = true);

唯一索引和主键：必须包含分区列。

13.2 数据块跳过（Chunk Skipping）

数据块跳过是 UXDB-T 的关键性能特性，引擎在查询时自动跳过与时间范围无关的数据块。

-- 1. 实例级启用并重启数据库
ALTER SYSTEM SET timescaledb.enable_chunk_skipping TO on;

-- 2. 启用非分区列的块跳过
SELECT enable_chunk_skipping('sensor_data', 'device_id');

13.3 SkipScan

SkipScan 优化针对 DISTINCT 操作，通过增量跳跃有序值来加速：

-- 当有合适索引时，UXDB-T 自动使用 SkipScan
SELECT DISTINCT device_id FROM sensor_data
WHERE time > NOW() - INTERVAL '1 day';

13.4 超表性能调优

-- 启用逐块聚合优化
SET timescaledb.enable_chunkwise_aggregation = on;

-- 启用向量化聚合（针对列存储）
SET timescaledb.vectorized_aggregation = on;

-- 启用连续聚合合并优化
SET timescaledb.enable_merge_on_cagg_refresh = on;

13.5 约束排除

UXDB-T 自动使用约束排除机制来跳过不相关的数据块：

-- 创建 CHECK 约束帮助约束排除
ALTER TABLE sensor_data ADD CONSTRAINT temperature_check
CHECK (temperature >= -50 AND temperature <= 100);

13.6 存储管理与 VACUUM

-- 手动 vacuum 回收空间并刷新统计信息
VACUUM ANALYZE conditions;

-- 查看超表大小
SELECT hypertable_size('conditions');

13.7 模式变更性能

-- 添加列（在列存储上高效）
ALTER TABLE conditions ADD COLUMN wind_speed DOUBLE PRECISION;

-- 删除列
ALTER TABLE conditions DROP COLUMN humidity;

注意：对包含列存储数据块的超表，部分 ALTER TABLE 操作受限。

14. 作业与自动化

14.1 创建自定义作业

-- 创建清理存储过程
CREATE OR REPLACE PROCEDURE cleanup_old_data(job_id INT, config JSONB)
LANGUAGE PLUXSQL AS $$
BEGIN
    DELETE FROM metrics WHERE time < NOW() - INTERVAL '90 days';
    RAISE NOTICE 'Cleanup completed for job %', job_id;
END
$$;

-- 注册为定时作业（每小时执行）
SELECT add_job('cleanup_old_data', '1 hour');

14.2 带参数的作业

CREATE OR REPLACE PROCEDURE aggregate_metrics(job_id INT, config JSONB)
LANGUAGE PLUXSQL AS $$
DECLARE
    threshold INT := (config->>'threshold')::INT;
BEGIN
    INSERT INTO daily_summary
    SELECT time_bucket('1 day', time), location, AVG(value)
    FROM metrics
    WHERE value > threshold
    GROUP BY 1, 2;
END
$$;

-- 传递配置参数
SELECT add_job(
    'aggregate_metrics',
    '1 day',
    config => '{"threshold": 100}'
);

14.3 管理作业

-- 修改作业调度间隔
SELECT alter_job(1000, schedule_interval => INTERVAL '2 hours');

-- 暂停作业
SELECT alter_job(1000, scheduled => false);

-- 恢复作业
SELECT alter_job(1000, scheduled => true);

-- 手动运行作业
CALL run_job(1000);

-- 删除作业
SELECT delete_job(1000);

14.4 内置策略总结

15. 监控与信息视图

15.1 超表信息

-- 查看所有超表
SELECT hypertable_name, num_dimensions, num_chunks, compression_enabled
FROM timescaledb_information.hypertables;

15.2 数据块信息

-- 查看指定超表的所有数据块
SELECT chunk_name, range_start, range_end, is_compressed
FROM timescaledb_information.chunks
WHERE hypertable_name = 'conditions'
ORDER BY range_start DESC;

15.3 维度信息

SELECT * FROM timescaledb_information.dimensions;

15.4 列存储信息

-- 查看所有超表的列存储设置
SELECT * FROM timescaledb_information.hypertable_columnstore_settings;

-- 查看所有数据块的列存储设置
SELECT * FROM timescaledb_information.chunk_columnstore_settings;

15.5 连续聚合信息

SELECT view_name, view_definition, materialized_only,
       compression_enabled, finalized
FROM timescaledb_information.continuous_aggregates;

15.6 作业监控

-- 查看所有注册的作业
SELECT job_id, application_name, schedule_interval, next_start
FROM timescaledb_information.jobs
ORDER BY next_start;

-- 查看作业执行统计
SELECT job_id, last_run_started_at, last_successful_finish,
       last_run_status, total_runs, total_failures
FROM timescaledb_information.job_stats
ORDER BY last_run_started_at DESC;

-- 查看作业错误
SELECT * FROM timescaledb_information.job_errors;

-- 查看作业执行历史
SELECT * FROM timescaledb_information.job_history;

15.7 最佳实践

重要：所有监控和自动化查询应仅使用 timescaledb_information 模式中的视图。这些视图被视为公共 API 的一部分，在版本升级中保持稳定。

应避免直接查询的内部模式：

• _timescaledb_cache
• _timescaledb_catalog
• _timescaledb_internal

16. 备份与恢复

16.1 物理备份

# 使用 ux_basebackup 创建物理备份
ux_basebackup -D /backup/dir -h localhost -p 52025 -U uxdb

使用连续归档（修改数据库实例目录下的配置文件 uxsinodb.conf）：

archive_mode = on
archive_command = 'cp %p /archive/%f'

16.2 逻辑备份

# 使用 ux_dump 备份整个数据库
ux_dump -h localhost -U uxdb -F c -f backup.dump dbname

# 恢复到新数据库
ux_restore -h localhost -U uxdb -d dbname backup.dump

16.3 恢复准备

恢复操作前后需在同一会话/流程中依次执行：

-- 恢复前准备数据库
SELECT timescaledb_pre_restore();

ux_restore -d mydb2 -U uxdb /home/uxdb/backup.dump

-- 执行恢复操作后
SELECT timescaledb_post_restore();

17. 迁移数据

17.1 从普通表迁移到 UXDB-T 超表

SELECT create_hypertable('existing_table', by_range('time'), migrate_data => true);

17.2 文件导入导出

-- 导出为 CSV
COPY (SELECT * FROM conditions) TO '/home/uxdb/aaa.csv' DELIMITER ',' CSV HEADER;

-- 从 CSV 文件导入
COPY conditions FROM '/path/to/data.csv' DELIMITER ',' CSV HEADER;

也可使用 UxdbAdmin 等工具的导入功能。

18. 生态集成

18.1 编程语言连接

UXDB-T 可用任何 UXDB-T 驱动连接：

18.2 管理工具

18.3 基础设施

19. 故障排除

19.1 超表问题

19.2 连续聚合问题

19.3 列存储问题

19.4 数据导入问题

19.5 时间桶问题

19.6 作业失败排查

-- 查看失败的作业
SELECT * FROM timescaledb_information.job_errors;

-- 查看作业历史
SELECT * FROM timescaledb_information.job_history;

-- 手动运行作业测试
CALL run_job(<job_id>);

20. 参考与附录

20.1 关键 SQL 函数速查

超表与数据块

列存储

连续聚合

数据保留

作业自动化

20.2 配置参数

可以修改数据库实例目录下的配置文件 uxsinodb.conf 中的以下参数，并重启数据库。

在 uxsql 中可通过以下命令列出所有相关参数：

SHOW all;  -- 结合 grep -i timescaledb 过滤

参数总表

常用优化参数

重点参数调优说明

查询规划与执行

timescaledb.enable_chunkwise_aggregation（bool）

如果启用，聚合在查询过程中会被转换为部分聚合计划。聚合的第一部分按每个区块执行，然后这些部分结果会被合并并最终确定。拆分聚合减少创建的哈希表大小并提升数据局部性，从而加快查询速度。

timescaledb.vectorized_aggregation（bool）

启用或禁用查询执行程序中的向量化优化。例如，压缩块上的聚合函数可以进行此类优化。

timescaledb.enable_merge_on_cagg_refresh（bool）

设置为 on 可大幅减少连续聚合上写入的数据量：在存在少量变更的情况下，降低刷新连续聚合的 I/O 成本，并产生较少的预写日志（WAL）。只有在没有启用压缩的连续聚合上才有效。

策略

timescaledb.max_background_workers（int）

分配给 UXDB-T 的最大后台工作进程数。设置为至少 1 + 实例中加载了 UXDB-T 扩展的数据库数量。默认值是 16。

自托管 UXDB-T 调优

timescaledb.disable_load（bool）

禁用实际扩展的加载。

管理

timescaledb.restoring（bool）

将 UXDB-T 设置为恢复模式。默认情况下是禁用的。

timescaledb.telemetry_level（enum）

遥测设置级别。用于确定发送遥测数据的级别。可以设置为 off 或 basic，默认为 basic。

20.3 术语表