:pg_partman (パーティションマネージャー)

ステップ 1: 拡張機能のインストールと親テーブルの作成

まず、データベースに pg_partman 拡張機能をインストールします。次に、パーティショニング用の親テーブルを作成します。

-- 1. 現在のデータベースで pg_partman 拡張機能を有効にします。
CREATE EXTENSION pg_partman;

-- 2. ログデータを格納するための親テーブルを作成します。
-- PARTITION BY RANGE (created_at) は、このテーブルが created_at 列を範囲パーティショニングに使用するパーティションテーブルであることを指定します。
CREATE TABLE public.log_table (
    id bigint GENERATED BY DEFAULT AS IDENTITY,
    created_at timestamptz NOT NULL,
    message text
) PARTITION BY RANGE (created_at);

ステップ 2: パーティションセットの初期化

create_parent() 関数を呼び出して、log_table を pg_partman 管理システムに登録し、パーティショニングルールを定義します。

-- create_parent 関数を呼び出してパーティショニングを初期化します。
-- public.log_table: 管理対象の親テーブル。
-- created_at: パーティションキー列。
-- '1 day': パーティション間隔。新しいパーティションが毎日作成されることを意味します。
SELECT create_parent(
    p_parent_table := 'public.log_table',
    p_control := 'created_at',
    p_interval := '1 day'
);

正常に実行されると、pg_partman は現在の日付と今後数日間のパーティションを自動的に作成します。\d+ public.log_table コマンドを実行して、作成されたパーティションを表示できます。

ステップ 3: テストデータの挿入

親テーブル log_table にデータを挿入します。データは created_at の値に基づいて、対応するパーティションに自動的にルーティングされます。

INSERT INTO public.log_table (created_at, message)
SELECT generate_series(
     now() - interval '4 days',
     now(),
     interval '1 hour'
 ),
 'This is a log message.';

ステップ 4: 保持ポリシーの設定とクリーンアップの検証

データ保持ポリシーを設定して、pg_partman が期限切れのデータを自動的にクリーンアップするようにします。log_table が過去 2 日間のデータを保持し、期限切れのパーティションを自動的に DROP するように設定します。

-- 1. part_config テーブルを更新して、log_table の保持ポリシーを設定します。
UPDATE part_config
SET
    retention = '2 days',             -- 保持期間を 2 日に設定します。
    retention_keep_table = false      -- テーブル構造を保持しません。期限切れのパーティションを直接 DROP します。
WHERE parent_table = 'public.log_table';

-- 2. メンテナンス タスクを手動で実行して、保持ポリシーをトリガーします。
CALL run_maintenance_proc();

-- 3. 検証: パーティションリストを再度確認し、2 日以上経過したパーティションが削除されたことを確認します。
SELECT partition_schemaname, partition_tablename FROM show_partitions('public.log_table', 'DESC');

これで、日次でパーティショニングされ、自動的にクリーンアップされるログテーブルの設定が完了しました。

シナリオ 1: 自動クリーンアップ機能付きの日次パーティションログテーブル

このシナリオは、ログ、監査レコード、モノのインターネット (IoT) の時系列データなど、急速に増加し、明確なライフサイクルを持つデータに適しています。

ビジネスシナリオ

audit_logs テーブルを日次でパーティショニングし、データを 30 日間保持し、期限切れのデータを直接削除するのではなく、別のスキーマに自動的にアーカイブします。

手順

1. 親テーブルを作成し、パーティションセットを初期化します。

   -- 親テーブルを作成します。
   CREATE TABLE public.audit_logs (
       log_id uuid NOT NULL,
       event_time timestamptz NOT NULL,
       user_id text,
       details jsonb,
       PRIMARY KEY (event_time, log_id)
   ) PARTITION BY RANGE (event_time);
   
   -- 日次パーティショニングのためにパーティションセットを初期化します。
   SELECT create_parent(
       p_parent_table := 'public.audit_logs',
       p_control := 'event_time',
       p_interval := '1 day'
   );

