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でAjax処理で画面を更新する

1 はじめに2 View(遷移元)の設定3 Controllerの実装4 View(遷移先)の実装5 参考6 さいごに はじめに RailsでAjax処理で画面を更新する方法を簡単に紹介します。 Vi ...

麻雀で自分が何を切るか学習させる

1 はじめに2 プロジェクト名を決めてみる3 何切る問題で考慮される要素3.1 手牌3.2 ドラ3.3 考慮していない要素(本当は入れたい)4 学習の方法5 さいごに はじめに 前回執筆しました、配牌 ...

laravel logo

Laravelで画像アップロード実装が楽になるかもしれないlaravel-imageup

1 はじめに2 環境3 導入4 実装5 個人的にハマったこと5.1 配列はダメ5.2 PHPStanで引っかかる6 さいごに7 おすすめ書籍 はじめに こんにちは。webアプリケーションを作る際にファ ...

laravel logo

Laravelの開発環境構築

1 はじめに2 開発環境構築2.1 Homestead2.2 Laradock3 Laradockで開発環境構築3.1 Laradockのダウンロード3.2 コンテナの設定ファイルを作成3.3 コンテ ...

aws

CodeWhispererを使ってみた

1 はじめに2 CodeWhispererとは3 導入4 使い方5 データの取り扱い6 さいごに7 おすすめ書籍 はじめに AWSからコード生成サービスのCodeWhispererが一般公開されたので ...

フォロー

blog-page_side_responsive

2022年6月
 1234
567891011
12131415161718
19202122232425
2627282930  

アプリ情報

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