ClickHouse to PostgreSQL: How to Move Your Data

Replicating ClickHouse to PostgreSQL moves data from a columnar analytics engine into a row-oriented transactional database, usually so an application, an internal tool, or a service that only speaks Postgres can read results that were computed in ClickHouse. Airbyte builds that pipeline without code, and the interesting decisions are about what you send rather than how you connect.

This direction runs against the grain of both systems, which is fine as long as you are deliberate about it. ClickHouse is built to scan billions of rows; Postgres is built to fetch a few by key. Sending a raw event table across that boundary is the most common mistake here.

What should you actually send to Postgres?

ClickHouse tables are frequently enormous, and Postgres will accept an enormous table without complaint right up until the queries against it become unworkable. Decide early whether you are replicating a raw table or a result.

In most cases the answer is a result. Aggregate in ClickHouse, where that work is cheap and fast, into a materialised view or a summary table, and replicate that. A daily rollup of a billion-row event table might be a few thousand rows, which Postgres will serve happily with an index. The raw table will not behave the same way.

Note also that indexing is entirely your responsibility on the destination side. Airbyte creates the table and writes the rows, but the index your application needs is something you add afterwards, and its absence is the usual explanation for a pipeline that works fine and an application that feels slow.

What can the ClickHouse source do?

The ClickHouse source is built on the JDBC code base and supports full refresh and cursor-based incremental sync. Its documentation lists replicating incremental deletes and logical replication from the write-ahead log as coming soon, so neither is available today.

For append-heavy event tables a timestamp cursor captures everything, which covers most ClickHouse workloads. Deletions are the gap: a row removed in ClickHouse will remain in Postgres until a full refresh overwrite replaces the table.

If your source tables use ReplacingMergeTree, there is a subtler version of the same problem. A background merge that replaces a row does not advance the cursor, so the replacement may never be extracted. Read through a view applying FINAL if convergence matters, or accept a point-in-time read.

One version note before you build: connector version 0.4.0 declared temporal column typing a breaking change, and connections hitting schema evolution errors must follow the connector's migration guide. Read that first if you are upgrading rather than starting fresh.

Setting up the source

You need ClickHouse Server 21.3.10.1 or later. Create a dedicated read-only user rather than reusing an existing account, then grant SELECT on the relevant database or narrow the grant to specific tables.

One structural point catches people out. A single source cannot cover multiple ClickHouse databases. You grant the user access to each schema, then configure a separate source per schema, each connecting to the same instance. Plan your connection count around that rather than expecting one source to serve the whole cluster.

SSL is supported, and if the instance has no public address the connector can reach it through an SSH tunnel to a bastion host using an RSA key or a password. The SSH login username is the operating system user on the bastion, not a ClickHouse account, which is a routine misconfiguration.

How does the data land in Postgres?

The Postgres destination writes each stream to a table in your target schema, with your fields as typed columns alongside Airbyte's metadata columns. It is an Airbyte-supported connector with a high reported sync success rate, so this half of the pipeline is well-trodden.

The mapping to plan for is ClickHouse's Array and Nested types. These are commonly used for tags, event properties, and similar structures, and they arrive as JSON rather than as native Postgres arrays. Postgres queries JSON perfectly well with its JSON operators, but those columns will not behave like flat columns in an application ORM. If a specific key inside a nested structure is what your application reads, extract it into a typed column in a downstream model or a view.

On sync mode, incremental append with deduplication gives you a Postgres table mirroring the current state of the ClickHouse table, provided you have a usable primary key and a reliable cursor. Where deletions matter and volumes are modest, a scheduled full refresh overwrite is simpler and self-correcting.

Frequently asked questions

Will rows deleted in ClickHouse disappear from Postgres?

No. The source lists incremental deletes as coming soon, so deletions are not captured. A periodic full refresh overwrite is the way to bring the destination back in line.

Should I replicate my raw event table?

Usually not. Aggregate in ClickHouse first and replicate the summary. Postgres will accept a very large table but will not serve analytical queries over it the way ClickHouse does.

Can one source read several ClickHouse databases?

No. Configure a separate source per schema, each pointing at the same instance, and grant the read-only user access to every schema you intend to replicate.

Do I need to create indexes on the destination tables?

Yes. Airbyte creates the tables and writes the rows, but the indexes your application needs are yours to add after the first sync.

Get your ClickHouse data into PostgreSQL

Aggregate before you replicate, pick a cursor that suits an append-heavy table, and add your own indexes once the tables exist. For the reverse direction, see our guide to PostgreSQL to ClickHouse. For the same source into a warehouse instead, see ClickHouse to BigQuery.

Start syncing now →