TimescaleDB - 核心概念與架構入門TimescaleDB是PostgreSQL時序擴充，透過Hypertab

TimescaleDB - 核心概念與架構入門

你可以從這篇筆記獲得什麼？

了解 TimescaleDB 與一般 PostgreSQL 的差異，為什麼需要它。
熟悉 Hypertable / Chunk / 維度等關鍵名詞與實際作用。
透過簡化範例掌握 Continuous Aggregates、Hyperfunctions、壓縮與資料保留政策的運作方式。
建立後續實作（LamTrade 指南）的最低知識基線。

為何需要 TimescaleDB

TimescaleDB 是一個 PostgreSQL 擴充套件，專門處理「時間序列資料」。它保留 PostgreSQL 的 SQL、生態系、事務與擴充能力，並針對大量按時間排序的資料，提供自動分區、增強型聚合、壓縮與保留策略。對金融行情、IoT 指標、APM 監控等需要「每秒寫入 + 依時間分析」的場景，它能在熟悉的 SQL 介面上提供更高的吞吐與可維運性。（官方 Hypertable 頁面）

核心構件速覽

名稱	作用	你需要記得的要點
Hypertable	對外看起來像一張表，內部自動切分成多個 chunk	所有查詢/寫入都鎖定 Hypertable，TimescaleDB 會自動管理 chunk
Chunk	Hypertable 的實際資料分區，每個 chunk 代表固定時間範圍（例如 1 天）	查詢會跳過不相關的 chunk，大幅減少掃描量
維度（Dimension）	定義 chunk 的切分方式。至少需要時間維度，可選擇額外的雜湊或區間維度	第二維度常用在多租戶或股票代碼，以平衡熱點
Continuous Aggregate (CA)	連續聚合檢視，背景自動增量刷新	用來事先算好 5 分鐘線、15 分鐘線等聚合指標
Hyperfunctions	TimescaleDB 提供的分析函式套件，如進階 time_bucket、統計與金融函式	可減少自訂 SQL 或複雜副查詢，官方 Hyperfunctions 頁面
Policy Jobs	TimescaleDB 內建的排程器	自動執行 CA 刷新、壓縮、資料保留等作業

Hypertable 與 Chunk：如何切割時間序列資料

基本概念

Hypertable 就像是一張「無限延伸的時間軸」，TimescaleDB 會依 chunk_time_interval 參數，自動把這條時間軸切成一段段 chunk：

時間 →  |--- chunk_2024-01-01 ---|--- chunk_2024-01-02 ---|--- chunk_2024-01-03 ---| ...
資料量  ↑            ↑             ↑
        24h 5 分鐘 K 線寫入        24h 校對          24h 查詢

官方建議 chunk 大小要讓「活躍 chunk 的索引」可以放進記憶體的 25%，否則寫入與查詢都會變慢。（Optimize chunk interval）

建立 Hypertable 的步驟

CREATE TABLE tick_raw (
    ts timestamptz NOT NULL,
    symbol text NOT NULL,
    price numeric(18,6) NOT NULL,
    volume numeric(20,2) NOT NULL
);

SELECT create_hypertable('tick_raw', by_range('ts'), chunk_time_interval => interval '1 day');
SELECT add_dimension('tick_raw', by_hash('symbol', 8));

先用 PostgreSQL 語法建立表格。
create_hypertable 定義時間維度與 chunk interval。
add_dimension 加上第二維度（例如 symbol）分散熱點。

寫入資料的策略

直接寫入（即時）

INSERT INTO tick_raw (ts, symbol, price, volume)
VALUES (now(), 'AAPL', 189.12, 5000);

盤後校對（Upsert）

TimescaleDB 完全相容 PostgreSQL 的 INSERT ... ON CONFLICT ... DO UPDATE 語法：

