Go to Qiita Advent Calendar 2024 Top
LoginSignup
4
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@nuko_yokohama(nuko yokohama)

PostgreSQL 10がやってくる！（その4） 新パーティションの小ネタ

Last updated at Posted at 2017-02-16

はじめに

PostgreSQL 10がやってくる（その2） このパーティションがすごい！の記事で、PostgreSQL 10のリストパーティションについて簡単に紹介したんですが、今回はパーティションに関する小ネタを幾つか書いてみました。
(ちょい長いです)

今日のお題は以下の6つ

  • FOR VALUESに複数の値を指定する
  • パーティションキーが重複する場合
  • 別テーブルで同一のパーティションキーが存在し、同一のキー値を設定した場合
  • FOR VALUES IN () に関数を指定する
  • Primary keyは設定不可
  • パーティションテーブルにインデックスを設定してみる

FOR VALUESに複数の値を指定する

高度に発達した横濱は横浜と区別がつかない

PostgreSQL 10devel DocumentationのCREATE TABLEを見ると、

and partition_bound_spec is:

{ IN ( { bound_literal | NULL } [, ...] ) |
FROM ( { bound_literal | UNBOUNDED } [, ...] ) TO ( { bound_literal | UNBOUNDED } [, ...] ) }

と書いてある。構文上、IN (...) の中は複数の値を取れるようなので試してみる。
例えば、以下のようなリストパーティションテーブルを作成する。

CREATE UNLOGGED TABLE japan (
    pref text,
    city text,
    data text
)
PARTITION BY LIST (pref);

CREATE UNLOGGED TABLE tokyo PARTITION OF japan
FOR VALUES IN ('東京');

CREATE UNLOGGED TABLE kanagawa PARTITION OF japan
FOR VALUES IN ('神奈川')
PARTITION BY LIST (city);

CREATE UNLOGGED TABLE "kanagawa.yokohama" PARTITION OF kanagawa
FOR VALUES IN ('横浜','横濱');

CREATE UNLOGGED TABLE "kanagawa.kawasaki"  PARTITION OF kanagawa
FOR VALUES IN ('川崎');

CREATE UNLOGGED TABLE "kanagawa.machida"  PARTITION OF kanagawa
FOR VALUES IN ('町田');

CREATE UNLOGGED TABLE shizuoka PARTITION OF japan
FOR VALUES IN ('静岡');

kanagawa.yokohama テーブルに振り分けるための値として、「横浜」と「横濱」の2つの値を設定している。
このパーティションテーブルに対して、以下のようなレコードを4件挿入する。

INSERT INTO japan VALUES ('神奈川','横浜','麺恋亭');
INSERT INTO japan VALUES ('神奈川','横浜','吉村家');
INSERT INTO japan VALUES ('神奈川','横浜','ラーメン二郎 関内店');
INSERT INTO japan VALUES ('神奈川','横濱','玉泉亭');

検索してみると、cityが「横浜」のレコードも「横濱」のレコードもきちんと、kanagawa.yokohama テーブルに格納されている。

part=# TABLE japan;
  pref  | city |        data         
--------+------+---------------------
 神奈川 | 横浜 | 麺恋亭
 神奈川 | 横浜 | 吉村家
 神奈川 | 横浜 | ラーメン二郎 関内店
 神奈川 | 横濱 | 玉泉亭
(4 rows)

part=# TABLE "kanagawa.yokohama";
  pref  | city |        data         
--------+------+---------------------
 神奈川 | 横浜 | 麺恋亭
 神奈川 | 横浜 | 吉村家
 神奈川 | 横浜 | ラーメン二郎 関内店
 神奈川 | 横濱 | 玉泉亭
(4 rows)

もちろん、パーティションのプルーニングもきちんとやってくれる。

