PostgreSQL10 の新機能 Declarative Partitioning について検証してみた

こんにちは。サイオステクノロジー OSS サポート担当 Y です。

今回は PostgreSQL 10 の新機能である Declarative Partitioning を試してみました。(※以下の内容は PostgreSQL 10.1 にて検証/調査しています。)

(https://www.postgresql.org/docs/10/static/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE)

■はじめに

PostgreSQL 10 の Declarative Partitioning には RANGE パーティションと LIST パーティションがあるのですが、今回は RANGE パーティションを利用して以下の感じでレコードを振り分けてみようと思います。

親テーブル  : "1" ～ "10" の値を格納するテーブル
子テーブル1 : 数値 "1" ～ "5" を格納する
子テーブル2 : 数値 "6" ～ "10" を格納する

■検証

さて、まずはテーブルを作成してみます。親テーブル “hoge” に対して子テーブル “hoge_a”, “hoge_b” を作成しています。

postgres=# CREATE TABLE hoge (a int) PARTITION BY RANGE (a);
CREATE TABLE
postgres=# 
postgres=# CREATE TABLE hoge_a PARTITION OF hoge FOR VALUES FROM (1) TO (5);
CREATE TABLE
postgres=# 
postgres=# CREATE TABLE hoge_b PARTITION OF hoge FOR VALUES FROM (6) TO (10);
CREATE TABLE
postgres=# 
postgres=# \d+ hoge
                                   Table "public.hoge"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+---------+--------------+-------------
 a      | integer |           |          |         | plain   |              | 
Partition key: RANGE (a)
Partitions: hoge_a FOR VALUES FROM (1) TO (5),
            hoge_b FOR VALUES FROM (6) TO (10)

さっそく作成した親テーブル “hoge” に “1” ～ “10” の値を INSERT しようとしたのですが、エラーが出てしまいました…

postgres=# INSERT INTO hoge VALUES (generate_series(1,10));
ERROR:  no partition of relation "hoge" found for row
DETAIL:  Partition key of the failing row contains (a) = (5).

エラーの内容を見ると “5” を格納するパーティションが存在していない状況のようです。

結論から言うと、テーブルを定義する際に境界値の扱いをよく考えていませんでした…

試しに各境界値の値 (1, 5, 6, 10) を個別に INSERT してみると以下の感じになりました。

postgres=# INSERT INTO hoge VALUES (1);
INSERT 0 1
postgres=# 
postgres=# INSERT INTO hoge VALUES (5);
ERROR:  no partition of relation "hoge" found for row
DETAIL:  Partition key of the failing row contains (a) = (5).
postgres=# 
postgres=# INSERT INTO hoge VALUES (6);
INSERT 0 1
postgres=# 
postgres=# INSERT INTO hoge VALUES (10);
ERROR:  no partition of relation "hoge" found for row
DETAIL:  Partition key of the failing row contains (a) = (10).
postgres=# 
postgres=# SELECT * FROM hoge;
 a 
---
 1
 6
(2 rows)

どうやら子テーブルを作成する際の “FROM (X) TO (Y)” は “X <= a < Y” という定義になるようです。

ということで、テーブル作成からやり直します。

postgres=# CREATE TABLE hoge (a int) PARTITION BY RANGE (a);
CREATE TABLE
postgres=# 
postgres=# CREATE TABLE hoge_a PARTITION OF hoge FOR VALUES FROM (1) TO (6);
CREATE TABLE
postgres=# 
postgres=# CREATE TABLE hoge_b PARTITION OF hoge FOR VALUES FROM (6) TO (11);
CREATE TABLE
postgres=# 
postgres=# \d+ hoge
                                   Table "public.hoge"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+---------+--------------+-------------
 a      | integer |           |          |         | plain   |              | 
Partition key: RANGE (a)
Partitions: hoge_a FOR VALUES FROM (1) TO (6),
            hoge_b FOR VALUES FROM (6) TO (11)

改めて “1” ～ “10” の値を INSERT してみると、今度は無事に値を格納することができました。

postgres=# INSERT INTO hoge VALUES (generate_series(1,10));
INSERT 0 10
postgres=# 
postgres=# SELECT * FROM hoge;
 a  
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

続けて子テーブルの状況も確認してみると、設定した条件通りにレコードが振り分けられていることが確認できました。

postgres=# SELECT * FROM hoge_a;
 a 
---
 1
 2
 3
 4
 5
(5 rows)

postgres=# 
postgres=# SELECT * FROM hoge_b;
 a  
----
  6
  7
  8
  9
 10
(5 rows)

■最後に

さて、今回は PostgreSQL 10 の新機能である Declarative Partitioning を試してみようとしていきなりエラーに直面した訳ですが、上記内容 (境界値の扱い) についてはドキュメントにちゃんと記載されていました！

(https://www.postgresql.org/docs/10/static/sql-createtable.html)

When creating a range partition, the lower bound specified with FROM is 
an inclusive bound, whereas the upper bound specified with TO is an exclusive 
bound. That is, the values specified in the FROM list are valid values 
of the corresponding partition key columns for this partition, whereas 
those in the TO list are not. Note that this statement must be understood 
according to the rules of row-wise comparison (Section 9.23.5). For example, 
given PARTITION BY RANGE (x,y), a partition bound FROM (1, 2) TO (3, 4) 
allows x=1 with any y>=2, x=2 with any non-null y, and x=3 with any y<4.

また、範囲の指定に MINVALUE, MAXVALUE という特殊な値を指定すると "XX 以上", "XX 以下" といったような上限 (もしくは下限) を設定しない範囲指定 ("XX <= a", "a < XX") もできるようです。

The special values MINVALUE and MAXVALUE may be used when creating a 
range partition to indicate that there is no lower or upper bound on 
the column's value. For example, a partition defined using FROM (MINVALUE) 
TO (10) allows any values less than 10, and a partition defined using 
FROM (10) TO (MAXVALUE) allows any values greater than or equal to 10.

postgres=# CREATE TABLE fuga (a int) PARTITION BY RANGE (a);
CREATE TABLE
postgres=# 
postgres=# CREATE TABLE fuga_a PARTITION OF fuga FOR VALUES FROM (MINVALUE) TO (100);
CREATE TABLE
postgres=# 
postgres=# CREATE TABLE fuga_b PARTITION OF fuga FOR VALUES FROM (100) TO (MAXVALUE);
CREATE TABLE
postgres=# 
postgres=# \d+ fuga
                                   Table "public.fuga"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+---------+--------------+-------------
 a      | integer |           |          |         | plain   |              | 
Partition key: RANGE (a)
Partitions: fuga_a FOR VALUES FROM (MINVALUE) TO (100),
            fuga_b FOR VALUES FROM (100) TO (MAXVALUE)

postgres=# 
postgres=# INSERT INTO fuga VALUES (generate_series(98, 102));
INSERT 0 5
postgres=# 
postgres=# SELECT * FROM fuga;
  a  
-----
  98
  99
 100
 101
 102
(5 rows)

postgres=# 
postgres=# SELECT * FROM fuga_a;
 a  
----
 98
 99
(2 rows)

postgres=# 
postgres=# SELECT * FROM fuga_b;
  a  
-----
 100
 101
 102
(3 rows)

ということで、今回は Declarative Partitioning のお話でした。

About サイオステクノロジーの中の人 41 Articles

サイオステクノロジーで働く中の人です。

Twitter YouTube