大規模PostgreSQLテーブルの宣言的パーティショニングのベストプラクティス

大規模なPostgreSQLテーブルは、すべてのクエリ、インデックス再構築、データ保持ジョブが同じ巨大なリレーションにアクセスしなければならない場合、管理が難しくなります。宣言的パーティショニングを使用すると、1つの論理テーブルを複数の小さな子テーブルに分割できるため、PostgreSQLはパーティションキーを使用するクエリに対して行をルーティングし、パーティションをプルーニングできます。

鍵となるのは計画です。パーティショニングは、クエリフィルタやデータライフサイクルに一致する場合に最も効果を発揮し、パーティションキーがほとんど使用されない場合はオーバーヘッドが増える可能性があります。

宣言的パーティショニングの理解

宣言的パーティショニングを使用すると、テーブルをパーティション化されたものとして定義し、パーティションキーと戦略を指定できます。PostgreSQLは、パーティションキーの値に基づいてデータを適切なパーティションに自動的にルーティングします。これにより、複雑なトリガーや手動のデータ管理が不要になり、古い方法と比較してはるかにクリーンで効率的なソリューションになります。

宣言的パーティショニングの主な利点:

• クエリパフォーマンスの向上: パーティションキーでフィルタリングするクエリは、関連するパーティションのみをスキャンできるため、処理されるデータ量が削減されます。
• データロードの高速化: 一括ロード操作を特定のパーティションに直接実行でき、効率が向上します。
• メンテナンスの簡素化: アーカイブ、古いデータの削除、インデックス再構築などの操作を、テーブル全体に影響を与えることなく個別のパーティションで実行できます。
• オーバーヘッドの削減: 手動のパーティションロジックとそれに伴うメンテナンスが不要になります。

PostgreSQLのパーティショニング戦略

PostgreSQLは、宣言的パーティショニングに3つの主要な戦略を提供しており、それぞれ異なるユースケースに適しています。

1. 範囲パーティショニング

範囲パーティショニングは、パーティションキーの連続した値の範囲に基づいてデータを分割します。これは、時系列データ、連続ID、または値が定義された間隔に収まるデータに最適です。

使用するタイミング:

• 時系列データ（例：日付/タイムスタンプによるログ、イベント）。
• 連続的に生成されるID。
• 順序付けられた連続した値を持つデータ。

例: salesテーブルをsale_dateでパーティショニング。

-- 親のパーティションテーブルを作成
CREATE TABLE sales (
    sale_id SERIAL,
    product_id INT,
    amount DECIMAL(10, 2),
    sale_date DATE NOT NULL
)
PARTITION BY RANGE (sale_date);

-- 特定の日付範囲のパーティションを作成
CREATE TABLE sales_2023_q1 PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');

CREATE TABLE sales_2023_q2 PARTITION OF sales
    FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');

CREATE TABLE sales_2023_q3 PARTITION OF sales
    FOR VALUES FROM ('2023-07-01') TO ('2023-10-01');

CREATE TABLE sales_2023_q4 PARTITION OF sales
    FOR VALUES FROM ('2023-10-01') TO ('2024-01-01');

-- データを挿入すると自動的に正しいパーティションにルーティングされる
INSERT INTO sales (product_id, amount, sale_date) VALUES (101, 150.50, '2023-02-15');

2. リストパーティショニング

リストパーティショニングは、パーティションキーの個別の値のリストに基づいてデータを分割します。これは、固定された既知のカテゴリや識別子のセットがある場合に便利です。

使用するタイミング:

• 地理的リージョン（例：country、state）。
• 製品カテゴリ。
• ユーザーロールやステータス。

例: customersテーブルをcountry_codeでパーティショニング。

-- 親のパーティションテーブルを作成
CREATE TABLE customers (
    customer_id SERIAL,
    name VARCHAR(100),
    country_code CHAR(2) NOT NULL
)
PARTITION BY LIST (country_code);

-- 特定の国コードのパーティションを作成
CREATE TABLE customers_us PARTITION OF customers
    FOR VALUES IN ('US');

CREATE TABLE customers_ca PARTITION OF customers
    FOR VALUES IN ('CA');

CREATE TABLE customers_uk PARTITION OF customers
    FOR VALUES IN ('GB');

-- データを挿入すると自動的に正しいパーティションにルーティングされる
INSERT INTO customers (name, country_code) VALUES ('John Doe', 'US');

3. ハッシュパーティショニング

ハッシュパーティショニングは、パーティションキーのハッシュ値に基づいてデータを分割します。これは、自然な範囲やリストがない場合にデータをパーティション間で均等に分散させ、I/O負荷のバランスを取るのに役立ちます。

使用するタイミング:

