PostgreSQL パーティショニング本番運用ガイド：10億行テーブルのための Range・List・Hash 戦略

-- 10億行イベントテーブルの月別 Range パーティショニング
CREATE TABLE events (
    id              BIGSERIAL,
    event_time      TIMESTAMPTZ NOT NULL,
    user_id         BIGINT NOT NULL,
    event_type      TEXT NOT NULL,
    payload         JSONB,
    PRIMARY KEY (id, event_time)
) PARTITION BY RANGE (event_time);

-- 月別パーティションの作成
CREATE TABLE events_2026_01 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE events_2026_02 PARTITION OF events
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

CREATE TABLE events_2026_03 PARTITION OF events
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- 各パーティションにインデックスを作成（PG 11以降は自動継承）
CREATE INDEX idx_events_user_id ON events (user_id);
CREATE INDEX idx_events_event_type ON events (event_type);

-- マルチリージョン注文テーブルのリージョン別 List パーティショニング
CREATE TABLE orders (
    id              BIGSERIAL,
    region          TEXT NOT NULL,
    order_date      TIMESTAMPTZ NOT NULL,
    customer_id     BIGINT NOT NULL,
    total_amount    NUMERIC(12,2),
    status          TEXT NOT NULL,
    PRIMARY KEY (id, region)
) PARTITION BY LIST (region);

CREATE TABLE orders_us PARTITION OF orders
    FOR VALUES IN ('us-east-1', 'us-west-2', 'us-central-1');

CREATE TABLE orders_eu PARTITION OF orders
    FOR VALUES IN ('eu-west-1', 'eu-central-1', 'eu-north-1');

CREATE TABLE orders_ap PARTITION OF orders
    FOR VALUES IN ('ap-northeast-1', 'ap-southeast-1', 'ap-south-1');

-- マッピングされていないリージョン用のデフォルトパーティション
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

-- 高スループットセッションストアの Hash パーティショニング
CREATE TABLE sessions (
    session_id      UUID NOT NULL,
    user_id         BIGINT NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_active     TIMESTAMPTZ NOT NULL DEFAULT now(),
    session_data    JSONB,
    PRIMARY KEY (session_id)
) PARTITION BY HASH (session_id);

-- 8つのハッシュパーティションを作成（均等分散のため2のべき乗を推奨）
CREATE TABLE sessions_p0 PARTITION OF sessions FOR VALUES WITH (MODULUS 8, REMAINDER 0);
CREATE TABLE sessions_p1 PARTITION OF sessions FOR VALUES WITH (MODULUS 8, REMAINDER 1);
CREATE TABLE sessions_p2 PARTITION OF sessions FOR VALUES WITH (MODULUS 8, REMAINDER 2);
CREATE TABLE sessions_p3 PARTITION OF sessions FOR VALUES WITH (MODULUS 8, REMAINDER 3);
CREATE TABLE sessions_p4 PARTITION OF sessions FOR VALUES WITH (MODULUS 8, REMAINDER 4);
CREATE TABLE sessions_p5 PARTITION OF sessions FOR VALUES WITH (MODULUS 8, REMAINDER 5);
CREATE TABLE sessions_p6 PARTITION OF sessions FOR VALUES WITH (MODULUS 8, REMAINDER 6);
CREATE TABLE sessions_p7 PARTITION OF sessions FOR VALUES WITH (MODULUS 8, REMAINDER 7);

-- パーティションプルーニングが有効か確認
SHOW enable_partition_pruning;  -- 'on' であること

-- EXPLAIN でプルーニングを検証
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM events
WHERE event_time >= '2026-03-01' AND event_time < '2026-03-15';

-- 期待される出力：3月のパーティションのみスキャン
--   Append (actual rows=1523400 loops=1)
--     -> Seq Scan on events_2026_03 (actual rows=1523400 loops=1)
--           Filter: (event_time >= '2026-03-01' AND event_time < '2026-03-15')
--   Planning Time: 0.452 ms
--   Execution Time: 892.31 ms

