宣言的パーティショニングテーブルの気になるところを調べました。

本記事の概要

PostgreSQL にはパーティションテーブルという機能があります。簡単にいうと、テーブルを水平分割して効率的に扱えるようにする機能です。
パーティションテーブルは PostgreSQL 8.1 から利用できますが、PostgreSQL 10 から導入された宣言的パーティショニングという機能（概念？）が使いやすいです。
本記事は、その宣言的パーティショニングについて、気になる点を調べてみたという内容になっています。

確認した PostgreSQL のバージョンは 13.6 です。
宣言的パーティショニングは、バージョンごとに改良されていますので、過去のバージョンでは本記事と同じ動作をしない可能性がありますのでご注意ください。

レンジパーティションテーブル

まずはレンジパーティションテーブルです。
レンジパーティションテーブルは、その名の通り、パーティションキーに設定されたカラムの値をもってテーブルを分割します。

親テーブルを作成

postgres=# CREATE TABLE table_range_p (id INTEGER PRIMARY KEY, no INTEGER, val TEXT) PARTITION BY RANGE (id); 
CREATE TABLE

・親テーブル table_range_p を作成しました。
・パーティションキーはカラム「id」です。

子テーブルを作成

postgres=# CREATE TABLE table_range_c_000_100 PARTITION OF table_range_p FOR VALUES FROM ( 0 ) TO ( 100 );
CREATE TABLE

postgres=# CREATE TABLE table_range_c_100_200 PARTITION OF table_range_p FOR VALUES FROM ( 100 ) TO ( 200 );
CREATE TABLE

postgres=# CREATE TABLE table_range_c_200_300 PARTITION OF table_range_p FOR VALUES FROM ( 200 ) TO ( 300 );
CREATE TABLE

・子テーブルを 3 つ作成しました。

レンジパーティションテーブルの FROM と TO が、その値を含むのか含まないのか

カラムの値は FROM - TO で指定するのですが、FROM の値が「以上」なのか「超える」なのか、 TO の値が「以下」なのか「未満」なのかをいつも忘れてしまうので、改めて調べてみました。

親テーブルに対して INSERT 文を実行し、子テーブルにデータが追加されるのかを確認してみます。

postgres=# INSERT INTO table_range_p VALUES (0, 0, 'このデータが追加できれば、FROM は「以上」');
INSERT 0 1

postgres=# SELECT * FROM table_range_p;
 id | no |                    val                    
----+----+-------------------------------------------
  0 |  0 | このデータが追加できれば、FROM は「以上」
(1 row)

postgres=# SELECT * FROM table_range_c_000_100;
 id | no |                    val                    
----+----+-------------------------------------------
  0 |  0 | このデータが追加できれば、FROM は「以上」
(1 row)

・無事にインサートできましたので、FROM は「以上」であることが分かりました。

次は明示的に子テーブルにデータをインサートして、TO が「以下」か「未満」かを確認します。

postgres=# INSERT INTO table_range_c_000_100 VALUES (100, 100, 'このデータが追加できれば、TO は「以下」');
ERROR:  new row for relation "table_c_000_100" violates partition constraint
DETAIL:  Failing row contains (100, 100, TO はその値を含むのか).

・残念ながらインサートがエラーとなりましたので、TOは「未満」であることが分かりました。

ということで、レンジパーティションテーブルの範囲は
「FROM <= 値 < TO」
ということになります。

レンジパーティションテーブルの範囲を重複させるとどうなるか

次にレンジパーティションテーブルの範囲を重複させた場合の挙動を確認します。

テーブルの作成

postgres=# DROP TABLE table_range_p;
DROP TABLE

・先ほど、作成した親テーブル table_range_p を一旦削除します。

postgres=# DROP TABLE table_range_c_000_100;
ERROR:  table "table_range_c_000_100" does not exist

・親テーブルを削除すると、自動的に子テーブルも削除されます。

postgres=# CREATE TABLE table_range_p (id INTEGER PRIMARY KEY, no INTEGER, val TEXT) PARTITION BY RANGE (id); 
CREATE TABLE

postgres=# CREATE TABLE table_range_c_000_100 PARTITION OF table_range_p FOR VALUES FROM ( 0 ) TO ( 100 );
CREATE TABLE

・親テーブル table_range_p を作りなおして、子テーブル table_range_c_000_100 も作りなおしました。

重複した範囲の子テーブルを作成するとどうなるか

ここで、table_range_c_000_100 と範囲が重複するように新たに子テーブルを作成してみます。