2. 保持およびアーカイブポリシーを設定します。30 日以上経過した古いパーティションを archive スキーマに移動し、その後のバッチ分析やバックアップに備えます。

   -- アーカイブ用のスキーマを作成します。
   CREATE SCHEMA IF NOT EXISTS archive;
   
   -- 構成を更新して保持ポリシーを設定します。
   UPDATE part_config
   SET
       retention = '30 days',          -- 30 日間保持します。
       retention_schema = 'archive'    -- 期限切れになったら archive スキーマに移動します。
   WHERE parent_table = 'public.audit_logs';

   run_maintenance() が実行されると、audit_logs_p2023_10_01 のように、全体が 30 日以上経過したパーティションテーブルは、プライマリパーティションセットからデタッチされます。そのスキーマは public から archive に変更され、archive.audit_logs_p2023_10_01 になります。

シナリオ 2: ID による業務テーブルのパーティショニング

このシナリオは、ユーザーテーブルや商品テーブルのように、数値 ID をプライマリキーとして使用し、パーティショニングによってホットデータとコールドデータを分離したいビジネスに適しています。

ビジネスシナリオ

orders テーブルを order_id でパーティショニングし、各パーティションに 1,000 万 ID を含めます。

判断のアドバイス

p_interval の選択は重要です。ビジネス ID の増加率と単一パーティションの適切なサイズに基づいて見積もることができます。毎日 100 万件の新規注文が追加される場合、10000000 の間隔は、約 10 日ごとに新しいパーティションが作成されることを意味します。

手順

1. 親テーブルを作成し、パーティションセットを初期化します。

   -- 親テーブルを作成します。
   CREATE TABLE public.orders (
       order_id bigint NOT NULL,
       customer_id bigint,
       order_date timestamptz,
       amount numeric
   ) PARTITION BY RANGE (order_id);
   
   -- ID 範囲によるパーティショニングのためにパーティションセットを初期化します。
   -- 注意: p_interval は数値であってもテキストとして提供する必要があります。
   SELECT create_parent(
       p_parent_table := 'public.orders',
       p_control := 'order_id',
       p_interval := '10000000' -- 各パーティションには 1,000 万 ID が含まれます。
   );

2. 保持ポリシーの設定 (オプション)：古い注文データをアーカイブまたは削除できる場合は、ID ベースの保持ポリシーを設定できます。

   -- 最新の 5,000 万件の注文レコードを保持します。
   -- 現在の最大 order_id が 100,000,000 であると仮定します。
   -- このポリシーは、order_id < 50,000,000 のすべてのパーティションを削除します。
   UPDATE part_config
   SET
       retention = '50000000',
       retention_keep_table = false
   WHERE parent_table = 'public.orders';

シナリオ 3: 既存の大規模テーブルのパーティションテーブルへの移行

このシナリオは、テラバイト規模のテーブルなど、既存の大規模なモノリシックテーブルを、pg_partman で管理されるパーティション構造にスムーズに移行するためのものです。

ビジネスシナリオ

1 TB の sensor_data テーブルを月次パーティションテーブルに移行し、オンラインサービスへの影響を最小限に抑えます。

コアツール

partition_data_proc() プロシージャ。小規模なバッチおよび独立したトランザクションでデータを移行できるため、長時間実行されるテーブルロックやトランザクションのバックログを回避できます。

手順

1. 準備：親テーブルを作成し、パーティションセットを初期化します。

   -- 1. 元のテーブルをデータソースとして機能するように名前変更します。
   ALTER TABLE public.sensor_data RENAME TO sensor_data_source;
   
   -- 2. 同じ構造で新しい親テーブルを作成します。
   CREATE TABLE public.sensor_data (
       reading_id bigserial,
       device_id text,
       ts timestamptz NOT NULL,
       value double precision
   ) PARTITION BY RANGE (ts);
   
   -- 3. パーティションセットを初期化します。
   SELECT create_parent('public.sensor_data','ts','1 month');

