pg_clickhouseのご紹介: ClickHouseへのクエリを実行するPostgres拡張機能

この 1 年で、分析ワークロードを ClickHouse Cloud へ移行したユーザーの動向に顕著な傾向が見られました。セルフホストの ClickHouse に次いで、PostgreSQL が最も多い移行元となっていたのです。ClickPipes により、こうしたユースケースでのデータレプリケーションや移行は容易になりました。しかし、クエリやアプリケーションコードを PostgreSQL から ClickHouse へ移行する作業には、依然として大きな課題が残されていることがわかりました。そこで数か月前、私たちは分析クエリを PostgreSQL から ClickHouse へ移行する手間と所要時間を減らす方法の検討に着手しました。

PostgreSQL から直接 ClickHouse 上で分析クエリを透過的に実行できる Apache 2 ライセンスの PostgreSQL 拡張機能 (extension)、pg_clickhouse v0.1.0 のリリースをお知らせいたします。

pg_clickhouse は以下からダウンロードいただけます。

PGXN
GitHub

あるいは、Docker インスタンスを起動して手軽に試すことも可能です。

1docker run --name pg_clickhouse -e POSTGRES_PASSWORD=my_pass 
2       -d ghcr.io/clickhouse/pg_clickhouse:18

まずはチュートリアルをお試しいただくか、以下の動画で Sai によるチュートリアルの実演をご覧ください。

目標

ビジネスデータやトランザクション処理だけでなく、ロギングやメトリクスまで含めて、組織が Postgres をバックエンドにしたアプリケーションを構築する一般的なケースを考えてみましょう。プロダクトの成長に伴い、ユーザーのトラフィックとデータ量は急増します。その結果、顧客向けのリアルタイム機能やオブザーバビリティシステムを支える分析クエリの実行速度が低下し始めます。

開発者は多くの場合、PostgreSQL のリードレプリカを利用してこの問題を緩和しようとしますが、これはせいぜい一時しのぎの策にすぎません。最終的には、ClickHouse のような分析特化型データベースへとワークロードを移行することを目指すようになります。ClickPipes を使えば迅速なデータ移行が可能ですが、SQL ライブラリや ORM で生成された既存の PostgreSQL クエリをどう扱うかという問題が残ります。

手間がかかるのはデータの移動ではありません。そこは ClickPipes が完璧にこなします。真の課題は、ダッシュボードや ORM、cron ジョブに組み込まれた、数か月や数年分の分析 SQL を書き直す作業です。

データ移行に続き、それらのクエリの接続先を新しい Postgres データベースやスキーマに向けるだけでワークロード移行が完了し、クエリ自体の書き直しがそもそも不要になる PostgreSQL 拡張機能があればどうでしょうか。

そうした考えから、私たちは以下の目標を掲げて pg_clickhouse の開発に着手しました。

PostgreSQL からの ClickHouse クエリ実行を実現する
既存の PostgreSQL クエリを変更なしで実行できるようにする
クエリ実行を ClickHouse にプッシュダウンする
クエリおよびプッシュダウンの継続的な進化に向けた基盤を整える

ClickHouse のテーブルが、通常の PostgreSQL テーブルとまったく同じように見えたらどうでしょうか。既存の Postgres 分析テーブルとは別のスキーマに配置されつつ、同一の構造が提供されているとします。このパターンなら、search_path を変更するだけで、既存のクエリをそのまま動作させることができます。

これまでの経緯

SQL/MED はまさにこのユースケースに対応するものであり、外部データラッパー (Foreign Data Wrapper) と呼ばれるデータベース拡張機能を提供して、SQL 経由で外部データを管理できるようにしています。PostgreSQL では 2011 年のバージョン 9.3 から外部データラッパーをサポートしており、一般に「FDW」と呼ばれる拡張機能が長年にわたり豊富に開発されてきました。

既存のソリューションを探したところ、Percona の Ibrar Ahmed 氏による初期の成果をもとに、Adjust 向けに Ildus Kurbangaliev 氏が開発した clickhouse_fdw がすぐに見つかり、検証を行いました。これは生データへのアクセスだけでなく、一部の JOIN や集約関数を含むクエリのプッシュダウンにも対応していました。

