PostgreSQL パーティショニング実践ガイド

-- 親テーブルの作成
CREATE TABLE orders (
    id          BIGSERIAL,
    customer_id INTEGER NOT NULL,
    order_date  DATE NOT NULL,
    amount      DECIMAL(10, 2),
    status      VARCHAR(20),
    created_at  TIMESTAMP DEFAULT NOW()
) PARTITION BY RANGE (order_date);

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

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

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

-- パーティション別インデックス（自動的に継承）
CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_status ON orders (status, order_date);

-- デフォルトパーティション（どの範囲にも合わないデータを収容）
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

CREATE TABLE events (
    id          BIGSERIAL,
    event_type  VARCHAR(50) NOT NULL,
    payload     JSONB,
    created_at  TIMESTAMP DEFAULT NOW()
) PARTITION BY LIST (event_type);

CREATE TABLE events_user PARTITION OF events
    FOR VALUES IN ('user_signup', 'user_login', 'user_logout');

CREATE TABLE events_order PARTITION OF events
    FOR VALUES IN ('order_created', 'order_paid', 'order_cancelled');

CREATE TABLE events_system PARTITION OF events
    FOR VALUES IN ('health_check', 'deploy', 'config_change');

CREATE TABLE events_default PARTITION OF events DEFAULT;

CREATE TABLE user_sessions (
    id         BIGSERIAL,
    user_id    INTEGER NOT NULL,
    session_id UUID NOT NULL,
    data       JSONB,
    expires_at TIMESTAMP
) PARTITION BY HASH (user_id);

-- 4つのパーティションに均等分配
CREATE TABLE user_sessions_0 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE user_sessions_1 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE user_sessions_2 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE user_sessions_3 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- Partition pruning の有効化確認
SHOW enable_partition_pruning;  -- on

-- EXPLAIN で pruning を確認
EXPLAIN (ANALYZE, COSTS, BUFFERS)
SELECT * FROM orders
WHERE order_date >= '2026-03-01'
  AND order_date < '2026-04-01';

-- 結果例：
-- Append (actual rows=50000)
--   -> Seq Scan on orders_2026_03 (actual rows=50000)
--        Filter: (order_date >= '2026-03-01' AND order_date < '2026-04-01')
-- orders_2026_01、orders_2026_02 はスキャンされない！

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

-- 自動パーティション管理の設定
SELECT partman.create_parent(
    p_parent_table   => 'public.orders',
    p_control        => 'order_date',
    p_type           => 'native',
    p_interval       => 'monthly',
    p_premake        => 3       -- 3ヶ月先まで事前作成
);

-- メンテナンス関数（cron で毎日実行）
SELECT partman.run_maintenance();

# pg_partman メンテナンス（毎日午前2時）
0 2 * * * psql -U postgres -d mydb \
  -c "SELECT partman.run_maintenance();" \
  >> /var/log/pg_partman.log 2>&1

-- pg_partman なしで直接自動化
CREATE OR REPLACE FUNCTION create_monthly_partition(
    p_table TEXT,
    p_year INTEGER,
    p_month INTEGER
) RETURNS VOID AS $$
DECLARE
    partition_name TEXT;
    start_date DATE;
    end_date DATE;
BEGIN
    partition_name := format('%s_%s_%s',
        p_table,
        p_year,
        LPAD(p_month::TEXT, 2, '0')
    );
    start_date := make_date(p_year, p_month, 1);
    end_date := start_date + INTERVAL '1 month';

    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I
         FOR VALUES FROM (%L) TO (%L)',
        partition_name, p_table, start_date, end_date
    );

    RAISE NOTICE 'Created partition: %', partition_name;
END;
$$ LANGUAGE plpgsql;

-- 使用例
SELECT create_monthly_partition('orders', 2026, 4);
SELECT create_monthly_partition('orders', 2026, 5);

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

-- 分離されたパーティションを別テーブルとして維持または削除
DROP TABLE orders_2025_01;  -- 即時削除（数億件でも一瞬）

-- 比較：DELETE は非常に遅い
-- DELETE FROM orders WHERE order_date < '2025-02-01';  ← これはやめましょう！

-- パーティション別行数の確認
SELECT
    schemaname || '.' || relname AS partition,
    n_live_tup AS row_count,
    pg_size_pretty(pg_relation_size(relid)) AS size
FROM pg_stat_user_tables
WHERE relname LIKE 'orders_%'
ORDER BY relname;

-- パーティション一覧の照会
SELECT
    parent.relname AS parent,
    child.relname AS partition,
    pg_get_expr(child.relpartbound, child.oid) AS bounds
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
WHERE parent.relname = 'orders'
ORDER BY child.relname;

1. PRIMARY KEY にパーティションキーを含める必要あり
   CREATE TABLE orders (...) PARTITION BY RANGE (order_date);
   → PK は (id, order_date) の形式でなければならない

2. UNIQUE 制約にもパーティションキーを含める必要あり

3. パーティション数が多すぎるとプランニングオーバーヘッドが増加
   → 1000以下を推奨

4. クロスパーティション UPDATE は PostgreSQL 11+ でのみサポート