本記事は PostgreSQL Advent Calendar 2018の3日目です。
昨日は@meijikさんの「[勝手に更新] PostgreSQLの分析関数の衝撃2018(前編)」でした。
本記事のテーマ
PostgreSQLの宣言的パーティショニングでサポートされない細やかな振り分け条件について考える。
宣言的パーティショニング
PostgreSQLで標準実装されている宣言的パーティショニングでは、以下のパーティショニング方式がサポートされている。
- レンジパーティション
- リストパーティション
- ハッシュパーティション
従来型パーティショニング
マニュアルでは、従来の手作りパーティションでないと実装できない例外的な振り分け条件によるパーティションについて記載があるが、その実行例はわりと普通のレンジパーティションになっていて、どんな時に使える（使わざるを得ない）のかイメージしづらい。
そこで、従来型パーティショニングならどこまでできるかを考えてみた、というのがきっかけ。
パーティショニングに期待する要素
テーブル・パーティショニングでは一般に以下の機能が期待される。
| 到達点 | やりたいこと |
|---|---|
| 第1目標 | テーブル継承できる。 SELECT * FROM 親; で子に格納したデータが見えること。 |
| 第2目標 | DMLが伝播する。 INSERT INTO 親 ・・で、値に応じて正しく子にデータが 伝播すること。UPDATEやDELETEも同じ。 |
| 第3目標 | プルニングが効く。 WHERE条件がチェック制約に当てはまるかどうか評価し 実行計画の時点では不要な検索が排除される。 |
| 第4目標 | TRUNCATEでデータを捨てる。 PostgreSQLで大量行のDELETEを行うと長時間必要とし VACUUMも必要。 子テーブル単位でTRUNCATEできると効率が良い。 |
本記事ではこの第1～第4目標を変則的な振り分けルールでも実装するところを目指す。
変則的な振り分けルールについて
（リアルに要望があるか、性能が出るかは置いておいて）以下のようなケースを考えてみた。
- リストパーティションの値が決めきれないパターン（可変リスト）
- リストパーティションの個数が多すぎるパターン
- 座標と連動した地理別パーティション
可変リストパーティション
商品の販売履歴テーブルを商品カテゴリ別にパーティション分割したいが、商品マスタには大量アイテムが登録されており、「個数が多すぎてリスト・パーティション定義にできない」「アイテムは不定期に追加されるのでリストが動的に変わらないといけない」みたいなケース。
地理別パーティション
よくある「東西でDBを二つに分けてる」とか「県別パーティション」みたいなのの細かい版。座標を含むデータをINSERTしたときに、何県かを判定して県別パーティションに入れてくれる。
TRUNCATE = 20xx年、ゾンビウイルスのパンデミックに伴い、軍が〇〇州を隔離
以降、地図から消滅した・・・みたいな。
ｵｿﾛｼｲﾈｰ
可変リストを作ってみる
サンプル
商品名を持ってるproduct表と、商品の販売履歴を持ってるsales_history表を考える。
=# CREATE TABLE products (priduct_id int, product_name text, category_code int);
CREATE TABLE
=# INSERT INTO products VALUES
( 1, '炊飯器', 1 )
,( 2, '冷蔵庫', 1 )
,( 3, '電子レンジ', 1 )
,( 4, 'フライパン', 2 )
,( 5, '土鍋', 2 )
,( 6, '包丁', 2 )
,( 7, '水の激落〇くん', 3 )
,( 8, 'ビ〇レu ハンドジェル', 3)
,( 9, 'キュ〇〇ト 泡スプレー', 3);
データはこんな感じである。
| priduct_id | product_name | category_code |
|---|
1 | 炊飯器 | 1
2 | 冷蔵庫 | 1
3 | 電子レンジ | 1
4 | フライパン | 2
5 | 土鍋 | 2
6 | 包丁 | 2
7 | 水の激落〇くん | 3
8 | ビ〇レu ハンドジェル | 3
9 | キュ〇〇ト 泡スプレー | 3
※カテゴリは「1：白物家電」「2：調理器具」「3：日用品」みたいな感じか。
※我が家のキッチンを思い浮かべて気に入ってるものシリーズなわけだが、良いフライパンはマジで良い。とろふわオムライスも薄焼きで包むオムライスも習得した。
=# CREATE TABLE sales_history ( sales_hist_id bigint, sales_hist_ts timestamp ,product_id int , customer_id int );
=# INSERT INTO sales_history VALUES
(101, now(), 1, 54321)
,(102, now(), 2, 54321)
,(103, now(), 3, 54321)
,(104, now(), 4, 54321)
,(105, now(), 5, 54321)
,(106, now(), 6, 54321)
,(107, now(), 7, 54321)
,(108, now(), 8, 54321)
,(109, now(), 9, 54321)
;
=# SELECT * FROM sales_history ;
sales_hist_id | sales_hist_ts | product_id | customer_id
---------------+---------------------------+------------+-------------
101 | 04-DEC-18 01:08:49.617071 | 1 | 54321
102 | 04-DEC-18 01:08:49.617071 | 2 | 54321
103 | 04-DEC-18 01:08:49.617071 | 3 | 54321
104 | 04-DEC-18 01:08:49.617071 | 4 | 54321
105 | 04-DEC-18 01:08:49.617071 | 5 | 54321
106 | 04-DEC-18 01:08:49.617071 | 6 | 54321
107 | 04-DEC-18 01:08:49.617071 | 7 | 54321
108 | 04-DEC-18 01:08:49.617071 | 8 | 54321
109 | 04-DEC-18 01:08:49.617071 | 9 | 54321
(9 行)
※「customer_id」= 54321番は喜田さんというわけですね。
だがしかしここで問題が発生。
sales_history表は大量になるので時系列パーティショニングしたいとか、一定期間を過ぎたデータは消したい、という要望が出てくると、商品によって保証期間が異なるので簡単にポリシーを決められない。
「白物家電」は10年ぐらい使うでしょ？と考えると全データ10年保持みたいな大変な量のデータを持ち続けなければならない。
そこで可変リストパーティショニング
INSERTしたい各商品の商品カテゴリをチェックして「白物家電history」「調理器具history」「日用品history」に振り分けてINSERTしてくれるパーティショニングがあればなんとかなるのではないか。
（本当は「●●history」× 「レンジ（時系列）」パーティショニングしたいところだが単純化のために時系列は今回は無視する。）
「各商品の商品カテゴリをチェック」するには、以下のようなサブクエリで実現できないかと思ったが、
=# CREATE TABLE history_shiromono (
CHECK ( product_id IN ( SELECT product_id FROM products WHERE category_code = 1 )))
INHERITS (sales_history);
以下のエラーで失敗する。
ERROR: cannot use subquery in check constraint
サブクエリが使えないとなると、パーティション定義にリテラル値を直接書く（=固定リスト）以外ないのかっ。。。
ちょっと頑張ってみた。
サブクエリがダメなら関数に値を返させればイイじゃない。
/* ファンクション定義 */
/* 複数行を返すファンクションはCHECK制約で使えなかったのでARRAYで1行にして返す実装とした */
=# CREATE OR REPLACE FUNCTION check_prod_in_category(integer) RETURNS integer[]
AS $$
SELECT array_agg(product_id) FROM products WHERE category_code = $1;
$$
LANGUAGE SQL
;
=# SELECT check_prod_in_category(1);
check_prod_in_category
------------------------
{1,2,3}
check_prod_in_category()の入出力
| 入力（カテゴリ） | 結果（カテゴリに属する商品リスト） |
|---|---|
| 1（白物家電） | {1,2,3} |
| 2（調理器具） | {4,5,6} |
| 3（日用品） | {7,8,9} |
パーティション定義に上記の関数を使ってみる。
=# CREATE TABLE history_shiromono (
CHECK ( product_id = ANY(check_prod_in_category(1) ))) INHERITS (sales_history);
=# CREATE TABLE history_chourikigu (
CHECK ( product_id = ANY(check_prod_in_category(2) ))) INHERITS (sales_history);
=# CREATE TABLE history_nichiyouhin (
CHECK ( product_id = ANY(check_prod_in_category(3) ))) INHERITS (sales_history);
「指定したcategory_codeに属する製品リストを取得する関数」を使ってprodcut_idに一致することを調べるCHECK制約でパーティション表を作成することができた。
ポイントは、CHECK制約に固定リストを書いておらず、都度関数の結果を見に行くので、products表が変更されれば、以降の振り分け条件は新しい内容に従ってくれる点である。
調子に乗ってトリガ作成
関数を利用して親テーブルにINSERTしたときに発動し、子テーブルに内容を伝播してくれるトリガを作成する。単純にするために、今回はUPDATEやDELETEは考慮しない。
=# CREATE OR REPLACE FUNCTION sales_hist_insert_trigger_func()
RETURNS TRIGGER AS $$
BEGIN
IF ( array[NEW.product_id] <@ check_prod_in_category(1)) THEN
INSERT INTO history_shiromono VALUES (NEW.*);
ELSIF ( array[NEW.product_id] <@ check_prod_in_category(2) ) THEN
INSERT INTO history_chourikigu VALUES (NEW.*);
ELSIF ( array[NEW.product_id] <@ check_prod_in_category(3) ) THEN
INSERT INTO history_nichiyouhin VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Cannot find a matching category. Fix the sales_hist_insert_trigger_func() function!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
=# CREATE TRIGGER sales_hist_insert_trigger
BEFORE INSERT ON sales_history
FOR EACH ROW EXECUTE PROCEDURE sales_hist_insert_trigger_func();
トリガ作成に成功。テーブル定義はこんな感じで、子テーブルとトリガが見えている。
=# \d+ sales_history
テーブル "public.sales_history"
列 | 型 | 照合順序
---------------+-----------------------------+----------
sales_hist_id | bigint |
sales_hist_ts | timestamp without time zone | ・・・
product_id | integer |
customer_id | integer |
トリガー:
sales_hist_insert_trigger BEFORE INSERT ON sales_history
FOR EACH ROW EXECUTE PROCEDURE sales_hist_insert_trigger_func()
子テーブル: history_chourikigu,
history_nichiyouhin,
history_shiromono
動作テスト
上のほうでINSRTした喜田のお買い物履歴が残っているので一旦削除して・・・
=# TRUNCATE sales_history ;
TRUNCATE TABLE
=# SELECT * FROM sales_history ;
sales_hist_id | sales_hist_ts | product_id | customer_id
---------------+---------------+------------+-------------
(0 行)
親へのINSERTで適切な子にINSERTされるか
成功！（パーティショニングの第2目標クリア）
=# INSERT INTO sales_history VALUES
(101, now(), 1, 54321)
,(102, now(), 2, 54321)
,(103, now(), 3, 54321)
,(104, now(), 4, 54321)
,(105, now(), 5, 54321)
,(106, now(), 6, 54321)
,(107, now(), 7, 54321)
,(108, now(), 8, 54321)
,(109, now(), 9, 54321)
;
INSERT 0 0
=# SELECT * FROM history_shiromono ;
sales_hist_id | sales_hist_ts | product_id | customer_id
---------------+---------------------------+------------+-------------
101 | 04-DEC-18 02:37:20.044115 | 1 | 54321
102 | 04-DEC-18 02:37:20.044115 | 2 | 54321
103 | 04-DEC-18 02:37:20.044115 | 3 | 54321
=# SELECT * FROM history_chourikigu ;
sales_hist_id | sales_hist_ts | product_id | customer_id
---------------+---------------------------+------------+-------------
104 | 04-DEC-18 02:37:20.044115 | 4 | 54321
105 | 04-DEC-18 02:37:20.044115 | 5 | 54321
106 | 04-DEC-18 02:37:20.044115 | 6 | 54321
=# SELECT * FROM history_nichiyouhin ;
sales_hist_id | sales_hist_ts | product_id | customer_id
---------------+---------------------------+------------+-------------
107 | 04-DEC-18 02:37:20.044115 | 7 | 54321
108 | 04-DEC-18 02:37:20.044115 | 8 | 54321
109 | 04-DEC-18 02:37:20.044115 | 9 | 54321
親のSELECTで子のデータを見れるか
成功！（パーティショニングの第1目標クリア）
=# SELECT * FROM sales_history ;
sales_hist_id | sales_hist_ts | product_id | customer_id
---------------+---------------------------+------------+-------------
101 | 04-DEC-18 02:37:20.044115 | 1 | 54321
102 | 04-DEC-18 02:37:20.044115 | 2 | 54321
103 | 04-DEC-18 02:37:20.044115 | 3 | 54321
104 | 04-DEC-18 02:37:20.044115 | 4 | 54321
105 | 04-DEC-18 02:37:20.044115 | 5 | 54321
106 | 04-DEC-18 02:37:20.044115 | 6 | 54321
107 | 04-DEC-18 02:37:20.044115 | 7 | 54321
108 | 04-DEC-18 02:37:20.044115 | 8 | 54321
109 | 04-DEC-18 02:37:20.044115 | 9 | 54321
パーティションキーをWHERE条件にしたときに、欲しい子だけにアクセスするか
失敗！（パーティショニングの第3目標ダメ）
実行計画をみると、すべての子テーブルにアクセスして、当該条件にマッチする行がないことを調べてしまっている。これはパーティショニングに期待する「チェック制約を用いたプルニング」ができていないことを意味する。
=# EXPLAIN ANALYZE
SELECT * FROM sales_history WHERE product_id = 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..88.88 rows=25 width=24) (actual time=0.021..0.035 rows=1 loops=1)
-> Seq Scan on sales_history (cost=0.00..0.00 rows=1 width=24) (actual time=0.008..0.008 rows=0 loops=1)
Filter: (product_id = 1)
-> Seq Scan on history_shiromono (cost=0.00..29.62 rows=8 width=24) (actual time=0.012..0.015 rows=1 loops=1)
Filter: (product_id = 1)
Rows Removed by Filter: 2
-> Seq Scan on history_chourikigu (cost=0.00..29.62 rows=8 width=24) (actual time=0.006..0.006 rows=0 loops=1)
Filter: (product_id = 1)
Rows Removed by Filter: 3
-> Seq Scan on history_nichiyouhin (cost=0.00..29.62 rows=8 width=24) (actual time=0.004..0.005 rows=0 loops=1)
Filter: (product_id = 1)
Rows Removed by Filter: 3
Planning time: 0.551 ms
Execution time: 0.099 ms
(14 行)
実行計画を決める時点で、パーティションを見つけるための check_prod_in_category()の結果を知らないといけないので難しいだろう。
ではどうするか。 パーティションキー列であるproduct_idにインデックスを追加し、
=# CREATE INDEX partkey_shiromono ON history_shiromono(product_id);
CREATE INDEX
=# CREATE INDEX partkey_chourikigu ON history_chourikigu(product_id);
CREATE INDEX
=# CREATE INDEX partkey_nichiyouhin ON history_nichiyouhin(product_id);
CREATE INDEX
データを増やして確認したところ、全パーティションにアクセスするものの、行の存在チェックにはインデックスを使い、該当0行であることはすぐに判断できることは確認できた。
=# EXPLAIN ANALYZE
SELECT * FROM sales_history WHERE product_id = 1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=10000000000.00..10000000022.67 rows=61 width=24) (actual time=0.046..0.091 rows=58 loops=1)
-> Seq Scan on sales_history (cost=10000000000.00..10000000000.00 rows=1 width=24) (actual time=0.004..0.004 rows=0 loops=1)
Filter: (product_id = 1)
-> Bitmap Heap Scan on history_shiromono (cost=4.59..7.32 rows=58 width=24) (actual time=0.041..0.058 rows=58 loops=1)
Recheck Cond: (product_id = 1)
Heap Blocks: exact=2
-> Bitmap Index Scan on partkey_shiromono (cost=0.00..4.58 rows=58 width=0) (actual time=0.025..0.025 rows=58 loops=1)
Index Cond: (product_id = 1)
-> Index Scan using partkey_chourikigu on history_chourikigu (cost=0.14..7.68 rows=1 width=24) (actual time=0.009..0.009 rows=0 loops=1)
Index Cond: (product_id = 1)
-> Index Scan using partkey_nichiyouhin on history_nichiyouhin (cost=0.14..7.68 rows=1 width=24) (actual time=0.006..0.006 rows=0 loops=1)
Index Cond: (product_id = 1)
Planning time: 1.327 ms
Execution time: 0.221 ms
(14 行)
余計なSeqScanはしなくていいんだ。よかった。
子テーブルを指定してTRUNCATEできるか。
成功！（パーティショニングの第4目標クリア）
まあこれは当然できるでしょう、という感じ。
保持期間が一番短い「日用品history」をTRUNCATEしてみる。
=# TRUNCATE history_nichiyouhin;
TRUNCATE TABLE
本当は、「日用品 × 時系列」までやってあるとちゃんと古いものを消す運用ができる。
まとめ
テーブル・パーティショニングへの期待が、可変リストパーティションでどこまで実現されたか。
| 到達点 | 確認項目 | 結果 |
|---|---|---|
| 第1目標 | テーブル継承できるか | 成功 |
| 第2目標 | DMLが伝播するか | 成功 |
| 第3目標 | プルニングが効くか | 失敗(※) |
| 第4目標 | 子のTRUNCATEできるか | 成功 |
※インデックスを使って、余分な子へのアクセスは極力抑えられることは確認できた。
余談
今回の思い付きは、Zabbixのデータ保管先にPostgreSQLのパーティショニングを利用しようとした際、「Zabbix上では収集したメトリックごとに保持期限を設定できる」けど、「データベース上では全履歴を対象にした時系列パーティションのみ」という作りであることを知ったのがきっかけ。
長期保管したいメトリックがあると、それに合わせて全データを残さないといけない、ということを知ってなんかいい方法ないかなーと考えた。
Zabbixを使う上でのイイ例が出せればよかったけど、リアルな運用に耐える性能・エラーハンドリングなど考えてないので、適当なサンプルでアイデアを書き留めてみた。
明日のアドカレ
明日はplusultra氏です。
RDS PostgreSQLとOracle互換関数の話のようですので、即実用できる有難いネタかもしれないですね！期待です！！！