はじめに
にゃーん。
前回は、PostgreSQL 10とPostgreSQL 11のシステムカタログの差分を探ってみた。
今回は、PostgreSQL 11の目玉機能の一つと思われる、ハッシュパーティションをちょっと試してみた。まだ、beta1時点のものなので、正式版時点だと構文の細かいところなどは変わるかもしれないけど。
ハッシュパーティションって何？
すごくざっくり言うと、パーティションキーの値から一定の規則で演算した値を使って、格納先/検索先のパーティションを決定するもの。
リストパーティションだと都道府県のような特定の値域をパーティションキーに指定して格納先/検索先のパーティションを決定するし、レンジパーティーションだと、日時をパーティションキーに指定して、一定の区間(1月1日から1月31日など)と比較して格納先/検索先のパーティションを決定する。
リストパーティションやレンジパーティションのように、論理的に意味のある値で評価して、格納先/検索先のパーティションを決定するわけではない。ハッシュパーティションを使うと、パーティションキーに指定する列に偏りがなければ、特定のパーティションに格納先/検索先が集中することなくなる（らしい）。
PostgreSQL 11のハッシュパーティション
PostgreSQL 10から実装さられたリスト/レンジパーティションは、CREATE TABLEコマンドを使って構築する。ハッシュパーティションの場合にも、同様にCREATE TABLEコマンドで構築することになる。まず、パーティションの親となる親テーブルを作成し、その後に作成した親テーブルを参照した、子側のパーティションテーブルを作成することになる。
親テーブルを作成する
pgbenchで使われるpgbench_accountsテーブルと同じ構造をもつ、accountsというテーブルを作成する。なお、この時点ではPKはあえてつけない（つけてもいいけど）。
CREATE TABLE accounts(aid int not null, bid int, abalance int, filler character(84))
PARTITION BY HASH (aid);
PARTITION BYの後にHASHというキーワードを指定し、パーティーションキーとなる列を指定する。今回は列を指定したが、列だけでなく、式を指定しても構わない。ただし、ハッシュパーティションの場合には、指定する列または式は数値型でなくてはならない。
これでパーティションの親となるテーブルは作成された。現状、子にあたるパーティションテーブルは存在していない状態なので、この親テーブルaccountsに対してデータを挿入しようとすると、振り分け先がないので、あたりまえに怒られる。
test=# INSERT INTO accounts VALUES (1, 1, 0, '');
ERROR: no partition of relation "accounts" found for row
DETAIL: Partition key of the failing row contains (aid) = (1).
子側のパーティションテーブルを作成する
子側のパーティションテーブルを作成する。今回はハッシュパーティションとして、3つのテーブルに分割する方針とする。
CREATE TABLE accounts_p0 PARTITION OF accounts FOR VALUES WITH (MODULUS 3, REMAINDER 0)
子側のテーブル定義では、PARTITION OF構文を使う。そこで親テーブル（この例だとaccounts）を指定する。振り分けのルールはFOR VALUES WITHで指定する。MODULUSは分割するパーティション数を指定する。REMAINDERには振り分けのための値を設定する。実際に値を格納する際に、パーティションキーの値（この例だとaidの値）を、MODULUSで除算したときの剰余とREMAINDERを比較するのだろうか？
以下、同様に残りのパーティションを作成していく。
CREATE TABLE accounts_p1 PARTITION OF accounts FOR VALUES WITH (MODULUS 3, REMAINDER 1)
CREATE TABLE accounts_p2 PARTITION OF accounts FOR VALUES WITH (MODULUS 3, REMAINDER 2)
子側のパーティション作成って、ほぼ機械的にできる気もするので、親テーブル指定とセットでやってくれると手間が省けていいかなーとも思ったが、パーティションごとにテーブルスペースを別にしたり等、細かい指定をパーティション毎にしたいケースもあるから、まあ面倒だが、個々に子パーティションを作成するために、CREATE TABLEコマンドを実行するしかないのかな。
パーティション状態の確認
ハッシュパーティションの場合にも、psqlの\dや\d+メタコマンドで状態を詳細に確認できる。
メタコマンド\dの引数なしだと、テーブル一覧はでるが、特にパーティションテーブルかどうかの情報はでない。```\d+``のときに出力してくれると素敵なんだけどなあ。
test=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------+-------+-------
public | accounts | table | nuko
public | accounts_p0 | table | nuko
public | accounts_p1 | table | nuko
public | accounts_p2 | table | nuko
(4 rows)
親テーブルを指定して\d+を実行すると、子テーブルの一覧と、振り分け規則が表示される。
test=# \d+ accounts
Table "public.accounts"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+---------------+-----------+----------+---------+----------+--------------+-------------
aid | integer | | not null | | plain | |
bid | integer | | | | plain | |
abalance | integer | | | | plain | |
filler | character(84) | | | | extended | |
Partition key: HASH (aid)
Partitions: accounts_p0 FOR VALUES WITH (modulus 3, remainder 0),
accounts_p1 FOR VALUES WITH (modulus 3, remainder 1),
accounts_p2 FOR VALUES WITH (modulus 3, remainder 2)
子テーブルを指定して、\d+を実行すると、従属する親テーブル名と、振り分け規則、最終行にパーティション制約が表示される。最終行の表示は、今ひとつ洗練された感じはないが・・・
test=# \d+ accounts_p0
Table "public.accounts_p0"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+---------------+-----------+----------+---------+----------+--------------+-------------
aid | integer | | not null | | plain | |
bid | integer | | | | plain | |
abalance | integer | | | | plain | |
filler | character(84) | | | | extended | |
Partition of: accounts FOR VALUES WITH (modulus 3, remainder 0)
Partition constraint: satisfies_hash_partition('16411'::oid, 3, 0, aid)
データの格納/検索
ハッシュパーティションテーブルの構築が終わったので、データを入れてみよう。COPY文によるデータロードももちろん可能である。
入力データはこんな感じ。4カラム目(filler)は空白文字84文字が入っている（見えないけど）。このファイル自体は100万件のデータである。
$ head -3 /tmp/accounts.txt
1 1 0
2 1 0
3 1 0
$
COPY文で親テーブル(accounts)にロードする。
test=# COPY accounts FROM '/tmp/accounts.txt';
COPY 1000000
test=#
この状態で、accountsテーブルの件数を確認する。
test=# SELECT COUNT(*) FROM accounts;
count
---------
1000000
(1 row)
うむ。100万件きちんと格納されているようですね。
各パーティションテーブルに対しても検索は可能。
test=# SELECT COUNT(*) FROM accounts_p0;
count
--------
333263
(1 row)
test=# SELECT COUNT(*) FROM accounts_p1;
count
--------
333497
(1 row)
test=# SELECT COUNT(*) FROM accounts_p2;
count
--------
333240
(1 row)
アイエエエエエ！ナンデこんな結果！？
てっきり333334件, 333333件, 333333件とかになると思ったんだけど、単純に剰余をとっているわけじゃないのか！
さっき、\d+の結果に出ていた、satisfies_hash_partition()という関数の結果なんだろうか？
実行計画
検索時の実行計画も見てみる。
test=# EXPLAIN SELECT COUNT(*) FROM accounts;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=24686.88..24686.89 rows=1 width=8)
-> Gather (cost=24686.67..24686.88 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=23686.67..23686.68 rows=1 width=8)
-> Parallel Append (cost=0.00..22645.00 rows=416667 width=0)
-> Parallel Seq Scan on accounts_p1 (cost=0.00..6857.57 rows=138957 width=0)
-> Parallel Seq Scan on accounts_p0 (cost=0.00..6852.60 rows=138860 width=0)
-> Parallel Seq Scan on accounts_p2 (cost=0.00..6851.50 rows=138850 width=0)
(8 rows)
パーティションテーブルに対する検索時にも、パラレルスキャンが実行されるようだ。今度はEXPLAIN ANALYZEでやってみる。
test=# EXPLAIN ANALYZE SELECT COUNT(*) FROM accounts;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
---------------------------
Finalize Aggregate (cost=24686.88..24686.89 rows=1 width=8) (actual time=507.778..507.778 rows=1 loops=1)
-> Gather (cost=24686.67..24686.88 rows=2 width=8) (actual time=506.083..507.761 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=23686.67..23686.68 rows=1 width=8) (actual time=494.817..494.818 rows=1 loops=3)
-> Parallel Append (cost=0.00..22645.00 rows=416667 width=0) (actual time=0.081..392.529 rows=333333 loop
s=3)
-> Parallel Seq Scan on accounts_p1 (cost=0.00..6857.57 rows=138957 width=0) (actual time=0.040..10
2.736 rows=111166 loops=3)
-> Parallel Seq Scan on accounts_p0 (cost=0.00..6852.60 rows=138860 width=0) (actual time=0.071..17
9.749 rows=166632 loops=2)
-> Parallel Seq Scan on accounts_p2 (cost=0.00..6851.50 rows=138850 width=0) (actual time=0.076..20
5.437 rows=333240 loops=1)
Planning Time: 0.222 ms
Execution Time: 508.249 ms
(11 rows)
実際にワーカを2つ起動して3並列で子パーティションテーブルを検索しているようだ。（そして、rowsの値は相変わらずビミョーな数値が・・・）
細かいこと
既にMODULUSが設定されている状況で、別のMODULUSの値を設定しようとすると、きちんとエラーにしてくれる。
test=# CREATE TABLE accounts_p1 PARTITION OF accounts FOR VALUES WITH (MODULUS 4, REMAINDER 1);
ERROR: every hash partition modulus must be a factor of the next larger modulus
test=# CREATE TABLE accounts_p1 PARTITION OF accounts FOR VALUES WITH (MODULUS 2, REMAINDER 1);
ERROR: every hash partition modulus must be a factor of the next larger modulus
REMAINDERが重複していた場合もきちんとエラーにしてくれる。
test=# CREATE TABLE accounts_p0 PARTITION OF accounts FOR VALUES WITH (MODULUS 3, REMAINDER 0);
CREATE TABLE
test=# CREATE TABLE accounts_p1 PARTITION OF accounts FOR VALUES WITH (MODULUS 3, REMAINDER 0);
ERROR: partition "accounts_p1" would overlap partition "accounts_p0"
インデックスの伝搬
これはハッシュパーティション固有の話ではなく、リストパーティションやレンジパーティションでも同様だが、PostgreSQL 11から親テーブルに対して、CREATE INDEXでインデックスを作成すると、子側の全てのパーティションに対しても同様のインデックスを設定してくれるようになった。
インデックス設定前の親テーブル、子側のパーティションテーブルの状態はこうなっている。
test=# \d accounts
Table "public.accounts"
Column | Type | Collation | Nullable | Default
----------+---------------+-----------+----------+---------
aid | integer | | not null |
bid | integer | | |
abalance | integer | | |
filler | character(84) | | |
Partition key: HASH (aid)
Number of partitions: 3 (Use \d+ to list them.)
test=# \d accounts_p0
Table "public.accounts_p0"
Column | Type | Collation | Nullable | Default
----------+---------------+-----------+----------+---------
aid | integer | | not null |
bid | integer | | |
abalance | integer | | |
filler | character(84) | | |
Partition of: accounts FOR VALUES WITH (modulus 3, remainder 0)
この状態で、親テーブルaccountsに対してインデックスを作成する。
test=# CREATE INDEX accounts_idx ON accounts USING btree (aid);
CREATE INDEX
インデックス作成は成功したもよう。この状態で、accountsとaccounts_p0の状態を再度確認する。
test=# \d accounts
Table "public.accounts"
Column | Type | Collation | Nullable | Default
----------+---------------+-----------+----------+---------
aid | integer | | not null |
bid | integer | | |
abalance | integer | | |
filler | character(84) | | |
Partition key: HASH (aid)
Indexes:
"accounts_idx" btree (aid)
Number of partitions: 3 (Use \d+ to list them.)
test=# \d accounts_p0
Table "public.accounts_p0"
Column | Type | Collation | Nullable | Default
----------+---------------+-----------+----------+---------
aid | integer | | not null |
bid | integer | | |
abalance | integer | | |
filler | character(84) | | |
Partition of: accounts FOR VALUES WITH (modulus 3, remainder 0)
Indexes:
"accounts_p0_aid_idx" btree (aid)
親テーブル(accounts)にも子側のパーティションテーブル(accounts_p0)にもインデックスが設定されていることがわかる。この機能は、PostgreSQL 10のときに欲しいなと思っていたので嬉しい！
なお、PostgreSQL 10では、パーティションテーブルを持っているテーブルに対してインデックスを作成しようとすると、インデックス作成をサポートしていない、という内容のエラーになってしまい、インデックスを作成すること自体ができなかった。
どんなときに使えるのだろう？
ハッシュパーティションは、リストパーティションやレンジパーティションのように、不要になったパーティションをTRUNCATEしたりといった用途では使えない。ハッシュパーティションは、純粋にデータの分散化のためにだけに使うものなんだろう。
1つのデータベースサーバに、何個もストレージがついているような場合に、テーブルスペース指定を併用して、1つのパーティションテーブルを別個のストレージに割り当ててI/O分散を狙ったり、postgres_fdwを併用して、1つのパーティションテーブルを外部テーブルに割り当てて、I/OとCPU負荷の分散を狙ったりするのに使えるかもしれない。ハッシュパーティションとpostgres_fdwの連携については、別途検証してみるつもり。
おまけ
何個もパーティションがあったら作るの大変だよう、という人のために、psqlでは、\gexecメタコマンドが用意されているので、それをうまく利用すべし。
まず、SELECT文で、子側パーティションを作成するCREATE TABLE文をテキストとしれ生成する。その直後に、\gexecメタコマンドを実行すると、先ほどのSELECT文の実行結果をコマンドとして解釈して実行してくれる。これを応用すればパーティション数が100個あっても手間はかからない。
やっぱりpsql最高かよ！
test=# SELECT 'CREATE TABLE accounts_p' || i || ' PARTITION OF accounts FOR VALUES WITH (MODULUS 3, REMAINDER ' || i || ');' FROM (SELECT generate_series(0, 2) AS i) t;
?column?
------------------------------------------------------------------------------------------
CREATE TABLE accounts_p0 PARTITION OF accounts FOR VALUES WITH (MODULUS 3, REMAINDER 0);
CREATE TABLE accounts_p1 PARTITION OF accounts FOR VALUES WITH (MODULUS 3, REMAINDER 1);
CREATE TABLE accounts_p2 PARTITION OF accounts FOR VALUES WITH (MODULUS 3, REMAINDER 2);
(3 rows)
test=# \gexec
CREATE TABLE
CREATE TABLE
CREATE TABLE
test=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------+-------+-------
public | accounts | table | nuko
public | accounts_p0 | table | nuko
public | accounts_p1 | table | nuko
public | accounts_p2 | table | nuko
(4 rows)
おわりに
今回はPostgreSQL 11のハッシュパーティションを軽く触ってみただけなんだけど、たぶんこれが本当に威力を発揮するのは、postgres_fdwによる別サーバとの連携と、PostgreSQL 11で強化されたパーティションテーブルに対するパラレル処理が組み合わさった分散DB構成(更新までは対応できないけど)ときなんだと思う。
TODO
このへんもきちんと確認しておかないとなあ。
- 整数型以外の数値に対するハッシュパーティション挙動
- パーティションの増加・減少方式
- postgres_fdw連携