PostgreSQLでWhere句の内容に応じて参照するテーブルを動的に変更したい

Question

背景

常時、データ(タイムスタンプのカラムがあるデータ)がインサートされるテーブルがあり、そのテーブルには直近1年以内のデータしか保持できない設計としています。
そのため1年以上前のデータはcsv化され別のストレージに保存される仕組みとしています(テーブル容量削減の意図)

しかし、運用上、1年以上前のデータを参照することがあります。
このとき、そのテーブルにアクセスした際に1年以上前のデータを参照する際にはcsvからデータを取得する運用としたいです、

環境

• PostgreSQL Ver

　　　　9.5

現状できていること

file_fdwを使って外部ストレージに保存されたcsvをテーブルとみなしてデータを取得することはできています。

やりたいこと

Where句でタイムスタンプの値が1年以上前を参照した際に、参照するテーブルを動的に変更したいです。
手段としては拡張機能を検討していますが、実現手法のイメージができていません。
全体構造は以下のような形を想定しています

1. 直近一年以内のデータを参照するときには、PostgreSQL内のテーブルからデータを参照したいです。

1. 一年以上前のデータを参照するときには、外部ストレージのcsvデータを参照したいです。

ただし、問い合わせるクエリ文(青四角部)のFrom句は、直近一年分のデータを参照するときと一年以上前を参照するときで変更できません
これは問い合わせ側の制約になります。

例えば実行日時を2020-11-04 00:00:00とした際に以下のクエリを受け取るとします。

Select *
From TABLE
Where ‘2019-11-04 00:03:00’ >=  _timestamp and  _timestamp >= '2019-11-04 00:00:00'

この場合、一年以上前のデータになるため、From句を変更して外部テーブルにアクセスする必要があります。
また、csvは毎分ごとに作成されるものになるため、上記のようなWhere句の場合以下の3つのcsvにアクセスする必要があります。

• アクセスする必要のあるテーブル名

TABLE_2020110403
TABLE_2020110402
TABLE_2020110401

現在想定している手法は、拡張機能でアクセスする必要のあるテーブル名のARRAYを作成し、以下の関数によって各テーブルの結果を結合することです。

DROP FUNCTION IF EXISTS csv(csv_names text[]);

CREATE OR REPLACE FUNCTION csv(csv_names text[])
RETURNS TABLE(
	_timestamp timestamp(3) without time zone,
	_data text
) AS $$
	DECLARE
		csv_name text;
	Begin
		FOREACH csv_name IN ARRAY csv_names LOOP
			RETURN QUERY EXECUTE format('Select * FROM %I', csv_name);
		END LOOP;
	END;
$$ LANGUAGE plpgsql;

行き詰っているところ

以下の2の手順における、1の手順で受けたクエリのWhere句に応じて、リストを作成する工程で行き詰っています。

１. クエリを受け取る

Select *
From TABLE
Where ‘2019-11-04 00:03:00’ >=  _timestamp and  _timestamp >= '2019-11-04 00:00:00'

２. 一年以上前のデータなので、上記のクエリは実行せずに、以下のリストを作成する

[TABLE_2020110403, TABLE_2020110402, TABLE_2020110401]

３. 2のリストを受け取って、関数(csv)に引数として与えて、処理を実行する

Select * 
FROM csv(ARRAY[‘TABLE_2020110403’, ‘TABLE_2020110402’, ‘TABLE_2020110401’]
Where ‘2019-11-04 00:03:00’ >=  _timestamp and  _timestamp >= '2019-11-04 00:00:00'

拡張機能であればこの処理が実現できるのかもしれませんが、具体的な手法が思いつきません。

なにかいい手法があればご教授いただけますと幸いです。

設計上の制約

• 受け取るクエリのFrom句は変更できない
• PostgreSQLのVerを変更することはできない

Answer 1

そのため1年以上前のデータはcsv化され別のストレージに保存される仕組みとしています(テーブル容量削減の意図)

この目的の為なら、テーブル・パーティショニングにするだけで目的は果たせると思います。
外部ストレージにテーブルスペースを定義しそこに古いデータを格納するようにすれば良いだけで、わざわざCSVに変換する必要もないので、file_fdwを使用する事もないです。

postgresのパーティショニングは複数の実体のテーブルを一つのテーブルに見せかけるものです。
パーティショニング : 用途と利点
5.10. パーティショニング

現状の構成でパーティショニングできれば、SQL上は一つのテーブルにアクセスするだけになります。

ここからはアイデアで可能かどうかは、分かりません。

パーティショニングは、定義だけの親テーブル構造を実体の子テーブルに継承する仕組みです。
この子テーブルに外部テーブルが指定できれば、外部テーブル混在型のパーティショニングになります。
但し、外部テーブルを含んだ検索になった場合の性能には期待できないと思いますが。

Comments

[naotel]

sazi様
ご回答ありがとうございます。

> この子テーブルに外部テーブルが指定できれば、外部テーブル混在型のパーティショニングになります
CREATE FOREIGN TABLEにINHERITSのオプションができようできたため、子テーブルに外部テーブルを指定したパーティショニングが実現できました。

> 但し、外部テーブルを含んだ検索になった場合の性能には期待できないと思いますが。
複数テーブルをまたぐ、且つ外部ストレージの検索処理になるため、ある程度のパフォーマンス低下は仕方ないと考えています。
ここは外部テーブルを含んだ検索でパフォーマンス評価を行い、要件を満たすかどうかで判断していきたいと思います。

Answer 2

普通にUnionしたらいいかと。
参考

あと、

以下のようにFrom句を変更したいです

と

実行するクエリのFrom句は変更できない

が矛盾してます。

Comments

[sazi]

リンク先にはunionではなくて、パーティションについてですけど？
パーティションのアイデアで回答しようとしたけど、この回答にコメントした方が良いのかな？

[naotel]

To:szk.様
回答ありがとうございます。
矛盾している箇所を修正しました。
修正前)実行するクエリのFrom句は変更できない
修正後)受け取るクエリのFrom句は変更できない

また、行き詰っているところを明確化しました。

[naotel]

To:sazi様
sazi様がご覧になったときと質問文を少し修正しております。
パーティションのアイデアがどういったものか教えていただけますでしょうか。

[szk.]

saziさん
別途回答いただいた方がいいかと思います、パーティションに関して回答したつもりはなかったのでm()m
どちらかというと、file_fdw使ってunionできるというところの参考でした。

naotelさん
詳細な記載ありがとうございます、unionで解決が難しいというのであれば、
saziさんからの回答を待っていただいた方がいいかと思います。