find-hypertable-candidates

PostgreSQL ハイパーテーブル候補の分析

TimescaleDB のハイパーテーブルへの変換によってメリットが得られるテーブルを特定します。特定した後、設定と移行には関連する「migrate-postgres-tables-to-hypertables」スキルを使用してください。

TimescaleDB のメリット

パフォーマンスの向上： 90% 以上の圧縮、時間ベースのクエリの高速化、挿入パフォーマンスの改善、効率的な集計、マテリアライズドビューによる継続的集計（ダッシュボード、レポート、分析）、自動データ管理（保持、圧縮）。

挿入中心のパターンに最適：

• 時系列データ（センサー、メトリクス、モニタリング）
• イベントログ（ユーザーイベント、監査証跡、アプリケーションログ）
• トランザクションレコード（注文、支払い、金融）
• 連続データ（タイムスタンプ付きの自動インクリメント ID）
• 追記専用データセット（不変レコード、履歴データ）

要件： 大量のデータ（100 万行以上）、時間ベースのクエリ、更新頻度が低いこと

ステップ 1：データベーススキーマの分析

オプション A：データベース接続から分析

テーブルの統計とサイズ

-- Get all tables with row counts and insert/update patterns
WITH table_stats AS (
    SELECT
        schemaname, tablename,
        n_tup_ins as total_inserts,
        n_tup_upd as total_updates,
        n_tup_del as total_deletes,
        n_live_tup as live_rows,
        n_dead_tup as dead_rows
    FROM pg_stat_user_tables
),
table_sizes AS (
    SELECT
        schemaname, tablename,
        pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,
        pg_total_relation_size(schemaname||'.'||tablename) as total_size_bytes
    FROM pg_tables
    WHERE schemaname NOT IN ('information_schema', 'pg_catalog')
)
SELECT
    ts.schemaname, ts.tablename, ts.live_rows,
    tsize.total_size, tsize.total_size_bytes,
    ts.total_inserts, ts.total_updates, ts.total_deletes,
    ROUND(CASE WHEN ts.live_rows > 0
          THEN (ts.total_inserts::float / ts.live_rows) * 100
          ELSE 0 END, 2) as insert_ratio_pct
FROM table_stats ts
JOIN table_sizes tsize ON ts.schemaname = tsize.schemaname AND ts.tablename = tsize.tablename
ORDER BY tsize.total_size_bytes DESC;

確認する項目：

• 主に挿入中心のパターン（更新や削除が少ない）
• 大きなテーブル（100 万行以上、または 100MB 以上）

インデックスのパターン

-- Identify common query dimensions
SELECT schemaname, tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname NOT IN ('information_schema', 'pg_catalog')
ORDER BY tablename, indexname;

確認する項目：

• timestamp／created_at 列を含む複数のインデックス → 時間ベースのクエリ
• 複合（entity_id、timestamp）インデックス → 有望な候補
• 時刻のみのインデックス → 時間範囲での絞り込みが一般的

クエリのパターン（pg_stat_statements が利用可能な場合）

-- Check availability
SELECT EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pg_stat_statements');

-- Analyze expensive queries for candidate tables
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
WHERE query ILIKE '%your_table_name%'
ORDER BY total_exec_time DESC LIMIT 20;

✅ 適したパターン： 時間ベースの WHERE、時間ベースの条件と組み合わせたエンティティの絞り込み、GROUP BY time_bucket、時間範囲を対象とするクエリ ❌ 適さないパターン： 同じクエリ内に時間ベースの条件を含まない、時間以外の検索（WHERE email = ...）

制約

-- Check migration compatibility
SELECT conname, contype, pg_get_constraintdef(oid) as definition
FROM pg_constraint
WHERE conrelid = 'your_table_name'::regclass;

互換性：

• 主キー（p）：パーティション列を含める必要があります。変更可能かどうかをユーザーに確認してください
• 外部キー（f）：通常テーブル→ハイパーテーブル、ハイパーテーブル→通常テーブルは可能ですが、ハイパーテーブル→ハイパーテーブルはサポートされません
• 一意制約（u）：パーティション列を含める必要があります。変更可能かどうかをユーザーに確認してください
• チェック制約（c）：通常は問題ありません

オプション B：コード分析から確認

✅ 適したパターン

# Append-only logging
INSERT INTO events (user_id, event_time, data) VALUES (...);
# Time-series collection
INSERT INTO metrics (device_id, timestamp, value) VALUES (...);
# Time-based queries
SELECT * FROM metrics WHERE timestamp >= NOW() - INTERVAL '24 hours';
# Time aggregations
SELECT DATE_TRUNC('day', timestamp), COUNT(*) GROUP BY 1;

❌ 適さないパターン

# Frequent updates to historical records
UPDATE users SET email = ..., updated_at = NOW() WHERE id = ...;
# Non-time lookups
SELECT * FROM users WHERE email = ...;
# Small reference tables
SELECT * FROM countries ORDER BY name;

スキーマの指標

✅ 適している場合：

• timestamp／timestamptz 列がある
• timestamp ベースの列を含むインデックスが複数ある
• 複合（entity_id、timestamp）インデックスがある

