Postgres to ClickHouse: 5 Proven Ways to Move or Sync Data

Most teams that move from Postgres to ClickHouse never actually leave Postgres. They split the workload: Postgres keeps the transactional writes, ClickHouse takes the analytical reads. There are five ways to get the data across, and choosing between them comes down to three questions: one-time move or continuous sync, how much data, and whether your ClickHouse runs on ClickHouse Cloud or somewhere else.

That third question is the one most guides skip, and it silently removes options. The managed CDC path that gets recommended first only exists on ClickHouse Cloud.

This is written for developers whose aggregations have outgrown vanilla Postgres and who are standing up ClickHouse right now. By the end, you will be able to decide three things: which of the five methods fits your data size and downtime tolerance, which tables to move and which to leave in Postgres, and where the ClickHouse target should actually run.

Postgres to ClickHouse is a split, not a replacement

Postgres stays your system of record for transactional writes. ClickHouse becomes the read path for analytical queries, fed a copy of the data rather than replacing the source.

The two engines are built on opposing tradeoffs, and the differences are structural rather than something you tune your way out of:

Read that table left to right and you have the reason a PostgreSQL to ClickHouse lift and shift disappoints: a schema that is correct in Postgres is close to wrong in ClickHouse. Same SQL dialect family, same table names, entirely different physics.

The split itself is well established rather than novel. ClickHouse’s own product page for its managed Postgres service describes pairing Postgres for transactions with ClickHouse for analytics as a pattern used by companies including GitLab, Cloudflare, Instacart and LangChain.

What remains is the mechanical decision: what to copy, and how.

How to tell when you have outgrown Postgres for analytics

You have outgrown vanilla Postgres for analytics when specific measurable signals appear, not when you cross a row count. There is no universal wall. Row width, indexing, partitioning, write rate, retention and hardware all move it, which is why any article quoting a fixed number of rows is guessing.

These four signals are worth checking against your own instance before you decide anything:

The 90% cache-hit figure is a widely used monitoring heuristic rather than a hard threshold, so treat the direction of travel as the signal, not the number.

Exhaust the cheap fixes first. Most teams reach for a new engine before they have tried:

• A dedicated read replica so reporting stops competing with production traffic
• Materialized views for aggregations you already know you need
• BRIN indexes on time-ordered columns, which stay small on append-only data
• A higher work_mem for the analytics role so sorts and hash joins stay in memory

If a read replica is the fix, the next question is who operates it, and the tradeoffs are the same ones that apply to any managed PostgreSQL deployment. For the wider picture of which component gives way first under load, the analytics symptom is one part of what breaks first as you scale.

Where they stop working is contested. In one r/dataengineering thread on handling analytics at 1 TB and above, the consensus leaned toward ClickHouse, with the sharpest caveat being that “can” and “should” are different questions. If you want a read on your own setup before changing anything, you can audit your current infrastructure first.

When not to use ClickHouse: 5 workloads to keep in Postgres

ClickHouse is the wrong choice for five specific workload shapes, and knowing them is more useful than knowing when to use ClickHouse. If your workload looks like any of these, moving data into it will cost you more than it returns:

The 100 GB line is a rule of thumb repeated widely in the ClickHouse community rather than a documented limit, usually paired with a similar note about servers below 64 GB of RAM. Community answers to smaller cases tend to be blunter still: at hundreds of thousands of rows, the advice is generally to stay on Postgres and stop there.

One caveat on that list, because the internet is out of date on it. The frequently repeated claim that ClickHouse lacks a sophisticated query optimizer no longer holds. ClickHouse’s own engineering write-up from March 2026 documents the Analyzer as the default execution layer, with predicate pushdown added in 24.4 and automatic join reordering that arrived for two-table joins in 24.12 and extended to three or more tables in 25.9.

Joins are meaningfully better than the older advice suggests. They still are not free, and the same post recommends keeping real-time queries to three or four joins and using dictionaries instead of joins for lookups, so denormalisation remains the safer default.

