適用対象:
Azure Database for PostgreSQL - フレキシブル サーバー
この記事では、PostgreSQL Partition Manager (pg_partman) 拡張機能を使用して Azure Database for PostgreSQL - フレキシブル サーバーを最適化する方法について説明します。
データベース内のテーブルが大きくなると、バキュームされる頻度、占有する領域の量、インデックスの効率を維持する方法を管理するが難しくなります。 この困難さにより、クエリの速度が低下し、パフォーマンスに影響を与える可能性があります。 このような状況に対する解決策は、大きなテーブルをパーティション分割することです。
この記事では、pg_partman を使用して、Azure Database for PostgreSQL フレキシブル サーバーでテーブルの範囲ベースのパーティションを作成する方法について説明します。
前提条件
pg_partman 拡張機能を有効にするには、次の手順に従います。
Azure portal で、
azure.extensionsのサーバー パラメーターの一覧でpg_partmanを選択します。
CREATE EXTENSION pg_partman;関連する
pg_partman_bgw拡張機能をshared_preload_librariesに含めます。 これは、スケジュールされた関数run_maintenance()を提供します。part_configでautomatic_maintenanceがONに設定されたパーティション セットに対応します。
Azure portal でサーバー パラメータを使って、バックグラウンド ライター (BGW) プロセスに影響する次の構成オプションを変更できます。
pg_partman_bgw.dbname: 必須。 このパラメータには、run_maintenance()を実行する必要がある 1 つ以上のデータベースを含める必要があります。 複数のデータベースがある場合は、コンマ区切りのリストを使用します。 何も設定しないと、pg_partman_bgwはプロシージャを実行しません。pg_partman_bgw.interval:run_maintenance()プロシージャ呼び出し間隔 (秒)。 既定値は3600(1 時間) です。 この値は、プロジェクトの要件に基づいて更新できます。pg_partman_bgw.role: プロシージャrun_maintenance()が実行されるロール。 既定値はpostgresです。 許可されるロール名は 1 つだけです。pg_partman_bgw.analyze:run_maintenance()に対するp_analyze引数と同じ用途です。 既定では、OFFに設定されています。pg_partman_bgw.jobmon:run_maintenance()に対するp_jobmon引数と同じ用途です。 既定では、ONに設定されています。
Note
ID 機能でシーケンスが使われている場合、親テーブルからのデータには新しいシーケンス値が設定されます。 データが子テーブルに直接追加される場合は、新しいシーケンス値は生成されません。
pg_partman拡張機能は、テンプレートを使ってテーブルがUNLOGGEDかどうかを制御します。 つまり、ALTER TABLEコマンドでパーティション セットのこの状態を変更することはできません。 テンプレートで状態を変更すると、将来のすべてのパーティションに適用できます。 ただし、既存の子テーブルの場合は、ALTER TABLEコマンドを手動で使用する必要があります。 このバグが、その理由を示しています。
アクセス許可の設定
スーパーユーザー ロールは、pg_partman では必要ありません。 唯一の要件は、pg_partman 関数を実行するロールが、新しいオブジェクトが作成されるすべてのパーティション セットとスキーマに対する所有権を持っていることです。
pg_partman 用に別のロールを作成し、pg_partman が操作するスキーマとすべてのオブジェクトに対する所有権をそれに付与することをお勧めします。
CREATE ROLE partman_role WITH LOGIN;
CREATE SCHEMA partman;
GRANT ALL ON SCHEMA partman TO partman_role;
GRANT ALL ON ALL TABLES IN SCHEMA partman TO partman_role;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA partman TO partman_role;
GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA partman TO partman_role;
GRANT ALL ON SCHEMA <partition_schema> TO partman_role;
GRANT TEMPORARY ON DATABASE <databasename> to partman_role; -- This allows temporary table creation to move data.
パーティションの作成
pg_partman 拡張機能は範囲タイプのパーティションのみをサポートし、トリガー ベースのパーティションはサポートしません。 次のコードは、pg_partman がテーブルのパーティション分割にどのように役立つのかを示します。
CREATE SCHEMA partman;
CREATE TABLE partman.partition_test
(a_int INT, b_text TEXT,c_text TEXT,d_date TIMESTAMP DEFAULT now())
PARTITION BY RANGE(d_date);
CREATE INDEX idx_partition_date ON partman.partition_test(d_date);
create_parent 関数を使うと、パーティション テーブルに必要なパーティションの数を設定できます。
SELECT public.create_parent(
p_parent_table := 'partman.partition_test',
p_control := 'd_date',
p_type := 'native',
p_interval := 'daily',
p_premake :=20,
p_start_partition := (now() - interval '10 days')::date::text
);
UPDATE public.part_config
SET infinite_time_partitions = true,
retention = '1 hour',
retention_keep_table=true
WHERE parent_table = 'partman.partition_test';
上記のコマンドは、ネイティブパーティション分割を使用して、p_parent_table を p_control 列に基づいて小さな部分に分割します。 (もう 1 つのオプションはトリガー ベースのパーティション分割ですが、pg_partman では現在サポートされていません。)パーティションは毎日作成されます。
この例では、既定値の 4 を使用する代わりに、将来のために 20 個のパーティションを事前に作成します。 また、パーティションを開始する必要がある過去の日付を示す p_start_partition も指定します。
create_parent() 関数では、part_config と part_config_sub の 2 つのテーブルが設定されます。 メンテナンス関数 run_maintenance() があります。 定期的に実行するようにこのプロシージャの cron ジョブをスケジュールできます。 この関数は、part_config テーブル内のすべての親テーブルをチェックし、それらの新しいパーティションを作成するか、テーブル セットのアイテム保持ポリシーを実行します。
pg_partman の関数とテーブルの詳細については、GitHub の「PostgreSQL Partition Manager 拡張機能」ドキュメントを参照してください。
pg_partman_bgw 拡張機能を使ってバックグラウンドで run_maintenance() が実行されるたびに新しいパーティションを作成するには、次の UPDATE ステートメントを実行します。
UPDATE partman.part_config SET premake = premake+1 WHERE parent_table = 'partman.partition_test';
premake が同じで、run_maintenance() プロシージャが実行される場合、その日に対して新しいパーティションは作成されません。 premake の定義は現在の日が起点なので、次の日に run_maintenance() 関数を実行すると、1 日分の新しいパーティションが作成されます。
次の INSERT INTO コマンドを使用して、毎月 100,000 行を挿入します。
INSERT INTO partman.partition_test SELECT GENERATE_SERIES(1,100000),GENERATE_SERIES(1, 100000) || 'abcdefghijklmnopqrstuvwxyz',
GENERATE_SERIES(1, 100000) || 'zyxwvutsrqponmlkjihgfedcba', GENERATE_SERIES (timestamp '2024-03-01',timestamp '2024-03-30', interval '1 day ') ;
INSERT INTO partman.partition_test SELECT GENERATE_SERIES(100000,200000),GENERATE_SERIES(100000,200000) || 'abcdefghijklmnopqrstuvwxyz',
GENERATE_SERIES(100000,200000) || 'zyxwvutsrqponmlkjihgfedcba', GENERATE_SERIES (timestamp '2024-04-01',timestamp '2024-04-30', interval '1 day') ;
INSERT INTO partman.partition_test SELECT GENERATE_SERIES(200000,300000),GENERATE_SERIES(200000,300000) || 'abcdefghijklmnopqrstuvwxyz',
GENERATE_SERIES(200000,300000) || 'zyxwvutsrqponmlkjihgfedcba', GENERATE_SERIES (timestamp '2024-05-01',timestamp '2024-05-30', interval '1 day') ;
INSERT INTO partman.partition_test SELECT GENERATE_SERIES(300000,400000),GENERATE_SERIES(300000,400000) || 'abcdefghijklmnopqrstuvwxyz',
GENERATE_SERIES(300000,400000) || 'zyxwvutsrqponmlkjihgfedcba', GENERATE_SERIES (timestamp '2024-06-01',timestamp '2024-06-30', interval '1 day') ;
INSERT INTO partman.partition_test SELECT GENERATE_SERIES(400000,500000),GENERATE_SERIES(400000,500000) || 'abcdefghijklmnopqrstuvwxyz',
GENERATE_SERIES(400000,500000) || 'zyxwvutsrqponmlkjihgfedcba', GENERATE_SERIES (timestamp '2024-07-01',timestamp '2024-07-30', interval '1 day') ;
PostgreSQL で次のコマンドを実行して、作成されたパーティションを確認します。
\d+ partman.partition_test;
実行した SELECT ステートメントの出力を次に示します。
メンテナンス手順を手動で実行する
pg_partman_bgw を使用する代わりに、partman.run_maintenance() コマンドを手動で実行できます。 メンテナンス手順を手動で実行するには、次のコマンドを使います。
SELECT partman.run_maintenance(p_parent_table:='partman.partition_test');
警告
パーティションを作成する前にデータを挿入すると、データは既定のパーティションに格納されます。 既定のパーティションに、後で作成する新しいパーティションに属するデータがある場合、既定パーティション違反エラーが発生し、プロシージャは機能しません。 先にお勧めしたように premake の値を変更して、プロシージャを実行します。
メンテナンス プロシージャをスケジュールする
pg_cron を使ってメンテナンス プロシージャを実行します。
まず、サーバーで
pg_cronを有効にします。 Azure portal で、azure. extensions、shared_preload_libraries、およびcron.database_nameサーバー パラメーターにpg_cronを追加します。
[保存] ボタンを選んで、デプロイを完了します。
デプロイが完了すると、
pg_cronが自動的に作成されます。 インストールしようとすると、次のメッセージが表示されます。CREATE EXTENSION pg_cron;ERROR: extension "pg_cron" already existscronジョブをスケジュールするには、次のコマンドを使用します。SELECT cron.schedule_in_database('sample_job','@hourly', $$SELECT partman.run_maintenance(p_parent_table:= 'partman.partition_test')$$,'postgres');すべての
cronジョブを表示するには、次のコマンドを使用します。SELECT * FROM cron.job;-[ RECORD 1 ]----------------------------------------------------------------------- jobid | 1 schedule | @hourly command | SELECT partman.run_maintenance(p_parent_table:= 'partman.partition_test') nodename | /tmp nodeport | 5432 database | postgres username | postgres active | t jobname | sample_jobジョブの実行履歴を確認するには、次のコマンドを使用します。
SELECT * FROM cron.job_run_details;ジョブをまだ実行していないため、結果には 0 個のレコードが表示されます。
cronジョブのスケジュールを解除するには、次のコマンドを使用します。SELECT cron.unschedule(1);
よく寄せられる質問
指定した間隔に基づいて
pg_partman_bgwがメンテナンス プロシージャを実行しないのはなぜですか?サーバー パラメータ
pg_partman_bgw.dbnameを調べて、適切なデータベース名に更新します。 また、サーバー パラメータpg_partman_bgw.roleを調べて、適切なロールを指定します。 また、Postgres ではなく、拡張機能の作成と同じユーザーを使ってサーバーに接続していることを確認する必要もあります。pg_partman_bgwがメンテナンス プロシージャを実行しているときにエラーが発生します。 なぜでしょうか?前の回答を参照してください。
前日から開始するようにパーティションを設定するにはどうすればよいですか?
p_start_partition関数は、パーティションの作成を開始する必要がある日付を指します。 次のコマンドを実行します。SELECT public.create_parent( p_parent_table := 'partman.partition_test', p_control := 'd_date', p_type := 'native', p_interval := 'daily', p_premake :=20, p_start_partition := (now() - interval '10 days')::date::text );