ClickHouse destination

The ClickHouse destination is in private alpha and available only to approved organizations. Request access before following this guide.

ClickHouse is a database for analytics. Supabase Pipelines replicates Postgres tables to ClickHouse as either current-state tables or an append-only history of changes.

To replicate data to ClickHouse:

1. Choose a table engine and check the source table requirements.
2. Prepare a database, user, and HTTPS endpoint in ClickHouse.
3. Configure the ClickHouse destination in the Dashboard.
4. Query the replicated data in ClickHouse.

Source table requirements#

The source table requirements depend on the selected table engine and the operations in the Postgres publication. ReplacingMergeTree requires a source primary key. MergeTree can replicate insert-only tables without one. If a table has a primary key, include all its key columns in the publication.

Check the replica-identity and array requirements for your source tables.

Choose a table engine#

The table engine determines how ClickHouse stores and queries replicated changes. Choose one engine for the entire destination:

Choose the engine before creating the pipeline. Changing Table engine later does not convert existing destination tables. Writes fail if their engine differs from the configured one. Restore the previous setting to resume writing to those tables.

With ReplacingMergeTree, changing a source primary-key value removes the old key from the current-state view and writes the row under its new key. Changing which columns make up the primary key is a separate schema change.

Prepare ClickHouse resources#

Managed Pipelines run in AWS eu-central-1 (Frankfurt). When creating your ClickHouse service, choose a nearby region to reduce replication latency. Then prepare these resources in ClickHouse before creating the destination:

1. Create an empty database for the replicated tables. In ClickHouse Cloud, open your service's SQL console and run create database if not exists pipelines;, replacing pipelines if you prefer another name. Pipelines creates and manages the tables and, for ReplacingMergeTree, the current-state views. Do not create or alter these objects yourself.
2. Create a dedicated database user for Pipelines and grant it access to that database. In ClickHouse Cloud, create this user in the SQL console; the organization's Users and roles page manages console members. The database user must be able to:
   • Query system.databases, system.tables, and system.columns
   • Create, alter, truncate, and drop tables
   • Create and drop views when using ReplacingMergeTree
   • Insert rows into managed tables
3. Copy the public HTTPS endpoint for your ClickHouse server. In ClickHouse Cloud, click Connect and select HTTPS to find it. Include the port if your endpoint requires one. Pipelines cannot connect to HTTP endpoints or private and internal hostnames.

For ReplacingMergeTree, the ClickHouse server must run version 23.5 or later. MergeTree does not have this minimum-version requirement.

Configure ClickHouse as a destination#

Follow Set up Pipelines. When prompted to choose a destination, select ClickHouse and enter the following settings:

Click Start pipeline to validate the destination. Review the cost estimate, then click Create and start pipeline.

Query replicated data#

How table names are mapped#

Pipelines maps each Postgres schema and table pair to one ClickHouse table. Existing underscores are doubled, and the schema and table names are joined with one underscore:

Postgres schema and table names cannot start or end with _ or contain " or ; when replicating to ClickHouse.

ReplacingMergeTree#

Use the generated __current view when you need the latest version of each source row. ReplacingMergeTree is the default engine. Pipelines:

• Uses the source primary key as ClickHouse's sorting and deduplication key
• Adds an _etl_version UInt128 ordering column
• Adds an _etl_deleted UInt8 tombstone column
• Creates a <table>__current view that runs the base table with FINAL and removes deleted rows

The _etl_version and _etl_deleted names are reserved and can't be used by source columns.

Query the generated view for the current state:

1
select *
2
from pipelines."public_orders__current";

ClickHouse background merges combine older row versions over time. Before a merge, the base table can contain multiple versions of the same source row. The generated view uses FINAL to return the current version and excludes deleted rows.

Pipelines does not run OPTIMIZE ... FINAL CLEANUP. ClickHouse operators remain responsible for any physical tombstone cleanup required by their storage-retention policy.

MergeTree#

Query the base table when you need the history of inserts, updates, and deletes. MergeTree stores each replicated change as an append-only event. Pipelines adds:

• cdc_operation, containing INSERT, UPDATE, or DELETE
• cdc_lsn, containing the Postgres commit LSN for the change
• cdc_tx_ordinal, containing the change's position within that transaction

The cdc_operation, cdc_lsn, and cdc_tx_ordinal names are reserved and can't be used by source columns.

Inserts and updates append the complete new row. A primary-key value update also appends a DELETE for the old key. Deletes append the old row when the source uses REPLICA IDENTITY FULL. With primary-key identity, a delete contains the key values; other fields use NULL for nullable scalars or placeholders such as zero, empty strings, and empty arrays. Those placeholders are not the deleted row's original values.

Order source changes by cdc_lsn and then cdc_tx_ordinal. The old-key delete and new-key update from one primary-key value change share both values, so this pair is not a unique destination-row ID.

Truncates and table restarts#

A source TRUNCATE truncates the ClickHouse table for either engine, then ongoing replication continues. It does not start a new initial sync.

Restarting replication for a table drops and recreates its table and, for ReplacingMergeTree, its generated view. A table restart erases the accumulated destination data and copies existing source rows only if the table is selected for initial sync.

Replica identity and arrays#

REPLICA IDENTITY NOTHING cannot support updates or deletes.

Use REPLICA IDENTITY FULL for tables whose updates can omit unchanged out-of-line TOAST values. It lets Pipelines reconstruct the complete new row. Changing replica identity affects only new WAL; incompatible retained updates can still require a table restart.

Array elements can be NULL, but a top-level array value cannot. Replace top-level NULL values and enforce NOT NULL, or ensure producers always supply an array. Empty arrays are supported.

Type mapping#

Pipelines creates ClickHouse columns with these mappings:

Nullable scalar columns are wrapped in Nullable(...). Character, text, numeric, money, JSON, time, interval, binary, bit-string, enum, and unsupported custom values are serialized into String columns rather than stored as native ClickHouse types.

Schema change support#

Pipelines supports:

• Adding columns
• Renaming or dropping columns; nested subcolumns must stay under the same parent when renamed
• Dropping NOT NULL from an existing scalar column
• Adding, changing, or removing supported column defaults
• Adding or removing published columns on tracked tables, subject to the same restrictions

With ReplacingMergeTree, renaming or dropping a primary-key column or changing the key's columns or their order is rejected.

New scalar columns are made nullable when ClickHouse needs a value for historical rows and the source default cannot be represented safely. Adding NOT NULL keeps an existing destination column nullable. Defaults that cannot be translated are skipped; Postgres still supplies the source values through replication.

A previously excluded scalar column is added without a default, leaving historical rows NULL. Removing a published column drops its destination values; adding it again does not restore them. To include a previously excluded array column, select the table for initial sync and restart its replication.

For type changes, unsupported changes, and interrupted schema changes, see the shared schema-change behavior and recovery.

Troubleshooting#

Use pipeline monitoring to inspect errors and include the pipeline ID when contacting support.

Additional resources#

• ClickHouse documentation
• ReplacingMergeTree
• Monitor pipeline status