2. データ移行の実行：partition_data_proc を使用して、sensor_data_source から新しいパーティションテーブル sensor_data にデータをバッチで移行します。

   -- データ移行プロシージャを呼び出します。
   -- p_source_table: ソーステーブルを指定します。
   -- p_interval: 各バッチで処理するデータの時間範囲。パーティション間隔以下に設定することを推奨します。ここでは '1 day' に設定されており、1 日分のデータが 1 つのトランザクションで処理されることを意味します。
   -- p_wait: 各バッチ間の待機時間 (秒)。I/O と CPU への持続的な圧力を軽減するために使用します。
   CALL partition_data_proc(
       p_parent_table := 'public.sensor_data',
       p_source_table := 'public.sensor_data_source',
       p_interval := '1 day',
       p_wait := 1
   );

   このプロシージャは、sensor_data_source テーブルのすべてのデータが移行されるまでループします。移行中、pg_partman は、必要に応じてパーティションを自動的に作成します。

3. 最終ステップ：移行が完了したら、データ整合性を検証します。その後、元のデータソーステーブル sensor_data_source を安全に削除できます。移行中、アプリケーションは新しいパーティション化された親テーブル sensor_data に新しいデータを書き込み続けることができます。

パーティションタイプと間隔

パーティションタイプ

pg_partman は PostgreSQL の宣言型パーティショニングに基づいており、主に以下のパーティショニング方法をサポートしています。

• 範囲パーティショニング：タイムスタンプやシリアル ID などの連続データに適しています。これは最も一般的なパーティションタイプです。

• リストパーティショニング：間隔が 1 の ID ベースのパーティショニングでのみサポートされます。

パーティション間隔 (p_interval)

このパラメーターは、各子パーティションがカバーするデータ範囲を定義します。

• 時間ベース：'1 hour'、'1 day'、'1 week'、'1 month' など、有効な interval 値を受け入れます。

  説明

  • サポートされる最小時間間隔は 1 秒です。上限は PostgreSQL がサポートする最小および最大タイムスタンプ値によって制約されます。

  • create_parent() を初めて実行してパーティションセットを作成する際、1 日未満の間隔は、作成する最初のパーティションを決定するときに切り捨てられます。

    • 24 時間未満で 1 分より大きい間隔は、最も近い時間に切り捨てられます。

    • 1 分未満の間隔は、最も近い分に切り捨てられます。

    • システムは現在の実時間をサポートするのに十分なパーティションを作成します。これは、create_parent() を実行すると、予想よりも多くの過去のパーティションが作成され、すべての将来のパーティションが作成されない可能性があることを意味します。run_maintenance() の初回実行時に、不足している将来のパーティションが作成されます。これは、カスタム時間間隔をサポートする必要があるためです。24 時間以上の間隔の場合、設定は期待どおりに進みます。

  • 100 年以上の間隔の場合、拡張機能は世紀または千年紀の実際の開始点を使用してパーティション名と制約ルールを決定します。例えば、21 世紀と第 3 千年紀は 2001 年 1 月 1 日 (2000 年ではない) に始まりました。これは、0 年が存在しないことも意味します。

• 数値/ID ベース：範囲サイズを表す整数を受け入れますが、'1000000' のようにテキストとして提供する必要があります。

サブパーティショニング

サブパーティショニングを使用すると、既存のパーティションにさらに細かい粒度のパーティションを作成して、年単位でパーティショニングし、各年を月単位でサブパーティショニングするなどの多階層パーティション構造を形成できます。これは主に、大規模な年次パーティションを月単位でアーカイブするなど、データの組織化とライフサイクル管理に使用されます。パフォーマンス向上については、サブパーティショニングの効果は通常限定的です。トップレベルのパーティション間隔の調整を優先することを推奨します。

データ保持ポリシー

pg_partman は、run_maintenance() を通じてデータ保持ポリシーを自動的に適用し、古いパーティションのライフサイクルを管理します。コア設定パラメーターは以下の通りです。

制約除外

制約除外は、パーティションテーブルのクエリパフォーマンスを向上させるための重要な機能です。クエリの WHERE 句が特定のパーティションを明示的に除外できる場合、クエリオプティマイザはそれらのパーティションのスキャンをスキップします。