このプロジェクトは 2019 年に、PostgreSQL FDW の標準的なリファレンス実装である postgres_fdw のフォークと、ClickHouse C++ ライブラリのフォークから始まりました。残念ながら 2020 年末以降は、新しいバージョンの PostgreSQL での動作を維持するパッチ当て程度の基本的なメンテナンスしか行われていませんでした。clickhouse_fdw は優れた足がかりでしたが、高度な集約、SEMI-JOIN、サブクエリのサポートなど、近年の PostgreSQL FDW API におけるプッシュダウンの改善点を取り込めていませんでした。また、テスト環境や Linux 以外のプラットフォームへの対応が不足しており、PostgreSQL の新しいメジャーリリースへの追従も遅れていました。Ildus 氏と協議の上、私たちはその機能の大部分を新しいプロジェクトである pg_clickhouse へ移植し、配布の一貫性を保つため Apache 2 ライセンスを維持することにしました。

今すぐ始める

自社のデータでClickHouseの性能を試してみませんか？ClickHouse Cloudなら数分で利用開始でき、300ドル分の無料クレジットも進呈されます。

サインアップ

改善点

clickhouse_fdw とその前身である postgres_fdw は今回の FDW の基盤となりましたが、私たちはコードとビルドプロセスの近代化、バグの修正や欠点の解消、そして分析クエリや集約処理をほぼ全面的にプッシュダウンできる完成された製品への作り込みに着手しました。

主な進展は以下の通りです。

PostgreSQL 拡張機能向けの標準 PGXS ビルドパイプラインの採用
プリペアド INSERT のサポート追加、およびサポート対象となる最新リリースの ClickHouse C++ ライブラリの採用
PostgreSQL バージョン 13〜18 および ClickHouse バージョン 22〜25 での動作を保証するテストケースと CI ワークフローの作成
ClickHouse Cloud で必須となる、バイナリプロトコルと HTTP API の両方における TLS ベース接続のサポート
Bool、Decimal、JSON のサポート
percentile_cont() のような順序集合集約を含む、透過的な集約関数のプッシュダウン
SEMI JOIN のプッシュダウン

特に最後の 2 つの機能は、分析用データベース向け外部データラッパーとしての実用性を大幅に向上させます。結局のところ最大の目的は、特化された高効率なエンジンで分析ワークロードを実行し、その実行速度の恩恵を受けることにあります。PostgreSQL 側で集約させるために何百万行ものデータをそのまま返すだけでは、あまり役に立ちません。

集約のプッシュダウン

順序集合集約は構文が直接変換できないため、エンジン間でマッピングするのが最も難しい部類の関数です。理想としては、高効率な実行のために集約関数が ClickHouse へ完全にプッシュダウンされることです。ClickHouse の HouseClick プロジェクトから引用した以下のクエリを見てみましょう。

1SELECT
2    type,
3    round(min(price)) + 100 AS min,
4    round(max(price)) AS max,
5    round(percentile_cont(0.5) WITHIN GROUP (ORDER BY price)) AS median,
6    round(percentile_cont(0.25) WITHIN GROUP (ORDER BY price)) AS "25th",
7    round(percentile_cont(0.75) WITHIN GROUP (ORDER BY price)) AS "75th"
8FROM
9    uk.uk_price_paid
10GROUP BY
11    type

このクエリでは 3 種類の集約関数を使用しています。min() と max() は ClickHouse と PostgreSQL で名前が同じであるため、自動的にプッシュダウンされます。しかし、全値のパーセンテージ内の最大値を計算または平均化する percentile_cont() はそうはいきません。ClickHouse にはそのような関数は存在せず、WITHIN GROUP (ORDER BY x) という順序集合集約の構文もサポートしていません。

ただし ClickHouse には、順序集合集約の構文の一部を実装した quantile をはじめとするパラメータ付き集約関数が用意されています。そこで pg_clickhouse は、このクエリを ClickHouse 向けに次のように書き換えます。

