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


関連記事

laravel logo

Laravelのブラウザテスト「Dusk」をシンプルなCRUDで始めてみよう

1 はじめに2 Laravel Duskのセットアップ3 新規登録のテスト3.1 テスト対象となるコード3.2 Duskのテストコード3.3 テストの実行4 更新のテスト4.1 テスト対象となるコード ...

js

GoogleAppsScriptを使ってmBaaSの定期実行処理を実装する

1 はじめに1.1 簡単な状況説明1.2 定期実行を行う方法2 実装2.1 実装の流れ2.2 JavaScriptの実装2.3 スクリプトをアップロードする2.4 Google Apps Script ...

laravel logo

LaravelのArtisanコマンドを自作する

1 はじめに2 コマンドの作成方法3 コマンドの実装3.1 文字列出力3.2 引数3.3 オプション3.4 入力を使用した対話型3.4.1 ask()メソッド3.4.2 secret()メソッド3.4 ...

rails

Rails5で出力される「alias_method_chain is deprecated.」について

1 はじめに2 alias_method_chainとは3 Module#prependとは4 とりあえず、DEPRECATION WARNINGの出力をなくしたい5 さいごに はじめに 直近のプロジ ...

Go言語

GoのWeb Application Framework

1 はじめに2 代表的なGoのWAF2.1 軽量なWAF2.2 フルスタックなWAF3 Ginを使ってみる3.1 クエリパラメータ+ポストパラメータ3.2 ファイルアップロード3.3 URLのグループ ...

フォロー

blog-page_side_responsive

2022年6月
 1234
567891011
12131415161718
19202122232425
2627282930  

アプリ情報

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