PostgreSQL to ClickHouse: How to Replicate Your Data
Replicate PostgreSQL into ClickHouse with Airbyte. CDC setup, the async_insert fix, and why you need FINAL in your queries to see correct results.
Replicating PostgreSQL to ClickHouse is the classic move for teams whose analytical queries have outgrown their transactional database. Postgres keeps serving the application; ClickHouse takes the aggregations, the dashboards, and the scans over hundreds of millions of rows.
The pipeline is well supported on both ends. The part that surprises people is what happens after the data lands, because ClickHouse handles updates and deletes differently from every other destination in this list. If you do not know that, your query results will be wrong in a way that looks like a sync bug.
PostgreSQL to ClickHouse at a glance:
Which Postgres replication method should you choose?
The Postgres source offers three, and for a ClickHouse destination the choice is more consequential than usual.
CDC reads the write-ahead log through logical replication and is the only method that captures deletions. Airbyte recommends it when you need a record of deletes, when the database is 500 GB or more, or when a table has a primary key but no sensible cursor column. Because ClickHouse deduplication depends on knowing which rows superseded which, CDC is the method that makes this pairing work properly.
xmin uses the Postgres system column to track changes reliably without configuring logical replication. It is a good middle ground for moderate databases, but it does not capture deletes.
Standard relies on a cursor column you nominate. Simplest to set up, weakest guarantees: no deletes, and rows are missed if the cursor is not reliably monotonic.
How do you configure CDC on the Postgres side?
Grant REPLICATION permission to your Airbyte user, then create a dedicated logical replication slot using the pgoutput plugin with SELECT pg_create_logical_replication_slot('airbyte_slot', 'pgoutput');.
The slot must be used by exactly one source, since sharing it across connections causes problems. Next, set a replication identity on each table you want to replicate, and create a publication covering them with CREATE PUBLICATION airbyte_publication FOR TABLE <tbl1, tbl2, tbl3>; A table selected in the Airbyte UI but absent from the publication will not replicate, and nothing will warn you.
One piece of operational hygiene matters more than any other: if you stop syncing a CDC-configured Postgres instance, delete the replication slot. An abandoned slot causes Postgres to retain WAL indefinitely and can fill your disk. Also note that AWS Aurora's CDC caching layer is incompatible with Airbyte's implementation, so set rds.logical_wal_cache to 0. TimescaleDB does not support CDC replication at all.
How do you set up the ClickHouse destination?
Create a dedicated user rather than reusing an existing one. The ClickHouse destination docs list the exact grants: create and manage databases, create, alter, drop and truncate tables, and insert and select data.
One grant-adjacent setting causes a specific, confusing failure. If async_insert is enabled on your ClickHouse instance, disable it for the Airbyte user with ALTER USER airbyte_user SETTINGS async_insert = 0; Without it you will see an error about failing to insert expected rows into the check table, with zero rows actually written.
Then configure the connector with the hostname (no protocol prefix), the port (8123 for HTTP or 8443 for HTTPS), the database, and your credentials. Enable the JSON option if your ClickHouse version supports it, so object fields land as the native JSON type rather than strings. SSH tunnelling is available but currently in beta.
Version 2.0.0 of the destination was a full rewrite. Data is written directly to typed columns matching your source schema rather than stored as JSON blobs in raw tables, and all sync modes are supported.
Why do you need FINAL in your queries?
This is the thing to internalise. Deduplication in the ClickHouse destination uses the ReplacingMergeTree table engine, which collapses duplicate rows during background merges rather than at write time. Merges happen when ClickHouse decides to run them, not when your sync finishes.
Until a merge completes, a plain SELECT can return duplicated rows, or rows that CDC already deleted. To guarantee deduplicated results at query time, add the FINAL operator: SELECT * FROM your_table FINAL.
Skip this and your dashboards will disagree with Postgres in ways that look like a replication failure but are not. Tuning merge settings to match your query patterns is the alternative if FINAL is too expensive at your scale.
Your cursor column type also affects dedup quality. Integer, Date, and DateTime64 cursors give correct ordering. A string cursor makes the connector fall back to ordering by _airbyte_extracted_at, which may not reflect the true order of changes in your source. A warning appears in the sync logs when this happens.
How do Postgres types land in ClickHouse?
- Decimals become Decimal(38, 9), giving 38 digits of precision with 9 decimal places.
- Timestamps become DateTime64(3), millisecond precision.
- Objects become the native JSON type if you enabled that option, otherwise String.
- Arrays and unions always become String. Use ClickHouse JSON functions to parse them if you need structured access.
Frequently asked questions
Why am I seeing duplicate rows in ClickHouse?
Almost certainly because your query is missing the FINAL operator. ReplacingMergeTree deduplicates during background merges, so until one runs, duplicates and deleted rows remain visible to a plain SELECT.
Do I need CDC, or will xmin do?
Use CDC if you need deletions reflected, if the database is 500 GB or larger, or if tables have primary keys but no reliable cursor. xmin is a solid middle option for moderate databases where deletes do not matter.
What happens if I stop syncing but leave the replication slot?
Postgres retains write-ahead log segments for that slot indefinitely, which can fill your disk. Delete the slot when you decommission a CDC connection.
Why does my sync keep doing a full refresh instead of incremental?
If the logs mention that the saved offset is before the replication slot's confirmed flush LSN, the WAL segments Airbyte needed were already removed by Postgres. Heavy update volume or long gaps between syncs are the usual causes.
Does Airbyte work with Aurora or TimescaleDB?
Aurora works if you disable its CDC caching layer by setting rds.logical_wal_cache to 0. TimescaleDB does not support CDC replication, so use xmin or a cursor there instead.
Get your PostgreSQL data into ClickHouse
Configure CDC properly, disable async_insert for the Airbyte user, and remember FINAL. Sending Postgres to a cloud warehouse instead? See our guides to PostgreSQL to BigQuery and PostgreSQL to Snowflake.
Connector behaviour described here reflects the Postgres source and ClickHouse destination v2.x, verified August 2026. Check the linked documentation for current versions.
Integrate with 600+ apps using Airbyte
Move data from 600+ sources into warehouses, lakes, and beyond. Set up pipelines in minutes with pre-built connectors and the Connector Builder.