-- プリペアドステートメントでのランタイムプルーニング
PREPARE get_events(timestamptz, timestamptz) AS
SELECT * FROM events WHERE event_time >= $1 AND event_time < $2;

-- 実行時にプルーニングが行われる（プランニング時ではなく）
EXECUTE get_events('2026-03-01', '2026-03-08');

# pg_partman のインストール（Debian/Ubuntu の場合）
sudo apt-get install postgresql-17-partman

# またはソースからコンパイル
git clone https://github.com/pgpartman/pg_partman.git
cd pg_partman
make && sudo make install

-- 拡張機能を有効化
CREATE SCHEMA IF NOT EXISTS partman;
CREATE EXTENSION pg_partman SCHEMA partman;

-- まず親テーブルを作成（パーティション化済みであること）
CREATE TABLE metrics (
    id              BIGSERIAL,
    recorded_at     TIMESTAMPTZ NOT NULL,
    metric_name     TEXT NOT NULL,
    metric_value    DOUBLE PRECISION NOT NULL,
    labels          JSONB,
    PRIMARY KEY (id, recorded_at)
) PARTITION BY RANGE (recorded_at);

-- pg_partman に登録して自動管理を開始
SELECT partman.create_parent(
    p_parent_table   => 'public.metrics',
    p_control        => 'recorded_at',
    p_interval       => '1 day',
    p_premake        => 7,           -- 将来のパーティションを7つ事前作成
    p_start_partition => '2026-01-01'
);

-- 保持ポリシーの設定
UPDATE partman.part_config
SET retention = '90 days',
    retention_keep_table = false,    -- 古いパーティションを DROP（デタッチだけでなく）
    infinite_time_partitions = true  -- 将来のパーティションを常に作成
WHERE parent_table = 'public.metrics';

-- すべての管理対象テーブルのメンテナンスを実行（pg_cron または crontab で呼び出し）
SELECT partman.run_maintenance();

-- または特定のテーブルのみ
SELECT partman.run_maintenance('public.metrics');

# pg_cron でスケジューリング（推奨）
SELECT cron.schedule('partman-maintenance', '*/15 * * * *',
    $$SELECT partman.run_maintenance()$$);

# またはシステムの crontab で
echo "*/15 * * * * psql -d mydb -c \"SELECT partman.run_maintenance()\"" | crontab -

-- 新しいパーティションテーブルを作成（別名で）
CREATE TABLE events_partitioned (
    LIKE events INCLUDING ALL
) PARTITION BY RANGE (event_time);

-- データ範囲全体をカバーするパーティションを作成
-- （pg_partman または手動の CREATE TABLE ... PARTITION OF を使用）
SELECT partman.create_parent(
    p_parent_table   => 'public.events_partitioned',
    p_control        => 'event_time',
    p_interval       => '1 month',
    p_premake        => 3,
    p_start_partition => '2024-01-01'
);

-- パーティションテーブルへの書き込みを同期するトリガー関数
CREATE OR REPLACE FUNCTION sync_to_partitioned() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO events_partitioned VALUES (NEW.*) ON CONFLICT DO NOTHING;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO events_partitioned VALUES (NEW.*)
            ON CONFLICT (id, event_time)
            DO UPDATE SET
                user_id = EXCLUDED.user_id,
                event_type = EXCLUDED.event_type,
                payload = EXCLUDED.payload;
    ELSIF TG_OP = 'DELETE' THEN
        DELETE FROM events_partitioned
            WHERE id = OLD.id AND event_time = OLD.event_time;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_partitioned
    AFTER INSERT OR UPDATE OR DELETE ON events
    FOR EACH ROW EXECUTE FUNCTION sync_to_partitioned();

-- 長時間ロックを避けるため、バッチ単位で移行
DO $$
DECLARE
    batch_start TIMESTAMPTZ := '2024-01-01';
    batch_end   TIMESTAMPTZ;
    total_migrated BIGINT := 0;