part=# EXPLAIN SELECT * FROM japan WHERE pref = '神奈川' AND city = '横濱';
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Append  (cost=0.00..19.75 rows=3 width=96)
   ->  Seq Scan on japan  (cost=0.00..0.00 rows=1 width=96)
         Filter: ((pref = '神奈川'::text) AND (city = '横濱'::text))
   ->  Seq Scan on kanagawa  (cost=0.00..0.00 rows=1 width=96)
         Filter: ((pref = '神奈川'::text) AND (city = '横濱'::text))
   ->  Seq Scan on "kanagawa.yokohama"  (cost=0.00..19.75 rows=1 width=96)
         Filter: ((pref = '神奈川'::text) AND (city = '横濱'::text))
(7 rows)

パーティションキーが重複する場合

2つの都道府県に属したいなんて、町田市は欲張りさんのシティなんだね。すごーい！

例えば以下のように、kanagawaの子テーブルとして、kanagawa.machida と tokyo.machida を作成する。
ポイントはどちらの子テーブルも、パーティションキー city に、FOR VALUES IN ('町田') をセットしていることだ。

CREATE UNLOGGED TABLE japan (
    pref text,
    city text,
    data text
)
PARTITION BY LIST (pref);

CREATE UNLOGGED TABLE kanagawa PARTITION OF japan
FOR VALUES IN ('神奈川')
PARTITION BY LIST (city);
;

CREATE UNLOGGED TABLE "kanagawa.yokohama" PARTITION OF kanagawa
FOR VALUES IN ('横浜');

CREATE UNLOGGED TABLE "kanagawa.kawasaki"  PARTITION OF kanagawa
FOR VALUES IN ('川崎');

CREATE UNLOGGED TABLE "kanagawa.machida"  PARTITION OF kanagawa
FOR VALUES IN ('町田');

CREATE UNLOGGED TABLE "tokyo.machida"  PARTITION OF kanagawa
FOR VALUES IN ('町田');

このDDLを実行すると、tokyo.machida テーブル作成時に失敗する。

（略）
CREATE UNLOGGED TABLE "kanagawa.machida"  PARTITION OF kanagawa
FOR VALUES IN ('町田');
CREATE TABLE
CREATE UNLOGGED TABLE "tokyo.machida"  PARTITION OF kanagawa
FOR VALUES IN ('町田');
psql:create_table_dupkey.sql:25: ERROR:  partition "tokyo.machida" would overlap partition "kanagawa.machida"

これは 町田は神奈川だからエラーになる・・・のではなく、 このCREATE TABLE文を実行されるときに、kanagawaのパーティションキー cityに「町田」がシステムカタログに登録済みだからである。
まだ、各システムカタログ間の相関は追いきれていないのだが、実はこのパーティションキーの情報は、pg_class の relpartbound 列に設定されている。

part=# SELECT oid, relname, relpartbound FROM pg_class as c WHERE relname = 'kanagawa.yokohama';
-[ RECORD 1 ]+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
oid          | 16556
relname      | kanagawa.yokohama
relpartbound | {PARTITIONBOUND :strategy l :listdatums ({CONST :consttype 25 :consttypmod -1 :constcollid 100 :constlen -1 :constbyval false :constisnull false :location 79 :constvalue 10 [ 40 0 0 0 -26 -88 -86 -26 -75 -100 ]} {CONST :consttype 25 :consttypmod -1 :constcollid 100 :constlen -1 :constbyval false :constisnull false :location 88 :constvalue 10 [ 40 0 0 0 -26 -88 -86 -26 -65 -79 ]}) :lowerdatums <> :upperdatums <>}

relpartboud列には、なにか謎めいた値が入ってますね・・・
なお、これを分かりやすく表示するために、pg_get_expr()というSQL関数もある。

