C#でNpgsqlを使ってPostgreSQLへアクセス【LISTパーティションのあるテーブル作成】

はじめに

データベースに慣れている方なら特に問題はないと思いますが、ついでにPostgreSQLのバージョン10から使えるようになった宣言的パーティションの手法も含め解説したいと思います。

宣言的パーティションとは1つのテーブルを条件に則った形で分割する手法です。

メリットとしてはSELECTの条件にパーティションテーブルの条件と一致した条件が含まれていれば余計なテーブルをアクセスする必要がなくなるので処理速度が高速化できます。

また年ごとのパーティションテーブルにしておいて古いデータを破棄したい場合はその年のテーブルを削除すれば該当するデータも削除され、私が色々な記事を見た限りでは日付指定で削除するより削除処理が高速化されるようです。

Npgsqlの本家情報は

私の作成するサンプルソースファイルは

私はよく下図のような構成のパーティションを作ります。

どの装置のデータかと日付を条件としたSELECTでデータを取得するので高速化の恩恵を受けていると思います。

尚、この記事を書いている時のバージョン(13.4)では少しだけ制約がありますが、今後バージョンアップで変わるかもしれません。

制約とはプライマリキーが存在している場合パーティションで参照する値にプライマリキーを含める必要があります。

バージョン10ではそもそもプライマリキーはNGだったのでその流れで今でもパーティションを利用する時はプライマリキーを使わない癖がついてます。

今回解説するテーブルの構成は以下となります。

テーブル名	型	概要
time	timestamp	トランザクション開始時刻または入力された日付
name	text	任意の文字列
numeric	integer	任意の数値

予め決められた値でパーティションテーブルを作成します。

値は数値でもいいですし文字列でもいいです。

基となるテーブル

なにはともあれ最初に基となるテーブルを作成します。

using NpgsqlConnection con = new("Server=127.0.0.1; Port=5432; User Id=test_user; Password=pass; Database=db_PostgreTest; SearchPath=public");
con.Open();
using NpgsqlCommand cmd = new(@"CREATE TABLE data(time timestamp DEFAULT clock_timestamp(), name text, numeric integer) PARTITION BY LIST (name)", con);
try
{
    _ = cmd.ExecuteNonQuery();
}
catch (PostgresException exc)
{
}

上記の「CREATE TABLE」の最後にある「PARTITION BY LIST (name)」がミソです。

クエリ文を説明すると「PARTITION BY LIST 」がリストパーティションを作成する命令となり「(name)」がリスト対象となるカラム名を指定しています。

前回説明した存在していればCREATE TABLEを実行しない「IF NOT EXISTS」も使えます。

パーティションテーブル

次に指定されたデータごとに振り分けるパーティションテーブルを作成します。

using NpgsqlConnection con = new("Server=127.0.0.1; Port=5432; User Id=test_user; Password=pass; Database=db_PostgreTest; SearchPath=public");
con.Open();
using NpgsqlCommand cmd = new(@"CREATE TABLE パーティションテーブル名 PARTITION OF data FOR VALUES IN ('a');", con);
_ = cmd.ExecuteNonQuery();
// IF NOT EXISTSがあればパーティションテーブルの重複生成も抑制できる
cmd.CommandText = @"CREATE TABLE IF NOT EXISTS パーティションテーブル名 PARTITION OF data FOR VALUES IN ('a');";
_ = cmd.ExecuteNonQuery();
// パーティションテーブルが存在しない値を入れるパーティションテーブル
cmd.CommandText = @$"CREATE TABLE default_name PARTITION OF data DEFAULT;";
_ = cmd.ExecuteNonQuery();

上記3行目がパティションテーブル作成のクエリ文です。

「"CREATE TABLE パーティションテーブル名」は普段使用するテーブル作成のクエリ文ですね。

「PARTITION OF data」はパーティション対象となる上位のテーブルを指します。

「FOR VALUES IN ('a')」は上位で「nama」がLISTパーティションの対象となっているかは明確化されていますので、この値が幾つかを指定します。

今回は「name」が「a」の時に3行目で作成されたパーティションテーブルに振り分けられるという事になります。

6行目は同じパーティシィンテーブル名で「 IF NOT EXISTS」を追加した文です。

3行目と6行目を連続で実行しても例外が発生しない事を確認できます。

ここまで説明してふと疑問に思われた方もいるかもしれませんが、「a」以外はどうなるの？かです。

入れるパーティションテーブルが存在しないと例外が発生します。

もしこのような懸念点があるなら9行目にある「それ以外のデータ」が入るパーティションテーブルを作成しましょう。

最後の「DEFAULT」がどのパーティションテーブルにも一致しないデータの入るパーティションテーブルになります。

今回の検証ではnameがaとbのデータをINSERTします。

nameがbのデータは「それ以外」のパーティションテーブルに入りますが、後からnameがbの時に入るパーティションテーブルを追加すると例外が発生するようです。

私のやり方がダメなのかもしれませんが、そもそもどのパーティションテーブルにも一致しないデータを作る事は避けた方が良いような気がします。

データをINSERETするとどのようになるか確認してみます。

cmd.CommandText = @"INSERT INTO data(name, numeric) VALUES ('a', 1);";
_ = cmd.ExecuteNonQuery();
cmd.CommandText = @"INSERT INTO data(name, numeric) VALUES ('b', 10);";
_ = cmd.ExecuteNonQuery();

「name」が「a」と「b」のデータです。

パーティションテーブルは「a」用と「それ以外」用のデフォルトパーティションです。

確認は簡単にできるpgAdmin4を使用します。

最上位のテーブル

最上位のテーブルで条件なしのSELECTをすればnameがaでもbでも取得できるのが分かります。

nameがaの条件で振り分けられるパーティションテーブル

今回はnameがaのときはテーブル名「numeric_a」に振り分けるよう指定してあり、このテーブルでSELECTするとnameがaのデータだけが入っているのが分かります。

nameがa以外の条件で振り分けられるパーティションテーブル

今回はnameがbのデータが該当しますが、このデータがテーブル名「default_name」に振り分けされているのがSELECTで分かります。

ちなみに

SELECT文で直接パーティションテーブルからデータを取得する事も可能ですがWHEREで条件設定(今回ならnameがaのデータ)やっても取得する時間の差は誤差範囲程度なので、自分が分かりやすい方法で問題ないかと思います。