
- EDB
- PostgreSQL
新ツール Postgres Workload Report によるパフォーマンス診断~データベース管理の未来を共に創る!~
EDB Postgres Workload Reportsは、Postgresデータベースのパフォーマンス診断とトラブルシューティングを強化する新しいツールです。OracleのAWRに似た詳細なレポートを提供し、データベースの問題を迅速に特定・解決できるようサポートします。本記事では概要と利用手順をご紹介します。
|
当記事はエンタープライズDB社のブログ記事の翻訳の紹介および弊社考察を加えています。
この記事では EDB Postgres Advanced Server 14 のサブパーティションを自動作成する新機能について説明します。
1. テンプレートの設定
2. 使用方法
3. テンプレートの変更
4. カタログ情報
5. テーブルスペースの処理
サブパーティションを作成する場合、サブパーティションごとに名前、パーティション条件、テーブルスペース等の詳細を設定する必要があるため、作成に時間がかかる場合があります。EPAS14 の新機能「サブパーティションテンプレート」を使えば、サブパーティションの詳細設定を事前に定義したブループリントを用意できます。このブループリントは、テーブルにパーティションが追加されるたびに使用することができます。
CREATE TABLE コマンドでテンプレートを設定する場合、SUBPARTITION BY 句の後にSUBPARTITION TEMPLATE 句を指定します。
CREATE TABLE <table_name> ( <col_list> ) PARTITION BY <partition_strategy> ( <col> ) SUBPARTITION BY <subpart_strategy>( <col> ) SUBPARTITION TEMPLATE ( <template_description> ) ( <partition_description> )
<template_description> は次のフォーマットで設定します。
<subpart_name> <subpart_bound> [<tablespace_info>]
リストパーティションの場合、パーティション条件は VALUES (<value_list>) の形式で定義します。
SUBPARTITION TEMPLATE (SUBPARTITION a VALUES (1), SUBPARTITION b VALUES (2,7) TABLESPACE ts2 )
レンジパーティションの場合、「VALUES LESS THAN (value)」の形式で条件を定義します。
SUBPARTITION TEMPLATE (SUBPARTITION s1 VALUES LESS THAN (100) TABLESPACE ts1, SUBPARTITION s2 VALUES LESS THAN (200) )
ハッシュパーティションの場合、定義されたパーティションの数に基づいて自動生成されるため、明確なパーティション条件は定義しません。
SUBPARTITION TEMPLATE (SUBPARTITION x TABLESPACE ts1, SUBPARTITION y TABLESPACE ts1 )
サブパーティション定義時にパーティション数を指定することができますが、この動作についての詳細はこちらのブログ で説明をしています。
サブパーティションテンプレートを設定すると、新しいパーティションを作成する時にサブパーティションの記述を省略することができます。CREATE TABLE や ADD PARTITION コマンドで作成されたサブパーティションの名前には、パーティション名が接頭辞として自動的に付けられます。サブパーティションに対応するテーブルの名前は、サブパーティション名の前に親テーブル名が付くことで一意になるようになっています。
以下、CREATE TABLE 文でパーティション表を作成した場合の例です。
CREATE TABLE tbl_ll (col1 NUMBER(4) NOT NULL, col2 number(4)) PARTITION BY LIST (col1) SUBPARTITION BY LIST(col2) SUBPARTITION TEMPLATE (SUBPARTITION a VALUES (1), SUBPARTITION b VALUES (2) ) (PARTITION p1 VALUES (10)); SELECT partition_name, subpartition_name, backing_table, high_value FROM sys.all_tab_subpartitions WHERE TABLE_NAME='TBL_LL' AND PARTITION_NAME = 'P1' ORDER BY 1,2; partition_name | subpartition_name | backing_table | high_value ----------------+-------------------+---------------+------------ P1 | P1_A | tbl_ll_p1_a | 1 P1 | P1_B | tbl_ll_p1_b | 2 (2 rows)
ADD PARTITION 文でパーティションを追加する場合は次のようになります。
ALTER TABLE tbl_ll ADD PARTITION P2 VALUES (25); SELECT partition_name, subpartition_name, backing_table, high_value FROM sys.all_tab_subpartitions WHERE TABLE_NAME='TBL_LL' AND PARTITION_NAME = 'P2' ORDER BY 1,2; partition_name | subpartition_name | backing_table | high_value ----------------+-------------------+---------------+------------ P2 | P2_A | tbl_ll_p2_a | 1 P2 | P2_B | tbl_ll_p2_b | 2 (2 rows)
動的パーティションの場合はパーティション名が自動で生成されるため、テンプレートを使用して作成されたサブパーティションも自動生成された名前が接頭辞として付けられます。パーティション条件やテーブルスペースの情報は定義時に指定された設定が保持されます。
CREATE TABLE tbl_dyn (col1 NUMBER(4) NOT NULL, col2 number(4)) PARTITION BY LIST (col1) AUTOMATIC SUBPARTITION BY LIST(col2) SUBPARTITION TEMPLATE (SUBPARTITION a VALUES (1) TS1, SUBPARTITION b VALUES (2) ) (PARTITION p1 VALUES (10)); INSERT INTO tbl_dyn VALUES (90, 1); SELECT partition_name, subpartition_name, backing_table, tablespace_name, high_value FROM sys.all_tab_subpartitions WHERE TABLE_NAME='TBL_DYN' ORDER BY 1,2; partition_name | subpartition_name | backing_table | tablespace_name | high_value ----------------+-------------------+----------------------+-----------------+------------ P1 | P1_A | tbl_dyn_p1_a | TS1 | 1 P1 | P1_B | tbl_dyn_p1_b | PG_DEFAULT | 2 SYS213140104 | SYS213140105 | tbl_dyn_sys213140105 | TS1 | 1 SYS213140104 | SYS213140106 | tbl_dyn_sys213140106 | PG_DEFAULT | 2 (4 rows)
INSERT された値が、サブパーティションテンプレートのいずれの条件にも適合しない場合、次のようなエラーになり、動的にパーティションが作成されないことには注意が必要です。このような条件に合致しないエントリーを補足するために、テンプレート定義にデフォルトのサブパーティションを1つ用意しておくことをお勧めします。
INSERT INTO tbl_dyn VALUES (90, 5); ERROR: no partition of relation "tbl_dyn_sys213140104" found for row DETAIL: Partition key of the failing row contains (col2) = (5).
サブパーティションテンプレートを変更または設定するには、ALTER TABLE... SET SUBPARTITION TEMPLATE コマンドを使用します。これにより、既存のサブパーティションテンプレート情報が上書きされ、このコマンドの後に作成された全てのパーティションは新しいテンプレートを使用することになります。既存のサブパーティションの設定は変更されません。
ALTER TABLE tbl_ll SET SUBPARTITION TEMPLATE ( SUBPARTITION s1 VALUES (100,500), SUBPARTITION s2 VALUES (200) ); ALTER TABLE tbl_ll ADD PARTITION P3 VALUES (50); SELECT partition_name, subpartition_name, backing_table, high_value FROM sys.all_tab_subpartitions WHERE TABLE_NAME='TBL_LL' AND PARTITION_NAME = 'P3' ORDER BY 1,2; partition_name | subpartition_name | backing_table | high_value ----------------+-------------------+---------------+------------ P3 | P3_S1 | tbl_ll_p3_s1 | 100, 500 P3 | P3_S2 | tbl_ll_p3_s2 | 200 (2 rows)
また、新しいテンプレートは CREATE TABLE 実行時に指定されたサブパーティションの条件に従っている場合にのみ追加することができ、そうでない場合は次のエラーが発生します。
ALTER TABLE tbl_ll SET SUBPARTITION TEMPLATE (
SUBPARTITION s1 VALUES LESS THAN (100),
SUBPARTITION s2 VALUES LESS THAN (200)
);
ERROR: subpartition specification type does not match subpartition definition
サブパーティションテンプレートをリセットする場合は、次のように空のテンプレートを定義するだけです。これにより、サブパーティションの作成方法がデフォルト(従来)の動作になります。
ALTER TABLE tbl_ll SET SUBPARTITION TEMPLATE (); ALTER TABLE tbl_ll ADD PARTITION P4 VALUES (150); SELECT partition_name, subpartition_name, backing_table, high_value FROM sys.all_tab_subpartitions WHERE TABLE_NAME='TBL_LL' AND PARTITION_NAME = 'P4' ORDER BY 1,2; partition_name | subpartition_name | backing_table | high_value ----------------+-------------------+----------------+------------ P4 | SYS0110 | tbl_ll_sys0110 | DEFAULT (1 row)
テンプレートデータは、pg_partitioned_table の partsubparttemplate 列(pg_node_tree型)に格納されています。pg_get_expr 関数または edb_get_subpart_template 関数を使用すると、テンプレートデータを読みやすい形で表示することができます。
SELECT pg_get_expr(partsubparttemplate, 'tbl_ll'::regclass) FROM pg_partitioned_table WHERE partrelid = 'tbl_ll'::regclass; pg_get_expr ----------------------------------------------------------------- SUBPARTITION s1 VALUES (100, 500), SUBPARTITION s2 VALUES (200) (1 row) SELECT edb_get_subpart_template('tbl_ll'::regclass); edb_get_subpart_template ------------------------------------------------------------------- SUBPARTITION s1 VALUES (100, 500), SUBPARTITION s2 VALUES (200) (1 row)
サブパーティションテンプレートの情報は、psql のメタコマンド(\d, \d+)で親テーブルの定義を表示した場合にも出力されます。
\d tbl_ll Partitioned table "public.tbl_ll" Column | Type | Collation | Nullable | Default --------+--------------+-----------+----------+--------- col1 | numeric(4,0) | | not null | col2 | numeric(4,0) | | | Partition key: LIST (col1) AUTOMATIC Subpartiton template: SUBPARTITION s1 VALUES (100, 500), SUBPARTITION s2 VALUES (200) Number of partitions: 6 (Use \d+ to list them.)
サブパーティションテンプレートがリセットされた場合、partsubparttemplate列は NULL に設定されます。
SELECT partsubparttemplate FROM pg_partitioned_table WHERE partrelid = 'tbl_ll'::regclass; partsubparttemplate --------------------- (1 row) SELECT edb_get_subpart_template('tbl_ll'::regclass); edb_get_subpart_template -------------------------- (1 row)
サブパーティションテンプレートで使用されるテーブルスペースはドロップできません。これは、パーティションの追加時にテーブルスペースが使用できず失敗することを防ぐための制限です。
ALTER TABLE tbl_ll SET SUBPARTITION TEMPLATE ( SUBPARTITION s1 VALUES (1500) TABLESPACE ts1, SUBPARTITION s2 VALUES (2000) ); DROP TABLESPACE ts1; ERROR: tablespace "ts1" cannot be dropped because some objects depend on it DETAIL: subpartition template of table tbl_ll ALTER TABLE tbl_ll ADD PARTITION P5 VALUES (300); SELECT partition_name, subpartition_name, high_value, tablespace_name FROM sys.all_tab_subpartitions WHERE TABLE_NAME='TBL_LL' AND PARTITION_NAME = 'P5' ORDER BY 1,2; partition_name | subpartition_name | high_value | tablespace_name ----------------+-------------------+------------+----------------- P5 | P5_S1 | 1500 | TS1 P5 | P5_S2 | 2000 | PG_DEFAULT (2 rows)
この機能は、パーティショニングがパフォーマンスに有効なテーブルを作成する時に非常に便利です。動的パーティションのように、サブパーティションの記述を指定することができない場合でも、サブパーティションの定義を一度記述するだけで何度も使用することができ、時間を大幅に節約することができます。
(以上、EDB社ブログ記事より翻訳)
サブパーティションを含むパーティション表を作成する場合、サブパーティションごとに詳細な設定を定義する必要があり、とても冗長な構文のクエリが必要でした。EPAS14 の新機能「サブパーティションテンプレート」を使うことで簡潔な構文でサブパーティションが定義ができます。また、一度記述した定義を使いまわせるため、メンテナンスの時間やオペレーションミスの削減が期待できます。
EPAS14でサブパーティションをご利用される場合はぜひテンプレート機能をご利用ください。
![]() |
---|
2017年に中途入社。Oracle Database、EDB Postgres/PostgreSQL のサポート経験を経て、2020年からバックサポートを担当。DBとアプリケーションを繋ぐミドルウェア製品のスペシャリスト。トレンドな技術は積極的に触れるほど好奇心旺盛。最近はプロアクティブなサポートを目指して粉骨砕身。趣味はボードゲーム。...show more
■本記事の内容について
本記事に示した定義及び条件は変更される場合があります。あらかじめご了承ください。
■商標に関して
・Oracle®、Java、MySQL及びNetSuiteは、Oracle、その子会社及び関連会社の米国及びその他の国における登録商標です。
・Amazon Web Services、AWS、Powered by AWS ロゴ、[およびかかる資料で使用されるその他の AWS 商標] は、Amazon.com, Inc. またはその関連会社の商標です。
文中の社名、商品名等は各社の商標または登録商標である場合があります。
EDB Postgres Workload Reportsは、Postgresデータベースのパフォーマンス診断とトラブルシューティングを強化する新しいツールです。OracleのAWRに似た詳細なレポートを提供し、データベースの問題を迅速に特定・解決できるようサポートします。本記事では概要と利用手順をご紹介します。
35年以上教育事業を展開しているアシストが新たに取り組み始めた「ポスグレ学園」。連載10回目となる今回の記事では、OSS-DB Gold試験対策問題集 出版の経緯や内容を 新校長 我妻にインタビューしました。
EDB社のAjit Gadge氏を招き「PostgreSQLユーザーに捧ぐ、EDBを使ったDB機能向上とコスト削減の両立」セミナーを開催しました。DB市場の現状やトレンド、EDBの最新動向について紹介しております。アシストセッションのアーカイブ配信の視聴申し込みも可能です。ぜひご覧ください。