part=# SELECT oid, relname, relpartbound, pg_get_expr(c.relpartbound, c.oid) FROM pg_class as c WHERE relname = 'kanagawa.yokohama';
-[ RECORD 1 ]+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
oid          | 16556
relname      | kanagawa.yokohama
relpartbound | {PARTITIONBOUND :strategy l :listdatums ({CONST :consttype 25 :consttypmod -1 :constcollid 100 :constlen -1 :constbyval false :constisnull false :location 79 :constvalue 10 [ 40 0 0 0 -26 -88 -86 -26 -75 -100 ]} {CONST :consttype 25 :consttypmod -1 :constcollid 100 :constlen -1 :constbyval false :constisnull false :location 88 :constvalue 10 [ 40 0 0 0 -26 -88 -86 -26 -65 -79 ]}) :lowerdatums <> :upperdatums <>}
pg_get_expr  | FOR VALUES IN ('横浜', '横濱')

見える！私にもパーティーションキーの内容が見えるぞ！
実はこのpg_get_expr()というのは、psqlのメタコマンド \d+ パーティションテーブル名 の表示に使われていたりする。

part=# \d+ "kanagawa.yokohama"
                       Unlogged table "public.kanagawa.yokohama"
 Column | Type | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+------+-----------+----------+---------+----------+--------------+-------------
 pref   | text |           |          |         | extended |              | 
 city   | text |           |          |         | extended |              | 
 data   | text |           |          |         | extended |              | 
Partition of: kanagawa FOR VALUES IN ('横浜', '横濱')

「Partition of:」の内容を表示するのに使ってるようですね。

それはともかく、おそらくCREATE TABLE時に、PARTITION OF ... が指定された場合に、pg_class内の親テーブル内のrelpartbound列を参照して既に存在している場合に、CREATE TABLEをエラーにさせるということなのだろう。
これによって、従来のトリガベースのパーティション方式ではできなかった(と思う)、パーティションキー重複チェックが可能になったようだ。すごーい！

別テーブルで同一のパーティションキーが存在し、同一のキー値を設定した場合

ふたつの都市“東京都町田市”と“神奈川県町田市”は、関東において地理的にほぼ同じ位置を占めるモザイク状に組み合わさった特殊な領土を有していた。

こういうケースは問題ない。
例えば以下のようなパーティションテーブルを定義する。

CREATE UNLOGGED TABLE japan (
    pref text,
    city text,
    data text
)
PARTITION BY LIST (pref);

CREATE UNLOGGED TABLE tokyo PARTITION OF japan
FOR VALUES IN ('東京')
PARTITION BY LIST (city);

CREATE UNLOGGED TABLE "tokyo.shinagawa" PARTITION OF tokyo
FOR VALUES IN ('品川');

CREATE UNLOGGED TABLE "tokyo.machida"  PARTITION OF tokyo
FOR VALUES IN ('町田');

CREATE UNLOGGED TABLE kanagawa PARTITION OF japan
FOR VALUES IN ('神奈川')
PARTITION BY LIST (city);

CREATE UNLOGGED TABLE "kanagawa.yokohama" PARTITION OF kanagawa
FOR VALUES IN ('横浜');

CREATE UNLOGGED TABLE "kanagawa.machida"  PARTITION OF kanagawa
FOR VALUES IN ('町田');

tokyo, kanagawaそれぞれのリストパーティションキーとして、cityを選択し、それぞれの子テーブル、tokyo.machida と kanagawa.machidaの FOR VALUES句に同じ値「町田」を定義する。
こういう指定は問題はない。

このパーティションテーブルに以下のようなデータを挿入する。

INSERT INTO japan VALUES ('神奈川','横浜','麺恋亭');
INSERT INTO japan VALUES ('神奈川','町田','竹の助');
INSERT INTO japan VALUES ('東京','品川','丸直');
INSERT INTO japan VALUES ('東京','町田','竹の助');

prefが異なるがcityのみ同じというデータは問題なく挿入される。検索結果はこうなる。

part=# TABLE japan;
  pref  | city |  data  
