EPAS14 新機能「サブパーティションテンプレート」の使い方

当記事はエンタープライズDB社のブログ記事の翻訳の紹介および弊社考察を加えています。

Beena Emerson . (2019, Dec, 30). How to Create Subpartition Template in EDB Postgres Advanced Server 14.
The EDB Blog.
https://www.enterprisedb.com/postgres-tutorials/how-create-subpartition-template-edb-postgres-advanced-server-14

この記事では EDB Postgres Advanced Server 14 のサブパーティションを自動作成する新機能について説明します。

　1. テンプレートの設定
　2. 使用方法
　3. テンプレートの変更
　4. カタログ情報
　5. テーブルスペースの処理

サブパーティションを作成する場合、サブパーティションごとに名前、パーティション条件、テーブルスペース等の詳細を設定する必要があるため、作成に時間がかかる場合があります。EPAS14 の新機能「サブパーティションテンプレート」を使えば、サブパーティションの詳細設定を事前に定義したブループリントを用意できます。このブループリントは、テーブルにパーティションが追加されるたびに使用することができます。

1. テンプレートの設定

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＞]

<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
　)

サブパーティション定義時にパーティション数を指定することができますが、この動作についての詳細はこちらのブログで説明をしています。

2. 使用方法

サブパーティションテンプレートを設定すると、新しいパーティションを作成する時にサブパーティションの記述を省略することができます。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).

3. テンプレートの変更

サブパーティションテンプレートを変更または設定するには、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)

4. カタログ情報

テンプレートデータは、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)

5. テーブルスペースの取り扱い

サブパーティションテンプレートで使用されるテーブルスペースはドロップできません。これは、パーティションの追加時にテーブルスペースが使用できず失敗することを防ぐための制限です。

　
　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. またはその関連会社の商標です。
　　文中の社名、商品名等は各社の商標または登録商標である場合があります。