Database Support Blog

  • EDB
  • PostgreSQL
2022.02.09

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

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

この記事では 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とアプリケーションを繋ぐミドルウェア製品のスペシャリスト。トレンドな技術は積極的に触れるほど好奇心旺盛。最近はプロアクティブなサポートを目指して粉骨砕身。趣味はボードゲーム。


■本記事の内容について
 本記事に示した定義及び条件は変更される場合があります。あらかじめご了承ください。

■商標に関して
 OracleとJavaは、Oracle Corporation 及びその子会社、関連会社の米国及びその他の国における登録商標です。
 文中の社名、商品名等は各社の商標または登録商標である場合があります。


Oracle Databaseライセンスのご相談ならアシスト

関連している記事

  • PostgreSQL
  • EDB
2022.05.25

アシストがPostgreSQLグローバル開発グループのSponsor一覧に掲載されたので、中の人に話を聞いてみた!(前編)

アシストは2009年からPostgreSQLのプロダクトサポートを開始、2010年に日本PostgreSQLユーザー会への協賛会員として参画、2012年にPostgreSQL エンタープライズ・コンソーシアムに参画しています。現在JPUGで活躍する田中・喜田の両名がどのような活動をしているのか2回にわたってお届けします。

  • Oracle Database
  • Oracle Cloud
  • PostgreSQL
  • AWS
  • Exadata
2022.05.24

入社2年目がレポート!Cloud Database Days 2022技術セッションの見どころ

2022年5月26日~6月3日に開催される「Cloud Database Days 2022」。その中でも10個に分かれた技術セッションについて、ポイントを押さえてそれぞれご紹介いたします!

  • EDB
  • PostgreSQL
  • AWS
2022.04.13

クラウドへのデータベース移行を検討中の方必見!「EDB」とは?

2022年4月22日(金)に開催する「アシスト流 EDB活用術 on AWS」の内容をチラ見せ。「EDBって何?」という方から「EDBは知ってるけどクラウド上でもEDB??」という方まで、ぜひお申し込みください。

ページの先頭へ戻る