はじめに
RDBで大量のデータを保存する必要がある場合、一般的な対策の一つとして、テーブルのパーティショニングがあげられると思います。
PostgreSQLでのパーティショニングはやったとこがなかったので、調べた内容をまとめます。
パーティショニングとは
PostgreSQLでのパーティショニングを説明する前に、一般的なパーティショニングの効果について、簡単に紹介します。
パーティショニングとは、論理的には1つの大きなテーブルを物理的に小さなテーブル（パーティションテーブル）に分割する手法です。これによるメリットは以下のとおりです。
- 特定の条件下での問い合わせのパフォーマンスが向上する（インデックスを小さくして、よく使われる部分がメモリに収まりやすくなる）
- 問い合わせや更新の大部分が一つのパーティションに対して行われる場合、パフォーマンスが向上する（テーブル全体の代わりにパーティションテーブルにアクセスする）
- データの一括挿入・削除よりパーティションを削除するほうが高速
- 滅多に使用されない部分をより安価なストレージメディアへ以降できる
PostgreSQLでのパーティショニング
ここからは、PostgreSQLでのパーティショニングについて見ていきます。
パーティションには、他のパーティションとは異なる独自のインデックス、制約、デフォルト値を定義することができます。また、インデックスはパーティションごとにその都度定義する必要があります。
また、通常のテーブルをパーティションテーブルに変更する、および、その逆はできません。
パーティションテーブルでの制約
個々のパーティションは継承を背景にパーティションテーブルと紐付けられますが、継承の機能のうちの一部はパーティションでは使用できません。以下にいくつかの重要な例外を示します。
- パーティションテーブルの
CHECK制約とNOT NULL制約はすべてのパーティションに継承されるが、パーティションテーブルでNOT INHERITの印をつけたCHECK制約を作ることはできない -
ONLYを使ってパーティションテーブルのみに制約を追加する、または削除するのは、パーティションが1つも存在しない場合のみ行える - パーティションは親に存在しない列を持つことができない。また、パーティションの作成後に
ALTER TABLEでパーティションに列を追加することもできない - 親テーブルの列に存在する
NOT NULL制約をパーティションの列から削除することはできない
パーティショニングの種類
パーティショニングには3種類の方法があります。
1つ目は範囲パーティショニングで、これはキー列または、キー列の集合で定義される範囲ごとにパーティションを分ける方法です。例としては、
created_at
が
2022/06/01
〜
2022/06/30
の範囲で分割します。
2つ目はリストパーティションで、各パーティションに現れるキーを明示的に列挙することで、分割します。
3つ目はハッシュパーティションで、パーティションキーの値に対してハッシュ値を作成し、そのハッシュ値を分割数で割った余りで分割します。
なお、ハッシュパーティションはPostgreSQL 11以降で利用可能です。
パーティションテーブルを定義する
それでは、実際にパーティションテーブルを定義してみます。とある販売システムでいつ、何の商品を、何個販売したかを記録するitem_salesテーブルを作る場合例に見ていきます。
このテーブルを月ごとのパーティションに分けたいとすると、まず、
created_at
をパーティションキーとして範囲パーティションのパーティションテーブルを作成します。
| 1 2 3 4 5 6 | CREATE TABLE item_sales ( item_id int NOT NULL, qty int NOT NULL DEFAULT 0, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) PARTITION BY RANGE (created_at); |
次に、作成したテーブルを親として、各月のパーティションを作成していきます。パーティションの作成では、親のパーティショニング方式および、パーティションキーに対応する境界を指定する必要があります。
| 1 2 3 4 5 6 | CREATE TABLE item_sales_y2022m06 PARTITION OF item_sales FOR VALUES FROM ('2022-06-01 00:00:00') TO ('2022-07-01 00:00:00'); CREATE TABLE item_sales_y2022m07 PARTITION OF item_sales FOR VALUES FROM ('2022-07-01 00:00:00') TO ('2022-08-01 00:00:00'); CREATE TABLE item_sales_y2022m08 PARTITION OF item_sales FOR VALUES FROM ('2022-08-01 00:00:00') TO ('2022-09-01 00:00:00'); |
キー列にインデックスを作成し、必要に応じてその他のインデックスも定義します。キー列のインデックスは必須ではありませんが、多くの場合役に立ちます。また、キーの値が一意であることを意図している場合は、各パーティションに一意制約もしくは主キー制約を作成する方が良いです。
| 1 2 3 | CREATE INDEX ON item_sales_y2022m06 (sale_date); CREATE INDEX ON item_sales_y2022m07 (sale_date); CREATE INDEX ON item_sales_y2022m08 (sale_date); |
最後に、
postgresql.conf
でconstraint exclusionが無効になっていないことを確認します。これが無効になっていると、問い合わせが最適化されません。
ちなみに、constraint exclusionのデフォルト値は
partition
です。
古いデータを削除する
基本的に、パーティションテーブルはそのまま残り続けることを意図したものではありません。古いデータを削除し、新しいパーティションを定期的に追加するのが一般的です。
通常、パーティションテーブルの古いデータを削除する場合、以下のように不要になったパーティションを削除します。
| 1 | DROP TABLE item_sales_y2022m06; |
この場合、すべてのレコードを個別に削除する必要がないため、高速に削除することができます。
または、パーティションテーブルからパーティションは削除しても、パーティション自体はアクセス可能な状態にしたい場合は
DETACH PARITITION
を使います。
| 1 | ALTER TABLE item_sales DETACH PARTITION item_sales_y2022m06; |
こうすることで、item_salesテーブルに問い合わせた時にこの部分のデータは取得されなくなりますが、データ自体は残っているので、データを削除する前にバックアップなどの作業を行うことができます。
リストパーティションを作成する
今度はリストパーティションの作成について紹介します。先程の
item_sales
テーブルに場所を示す
region
を追加し、場所ごとにパーティションを分割します。
| 1 2 3 4 5 6 7 | CREATE TABLE item_sales ( item_id int NOT NULL, region varchar(8) NOT NULL, qty int NOT NULL DEFAULT 0, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) PARTITION BY LIST (region); |
次に、パーティションを作成します。
| 1 2 3 | CREATE TABLE item_sales_tokyo PARTITION OF item_sales FOR VALUES IN ('東京'); CREATE TABLE item_sales_kanagawa PARTITION OF item_sales FOR VALUES IN ('神奈川'); CREATE TABLE item_sales_chiba PARTITION OF item_sales FOR VALUES IN ('千葉'); |
最後に
region
にインデックスを張っておきます。
| 1 2 3 | CREATE INDEX ON item_sales_tokyo (region); CREATE INDEX ON item_sales_kanagawa (region); CREATE INDEX ON item_sales_chiba (region); |
さいごに
PostgreSQLでのパーティショニングについて、まとめました。
おすすめ書籍