1SELECT
2    type,
3    (round(min(price)) + 100),
4    round(max(price)),
5    round(quantile(0.5)(price)),
6    round(quantile(0.25)(price)),
7    round(quantile(0.75)(price))
8FROM
9    uk.uk_price_paid
10GROUP BY
11    type

percentile_cont() の直接引数(0.5、0.25、0.75)が quantile() のパラメータ定数へ、そして ORDER BY の引数が関数の引数へと透過的に変換されている点に注目してください。

1percentile_cont(0.5) WITHIN GROUP (ORDER BY price) => quantile(0.5)(price)

さらに、pg_clickhouse は以前の clickhouse_fdw と同様に、PostgreSQL の集約の FILTER (WHERE) 式を ClickHouse の -If コンビネータに変換します。HouseClick の完全な PostgreSQL クエリは以下の通りです。

1SELECT
2    type,
3    round(min(price)) + 100 AS min,
4    round(min(price) FILTER (WHERE town='ILMINSTER' AND district='SOUTH SOMERSET' AND postcode1='TA19')) AS min_filtered,
5    round(max(price)) AS max,
6    round(max(price) FILTER (WHERE town='ILMINSTER' AND district='SOUTH SOMERSET' AND postcode1='TA19')) AS max_filtered,
7    round(percentile_cont(0.5) WITHIN GROUP (ORDER BY price)) AS median,
8    round(percentile_cont(0.5) WITHIN GROUP (ORDER BY price) FILTER (WHERE town='ILMINSTER' AND district='SOUTH SOMERSET' AND postcode1='TA19')) AS median_filtered,
9    round(percentile_cont(0.25) WITHIN GROUP (ORDER BY price)) AS "25th",
10    round(percentile_cont(0.25) WITHIN GROUP (ORDER BY price) FILTER (WHERE town='ILMINSTER' AND district='SOUTH SOMERSET' AND postcode1='TA19')) AS "25th_filtered",
11    round(percentile_cont(0.75) WITHIN GROUP (ORDER BY price)) AS "75th",
12    round(percentile_cont(0.75) WITHIN GROUP (ORDER BY price) FILTER (WHERE town='ILMINSTER' AND district='SOUTH SOMERSET' AND postcode1='TA19')) AS "75th_filtered"
13FROM
14    uk.uk_price_paid
15GROUP BY
16    type

EXPLAIN を付けて実行し、クエリプランを確認してみます。

1QUERY PLAN                     
2---------------------------------------------------
3 Foreign Scan  (cost=1.00..-0.90 rows=1 width=112)
4   Relations: Aggregate on (uk_price_paid)

完全にプッシュダウンされています。この書き換えによって PostgreSQL への数百万行ものデータ転送が回避され、負荷の高い処理は ClickHouse 内部で完結します。EXPLAIN (VERBOSE) を使用すると、ClickHouse に送信されたクエリも出力されます(ここではフォーマットを整えています)。

1SELECT
2    type,
3    (round(min(price)) + 100),
4    round(minIf(price,((((town = 'ILMINSTER') AND (district = 'SOUTH SOMERSET') AND (postcode1 = 'TA19'))) > 0))),
5    round(max(price)),
6    round(maxIf(price,((((town = 'ILMINSTER') AND (district = 'SOUTH SOMERSET') AND (postcode1 = 'TA19'))) > 0))),
7    round(quantile(0.5)(price)),
8    round(quantileIf(0.5)(price,((((town = 'ILMINSTER') AND (district = 'SOUTH SOMERSET') AND (postcode1 = 'TA19'))) > 0))),
9    round(quantile(0.25)(price)),
10    round(quantileIf(0.25)(price,((((town = 'ILMINSTER') AND (district = 'SOUTH SOMERSET') AND (postcode1 = 'TA19'))) > 0))),
11    round(quantile(0.75)(price)),
12    round(quantileIf(0.75)(price,((((town = 'ILMINSTER') AND (district = 'SOUTH SOMERSET') AND (postcode1 = 'TA19'))) > 0))) 
13FROM
14    uk.uk_price_paid
15GROUP BY
16    type
17;

