PostgreSQLパーティショニングのススメ

Pocket

こんにちは 村上です。

この記事はTECHSCORE Advent Calendar 2014、9日目の記事です。

本日はパーティショニングについて書きたいと思います。

データの分割

システム開発をする上でデータを分割したい時がよくあると思います。
例えば

  1. マルチテナントな時
  2. スキーマ、テーブルスペースでデータを分けたい
  3. データ量増加により、何かのキーでテーブルを分けたい

などです。

今回は上記の3つめについて書きます。

テーブルのパーティショニング

データを分割(パーティショニング)したいものにはどんなものがあるか?

例えば、履歴データ
ユーザーの履歴データを格納するデータは日を追うごとに増えていきます。

他には、お気に入り商品
この場合、最大で「ユーザー数 ✕ 商品数」のデータが格納されます。

こういったテーブルに対してはパーティショニングをする必要がでてきます。

データを分割する場合は、単純にテーブルを分ければいいのですが、アプリケーションからはめんどくさい実装が必要になります。

たとえば、年月で判断して検索するテーブルを判断したり、年月をまたぐ場合はデータを統合したり。。。

そのため、パーティショニングはDBレイヤーで行うのがベストです。

商用のDBではこのパーティショニングが簡単にできるものもありますが、PostgreSQLの場合は単純にはいきません。

PostgreSQLによるパーティショニング

PostgreSQLのパーティショニングは下記の要領で行います。

  1. 分割元となるテーブルの作成
  2. 分割したデータが入るテーブルの作成
  3. データを分割する関数の作成
  4. データを分割するトリガを作成

といった感じになります。

試しに商品テーブルを「type」によってパーティショニングしてみましょう。

テーブルレイアウトはこんな感じです。

次に下記のSQLを実行して、分割したデータが入るテーブルを作ります。

items1を継承したテーブルです。
これらのテーブルにはCHECK制約でどのタイプが格納されるかを定義しています。

次に関数を作成します。

ポイントは7-8行目です。
「items1_type」とtypeの項目を文字列結合して、挿入先をtypeによって分けています。
これで、挿入時にデータが分割されて挿入されていきます。

関数が作成できたらこの関数を実行するトリガを作成します。

itemsの行ごとの挿入前にテーブル分割用の関数を実行するように設定します。

これで準備が整いました。
データを挿入してみましょう。
挿入先はデータの分割元となるテーブルに対して行います。

では分割できているかどうか確認してみましょう。

とりあえず、データは挿入されて分割もできています。
実行計画も見てみましょう。

実行計画を見るとCHECK制約が効いており余計なテーブルスキャンが行われていないことも確認できます。

ではデータの削除をしてみましょう。

データも綺麗に消えてくれますね。

その他のコマンド

SQLではテーブル分割がうまくいくことがわかりました。
ではCOPYやtruncateといったコマンドではどうでしょうか?

COPY IMPORT

COPYコマンドで返される件数が0件になってしまいました。
ですが、取込はできているようです。

COPY EXPORT

EXPORTも0件で返されます。
しかも!!

ゼロ件。。。

EXPORTは対応できないみたいです。。。
ですが、SQLではEXPORTできるようです。

TRUNCATE

truncateはできてそうですね。

運用では

PostgreSQLのパーティショニングですが、実際の運用でも使用しています。
1000万件以上のテーブル(中には億)に対してパーティショニングを実施しています。

その中で得たノウハウも紹介します。

    • CHECK制約が複雑なものは効かない

テーブルの項目の値のみでCHECK制約を設ける場合は問題ありませんが、少し複雑になった場合は機能しません。
例えば、「お気に入り商品」などをユーザーごとに管理したい場合では、ユーザーIDを100で割ったあまりをsuffixとしてテーブルを分割する場合は、検索時にCHECK制約がうまく動作せず、パーティショニングされたテーブルを全件SCANしてしまいます。こういった場合はCHECK制約用の項目を設けて検索の条件に含めるようにした方が良いです。

    • 分割数

PostgreSQLのドキュメントではパーティショニングは100個までを推奨しており、何千ものパーティションを使用することは避けるように書かれています。
PostgreSQLドキュメント
100個以上のパーティショニングは運用でもやっておりません。

    • レスポンス

レスポンスが気になったことはありません。(CHECK制約がうまく動作している時!!)
あと、COPYコマンドによる大量のデータIMPORT(1000万件ほど)はパーティショニングがなければ、1分半ほどですみますが、パーティショニングしていると8分ぐらいかかります。
大量のデータ投入の際はパーティショニング先のテーブルに対してCOPYコマンドを実行する方が速いです。

Pocket

Comments are closed, but you can leave a trackback: Trackback URL.