PostgreSQLでテーブルをパーティショニングする

はじめに

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をパーティションキーとして範囲パーティションのパーティションテーブルを作成します。

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);

次に、作成したテーブルを親として、各月のパーティションを作成していきます。パーティションの作成では、親のパーティショニング方式および、パーティションキーに対応する境界を指定する必要があります。

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');

キー列にインデックスを作成し、必要に応じてその他のインデックスも定義します。キー列のインデックスは必須ではありませんが、多くの場合役に立ちます。また、キーの値が一意であることを意図している場合は、各パーティションに一意制約もしくは主キー制約を作成する方が良いです。

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です。

古いデータを削除する

基本的に、パーティションテーブルはそのまま残り続けることを意図したものではありません。古いデータを削除し、新しいパーティションを定期的に追加するのが一般的です。

通常、パーティションテーブルの古いデータを削除する場合、以下のように不要になったパーティションを削除します。

DROP TABLE item_sales_y2022m06;

この場合、すべてのレコードを個別に削除する必要がないため、高速に削除することができます。

または、パーティションテーブルからパーティションは削除しても、パーティション自体はアクセス可能な状態にしたい場合はDETACH PARITITIONを使います。

ALTER TABLE item_sales DETACH PARTITION item_sales_y2022m06;

こうすることで、item_salesテーブルに問い合わせた時にこの部分のデータは取得されなくなりますが、データ自体は残っているので、データを削除する前にバックアップなどの作業を行うことができます。

リストパーティションを作成する

今度はリストパーティションの作成について紹介します。先程のitem_salesテーブルに場所を示すregionを追加し、場所ごとにパーティションを分割します。

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);

次に、パーティションを作成します。

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にインデックスを張っておきます。

CREATE INDEX ON item_sales_tokyo (region);
CREATE INDEX ON item_sales_kanagawa (region);
CREATE INDEX ON item_sales_chiba (region);

さいごに

PostgreSQLでのパーティショニングについて、まとめました。

おすすめ書籍