If one of those five rows describes your workload, the useful next step is a different engine rather than a different pipeline, and there are other real-time analytics engines that handle updates, joins or point lookups better than ClickHouse does.

5 ways to move data from Postgres to ClickHouse

There are five ways to get data from Postgres into ClickHouse: a one-time bulk load through files, ClickHouse’s native postgresql() table function and table engine, the MaterializedPostgreSQL database engine, managed or third-party CDC, and pg_clickhouse query pushdown, which moves no data at all. Which one fits comes down to the three questions from the intro: once or continuously, how much data, and whether your ClickHouse instance is on ClickHouse Cloud.

That last column is the one most guides leave out, and it eliminates options before you start:

All five need a target instance to point at, whether that is a cluster you run yourself or managed ClickHouse that someone else operates.

Method 1: One-time bulk load via CSV or object storage

Use this for the initial load, and for anything multi-terabyte where a direct connection between the two servers is the bottleneck. Export with Postgres’ native copy, then stream the file into ClickHouse:

psql -h pg-host -U pg_user -d mydb -c "\copy (SELECT * FROM events) TO 'events.csv' WITH CSV HEADER"
clickhouse-client --query="INSERT INTO events FORMAT CSVWithNames" < events.csv

For large loads, write Parquet to S3 or GCS and read it with ClickHouse’s s3 table function instead. Parquet is columnar, so ClickHouse reads only the columns it needs and skips the parsing overhead that JSON and CSV impose on every row.

Method 2: The postgresql() table function and PostgreSQL table engine

This is the fastest path to a working copy, and the only one that also lets you query Postgres in place. Create the target with a real sorting key, then pull the rows across in a single statement:

CREATE TABLE events
(
    event_type LowCardinality(String),
    user_id UInt64,
    created_at DateTime,
    payload String
)
ENGINE = MergeTree
ORDER BY (event_type, created_at);

INSERT INTO events
SELECT *
FROM postgresql(
    'pg-host:5432',
    'mydb',
    'events',
    'pg_user',
    'pg_password'
);

Two details the docs bury. The ClickHouse postgresql engine supports INSERT as well as SELECT, so writes against that table land back in Postgres, which is occasionally useful and occasionally a nasty surprise. And past roughly 50 to 100 GB in one statement, you should chunk it with a WHERE id BETWEEN filter, or you risk connection timeouts and heavy memory use on the Postgres side.

Method 3: MaterializedPostgreSQL, for self-hosted continuous sync

ClickHouse can act as a Postgres logical replication client itself, taking a snapshot and then streaming changes with no external tooling. Three constraints decide whether you can use it: ClickHouse still flags it experimental, it is not supported on ClickHouse Cloud, and it does not replicate DDL, so schema changes upstream will break it.

SET allow_experimental_database_materialized_postgresql = 1;

CREATE DATABASE pg_sync ENGINE = MaterializedPostgreSQL('pg-host:5432', 'mydb', 'pg_user', 'pg_password')
SETTINGS materialized_postgresql_tables_list = 'events,sessions';

On the Postgres side, you need wal_level = logical and spare replication slots. Reasonable for a stable schema on a self-hosted cluster, not something to build a business on.

Method 4: Managed and third-party CDC for Postgres to ClickHouse replication

This is the production answer for continuous replication, and where the Cloud question bites hardest. ClickPipes, the managed CDC connector built on PeerDB, only exists on ClickHouse Cloud, including its BYOC deployments in your own AWS or GCP account. If your ClickHouse runs anywhere else, self-hosted or managed by a third party, your options are the open-source and third-party tools below.

Capability columns reflect vendor documentation, so verify them against your own schema before committing. The gotchas come from engineers reporting them in public rather than from vendor docs, which is why they are worth more.

Pick by scope, not by capability. Debezium plus Kafka is the most battle-tested path and the correct one if you are already running Kafka or fanning data out to several destinations. For a handful of tables, it is a lot of infrastructure to operate for one pipeline, and practitioners consistently say so.