• 他の戦略が適さない場合にデータを均等に分散させる。
• I/Oのホットスポットを回避する。
• 均等な分散が重要な大量トランザクションテーブル。

例: ordersテーブルをorder_idでパーティショニング。

-- 親のパーティションテーブルを作成
CREATE TABLE orders (
    order_id BIGSERIAL,
    user_id INT,
    order_total DECIMAL(10, 2)
)
PARTITION BY HASH (order_id);

-- 指定された数のパーティションを作成（例：4つ）
CREATE TABLE orders_part_1 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE orders_part_2 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE orders_part_3 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE orders_part_4 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- データを挿入すると自動的に正しいパーティションにルーティングされる
INSERT INTO orders (user_id, order_total) VALUES (500, 250.75);

宣言的パーティショニング実装のベストプラクティス

パーティショニングを効果的に実装するには、その利点を最大限に引き出すために慎重な計画とベストプラクティスの順守が必要です。

1. 適切なパーティションキーを選択する

パーティションキーは最も重要な決定事項です。クエリのパフォーマンスとメンテナンスに直接影響します。最も一般的なクエリのWHERE句で頻繁に使用されるキーを選択してください。

• 時系列データの場合: DATE、TIMESTAMPカラムは範囲パーティショニングに最適です。
• カテゴリデータの場合: country_code、status、regionなどのカラムはリストパーティショニングに適しています。
• 均等分散の場合: クエリで頻繁に使用される高カーディナリティのカラムで、ハッシュパーティショニングに適しています。

ヒント: WHERE句でほとんど使用されないカラムや、パーティション間で明確な値を持たないカラムでのパーティショニングは避けてください。これにより、クエリがすべてのパーティションをスキャンする可能性があります。

2. 適切なパーティショニング戦略を選択する

前述のように、データとクエリパターンに最も適した戦略（範囲、リスト、ハッシュ）を選択してください。

• 範囲: 順序付けられた連続データ向け。
• リスト: 個別の既知のカテゴリ向け。
• ハッシュ: データの均等分散と負荷分散向け。

3. パーティションのサイズと数を計画する

パーティションサイズに関する万能の答えはありません。ただし、以下の点を考慮してください。

• 小さなパーティションが多すぎる: プランナーやシステムのオーバーヘッドが増加する可能性があります。各パーティションには独自のメタデータがあります。
• 大きなパーティションが少なすぎる: パーティショニングのパフォーマンス上の利点が打ち消される可能性があります。
• 理想的なサイズ: パフォーマンス上の利点を提供するのに十分な大きさでありながら、メンテナンス操作が管理しやすいサイズを目指してください。一般的な出発点は、パーティションを論理的な時間単位（時系列データの場合は日次、週次、月次など）または管理可能なデータ量に合わせることです。

ヒント: パーティションサイズを監視し、データの成長に合わせてパーティショニング戦略を調整してください。必要に応じて、パーティションをデタッチして再アタッチしたり、異なる戦略でパーティションを再作成したりすることもできます。

4. 将来のデータのためのパーティショニング戦略を定義する

パーティションテーブルを作成する際に、既存のパーティションに該当しないデータを処理するためのデフォルトパーティションや戦略を定義することもできます。ただし、予期しないデータ配置やエラーを避けるために、パーティションを明示的に作成することを一般的にお勧めします。

例: 範囲パーティショニングで予期しない値をキャッチするためのDEFAULTパーティションの使用。

CREATE TABLE events (
    event_id BIGSERIAL,
    created_at DATE NOT NULL,
    payload JSONB
)
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_default PARTITION OF events DEFAULT;

ベストプラクティス: 明確さと制御のために、予想されるデータ範囲/リストのパーティションを手動で作成してください。特にリストや範囲パーティショニングの場合、DEFAULTパーティションは意図しないデータが蓄積される可能性があるため、慎重に検討してください。

5. パーティションのライフサイクルを管理する（データのアーカイブ/削除）

パーティショニングの最大の利点の1つは、データライフサイクル管理の簡素化です。時系列データの場合、古いデータをアーカイブまたは削除することが一般的です。

• パーティションのデタッチ: パーティションをデタッチしてデータをアーカイブしたり、他のパーティションに影響を与えずに完全に削除したりできます。

  -- パーティションをデタッチ
  ALTER TABLE sales DETACH PARTITION sales_2023_q1;
  
  -- オプションで、削除前にデタッチしたパーティションをアーカイブ
  -- CREATE TABLE sales_archive_2023_q1 (LIKE sales INCLUDING ALL);
  -- INSERT INTO sales_archive_2023_q1 SELECT * FROM sales_2023_q1;
  
  -- デタッチしたパーティションを削除
  DROP TABLE sales_2023_q1;
  

