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

1 はじめに
2 パーティショニングとは
3 PostgreSQLでのパーティショニング
- 3.1 パーティションテーブルでの制約
- 3.2 パーティショニングの種類
4 パーティションテーブルを定義する
- 4.1 古いデータを削除する
- 4.2 リストパーティションを作成する
5 さいごに
6 おすすめ書籍

はじめに

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

(

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

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;

1	DROP TABLE item_sales_y2022m06;

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

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

 ALTER TABLE item_sales DETACH PARTITION item_sales_y2022m06;

1	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

(

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 ('千葉');

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

CREATE INDEX ON item_sales_tokyo (region);

CREATE INDEX ON item_sales_kanagawa (region);

CREATE INDEX ON item_sales_chiba (region);

さいごに

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

日	月	火	水	木	金	土
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30