PostgreSQL: PostgreSQLのパーティションテーブルとテーブル継承

-- 親テーブル: パーティションキーのみ
CREATE TABLE orders (
    id          BIGINT GENERATED ALWAYS AS IDENTITY,
    user_id     BIGINT NOT NULL,
    order_date  DATE NOT NULL,
    total_amount NUMERIC(12,2),
    status      TEXT DEFAULT 'pending'
) PARTITION BY RANGE (order_date);

-- 2024年の月次パーティション
CREATE TABLE orders_2024_01 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

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

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

-- デフォルトパーティションが範囲外の行を受け取る
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

CREATE TABLE

-- 指定した年の月次パーティションを生成
CREATE OR REPLACE FUNCTION create_monthly_partitions(
    p_parent  REGCLASS,
    p_year    INT
) RETURNS VOID AS $$
DECLARE
    m      INT;
    p_name TEXT;
    s_date TEXT;
    e_date TEXT;
BEGIN
    FOR m IN 1..12 LOOP
        p_name := format('%s_%s_%02s', p_parent::text, p_year, m);
        s_date := format('%s-%02s-01', p_year, m);
        e_date := format('%s-%02s-01', p_year, m + 1);
        EXECUTE format(
            'CREATE TABLE IF NOT EXISTS %I PARTITION OF %s
             FOR VALUES FROM (%L) TO (%L)',
            p_name, p_parent::text, s_date, e_date
        );
    END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT create_monthly_partitions('orders', 2025);

CREATE TABLE

INSERT INTO orders (user_id, order_date, total_amount, status)
VALUES
    (1001, '2024-01-15', 299.99, 'completed'),
    (1002, '2024-02-20', 159.50, 'shipped'),
    (1003, '2024-03-10', 89.00,  'pending');

-- パーティションプルーニング付きクエリ
SELECT * FROM orders
WHERE order_date BETWEEN '2024-02-01' AND '2024-02-29';

  id  | user_id | order_date | total_amount | status
------+---------+------------+--------------+--------
 1002 |    1002 | 2024-02-20 |       159.50 | shipped
(1 row)

CREATE TABLE users_by_region (
    id       BIGINT GENERATED ALWAYS AS IDENTITY,
    name     TEXT NOT NULL,
    region   TEXT NOT NULL,
    email    TEXT
) PARTITION BY LIST (region);

CREATE TABLE users_north_america PARTITION OF users_by_region
    FOR VALUES IN ('US', 'CA', 'MX');

CREATE TABLE users_europe PARTITION OF users_by_region
    FOR VALUES IN ('UK', 'DE', 'FR', 'ES');

CREATE TABLE users_asia PARTITION OF users_by_region
    FOR VALUES IN ('CN', 'JP', 'KR', 'SG');

CREATE TABLE users_other PARTITION OF users_by_region DEFAULT;

CREATE TABLE

CREATE TABLE access_logs (
    id         BIGINT GENERATED ALWAYS AS IDENTITY,
    user_id    BIGINT,
    action     TEXT,
    log_time   TIMESTAMPTZ DEFAULT now()
) PARTITION BY HASH (user_id);

-- 8つのHASHパーティションを作成
CREATE TABLE access_logs_p0 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 0);
CREATE TABLE access_logs_p1 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 1);
CREATE TABLE access_logs_p2 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 2);
CREATE TABLE access_logs_p3 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 3);
CREATE TABLE access_logs_p4 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 4);
CREATE TABLE access_logs_p5 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 5);
CREATE TABLE access_logs_p6 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 6);
CREATE TABLE access_logs_p7 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 7);

CREATE TABLE

CREATE TABLE order_details (
    id          BIGINT,
    order_id    BIGINT,
    order_date  DATE NOT NULL,
    region      TEXT NOT NULL,
    product_id  INT,
    quantity    INT
) PARTITION BY RANGE (order_date);

-- 第1レベル: 月単位
CREATE TABLE order_details_2024q1 PARTITION OF order_details
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01')
    PARTITION BY LIST (region);

-- 第2レベル: 各月範囲内で地域単位
CREATE TABLE order_details_2024q1_na PARTITION OF order_details_2024q1
    FOR VALUES IN ('US', 'CA', 'MX');

CREATE TABLE order_details_2024q1_eu PARTITION OF order_details_2024q1
    FOR VALUES IN ('UK', 'DE', 'FR');

CREATE TABLE

-- パーティションプルーニングを有効化（デフォルトON）
SET enable_partition_pruning = on;

-- どのパーティションがスキャンされるか確認
EXPLAIN (COSTS OFF) SELECT * FROM orders
WHERE order_date = '2024-02-15';

Append
  -> Seq Scan on orders_2024_02
       Filter: (order_date = '2024-02-15'::date)

-- 2023年1月のパーティションをデタッチ（データ損失なし）
ALTER TABLE orders DETACH PARTITION orders_2023_01;

-- これでスタンドアロンテーブルになり、安価なストレージに移動可能
ALTER TABLE orders_2023_01 SET TABLESPACE archive_tbs;

-- またはエクス��ートして削除
COPY orders_2023_01 TO '/archive/orders_2023_01.csv';
DROP TABLE orders_2023_01;

-- SQL statement executed successfully

-- 最初に新しいテーブルを作成
CREATE TABLE orders_2025_01 (LIKE orders INCLUDING DEFAULTS);

