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.

Sign up for the latest in SQL knowledge from the Galaxy Team!
Welcome to the Galaxy, Guardian!
Oops! Something went wrong while submitting the form.

Description

Table of Contents

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

Frequently Asked Questions (FAQs)

Is clickhouse_fdw safe for writes?

No. The wrapper is read-only. Use ClickHouse’s native clients or Kafka engine for inserts.

Does PostgreSQL push down filters?

Yes—WHERE clauses, LIMIT, and some aggregates are executed in ClickHouse when supported.

Can I secure the connection with TLS?

Set secure '1' and point to the ClickHouse server’s HTTPS port in the SERVER definition.

Want to learn about other SQL terms?

Trusted by top engineers on high-velocity teams
Aryeo Logo
Assort Health
Curri
Rubie Logo
Bauhealth Logo
Truvideo Logo
Welcome to the Galaxy, Guardian!
Oops! Something went wrong while submitting the form.