--------+------+--------
 東京   | 品川 | 丸直
 東京   | 町田 | 竹の助
 神奈川 | 横浜 | 麺恋亭
 神奈川 | 町田 | 竹の助
(4 rows)

part=# TABLE "kanagawa.machida";
  pref  | city |  data  
--------+------+--------
 神奈川 | 町田 | 竹の助
(1 row)

part=# TABLE "tokyo.machida";
 pref | city |  data  
------+------+--------
 東京 | 町田 | 竹の助
(1 row)

こう定義することで、町田は神奈川にも東京にも属することになりました。
なんか、チャイナ・ミエヴィルの都市と都市っぽい感じになってきましたね。
神奈川から見た町田と東京から見た町田が重層的に存在する的なｗ

FOR VALUES IN () に関数を指定する

こまけぇこたぁいいんだよ

今度はパーティーションキーの値を日本語じゃなくて、英字にしてみる。

CREATE UNLOGGED TABLE japan (
    pref text,
    city text,
    data text
)
PARTITION BY LIST (pref);

CREATE UNLOGGED TABLE tokyo PARTITION OF japan
FOR VALUES IN ('Tokyo');

CREATE UNLOGGED TABLE kanagawa PARTITION OF japan
FOR VALUES IN ('Kanagawa')
PARTITION BY LIST (city);

CREATE UNLOGGED TABLE "kanagawa.yokohama" PARTITION OF kanagawa
FOR VALUES IN ('Yokohama');

CREATE UNLOGGED TABLE "kanagawa.kawasaki"  PARTITION OF kanagawa
FOR VALUES IN ('Kawasaki');

CREATE UNLOGGED TABLE "kanagawa.machida"  PARTITION OF kanagawa
FOR VALUES IN ('Machida');


CREATE UNLOGGED TABLE shizuoka PARTITION OF japan
FOR VALUES IN ('Sizuoka');

さて、この状態のパーティションテーブルに、以下のようなデータを挿入しようとすると、パーティションキーと一致しないために怒られる。

part=# INSERT INTO japan VALUES ('kanagawa', 'yokohama', '玉泉亭');
ERROR:  no partition of relation "japan" found for row
DETAIL:  Failing row contains (kanagawa, yokohama, 玉泉亭).

エラーになること自体は問題はない。仕方がないだろう。じゃあ、みんな小文字で判定すればいいんじゃね？
ということで、PARTITION BY LIST にlower(pref)やlower(city)を設定してみる。
で、FOR VALUES INには全て小文字の値を設定しておく。

CREATE UNLOGGED TABLE japan (
    pref text,
    city text,
    data text
)
PARTITION BY LIST (lower(pref));

CREATE UNLOGGED TABLE tokyo PARTITION OF japan
FOR VALUES IN ('tokyo');

CREATE UNLOGGED TABLE kanagawa PARTITION OF japan
FOR VALUES IN ('kanagawa')
PARTITION BY LIST (lower(city));
;

CREATE UNLOGGED TABLE "kanagawa.yokohama" PARTITION OF kanagawa
FOR VALUES IN ('yokohama');

CREATE UNLOGGED TABLE "kanagawa.kawasaki"  PARTITION OF kanagawa
FOR VALUES IN ('kawasaki');

CREATE UNLOGGED TABLE "kanagawa.machida"  PARTITION OF kanagawa
FOR VALUES IN ('machida');


CREATE UNLOGGED TABLE shizuoka PARTITION OF japan
FOR VALUES IN ('sizuoka');

で、さっきのようなデータを挿入してみる。

