ClickHouse /pg_clickhouse
Interfaces to query ClickHouse databases from PostgreSQL
★ 299⑂ 23👁 1⑃ 369● 86▤ 17
More from this author
299
stars
23
forks
1
watchers
369
prs
86
issues
17
releases
README
pg_clickhouse Postgres Extension
================================
[![PGXN]][⚙️] [![Docker]][🐳] [![GitHub]][🐙] [![Postgres]][🐘] [![ClickHouse]][🏠]
This library contains pg_clickhouse, a PostgreSQL extension that runs
analytics queries on ClickHouse right from PostgreSQL without rewriting any
SQL. It supports PostgreSQL 14 and later and ClickHouse v23 and later.
Getting Started
The simplest way to try pg_clickhouse is the [Docker image][🐳], which
contains the standard PostgreSQL Docker image with the pg_clickhouse and [re2]
extensions:
sh
docker run --name pg_clickhouse -e POSTGRES_PASSWORD=my_pass \
-d ghcr.io/clickhouse/pg_clickhouse:18
docker exec -it pg_clickhouse psql -U postgres -c 'CREATE EXTENSION pg_clickhouse'
See the tutorial to get started importing ClickHouse tables
and pushing down queries.
Documentation
Reference
Tutorial
Test Case: TPC-H
This table compares [TPC-H] query performance between regular PostgreSQL
tables and pg_clickhouse connected to ClickHouse, both loaded at scaling
factor 1; ✔︎ indicates full pushdown as a single foreign scan and ✼ indicates
full pushdown as multiple foreign scans. All tests run on a MacBook Pro M4 Max
with 36 GB of memory.
| Query | PostgreSQL | pg_clickhouse | Pushdown |
| ----------:| ----------:| -------------:|:--------:|
| [Query 1] | 4483 ms | 59 ms | ✔ |
| [Query 2] | 588 ms | 24 ms | ✔ |
| [Query 3] | 786 ms | 62 ms | ✔ |
| [Query 4] | 550 ms | 38 ms | ✔ |
| [Query 5] | 721 ms | 1439 ms | ✔ |
| [Query 6] | 592 ms | 17 ms | ✔ |
| [Query 7] | 639 ms | 29 ms | ✔ |
| [Query 8] | 398 ms | 383 ms | ✔ |
| [Query 9] | 2842 ms | 162 ms | ✔ |
| [Query 10] | 860 ms | 125 ms | ✔ |
| [Query 11] | 276 ms | 21 ms | ✼ |
| [Query 12] | 963 ms | 26 ms | ✔ |
| [Query 13] | 1037 ms | 1354 ms | |
| [Query 14] | 675 ms | 30 ms | ✔ |
| [Query 15] | 2520 ms | 387 ms | |
| [Query 16] | 539 ms | 823 ms | |
| [Query 17] | 2107 ms | 37 ms | ✔ |
| [Query 18] | 5230 ms | 7228 ms | |
| [Query 19] | 68 ms | 47 ms | ✔ |
| [Query 20] | 473 ms | 28 ms | |
| [Query 21] | 1145 ms | 4470 ms | |
| [Query 22] | 270 ms | 45 ms | ✼ |
Installation
Compile From Source
General Unix
The PostgreSQL and curl development packages include pg_config and
curl-config in the path, so you should be able to just run make (or
gmake), then make install, then in your database
CREATE EXTENSION pg_clickhouse.
Debian / Ubuntu / APT
See [PostgreSQL Apt] for details on pulling from the PostgreSQL Apt repository.
sh
sudo apt install \
postgresql-server-19 \
libcurl4-openssl-dev \
uuid-dev \
Top issues
#276ORDER BY pushdown ignores sort-order semantics mismatch (text collation, UUID) — merge joins fail with "mergejoin input data is out of order", wrong-results risk3 comments · 2026-06-13#49Support All Data Types2 comments · 2025-11-19#409round(avg(int), n) pushed down rounds ties to even; Postgres rounds them away from zero1 comments · 2026-10-14#383Document how to correlate a foreign scan with `system.query_log`1 comments · 2026-09-27#294Add support for asynchronous fetching, the `async_capable` table option1 comments · 2026-06-24#248Support JSON/JSONB #> array and #>> array path operators1 comments · 2026-05-11#83Add Session Settings to `FOREIGN SERVER`s and `USER MAPPING`s1 comments · 2025-12-10#79Improve Subquery Joins1 comments · 2025-12-10
Latest release
Release v0.11.1 v0.11.1 2026-11-09This release makes binary-only changes. Once installed, any existing use of pg_clickhouse v0.11 will get its benefits on reload without needing to ALTER EXTENSION UPDATE. 🚨 Compatibility Changed the default driver from "http" to "binary". Use OPTIONS(driver 'http') to restore use of the HTTP driver if needed. Changed pg_clickhouse.session_settings to override built-in settings by key rather than replace them. It now defaults to an empty string, and pg_clickhouse always sends join_use_nulls 1, group_by_use_nulls 1, final 1, transform_null_in 0 unless overridden. Setting it to an e