BackEnd

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

投稿日:

はじめに

RDBで大量のデータを保存する必要がある場合、一般的な対策の一つとして、テーブルのパーティショニングがあげられると思います。

PostgreSQLでのパーティショニングはやったとこがなかったので、調べた内容をまとめます。

パーティショニングとは

PostgreSQLでのパーティショニングを説明する前に、一般的なパーティショニングの効果について、簡単に紹介します。

パーティショニングとは、論理的には1つの大きなテーブルを物理的に小さなテーブル(パーティションテーブル)に分割する手法です。これによるメリットは以下のとおりです。

  • 特定の条件下での問い合わせのパフォーマンスが向上する(インデックスを小さくして、よく使われる部分がメモリに収まりやすくなる)
  • 問い合わせや更新の大部分が一つのパーティションに対して行われる場合、パフォーマンスが向上する(テーブル全体の代わりにパーティションテーブルにアクセスする)
  • データの一括挿入・削除よりパーティションを削除するほうが高速
  • 滅多に使用されない部分をより安価なストレージメディアへ以降できる

PostgreSQLでのパーティショニング

ここからは、PostgreSQLでのパーティショニングについて見ていきます。

パーティションには、他のパーティションとは異なる独自のインデックス、制約、デフォルト値を定義することができます。また、インデックスはパーティションごとにその都度定義する必要があります。

また、通常のテーブルをパーティションテーブルに変更する、および、その逆はできません。

パーティションテーブルでの制約

個々のパーティションは継承を背景にパーティションテーブルと紐付けられますが、継承の機能のうちの一部はパーティションでは使用できません。以下にいくつかの重要な例外を示します。

  • パーティションテーブルの CHECK制約NOT NULL制約 はすべてのパーティションに継承されるが、パーティションテーブルで NOT INHERIT の印をつけた CHECK制約 を作ることはできない
  • ONLY を使ってパーティションテーブルのみに制約を追加する、または削除するのは、パーティションが1つも存在しない場合のみ行える
  • パーティションは親に存在しない列を持つことができない。また、パーティションの作成後に ALTER TABLE でパーティションに列を追加することもできない
  • 親テーブルの列に存在する NOT NULL制約 をパーティションの列から削除することはできない

パーティショニングの種類

パーティショニングには3種類の方法があります。

1つ目は範囲パーティショニングで、これはキー列または、キー列の集合で定義される範囲ごとにパーティションを分ける方法です。例としては、 created_at2022/06/012022/06/30 の範囲で分割します。

2つ目はリストパーティションで、各パーティションに現れるキーを明示的に列挙することで、分割します。

3つ目はハッシュパーティションで、パーティションキーの値に対してハッシュ値を作成し、そのハッシュ値を分割数で割った余りで分割します。

なお、ハッシュパーティションはPostgreSQL 11以降で利用可能です。

パーティションテーブルを定義する

それでは、実際にパーティションテーブルを定義してみます。とある販売システムでいつ、何の商品を、何個販売したかを記録するitem_salesテーブルを作る場合例に見ていきます。

このテーブルを月ごとのパーティションに分けたいとすると、まず、 created_at をパーティションキーとして範囲パーティションのパーティションテーブルを作成します。

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

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

最後に、 postgresql.confconstraint exclusionが無効になっていないことを確認します。これが無効になっていると、問い合わせが最適化されません。

ちなみに、constraint exclusionのデフォルト値は partition です。

古いデータを削除する

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

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

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

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

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

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

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

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

最後に region にインデックスを張っておきます。

さいごに

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

おすすめ書籍

PostgreSQLではじめるDB入門: DB基礎講座&PostgreSQL環境構築編 SQL実践入門 ──高速でわかりやすいクエリの書き方 WEB+DB PRESS plus 達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ

blog-page_footer_336




blog-page_footer_336




-BackEnd
-

執筆者:


comment

メールアドレスが公開されることはありません。

CAPTCHA


関連記事

rails

RailsでS3に画像をアップロードする

1 はじめに2 今回やりたいこと3 下準備3.1 ImageMagickをインストールする3.2 Gemをインストールする4 レコード生成時にファイルを指定してアップロードする4.1 アップローダを作 ...

Rubyを使ってDialogflowのお勉強

1 はじめに1.1 環境2 とりあえずAPIで叩いてみる2.1 テスト用のIntentを作成2.2 Gemの設定3 Eventについて3.1 WELCOMEイベント3.2 パラメータ付きで叩く3.3 ...

rails

Railsのバリデーション

1 はじめに2 基本的なバリデーション3 EachValidatorクラス4 Validatorクラス5 autoload_pathsの編集6 さいごに はじめに 今回はRailsのActiveRec ...

markdownで書けるドキュメントツールのGitbookを試す

1 はじめに2 Gitbookとは3 nvm4 node.jsインストール5 Gitbook導入5.1 インストール5.2 初期化5.3 ローカルでブラウザから確認6 作成と編集6.1 見出し編集7 ...

laravel logo

DeployerでLaravelをデプロイ! 初期設定〜レシピのカスタマイズまで

1 はじめに2 Deployerの導入2.1 前提条件2.2 インストール3 デプロイの設定3.1 デプロイスクリプト3.2 サーバサイドの設定3.3 デプロイコマンド3.4 Deployerのディレ ...

フォロー

blog-page_side_responsive

2022年6月
 1234
567891011
12131415161718
19202122232425
2627282930  

アプリ情報

私たちは無料アプリもリリースしています、ぜひご覧ください。 下記のアイコンから無料でダウンロードできます。