-- 検証用にCHECK制約を追加（ATTACHを高速化）
ALTER TABLE orders_2025_01
    ADD CONSTRAINT orders_2025_01_check
    CHECK (order_date >= '2025-01-01' AND order_date < '2025-02-01');

-- 親にアタッチ（短時間の排他ロックを取得）
ALTER TABLE orders ATTACH PARTITION orders_2025_01
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE

-- 同時読み取りをブロックせずにDETACH
ALTER TABLE orders DETACH PARTITION orders_2023_01 CONCURRENTLY;

-- SQL statement executed successfully

-- 親に作成したインデックスは全パーティションに伝播
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- 各パーティションが独自のインデックスを取得
\d orders_2024_01

Indexes:
    "orders_2024_01_user_id_idx" btree (user_id)

-- これは失敗: パーティションキーなしの一意制約
CREATE UNIQUE INDEX idx_orders_id ON orders (id);
-- エラー: 一意制約にパーティションキーを含める必要があります

-- これは成功: パーティションキー付き一意制約
CREATE UNIQUE INDEX idx_orders_id_date ON orders (id, order_date);

CREATE TABLE

-- 親テーブル
CREATE TABLE people (
    id    SERIAL PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT
);

-- 子テーブルは全カラムを継承 + 追加カラム
CREATE TABLE employees (
    salary    NUMERIC(10,2),
    dept      TEXT,
    hire_date DATE
) INHERITS (people);

-- 子に主キーを追加
ALTER TABLE employees ADD PRIMARY KEY (id);

-- 親 + 全子を検索
SELECT * FROM people;

-- 親のみを検索（子を含まない）
SELECT * FROM ONLY people;

-- 各行がどのテーブルから来たか確認
SELECT tableoid::regclass, * FROM people;

-- 特定のパーティションを分析
ANALYZE orders_2024_02;

-- 親経由で全パーティションを分析
ANALYZE orders;

-- パーティション統計の確認
SELECT relname, n_live_tup, last_analyze
FROM pg_stat_user_tables
WHERE relname LIKE 'orders_%'
ORDER BY relname;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- パーティション単位のJOIN（PG 12+）
SET enable_partitionwise_join = on;

SELECT o.id, o.total_amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-03-31';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- 基本監査テーブル
CREATE TABLE audit_log (
    id         BIGINT GENERATED ALWAYS AS IDENTITY,
    table_name TEXT NOT NULL,
    action     TEXT NOT NULL,
    changed_at TIMESTAMPTZ DEFAULT now()
) PARTITION BY RANGE (changed_at);

-- 月次パーティション、それぞれが型固有テーブルに継承される
CREATE TABLE audit_log_2024_01 PARTITION OF audit_log
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- 継承による特定監査タイプ用の追加カラム
CREATE TABLE audit_log_user_changes (
    old_email TEXT,
    new_email TEXT
) INHERITS (audit_log_2024_01);

CREATE TABLE

-- Eコマース注文の完全な月次パーティショニング設定
CREATE TABLE orders (
    id           BIGINT GENERATED ALWAYS AS IDENTITY,
    user_id      BIGINT NOT NULL,
    order_date   DATE NOT NULL,
    total_amount NUMERIC(12,2) DEFAULT 0,
    status       TEXT DEFAULT 'pending',
    created_at   TIMESTAMPTZ DEFAULT now()
) PARTITION BY RANGE (order_date);

-- 2024年第1四半期のパーティションを作成
CREATE TABLE orders_2024_01 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE orders_2024_02 PARTITION OF orders
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
CREATE TABLE orders_2024_03 PARTITION OF orders
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

-- 一意インデックスにパーティションキーを含める
CREATE UNIQUE INDEX idx_orders_id_date ON orders (id, order_date);
CREATE INDEX idx_orders_user_date ON orders (user_id, order_date);

-- テストデータを挿入
INSERT INTO orders (user_id, order_date, total_amount, status) VALUES
    (1001, '2024-01-05', 299.99, 'completed'),
    (1001, '2024-02-14', 159.50, 'shipped'),
    (1002, '2024-01-20', 450.00, 'completed'),
    (1002, '2024-03-01', 89.00,  'pending'),
    (1003, '2024-02-28', 1200.00,'completed');

-- パーティションプルーニングを確認
EXPLAIN (COSTS OFF) SELECT * FROM orders
    WHERE order_date BETWEEN '2024-02-01' AND '2024-02-28';

-- 古いパーティションをデタッチしてアーカイブ
ALTER TABLE orders DETACH PARTITION orders_2024_01;
COPY orders_2024_01 TO '/archive/orders_2024_01.csv';

-- 次の四半期の新しいパーティションをアタッチ
CREATE TABLE orders_2024_04 (LIKE orders INCLUDING DEFAULTS);
ALTER TABLE orders_2024_04
    ADD CONSTRAINT chk_2024_04
    CHECK (order_date >= '2024-04-01' AND order_date < '2024-05-01');
ALTER TABLE orders ATTACH PARTITION orders_2024_04
    FOR VALUES FROM ('2024-04-01') TO ('2024-05-01');

-- パーティションサイズを監視
SELECT relname,
       pg_size_pretty(pg_total_relation_size(oid)) AS size,
       reltuples::bigint AS row_estimate
FROM pg_class
WHERE relname LIKE 'orders_2024%' ORDER BY relname;