pg_partman は、制約除外の効果を高めるために、非パーティションキー列に CHECK 制約を追加する機能を提供します。例えば、created_at でパーティショニングされたテーブルに対して、WHERE device_id = 'A' を伴うクエリが頻繁にある場合、データがもはや変更されない古いパーティションの device_id 列に制約を追加できます。

コア設定パラメーターは以下の通りです。

• constraint_cols：CHECK 制約を作成する列を指定するテキスト配列。例：'{device_id, user_id}'。

• optimize_constraint：制約を適用する前にパーティションがどれだけ古くなければならないかを定義する整数。デフォルト値の 30 は、30 パーティション間隔より古いパーティションに制約が適用されることを意味します。

• constraint_valid：テーブルに制約を追加すると、テーブル内の既存のデータと競合する可能性があり、また pg_partman のメンテナンス操作に時間がかかる可能性があります。これらの制約を作成時に無効に設定できます。これにより、制約の作成はほぼ瞬時に完了しますが、検証されるまで制約除外は使用できません。したがって、デフォルトでは、制約は有効として作成されます。

テンプレートテーブルと属性の継承

PostgreSQL の宣言型パーティショニングは、非パーティションキーのプライマリキーや一意なインデックス、autovacuum 設定など、特定のオブジェクト属性の継承を完全にはサポートしていません。そのため、pg_partman はテンプレートテーブルメカニズムを使用して、新しく作成されたパーティションがこれらの属性を正しく継承できるようにします。以下の表は、pg_partman が特定の属性の継承をどのように管理するかを示しています。表に記載されていない属性は、親テーブルを通じて管理されます。

• 属性：create_parent() を呼び出すと、pg_partman は自動的に parent_table_template という名前のテーブルを作成します。インデックスの追加や autovacuum パラメーターの変更など、このテンプレートテーブルに対して行う ALTER TABLE 変更は、将来新しいパーティションが作成されるときに適用されます。

  説明

  テンプレートテーブルへの変更は、既存のパーティションに遡って適用されません。変更を行うには、古いパーティションに対して手動で ALTER 操作を実行する必要があります。

• 権限と所有権：デフォルトでは、権限と所有権は継承されません。pg_partman を通じてこの機能を有効にした場合、この継承はパーティション作成時にのみ行われ、変更後に自動的に適用されないことに注意してください。reapply_privileges() で再適用できます。パーティションに直接アクセスする必要がない限り、通常はこの機能を有効にする必要はありません。必要な場合は、inherit_privileges オプションを設定できます。

  説明

  IDENTITY 機能を使用してシーケンスを管理する場合、新しいシーケンス値の自動生成は、親テーブルを通じてデータが挿入された場合にのみサポートされます。パーティションに直接データを挿入する場合はサポートされません。

自動メンテナンス

pg_partman のコアメンテナンスタスクである新しいパーティションの作成と保持ポリシーの適用は、run_maintenance() 関数または run_maintenance_proc() プロシージャによって実行されます。PolarDB for PostgreSQL (Oracle 互換) では、組み込みのバックグラウンドワーカーを使用してこのプロセスを自動化することを推奨します。設定した間隔で run_maintenance_proc() を自動的に呼び出します。

手動メンテナンス

デバッグ中や特定のテーブルをきめ細かく制御する必要がある場合など、手動でメンテナンスをトリガーする必要がある場合があります。

• CALL run_maintenance_proc(); このプロシージャの使用を推奨します。part_config 内で automatic_maintenance が on になっているすべてのパーティションセットのメンテナンスを実行します。また、各パーティションセットのメンテナンス後にトランザクションを自動的にコミットするため、ロック競合を軽減します。

• SELECT run_maintenance('public.my_table'); 単一のパーティションセットのみをメンテナンスしたい場合は、この関数を呼び出して親テーブル名を渡すことができます。

監視とアラート