INSERT INTO japan VALUES ('kanagawa','yokohama','麺恋亭');
INSERT INTO japan VALUES ('KANAGAWA','YOKOHAMA','吉村家');
INSERT INTO japan VALUES ('Kanagawa','Yokohama','ラーメン二郎 関内店');
INSERT INTO japan VALUES ('kanagawa','Kawasaki','クマさん');
INSERT INTO japan VALUES ('Kanagawa','Kawasaki','ニュータンタン本舗 本店');
INSERT INTO japan VALUES ('KANAGAWA','Machida','竹の助');
INSERT INTO japan VALUES ('Sizuoka','Atami','雨風本舗');
INSERT INTO japan VALUES ('sizuoka','fujieda','池田屋');
INSERT INTO japan VALUES ('TOKYO','SINAGAWA','丸直');
INSERT INTO japan VALUES ('tokyo','hachijo-shima','蓮華');

全部小文字でもキャピタライズしていても全部大文字でも、どれも正常に挿入はされる。
ただ、格納されている値はlower化されているわけではない。

part=# TABLE japan;
   pref   |     city      |          data           
----------+---------------+-------------------------
 TOKYO    | SINAGAWA      | 丸直
 tokyo    | hachijo-shima | 蓮華
 Sizuoka  | Atami         | 雨風本舗
 sizuoka  | fujieda       | 池田屋
 kanagawa | yokohama      | 麺恋亭
 KANAGAWA | YOKOHAMA      | 吉村家
 Kanagawa | Yokohama      | ラーメン二郎 関内店
 kanagawa | Kawasaki      | クマさん
 Kanagawa | Kawasaki      | ニュータンタン本舗 本店
 KANAGAWA | Machida       | 竹の助
(10 rows)

そんなの問題ないじゃん。検索するときに列にlowerかければいいんでしょ？

part=# SELECT * FROM japan WHERE lower(pref) = 'kanagawa';
   pref   |   city   |          data           
----------+----------+-------------------------
 kanagawa | yokohama | 麺恋亭
 KANAGAWA | YOKOHAMA | 吉村家
 Kanagawa | Yokohama | ラーメン二郎 関内店
 kanagawa | Kawasaki | クマさん
 Kanagawa | Kawasaki | ニュータンタン本舗 本店
 KANAGAWA | Machida  | 竹の助

このときの実行計画を見てみると

part=# EXPLAIN SELECT * FROM japan WHERE lower(pref) = 'kanagawa';
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Append  (cost=0.00..59.25 rows=11 width=96)
   ->  Seq Scan on japan  (cost=0.00..0.00 rows=1 width=96)
         Filter: (lower(pref) = 'kanagawa'::text)
   ->  Seq Scan on kanagawa  (cost=0.00..0.00 rows=1 width=96)
         Filter: (lower(pref) = 'kanagawa'::text)
   ->  Seq Scan on "kanagawa.yokohama"  (cost=0.00..19.75 rows=3 width=96)
         Filter: (lower(pref) = 'kanagawa'::text)
   ->  Seq Scan on "kanagawa.kawasaki"  (cost=0.00..19.75 rows=3 width=96)
         Filter: (lower(pref) = 'kanagawa'::text)
   ->  Seq Scan on "kanagawa.machida"  (cost=0.00..19.75 rows=3 width=96)
         Filter: (lower(pref) = 'kanagawa'::text)
(11 rows)

うん。きちんとkanagawaおよびkanagawaの子テーブルのみをプルーニングしてますね。

part=# EXPLAIN SELECT * FROM japan WHERE lower(pref) = 'kanagawa' AND lower(city) = 'machida';
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Append  (cost=0.00..23.00 rows=3 width=96)
   ->  Seq Scan on japan  (cost=0.00..0.00 rows=1 width=96)
         Filter: ((lower(pref) = 'kanagawa'::text) AND (lower(city) = 'machida'::text))
   ->  Seq Scan on kanagawa  (cost=0.00..0.00 rows=1 width=96)
         Filter: ((lower(pref) = 'kanagawa'::text) AND (lower(city) = 'machida'::text))
   ->  Seq Scan on "kanagawa.machida"  (cost=0.00..23.00 rows=1 width=96)
         Filter: ((lower(pref) = 'kanagawa'::text) AND (lower(city) = 'machida'::text))

