How to Query ClickHouse Data in PostgreSQL

Galaxy Glossary

How do I query ClickHouse tables directly from PostgreSQL?

Access ClickHouse tables directly from PostgreSQL using the clickhouse_fdw foreign data wrapper.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why query ClickHouse from PostgreSQL?

Combine high-speed ClickHouse analytics with PostgreSQL joins, views, and constraints without moving data.

What prerequisites are needed?

Install clickhouse_fdw on the PostgreSQL server and create a ClickHouse user with read permissions.

How to create a foreign server?

Use CREATE SERVER to register ClickHouse connection details such as host, port, and authentication.

How to map ClickHouse tables?

Run IMPORT FOREIGN SCHEMA or individual CREATE FOREIGN TABLE statements to expose ClickHouse tables.

Can I join PostgreSQL and ClickHouse tables?

Yes.Once foreign tables exist, standard SQL joins work, allowing blended analytics across systems.

Best practice for data types?

Explicitly cast ClickHouse Decimal, DateTime64, and Nullable columns to matching PostgreSQL types to avoid runtime errors.

How to secure cross-database access?

Use role-based privileges in PostgreSQL (GRANT SELECT) and limit ClickHouse user scope to read-only.

How to monitor performance?

Enable EXPLAIN (VERBOSE) to verify pushdown.Large aggregations remain in ClickHouse, reducing network traffic.

When should I avoid the FDW?

Avoid write-heavy workloads or millisecond-latency OLTP queries; the FDW adds overhead over direct ClickHouse access.

Why How to Query ClickHouse Data in PostgreSQL is important

How to Query ClickHouse Data in PostgreSQL Example Usage


-- Top 5 customers by ClickHouse order spend
SELECT c.name, SUM(o.total_amount) AS lifetime_spend
FROM Customers_ch c      -- foreign table from ClickHouse
JOIN Orders_ch o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY lifetime_spend DESC
LIMIT 5;

How to Query ClickHouse Data in PostgreSQL Syntax


-- 1. Create extension
CREATE EXTENSION IF NOT EXISTS clickhouse_fdw;

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

-- 3. Create user mapping
CREATE USER MAPPING FOR app_user
  SERVER ch_srv
  OPTIONS (user 'ch_reader', password 'secret');

-- 4. Import ecommerce schema from ClickHouse
IMPORT FOREIGN SCHEMA ecommerce
  LIMIT TO (Customers, Orders, Products, OrderItems)
  FROM SERVER ch_srv INTO public;

-- 5. Join PostgreSQL & ClickHouse tables
SELECT c.name, SUM(oi.quantity) AS total_items
FROM Customers c
JOIN Orders o  ON o.customer_id = c.id
JOIN OrderItems oi ON oi.order_id = o.id
GROUP BY c.name;

Common Mistakes

Mistake: Importing every ClickHouse table at once. This bloats system catalogs and slows planning. Fix: Use LIMIT TO or create only required foreign tables.
Mistake: Assuming writes are supported. clickhouse_fdw is read-only. Fix: Use INSERT/UPDATE only on PostgreSQL tables or a materialized view pipeline.