postgres=# CREATE TABLE table_range_c_99_200 PARTITION OF table_p FOR VALUES FROM ( 99 ) TO ( 200 );
ERROR:  partition "table_range_c_99_200" would overlap partition "table_c_000_100"

・当然ながらエラーが発生しました。

レンジパーティションテーブルの範囲が重複するような子テーブルの作成はできないことを確認しました。

パーティションキーに該当するカラムの値を変更して、別の子テーブルに移動できるか

table_range_p のカラム「id」がパーティションキーとなっています。
この id に格納している値を UPDATE 文で更新することで、別の子テーブルに移動されるかを確認してみます。

postgres=# SELECT * FROM table_range_c_000_100 ;
 id | no |         val          
----+----+----------------------
  1 |  1 | 子テーブル移動テスト
(1 row)

上記の通り、子テーブル table_range_c_000_100 に 1 件データが格納されています。
id の値を UPDATE 文で変更し、別の子テーブルに移動するかを確認してみます。

postgres=# UPDATE table_range_p SET id = 100 WHERE id = 1;
UPDATE 1

・id を 1 から 100 に変更しました。

postgres=# SELECT * FROM table_range_c_000_100 ;
 id | no | val 
----+----+-----
(0 rows)

postgres=# SELECT * FROM table_range_c_100_200 ;
 id  | no |         val          
-----+----+----------------------
 100 |  1 | 子テーブル移動テスト
(1 row)

・上記の通り、無事に table_range_c_000_100 から table_range_c_100_200 へデータが移動しています。

リストパーティションテーブル

リストパーティションテーブルはパーティションキーに設定されたカラムの値によって、テーブルを分割します。
リストパーティションテーブルについて気になった点を調べてみます。

親テーブルのパーティションキーではないカラムに主キーを設定できるかどうか

seq_id を主キーとした親テーブル table_list_p を作成します。

postgres=# CREATE TABLE table_list_p (seq_id INTEGER PRIMARY KEY, kind_code INTEGER, name TEXT) PARTITION BY LIST (kind_code);
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  PRIMARY KEY constraint on table "table_list_p" lacks column "kind_code" which is part of the partition key.

・テーブル作成がエラーとなりました。

今度は、seq_id と kind_code を主キーとして、親テーブル table_list_p を作成してみます。

postgres=# CREATE TABLE table_list_p (seq_id INTEGER, kind_code INTEGER, name TEXT, PRIMARY KEY (seq_id, kind_code)) PARTITION BY LIST (kind_code);
CREATE TABLE

・無事に親テーブル table_list_p が作成されました。

リストパーティションテーブルでは、パーティションキーではないカラムを主キーとできないことが分かりました。

子テーブルのパーティションキーではないカラムに主キーを設定できるかどうか

親テーブルでは駄目でしたが、子テーブルではパーティションキー以外のカラムを主キーとして定義できるかを確認します。

テーブルの作成

postgres=# CREATE TABLE table_list_p (seq_id INTEGER, kind_code INTEGER, name TEXT) PARTITION BY LIST (kind_code);
CREATE TABLE

postgres=# CREATE TABLE table_list_c_0 PARTITION OF table_list_p FOR VALUES IN (0);
CREATE TABLE

postgres=# CREATE TABLE table_list_c_1 PARTITION OF table_list_p FOR VALUES IN (1);
CREATE TABLE

postgres=# CREATE TABLE table_list_c_2 PARTITION OF table_list_p FOR VALUES IN (2);
CREATE TABLE

・親テーブル table_list_p に対して、子テーブルを 3 つ作成しました。
・パーティションキーは kind_code です。

ALTER TABLE コマンドで子テーブルに主キーを追加してみます。

postgres=# ALTER TABLE table_list_c_0 ADD CONSTRAINT table_list_c_0_pkey PRIMARY KEY (seq_id);
ALTER TABLE

postgres=# ALTER TABLE table_list_c_1 ADD CONSTRAINT table_list_c_1_pkey PRIMARY KEY (seq_id);
ALTER TABLE

postgres=# ALTER TABLE table_list_c_2 ADD CONSTRAINT table_list_c_2_pkey PRIMARY KEY (seq_id);
ALTER TABLE

・無事に ALTER TABLE コマンドが完了しました。

子テーブルではパーティションキー以外のカラムでも、主キーを設定できることを確認しました。
このままデータを追加して、挙動を確認してみます。

