大量の時系列データはパーティションテーブルで取り込もう【PostgreSQL】

はじめに

aucfan エンジニアの @ngok です。

aucfanでは、商品の売買データや在庫データなどを収集・分析して、保有している在庫の価値を診断しています。

数十万商品について数百店舗の情報を日次で取込み、1日当たり数千万レコードになるデータを分析するにあたって、パフォーマンスチューニング行った際の最も効果的だったものをご紹介します。

（´-`）.｡oO（体験談をもとに記事を記載していますので、結果だけ知りたい方は記事の後半のパーティションテーブルについて記載している辺りから読んでください。）

それは性能テスト中のこと

大量なデータにシステムが耐えうるのか、性能テストを行いました。
使用しているシステム構成は下記のようになっています。

構成	内容
DB	PostgreSQL10系
データの取込みバッチ	Java(SpringBoot2)、Amazon S3に配置したCSVを取得し、DBに格納しやすいように整形してDBにデータ投入する。
集計・分析バッチ	Java(SpringBoot2)、DBからSELECTしたデータを集計と分析を行い、別のテーブルに格納する。

まずは普通に性能テストしてみた

概ね下記のような実行時間となりました。

日数	データの取込み時間	集計・分析の実行時間	合計時間
1	01時間04分	02時間24分	03時間28分
2	01時間05分	05時間21分	06時間26分
3	01時間05分	06時間46分	07時間51分
4	01時間05分	05時間06分	06時間11分
5	01時間06分	05時間50分	06時間56分
6	01時間06分	05時間38分	06時間44分
7	01時間04分	05時間53分	06時間57分
8	01時間03分	06時間34分	07時間37分
9	01時間05分	07時間59分	09時間04分
10	01時間07分	07時間32分	08時間39分
11	01時間10分	08時間25分	09時間35分
12	01時間09分	08時間08分	09時間17分
13	01時間10分	08時間39分	09時間49分
14	01時間09分	09時間50分	10時間59分
15	01時間10分	10時間00分	11時間10分
16	01時間10分	11時間39分	12時間49分
17	01時間12分	10時間56分	12時間08分
18	01時間11分	11時間59分	13時間10分

御覧の通り、日数の経過とともにデータ量が増えるのと合わせて、集計・分析の実行時間が徐々に増えています。

集計・分析には1週間や1か月といった範囲でデータ取得して分析する処理があり、1か月分に到達していない間はデータ量に応じて時間がかかるのはある程度は想定内でした。

順調♪順調♪と鼻歌交じりで眺めていました。

そして爆発

日数	データの取込み時間	集計・分析の実行時間	合計時間
19	01時間22分	2日12時間17分	2日13時間39分

そして、19日目に爆発しました。。。
1日分の集計をするのに1日以上かかっては全く業務に耐えられないですね。

何が起きたのか

データの取り込み時間は大きく変動していないことから、原因は集計・分析にあることは明白なので、集計・分析で使用しているSQLについて調査しました。

分析対象のデータを取得するSQLの実行計画に変化が

集計・分析で使用しているSQLの実行計画を、データ量が少ないDBと問題が発生したDBで比較すると異なる結果が得られました。

データ量が少ないDBでは要所でインデックスを活用したスキャン方法や結合が想定通り機能していました。

一方で、問題の発生したDBでは、インデックスを活用したスキャンや結合が採用されず、ほぼほぼフルテーブルスキャンのような状態でデータを走査していました。

(実行計画の詳細は割愛します。)

なぜ実行計画が変化したのか

PostgreSQLの実行計画を決定する統計情報が大きく関係していました。

実行計画を決定する プランナ は、PostgreSQL内部で各テーブル毎に持つ統計情報を基にして実行計画を決定します。

統計情報と実行計画の決定方法については詳細は割愛しますが、簡単に説明すると、 各カラムでよく登場する値やその頻出率 などが統計情報にあり、それらを基にクエリが効率よくデータを取得するよう、どのインデックスを採用してスキャンするかを決定するといった動作のようです。

実行計画を決定するために使用される統計情報は、バキュームが発生するたびに更新されます。
また、統計情報を作成するために内部でサンプリングするレコードは全レコードではなく一定数でランダムです。

そのため、前述したテストでは下記のような経緯で性能が悪くなったと考えられます。

1. 日数を進めている間にデータが増えた
2. 自動バキュームによって統計情報が更新された
3. 統計情報作成時にサンプリングしたレコードに偏りが発生した
4. 実行計画が効率的に動作しなくなってしまった

パーティションテーブルを使用して膨大なデータを分散させる

前述のとおり、データ量が大量になってしまったことに起因して実行計画が悪くなってしまったため、1テーブル辺りのレコード数を抑えていく必要があります。

そこで、動作が遅くなってしまったテーブルにパーティションテーブルを使用することにしました。

パーティションテーブルに変更してからの性能テスト

パーティションテーブルについての紹介は後述しますが、まずはパーティションテーブルを使用してから再開した性能テストについてご覧ください。

履歴のように日付を持った時系列データを持つテーブルについてパーティションテーブルに置き換えました。
データの性質上、1週間おきにサイクルがあるようなデータであるため今回は1週間ごとに子テーブルを作成しました。
結果は下記のとおりです。

日数	データの取込み時間	集計・分析の実行時間	合計時間
20	01時間03分	04時間07分	05時間10分
21	00時間59分	05時間58分	06時間57分
22	00時間56分	03時間07分	04時間03分
23	00時間58分	03時間41分	04時間39分
24	00時間57分	04時間12分	05時間09分
25	00時間57分	04時間36分	05時間33分
26	00時間59分	03時間52分	04時間51分
27	00時間57分	04時間07分	05時間04分
28	00時間57分	07時間46分	08時間43分
29	00時間55分	03時間59分	04時間54分
30	00時間55分	05時間22分	06時間17分
31	01時間00分	06時間37分	07時間37分
32	01時間01分	05時間34分	06時間35分
33	01時間03分	05時間59分	07時間02分
34	01時間01分	06時間43分	07時間44分
35	01時間02分	06時間06分	07時間08分
36	00時間55分	04時間50分	05時間45分
37	00時間57分	06時間03分	07時間00分
38	00時間57分	06時間10分	07時間07分
39	01時間00分	06時間14分	07時間14分
40	01時間02分	06時間04分	07時間06分

パーティションテーブル導入前のバッチの実行時間よりも早くすることが出来ました。

また、20日目移行、右肩上がりにならなずに安定しており、日数が経って総データ量が増えてもバッチの動作時間はある程度の範囲で安定させることが出来ました。

パーティションテーブルの紹介

パーティションテーブルは、親子関係のあるテーブルを作成し、親テーブルへの問い合わせが子テーブルに分散されるというテーブル構造になっています。
子テーブルもそれぞれ通常のテーブルと同様に存在するため、インデックスや統計情報などもそれぞれに持つことが出来ます。
問い合わせ時には各テーブルへの問い合わせが分散され、それぞれのテーブルにて並列に実行計画が実行されます。

下記のようなイメージです。

パーティションテーブル作成のSQLの例です。

-- 親テーブル作成
CREATE TABLE item_stock_history  (
  id bigserial not null
  , retailer_id integer not null
  , warehouse_id integer not null
  , history_date date not null
  , packing_type_id integer not null
  , item_code varchar(256) not null
  , item_name varchar(1024)
  , jan13 char(13)
  , jan8 char(8)
  , expiration_date date
  , available_stock_num integer
  , book_value decimal(20,2)
  , purchase_cost decimal(20,2)
  , created_by varchar(128)
  , created_at timestamp with time zone not null
  , updated_by varchar(128)
  , updated_at timestamp with time zone
  , deleted_by varchar(128)
  , deleted_at timestamp with time zone
) PARTITION BY RANGE (history_date) ;

-- 子テーブル作成
CREATE TABLE
  item_stock_history_20210101
PARTITION OF
  item_stock_history
FOR VALUES FROM ('2021-01-01') TO ('2021-01-08');

CREATE TABLE
  item_stock_history_20210108
PARTITION OF
  item_stock_history
FOR VALUES FROM ('2021-01-08') TO ('2021-01-16');

CREATE TABLE
  item_stock_history_20210101
PARTITION OF
  item_stock_history
FOR VALUES FROM ('2021-01-16') TO ('2021-01-23');

-- インデックス作成(子テーブルごとに必要)
CREATE INDEX
  item_stock_history_20210101_ix1 ON item_stock_history_20210101 (item_code);
CREATE INDEX
  item_stock_history_20210101_ix2 ON item_stock_history_20210101 (history_date);
CREATE INDEX
  item_stock_history_20210101_ix3 ON item_stock_history_20210101 (retailer_id);

CREATE INDEX
  item_stock_history_20210108_ix1 ON item_stock_history_20210108 (item_code);
CREATE INDEX
  item_stock_history_20210108_ix2 ON item_stock_history_20210108 (history_date);
CREATE INDEX
  item_stock_history_20210108_ix3 ON item_stock_history_20210108 (retailer_id);

CREATE INDEX
  item_stock_history_20210116_ix1 ON item_stock_history_20210116 (item_code);
CREATE INDEX
  item_stock_history_20210116_ix2 ON item_stock_history_20210116 (history_date);
CREATE INDEX
  item_stock_history_20210116_ix3 ON item_stock_history_20210116 (retailer_id);

上記テーブルに対してSQLを発行し、その実行計画を出してみました。

SELECT
 *
FROM item_stock_history
WHERE
 retailer_id = 1
 AND history_date >= '2021-01-03'
 AND history_date < '2021-01-20'
;

このように、実行計画が分散、並列化されています。

パーティションテーブルの詳細な作成方法等はPostgreSQLのドキュメントを参照ください。
https://www.postgresql.jp/document/10/html/ddl-partitioning.html

パーティションテーブルによって結果的に性能の改善を図ることができましたが、使用してみてよかった点と悪かった点がそれぞれ見えましたのでご紹介します。

良い点

1. 親テーブルへの問い合わせで全ての子テーブルへ問い合わせできる

親テーブルへの問い合わせで全ての子テーブルへの問い合わせになるため、既存のSQLへの影響が少なく済みます。
既に構築・稼働済みのシステムでもをSQL等を変更しなくても導入が可能です。

2. 子テーブルも普通のテーブルとして問い合わせ出来る

子テーブルも実態を持つテーブルであるため、通常のテーブル同様問い合わせ可能です。
必要に応じてテーブルへの問い合わせ(SQL)自体を分けることでDBへの負荷も調整できます。

(親テーブルへの問い合わせをすると、子テーブル全てに問い合わせしてしまうため、負荷がかかる場合があります。)

3. 親テーブルへの問い合わせは子テーブルごとに並列処理される

親テーブルへの問い合わせは子テーブルごとに並列処理されるため、実行計画等もも子テーブルごとに作成されます。
並列に実行計画が動作するのでクエリの実行速度が向上する場合があります。

4. テーブルの統計情報は子テーブルごとに作成される

実行計画の作成に使用される統計情報は、子テーブルごとにそれぞれ作成されます。
そのためデータ量による統計情報の偏りを防げます。

5. 一部の子テーブルのみをテーブルごと削除できる

一部の子テーブルを削除してもパーティションテーブル全体には影響がありません。
一定期間過ぎたデータを削除する運用などに使いやすいです。

悪い点

1. 振り分け条件に合致するテーブルがない場合には保存できない

PostgreSQL10系では、デフォルトの子テーブルを作成することが出来ません。
そのため、登録したいデータに応じて子テーブルを作成しておく必要があります。

※ PostgreSQL11系から作成できるようになったようです。

2. 子テーブルは自動作成されない

子テーブルは自動では作成されません。
上記の悪い点1と同様、都度必要に応じて作成する必要があります。

3. インデックスは子テーブルごとに作成する必要がある

インデックスは子テーブルごとに作成する必要があり、手動での作成が必要となります。

※ PostgreSQL11系からは、親テーブルに設定したインデックスを引き継いで子テーブル作成時に自動作成できるようになったようです。

3. 子テーブルをまたぐようなクエリは遅くなる場合がある

子テーブルをまたぐような条件での集計(GROUP BY)やソート(ORDER BY)のクエリは遅くなる場合があります。
子テーブルから必要なレコードを取得してから、作業領域上で操作するというように段階を踏んだプロセスが内部で実行されるためです

まとめ

下記のような場合にはまずパーティションテーブルの採用を検討した方が良いと感じました。

• 履歴のような時系列にデータが増え続けていくデータ
• 1テーブルが膨大な量になる想定のデータ

ただし、パーティションテーブルを採用した場合にはテーブルを管理する運用コストも増えてしまうため、その辺りを上手いこと折り合いを付ける必要があるため要注意です。

参考資料

• PostgreSQL 10.5文書
  • https://www.postgresql.jp/document/10/html/ddl-partitioning.html
  • https://www.postgresql.jp/document/10/html/planner-stats-details.html
  • https://www.postgresql.jp/document/10/html/monitoring-stats.html
  • https://www.postgresql.jp/document/10/html/planner-stats.html

あとがき

aucfanではElasticsearchなどの分散型のNoSQLデータベースを使用したシステムもありノウハウもあります。
今回のようなデータはES向きだったんだろうなーorz