各 FILTER (WHERE) 式が、同等のフィルタリングを計算する -If 接尾辞付きの ClickHouse 関数に変換されていることがわかります。つまり、以下の式は、

1min(price) FILTER (WHERE town='ILMINSTER' AND district='SOUTH SOMERSET' AND postcode1='TA19')

次のように変換されます。

1minIf(price,((((town = 'ILMINSTER') AND (district = 'SOUTH SOMERSET') AND (postcode1 = 'TA19'))) > 0))

SEMI JOIN のプッシュダウン

pg_clickhouse の基本機能が固まった段階で、私たちはスケールファクタ 1 で ClickHouse にロードした歴史ある意思決定支援ベンチマーク TPC-H を対象にプッシュダウンのテストを開始しました。当初、Decimal 型のサポートを追加した時点で全 22 クエリ中 10 クエリが高速に実行されましたが、pg_clickhouse の外部テーブルから ClickHouse のソースへと完全にプッシュダウンされたのはわずか 3 クエリにとどまりました。その 1 つがクエリ 3 の結合です。

1EXPLAIN (ANALYZE, COSTS)
2-- using default substitutions
3select
4    l_orderkey,
5    sum(l_extendedprice * (1 - l_discount)) as revenue,
6    o_orderdate,
7    o_shippriority
8from
9    customer,
10    orders,
11    lineitem
12where
13    c_mktsegment = 'BUILDING'
14    and c_custkey = o_custkey
15    and l_orderkey = o_orderkey
16    and o_orderdate < date '1995-03-15'
17    and l_shipdate > date '1995-03-15'
18group by
19    l_orderkey,
20    o_orderdate,
21    o_shippriority
22order by
23    revenue desc,
24    o_orderdate
25LIMIT 10;
26                                            QUERY PLAN                                             
27---------------------------------------------------------------------------------------------------
28 Foreign Scan  (cost=0.00..-10.00 rows=1 width=44) (actual time=60.146..60.162 rows=10.00 loops=1)
29   Relations: Aggregate on (((customer) INNER JOIN (orders)) INNER JOIN (lineitem))
30   FDW Time: 0.106 ms
31 Planning:
32   Buffers: shared hit=230
33 Planning Time: 6.973 ms
34 Execution Time: 61.567 ms
35(7 rows)

しかし失敗した残りのクエリの多くは、サブクエリへの JOIN や WHERE 句内の EXISTS サブクエリを使用していました。クエリ 4 は後者の好例です(時間がかかりすぎるため ANALYZE は無効化しています)。

1EXPLAIN (COSTS, VERBOSE, BUFFERS)
2-- using default substitutions
3select
4    o_orderpriority,
5    count(*) as order_count
6from
7    orders
8where
9    o_orderdate >= date '1993-07-01'and o_orderdate < date(date '1993-07-01' + interval '3month')
10    and exists (select * from lineitem where l_orderkey = o_orderkey and l_commitdate < l_receiptdate)
11group by
12    o_orderpriority
13order by
14    o_orderpriority;
15                                                                                                                                                                                          QUERY PLAN                                                                                                                                                                                          
16----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
17 Sort  (cost=-80.86..-80.36 rows=200 width=40)
18   Output: orders.o_orderpriority, (count(*))
19   Sort Key: orders.o_orderpriority
20   ->  HashAggregate  (cost=-90.50..-88.50 rows=200 width=40)
21         Output: orders.o_orderpriority, count(*)
22         Group Key: orders.o_orderpriority
23         ->  Nested Loop  (cost=3.50..-93.00 rows=500 width=32)
24               Output: orders.o_orderpriority
25               Join Filter: (orders.o_orderkey = lineitem.l_orderkey)
26               ->  HashAggregate  (cost=2.50..4.50 rows=200 width=4)
27                     Output: lineitem.l_orderkey
28                     Group Key: lineitem.l_orderkey
29                     ->  Foreign Scan on tpch.lineitem  (cost=0.00..0.00 rows=0 width=4)
30                           Output: lineitem.l_orderkey, lineitem.l_partkey, lineitem.l_suppkey, lineitem.l_linenumber, lineitem.l_quantity, lineitem.l_extendedprice, lineitem.l_discount, lineitem.l_tax, lineitem.l_returnflag, lineitem.l_linestatus, lineitem.l_shipdate, lineitem.l_commitdate, lineitem.l_receiptdate, lineitem.l_shipinstruct, lineitem.l_shipmode, lineitem.l_comment
31                           Remote SQL: SELECT l_orderkey FROM tpch.lineitem WHERE ((l_commitdate < l_receiptdate))
32               ->  Foreign Scan on tpch.orders  (cost=1.00..-0.50 rows=1 width=36)
33                     Output: orders.o_orderkey, orders.o_custkey, orders.o_orderstatus, orders.o_totalprice, orders.o_orderdate, orders.o_orderpriority, orders.o_clerk, orders.o_shippriority, orders.o_comment
34                     Remote SQL: SELECT o_orderkey, o_orderpriority FROM tpch.orders WHERE ((o_orderdate >= '1993-07-01')) AND ((o_orderdate < '1993-10-01')) ORDER BY o_orderpriority ASC NULLS LAST
35 Planning:
36   Buffers: shared hit=236
37(20 rows)