INSERT INTO tick_raw (ts, symbol, price, volume)
VALUES ('2026-02-28 09:35', 'AAPL', 188.80, 1200)
ON CONFLICT (ts, symbol) DO UPDATE
    SET price = EXCLUDED.price,
        volume = EXCLUDED.volume;

大量校對時，建議使用 staging table 搭配 COPY，再一次性 Upsert，可大幅提升效率。（官方 COPY + Upsert 範例）

Continuous Aggregates：先算好再查最省時間

它是什麼？

Continuous Aggregate（CA）是一種「長期存在的物化檢視」，TimescaleDB 會在背景自動刷新，僅重新計算新資料。這讓你可以即時查詢 5 分鐘、15 分鐘、1 小時線等聚合結果，而不必每次重算整張表。（官方 CA 起步）

建立流程

CREATE MATERIALIZED VIEW bar_15m
WITH (timescaledb.continuous) AS
SELECT time_bucket(INTERVAL '15 minutes', ts) AS bucket,
       symbol,
       first(price, ts) AS open,
       max(price) AS high,
       min(price) AS low,
       last(price, ts) AS close,
       sum(volume) AS volume
FROM tick_raw
GROUP BY bucket, symbol;

SELECT add_continuous_aggregate_policy(
    'bar_15m',
    start_offset => INTERVAL '30 days',
    end_offset => INTERVAL '5 minutes',
    schedule_interval => INTERVAL '5 minutes'
);

time_bucket：把 timestamp 切成 15 分鐘區間。
first/last：Hyperfunctions 提供的聚合函式，確保正確開盤/收盤價。
add_continuous_aggregate_policy：設定背景刷新窗口與頻率。
若需要更即時的資料，可在查詢時啟用 real-time aggregates（WITH NO DATA / timescaledb.materialized_only 選項）。

階梯式聚合（Hierarchical CA）

CA 可以層層堆疊，例如 5 分鐘 CA 可作為 1 小時 CA 的來源，官方文件提供範例與效能分析。（官方 Hierarchical CA）

Hyperfunctions：針對時序資料設計的函式庫

TimescaleDB 內建一組 Hyperfunctions，涵蓋時間 bucket、統計、財務、gap 補齊等分析場景。（官方 Hyperfunctions 體系）

常用分類

時間切片：time_bucket、time_bucket_ng（支援曆法對齊，如一週、一個月）、gapfill（補齊缺值）。
金融函式：candlestick、first, last、moving_average，用於 OHLC 聚合。
統計函式：stats_agg、percentile_agg、counter_agg，提供複合統計維度。

進階 Hyperfunctions 範例

1. 使用 stats_agg / stats_mean / stats_stddev 計算一小時統計值

WITH hourly AS (
    SELECT time_bucket(INTERVAL '1 hour', ts) AS bucket,
           stats_agg(price) AS stats
    FROM tick_raw
    WHERE symbol = 'AAPL'
    GROUP BY bucket
)
SELECT bucket,
       stats_mean(stats)      AS avg_price,
       stats_stddev(stats)    AS price_stddev,
       stats_min(stats)       AS min_price,
       stats_max(stats)       AS max_price
FROM hourly
ORDER BY bucket;

stats_agg 會產生一個複合欄位，包含平均、標準差、最小值等必要資訊，一次聚合後可重複使用。
資料多層聚合時，可使用 rollup(stats_agg(...)) 將不同維度的統計結果合併，官方示例提供更多寫法。

2. 使用 moving_average 建立 10 筆滑動平均線

SELECT ts,
       price,
       moving_average(price, 10) OVER (PARTITION BY symbol ORDER BY ts) AS ma_10
FROM tick_raw
WHERE symbol = 'AAPL'
ORDER BY ts
LIMIT 200;

moving_average 來自 timescaledb_toolkit Hyperfunctions。確保安裝 CREATE EXTENSION timescaledb_toolkit;。
可搭配 moving_stddev, moving_variance 等函式，用於技術指標或趨勢分析。

