migrate-postgres-tables-to-hypertables

PostgreSQL から TimescaleDB ハイパーテーブルへの移行

最適な設定、移行計画、検証を行い、特定した PostgreSQL テーブルを TimescaleDB ハイパーテーブルに移行します。

前提条件：ハイパーテーブルの候補となるテーブルをすでに特定していること（必要に応じて、関連する「find-hypertable-candidates」スキルを使用してください）。

ステップ 1：最適な設定

パーティション列の選択

-- Find potential partition columns
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'your_table_name'
  AND data_type IN ('timestamp', 'timestamptz', 'bigint', 'integer', 'date')
ORDER BY ordinal_position;

要件： 時刻ベース（TIMESTAMP/TIMESTAMPTZ/DATE）または連番整数（INT/BIGINT）

イベントが実際に発生した時刻、または連続した順序を表す必要があります。

一般的な選択肢：

• timestamp、created_at、event_time - イベントが発生した時刻
• id、sequence_number - 自動インクリメント（タイムスタンプのない連番データ向け）
• ingested_at - あまり理想的ではありません。主なクエリ対象のディメンションである場合に限ります
• updated_at - 原則として避けてください（レコードが順不同で更新され、チャンクの分散が損なわれます）。主なクエリ対象のディメンションである場合を除きます

特殊なケース：ID とタイムスタンプの両方があるテーブル

連番 ID（PK）と、それに相関するタイムスタンプがある場合：

-- Partition by ID, enable minmax sparse indexes on timestamp
SELECT create_hypertable('orders', 'id', chunk_time_interval => 1000000);
ALTER TABLE orders SET (
    timescaledb.sparse_index = 'minmax(created_at),...'
);

タイム列にスパースインデックスを設定すると、クエリ対象の時間範囲外にある圧縮ブロックをスキップできます。

次の場合に使用します：ID と時刻に相関がある（新しいレコードほど ID が大きい）、ID ベースの検索が必要、時刻によるクエリもよく行う

チャンク間隔の選択

-- Ensure statistics are current
ANALYZE your_table_name;

-- Estimate index size per time unit
WITH time_range AS (
    SELECT
        MIN(timestamp_column) as min_time,
        MAX(timestamp_column) as max_time,
        EXTRACT(EPOCH FROM (MAX(timestamp_column) - MIN(timestamp_column)))/3600 as total_hours
    FROM your_table_name
),
total_index_size AS (
    SELECT SUM(pg_relation_size(indexname::regclass)) as total_index_bytes
    FROM pg_stat_user_indexes
    WHERE schemaname||'.'||tablename = 'your_schema.your_table_name'
)
SELECT
    pg_size_pretty(tis.total_index_bytes / tr.total_hours) as index_size_per_hour
FROM time_range tr, total_index_size tis;

目標： 最新のチャンクのインデックスサイズを RAM の 25% 未満にする デフォルト： 重要：判断に迷う場合は、デフォルトの 7 日を維持してください 範囲： 最小 1 時間、最大 30 日

例： RAM が 32GB の場合、最新のインデックスの目標は 8GB です。index_size_per_hour が 200MB の場合：

• 1 時間チャンク：チャンクあたりのインデックスサイズ 200MB × 最新の 40 チャンク = 8GB ✓
• 6 時間チャンク：チャンクあたりのインデックスサイズ 1.2GB × 最新の 7 チャンク = 8.4GB ✓
• 1 日チャンク：チャンクあたりのインデックスサイズ 4.8GB × 最新の 2 チャンク = 9.6GB ⚠️ 最新のチャンクインデックスが 2 個以上あり、目標値以下に収まる最大の間隔を選択してください。

主キー／一意制約との互換性

-- Check existing primary key/ unique constraints
SELECT conname, pg_get_constraintdef(oid) as definition
FROM pg_constraint
WHERE conrelid = 'your_table_name'::regclass AND contype = 'p' OR contype = 'u';

ルール： PK／UNIQUE にはパーティション列を含める必要があります

対応：

1. PK／UNIQUE がない： 変更は不要です
2. PK／UNIQUE にパーティション列が含まれている： 変更は不要です
3. PK／UNIQUE にパーティション列が含まれていない： ⚠️ PK／UNIQUE の変更についてユーザーの許可を求めてください

