How to connect ClickHouse on Azure in PostgreSQL

Why combine ClickHouse on Azure with PostgreSQL?

ClickHouse excels at large-scale analytics; PostgreSQL handles OLTP. A foreign data wrapper (FDW) lets you join both worlds without ETL or duplicated storage.

What prerequisites are required?

Azure-deployed ClickHouse endpoint, Postgres 13+ with clickhouse_fdw, open port 9440, and valid ClickHouse credentials or Azure AD token.

How do I install clickhouse_fdw?

Run CREATE EXTENSION IF NOT EXISTS clickhouse_fdw;. If missing, install postgresql-clickhouse-fdw via package manager or compile from source, then retry.

How do I define a foreign server?

Code

CREATE SERVER clickhouse_srv
FOREIGN DATA WRAPPER clickhouse_fdw
OPTIONS (
host 'ch-eastus-01.a.database.azure.com',
port '9440',
dbname 'ecommerce',
secure 'true'
);

How do I map users securely?

Code

CREATE USER MAPPING FOR app_user
SERVER clickhouse_srv
OPTIONS (user 'ch_app', password 'REDACTED');

How do I expose ClickHouse tables?

Code

IMPORT FOREIGN SCHEMA public
LIMIT TO (customers, orders, orderitems, products)
FROM SERVER clickhouse_srv INTO public;

How can I run cross-database analytics?

Example

SELECT c.name,
SUM(oi.quantity * p.price) AS lifetime_value
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN orderitems oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
GROUP BY c.name
ORDER BY lifetime_value DESC
LIMIT 10;

What performance flags help?

Set clickhouse_fdw.use_remote_estimate = on, align ClickHouse partitions with WHERE clauses, avoid SELECT *, and use LIMIT to cut transfer costs.

How do I troubleshoot?

Enable log_fdw_detail, watch Azure Monitor for CPU/latency, and test network RTT—keep it <50 ms for stable query times.