Migrating from PostgreSQL to ClickHouse

This migration is different from the others in this series. Almost nobody replaces PostgreSQL with ClickHouse -- they offload to it. PostgreSQL stays as the transactional system of record; ClickHouse takes over the analytical queries that were melting your read replicas. ClickHouse itself pushes this "Postgres for OLTP, ClickHouse for OLAP" pairing as the default architecture, and it's the honest framing: ClickHouse has no row-level locking for transactional workloads, updates and deletes are asynchronous background mutations, and single-row lookups are not what MergeTree is built for.

So the first question isn't "how" but "should you": if your dashboards run fine on PostgreSQL with good indexes and a few materialized views, stay. Move when aggregation scans over tens of millions of rows dominate query time, when materialized view refreshes can't keep up, or when BI traffic is degrading your OLTP latency. For the underlying engine differences, see PostgreSQL vs ClickHouse.

The Three Realistic Paths

1. One-time copy with the postgresql() table function

Built into ClickHouse, no extra infrastructure. Create the target table, then pull:

CREATE TABLE events
(
    id UInt64,
    user_id UInt64,
    event_type LowCardinality(String),
    payload String,
    created_at DateTime64(6, 'UTC')
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(created_at)
ORDER BY (event_type, created_at);
 
INSERT INTO events
SELECT id, user_id, event_type, payload, created_at
FROM postgresql('pg-host:5432', 'appdb', 'events', 'readonly_user', 'secret');

This streams the full table over one connection. It's the right tool for backfills, snapshots, and datasets that fit an overnight window. For repeated incremental pulls (e.g. WHERE created_at > ... on a schedule), the PostgreSQL table engine gives you a persistent proxy table to select from.

2. Continuous replication with CDC

For a living pipeline -- PostgreSQL keeps taking writes, ClickHouse stays current -- you want change data capture from the WAL:

• ClickPipes Postgres CDC (ClickHouse Cloud): generally available as of 2026. This is PeerDB, which ClickHouse acquired in 2024, integrated into Cloud. Parallel initial snapshot, then continuous WAL streaming. A few clicks if you're on Cloud.
• Self-hosted PeerDB (open source, v0.37.1 as of July 2026): the same engine, run via Docker Compose against your own ClickHouse. Source setup is standard logical replication: wal_level = logical, a publication, and a replication slot. Mind the slot if the pipeline stops -- an abandoned replication slot makes PostgreSQL retain WAL indefinitely.

Both land rows in ReplacingMergeTree tables with metadata columns (_peerdb_version, _peerdb_is_deleted). That has a query-side consequence covered under Validation below.

3. MaterializedPostgreSQL -- know it exists, don't build on it

ClickHouse ships a MaterializedPostgreSQL database engine that consumes logical replication directly. It is still flagged experimental (requires allow_experimental_database_materialized_postgresql = 1) and has a history of edge-case issues. Fine for a lab; use PeerDB/ClickPipes for anything you'd page someone about.

Data Type Mapping

Two blanket rules. First, resist Nullable(T) everywhere: it costs a separate bitmap per column and blocks some optimizations. If NULL doesn't carry meaning, use the type's default (empty string, 0) instead. Second, right-size integers -- see choosing data types.

The Schema Redesign Is the Real Work

Copying columns is mechanical. What actually needs thought:

• ORDER BY is a sort key, not a primary key. ClickHouse does not enforce uniqueness -- inserting the same id twice gives you two rows. Design ORDER BY for your query filters (low-cardinality first, then time), not for identity. If you need upsert semantics, that's ReplacingMergeTree with eventual deduplication -- see MergeTree engines.
• Foreign keys don't exist. Denormalize into wide tables or use dictionaries for lookup joins.
• Updates and deletes change shape. ALTER TABLE ... UPDATE rewrites whole parts asynchronously. If rows change often in PostgreSQL, replicate via CDC into ReplacingMergeTree rather than trying to mutate -- see updating and deleting data.
• Batch your inserts. Per-row inserts create one part each and you will hit TOO_MANY_PARTS within hours. Thousands of rows per insert, or async_insert = 1.

Query Changes

ILIKE, || concatenation, and CTEs work as expected (CTEs are inlined by default, not materialized -- see ClickHouse CTEs).

Validation

Run the same aggregates on both sides before trusting the new system:

-- Both engines
SELECT count(*), sum(amount), min(created_at), max(created_at) FROM events;

One trap specific to CDC pipelines: tables fed by PeerDB/ClickPipes are ReplacingMergeTree, and deduplication happens at merge time, not insert time. A bare count(*) can be higher than PostgreSQL's until merges settle. Compare with FINAL:

SELECT count(*) FROM events FINAL;

Common Mistakes

Re-pointing dashboards and services: the ClickHouse connection guides cover drivers per language.

Mako connects to PostgreSQL and ClickHouse side-by-side with AI-powered autocomplete -- useful for running validation queries against both ends of a migration. Try it free at mako.ai.