データベース(以下、DB)で大量データを扱う際には、必ずと言ってよいほどパーティションニング(Partitioning)は検討すると思います。そこで、オープンソースのERP iDempiere/JPiereを題材として、PostgreSQL(ポストグレス/ポスグレ)でパーティショングを行う方法について調査及び研究し、その成果をまとめておきたいと思います。
ページ内目次
パーティショニング(Partitioning)とは
パーティショニングとは、PostgreSQLのドキュメント「PostgreSQL 13.1文書 第5章 データ定義 5.11. テーブルのパーティショニング」において、「論理的には一つの大きなテーブルであるものを、物理的により小さな部品に分割すること」 と説明されています。パーティショニングする事で、１つのテーブルをある特定の情報をもとに分割する事で、特定の条件下においてクエリのパフォーマンスの向上が期待できます。
他にもパーティショニングするメリットが、「PostgreSQL 13.1文書 第5章 データ定義 5.11. テーブルのパーティショニング」に記載されていますので参照して下さい。
PostgreSQLのパーティショニングの種類
PostgreSQLには、ver13において次の３つのパーティショニングの方法があります。
◆範囲パーティショニング
例えば、日付をキーにして範囲指定し年単位でテーブルを分割するなど、特定の情報で範囲指定してテーブルをパーティションに分ける方法です。
◆リストパーティション
ある特定のキー情報によりテーブルをパーティションに分ける方法です。
◆ハッシュパーティション
ハッシュ値でパーティション分けする方法だそうです・・・。
業務データにおいては日付をキー情報としてパーティション分けすることが多いと思われるため、このコンテンツでは範囲パーティショニングについて検証して行きます！
PostgreSQL(ポスグレ)のパーティショニングの歴史
PostgreSQLのパーティショングは、バージョン(以下、Ver)9以前と、Ver10以降ではその実現方法が大きく異なります。Ver10において、パーティションをデータ定義言語(Data Definition Language、以下、DDL)で簡単に作成できるようになりました。
このコンテンツでは、PosrgreSQLのVer10で導入された、パーティションをDDLで作成できる"宣言的パーティショニング"について検証して行きます。
PostgreSQL - Ver9以前
PostgreSQLのVer9以前は、トリガーなどを駆使してパーティションを設定しなければならず、難易度が少々高かったです・・・。
PostgreSQL - Ver10
SRA OSS, Inc. 日本支社が公開しているPostgreSQL 10 検証レポートにおいて、パーティショニングに関連する改善として下記のように紹介されています(P18～P26)。
◆宣言的パーティショニング
PostgreSQL10からパーティショニングの機能が正式にサポートされました。
PostgreSQL10では、レンジパーティショニングとリストパーティショニングの2種類がサポートされています。
PostgreSQL - Ver11
SRA OSS, Inc. 日本支社が公開しているPostgreSQL 11 検証レポートにおいて、パーティショニングに関連する改善として下記のように紹介されています(P8～P19)。
◆パーティション全体に対するインデックス作成
「PostgreSQL 10 までは、インデックスは個々のパーティションにしか付与できず、パーティションテーブル全体にプライマリキー制約やユニーク制約を設ける事ができませんでした。これらがPostgreSQL11から条件付きながら可能となります。その条件とは、インデックス対象にパーティションキーの列(すなわちパーティション分割の基準となる列)を含む事です。」と紹介されています。
◆パーティションテーブルに対する外部キー
◆パーティション間の透過的データ移動
◆ハッシュパーティショニング
◆問い合わせ実行時のパーティション除外処理
SQLを実行する段階で不要なパーティションを除外してクエリが実行できるようになった様子です。
◆パーティション指向の結合・集約
PostgreSQL - Ver12
SRA OSS, Inc. 日本支社が公開しているPostgreSQL 12 検証レポートにおいて、パーティショニングに関連する改善として下記のように紹介されています(P18～P23)。
◆多パーティションにCOPYデータ投入する際の性能改善
◆多パーティションに対する検索の性能改善
多数のパーティションを含むパーティションテーブルを検索して、実際にデータを取り出す対象のパーティションが少ない場合に、大幅に性能が改善しているとの事です。SRA OSS, Inc. 日本支社の検証事例では、PostgreSQL11と比較して、5分の１以下になっています。
◆外部キー制約対応
◆パーティション定義に式を使用
PostgreSQL - Ver13
SRA OSS, Inc. 日本支社が公開しているPostgreSQL 13 検証レポートにおいて、パーティショニングに関連する改善として下記のように紹介されています(P16～P26)。
◆ パーティションテーブルのロジカルレプリケーション
◆ パーティションテーブル同士の結合の改善
◆ 行単位BEFOREトリガ対応
テーブル分けするためのキー情報(以下、パーティションキー)は、アクティブ[IsActive]と伝票日付[DocDate]とします。
- アクティブ[IsActive]には、'Y' か 'N' のどちらかが入力され、入力したデータが有効かどうかのステータスとして使う事を想定しています。'Y'は有効、'N'は無効を意味します。
- 伝票日付[DocDate]は、データを入力した日付を想定して、1年間分のデータを1つのパーティションとなるようにします。
- パーティショニングの方法は、範囲パーティショニングを使用します。
パーティションテーブルを作成するDDL
パーティションテーブルを作成するDDLは基本的には通常のCREATE TALBE文ですが、下記の2点に違いがあります。
主キー(PRIMARY KEY)に、パーティションキーを含める
DDL分の最後に、PARTITION BY RANGE (パーティションキー)を追加する
コピー＆ペーストして試せるようにテキストのDDLも掲載しておきます。
CREATE TABLE IF NOT EXISTS adempiere.JP_Test
(
AD_Client_ID numeric(10,0) NOT NULL,
AD_Org_ID numeric(10,0) NOT NULL,
Amt numeric NOT NULL DEFAULT 0,
Created timestamp without time zone NOT NULL,
CreatedBy numeric(10,0) NOT NULL,
Description character varying(255) COLLATE pg_catalog."default" DEFAULT NULL::character varying,
Documentno character varying(30) COLLATE pg_catalog."default",
IsActive character(1) COLLATE pg_catalog."default" NOT NULL DEFAULT 'Y'::bpchar,
JP_Test_ID numeric(10,0) NOT NULL,
JP_Test_UU character varying(36) COLLATE pg_catalog."default" DEFAULT NULL::character varying,
PaymentRule character(1) COLLATE pg_catalog."default" NOT NULL DEFAULT 'P'::bpchar,
Updated timestamp without time zone NOT NULL,
UpdatedBy numeric(10,0) NOT NULL,
DateDoc timestamp without time zone NOT NULL,
CONSTRAINT jp_test_key PRIMARY KEY (JP_Test_ID,IsActive,DateDoc)
) PARTITION BY RANGE (IsActive,DateDoc)
パーティショニングが設定されると、pgAdmin4ではテーブルのアイコンに、Pのマークが付されます。
ユニーク制約にはパーティションキーを含める!!
パーティションを作成しているテーブルにユニーク制約を付ける場合には、パーティションキーも指定しないとエラーになります。
ユニーク制約にパーティションキーを含めずに作成しようとすると下記のようなエラーが表示されます。
org.postgresql.util.PSQLException: ERROR: unique constraint on partitioned table must include all partitioning columns 詳細: テーブル "jp_test" 上の UNIQUE制約にパーティションキーの一部である列 "isactive" が含まれていません。
パーティションを作成するDDL
CREATE TABLE JP_Test_Y_2023
PARTITION OF adempiere.JP_Test
FOR VALUES FROM ('Y','2023-01-01') TO ('Y','2024-01-01');
日付で範囲指定する場合、FROMに設定した日付は含まれて、TOに設定した日付は含まれません!!
上記のように 「From 2023-01-01 TO 2024-01-01」 と指定する事で、2023年の1年間分のデータをすべて含む事ができまｓ。2024年1月1日付のデータは含まれませんので安心して下さい。
作成したJP_Testテーブルは、アクティブ[IsActive]フラグ('Y' or 'N')と伝票日付[DateDoc]を年単位でパーティション分けする想定ですので、試しに2022年と2023年の2年分の下記パーティションを作成し確認しました。
- アクティブフラグ:’N’ - 2022年の日付
- アクティブフラグ:’N’ - 2023年の日付
- アクティブフラグ:’Y’ - 2022年の日付
- アクティブフラグ:’Y’ - 2023年の日付
まずは、それぞれのパーティションに1レコードずつ挿入されるようにデータを登録してみました。
そうすると、パ―ティンごとに1レコードずつ挿入されているのが確認できます。
おおもととなっているJP_Testテーブルを確認すると4レコードすべて確認する事ができます。
登録(Insert)だけでなく、更新(Update)、削除(Delete)ももちろん問題なく行えます。パーティションを跨ぐような更新を行っても大丈夫です！！
パーティションの範囲外のデータを登録する
作成したパーティションの範囲を外れたデータを登録しようとするとエラーになります。
データベースエラー: DBExecuteError:ERROR: 行に対応するパーティションがリレーション"jp_test"に見つかりません - 詳細: 失敗した行のパーティションキーは(isactive, datedoc) = (Y, 2019-04-02 00:00:00)を含みます。
レポート表示
一覧レポートも問題なく表示できます。
検索ウィンドウ表示
検索ウィンドウも問題なく表示できます
【注意】iDempiereでパーティショニングが問題なく動作する事を保証しているわけではありません！！
このコンテンツにおける確認で、iDempiereでパーティショニングを行うにあたり、特に問題になるような動作は確認していませんが、それはiDempiereでパーティショングが安全に行える事を保証するものではありません。パーティショニングを行うかどうかは自己責任で判断して下さい。
パーティショニングは、基本的にDDLの定義で行われ、DML(データ操作言語 / Data Manipulation Language)には影響はないので、ソフトウェア(iDempiere)のソースコードを修正する必要はないはずです。しかしながら、そのことをもとにiDempiereでパーティショングが安全に行える事が保証されるわけではありません。
iDempiereでパーティショングをより安全にそして効果的に行うためにも知っておいた方が良い事項がいくつかありますので、「iDempiereでパーティショニングを行うために知っておくべき事項」サポーターズコンテンツを参照して下さい。
iDempiereでパーティショニングを行うために知っておくべき事項
iDempiereでパーティショニングをより安全に、より効果的に行うために知っておいてもらいたい事項についてまとめています。