必要な場合のユーザープロンプト例：

「主キー（id）にパーティション列（timestamp）が含まれていません。ハイパーテーブルに変換するには、PRIMARY KEY (id, timestamp) に変更する必要があります。アプリケーションコードに影響する可能性があります。問題ありませんか？」 「一意制約（id）にパーティション列（timestamp）が含まれていません。ハイパーテーブルに変換するには、UNIQUE (id, timestamp) に変更する必要があります。アプリケーションコードに影響する可能性があります。問題ありませんか？」

ユーザーが同意した場合は、制約を変更します：

BEGIN;
ALTER TABLE your_table_name DROP CONSTRAINT existing_pk_name;
ALTER TABLE your_table_name ADD PRIMARY KEY (existing_columns, partition_column);
COMMIT;

ユーザーが同意しない場合は、そのテーブルを移行してはいけません。

重要：ユーザーの許可なく主キー／一意制約を変更してはいけません。

圧縮設定

segment_by と order_by の選択について詳しくは、「setup-timescaledb-hypertables」スキルを参照してください。クイックリファレンス：

segment_by： 値ごとにチャンクあたり 100 行を超える、最も一般的な WHERE フィルター

• IoT：device_id
• 金融：symbol
• 分析：user_id または session_id

-- Analyze cardinality for segment_by selection
SELECT column_name, COUNT(DISTINCT column_name) as unique_values,
       ROUND(COUNT(*)::float / COUNT(DISTINCT column_name), 2) as avg_rows_per_value
FROM your_table_name GROUP BY column_name;

order_by： 通常は timestamp DESC です。（segment_by, order_by）の組み合わせは、自然な時系列の順序を形成する必要があります。

• 列の行数がチャンクあたり 100 未満で（segment_by に適さない場合）、order_by の先頭に追加します：order_by='low_density_col, timestamp DESC'

スパースインデックス： WHERE 句で使用される列のうち、segment_by または order_by に含まれない列に minmax を追加します。範囲クエリで使用する列には minmax を使用してください。

ALTER TABLE your_table_name SET (
    timescaledb.enable_columnstore,
    timescaledb.segmentby = 'entity_id',
    timescaledb.orderby = 'timestamp DESC'
    timescaledb.sparse_index = 'minmax(value_1),...'
);

-- Compress after data unlikely to change (adjust `after` parameter based on update patterns)
CALL add_columnstore_policy('your_table_name', after => INTERVAL '7 days');

ステップ 2：移行計画

移行前のチェックリスト

• パーティション列を選択済み
• チャンク間隔を計算済み（またはデフォルトを使用）
• PK にパーティション列が含まれている、または変更についてユーザーの承認済み
• ハイパーテーブル間の外部キーがない
• 一意制約にパーティション列が含まれている
• 圧縮設定を作成済み（segment_by、order_by、スパースインデックス、圧縮ポリシー）
• メンテナンス時間を設定済み／バックアップを作成済み

移行オプション

オプション 1：インプレース（1GB 未満のテーブル）

-- Enable extension
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Convert to hypertable (locks table)
SELECT create_hypertable(
    'your_table_name',
    'timestamp_column',
    chunk_time_interval => INTERVAL '7 days',
    if_not_exists => TRUE
);

-- Configure compression
ALTER TABLE your_table_name SET (
    timescaledb.enable_columnstore,
    timescaledb.segmentby = 'entity_id',
    timescaledb.orderby = 'timestamp DESC',
    timescaledb.sparse_index = 'minmax(value_1),...'
);

-- Adjust `after` parameter based on update patterns
CALL add_columnstore_policy('your_table_name', after => INTERVAL '7 days');

オプション 2：ブルーグリーン（テーブル > 1GB）

-- 1. Create new hypertable
CREATE TABLE your_table_name_new (LIKE your_table_name INCLUDING ALL);

-- 2. Convert to hypertable
SELECT create_hypertable('your_table_name_new', 'timestamp_column');

-- 3. Configure compression
ALTER TABLE your_table_name_new SET (
    timescaledb.enable_columnstore,
    timescaledb.segmentby = 'entity_id',
    timescaledb.orderby = 'timestamp DESC'
);