このような単純なクエリに対して、実行プランの奥深くで 2 回も外部スキャンが発生していては決して効率的とは言えません。

私たちは以下の 2 つの方法でこれらのケースへの対処を始めました。

分析ユースケースに適するよう、コスト設定を調整して PostgreSQL プランナーがクエリをプッシュダウンしやすくする
さらに重要な点として、SEMI JOIN のプッシュダウンサポートを追加する

これらの変更により、22 クエリ中 21 クエリが効率的に(1 秒未満で)実行されるようになり、クエリ 4 を含む 12 クエリで完全なプッシュダウンが達成されました。

1EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
2-- using default substitutions
3select
4    o_orderpriority,
5    count(*) as order_count
6from
7    orders
8where
9    o_orderdate >= date '1993-07-01'and o_orderdate < date(date '1993-07-01' + interval '3month')
10    and exists (select * from lineitem where l_orderkey = o_orderkey and l_commitdate < l_receiptdate)
11group by
12    o_orderpriority
13order by
14    o_orderpriority;
15                                                                                                                                                             QUERY PLAN                                                                                                                                                              
16-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
17 Foreign Scan  (cost=1.00..5.10 rows=1000 width=40) (actual time=51.835..51.847 rows=5.00 loops=1)
18   Output: orders.o_orderpriority, (count(*))
19   Relations: Aggregate on ((orders) LEFT SEMI JOIN (lineitem))
20   Remote SQL: SELECT r1.o_orderpriority, count(*) FROM  tpch.orders r1 LEFT SEMI JOIN tpch.lineitem r3 ON (((r3.l_commitdate < r3.l_receiptdate)) AND ((r1.o_orderkey = r3.l_orderkey))) WHERE ((r1.o_orderdate >= '1993-07-01')) AND ((r1.o_orderdate < '1993-10-01')) GROUP BY r1.o_orderpriority ORDER BY r1.o_orderpriority ASC
21   FDW Time: 0.056 ms
22 Planning:
23   Buffers: shared hit=242
24 Planning Time: 6.583 ms
25 Execution Time: 54.937 ms
26(9 rows)

以下の表は、通常の PostgreSQL テーブル、SEMI-JOIN サポート導入前の pg_clickhouse、そして本リリースで提供される SEMI-JOIN サポート適用後の pg_clickhouse におけるクエリ性能を比較したものです。テストはスケールファクタ 1 の TPC-H データをロードした PostgreSQL テーブルと ClickHouse テーブルに対して実行しました。✅ は完全なプッシュダウンを示し、ダッシュ(-)は 1 分後のクエリタイムアウトを示します。