postgres=#  INSERT INTO table_list_p VALUES (0, 0, 'レコード0_0');
INSERT 0 1

postgres=#  INSERT INTO table_list_p VALUES (1, 1, 'レコード1_1');
INSERT 0 1

postgres=#  INSERT INTO table_list_p VALUES (2, 2, 'レコード2_2');
INSERT 0 1

postgres=# SELECT * FROM table_list_p ORDER BY seq_id;
 seq_id | kind_code |    name     
--------+-----------+-------------
      0 |         0 | レコード0_0
      1 |         1 | レコード1_1
      2 |         2 | レコード2_2
(3 rows)

postgres=# SELECT * FROM table_list_c_0;
 seq_id | kind_code |    name     
--------+-----------+-------------
      0 |         0 | レコード0_0
(1 row)

postgres=# SELECT * FROM table_list_c_1;
 seq_id | kind_code |    name     
--------+-----------+-------------
      1 |         1 | レコード1_1
(1 row)

postgres=# SELECT * FROM table_list_c_2;
 seq_id | kind_code |    name     
--------+-----------+-------------
      2 |         2 | レコード2_2
(1 row)

・それぞれの子テーブルにデータが追加されました。

ここで親テーブル table_list_p に対して、seq_id が重複するデータを追加してみます。

postgres=#  INSERT INTO table_list_p VALUES (0, 1, 'レコード0_1');
INSERT 0 1

postgres=# SELECT * FROM table_list_p ORDER BY seq_id;
 seq_id | kind_code |    name     
--------+-----------+-------------
      0 |         0 | レコード0_0
      0 |         1 | レコード0_1
      1 |         1 | レコード1_1
      2 |         2 | レコード2_2
(4 rows)

・登録できてしまいました。

子テーブルごとに主キー制約が定義されていますので、子テーブルごとに seq_id が重複しなければデータは追加できるようです。

別の子テーブルに移動できるか

リストパーティションテーブルでも、子テーブル間のデータ移動が可能かを確認します。

postgres=# UPDATE table_list_p SET kind_code = 2, name = 'レコード0_1 から レコード0_2 に変更' WHERE seq_id = 0 AND kind_code = 1;
UPDATE 1

postgres=# SELECT * FROM table_list_c_0 ORDER BY seq_id;
 seq_id | kind_code |    name     
--------+-----------+-------------
      0 |         0 | レコード0_0
(1 row)

postgres=# SELECT * FROM table_list_c_1 ORDER BY seq_id;
 seq_id | kind_code |    name     
--------+-----------+-------------
      1 |         1 | レコード1_1
(1 row)

postgres=# SELECT * FROM table_list_c_2 ORDER BY seq_id;
 seq_id | kind_code |                name                 
--------+-----------+-------------------------------------
      0 |         2 | レコード0_1 から レコード0_2 に変更
      2 |         2 | レコード2_2
(2 rows)

・table_list_c_1 から table_list_c_2 にデータが移動されていることを確認しました。

ハッシュパーティションテーブル

最後はハッシュパーティションテーブルです。
ハッシュパーティションテーブルはパーティションキーのカラムの値に対して、特定の計算方法で算出された「あまり（REMAINDER）」をもとにテーブルを分割します。
ちなみに、ハッシュパーティションテーブルは PostgreSQL 11 より利用可能です。

親テーブルの作成

postgres=# CREATE TABLE table_hash_p  (id INTEGER PRIMARY KEY, no INTEGER, val TEXT) PARTITION BY HASH (id);
CREATE TABLE

子テーブルの作成

postgres=# CREATE TABLE table_hash_c_0 PARTITION OF table_hash_p FOR VALUES WITH (MODULUS 3, REMAINDER 0);
CREATE TABLE

postgres=# CREATE TABLE table_hash_c_1 PARTITION OF table_hash_p FOR VALUES WITH (MODULUS 3, REMAINDER 1);
CREATE TABLE

postgres=# CREATE TABLE table_hash_c_2 PARTITION OF table_hash_p FOR VALUES WITH (MODULUS 3, REMAINDER 2);
CREATE TABLE

・作成した親テーブルに対して、子テーブルを 3 つ追加しました。
・MODULUS が分割数になります。

分割数が違う子テーブルを作成するとどうなるか

postgres=# CREATE TABLE table_hash_c_3 PARTITION OF table_hash_p FOR VALUES WITH (MODULUS 4, REMAINDER 3);
ERROR:  every hash partition modulus must be a factor of the next larger modulus

