Skip to content

Updated

ClickHouse destination

Replicate Supabase Postgres changes to ClickHouse.

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.

Source primary keys must be nondeferrable for either engine.

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:

EngineData model and query pattern
ReplacingMergeTreeCurrent-state tables. Requires a primary key. Query the generated __current view.
MergeTreeAppend-only CDC history. A primary key is optional for insert-only tables. Query the base table.

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:

FieldValue
HTTPS endpointPublic HTTPS endpoint, including its port when required
UserYour dedicated database user, such as pipelines_user
PasswordThat user's password, if set
DatabaseDestination database, such as pipelines
Table engineReplacingMergeTree for current state or MergeTree for event history

Complete validation and review the initial sync estimate before confirming creation.

Query replicated data

How table names are mapped

Pipelines maps each Postgres schema and table pair to one ClickHouse table. For names without leading or trailing underscores, existing underscores are doubled and the names are joined with one underscore:

Postgres tableClickHouse table
public.orderspublic_orders
my_schema.logsmy__schema_logs

Source schema and table names cannot contain " or ;. If either name starts or ends with _, Pipelines uses _SSTT_<schema>_<table> instead, preserving the original underscores. SS and TT are the two-digit lengths of the schema and table names. For this format, both names must contain only ASCII letters, digits, and underscores. For example, public._orders becomes _0607_public__orders, or _0607_PUBLIC__ORDERS in Snowflake. Check the generated destination name before writing queries.

With ReplacingMergeTree, destination table names ending in __current are reserved for generated views. Rename source tables that would map to these names, or choose MergeTree.

ReplacingMergeTree

Use the generated __current view when you need the latest version of each source row. ReplacingMergeTree is the default engine. It uses the source primary key to identify rows and the generated view resolves row versions 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:

select *
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 uses at-least-once ingestion into ClickHouse, so recovery can append the same change again. The generated view provides query-time deduplication. Querying the base table without FINAL can count repeated versions. See delivery guarantees for ordering and recovery limits.

Removing deletion markers can make deleted rows reappear when older changes replay. Pipelines does not perform tombstone cleanup. For manual cleanup, follow the ETL ClickHouse guide to verify that no older changes can replay.

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.

Retries can append repeated events. ClickHouse's insert-block deduplication can suppress some retries, but replay can group events into different blocks. MergeTree history therefore requires deduplication in your queries or downstream processing when each change must count once. Include the operation and row key when distinguishing a primary-key change's delete and update.

Initial-sync rows share baseline ordering metadata. It does not uniquely identify copied rows. Treat the initial snapshot separately from ongoing changes when deduplicating history.

Truncates and table resets

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

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

Replica identity and arrays

Published operationRequired replica identity
InsertNone; ReplacingMergeTree still requires a primary key
UpdatePrimary-key identity or REPLICA IDENTITY FULL; updates must contain complete new rows
DeletePrimary-key identity or REPLICA IDENTITY FULL
Delete with REPLICA IDENTITY USING INDEXThe index must contain exactly the source primary-key columns

REPLICA IDENTITY NOTHING cannot support updates or deletes.

Array elements can be NULL. A whole-array SQL NULL is stored as an empty array, so these two source values become indistinguishable.

Recover from partial updates

Use REPLICA IDENTITY FULL for tables whose updates can omit unchanged out-of-line TOAST values. It lets Pipelines reconstruct the complete new row.

Before setting REPLICA IDENTITY FULL, check the column lists in every publication that sends updates or deletes for this table. Full replica identity requires omitting the list entirely. Remove a list only if all columns can be replicated to the publication's subscribers.

Run the following SQL using the source project's SQL Editor in the Supabase Dashboard or with psql over a direct connection. To connect with psql, see Direct connection.

alter table public.your_table replica identity full;

For a partitioned table, run the statement on the parent and each partition. Postgres applies replica identity per table.

If replication has already failed on a partial update, use Reset table on the affected table. Changing replica identity applies only to new write-ahead log (WAL) records. The reset lets the table resume from a new replication position past the incompatible update.

Type mapping

Pipelines creates ClickHouse columns with these mappings:

Postgres typeClickHouse type
booleanBool
smallintInt16
integerInt32
bigintInt64
realFloat32
double precisionFloat64
dateDate32
timestamp without time zoneDateTime64(6, 'UTC')
timestamp with time zoneDateTime64(6, 'UTC')
uuidUUID
oidUInt32
Other scalar and custom typesString
ArraysArray(Nullable(<element>))

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.

Postgres timestamp values keep their source wall-clock value in the UTC column. Infinite dates and timestamps are rejected. Keep finite dates and timestamps within ClickHouse's supported range, from 1900 through 2299; values outside it can read back at the range limits.

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.

Source type changes that alter the mapped ClickHouse type are rejected, such as integer to bigint. Switching between timestamp and timestamptz is also rejected because it can change existing values. Changes that keep the mapped type, such as increasing a varchar length, continue without changing the destination type. Reset the table to rebuild the schema after a rejected change.

For publication changes, type changes, and schema errors, see the shared schema-change behavior and recovery.

Troubleshooting

IssueResolution
URL or connection failsUse a reachable public HTTPS endpoint with the correct port and credentials. Private endpoints are unsupported.
Validation passes but setup or writes failCheck permissions and resource requirements, including table ownership and the server version.
Updates, deletes, or arrays failCheck the source table requirements.
A schema change failsFollow the shared schema-change behavior and recovery.

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

Additional resources