• パーティションの削除: クエリする必要がなくなった非常に古いデータの場合。

  -- パーティションを直接削除（最初にデタッチしない場合、親テーブルが認識している必要があります）
  DROP TABLE sales_2023_q1;
  

ヒント: cronジョブやその他のスケジューリングツールを使用して、新しいパーティションの作成と古いパーティションのデタッチ/削除を自動化し、多くの場合スクリプトと組み合わせて行います。

6. パーティションのインデックス作成

パーティションテーブルのインデックスは、親テーブルレベルまたは個々のパーティションレベルで管理できます。

• 親テーブル上のパーティション化されたインデックス: パーティション化された親で宣言されたインデックスは仮想的です。PostgreSQLはパーティション上に一致するインデックスを作成またはアタッチします。実際のインデックスデータは子インデックスに存在します。
• 個々のパーティション上のインデックス: 特定のパーティションに異なるインデックスが必要な場合や、既存のテーブルをパーティションとしてアタッチする場合に、パーティションごとにインデックスを管理できます。

ベストプラクティス: 新しいパーティションが意図されたインデックスパターンを継承できるように、パーティション化された親に共通のインデックスを作成してください。例外や大規模なメンテナンス操作には、パーティションごとのインデックス管理を使用します。

-- 例：パーティションにローカルインデックスを作成
CREATE INDEX ON sales_2023_q2 (product_id);

7. 宣言的構文を一貫して使用する

宣言的パーティショニングには、親テーブルにPARTITION BYを、子テーブルにPARTITION OF ... FOR VALUESを使用してください。古い継承ベースのパーティショニングパターンはレガシーシステムにまだ存在しますが、より多くの手動ルーティングとメンテナンスが必要です。

8. クエリプランを監視および分析する

パーティショニングを実装した後は、クエリのパフォーマンスを監視することが重要です。EXPLAIN ANALYZEを使用して、クエリが正しくパーティションをプルーニングしていること（つまり、関連するパーティションのみをスキャンしていること）を確認してください。

EXPLAIN ANALYZE SELECT * FROM sales WHERE sale_date BETWEEN '2023-02-01' AND '2023-02-28';

EXPLAINの出力で、クエリプランナーがsales_2023_q1パーティションのみを考慮していることを示す兆候を探してください。クエリプランが、そうすべきでない場合に複数またはすべてのパーティションをスキャンしていることを示している場合、パーティションキーまたはクエリを調整する必要があるかもしれません。

高度な考慮事項

外部キーと一意制約

• 外部キー: 最新のPostgreSQLはパーティションテーブルを含む外部キーをサポートしていますが、ロック動作とパフォーマンスは、使用しているバージョンとスキーマでテストする価値があります。
• 一意制約: パーティションテーブルの主キーまたは一意制約には、すべてのパーティションキーカラムを含める必要があり、パーティションキーは式にすることはできません。この制限により、PostgreSQLはパーティションごとのインデックスで一意性を強制できます。

ヒント: 論理テーブル全体での一意性を確保するには、制約にパーティションキーを含めてください。例えば、country_codeでのリストパーティショニングにはUNIQUE (country_code, customer_id)を使用します。

INSERTパフォーマンス

パーティショニングは一般的にSELECTパフォーマンスを向上させますが、INSERTパフォーマンスは影響を受ける可能性があります。パーティションキーが均一に分散されていない場合や、パーティションロジックが複雑な場合、PostgreSQLが正しいパーティションを決定する際に挿入にオーバーヘッドが発生する可能性があります。ハッシュパーティショニングは、書き込み負荷の分散に適していることがよくあります。

既存の大規模テーブルのパーティショニング戦略

既存の非常に大きなテーブルをパーティショニングすることは、複雑な操作になる可能性があります。これには通常、以下の手順が含まれます。

1. 新しいパーティションテーブル構造を作成する。
2. 履歴データのパーティションを作成する。
3. 古いテーブルから新しいパーティションテーブルにデータをコピーする（場合によってはバッチで）。
4. アプリケーションの読み取り/書き込みを新しいパーティションテーブルに切り替える。
5. 古いテーブルを削除する。

このプロセスは慎重に計画し、ステージング環境でテストし、ダウンタイムを最小限に抑えるためにメンテナンスウィンドウ中に実行する必要があります。

クエリとカレンダーに合わせたパーティショニング

宣言的パーティショニングは、パーティションキーが最も重要なフィルタに現れ、データの保持やアーカイブ方法と一致する場合に最も効果を発揮します。クエリパターンから始めて、そこから範囲、リスト、またはハッシュパーティショニングを選択し、EXPLAIN ANALYZEでプルーニングを確認してください。その後、パーティションの作成と廃止を自動化して、最初の1か月のデータが到着した後も設計が機能し続けるようにします。