PostgreSQL パーティショニング完全ガイド: Range, List, Hash 戦略とパフォーマンス最適化

-- 親テーブルの作成
CREATE TABLE events (
    id          BIGSERIAL,
    event_type  VARCHAR(50) NOT NULL,
    payload     JSONB,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- 月別パーティションの作成
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');

-- デフォルトパーティション（範囲に合うパーティションがない場合）
CREATE TABLE events_default PARTITION OF events DEFAULT;

-- 各パーティションに自動作成されるグローバルインデックス
CREATE INDEX idx_events_type ON events (event_type);
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- パーティション別ローカルインデックス
CREATE INDEX idx_events_2026_03_type
    ON events_2026_03 (event_type, created_at DESC);

-- パーティションプルーニングが動作するか EXPLAIN で確認
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events
WHERE created_at >= '2026-03-01'
  AND created_at < '2026-03-15'
  AND event_type = 'purchase';

-- 結果: events_2026_03 パーティションのみスキャン
-- Append
--   -> Index Scan using events_2026_03_type on events_2026_03
--        Index Cond: (event_type = 'purchase')
--        Filter: (created_at >= '2026-03-01' AND created_at < '2026-03-15')

-- 地域別パーティショニング
CREATE TABLE orders (
    id          BIGSERIAL,
    customer_id BIGINT NOT NULL,
    amount      DECIMAL(12,2) NOT NULL,
    region      VARCHAR(10) NOT NULL,
    status      VARCHAR(20) NOT NULL,
    ordered_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, region)
) PARTITION BY LIST (region);

CREATE TABLE orders_kr PARTITION OF orders
    FOR VALUES IN ('KR');

CREATE TABLE orders_jp PARTITION OF orders
    FOR VALUES IN ('JP');

CREATE TABLE orders_us PARTITION OF orders
    FOR VALUES IN ('US');

CREATE TABLE orders_eu PARTITION OF orders
    FOR VALUES IN ('DE', 'FR', 'GB', 'IT', 'ES');

CREATE TABLE orders_other PARTITION OF orders DEFAULT;

-- ユーザー ID ベースのハッシュパーティショニング（4パーティション）
CREATE TABLE user_activities (
    id          BIGSERIAL,
    user_id     BIGINT NOT NULL,
    activity    VARCHAR(100) NOT NULL,
    metadata    JSONB,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, user_id)
) PARTITION BY HASH (user_id);

CREATE TABLE user_activities_0 PARTITION OF user_activities
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE user_activities_1 PARTITION OF user_activities
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE user_activities_2 PARTITION OF user_activities
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE user_activities_3 PARTITION OF user_activities
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- 第1レベル: 日付（Range）、第2レベル: 地域（List）
CREATE TABLE sales (
    id          BIGSERIAL,
    product_id  BIGINT NOT NULL,
    region      VARCHAR(10) NOT NULL,
    amount      DECIMAL(12,2) NOT NULL,
    sold_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, sold_at, region)
) PARTITION BY RANGE (sold_at);

-- 月別サブパーティション
CREATE TABLE sales_2026_03 PARTITION OF sales
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01')
    PARTITION BY LIST (region);

CREATE TABLE sales_2026_03_kr PARTITION OF sales_2026_03
    FOR VALUES IN ('KR');

CREATE TABLE sales_2026_03_jp PARTITION OF sales_2026_03
    FOR VALUES IN ('JP');

CREATE TABLE sales_2026_03_other PARTITION OF sales_2026_03 DEFAULT;

-- pg_partman のインストール
CREATE EXTENSION pg_partman;

-- 自動パーティション管理の設定
SELECT partman.create_parent(
    p_parent_table := 'public.events',
    p_control := 'created_at',
    p_type := 'native',
    p_interval := '1 month',
    p_premake := 3,           -- 3ヶ月先まで事前作成
    p_start_partition := '2026-01-01'
);

