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

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

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

1 ■はじめに
2 ■検証 (PostgreSQL 10 の動作確認)
3 ■検証 (PostgreSQL 11 の動作確認)
4 ■最後に

■はじめに

昨年リリースされた 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) になります)