How to connect ClickHouse on Azure in PostgreSQL

Galaxy Glossary

How do I connect ClickHouse on Azure to PostgreSQL?

Use clickhouse_fdw to query Azure-hosted ClickHouse tables directly from PostgreSQL.

Sign up for the latest in SQL knowledge from the Galaxy Team!
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

Table of Contents

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.

Why How to connect ClickHouse on Azure in PostgreSQL is important

How to connect ClickHouse on Azure in PostgreSQL Example Usage


-- Top ten customers by total spend, querying ClickHouse tables from Postgres
SELECT c.name,
       SUM(oi.quantity * p.price) AS total_spend
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 total_spend DESC
LIMIT  10;

How to connect ClickHouse on Azure in PostgreSQL Syntax


-- Install extension
CREATE EXTENSION IF NOT EXISTS clickhouse_fdw;

-- Create foreign server pointing to Azure
CREATE SERVER clickhouse_srv
  FOREIGN DATA WRAPPER clickhouse_fdw
  OPTIONS (
    host   'ch-eastus-01.a.database.azure.com',
    port   '9440',        -- Azure’s secure HTTPS port
    dbname 'ecommerce',   -- ClickHouse database name
    secure 'true'         -- Enable TLS
);

-- Map PostgreSQL role to ClickHouse credentials
CREATE USER MAPPING FOR app_user
  SERVER clickhouse_srv
  OPTIONS (
    user     'ch_app',
    password 'REDACTED'   -- Use Azure Key Vault in prod
);

-- Import only needed tables
IMPORT FOREIGN SCHEMA public
  LIMIT TO (customers, orders, products, orderitems)
  FROM SERVER clickhouse_srv INTO public;

-- Optional FDW session parameters
SET clickhouse_fdw.use_remote_estimate = on;  -- pushdowns
SET clickhouse_fdw.compress               = on;  -- gzip wire compression

Common Mistakes

Frequently Asked Questions (FAQs)

Can I write to ClickHouse through the FDW?

No. clickhouse_fdw is read-only. Perform inserts via ClickHouse native interfaces or Kafka-engine tables.

Does the FDW support SSL?

Yes. Set secure 'true' and ensure port 9440 is open. Certificates are validated by libcurl.

How do I push filters to ClickHouse?

Keep use_remote_estimate = on and avoid PostgreSQL functions in WHERE clauses. Stick to simple comparisons and arithmetic.

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!
You'll be receiving a confirmation email

Follow us on twitter :)
Oops! Something went wrong while submitting the form.