Contents

• pg_clickhouse Postgres Extension
  • Getting Started
  • Documentation
  • Test Case: TPC-H
    • Compile From Source
    • Loading
  • Dependencies
  • Road Map
  • Authors
  • Copyright

pg_clickhouse Postgres Extension

This library contains the PostgreSQL extension pg_clickhouse, including a foreign data wrapper for ClickHouse databases. It supports 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 extension:

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, while a dash indicates a query cancellation after 1m. All tests run on a MacBook Pro M4 Max with 36 GB of memory.

| Query | Pushdown | pg_clickhouse | PostgreSQL | | —————–: | :——: | ————: | ———: | | Query 1 | ✅ | 73ms | 4478ms | | Query 2 | | - | 560ms | | Query 3 | ✅ | 74ms | 1454ms | | Query 4 | ✅ | 67ms | 650ms | | Query 5 | ✅ | 104ms | 452ms | | Query 6 | ✅ | 42ms | 740ms | | Query 7 | ✅ | 83ms | 633ms | | Query 8 | ✅ | 114ms | 320ms | | Query 9 | ✅ | 136ms | 3028ms | | Query 10 | ✅ | 10ms | 6ms | | Query 11 | ✅ | 78ms | 213ms | | Query 12 | ✅ | 37ms | 1101ms | | Query 13 | | 1242ms | 967ms | | Query 14 | ✅ | 51ms | 193ms | | Query 15 | | 522ms | 1095ms | | Query 16 | | 1797ms | 492ms | | Query 17 | | 9ms | 1802ms | | Query 18 | | 10ms | 6185ms | | Query 19 | | 532ms | 64ms | | Query 20 | | 4595ms | 473ms | | Query 21 | | 1702ms | 1334ms | | Query 22 | | 268ms | 257ms |

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 http.

Debian / Ubuntu / APT

See PostgreSQL Apt for details on pulling from the PostgreSQL Apt repository.

sudo apt install \
  postgresql-server-18 \
  libcurl4-openssl-dev \
  uuid-dev \
  libssl-dev \
  make \
  cmake \
  g++

RedHat / CentOS / Yum

sudo yum install \
  postgresql-server \
  libcurl-devel \
  libuuid-devel \
  openssl-libs \
  automake \
  cmake \
  gcc

See PostgreSQL Yum for details on pulling from the PostgreSQL Yum repository.

Compile and Install

To build and install the ClickHouse library and pg_clickhouse, run:

make
sudo make install

If your host has several PostgreSQL installations, you might need to specify the appropriate version of pg_config:

export PG_CONFIG=/usr/lib/postgresql/18/bin/pg_config
make
sudo make install

If curl-config is not in the path on you host, you can specify the path explicitly:

export CURL_CONFIG=/opt/homebrew/opt/curl/bin/curl-config
make
sudo make install

If you encounter an error such as:

"Makefile", line 8: Need an operator

You need to use GNU make, which may well be installed on your system as gmake:

gmake
gmake install
gmake installcheck

If you encounter an error such as:

make: pg_config: Command not found

Be sure that you have pg_config installed and in your path. If you used a package management system such as RPM to install PostgreSQL, be sure that the -devel package is also installed. If necessary tell the build process where to find it:

export PG_CONFIG=/path/to/pg_config
make
sudo make install

To install the extension in a custom prefix on PostgreSQL 18 or later, pass the prefix argument to install (but no other make targets):

sudo make install prefix=/usr/local/extras

Then ensure that the prefix is included in the following postgresql.conf parameters:

extension_control_path = '/usr/local/extras/postgresql/share:$system'
dynamic_library_path   = '/usr/local/extras/postgresql/lib:$libdir'

Testing

To run the test suite, once the extension has been installed, run

make installcheck

If you encounter an error such as:

ERROR:  must be owner of database regression

You need to run the test suite using a super user, such as the default “postgres” super user:

make installcheck PGUSER=postgres

Loading

Once pg_clickhouse is installed, you can add it to a database by connecting as a super user and running:

CREATE EXTENSION pg_clickhouse;

If you want to install pg_clickhouse and all of its supporting objects into a specific schema, use the SCHEMA clause to specify the schema, like so:

CREATE SCHEMA env;
CREATE EXTENSION pg_clickhouse SCHEMA env;

Dependencies

The pg_clickhouse extension requires PostgreSQL 13 or higher, libcurl, libuuid. Building the extension requires a C and C++ compiler, libSSL, GNU make, and CMake.

Road Map

Our top focus is finishing pushdown coverage for analytic workloads before adding DML features. Our road map:

• Get the remaining 10 un-pushed-down TPC-H queries optimally planned
• Test and fix pushdown for the ClickBench queries
• Support transparent pushdown of all PostgreSQL aggregate functions
• Support transparent pushdown of all PostgreSQL functions
• Allow server-level and session-level ClickHouse settings via CREATE SERVER and GUCs
• Support all ClickHouse data types
• Support lightweight DELETEs and UPDATEs
• Support batch insertion via COPY
• Add a function to execute an arbitrary ClickHouse query and return its results as a tables
• Add support for pushdown of UNION queries when they all query the remote
• database

Authors

• David E. Wheeler
• Ildus Kurbangaliev
• Ibrar Ahmed

Copyright

• Copyright (c) 2025, ClickHouse
• Portions Copyright (c) 2023-2025, Ildus Kurbangaliev
• Portions Copyright (c) 2019-2023, Adjust GmbH
• Portions Copyright (c) 2012-2019, PostgreSQL Global Development Group