Method 5: pg_clickhouse query pushdown, with no data movement

The newest option, released by ClickHouse in December 2025, and the only one that copies nothing. You install the pg_clickhouse extension in Postgres; analytical queries still arrive at Postgres, and execution is pushed down to ClickHouse behind the scenes. Application SQL stays unchanged, which makes it the least invasive way to test whether columnar execution actually solves your problem.

It is also bundled into ClickHouse’s own managed Postgres, so check availability on your platform before designing around it.

Which tables to move from Postgres to ClickHouse (and which to leave)

Move your large append-only fact and event tables. Leave small, mutable dimension and lookup tables in Postgres, and reach them from ClickHouse when a query needs them.

A version of this question comes up repeatedly in the ClickHouse community: four or five big append-only tables, a handful of small lookup tables the application still updates, and no clear guidance on whether all of it should move. Moving everything is the default assumption, and it is usually the wrong one.

Three questions settle it per table:

• Is it appended to and rarely changed? Move it.
• Does the application update it in place? Leave it in Postgres.
• Does it exist only to label or filter facts? Leave it, and load it into ClickHouse as a dictionary.

For the tables you leave behind, you have two ways to use them from ClickHouse. The postgresql table engine queries them live, which is fine for occasional joins. For labels you touch on every query, load them as a ClickHouse dictionary instead and use dictGet, which keeps the lookup in memory and avoids the join entirely.

The mistake worth avoiding is routing a mutable table through CDC because it was easier than thinking about it. A few thousand row updates a day arriving in a ReplacingMergeTree gives you deduplication semantics to reason about on every query, in exchange for data you could have read from Postgres directly.

Half your schema staying in Postgres permanently also means Postgres is not going anywhere, so it is worth knowing what managed Postgres costs once it is carrying production writes and serving lookups for your analytics layer.

5 Postgres to ClickHouse schema mistakes that keep queries slow

Data arriving in ClickHouse is not the finish line. Automated tooling generates a schema that technically works and performs badly, so teams reach the end of a migration, run the query that started the project, and find it is still slow.

The pattern is documented, not anecdotal. Altinity’s own guide for its sink connector shows the auto-created target table arriving as ORDER BY tuple() with nearly every column wrapped in Nullable. Engineers running Debezium report the same type mappings. Both defaults are close to the worst choices available for analytical queries.

Five things to fix before you call it done:

• ORDER BY tuple(), or a UUID as the leading key. The sorting key sets physical data order and is what lets ClickHouse skip granules. Put low-cardinality columns first, then time. Leading with a UUID gives you no pruning at all.
• Blanket Nullable. Every nullable column carries a separate marker column that is read on every query. Use defaults where null is not meaningful, and LowCardinality(String) for repeated string values.
• No primary key on the source table. Postgres tables need a primary key, or REPLICA IDENTITY FULL, before updates and deletes will replicate at all. Without it, your CDC pipeline silently ships inserts only.
• FINAL in ad-hoc queries. It forces reconciliation across unmerged parts at query time. Push deduplication into a materialized view instead, and use CollapsingMergeTree when you need real deletes rather than tombstones.
• Unchanged TOAST columns. Postgres leaves large TOASTed values out of logical replication messages when the row changed but that column did not, so your pipeline receives a placeholder instead of the real value. Debezium sends __debezium_unavailable_value, and a consumer that writes it through will overwrite good data with a sentinel string. REPLICA IDENTITY FULL is the general fix.

Type mapping and sorting-key design is the kind of work you can hand to an AI agent connected to the live instance and iterate on in minutes, rather than doing by hand against a schema you are still learning.

One limit to plan around: how much of your ClickHouse an agent can actually touch depends on the server it connects through. The query-side ones read your schema and run SELECTs against it, which covers the diagnosis, but none of them can alter a table, so applying the fix stays with you.

Verify before you cut over

