pg_partman エクステンションによる PostgreSQL パーティションの管理 - Amazon Relational Database Service

pg_partman エクステンションによる PostgreSQL パーティションの管理

PostgreSQL テーブルパーティションは、データ入力とレポートにおける高性能な処理のためのフレームワークを提供します。大量のデータを非常に速く入力する必要があるデータベースには、パーティションを使用します。またパーティションは、大きなテーブルでより高速のクエリを提供します。パーティションは、必要とする I/O リソースが少ないため、データベースインスタンスに影響を与えずにデータを維持するのに役立ちます。

パーティションを使用すると、データをカスタムサイズのチャンクに分割して処理することができます。例えば、時間単位、日単位、週単位、月単位、四半期単位、年単位、カスタム、またはこれらの組み合わせなどの範囲のパーティションの時系列データを選択できます。時系列データの例では、テーブルを時間単位でパーティション化した場合、各パーティションには 1 時間のデータが含まれます。時系列テーブルを日単位でパーティション化した場合、パーティションには 1 日分のデータが保持されます。パーティションキーは、パーティションのサイズを制御します。

パーティション化されたテーブルで INSERT SQL コマンドまたは UPDATE SQL コマンドを使用すると、データベースエンジンはデータを適切なパーティションにルーティングします。データを格納する PostgreSQL テーブルパーティションは、メインテーブルの子テーブルです。

データベースクエリの読み取り中、PostgreSQL オプティマイザはクエリの WHERE 句を調べ、可能な場合、関連するパーティションだけにデータベーススキャンを行うよう指示します。

バージョン 10 以降、PostgreSQL は宣言的なパーティショニングを使用してテーブルパーティションを実装します。これは、ネイティブ PostgreSQL パーティションとも言います。PostgreSQL バージョン 10 より前は、トリガーを使用してパーティションを実装していました。

PostgreSQL テーブルパーティションは、次の機能を提供します。

  • 新しいパーティションの作成がいつでも可能。

  • 可変のパーティション範囲。

  • データ定義言語 (DDL) ステートメントを使用して、取り外し可能かつ再接続可能なパーティション。

    例えば、デタッチ可能なパーティションは、メインパーティションから履歴データを削除し、履歴データを分析用に保持する場合に便利です。

  • 新しいパーティションは、親のデータベーステーブルの以下のプロパティを継承します。

    • インデックス

    • プライマリキー (パーティションキー列を含める必要があります)

    • 外部キー

    • 検査制約

    • 参照

  • フルテーブルまたは各特定のパーティションのインデックスの作成。

個々のパーティションのスキーマを変更することはできません。ただし、パーティションに伝播される親テーブル (新しい列の追加など) は変更できます。

PostgreSQL pg_partman エクステンションの概要

PostgreSQL pg_partman エクステンションを使用すると、テーブルパーティションの作成とメンテナンスを自動化できます。一般的な情報については、pg_partman ドキュメントの「PG Partition Manager」を参照してください。

注記

pg_partman エクステンションは、RDS for PostgreSQL のバージョン 12.5 以降でサポートされています。

各パーティションを手動で作成する代わりに、次の設定で pg_partman を設定します。

  • パーティション化するテーブル

  • パーティションタイプ

  • パーティションキー

  • パーティションの粒度

  • パーティションの事前作成および管理オプション

PostgreSQL のパーティション化されたテーブルの作成後、create_parent 関数を呼び出して、そのテーブルを pg_partman に登録します。これにより、関数に渡すパラメータに基づいて、必要なパーティションを作成します。

pg_partman エクステンションには、設定したスケジュールに基づいて呼び出しを行うことでパーティションを自動的に管理できる run_maintenance_proc 関数も用意されています。必要に応じて適切なパーティションが作成されるようにするには、この関数を定期的に (時間単位など) 実行するようにスケジュールします。また、パーティションが自動的に削除されるようにすることもできます。

pg_partman エクステンションの有効化

パーティションを管理する同じ PostgreSQL DB インスタンス内に複数のデータベースがある場合は、データベースごとに pg_partman エクステンションを有効にします。特定のデータベースで pg_partman エクステンションを有効にするには、パーティションメンテナンススキーマを作成した上で、次のように pg_partman エクステンションを作成します。

CREATE SCHEMA partman; CREATE EXTENSION pg_partman WITH SCHEMA partman;
注記

pg_partman エクステンションを作成するには、rds_superuser 権限が必要です。

次のようなエラーが表示された場合は、アカウントに rds_superuser 権限を付与するか、スーパーユーザーアカウントを使用します。

ERROR: permission denied to create extension "pg_partman" HINT: Must be superuser to create this extension.

rds_superuser 権限を付与するには、スーパーユーザーアカウントを使用して接続し、以下のコマンドを実行します。

GRANT rds_superuser TO user-or-role;

pg_partman エクステンションの使用方法を示す例では、次のサンプルのデータベーステーブルとパーティションを使用します。このデータベースでは、タイムスタンプに基づいてパーティション化されたテーブルを使用します。スキーマ data_mart には、events という列を持つ created_at という名前のテーブルが含まれています。この events テーブルには、次の設定が含まれています。

  • プライマリキー event_id および created_at。パーティションのガイドに使用される列を含める必要があります。

  • ck_valid_operation テーブル列に値を適用するための検査制約 operation

  • 2 つの外部キー。1 つ (fk_orga_membership)) は外部テーブル organization で、もう 1 つ (fk_parent_event_id) は自己参照外部キーです。

  • 2 つのインデックス。1 つ (idx_org_id) は外部キー用で、もう 1 つ (idx_event_type) はイベントタイプ用です。