-- 4. Migrate data in batches
INSERT INTO your_table_name_new
SELECT * FROM your_table_name
WHERE timestamp_column >= '2024-01-01' AND timestamp_column < '2024-02-01';
-- Repeat for each time range

-- 4. Enter maintenance window and do the following:

-- 5. Pause modification of the old table.

-- 6. Copy over the most recent data from the old table to the new table.

-- 7. Swap tables
BEGIN;
ALTER TABLE your_table_name RENAME TO your_table_name_old;
ALTER TABLE your_table_name_new RENAME TO your_table_name;
COMMIT;

-- 8. Exit maintenance window.

-- 9. (sometime much later) Drop old table after validation
-- DROP TABLE your_table_name_old;

よくある問題

外部キー

-- Check foreign keys
SELECT conname, confrelid::regclass as referenced_table
FROM pg_constraint
WHERE (conrelid = 'your_table_name'::regclass
    OR confrelid = 'your_table_name'::regclass)
  AND contype = 'f';

サポート対象： プレーンテーブル→ハイパーテーブル、ハイパーテーブル→プレーンテーブル サポート対象外： ハイパーテーブル→ハイパーテーブル

⚠️ 重大： ハイパーテーブル→ハイパーテーブルの外部キーは削除する必要があります（アプリケーション側で適用してください）。ユーザーの許可を求めてください。許可されない場合は、移行を中止してください。

大規模テーブルの移行時間

-- Rough estimate: ~75k rows/second
SELECT
    pg_size_pretty(pg_total_relation_size(tablename)) as size,
    n_live_tup as rows,
    ROUND(n_live_tup / 75000.0 / 60, 1) as estimated_minutes
FROM pg_stat_user_tables
WHERE tablename = 'your_table_name';

大規模テーブル（>1GB／1,000 万行）の解決策： ブルーグリーン移行を使用する、オフピーク時に移行する、まずサブセットでテストする

ステップ 3：パフォーマンスの検証

チャンクと圧縮の分析

-- View chunks and compression
SELECT
    chunk_name,
    pg_size_pretty(total_bytes) as size,
    pg_size_pretty(compressed_total_bytes) as compressed_size,
    ROUND((total_bytes - compressed_total_bytes::numeric) / total_bytes * 100, 1) as compression_pct,
    range_start,
    range_end
FROM timescaledb_information.chunks
WHERE hypertable_name = 'your_table_name'
ORDER BY range_start DESC;

確認項目：

• チャンクサイズが一定している（差が 2 倍以内）
• 時系列データの圧縮率が 90% 超
• 最近のチャンクは未圧縮
• チャンクインデックスが RAM の 25% 未満

クエリパフォーマンステスト

-- 1. Time-range query (should show chunk exclusion)
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*), AVG(value)
FROM your_table_name
WHERE timestamp >= NOW() - INTERVAL '1 day';

-- 2. Entity + time query (benefits from segment_by)
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM your_table_name
WHERE entity_id = 'X' AND timestamp >= NOW() - INTERVAL '1 week';

-- 3. Aggregation (benefits from columnstore)
EXPLAIN (ANALYZE, BUFFERS)
SELECT DATE_TRUNC('hour', timestamp), entity_id, COUNT(*), AVG(value)
FROM your_table_name
WHERE timestamp >= NOW() - INTERVAL '1 month'
GROUP BY 1, 2;

✅ 良い兆候：

• EXPLAIN プランに「起動時に除外されたチャンク：X」と表示される
• 圧縮データに対して「Custom Scan (ColumnarScan)」が表示される
• EXPLAIN ANALYZE プランの「Buffers: shared read」が移行前より少ない
• 実行時間が短縮されている

❌ 悪い兆候：

• 大きなチャンクに対する「Seq Scan」
• チャンク除外に関するメッセージがない
• 移行前より遅い

ストレージメトリクス