Run your real queries against the target before switching any reads. Take the top queries the migration was meant to fix, run them on both engines, and compare row counts, aggregate totals and latency.

A SELECT count() that matches is not verification; an aggregate that matches to the last decimal on a month of data is. This is also the cheapest moment to discover the schema problems above, while Postgres is still serving every read and rolling back costs you nothing.

Where should your ClickHouse run: self-hosted, ClickHouse Cloud, or managed?

Three options, and one constraint decides more than the others: ClickPipes only exists on ClickHouse Cloud. Choosing where the target runs also chooses which CDC paths are open to you, so make this decision before you pick a pipeline rather than after.

By this point, the split has added real work: two engines, a sorting key to design, a CDC pipeline to own, and a cutover to verify. One cost worth pricing before you commit: on ClickHouse Cloud the backfill itself is billable, because ClickPipes meters $0.20 per unit-hour plus $0.04 per GB ingested on top of compute and storage. That is one of the four meters behind a ClickHouse Cloud bill, and a migration is the thing that switches it on.

That work does not disappear when you pay someone, but most of the operational half does.

There is now a fourth shape worth naming. ClickHouse sells managed Postgres as well, bundled with pg_clickhouse and ClickPipes, so both halves of the split live in one Cloud account. If minimising integration work matters more than anything else, that is the shortest path available.

Worth knowing what that shape costs before you commit to it. The Postgres half starts at $23.19 a month for 1 vCPU and 8 GB, though that is a 50% beta rate and three of the four lines on its bill carry no published price yet. We priced the full ClickHouse Managed Postgres ladder and modelled what changes at GA.

Their pitch argues against network-attached storage such as EBS, which is a real consideration for latency-sensitive transactional writes and a much less decisive one for an analytical target fed by batched inserts, where merge throughput and compression usually bind before the storage attachment does.

Two things to check against your own requirements: read replicas are priced separately from the two HA standby instances, and the Terraform provider was still in alpha as of July 2026 rather than generally available.

Selfhost.dev sits in the third row. It runs dedicated managed ClickHouse on AWS at fixed instance pricing, with backups and snapshots included, and Multi-AZ and Keeper HA available as options. On the configuration we price publicly, 2 vCPU, 8 GB RAM and 100 GB of always-on storage in ap-south-1, that works out to $56.46 per month with Multi-AZ off, against roughly $173 for a comparable always-on ClickHouse Cloud instance (8 GiB, single replica) in the same region as of August 2026, close to 3x.

The gap is configuration-dependent and narrows at larger sizes, so price your own shape before treating that as a rule. If you want the full head-to-head, we keep a detailed Selfhost.dev vs ClickHouse Cloud comparison, and if you are still choosing a provider, our roundup of the best managed ClickHouse services covers the field rather than just us.

Three things make it fit the split specifically. Billing is prepaid credits rather than metered usage, so a backfill that reads a month of history does not produce a surprise invoice. Instances pause to zero while you are still testing.

And you can run it inside your own AWS account with BYOC, where the management fee is 30% of the managed price and AWS bills you for compute and storage directly, which keeps the data in infrastructure you control. Postgres and ClickHouse also sit on the same bill, which is what running both engines actually requires.

If you are costing this out, you can see the pay-as-you-go pricing before committing to anything. If you already know the shape you need, you can deploy managed ClickHouse free and point your first backfill at it today.

The verdict: pick the target before the pipeline

Moving from Postgres to ClickHouse is a split rather than a replacement, and the method follows from three answers: one-time or continuous, how much data, and whether your ClickHouse runs on ClickHouse Cloud. Bulk load through files for the first move.

The postgresql() table function for backfills up to a few hundred gigabytes. Managed or third-party CDC once it is carrying production. And pg_clickhouse if you would rather not move data at all yet.

Decide where the target runs before you choose the pipeline, because that decision removes options rather than adding them. When you are ready to test one, you can deploy managed ClickHouse free and run your first backfill against it.

Frequently asked questions