Use the clickhouse_fdw extension to query ClickHouse data inside PostgreSQL as if it were local tables.
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.
Install ClickHouse server, grant a user, and compile the clickhouse_fdw extension on the PostgreSQL host that will run the queries.
Run git clone https://github.com/Percona-Lab/clickhouse_fdw
, then make && make install
.Restart PostgreSQL so the new foreign-data wrapper is discoverable.
Inside psql, load the extension, create the foreign server with host, port, and database, then map a PostgreSQL role to the ClickHouse login.
Yes—IMPORT FOREIGN SCHEMA
pulls every table or a subset into Postgres, creating foreign tables you can query instantly.
Once foreign tables exist you can use standard SQL joins, filters, and aggregates.PostgreSQL pushes compatible operations down to ClickHouse for speed.
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.
.
No. The wrapper is read-only. Use ClickHouse’s native clients or Kafka engine for inserts.
Yes—WHERE clauses, LIMIT, and some aggregates are executed in ClickHouse when supported.
Set secure '1'
and point to the ClickHouse server’s HTTPS port in the SERVER definition.