setup-timescaledb-hypertables

TimescaleDB の完全なセットアップ

データは挿入されますが、変更されることはまれな、挿入中心のデータパターン向けの手順です。

• 時系列データ（センサー、メトリクス、システム監視）
• イベントログ（ユーザーイベント、監査証跡、アプリケーションログ）
• トランザクションレコード（注文、支払い、金融取引）
• シーケンシャルデータ（自動採番 ID とタイムスタンプを持つレコード）
• 追記専用データセット（不変のレコード、履歴データ）

ステップ 1：ハイパーテーブルを作成する

CREATE TABLE your_table_name (
    timestamp TIMESTAMPTZ NOT NULL,
    entity_id TEXT NOT NULL,          -- device_id, user_id, symbol, etc.
    category TEXT,                    -- sensor_type, event_type, asset_class, etc.
    value_1 DOUBLE PRECISION,         -- price, temperature, latency, etc.
    value_2 DOUBLE PRECISION,         -- volume, humidity, throughput, etc.
    value_3 INTEGER,                  -- count, status, level, etc.
    metadata JSONB                    -- flexible additional data
) WITH (
    tsdb.hypertable,
    tsdb.partition_column='timestamp',
    tsdb.enable_columnstore=true,     -- Disable if table has vector columns
    tsdb.segmentby='entity_id',       -- See selection guide below
    tsdb.orderby='timestamp DESC',     -- See selection guide below
    tsdb.sparse_index='minmax(value_1),minmax(value_2),minmax(value_3)' -- see selection guide below
);

圧縮の判断

• 挿入中心のパターンでは、デフォルトで有効にする
• テーブルにベクトル型の列（pgvector）がある場合は、無効にする。ベクトル列のインデックスはカラムストアと互換性がありません。

パーティション列の選択

時間ベース（TIMESTAMP/TIMESTAMPTZ/DATE）または、時間的／シーケンシャルな分布が良好な整数（INT/BIGINT）である必要があります。

一般的なパターン：

• 時系列：timestamp、event_time、measured_at
• イベントログ：event_time、created_at、logged_at
• トランザクション：created_at、transaction_time、processed_at
• シーケンシャル：id（タイムスタンプがない場合の自動採番）、sequence_number
• 追記専用：created_at、inserted_at、id

あまり適していない列： ingested_at（データがシステムに取り込まれた時刻。主要なクエリ軸である場合に限り使用してください） 避ける列： updated_at（主要なクエリ軸でない限り、時系列順が崩れます）

Segment_By 列の選択

単一列を優先してください。複数列が最適になることはほとんどありません。複数列が有効なのは、十分な行密度があり、相関性が非常に高い列（例：metric_name と metric_type）の場合に限られます。

要件：

• WHERE 句で頻繁に使用される（最もよく使われるフィルター）
• 行密度が高い（チャンクあたり、値ごとに 100 行超）
• 主要な論理パーティション／グループ

例：

• IoT：device_id
• 金融：symbol
• メトリクス：service_name、service_name, metric_type（十分な行密度がある場合）、metric_name, metric_type（十分な行密度がある場合）
• アナリティクス：十分な行密度がある場合は user_id、それ以外は session_id
• E コマース：十分な行密度がある場合は product_id、それ以外は category_id

行密度の目安：

• 目標：各チャンク内で、segment_by の値ごとに 100 行超。
• 不適切：チャンクあたり、segment_by の値ごとに 10 行未満 → 粒度の低い列を選択します。
• 行密度の低い列への対処：order_by 列のリストの先頭に追加します。

クエリパターンに基づいて選択します：

SELECT * FROM table WHERE entity_id = 'X' AND timestamp > ...
-- ↳ segment_by: entity_id (if >100 rows per chunk)

避ける列： タイムスタンプ、一意の ID、行密度の低い列（値ごとに 100 行未満／チャンク）、フィルタリングにほとんど使われない列

Order_By 列の選択