BEGIN
    WHILE batch_start < now() LOOP
        batch_end := batch_start + INTERVAL '1 day';

        INSERT INTO events_partitioned
        SELECT * FROM events
        WHERE event_time >= batch_start AND event_time < batch_end
        ON CONFLICT (id, event_time) DO NOTHING;

        GET DIAGNOSTICS total_migrated = ROW_COUNT;
        RAISE NOTICE 'Migrated % rows for %', total_migrated, batch_start;

        batch_start := batch_end;
        PERFORM pg_sleep(0.1);  -- 過負荷を避けるためスロットリング
    END LOOP;
END $$;

-- 単一トランザクション内でテーブル名を変更
BEGIN;
    ALTER TABLE events RENAME TO events_old;
    ALTER TABLE events_partitioned RENAME TO events;
    -- 古いテーブルからトリガーを削除
    DROP TRIGGER IF EXISTS trg_sync_partitioned ON events_old;
COMMIT;

-- 確認後、安全な期間を経て古いテーブルを削除
-- DROP TABLE events_old;

ERROR: no partition of relation "events" found for row
DETAIL: Partition key of the failing row contains (event_time) = (2026-04-01 00:00:00+00).

CREATE TABLE events_2026_04 PARTITION OF events
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

-- CONCURRENTLY を使用してブロッキングを回避（PostgreSQL 14以降）
ALTER TABLE events DETACH PARTITION events_2024_01 CONCURRENTLY;

-- これは既存のすべてのクエリが完了するまで待機してから完了する
-- コマンド開始後、新しいクエリはそのパーティションを参照しない

-- パーティションの年齢を確認
SELECT relname, age(relfrozenxid) AS xid_age
FROM pg_class
WHERE relname LIKE 'events_%'
ORDER BY xid_age DESC;

-- 古いパーティションを手動でフリーズ
VACUUM (FREEZE, VERBOSE) events_2024_01;

-- 不足しているパーティションを作成
CREATE TABLE events_2026_04 PARTITION OF events
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

-- 上記により、一致する行が DEFAULT から新しいパーティションに
-- 自動的に移動される（PostgreSQL 13以降）

-- 親テーブルの手動 ANALYZE（パーティション横断統計に必要）
ANALYZE events;

-- pg_cron でスケジューリング
SELECT cron.schedule('analyze-events', '0 4 * * *',
    $$ANALYZE events$$);

-- 当月パーティションの積極的な autovacuum 設定
ALTER TABLE events_2026_03 SET (
    autovacuum_vacuum_scale_factor = 0.01,        -- デッドタプル1%でトリガー
    autovacuum_vacuum_cost_delay = 2,             -- スロットリングを軽減
    autovacuum_analyze_scale_factor = 0.005       -- 頻繁に再解析
);

-- 古い読み取り専用パーティションの緩和設定
ALTER TABLE events_2025_12 SET (
    autovacuum_vacuum_scale_factor = 0.2,         -- デフォルトに近い動作
    autovacuum_enabled = true                     -- XID エイジングのため有効に維持
);

#!/bin/bash
# freeze_old_partitions.sh - 6ヶ月以上前のパーティションをフリーズ

DB_NAME="production"
PARENT_TABLE="events"
CUTOFF=$(date -d "6 months ago" +%Y_%m)

psql -d "$DB_NAME" -t -A -c "
    SELECT relname FROM pg_class c
    JOIN pg_namespace n ON c.relnamespace = n.oid
    WHERE relname LIKE '${PARENT_TABLE}_%'
      AND relkind = 'r'
      AND relname < '${PARENT_TABLE}_${CUTOFF}'
    ORDER BY relname
" | while read partition; do
    echo "Freezing $partition..."
    psql -d "$DB_NAME" -c "VACUUM (FREEZE, VERBOSE) $partition"
done

-- 読み取りをブロックせずに単一パーティションを再インデックス（PostgreSQL 12以降）
REINDEX TABLE CONCURRENTLY events_2026_03;

-- すべてのパーティションのインデックス肥大化を確認
SELECT
    schemaname || '.' || tablename AS partition_name,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan AS scans,
    idx_tup_read AS tuples_read
FROM pg_stat_user_indexes
WHERE tablename LIKE 'events_%'
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;