条件にlower(pref)とlower(city)を指定しても問題なさげ。

Primary keyは設定不可

ならぬものはならぬものです。

これまで例にしてきたパーティションテーブルにはidに相当するものがなかった。今は件数が少ないので性能は気にしてないけど、件数が増えたらidをインデックスにした検索もしたくなるかもしれない。

じゃあ、親テーブルにidを追加してみよう。

CREATE UNLOGGED TABLE japan (
    id   int primary key, -- PK
    pref text,
    city text,
    data text
)
PARTITION BY LIST (pref);

が、このCREATE TABLEは失敗する。

ERROR:  primary key constraints are not supported on partitioned tables
LINE 2:     id   int primary key, 
                     ^

現状、パーティション化されるテーブルへのprimary keyの設定は制約されているようだ。無念。

この件、postgresql-jp Slackで聞いたところ、喜田さんから回答がありました。以下、Slackからの引用。

あ、その話、先日の勉強会でamitさんに質問しました。PKは子表側でつくることしか今のところ想定してなくて、そうすると親表全体で一意は保証できないので制約としての意味は薄れます。子表でパーティションキーと一意のキーの組でPK指定しないといけません。

ということらしい。

パーティションテーブルにインデックスを設定してみる

再三再四

パーティションテーブルに制約は設定できないのか・・・インデックスはどうなのか試してみた。

CREATE INDEX japan_id ON japan USING bree (id);
psql:create_table_index.sql:15: ERROR:  cannot create index on partitioned table "japan"

パーティションテーブルには、インデックスは設定できないようだ。
こんな風に、末端のテーブル個々にインデックスを設定するしかなさげ。

CREATE UNLOGGED TABLE japan (
    id   int,
    pref text,
    city text,
    data text
)
PARTITION BY LIST (pref);

CREATE UNLOGGED TABLE tokyo PARTITION OF japan
FOR VALUES IN ('東京');
CREATE INDEX tokyo_id ON "tokyo" USING btree (id);

CREATE UNLOGGED TABLE kanagawa PARTITION OF japan
FOR VALUES IN ('神奈川')
PARTITION BY LIST (city);

CREATE UNLOGGED TABLE "kanagawa.yokohama" PARTITION OF kanagawa
FOR VALUES IN ('横浜');
CREATE INDEX yokohama_id ON "kanagawa.yokohama" USING btree (id);

CREATE UNLOGGED TABLE "kanagawa.kawasaki"  PARTITION OF kanagawa
FOR VALUES IN ('川崎');
CREATE INDEX kawasaki_id ON "kanagawa.kawasaki" USING btree (id);

CREATE UNLOGGED TABLE "kanagawa.machida"  PARTITION OF kanagawa
FOR VALUES IN ('町田');
CREATE INDEX machida_kawasaki_id ON "kanagawa.machida" USING btree (id);

CREATE UNLOGGED TABLE shizuoka PARTITION OF japan
FOR VALUES IN ('静岡');
CREATE INDEX shizuoka_id ON "shizuoka" USING btree (id);

親テーブルにインデックス作成のコマンドを実行したら、子テーブルに自動的に伝播してくれるといいのになー。
PostgrreSQL 10正式版までに対応されていることを勝手に期待。

まとめ

  • FOR VALUESに複数の値を指定できるよ。
  • パーティションキーが重複する場合はエラーになるよ。
  • 別テーブルで同一のパーティションキーが存在し、同一のキー値を設定した場合は問題ないよ。
  • FOR VALUES IN () に関数を指定しても問題ないよ。
  • Primary keyは設定不可だよ。
  • パーティションテーブルにインデックスを設定できないので、子テーブル個別に設定が必要だよ。残念だね！
  • チャイナ・ミエヴィル「都市と都市」は面白いぞ。
  • チャイナ・ミエヴィル氏には町田市を舞台にした「都市と都市２」を書いて欲しいなあ。
4
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
4
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?