segment_by と組み合わせることで、圧縮を最適化する自然な時系列の並びを作成します。

最も一般的： timestamp DESC

例：

• IoT／金融／E コマース：timestamp DESC
• メトリクス：metric_name, timestamp DESC（metric_name の行密度が低すぎて segment_by に適さない場合）
• アナリティクス：user_id, timestamp DESC（user_id の行密度が低すぎて segment_by に適さない場合）

代替パターン：

• シーケンス番号を持つイベントストリームでは sequence_id DESC
• 同じタイムスタンプ内で順序付けするには timestamp DESC, event_order DESC

行密度の低い列への対処： 列の行数がチャンクあたり 100 行未満で（segment_by に対して少なすぎる場合）、order_by の先頭に追加します。

• 例：metric_name がチャンクあたり 20 行の場合、segment_by='service_name'、order_by='metric_name, timestamp DESC' を使用します。
• 類似した値（すべての温度測定値、その後にすべての圧力測定値）をまとめることで、圧縮率を高めます。

適切かどうかの確認方法： (segment_by_column, order_by_column) によって作られる並びが、自然な時系列の進行を形成していること。並びの中で近接する値が、互いに似ている必要があります。

order_by で避ける列： ランダムな列、隣接する行間のばらつきが大きい列、segment_by と無関係な列

圧縮スパースインデックスの選択

スパースインデックスを使用すると、解凍せずに圧縮データをクエリでフィルタリングできます。バッチ（約1000行）ごとにメタデータを保存し、クエリ述語に一致しないバッチを除外します。

種類：

• **minmax：**バッチごとの最小値／最大値。数値列や時間列に対する範囲クエリ（>, <, BETWEEN）に使用します

**minmax の使用例：**価格、温度、測定値、タイムスタンプ（範囲フィルタリング）

用途：

• 外れ値の検出には minmax を使用します（temperature > 90）。
• segmentby 列や orderby 列との相関が高いフィールドには minmax を使用します（例：orderby に created_at が含まれる場合、updated_at の minmax が役立ちます）。

**避ける対象：**フィルタリングにほとんど使われない列。

重要：segmentby または orderby の列には、絶対にインデックスを作成しないでください。orderby 列には、設定しなくても常に minmax インデックスが作成されます。

設定： 形式は、type_of_index(column_name) をカンマで区切ったリストです。

ALTER TABLE table_name SET (
    timescaledb.sparse_index = 'minmax(value_1),minmax(value_2)'
);

明示的な設定は v2.22.0 以降で利用できます（v2.16.0 以降は自動作成されていました）。

チャンクの時間間隔（任意）

デフォルト：7日（データ量が不明な場合に使用するか、ユーザーに確認します）。データ量に応じて調整します。

• 高頻度：1時間～1日
• 中頻度：1日～1週間
• 低頻度：1週間～1か月

SELECT set_chunk_time_interval('your_table_name', INTERVAL '1 day');

**目安：**最近のチャンクのインデックスが、RAM の 25% 未満に収まるようにします。

インデックスと主キー

一般的なインデックスパターン — ID とタイムスタンプの複合インデックス：

CREATE INDEX idx_entity_timestamp ON your_table_name (entity_id, timestamp DESC);

**重要：**実際に使用するインデックスのみを作成してください。各インデックスにはメンテナンスのオーバーヘッドが発生します。

**主キーと一意制約のルール：**パーティション列を含める必要があります。

オプション 1：パーティション列を含む複合主キー

ALTER TABLE your_table_name ADD PRIMARY KEY (entity_id, timestamp);

オプション 2：単一列の主キー（パーティション列の場合のみ）

CREATE TABLE ... (id BIGINT PRIMARY KEY, ...) WITH (tsdb.partition_column='id');

オプション 3：主キーなし：挿入中心のパターンでは、厳密な一意性が必要でないことがよくあります。

ステップ 2：圧縮ポリシー（任意）

