【PostgreSQL 11 新機能】パーティションニングされたテーブル (親テーブル) への INDEX 作成を試してみた

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

今回は PostgreSQL 11 (現時点ではまだ beta 版) の新機能である “パーティションニングされたテーブル (親テーブル) への INDEX 作成” を検証してみました。(※以下の内容は CentOS 7.5/PostgreSQL 10.5/PostgreSQL 11beta3 にて検証しています。)

■はじめに

昨年リリースされた PostgreSQL 10 の新機能である Declarative Partitioning では、パーティショニングされたテーブルの親テーブルに INDEX を作成することができませんでした。しかし、PostgreSQL 11 では、パーティショニングされた親テーブルに INDEX を作成することができるようになる予定です。

今回は PostgreSQL 11beta3 を使って、この親テーブルへの INDEX 作成を試してみようと思います。

■検証 (PostgreSQL 10 の動作確認)

まずは PostgreSQL 10 の動作を確認してみます。(以下の例では LIST パーティションを使用しています)

postgres=# CREATE TABLE hoge (list_key int, data text) PARTITION BY LIST (list_key);
CREATE TABLE
postgres=# CREATE TABLE hoge_child_1 PARTITION OF hoge FOR VALUES IN (1);
CREATE TABLE
postgres=# CREATE TABLE hoge_child_2 PARTITION OF hoge FOR VALUES IN (2);
CREATE TABLE
postgres=# CREATE TABLE hoge_child_3 PARTITION OF hoge FOR VALUES IN (3);
CREATE TABLE
postgres=# 
postgres=# \d+ hoge
                                     Table "public.hoge"
  Column  |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
----------+---------+-----------+----------+---------+----------+--------------+-------------
 list_key | integer |           |          |         | plain    |              | 
 data     | text    |           |          |         | extended |              | 
Partition key: LIST (list_key)
Partitions: hoge_child_1 FOR VALUES IN (1),
            hoge_child_2 FOR VALUES IN (2),
            hoge_child_3 FOR VALUES IN (3)

PostgreSQL 10 にて、上記の様にパーティショニングされたテーブルの親テーブル “hoge” に INDEX を作成しようとすると、以下の様に ERROR になってしまいます。

postgres=# CREATE INDEX hoge_test_idx ON hoge (list_key);
ERROR:  cannot create index on partitioned table "hoge"

■検証 (PostgreSQL 11 の動作確認)

それでは、PostgreSQL 11beta3 でパーティショニングされたテーブルの親テーブルに INDEX を作成してみます。

まずは、前述した PostgreSQL 10 の検証と同じテーブルを作成します。

postgres=# CREATE TABLE hoge (list_key int, data text) PARTITION BY LIST (list_key);
CREATE TABLE
postgres=# CREATE TABLE hoge_child_1 PARTITION OF hoge FOR VALUES IN (1);
CREATE TABLE
postgres=# CREATE TABLE hoge_child_2 PARTITION OF hoge FOR VALUES IN (2);
CREATE TABLE
postgres=# CREATE TABLE hoge_child_3 PARTITION OF hoge FOR VALUES IN (3);
CREATE TABLE
postgres=# 
postgres=# \d+ hoge
                                     Table "public.hoge"
  Column  |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
----------+---------+-----------+----------+---------+----------+--------------+-------------
 list_key | integer |           |          |         | plain    |              | 
 data     | text    |           |          |         | extended |              | 
Partition key: LIST (list_key)
Partitions: hoge_child_1 FOR VALUES IN (1),
            hoge_child_2 FOR VALUES IN (2),
            hoge_child_3 FOR VALUES IN (3)

この状態で親テーブル “hoge” に対して INDEX を作成してみます。すると、PostgreSQL 10 の様に ERROR にはならずに、親テーブル “hoge” に正常に INDEX が作成されていることが確認できます。

postgres=# CREATE INDEX hoge_test_idx ON hoge (list_key);
CREATE INDEX
postgres=# 
postgres=# \d+ hoge
                                     Table "public.hoge"
  Column  |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
----------+---------+-----------+----------+---------+----------+--------------+-------------
 list_key | integer |           |          |         | plain    |              | 
 data     | text    |           |          |         | extended |              | 
Partition key: LIST (list_key)
Indexes:
    "hoge_test_idx" btree (list_key)
Partitions: hoge_child_1 FOR VALUES IN (1),
            hoge_child_2 FOR VALUES IN (2),
            hoge_child_3 FOR VALUES IN (3)

この親テーブルに作成された INDEX ですが、上記 \d+ の結果からはテーブル “hoge” に 1つの INDEX が作成されている様に見えています。しかし、実際には以下の様に各子テーブルに INDEX が作成される様です。

postgres=# SELECT relname, indexrelname FROM pg_stat_user_indexes WHERE relname like 'hoge%';
   relname    |       indexrelname        
--------------+---------------------------
 hoge_child_1 | hoge_child_1_list_key_idx
 hoge_child_2 | hoge_child_2_list_key_idx
 hoge_child_3 | hoge_child_3_list_key_idx
(3 rows)

また、\d+ の “Indexes:” 欄に出力されている “hoge_test_idx” は別途管理されているようであり、pg_class.relkind の値が “I” になっていました。

postgres=# SELECT relname, relkind FROM pg_class WHERE relname like 'hoge%idx';
          relname          | relkind 
---------------------------+---------
 hoge_child_1_list_key_idx | i
 hoge_child_2_list_key_idx | i
 hoge_child_3_list_key_idx | i
 hoge_test_idx             | I
(4 rows)

pg_class のドキュメントを参照すると、pg_class.relkind の “I” は “partitioned index” というオブジェクトを示すものである旨の記載がありました。詳細は追えていませんが、親テーブルに作成された INDEX は通常の INDEX とは異なるオブジェクトとして扱われているようです。(通常の INDEX は pg_class.relkind の値が “i” (小文字の i) になります)

■最後に

PostgreSQL 10 のパーティショニングでは、子テーブルに個別に INDEX を作成する必要がありましたが、PostgreSQL 11 親テーブルに対して INDEX を作成できるようになっているので、かなり便利になっている様に感じます。

また、リリースノートを見るとパーティショニングされたテーブルに主キーや外部キーも設定できるようになっているようなので、PostgreSQL 11 ではパーティショニングがかなり使いやすくなっているのではないでしょうか。

PostgreSQL 11 には他にも新機能が多くあるため、次回も PostgreSQL 11 の新機能の検証を実施してみようと思います。

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

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

Twitter YouTube