❌ 適していない場合：

• 時間ベースでない列（email、name、status など）を対象とするインデックスが大半を占める
• 時間の経過とともに更新されると想定される列（updated_at、updated_by、status など）がある
• 時間以外のフィールドに一意制約がある
• updated_at が頻繁に変更される
• 小規模で静的なテーブル

特殊ケース：ID ベースのテーブル

連続 ID のテーブルは、次の条件に当てはまる場合、候補になる可能性があります。

• 挿入中心のパターンである、または更新頻度が低いか、直近のレコードのみが更新される。
• 更新が発生する場合、直近のレコードが対象である（たとえば、注文ステータスが ordered->processing->delivered のように更新されるケースです。注文が配達済みになった後に再び更新される可能性は低いことに注意してください。）
• ID が時間と相関している（serial／自動インクリメント ID／GENERATED ALWAYS AS IDENTITY の場合など）
• ID が主なクエリ対象である
• 新しいデータほど頻繁にアクセスされる（eコマース、金融などではよく見られます）
• 時間ベースのレポートが一般的である（例：月次・日次の集計／分析）

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,           -- Can partition by ID
    user_id BIGINT,
    created_at TIMESTAMPTZ DEFAULT NOW() -- For sparse indexes
);

注：時間列（created_at、ordered_at など）もある ID ベースのテーブルでは、ID でパーティションを作成し、時間列に疎インデックスを使用できます。 詳しくは migrate-postgres-tables-to-hypertables スキルを参照してください。

ステップ 2：候補のスコアリング（8 点以上なら有望な候補）

時系列の特性（5 点以上が必要）

• timestamp／timestamptz 列がある：3 点
• データが時系列順に挿入される：2 点
• クエリで時間による絞り込みを行う：2 点
• 時間集計が一般的である：2 点

規模とパフォーマンス (3 点以上を推奨)

• 大きなテーブル (100 万行以上または 100MB 以上): 2 点
• 挿入量が多い: 1 点
• 履歴データの更新がまれ: 1 点
• 範囲クエリが一般的: 1 点
• 集計クエリ: 2 点

データパターン (ボーナス)

• セグメンテーション用のエンティティ ID を含む (device_id、user_id、product_id、symbol など): 1 点
• 数値測定値: 1 点
• ログ/イベント構造: 1 点

一般的なパターン

✅ 適した候補

✅ イベント/ログテーブル (user_events、audit_logs)

CREATE TABLE user_events (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT,
    event_type TEXT,
    event_time TIMESTAMPTZ DEFAULT NOW(),
    metadata JSONB
);
-- Partition by id, segment by user_id, enable minmax sparse_index on event_time

✅ センサー/IoT データ (sensor_readings、telemetry)

CREATE TABLE sensor_readings (
    device_id TEXT,
    timestamp TIMESTAMPTZ,
    temperature DOUBLE PRECISION,
    humidity DOUBLE PRECISION
);
-- Partition by timestamp, segment by device_id, minmax sparse indexes on temperature and humidity

✅ 金融/取引 (stock_prices、transactions)

CREATE TABLE stock_prices (
    symbol VARCHAR(10),
    price_time TIMESTAMPTZ,
    open_price DECIMAL,
    close_price DECIMAL,
    volume BIGINT
);
-- Partition by price_time, segment by symbol, minmax sparse indexes on open_price and close_price and volume

✅ システムメトリクス (monitoring_data)

CREATE TABLE system_metrics (
    hostname TEXT,
    metric_time TIMESTAMPTZ,
    cpu_usage DOUBLE PRECISION,
    memory_usage BIGINT
);
-- Partition by metric_time, segment by hostname, minmax sparse indexes on cpu_usage and memory_usage

❌ 適さない候補

❌ 参照テーブル (countries、categories)

CREATE TABLE countries (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    code CHAR(2)
);
-- Static data, no time component

❌ ユーザープロファイル (users、accounts)

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255),
    created_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ
);
-- Accessed by ID, frequently updated, has timestamp but it's not the primary query dimension (the primary query dimension is id or email)

❌ 設定/構成 (user_settings)

CREATE TABLE user_settings (
    user_id BIGINT PRIMARY KEY,
    theme VARCHAR(20),       -- Changes: light -> dark -> auto
    language VARCHAR(10),    -- Changes: en -> es -> fr
    notifications JSONB,     -- Frequent preference updates
    updated_at TIMESTAMPTZ
);
-- Accessed by user_id, frequently updated, has timestamp but it's not the primary query dimension (the primary query dimension is user_id)

分析出力の要件

各候補テーブルについて以下を提供してください:

• スコア: 基準に基づく (8 以上 = 有力な候補)
• パターン: 挿入と更新の比率
• アクセス: 時間ベースかエンティティ検索か
• サイズ: 現在のサイズと増加率
• クエリ: 時間範囲、集計、ポイント検索

挿入が多いパターンで、時間ベースまたはシーケンシャルアクセスに注目してください。8 点以上のスコアのテーブルは変換の有力な候補です。