重要：ステップ 1 で tsdb.enable_columnstore=true を使用した場合、TimescaleDB バージョン 2.23 以降では、after => INTERVAL '7 days' を指定した列ストアポリシーが自動的に作成されます。after 間隔を 7 日以外にカスタマイズする場合にのみ、add_columnstore_policy() を呼び出してください。

データがほぼ不変になり（多少の更新やバックフィルは許容されます）、クエリで B-tree インデックスが不要になった時点に合わせて after 間隔を設定します（後者の条件に該当するケースは比較的まれです）。

-- In TimescaleDB 2.23 and later only needed if you want to override the default 7-day policy created by tsdb.enable_columnstore=true
-- Remove the existing auto-created policy first:
-- CALL remove_columnstore_policy('your_table_name');
-- Then add custom policy:
-- CALL add_columnstore_policy('your_table_name', after => INTERVAL '1 day');

ステップ 3：保持ポリシー

重要：推測せず、ユーザーに確認するか、不明な場合はコメントアウトしてください。

-- Example - replace with requirements or comment out
SELECT add_retention_policy('your_table_name', INTERVAL '365 days');

ステップ 4：継続的集約の作成

用途に応じて集約間隔を使い分けます。

短期（分／時間）

高頻度データのダッシュボードを最新状態に保つために使用します。

CREATE MATERIALIZED VIEW your_table_hourly
WITH (timescaledb.continuous) AS
SELECT
    time_bucket(INTERVAL '1 hour', timestamp) AS bucket,
    entity_id,
    category,
    COUNT(*) as record_count,
    AVG(value_1) as avg_value_1,
    MIN(value_1) as min_value_1,
    MAX(value_1) as max_value_1,
    SUM(value_2) as sum_value_2
FROM your_table_name
GROUP BY bucket, entity_id, category;

長期（日／週／月）

長期的なレポートや分析に使用します。

CREATE MATERIALIZED VIEW your_table_daily
WITH (timescaledb.continuous) AS
SELECT
    time_bucket(INTERVAL '1 day', timestamp) AS bucket,
    entity_id,
    category,
    COUNT(*) as record_count,
    AVG(value_1) as avg_value_1,
    MIN(value_1) as min_value_1,
    MAX(value_1) as max_value_1,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value_1) as median_value_1,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY value_1) as p95_value_1,
    SUM(value_2) as sum_value_2
FROM your_table_name
GROUP BY bucket, entity_id, category;

ステップ 5：集約の更新ポリシー

データの鮮度要件に基づいて更新ポリシーを設定します。

**start_offset：**通常は省略します（すべてを更新します）。例外：X より古いデータを更新する必要がない場合（下記参照）。生データに保持ポリシーを設定している場合は、その保持ポリシーに合わせます。

**end_offset：**アクティブな更新期間より長く設定します（例：通常10分以内にデータが届く場合は15分）。end_offset より新しいデータは、リアルタイム集約を使用しないクエリには表示されません。更新期間がわからない場合は、クエリの time_bucket のサイズを使用します。ただし、5分未満にはしないでください。

**schedule_interval：**end_offset と同じ値に設定します。ただし、1時間を超えないようにしてください。

毎時 — ダッシュボードを頻繁に更新する場合：

SELECT add_continuous_aggregate_policy('your_table_hourly',
    start_offset => NULL,
    end_offset => INTERVAL '15 minutes',
    schedule_interval => INTERVAL '15 minutes');

毎日 — レポートをより低頻度で更新する場合：

