ClickHouse Wrapper
Overview
ClickHouse Wrapper
ClickHouse is an open-source column-oriented database built for real-time analytics at scale. It delivers sub-second query performance over billions of rows, supports full read/write SQL, and is used by companies like Tesla, GitLab, and Cloudflare for observability, analytics, and AI workloads.
The ClickHouse Wrapper brings your ClickHouse tables into Postgres as queryable foreign tables. Read and write ClickHouse data with plain SQL and join it against your application data. Supports aggregate pushdown so queries run on ClickHouse rather than locally, and works with Supabase Vault for secure connection string management.
Supported Operations
| Object | Select | Insert | Update | Delete |
|---|---|---|---|---|
| Tables | ✅ | ✅ | ✅ | ✅ |
Supported Data Types
| Postgres Type | ClickHouse Type |
|---|---|
| boolean | Bool |
| "char" | Int8 |
| smallint | UInt8, Int16 |
| integer | UInt16, Int32 |
| bigint | UInt32, Int64, UInt64 |
| real | Float32 |
| double precision | Float64 |
| numeric | Decimal, UInt128, Int128 |
| text | String, FixedString(N), UInt256, Int256 |
| date | Date |
| timestamp | DateTime |
| uuid | UUID |
| boolean[] | Array(Boolean) |
| smallint[] | Array(Int16) |
| integer[] | Array(Int32) |
| bigint[] | Array(Int64) |
| real[] | Array(Float32) |
| double precision[] | Array(Float64) |
| text[] | Array(String) |
| any | Nullable(T) |
Preparation
Before you get started, make sure the wrappers extension is installed on your database:
_10create extension if not exists wrappers with schema extensions;
and then create the foreign data wrapper:
_10create foreign data wrapper clickhouse_wrapper_10 handler click_house_fdw_handler_10 validator click_house_fdw_validator;
Secure your credentials (optional)
By default, Postgres stores FDW credentials inside pg_catalog.pg_foreign_server in plain text. Anyone with access to this table will be able to view these credentials. Wrappers is designed to work with Vault, which provides an additional level of security for storing credentials. We recommend using Vault to store your credentials.
The connection string uses ClickHouse's native TCP protocol. Note: only native protocol ports 9000 and 9440 are supported. HTTP(S) ports 8123 and 8443 are not supported.
_10select vault.create_secret(_10 'tcp://default:@localhost:9000/default',_10 'clickhouse',_10 'ClickHouse credential for Wrappers'_10);
Connection string examples:
tcp://user:password@host:9000/clicks?compression=lz4&ping_timeout=42mstcp://default:PASSWORD@abc.eu-west-1.aws.clickhouse.cloud:9440/default?connection_timeout=30s&ping_before_query=false&secure=true
Connecting to ClickHouse
We need to provide Postgres with the credentials to connect to ClickHouse, and any additional options. We can do this using the create server command:
With Vault:
_10create server clickhouse_server_10 foreign data wrapper clickhouse_wrapper_10 options (_10 conn_string_id '<key_ID>' -- The Key ID from above._10 );
Without Vault:
_10create server clickhouse_server_10 foreign data wrapper clickhouse_wrapper_10 options (_10 conn_string 'tcp://default:@localhost:9000/default'_10 );
Resources
Details
Third-party integrations and docs are managed by Supabase partners.