・最初に子テーブルを作成したときの MODULUS の値と、違う値の子テーブルを追加しようとすると、エラーが発生することを確認しました。

あまり（REMAINDER）が分割数を超える子テーブルを作成するとどうなるか

postgres=# CREATE TABLE table_hash_c_3 PARTITION OF table_hash_p FOR VALUES WITH (MODULUS 3, REMAINDER 3);
ERROR:  remainder for hash partition must be less than modulus

・こちらも（当然ながら）エラーになりました。

対応する「あまり」がないデータの追加するとどうなるか

子テーブルの一部を削除します。

postgres=# DROP TABLE table_hash_c_2;
DROP TABLE

この状態でデータを追加するとどうなるでしょうか。

postgres=# INSERT INTO table_hash_p VALUES (1, 1, 'TEST');
ERROR:  no partition of relation "table_hash_p" found for row
DETAIL:  Partition key of the failing row contains (id) = (1).

・追加しようとするデータに対応する子テーブルが存在しないため、エラーになりました。

postgres=#  INSERT INTO table_hash_p VALUES (2, 2, 'TEST');
INSERT 0 1

・別のデータではインサートが成功しました。

エラーとなるのは、対応する子テーブルが存在しない場合のみのようです。

postgres=# CREATE TABLE table_hash_c_2 PARTITION OF table_hash_p FOR VALUES WITH (MODULUS 3, REMAINDER 2);
CREATE TABLE

postgres=#  INSERT INTO table_hash_p VALUES (1, 1, 'TEST');
INSERT 0 1

・id = 1 のデータも、対応する子テーブルを作成することで、無事にインサートできました。

パーティションキーに該当するカラムの値を更新して、別の子テーブルに移動できるか

ハッシュテーブルでも、子テーブル間のデータ移動が可能かを確認します。

postgres=# SELECT * FROM table_hash_c_0 ORDER BY id;
 id | no |     val     
----+----+-------------
  2 |  2 | レコード2_2
(1 row)

postgres=# SELECT * FROM table_hash_c_1 ORDER BY id;
 id | no | val 
----+----+-----
(0 rows)

postgres=# SELECT * FROM table_hash_c_2 ORDER BY id;
 id | no |     val     
----+----+-------------
  0 |  0 | レコード0_0
  1 |  1 | レコード1_1
(2 rows)

・table_hash_c_2 に 2 レコード格納されていることを確認しました。

table_hash_c2 にある id = 1, no = 1 のレコードを、table_hash_c_1 に移動させてみたいと思います。

postgres=# UPDATE table_hash_p SET id = 3, no = 3, val = 'レコード3_3' WHERE id = 1;
UPDATE 1

・データを更新しました。

postgres=# SELECT * FROM table_hash_c_0 ORDER BY id;
 id | no |     val     
----+----+-------------
  2 |  2 | レコード2_2
(1 row)

postgres=# SELECT * FROM table_hash_c_1 ORDER BY id;
 id | no |     val     
----+----+-------------
  3 |  3 | レコード3_3
(1 row)

postgres=# SELECT * FROM table_hash_c_2 ORDER BY id;
 id | no |     val     
----+----+-------------
  0 |  0 | レコード0_0
(1 row)

・無事にデータが table_hash_c_2 から table_hash_c_1 に移動できたことを確認しました。

パーティションテーブルの適切な分割数について

その他の気になる点としては「パーティションテーブルの適切な分割数」というのもありますが、これはテーブルやクエリの内容・傾向にもよりますので一概には定義できないと考えられます。
公式ドキュメント上には下記のような説明があります。

典型的なクエリではクエリプランナが少数のパーティションを除いて残り全てのパーティションを除外できるという前提に立てば、クエリプランナは通常最大数千パーティションのパーティション階層を適切に操作することができます。

PostgreSQL 13.1 文書 - 5.11.6. 宣言的パーティショニングのベストプラクティス -

https://www.postgresql.jp/document/13/html/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE-BEST-PRACTICES

一応、数千の分割数も大丈夫、ということですが、前提として「少数のパーティションを除いて残り全てのパーティションを除外できる」ということが明示されています。
数千の子テーブルを作成してもよいが、クエリで参照されるのはそのうちの少数の子テーブルにしてください、ということになります。

最後に

少し長くなってしまいましたが、以上がパーティションテーブルに関して気になったことの確認結果となります。
その他にも「バージョン間の差分」についても気になるところですが、まとめきれませんでした。機会と時間があればまとめてみたいと思います。