SELECT add_continuous_aggregate_policy('your_table_daily',
    start_offset => NULL,
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');

古いデータの更新が不要な場合にのみ start_offset を使用します 古いデータのクエリ精度が重要でない、大量データを扱うシステムで使用します。

-- the following aggregate can be stale for data older than 7 days
-- SELECT add_continuous_aggregate_policy('aggregate_for_last_7_days',
--     start_offset => INTERVAL '7 days',    -- only refresh last 7 days (NULL = refresh all)
--     end_offset => INTERVAL '15 minutes',
--     schedule_interval => INTERVAL '15 minutes');

重要：start_offset は、生データの保持期間より短く設定する必要があります。デフォルトでは、start_offset を保持期間と同じ値に設定します。 保持ポリシーをコメントアウトする場合は、start_offset も同様にコメントアウトしてください。例：

SELECT add_continuous_aggregate_policy('your_table_daily',
    start_offset => NULL,    -- Use NULL to refresh all data, or set to retention period if enabled on raw data
--  start_offset => INTERVAL '<retention period here>',    -- uncomment if retention policy is enabled on the raw data table
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');

ステップ 6：リアルタイム集計（任意）

リアルタイム集計では、クエリの実行時にマテリアライズ済みデータと直近の未加工データを組み合わせます。最新の結果を取得できますが、クエリのレイテンシーは高くなります。

大きなバケットは直近のデータでほとんど埋まらないため、粗い粒度（日次／月次など）より、細かい粒度（分次など）の集計に適しています。

v2.13 以降ではデフォルトで無効です。それ以前はデフォルトで有効でした。

次の場合に有効化： end_offset より新しいデータが必要、分単位で更新されるダッシュボードを使用、クエリのレイテンシー増加を許容できる 次の場合に無効化： パフォーマンスが重要、リフレッシュポリシーで十分、クエリ数が多い、直近のデータが欠落または古くても問題ない

最新の結果を取得する（クエリコストは高くなります）：

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

パフォーマンスを優先する（結果は古くなります）：

ALTER MATERIALIZED VIEW your_table_hourly SET (timescaledb.materialized_only = true);

ステップ 7：集計データを圧縮する

ルール：segment_by = time_bucket 以外のすべての GROUP BY 列、order_by = time_bucket DESC

-- Hourly
ALTER MATERIALIZED VIEW your_table_hourly SET (
    timescaledb.enable_columnstore,
    timescaledb.segmentby = 'entity_id, category',
    timescaledb.orderby = 'bucket DESC'
);
CALL add_columnstore_policy('your_table_hourly', after => INTERVAL '3 days');

-- Daily
ALTER MATERIALIZED VIEW your_table_daily SET (
    timescaledb.enable_columnstore,
    timescaledb.segmentby = 'entity_id, category',
    timescaledb.orderby = 'bucket DESC'
);
CALL add_columnstore_policy('your_table_daily', after => INTERVAL '7 days');

ステップ 8：集計データの保持期間

通常、集計データは未加工データより長く保持します。 IMPORTANT：推測しないでください。ユーザーに確認するか、不明な場合は必ずコメントアウトしてください。

-- Example - replace or comment out
SELECT add_retention_policy('your_table_hourly', INTERVAL '2 years');
SELECT add_retention_policy('your_table_daily', INTERVAL '5 years');

ステップ 9：継続的集計のパフォーマンスインデックス

インデックス戦略： 一般的なクエリの WHERE 句を分析し、フィルター列と時間順序に一致するインデックスを作成します。

パターン： (filter_column, bucket DESC) は、WHERE filter_column = X AND bucket >= Y ORDER BY bucket DESC をサポートします。

例：

CREATE INDEX idx_hourly_entity_bucket ON your_table_hourly (entity_id, bucket DESC);
CREATE INDEX idx_hourly_category_bucket ON your_table_hourly (category, bucket DESC);

複数列のフィルター： WHERE entity_id = X AND category = Y に対して複合インデックスを作成します。

CREATE INDEX idx_hourly_entity_category_bucket ON your_table_hourly (entity_id, category, bucket DESC);

重要： 実際に使用するインデックスのみを作成してください。それぞれにメンテナンスコストがかかります。

ステップ 10：任意の拡張

空間パーティショニング（推奨しません）

空間パーティション列で常にフィルターするクエリパターンの場合に限り、専門知識と十分なベンチマークに基づいて使用してください。時間のみのパーティショニングを強く推奨します。

ステップ 11：構成を確認する

-- Check hypertable
SELECT * FROM timescaledb_information.hypertables
WHERE hypertable_name = 'your_table_name';

-- Check compression settings
SELECT * FROM hypertable_compression_stats('your_table_name');

-- Check aggregates
SELECT * FROM timescaledb_information.continuous_aggregates;

-- Check policies
SELECT * FROM timescaledb_information.jobs ORDER BY job_id;

-- Monitor chunk information
SELECT
    chunk_name,
    range_start,
    range_end,
    is_compressed
FROM timescaledb_information.chunks
WHERE hypertable_name = 'your_table_name'
ORDER BY range_start DESC;

パフォーマンスのガイドライン

• チャンクサイズ： 直近のチャンクのインデックスは、RAM の 25% 未満に収まるようにします
• 圧縮： 適切なカラムストア構成では、90% 以上（10 倍）の削減を見込めます
• クエリの最適化： 履歴クエリやダッシュボードには継続的集計を使用します
• メモリ： セルフホストの場合は timescaledb-tune を実行します（クラウドでは自動構成されます）

スキーマのベストプラクティス

推奨事項と禁止事項

• ✅ timestamp ではなく TIMESTAMPTZ を使用します
• ✅ タイムスタンプには BETWEEN ではなく >= と < を使用します
• ✅ char(n)／varchar(n) ではなく、制約付きの TEXT を使用します
• ✅ CamelCase ではなく snake_case を使用します
• ✅ SERIAL ではなく BIGINT GENERATED ALWAYS AS IDENTITY を使用します
• ✅ ID には、デフォルトで INTEGER や SMALLINT ではなく BIGINT を使用します
• ✅ デフォルトでは REAL／FLOAT ではなく DOUBLE PRECISION を使用します
• ✅ MONEY ではなく NUMERIC を使用します
• ✅ NOT IN ではなく NOT EXISTS を使用します
• ✅ 切り捨てには timestamp(0) ではなく time_bucket() または date_trunc() を使用します

API リファレンス（現行と非推奨）

非推奨のパラメーター → 新しいパラメーター：

• timescaledb.compress → timescaledb.enable_columnstore
• timescaledb.compress_segmentby → timescaledb.segmentby
• timescaledb.compress_orderby → timescaledb.orderby

非推奨の関数 → 新しい関数：

• add_compression_policy() → add_columnstore_policy()
• remove_compression_policy() → remove_columnstore_policy()
• compress_chunk() → convert_to_columnstore()（SELECT ではなく CALL と併用）
• decompress_chunk() → convert_to_rowstore()（SELECT ではなく CALL と併用）

圧縮統計（ビューではなく関数を使用）：

• 関数を使用：hypertable_compression_stats('table_name')
• 関数を使用：chunk_compression_stats('_timescaledb_internal._hyper_X_Y_chunk')
• 注：columnstore_settings などのビューは、すべてのバージョンで利用できるとは限りません。代わりに関数を使用してください

手動圧縮の例：

-- Compress a specific chunk
CALL convert_to_columnstore('_timescaledb_internal._hyper_7_1_chunk');

-- Check compression statistics
SELECT
    number_compressed_chunks,
    pg_size_pretty(before_compression_total_bytes) as before_compression,
    pg_size_pretty(after_compression_total_bytes) as after_compression,
    ROUND(100.0 * (1 - after_compression_total_bytes::numeric / NULLIF(before_compression_total_bytes, 0)), 1) as compression_pct
FROM hypertable_compression_stats('your_table_name');

ユーザーに確認する質問

1. どのような種類のデータを保存しますか？
2. そのデータをどのように利用する予定ですか？
3. どのようなクエリを実行しますか？
4. データをどれくらいの期間保持しますか？
5. 不明な場合は、列の型を確認してください