次の DDL ステートメントは、これらのオブジェクトを作成し、これらは各パーティションに自動的に含まれます。

CREATE SCHEMA data_mart; CREATE TABLE data_mart.organization ( org_id BIGSERIAL, org_name TEXT, CONSTRAINT pk_organization PRIMARY KEY (org_id) ); CREATE TABLE data_mart.events( event_id BIGSERIAL, operation CHAR(1), value FLOAT(24), parent_event_id BIGINT, event_type VARCHAR(25), org_id BIGSERIAL, created_at timestamp, CONSTRAINT pk_data_mart_event PRIMARY KEY (event_id, created_at), CONSTRAINT ck_valid_operation CHECK (operation = 'C' OR operation = 'D'), CONSTRAINT fk_orga_membership FOREIGN KEY(org_id) REFERENCES data_mart.organization (org_id), CONSTRAINT fk_parent_event_id FOREIGN KEY(parent_event_id, created_at) REFERENCES data_mart.events (event_id,created_at) ) PARTITION BY RANGE (created_at); CREATE INDEX idx_org_id ON data_mart.events(org_id); CREATE INDEX idx_event_type ON data_mart.events(event_type);

create_parent 関数を使用したパーティションの設定

pg_partman エクステンションを有効にした後、create_parent 関数を使用して、パーティションメンテナンススキーマ内でパーティションの設定を行います。以下の例では、events で作成される pg_partman エクステンションの有効化 テーブルの例を使用します。create_parent 関数を次のように呼び出します。

SELECT partman.create_parent( p_parent_table => 'data_mart.events', p_control => 'created_at', p_type => 'native', p_interval=> 'daily', p_premake => 30);

パラメータは次のとおりです。

  • p_parent_table - 親パーティションテーブル。このテーブルは既に存在しており、スキーマを含めて完全修飾である必要があります。

  • p_control - パーティションのベースとなる列。データタイプは、整数または時間ベースである必要があります。

  • p_type - タイプは 'native' または 'partman' です。通常、パフォーマンスの向上と柔軟性のためには、native タイプを使用します。partman タイプは継承により変化します。

  • p_interval - 各パーティションの時間間隔または整数の範囲。この値の例としては、daily、時間単位その他があります。

  • p_premake - 新しい挿入をサポートするために事前に作成するパーティションの数。

create_parent 関数の詳細については、pg_partman ドキュメントの「Creation Functions (関数の作成)」を参照してください。

run_maintenance_proc 関数を使用したパーティションのメンテナンス設定

パーティションのメンテナンスオペレーションを実行して、自動的に新しいパーティションの作成、パーティションのデタッチ、または古いパーティションの削除ができます。パーティションのメンテナンスは、内部のスケジューラをスタートする pg_partman および pg_cron エクステンション の run_maintenance_proc 関数により異なります。pg_cron スケジューラは、データベースで定義された SQL ステートメント、関数、および手順を自動的に実行します。

次の例では、events で作成した pg_partman エクステンションの有効化 テーブルの例を使用して、パーティションのメンテナンスオペレーションを自動的に実行するように設定します。前提条件にあるように、DB インスタンスのパラメータグループで、shared_preload_libraries パラメータに pg_cron を追加します。

CREATE EXTENSION pg_cron; UPDATE partman.part_config SET infinite_time_partitions = true, retention = '3 months', retention_keep_table=true WHERE parent_table = 'data_mart.events'; SELECT cron.schedule('@hourly', $$CALL partman.run_maintenance_proc()$$);

その後、前の例についてのステップバイステップの説明を確認できます。

  1. DB インスタンスに関連付けられているパラメータグループを変更して、pg_cronshared_preload_libraries パラメータ値に追加します。この変更を有効にするには、DB インスタンスの再起動が必要です。詳細については、「Amazon RDS の DB パラメータグループのパラメータの変更」を参照してください。

  2. CREATE EXTENSION pg_cron; のアクセス許可を持つアカウントを使用して、コマンド rds_superuser を実行します。これにより、pg_cron エクステンションが有効になります。詳細については、「PostgreSQL pg_cron エクステンションによるメンテナンスのスケジューリング」を参照してください。

  3. UPDATE partman.part_config コマンドを実行して、data_mart.events テーブルの pg_partman 設定を調整します。

  4. SET . . .コマンドを実行して、以下の句を使用しながら、data_mart.events テーブルを設定します。

    1. infinite_time_partitions = true, - 制限なしで新しいパーティションを自動的に作成できるようにテーブルを設定します。

    2. retention = '3 months', - テーブルの最大保持期間を 3 か月に設定します。

    3. retention_keep_table=true - 保存期間の期限が過ぎてもテーブルが自動的に削除されないように、テーブルを構成します。代わりに、保持期間より古いパーティションは、親テーブルからのみデタッチされます。

  5. SELECT cron.schedule . . .コマンドを実行して、pg_cron 関数を呼び出します。この呼び出は、pg_partman メンテナンスプロシージャの partman.run_maintenance_proc が、スケジューラにより実行される頻度を定義します。この例では、プロシージャは 1 時間ごとに実行されます。

run_maintenance_proc 関数の詳細については、pg_partman ドキュメントの「Maintenance Functions (メンテナンス機能)」を参照してください。