パーティション — 絞り込みの単位である前に管理の単位

一言でいうと

PARTITION BYは、行をパーティションキーの値ごとに別々のパートに分けて格納させます。マージはパーティションをまたがないので、1か月分の行は常にその月のパートにしかありません。そのため、1か月分をまるごと削除したり、切り離したり、移したりする作業が、ファイル単位で終わります。クエリが速くなるのはおまけで、その効果は思ったより小さいです。スキップの大部分はソートキーが担います。

なぜ必要なのか

ログや注文のような時系列テーブルは、いずれ「古いものを消したい」という要求に出会います。前のモジュールで見たとおり、パートは変わりません。数行を削除するには、その行が入っているパートを書き直す必要があり(ミューテーション)、3か月前のデータを消すためにテーブル全体を書き直すのは筋が通りません。行が月ごとに別々のパートに入っていれば、話が変わります。7月のパートをテーブルから切り離せば終わりです。

公式ドキュメント(Table partitions)がパーティションを「主にデータ管理機能」と呼ぶ理由がこれです。同じドキュメントは、すべてのパーティションをまたぐクエリは、パーティションのないテーブルより通常遅くなるとも述べています。パーティションを「インデックスのように」使おうとして細かく分けた途端にパートの数が爆発し、前のモジュールのTOO_MANY_PARTSに逆戻りします。

どう動くのか

PARTITION BY toYYYYMM(ts)のテーブルの3つのパート、8月の範囲で絞り込んだときにMin-Maxがパートの1/3だけを残す様子、同じ7月をDROP PARTITIONで削除するとミューテーションなしでパートがまるごと外れ、ALTER DELETEで削除すると1つのミューテーションが3つのパートをすべて書き直す様子、DETACHのあとにATTACHしたパートが新しいブロック番号を受け取る様子、ATTACH PARTITION FROMがINSERTなしでパートをコピーする様子、日付と地域で細分化したテーブルのINSERTがパーティション460個で上限100に達して拒否される様子

INSERTはパーティションごとにパートを書きます。3か月分の150万行を1回で入れたら、パートが202607_1_1_0、202608_2_2_0、そして9月はブロックが2つに分かれて2つできました。名前の先頭の202607がpartition_idです。OPTIMIZE ... FINALはパーティションごとに別々にまとめるので、パートは3つになります。7月と8月をまとめたパートは、絶対にできません。

プルーニングは二重です。パートごとに、パーティションキーに使われた列(ts)の最小値・最大値が記録されているので、8月の範囲で絞り込むと、EXPLAINに次のように出ます。

Min-Max
  Keys: ts
  Parts: 1/3
  Granules: 62/183
Partition
  Keys: toYYYYMM(ts)
  Parts: 1/1

Min-Maxが、7月・9月のパートを開きもせずに捨てました。残ったパートの中では、前のモジュールのソートキーのインデックスが、あらためてグラニュールを選びます。パーティションキーに置き換えられない条件(例: toDayOfWeek(ts) = 1)は、Parts: 3/3で、何も捨てられません。

ところが、パーティションを外してもほとんど同じです。同じソートキー(region, ts)でパーティションだけがないテーブルでは、同じ8月のクエリが581,632行を読みました(パーティションありのテーブルは505,454行)。tsがソートキーの2番目の列で、前の列のregionが5種類しかないため、前のモジュールのgeneric exclusion searchが、8月の区間をほとんど見つけ出します。regionと8月を一緒に絞り込むと、むしろパーティションのないテーブルのほうが少なく読みました(106,496対114,688)。パーティションがもたらした利点は、境界のグラニュール数個の程度です。

管理作業はパート単位です。ALTER TABLE ... DROP PARTITION 202607は、7月のパートをテーブルから切り離します。system.mutationsには何も残らず、8月・9月のパートは名前すらそのままです。同じ行をALTER TABLE ... DELETE WHERE toYYYYMM(ts) = 202607で削除すると、ミューテーションが1つできて、part_logにMutatePartが3回記録されました。7月の行がまったくない8月・9月のパートまで、新しいバージョン(名前の末尾が_5)になったのです。ミューテーション自体は、最後のモジュールで詳しく扱います。

DETACH PARTITIONは、パートをテーブルのdetached/ディレクトリに移し、テーブルはその存在を忘れます(system.detached_partsに表示されます)。ATTACH PARTITIONで戻すと、パートが新しいブロック番号を受け取り、202609_3_4_1が202609_5_5_0になって戻ってきました。ATTACH PARTITION ... FROM 다른표は、構造が同じテーブルからパーティションをコピーしてきます(プレースホルダーは別のテーブル名です)。ドキュメントのとおり、コピー元からもコピー先からも削除せず、INSERTクエリなしでパートがそのまま渡ってきます。1か月分を保管用のテーブルに切り離しておく、よくある方法です。

細かく分けすぎると止まります。PARTITION BY (toDate(ts), region)は、3か月で460個のパーティションになります。そのテーブルに全体を入れたら、Too many partitions for single INSERT block (more than 100)エラー(252)でINSERT全体が拒否され、1行も入りませんでした。上限は設定max_partitions_per_insert_block(デフォルトは100)です。エラーメッセージ自体が、「パーティションはSELECTを速くするためのものではありません」と述べています。

現場での姿

最もよくある間違いは、「日付でよく絞り込むから日付でパーティション」です。1日ごとのパーティションは1年で365個になり、ユーザーや地域を混ぜると数千個になります。パーティションごとにパートが別々にあり、マージはパーティションをまたがないので、小さなパートが際限なくたまります。ドキュメント(Choosing a partitioning key)は、カーディナリティの低いパーティションキーを選ぶよう述べています。たいていは月で十分です。日付範囲のフィルターは、ソートキーに時間の列を入れて解決します。

逆に、パーティションが力を発揮するのは保管期間の管理です。「13か月より古いデータの削除」がDELETEなら、そのたびにミューテーションが全パートを書き直しますが、月単位のパーティションならDROP PARTITION 1行で終わります。再処理が必要な月を切り離し(DETACH)、修正したデータを別のテーブルで埋めて取り込む(ATTACH ... FROM / REPLACE PARTITION)運用も、同じ原理です。

次のラボですること

ptn.salesを月単位のパーティションで作って3か月分を入れ、パーティションごとのパートを書き込みます。8月のクエリのEXPLAINで、Min-Maxが残したパートの数を読み、パーティションのない同じソートキーのテーブルとrows_readを比べます。同じ7月をDROP PARTITIONとDELETEで削除してミューテーションの数を比べ、9月を切り離してから付け直して名前が変わることを見て、7月をINSERTなしで別のテーブルにコピーします。最後に、日付と地域のパーティションを持つテーブルがINSERTで拒否されることを確認します。