-- Monitor compression effectiveness
SELECT
    hypertable_name,
    pg_size_pretty(total_bytes) as total_size,
    pg_size_pretty(compressed_total_bytes) as compressed_size,
    ROUND(compressed_total_bytes::numeric / total_bytes * 100, 1) as compressed_pct_of_total,
    ROUND((uncompressed_total_bytes - compressed_total_bytes::numeric) /
          uncompressed_total_bytes * 100, 1) as compression_ratio_pct
FROM timescaledb_information.hypertables
WHERE hypertable_name = 'your_table_name';

監視項目：

• compression_ratio_pct が 90% 超（一般的な時系列データの場合）
• データの経過に伴い compressed_pct_of_total が増加している
• ハイパーテーブル化前と比べてサイズの増加が大幅に鈍化している
• compression_ratio_pct の低下は、segment_by の設定不良を示します

トラブルシューティング

チャンク除外が不十分

-- Verify chunks are being excluded
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM your_table_name
WHERE timestamp >= '2024-01-01' AND timestamp < '2024-01-02';
-- Look for "Chunks excluded during startup: X"

圧縮が不十分

-- Get newest compressed chunk name
SELECT chunk_name FROM timescaledb_information.chunks
WHERE hypertable_name = 'your_table_name'
  AND compressed_total_bytes IS NOT NULL
ORDER BY range_start DESC LIMIT 1;

-- Analyze segment distribution
SELECT segment_by_column, COUNT(*) as rows_per_segment
FROM _timescaledb_internal._hyper_X_Y_chunk  -- Use actual chunk name
GROUP BY 1 ORDER BY 2 DESC;

確認項目： セグメントあたりの行数が 20 未満の場合は、segment_by の選択が不適切です（100 行超が望ましい）。そのため、圧縮の可能性が低くなります。

挿入パフォーマンスの低下

インデックスが多すぎないことを確認してください。未使用のインデックスは挿入パフォーマンスを低下させるため、削除してください。

SELECT
    schemaname,
    tablename,
    indexname,
    idx_tup_read,
    idx_tup_fetch,
    idx_scan
FROM pg_stat_user_indexes
WHERE tablename LIKE '%your_table_name%'
ORDER BY idx_scan DESC;

確認項目： idx_scan の値が低い場合、未使用のインデックスがあります。そのようなインデックスを削除してください（ただし、ユーザーの許可を求めてください）。

継続的な監視

-- Monitor chunk compression status
CREATE OR REPLACE VIEW hypertable_compression_status AS
SELECT
    h.hypertable_name,
    COUNT(c.chunk_name) as total_chunks,
    COUNT(c.chunk_name) FILTER (WHERE c.compressed_total_bytes IS NOT NULL) as compressed_chunks,
    ROUND(
        COUNT(c.chunk_name) FILTER (WHERE c.compressed_total_bytes IS NOT NULL)::numeric /
        COUNT(c.chunk_name) * 100, 1
    ) as compression_coverage_pct,
    pg_size_pretty(SUM(c.total_bytes)) as total_size,
    pg_size_pretty(SUM(c.compressed_total_bytes)) as compressed_size
FROM timescaledb_information.hypertables h
LEFT JOIN timescaledb_information.chunks c ON h.hypertable_name = c.hypertable_name
GROUP BY h.hypertable_name;

-- Query this view regularly to monitor compression progress
SELECT * FROM hypertable_compression_status
WHERE hypertable_name = 'your_table_name';

確認項目：

• データの経過に伴って圧縮が進むため、compression_coverage_pct は時間とともに増加するはずです。
• total_chunks が急増しないこと（10,000 を超えると問題になる可能性があります）。
• total_size または compressed_size に予期しない急増がないこと。

成功基準

✅ 次の条件を満たせば移行は成功です：

• すべてのクエリが正しい結果を返す
• クエリパフォーマンスが同等以上
• 古いデータの圧縮率が 90% 超
• 時間ベースのクエリでチャンク除外が機能している
• 挿入パフォーマンスが許容範囲内

❌ 次の場合は調査してください：

• クエリパフォーマンスが 20% 超低下
• 圧縮率が 80% 未満
• チャンク除外が行われない
• 挿入パフォーマンスが低下
• エラー率が上昇

投資対効果を最大化するには、時間ベースのアクセスパターンを持つ、大量のデータを扱う挿入中心のワークロードに注力してください。