3. 使用 time_bucket_ng 與 gapfill 補齊缺漏資料

SELECT bucket,
       locf(price)     AS price_locf,
       interpolate(price) AS price_interpolated
FROM (
    SELECT time_bucket_ng(INTERVAL '5 minutes', ts, origin => '2026-02-28 09:30') AS bucket,
           symbol,
           price
    FROM tick_raw
    WHERE symbol = 'AAPL'
) sub
WHERE symbol = 'AAPL'
ORDER BY bucket;

time_bucket_ng 保證 bucket 與市場開盤時間對齊。
locf（last observation carried forward）與 interpolate 可以在視覺化時維持時間軸連續性。

儲存生命週期管理

壓縮（Compression）

將不常更新的 chunk 轉成列式儲存，節省 5~10 倍空間並加速分析查詢。
設定方式：

ALTER TABLE tick_raw
  SET (timescaledb.compress,
       timescaledb.compress_segmentby = 'symbol');

SELECT add_compression_policy('tick_raw', INTERVAL '14 days');

官方建議保留近期資料為未壓縮狀態，讓即時寫入與校對保持快速。（Compression 指南）

資料保留（Retention）

用於自動刪除過舊 chunk，或搭配 CA 只保留高粒度摘要。
設定：

SELECT add_retention_policy('tick_raw', INTERVAL '365 days');

可以針對不同 CA 設不同保留期，達到「原始資料保留 1 年，日線保留 5 年」的設計。（Data Retention）

背景 Job 與監控

TimescaleDB 透過 _timescaledb_internal.bgw_job 啟動背景工作。可在 timescaledb_information.jobs 查看狀態與最後執行結果。
關鍵監控指標：chunk_count, job_error_count, compress_chunk_stats, continuous_aggregate_stats。
結合 Grafana/Prometheus 或 timescaledb_information 視圖，可建立儀表板追蹤寫入延遲與 policy 成功率。

常見錯誤與排除

CA watermark 在未來導致查不到即時資料：通常因為建立 CA 時使用 WITH DATA 或手動刷新了整個範圍。解法是重新以 WITH NO DATA 建立 CA，並設定合適的 refresh policy。（官方 Troubleshooting）
「incompatible bucket width」錯誤：階梯式 CA 必須確保上層 bucket 是下層 bucket 的倍數，例如 1 小時 → 1 天具備整除關係。
Retention 只套用在 Hypertable：連續聚合需要獨立的保留政策，否則物化資料不會自動刪除。
插入歷史資料後 CA 未更新：即時查詢只補最新 bucket，需要呼叫 refresh_continuous_aggregate 或調整 policy 才能重新計算既有區段。

建議學習順序

依本文的流程建立第一張 Hypertable 並插入模擬數據。
建立最簡單的 Continuous Aggregate，觀察背景刷新。
引入 Hyperfunctions（candlestick, stats_agg, moving_average）完成第一個報表。
啟用壓縮與保留政策，觀察 chunk 尺寸與儲存節省幅度。
讀完 LamTrade 實作筆記，對照專案需求進行調整。

與本專案的連結

TimescaleDB - 股票時序數據平台設計指南：LamTrade 實作細節。
若要調整 chunk interval、policy 等設定，也請同步更新該專案筆記。

參考資料

Timescale Docs - Hypertables - Hypertable 與 chunk 最佳實務
Timescale Docs - Continuous Aggregates - 連續聚合原理、背景刷新與階梯式設計
Timescale Docs - Hyperfunctions - 時序專用函式分類與範例
Timescale Docs - Upsert Data - Staging table + UPSERT 實務
Timescale Docs - Compression - 列式壓縮與政策
Timescale Docs - Data Retention - 保留政策與下採樣策略
Timescale Docs - Continuous Aggregates Troubleshooting - 常見 CA 問題與修復步驟

TimescaleDB - 核心概念與架構入門