How to connect ClickHouse Open-Source from PostgreSQL

Galaxy Glossary

How do I query ClickHouse data directly from PostgreSQL?

Use the clickhouse_fdw extension to query ClickHouse data inside PostgreSQL as if it were local tables.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why query ClickHouse from PostgreSQL?

Unifying analytics in one place avoids ETL overhead. With clickhouse_fdw you can join ClickHouse’s lightning-fast aggregates with transactional data that already lives in PostgreSQL.

What do I need before starting?

Install ClickHouse server, grant a user, and compile the clickhouse_fdw extension on the PostgreSQL host that will run the queries.

How do I install clickhouse_fdw?

Run git clone https://github.com/Percona-Lab/clickhouse_fdw, then make && make install.Restart PostgreSQL so the new foreign-data wrapper is discoverable.

How do I create the foreign server?

Inside psql, load the extension, create the foreign server with host, port, and database, then map a PostgreSQL role to the ClickHouse login.

Can I import ClickHouse tables automatically?

Yes—IMPORT FOREIGN SCHEMA pulls every table or a subset into Postgres, creating foreign tables you can query instantly.

How do I join ClickHouse and local ecommerce tables?

Once foreign tables exist you can use standard SQL joins, filters, and aggregates.PostgreSQL pushes compatible operations down to ClickHouse for speed.

Best practices for production use

Limit imported columns to only those you need, keep statistics updated with ANALYZE, and monitor network latency because large result sets move over the wire.

Why How to connect ClickHouse Open-Source from PostgreSQL is important

How to connect ClickHouse Open-Source from PostgreSQL Example Usage


-- Join ClickHouse sales aggregates with Postgres Orders for extra detail
SELECT o.id,
       o.order_date,
       ss.gross_sales
FROM   orders o
JOIN   sales_summary ss  -- foreign table
       ON ss.order_id = o.id
WHERE  o.customer_id = 42
ORDER  BY o.order_date DESC;

How to connect ClickHouse Open-Source from PostgreSQL Syntax


-- 1. Enable the wrapper
CREATE EXTENSION IF NOT EXISTS clickhouse_fdw;

-- 2. Register the ClickHouse server
authentication
CREATE SERVER click_srv
  FOREIGN DATA WRAPPER clickhouse_fdw
  OPTIONS (host 'ch.prod.local', port '9000', dbname 'ecommerce');

-- 3. Map PostgreSQL role to ClickHouse credentials
CREATE USER MAPPING FOR app_user
  SERVER click_srv
  OPTIONS (user 'ch_reader', password 'secret');

-- 4. Import only SalesSummary table from ClickHouse
IMPORT FOREIGN SCHEMA public LIMIT TO (sales_summary)
  FROM SERVER click_srv INTO public;

-- 5. Optional manual table definition example
CREATE FOREIGN TABLE ch_daily_traffic (
    day date,
    visits bigint
) SERVER click_srv
  OPTIONS (table_name 'daily_traffic');

Common Mistakes

Forgetting to run ANALYZE on imported foreign tables. Without statistics, the planner may choose sub-optimal join orders and create slow queries. Run ANALYZE after IMPORT and on a schedule.
Importing every ClickHouse table by default. This clutters the schema and slows <code>\d</code> lookups. Use LIMIT TO or EXCEPT in IMPORT FOREIGN SCHEMA to pull only the tables you need.