クエリ	Postgres 実行時間	変更前実行時間	SEMI JOIN 実行時間
1	4478ms	✅ 82ms	✅ 73ms
2	560ms	-	-
3	1454ms	✅ 74ms	✅ 74ms
4	650ms	-	✅ 67ms
5	452ms	-	✅ 104ms
6	740ms	✅ 33ms	✅ 42ms
7	633ms	-	✅ 83ms
8	320ms	-	✅ 114ms
9	3028ms	-	✅ 136ms
10	6ms	10ms	✅ 10ms
11	213ms	-	✅ 78ms
12	1101ms	99ms	✅ 37ms
13	967ms	1028ms	1242ms
14	193ms	168ms	✅ 51ms
15	1095ms	101ms	522 ms
16	492ms	1387ms	1639ms
17	1802ms	-	9ms
18	6185ms	-	10ms
19	64ms	75m	65ms
20	473ms	-	4595ms
21	1334ms	-	1702ms
22	257ms	-	268ms

SEMI-JOIN サポートを備えた pg_clickhouse 外部テーブルに対するほぼすべてのクエリで、全体的なパフォーマンス向上が見られる点に注目してください。クエリ 13、15、16 のような一部のケースではクエリオプティマイザがより低速なプランを選択しており、クエリ 2 のパフォーマンスについては明らかに原因を究明する必要があります。しかし、他のクエリにおける全体的なパフォーマンス向上は紛れもない事実です。

今後の展望

私たちはこれらの改善に大きな手応えを感じており、この初期リリースをお届けできることを嬉しく思っています。しかし、開発はまだ道半ばです。DML 機能を追加する前に、まずは分析ワークロードに対するプッシュダウンのカバレッジを完成させることに最も注力しています。今後のロードマップは以下の通りです。

まだプッシュダウンされていない残りの 10 個の TPC-H クエリを最適にプランニングできるようにする
ClickBench クエリのプッシュダウンのテストと修正
すべての PostgreSQL 集約関数の透過的なプッシュダウンのサポート
すべての PostgreSQL 関数の透過的なプッシュダウンのサポート
包括的なサブクエリプッシュダウンの実装
CREATE SERVER および CREATE USER を介したサーバーレベルおよびユーザーレベルでの ClickHouse 設定の許可
すべての ClickHouse データ型のサポート
軽量な DELETE および UPDATE のサポート
COPY によるバッチ挿入のサポート
任意の ClickHouse クエリを実行し、その結果をテーブルとして返す関数の追加
すべてのリモートデータベースを照会する場合の UNION クエリのプッシュダウンサポート追加

他にもやるべきことが山ほどあります。GitHub および PGXN のリリースから pg_clickhouse をインストールし、実際のワークロードでぜひ試してみてください。プッシュダウンが機能しない箇所があれば、プロジェクトの Issue からお知らせください。迅速に対応します。

新しい拡張機能を試してみませんか？

プロジェクトのGitHubリポジトリにあるpg_clickhouseのチュートリアルをお試しください。

チュートリアルを試す

pg_clickhouseのご紹介: ClickHouseへのクエリを実行するPostgres拡張機能

目標

これまでの経緯

今すぐ始める

改善点

集約のプッシュダウン

SEMI JOIN のプッシュダウン

今後の展望

新しい拡張機能を試してみませんか？

Subscribe to our newsletter

最近の記事

ClickHouse が dbt プラットフォームで利用可能になりました

Replica-aware routing のパブリックベータ

ClickHouse の新しい TimeSeries エンジンのご紹介: Prometheus のドロップイン代替

ClickHouse の AI Functions: SQL を AI 時代へアップグレード

Follow us

pg_clickhouseのご紹介: ClickHouseへのクエリを実行するPostgres拡張機能

目標 #

これまでの経緯 #

今すぐ始める

改善点 #

集約のプッシュダウン #

SEMI JOIN のプッシュダウン #

今後の展望 #

新しい拡張機能を試してみませんか？

Subscribe to our newsletter

最近の記事

ClickHouse が dbt プラットフォームで利用可能になりました

Replica-aware routing のパブリックベータ

ClickHouse の新しい TimeSeries エンジンのご紹介: Prometheus のドロップイン代替

ClickHouse の AI Functions: SQL を AI 時代へアップグレード

Follow us

目標

これまでの経緯

改善点

集約のプッシュダウン

SEMI JOIN のプッシュダウン

今後の展望