本番環境では、パーティションメンテナンスが正常に実行されているかを監視し、パーティションが時間通りに作成されないことによるデータ書き込みの失敗を防ぐ必要があります。part_config テーブルの maintenance_last_run フィールドは、各パーティションセットの最後の成功したメンテナンス実行のタイムスタンプを記録します。これは重要な監視メトリックです。

-- 自動メンテナンスされるすべてのパーティションセットのうち、
-- メンテナンス間隔の 2 倍以上、正常に実行されていないものをクエリします。
SELECT parent_table
FROM part_config
WHERE
    automatic_maintenance = 'on'
    AND maintenance_last_run < (now() - (SELECT setting::interval * 2 FROM pg_settings WHERE name = 'pg_partman_bgw.interval'));

このクエリが何らかの行を返した場合、対応するパーティションセットのメンテナンスが中断された可能性があり、即時の調査が必要です。

パーティションの作成

これらの関数は、パーティションセットを初期化し、サブパーティションを定義するために使用されます。

create_parent(
    p_parent_table text
    , p_control text
    , p_interval text
    , p_type text DEFAULT 'range'
    , p_epoch text DEFAULT 'none'
    , p_premake int DEFAULT 4
    , p_start_partition text DEFAULT NULL
    , p_default_table boolean DEFAULT true
    , p_automatic_maintenance text DEFAULT 'on'
    , p_constraint_cols text[] DEFAULT NULL
    , p_template_table text DEFAULT NULL
    , p_jobmon boolean DEFAULT true
    , p_date_trunc_interval text DEFAULT NULL
    , p_control_not_null boolean DEFAULT true
    , p_time_encoder text DEFAULT NULL
    , p_time_decoder text DEFAULT NULL
)
RETURNS boolean

create_sub_parent(
    p_top_parent text
    , p_control text
    , p_interval text
    , p_type text DEFAULT 'range'
    , p_default_table boolean DEFAULT true
    , p_declarative_check text DEFAULT NULL
    , p_constraint_cols text[] DEFAULT NULL
    , p_premake int DEFAULT 4
    , p_start_partition text DEFAULT NULL
    , p_epoch text DEFAULT 'none'
    , p_jobmon boolean DEFAULT true
    , p_date_trunc_interval text DEFAULT NULL
    , p_control_not_null boolean DEFAULT true
    , p_time_encoder text DEFAULT NULL
    , p_time_decoder text DEFAULT NULL
)
RETURNS boolean

create_partition_time(
    p_parent_table text
    , p_partition_times timestamptz[]
    , p_start_partition text DEFAULT NULL
)
RETURNS boolean

create_partition_id(
    p_parent_table text
    , p_partition_ids bigint[]
    , p_start_partition text DEFAULT NULL
)
RETURNS boolean

データの移行

これらの関数は、既存データや誤ってデフォルトパーティションに入ってしまったデータを正しいパーティションに移行するために使用されます。

partition_data_time(
    p_parent_table text
    , p_batch_count int DEFAULT 1
    , p_batch_interval interval DEFAULT NULL
    , p_lock_wait numeric DEFAULT 0
    , p_order text DEFAULT 'ASC'
    , p_analyze boolean DEFAULT true
    , p_source_table text DEFAULT NULL
    , p_ignored_columns text[] DEFAULT NULL
)
RETURNS bigint

partition_data_id(
    p_parent_table text
    , p_batch_count int DEFAULT 1
    , p_batch_interval bigint DEFAULT NULL
    , p_lock_wait numeric DEFAULT 0
    , p_order text DEFAULT 'ASC'
    , p_analyze boolean DEFAULT true
    , p_source_table text DEFAULT NULL
    , p_ignored_columns text[] DEFAULT NULL
)
RETURNS bigint

partition_data_proc (
    p_parent_table text
    , p_loop_count int DEFAULT NULL
    , p_interval text DEFAULT NULL
    , p_lock_wait int DEFAULT 0
    , p_lock_wait_tries int DEFAULT 10
    , p_wait int DEFAULT 1
    , p_order text DEFAULT 'ASC'
    , p_source_table text DEFAULT NULL
    , p_ignored_columns text[] DEFAULT NULL
    , p_quiet boolean DEFAULT false
)

