Replicating PostgreSQL to ClickHouse with PeerDB CDC: what the move involves
When analytics queries start slowing the production PostgreSQL database, the usual path is a change data capture (CDC) pipeline into self-managed ClickHouse, not a one-time export. A continuous pipeline keeps the analytical copy current while PostgreSQL keeps serving the application, and it removes the reporting load from the primary. The work is less about moving bytes and more about deciding which tables replicate, how each one lands in ClickHouse, and how the two systems are reconciled before anyone trusts the numbers. For the team, the change is structural. Queries that relied on PostgreSQL indexes and joins will be rewritten against ordering keys and denormalized tables, and the pipeline itself becomes a new component to run, monitor and upgrade. PostgreSQL also takes on a new responsibility: it must retain the logical replication slot that the pipeline reads from, so an idle or failed consumer can grow WAL on the primary. Your DBA team needs to own that risk from day one.
Effort is driven by three things: the schema (how many tables, how wide, how many primary keys and data types need mapping), the write volume (sustained inserts, updates and deletes, and how long a full initial snapshot takes at your data size), and the cutover plan (how long both systems run in parallel and who signs off on reconciled counts). Ask any partner for a quote that lists each of these inputs explicitly, and confirm the source-side PostgreSQL version and replication settings in your own environment, since the replication prerequisites depend on them. The PostgreSQL high availability documentation is a useful starting point for the replication concepts involved.
Which PostgreSQL features map cleanly to ClickHouse and which do not
This table compares PostgreSQL features with their ClickHouse counterparts for a CDC replication design, so you can see which behaviors carry over and which need redesign. Read each row as a question to answer for your own schema before the initial snapshot starts. The main takeaway is that ClickHouse stores and replicates data well, but it does not enforce the transactional and relational guarantees your application may depend on.
| Feature | PostgreSQL | ClickHouse equivalent and caveats |
|---|---|---|
| Primary keys | Enforce uniqueness and act as row identity for updates and deletes | ORDER BY defines the sort and pruning key, not a uniqueness constraint. Duplicate keys can persist until merges run. |
| Row updates | Changed in place inside a transaction | Target tables usually use ReplacingMergeTree, which keeps the latest version by a version column after merges. Queries may need FINAL or argMax-style logic to see current rows. |
| Row deletes | Removed immediately and visible to the next query | Deletes arrive as tombstones or delete events that must be modeled explicitly, such as an is_deleted column. Physical removal waits for merges, so filter deleted rows in queries. |
| Data types | Wide range, including numeric, JSONB, arrays, and enums | Most common types map, but precision, nullability and JSON handling differ. Confirm each column type in a test snapshot before the schema is locked. |
| Logical replication slots | Track a consumer position and retain WAL until the slot advances | Slots belong to PostgreSQL, not ClickHouse. An unread slot can grow WAL on the source, so monitor it and plan for a stalled consumer. |
| Read replicas | Hot standby serves read-only queries and supports synchronous or asynchronous replication | ClickHouse replicas use ReplicatedMergeTree coordinated by ClickHouse Keeper. Replicas keep the same data for availability, but they are not a copy of the PostgreSQL standby topology. |
| Constraints and foreign keys | Enforced by the database on write | Not enforced on insert. Referential integrity must be checked in the pipeline or in validation queries. |
| Transactions | Atomic multi-table changes with rollback | Inserts into a table are not multi-statement transactions. Consistency across tables depends on how CDC batches and orders changes, so confirm the behavior you need. |
| Row-level joins and lookups | Normalized schemas joined at query time with indexes | Joins work but favor denormalized, wide tables. Plan the target model around the query patterns, not the source normalization. |
PeerDB, MaterializedPostgreSQL and other documented paths compared
PeerDB CDC reads row changes from PostgreSQL through logical replication and writes them into ClickHouse target tables. It covers an initial snapshot and then ongoing changes, which makes it the path for analytics that must stay current. It runs as a separate component next to your ClickHouse cluster, so its upgrades, credentials, monitoring and failure handling become part of your operating scope.
- Snapshot then streaming: the initial copy and the ongoing change feed are separate phases, and the cutover plan must account for changes that arrive during the copy.
- Explicit target mapping: you choose how source tables map to ClickHouse tables, including the table engine and ordering key, which determines how updates and deletes appear to queries.
- Source-side prerequisites: logical replication requires settings on the PostgreSQL primary and a replication role, and each one needs a review by your database team.
- Separate service to operate: the CDC component needs its own health checks, logs, restart procedure and access controls.
- Version coupling: supported PostgreSQL and ClickHouse versions depend on the PeerDB release you deploy.
Before you commit, confirm the supported PostgreSQL and ClickHouse versions, the delete handling and the recovery behavior in the PeerDB documentation for the release you plan to run.
ClickHouse includes a MaterializedPostgreSQL database engine that mirrors PostgreSQL tables inside ClickHouse without a separate CDC service. The ClickHouse documentation marks it as experimental, and that status is the main factor in the production decision. Experimental features can change behavior, settings or limits between releases, and their failure modes are less settled than those of stable features.
- Experimental status: treat behavior, supported DDL and recovery steps as subject to change, and do not assume a fix or guarantee from one release carries forward.
- Enablement setting: the engine requires an experimental setting to be turned on; confirm the setting name and its default in your ClickHouse version.
- Schema change handling: confirm how the engine handles added columns, dropped columns and type changes in your version before you rely on it.
- Testing burden: your team carries the test plan, the incident response and the support risk, with no vendor-backed production track record to point to within your own environment.
- Upgrade exposure: a ClickHouse upgrade can change how the engine behaves, so read the changelog before each upgrade, as the ClickHouse production guidance advises.
Use it only for non-critical copies with a tested fallback, and check the ClickHouse documentation for your exact version before you decide.
The PostgreSQL table engine lets ClickHouse read a live PostgreSQL table through a query without copying the data. It suits validation, reference lookups and small joins against dimension tables, and it does not suit sustained analytics over large fact tables. Every query reads from the source database, so the cost lands on your production PostgreSQL server and its connections.
- Load on the source: each query runs against PostgreSQL, so heavy reporting can compete with transactional traffic.
- Dependency on source availability: if the PostgreSQL server or network path is down, the ClickHouse queries fail.
- Read-only access: create a dedicated PostgreSQL role with read permissions only, and restrict it to the tables it needs.
- Type and semantics checks: compare how PostgreSQL types, null handling and timestamps map into ClickHouse before you trust results.
- Good fit for migration checks: comparing source and target rows during a migration is a common use, provided the load is controlled.
Confirm the engine's connection handling, pushdown behavior and supported types in the ClickHouse documentation for your version, and agree a connection limit with your PostgreSQL team.
For a dataset that is loaded once, or that changes rarely and can be reloaded on a schedule, a batch export is simpler than change data capture. You can run it with INSERT statements over a supported connection, or with clickhouse-local on a separate host so the ClickHouse cluster does not need a route to the source network. The sibling topic on clickhouse-local covers that host pattern in detail.
- No ongoing change capture: updates and deletes made after the export starts are missed unless you run another load, so define a cutoff.
- Repeatable loads: design the target so a rerun does not duplicate rows, for example by replacing whole partitions or by using a ReplacingMergeTree table with a version column.
- Network isolation: a separate export host limits the exposure of both the source database and the cluster.
- Verification: compare row counts and aggregate checksums on both sides for each table before you accept the load.
- Operational window: a consistent export may need a freeze or a read replica, which needs agreement with the database owner.
Decide with the business owners whether stale data is acceptable between loads, and confirm the INSERT and clickhouse-local syntax for your ClickHouse version.
How the snapshot, CDC and parallel run sequence works
The sequence moves PostgreSQL analytics into self-managed ClickHouse in controlled stages, so each stage has a verifiable result before the next one begins. It ends with analytics clients on ClickHouse and the old reporting path retired.
Inventory tables, keys and write rates
The platform team lists every table the reports depend on, with its primary key, approximate row count, update and delete frequency, and column types that may need mapping. They also confirm which PostgreSQL major version runs the source, since logical replication behavior and replication slot handling depend on it. The step is done when each table has a named owner, a sync requirement and a documented reason for inclusion or exclusion. Tables without a primary key need a decision before CDC design begins.
Prepare the source publication and replication slot
A DBA sets up a publication on the source PostgreSQL that covers only the agreed tables, and PeerDB creates a logical replication slot to read changes from it. Ideally this runs against a replica or a dedicated publication setup, so the primary carries as little extra load as possible. Confirm that the replication user has only the privileges it needs and that the slot's WAL retention is monitored, since an idle or stalled slot keeps WAL on disk. The step is done when the slot shows as active and the publication lists exactly the in-scope tables.
Run the initial snapshot into target tables
PeerDB copies the current contents of each in-scope table into ClickHouse as a baseline before streaming changes. Target tables use ReplacingMergeTree with the source primary key as the sorting key, so later updates for the same key collapse to the latest version during merges. Watch the snapshot for failed batches and type conversion errors, and record start and finish times per table. The step is done when every table reports a completed snapshot and no batch is left in an error state.
Stream continuous changes and handle deletes
After the snapshot, PeerDB reads inserts, updates and deletes from the replication slot and writes them to ClickHouse. Updates land as new row versions, and ReplacingMergeTree keeps the latest one, so queries that need the current state must use FINAL or a deduplicating view until merges catch up. Deletes need an explicit design, such as a deleted flag column or a tombstone pattern, because a row removed in PostgreSQL is not automatically removed from the merge result. The step is done when a test change made on the source appears in ClickHouse with the expected final state.
Validate row counts and sampled results
The team compares row counts per table between PostgreSQL and ClickHouse, accounting for rows still awaiting merge, and checks deduplicated counts against source counts for tables with heavy updates. They then compare a sample of keys across both systems, including recently changed rows and rows that were deleted at the source. Any mismatch is traced to a snapshot gap, a type mapping issue or a delete rule before cutover planning continues. The step is done when agreed tolerances are met on several consecutive checks and the mismatch log is empty or explained.
Run reports on both systems in parallel
The existing PostgreSQL reports and the equivalent ClickHouse queries run side by side for a period the team agrees in advance. Analysts or report owners compare outputs for each report, including totals, time-zone boundaries, null handling and join results, since query rewrites are a common source of drift. Record each discrepancy with its root cause and fix. The step is done when every production report has matching output accepted by its owner, and performance on ClickHouse is measured against the requirement.
Cut over analytics clients to ClickHouse
Dashboards, BI tools and scheduled jobs are repointed to ClickHouse one group at a time, with credentials scoped through ClickHouse users, roles and row policies rather than the default user. Keep the PostgreSQL path readable during this stage so a client can be switched back if a regression appears. The step is done when every in-scope client reads from ClickHouse, the access model has been reviewed, and no client still queries the old path.
Decommission the old reporting path
Once the rollback window closes, the team removes the unused reporting replicas or queries, drops the publication and replication slot that are no longer needed, and archives the final validation results. Confirm that WAL is no longer retained for the deleted slot and that ClickHouse backups cover the replicated tables under the team's backup and restore procedure. The step is done when the source shows no orphaned slots, the reporting path is documented as retired, and the backup restore test has passed on a spare environment.
How deletes and updated rows land in ReplacingMergeTree tables
Each change event that PeerDB writes to a ReplacingMergeTree target is an insert, not an in-place update, so the table holds several versions of the same source row until background merges collapse them. The version column is what decides which copy wins: the row with the highest value in the version column survives a merge. In a PeerDB-managed target, confirm which column carries the source commit order and which column is the sorting key, because the sorting key defines what counts as the same row. A common mistake is ordering by a mutable business key and then wondering why an update does not replace the original. Ask the team that owns the schema to write down the sorting key, the version column and, if used, the deleted flag column before the first snapshot is loaded.
Deletes need the same care. A source delete arrives as its own event, and unless the target models it, the old row stays visible to queries. Two patterns are common. A soft-delete flag column keeps the tombstone so queries can filter it out, and it also lets you answer audit questions about when a row disappeared. The alternative is to physically remove rows, which fits a stricter erasure position but depends on how the replication tool and the target engine handle deletes in your PeerDB version, so confirm that behavior in a test environment. Until merges complete, a plain SELECT may return superseded versions, so either query with FINAL (accepting its cost on large tables), or write deduplication logic that picks the latest version per key and skips rows flagged as deleted.
Retention and erasure decisions belong before go-live, not after. Deleted source rows may still sit in old parts, in backups and in replay logs, so an erasure request that is complete in PostgreSQL may not be complete in ClickHouse. Decide with your privacy and legal owners how long tombstones are kept, which backups must be purged, and how an erasure is verified in both systems. Whatever query pattern you choose, validate it against source counts and known updated and deleted keys, and repeat that validation after a merge-heavy period. Do not treat deduplication logic as correct until a set of changed rows has been traced end to end.
What drives the effort and duration of a PostgreSQL CDC project
Effort in a PostgreSQL to ClickHouse CDC project comes from how much data moves, how fast it changes, and how much downstream work depends on it. The cards below separate the one-time build from the recurring operation so you can ask a partner, or your own team, for a quote that covers both.
Table count and data volume
The number of tables, their total size and their largest partitions set the initial snapshot workload and the time to reach a consistent first copy. Each table also needs a target definition, ordering key and type check, so a schema with many small tables can cost as much design time as a few very large ones. Ask for a table inventory with row counts and sizes before scoping, and confirm which tables are truly needed in ClickHouse.
Write rate and update or delete share
Sustained insert, update and delete rates decide how far the target can fall behind and how much merge work ClickHouse performs. A high share of updates and deletes means ReplacingMergeTree or similar handling must be validated against real change patterns, not just row counts. Measure write rates over a representative business cycle, including batch jobs, and record the update and delete share per table.
Schema changes during replication
Columns added, renamed or retyped in PostgreSQL while replication runs can stop the pipeline or silently diverge the target. Each change needs a documented procedure covering the source migration, the ClickHouse target change and a replay check. Agree with application teams who owns DDL on replicated tables and how changes are announced before the project starts.
Data type mapping
PostgreSQL types such as numeric precision, arrays, JSON, enums, timestamps with time zones and domains do not map one to one onto ClickHouse column types. Each mapping choice affects storage, query results and the validation effort needed to prove that values match. Build a mapping table for every replicated column and review nullable, default and precision behavior with the people who own the reports.
Required data freshness
How current the ClickHouse data must be sets the replication design and the monitoring you need. Near-real-time dashboards, hourly reporting and end-of-day reconciliation each lead to different lag alerts, reconciliation jobs and operator runbooks. Write the freshness target per dataset and the acceptable lag during an incident, because that number drives both build and on-call effort.
Downstream queries and consumers
Every report, dashboard, API and export that reads from PostgreSQL today is a candidate for rewriting against ClickHouse. The count of these consumers and the complexity of their joins and functions often outweighs the replication work itself. Inventory each consumer, note which ones need result parity checks, and agree the acceptance criteria before cutover.
Security and network setup
TLS between PeerDB, PostgreSQL and ClickHouse, a dedicated replication role with the privileges it needs, and firewall rules for the source and target networks are one-time tasks with ongoing reviews. Access to the logical replication slot must be controlled, because an idle or lagging slot can hold back WAL on the source. Confirm credential storage, certificate rotation ownership and who may change replication settings.
Questions teams ask before replicating PostgreSQL to ClickHouse
What does it cost to replicate PostgreSQL to ClickHouse with PeerDB?
The cost is mostly people time and test infrastructure, not licenses. PostgreSQL and ClickHouse server are open source, and PeerDB's licensing terms should be confirmed for your deployment before you commit. Cost rises with the number of tables, the volume of changes per second, the number of pipelines, the complexity of your schema, and how many environments you must run for testing. Ask any partner for a fixed-scope quote that lists the source tables in scope, the target cluster, the delete handling method, the cutover approach and the verification steps, along with which items are one-time work and which are recurring support.
How long does a PostgreSQL to ClickHouse CDC project take?
Duration depends on how many tables you replicate, how clean the source schemas are, whether a test cluster already exists, and how long your team needs to validate query results against the source. The largest variable is usually validation, not the replication setup itself. Your schedule should also allow time for the initial snapshot of your largest tables and for a parallel run where dashboards query both systems. No duration figure is given here because it depends on your environment. Ask for a timeline with milestones in the written scope, and confirm the snapshot window against your data volume on the test cluster.
What must our team provide before the replication project starts?
Plan to provide four things before work begins:
- Read access and a replication-capable role on the source PostgreSQL databases, with
wal_levelset to logical so logical replication is possible. - A test ClickHouse cluster sized close enough to production to exercise the snapshot and the query workload.
- A named owner for each source schema who approves column changes, type changes and new tables before they reach the pipeline.
- Network paths between PeerDB, PostgreSQL and ClickHouse, plus a window for cutover.
Confirm the exact PostgreSQL and PeerDB version compatibility in your environment with the vendor before you start.
Can our own engineers replicate PostgreSQL to ClickHouse without a partner?
Yes, if you have engineers who already run PostgreSQL and ClickHouse in production and can own replication slots, WAL retention and table design. The hard parts are not the first copy. They are handling deletes and updates correctly with ReplacingMergeTree, watching replication slot lag so the source disk does not fill, and validating results over time. A partner can take on the design, build and validation work under a written scope, while your team keeps ownership of schema changes and on-call operations. Whichever path you choose, decide the owner for slot monitoring before go-live.
Is MaterializedPostgreSQL production ready?
Treat MaterializedPostgreSQL as experimental and do not base a production analytics platform on it. ClickHouse's own documentation labels the database engine as experimental, which is the main reason CDC tools such as PeerDB are used for production replication. Test any candidate pipeline against your own failure scenarios, including source restarts, schema changes and replica resyncs, before you decide. Confirm the current status in the ClickHouse documentation for your release, since engine status can change between versions.
How do backups and PostgreSQL upgrades affect the replication pipeline?
Backups of PostgreSQL and ClickHouse are independent, and both matter. ClickHouse BACKUP and RESTORE protect the analytical copy, but replication itself does not protect against a bad delete or corruption, so restore tests on a spare cluster are required. On the PostgreSQL side, a major upgrade changes how the source behaves for logical replication, so the pipeline must be planned into the upgrade. Logical replication can work between PostgreSQL major versions, but slots, publications and the PeerDB connector must be verified against the target version before the upgrade window. Ask for a runbook that covers slot handling during the upgrade and a restore test that proves the ClickHouse side can be rebuilt.
Does DeployOpen handle compliance evidence for this pipeline?
DeployOpen prepares systems, controls and evidence for compliance assessments, including access configuration, retention settings, backup records and restore test results for the replication path. It does not issue audit reports, attestations or certifications. Independent auditors and certification bodies perform those assessments. Ask for the evidence list in the written scope before work begins, so your auditor receives what it needs.
What the team keeps, changes and must still own after go-live
After go-live, PostgreSQL remains the system of record and ClickHouse becomes the analytics store. That split decides who owns what. Application writes, transactions, and business rules stay in PostgreSQL. The replicated copy in ClickHouse is derived data, so a wrong answer there is fixed by correcting the source or re-syncing the target, not by editing ClickHouse rows by hand. Your team keeps ownership of the PostgreSQL logical replication setup, including the publication, the replication slot and the role PeerDB uses to read changes, and it changes its runbooks to treat the ClickHouse tables as a downstream consumer.
The pipeline itself needs continuous ownership. Monitor replication lag and the replication slot on the PostgreSQL side, because an unconsumed slot keeps WAL on the primary and can fill its disk. Watch the PeerDB mirror status and the ClickHouse insert and merge health on the target side. Define a schema-change procedure before the first change arrives: a column added or altered in PostgreSQL must be reviewed for its ClickHouse type, its ReplacingMergeTree ordering key and version column, and the mirror configuration. Confirm with the PeerDB documentation how each DDL change is propagated for your version, and test that step in a pre-production environment first.
Backups and restore tests belong to the ClickHouse side. Replication on ClickHouse protects against node loss, but the ClickHouse backup documentation notes that replication does not protect against human error or corruption, so schedule BACKUP to storage you control and test RESTORE on a spare cluster on a regular basis. Plan the PostgreSQL upgrade separately: PostgreSQL supports logical replication between major versions, which the upgrade documentation describes as allowing a major upgrade with only several seconds of downtime. Before you start, confirm that your PeerDB version supports the source and target versions you plan to use, and rehearse the cutover of the replication path, including what happens to the slot and the mirror during the switch.
How DeployOpen works on this
DeployOpen plans and builds the PeerDB pipeline, the ClickHouse target tables and the checks that prove the replica matches the source, all inside infrastructure you control. The written scope fixes which databases, tables, ClickHouse cluster and network paths are in play before any work starts.
How we scope
- We inventory the PostgreSQL version, the tables to replicate, their primary keys and how often rows are updated or deleted.
- We confirm whether the source allows logical replication and who may create replication slots and publications.
- We agree in writing which ClickHouse cluster, PeerDB deployment and network paths are in scope, and what is excluded.
How we build
- We deploy PeerDB alongside your ClickHouse cluster and run the initial snapshot on a non-production copy first.
- We design ReplacingMergeTree target tables with ordering keys and version columns that match your update and delete patterns.
- We configure replication slot monitoring so WAL growth on the source is watched and alerted from the first day of sync.
How we hand over
- We document the replication topology, slot names, table mappings, credentials handling and the runbook for pausing or resyncing a table.
- We run row count and sampled checksum comparisons between PostgreSQL and ClickHouse and record the results for your team.
- We test a restore of the ClickHouse data from a BACKUP on a spare cluster and document the procedure and its timing.
How we support
- Ongoing support, upgrades and continuous testing are available as a retainer, scoped separately from the migration.
- We can review PostgreSQL and ClickHouse version changes, including the PostgreSQL minor release cycle, before they reach the replicated database.
- We can rerun reconciliation after schema changes on the source, so drift is caught before analysts notice wrong numbers.