-- 自動メンテナンス（cron で実行）
-- 新しいパーティション作成 + 古いパーティション管理
SELECT partman.run_maintenance();

#!/bin/bash
# create_monthly_partitions.sh

PGHOST="localhost"
PGDB="mydb"
PGUSER="admin"

# 今後3ヶ月分のパーティションを作成
for i in 0 1 2 3; do
    MONTH=$(date -d "+${i} months" +%Y-%m-01)
    NEXT_MONTH=$(date -d "+$((i+1)) months" +%Y-%m-01)
    TABLE_NAME="events_$(date -d "+${i} months" +%Y_%m)"

    psql -h $PGHOST -d $PGDB -U $PGUSER -c "
        CREATE TABLE IF NOT EXISTS ${TABLE_NAME}
        PARTITION OF events
        FOR VALUES FROM ('${MONTH}') TO ('${NEXT_MONTH}');
    " 2>/dev/null

    echo "Created partition: ${TABLE_NAME}"
done

-- パーティションの分離（データ保存、クエリから除外）
ALTER TABLE events DETACH PARTITION events_2025_01;

-- 分離したパーティションを圧縮テーブルスペースに移動
ALTER TABLE events_2025_01 SET TABLESPACE archive_tablespace;

-- または完全削除（DROP は DELETE よりはるかに高速！）
DROP TABLE events_2025_01;
-- vs.
-- DELETE FROM events WHERE created_at < '2025-02-01';
-- ↑ この方法は数百万行の削除に数十分かかる

-- 1億行のテーブル作成（パーティショニングなし）
CREATE TABLE events_no_part (
    id BIGSERIAL PRIMARY KEY,
    event_type VARCHAR(50),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 同一データでパーティショニングテーブルを作成（月別）
-- ...（上記の events テーブルを使用）

-- 1ヶ月分のデータ照会
-- パーティショニングなし: 15.2秒（Full Table Scan）
-- パーティショニングあり: 0.8秒（Partition Pruning → 単一パーティションスキャン）

-- インデックスサイズ
-- パーティショニングなし: 2.1 GB（単一インデックス）
-- パーティショニングあり: 175 MB/パーティション × 12 = 2.1 GB（合計は同じだが個別インデックスが効率的）

-- データ削除（1ヶ月分）
-- パーティショニングなし: DELETE → 45分 + VACUUM 30分
-- パーティショニングあり: DROP TABLE → 0.01秒

-- エラー！パーティションキー（created_at）が PK にない
CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,  -- ERROR
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

-- 正しい方法：複合 PK
CREATE TABLE events (
    id BIGSERIAL,
    created_at TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- UNIQUE 制約にもパーティションキーを含める必要あり
CREATE UNIQUE INDEX idx_events_unique
    ON events (event_type, created_at);  -- OK

-- パーティションキーのない UNIQUE は不可
-- CREATE UNIQUE INDEX ON events (event_type);  -- ERROR

-- パーティションキーでフィルタリングしないとすべてのパーティションをスキャン
-- 必ず WHERE 句にパーティションキーを含めること！
SELECT * FROM events
WHERE created_at >= '2026-03-01'  -- パーティションプルーニングが動作
  AND event_type = 'purchase';

-- enable_partition_pruning 設定の確認
SHOW enable_partition_pruning;  -- 'on' でなければならない

-- パーティション別サイズの確認
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as total_size,
    pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) as table_size
FROM pg_tables
WHERE tablename LIKE 'events_%'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

-- パーティション別行数の確認
SELECT
    relname as partition_name,
    n_live_tup as row_count
FROM pg_stat_user_tables
WHERE relname LIKE 'events_%'
ORDER BY relname;

-- パーティションプルーニング効果の確認
EXPLAIN (ANALYZE, COSTS, BUFFERS, FORMAT TEXT)
SELECT count(*) FROM events
WHERE created_at >= '2026-03-01' AND created_at < '2026-04-01';