パーティションのメンテナンス

これらの関数は、日常的なパーティション管理、監視、および情報クエリに使用されます。

run_maintenance(
    p_parent_table text DEFAULT NULL
    , p_analyze boolean DEFAULT false
)
RETURNS void

run_maintenance_proc(
    p_wait int DEFAULT 0
    , p_analyze boolean DEFAULT NULL
)

check_default(
    p_exact_count boolean DEFAULT true
)

show_partitions(
    p_parent_table text
    , p_order text DEFAULT 'ASC'
    , p_include_default boolean DEFAULT false
)
RETURNS TABLE (
    partition_schemaname text
    , partition_tablename text
)

show_partition_name(
    p_parent_table text
    , p_value text
    , OUT partition_schema text
    , OUT partition_table text
    , OUT suffix_timestamp timestamptz
    , OUT suffix_id bigint
    , OUT table_exists boolean
)
RETURNS record

show_partition_info(
    p_child_table text
    , p_partition_interval text DEFAULT NULL
    , p_parent_table text DEFAULT NULL
    , OUT child_start_time timestamptz
    , OUT child_end_time timestamptz
    , OUT child_start_id bigint
    , OUT child_end_id bigint
    , OUT suffix text
)
RETURNS record

パーティションの破棄

これらの関数は、パーティション構造を元に戻したり、保持ポリシーに従って古いパーティションを削除したりするために使用されます。

undo_partition(
    p_parent_table text
    , p_target_table text
    , p_loop_count int DEFAULT 1
    , p_batch_interval text DEFAULT NULL
    , p_keep_table boolean DEFAULT true
    , p_lock_wait numeric DEFAULT 0
    , p_ignored_columns text[] DEFAULT NULL
    , p_drop_cascade boolean DEFAULT false
    , OUT partitions_undone int
    , OUT rows_undone bigint)
RETURNS record

undo_partition_proc(
    p_parent_table text
    , p_target_table text DEFAULT NULL
    , p_loop_count int DEFAULT NULL
    , p_interval text DEFAULT NULL
    , p_keep_table boolean DEFAULT true
    , p_lock_wait int DEFAULT 0
    , p_lock_wait_tries int DEFAULT 10
    , p_wait int DEFAULT 1
    , p_ignored_columns text[] DEFAULT NULL
    , p_drop_cascade boolean DEFAULT false
    , p_quiet boolean DEFAULT false
)

drop_partition_time(
    p_parent_table text
    , p_retention interval DEFAULT NULL
    , p_keep_table boolean DEFAULT NULL
    , p_keep_index boolean DEFAULT NULL
    , p_retention_schema text DEFAULT NULL
    , p_reference_timestamp timestamptz DEFAULT CURRENT_TIMESTAMP
)
RETURNS int

drop_partition_id(
    p_parent_table text
    , p_retention bigint DEFAULT NULL
    , p_keep_table boolean DEFAULT NULL
    , p_keep_index boolean DEFAULT NULL
    , p_retention_schema text DEFAULT NULL
)
RETURNS int

part_config

このテーブルは pg_partman の主要な設定センターであり、すべてのトップレベルの親テーブルのパーティショニングポリシーとメンテナンス設定を保存します。

part_config_sub

このテーブルはサブパーティションの設定を保存します。その構造は基本的に part_config と同じですが、1 つの重要な違いがあります。それは、sub_parent 列を使用して直属の親テーブルを識別することです。

• sub_parent (text)：サブパーティションセットの親テーブルの名前。このテーブル自体がトップレベルの親テーブルのパーティションです。

• その他の列：partition_interval、retention、premake など、テーブル内の他のすべての列は part_config と同じ意味を持ちますが、そのスコープは現在のサブパーティションセットです。

すべての列の定義を確認する必要はありません。基本的に、pg_partman がサブパーティションセットをメンテナンスするときは、sub_parent をキーとして part_config_sub テーブルの設定行を検索し、その行で定義されたポリシーを適用します。