how to compile and install clickhousedb_fdw

clickhousedb_fdw is another PostgreSQL’s Foreign Data Wrapper For ClickHouse, bases on unixodbc.

The clickhousedb_fdw is open-source. It is a Foreign Data Wrapper (FDW) for one of the fastest column store databases, Clickhouse. This FDW allows you to SELECT from, and INSERT into, a ClickHouse database from within a PostgreSQL v11 server.

The FDW supports advanced features like aggregate pushdown and joins pushdown. These significantly improve performance by utilizing the remote server’s resources for these resource intensive operations.

To simplify the job, I use PostgreSQL 11 Docker, it bases on Debian 9.8.

Installing Prerequisites

Dowload and compile clickhouse-odbc

Clone clickhousedb_fdw source code and build

If you meet the error, means you need install unixodbc-dev first,

another issue,

It is bug of Makefile, you need run Makefile under lib directory first,

Install clickhouse

In order to test the environment, I install both clickhouse-server and clickhouse-client too.

clickhouse side

prepare data(create table and import data)

btw, I suggest download csv first,

PostgreSQL side

Now the data is ready in the ClickHouse, the next step is to set up the PostgreSQL side.

Install clickhousedb_fdw extension first,

Then create a ClickHouse foreign server, user mapping, foreign tables and test it.

Aggregate Pushdown.

Aggregate pushdown is a new feature of PostgreSQL FDW. There are currently very few foreign data wrappers that support aggregate pushdown: clickhousedb_fdw is one of them. Planner decides which aggregate to pushdown or not. Here is an example for both cases.

Join Pushdown

Join pushdown is also a very new feature of PostgreSQL FDW’s. The clickhousedb_fdw also supports join pushdown.

Some screen shot

Other test data


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.