How to Migrate from Postgres to ClickHouse

Why migrate from PostgreSQL to ClickHouse?

ClickHouse delivers sub-second analytics over billions of rows thanks to columnar storage and vectorized execution. Moving large, read-heavy datasets out of Postgres removes VACUUM overhead and frees your OLTP database for transactional traffic.

Which migration methods work best?

For one-off moves, use COPY ... TO STDOUT in Postgres and clickhouse-client --query "INSERT ... FORMAT CSV". For ongoing syncs, rely on MaterializedPostgreSQL engine or Debezium CDC into Kafka.

Step 1 — Export data from Postgres with COPY

Run COPY in parallel per table to a local CSV or to STDOUT piped into gzip. Text and CSV formats are fastest; include HEADER for clarity.

Example

COPY Customers TO '/tmp/customers.csv' WITH (FORMAT csv, HEADER true);COPY Orders TO '/tmp/orders.csv' WITH (FORMAT csv, HEADER true);

Step 2 — Create equivalent tables in ClickHouse

Pick a MergeTree engine keyed by frequently-filtered columns. Data types must match Postgres originals; replace serial with UInt32/UInt64 and timestamp with DateTime.

Example

CREATE TABLE Customers ( id UInt32, name String, email String, created_at DateTime) ENGINE = MergeTreeORDER BY id;

Step 3 — Load the exported files

Use clickhouse-client with --query "INSERT INTO … FORMAT CSV". Add --max_insert_block_size to tune batch size for large loads.

Example

clickhouse-client --query "INSERT INTO Customers FORMAT CSV" < /tmp/customers.csvclickhouse-client --query "INSERT INTO Orders FORMAT CSV" < /tmp/orders.csv

Step 4 — Validate row counts

Compare counts in both systems before unlocking production. Use SELECT count(*) on ClickHouse and Postgres. Mismatches usually mean NULL text fields or delimiter issues.

How do I handle incremental sync?

Create a MaterializedPostgreSQL database to stream changes into ClickHouse automatically. Alternatively, capture WAL changes with Debezium → Kafka → ClickHouse Kafka engine.

Best practices for a smooth cut-over

Disable foreign keys in Postgres exports, cast all dates to UTC, compress files with gzip for network transfers, and load smallest dimension tables first to support early query tests.

What performance gains can I expect?

Dashboards that scanned millions of Postgres rows typically drop to milliseconds in ClickHouse. Storage footprint shrinks 3-8× with default compression.