Cosmos DB for PostgreSQLに格納されたデータを分析に回す話

ひと口に分析といっても色々なパターンがあると思うのだけど、格納されているデータ全体を分析したいという話ではなく、格納されているデータのうち、一定の条件を満たすものだけを対象にしたい場合にどうするのが良いか、という話。

分析を実行する際にSELECTして抽出というのは、あまりにもコストがかかり過ぎる（遅い）。分析対象のデータは事前に抽出されているのが望ましいわけで、じゃあどうすれば良いか、という話です。

時系列データで時間範囲が条件となる場合
時間を含む諸条件となる場合
単に差分だけ取得しておく場合
まとめ
1. 関連

時系列データで時間範囲が条件となる場合

例えば「先週」「先月」のように範囲が指定できる場合であれば、create_time_partitions()でパーティションを作成しておけば、分析対象はパーティションにすれば良い。これはPostgreSQLのレンジパーティションの管理をより簡素化するCosmos DB for PostgreSQL固有の機能。

-- create a year's worth of monthly partitions
-- in table foo, starting from the current time

SELECT create_time_partitions(
  table_name         := 'foo',
  partition_interval := '1 month',
  end_at             := now() + '12 months'
);

Distributed Table（分散テーブル）になっているので、読み出し時のWHERE句にシャードキーを含むことになる。

時間を含む諸条件となる場合

例えば「商品型番が特定の値X」であったり、それらが複合している場合であれば、pg_cronを用いて定期的に抽出して別のテーブルに格納しておく、Aggregation（事前集計）の手法が使える。つまり、CREATE FUNCTIONでpl/pgsqlを用いてUDF（ユーザ定義関数）を作成しておき、pg_cronでこの関数を呼び出せば良い。

CREATE OR REPLACE FUNCTION filtering_data() RETURNS void AS $$
BEGIN
INSERT INTO filtered_table (...)
SELECT columns FROM original_table
WHERE SKU='value'
...
$$ LANGUAGE plpgsql;

SELECT cron.schedule('* * * * *','SELECT filtering_data();');

ちなみに最新のCosmos DB for PostgreSQL (PostgreSQL 16 + Citus 12.1)のpg_cronは数秒間隔で実行することも可能になっている。

SELECT cron.schedule('cronjob-1', '5 seconds', 'call filtering_data()');

単に差分だけ取得しておく場合

あまり良い方法とは思えないが、単に差分だけ別のCosmos DB for PostgreSQLに「転送」したいなら、論理レプリケーションが利用できる。あるいは、NOTIFYを用いて何かしらのサービスを呼び出してフィルタリングしてから別のCosmos DB for PostgreSQLに格納するという手法も考えられる。