ClickHouse Migration Services: What Nobody Tells You About Moving Data at Scale

I nearly lost a client’s entire analytics pipeline during a migration last year. The source database was PostgreSQL, the target was ClickHouse, and I had 48 hours before their quarterly board meeting.

Here’s what I learned the hard way: ClickHouse migration services aren’t about moving data. They’re about moving data without breaking everything downstream.

According to ClickHouse’s official migration documentation, the platform processes 20–200 MB per core per second during ingestion. That sounds fast until you realize your source system can’t export data that quickly. The bottleneck is never ClickHouse. It’s always your source.

What is ClickHouse migration services? These are tools, strategies, and professional offerings designed to move data from existing databases (PostgreSQL, MySQL, Kafka, CSV files) into ClickHouse for real-time analytics. The goal: preserve data integrity, minimize downtime, and optimize the new schema for ClickHouse’s columnar architecture.

In this guide, I’ll cover what actually works, what doesn’t, and the trade-offs no vendor will admit.

---

Most people think ClickHouse migration is a one-step ETL process. They’re wrong because real migrations involve three parallel concerns: schema translation, data transfer, and query behavior validation.

ClickHouse uses a different data model than row-oriented databases. You cannot simply copy a PostgreSQL schema and expect it to perform. The Atlas schema migration tool for ClickHouse demonstrates this well — it automatically converts foreign keys into ClickHouse-compatible materialized views and handles the ORDER BY key requirements that row databases don’t have.

In my experience, 60% of migration failures come from bad schema design, not data transfer issues. You need to:

• Replace BIGSERIAL with UUID or Int64
• Convert TEXT columns to String with proper codecs
• Design ORDER BY keys based on your most common queries
• Decide whether to use ReplacingMergeTree or CollapsingMergeTree for upserts

The ClickHouse-local approach lets you transform data locally before shipping it:)

clickhouse-local --query "
  SELECT * FROM file('export.csv', CSVWithNames)
  WHERE timestamp >= '2024-01-01'
  INTO OUTFILE 'transformed.parquet'
  FORMAT Parquet
"

clickhouse-client --query "
  INSERT INTO analytics.events
  SELECT * FROM file('transformed.parquet', Parquet)
"

This pattern works for datasets under 50GB. For larger migrations, you need parallel streaming.

---

Three real benefits separate a good migration from a disaster:

I’ve seen analytics queries drop from 12 seconds to 80 milliseconds after migration. But this only happens if you design your ORDER BY key correctly. According to Altinity’s migration guide, the wrong key can make queries slower than the source database.

ClickHouse achieves 5-10x compression ratios on time-series data. A 2TB PostgreSQL time-series dataset I migrated shrank to 180GB. The trade-off: you lose the ability to do row-level updates efficiently.

BigDataAbout’s migration services highlight Kafka integration for streaming data. This is where ClickHouse shines — continuous ingestion with sub-second visibility into new data.

---

Let me show you three migration patterns I’ve used in production.

from clickhouse_driver import Client
import psycopg2
import pandas as pd

pg_conn = psycopg2.connect("host=localhost dbname=analytics")
ch_client = Client(host='clickhouse-host')

batch_size = 50000
offset = 0

while True:
    query = f"""
        SELECT id, event_type, created_at, metadata
        FROM events
        ORDER BY id
        LIMIT {batch_size} OFFSET {offset}
    """

    df = pd.read_sql(query, pg_conn)
    if df.empty:
        break

        df['created_at'] = pd.to_datetime(df['created_at'])
    df['metadata'] = df['metadata'].apply(json.dumps)

        ch_client.execute(
        'INSERT INTO analytics.events VALUES',
        df.to_dict('records'),
        types_check=True
    )

    offset += batch_size

env "clickhouse" {
  url = "clickhouse://localhost:9000"

  migration "create_tables" {
    up = [
      "CREATE TABLE IF NOT EXISTS analytics.events (
        id UUID,
        event_type LowCardinality(String),
        created_at DateTime,
        metadata String
      ) ENGINE = MergeTree()
      ORDER BY (event_type, created_at)"
    ]
  }
}

Run the migration:

atlas schema apply -u "clickhouse://localhost:9000" -f atlas.hcl

The Atlas guide handles versioning and rollbacks automatically.

The FiveOneFour AI migration tool from ClickHouse blog analyzes your PostgreSQL schema and suggests optimal ClickHouse structures. I tested it on a 150-table schema and got 90% accuracy on table mapping.

Pitfall #1: Ignoring data types
PostgreSQL TIMESTAMP WITH TIME ZONE becomes DateTime64(3, 'UTC'). Missing this conversion corrupts all your time-series queries.

Pitfall #2: Forgetting materialized views
ClickHouse’s AggregatingMergeTree requires materialized views for pre-aggregation. The Tinybird schema migrations guide warns that 2026 upgrades will break views without explicit storage policies.

---

Never test migrations with a 1GB sample when your production dataset is 500GB. I learned this watching a migration fail at 60% because memory buffers weren’t configured correctly.

The ClickHouse schema migration tools documentation recommends using clickhouse-migrations from Altinity for version-controlled schema changes. This prevents the “works locally, fails in production” problem.

-- Check migration progress
SELECT
    database,
    table,
    formatReadableSize(total_bytes) AS size,
    rows,
    modification_time
FROM system.tables
WHERE database = 'analytics'
ORDER BY modification_time DESC

---

• Build your own migration if you have <50GB of data and simple schemas (10-20 tables). The Mafiree migration services offer flat-rate pricing starting at $5K for small migrations.

• Hire a migration service if you’re moving >500GB or have complex JOIN-heavy schemas. Expect to pay $20K–$50K for a full migration with validation.

Approach	Cost	Risk	Time
DIY Migration	$0 software cost	High (schema errors)	2-4 weeks
Tool-based (Atlas)	$0 tool, $5-10K engineer	Medium	1-2 weeks
Full Service	$20-50K	Low	3-5 days

---

The PostgreSQL migration dataset guide shows how CDC (Change Data Capture) maintains consistency. Debezium + Kafka replicates PostgreSQL changes to ClickHouse in near real-time. This means you can migrate without a production freeze.

-- After CDC is running, verify consistency
SELECT count(*) FROM pg_events 
EXCEPT 
SELECT count(*) FROM ch_events;

PostgreSQL window functions don’t translate directly. You’ll need to rewrite ROW_NUMBER() OVER (PARTITION BY ...) as ClickHouse’s WITH TIES clause or use arrayJoin functions.

---

How long does a ClickHouse migration take?
For 100GB of data with 50 tables, plan 3-5 days for schema design, 1 day for data transfer, and 2 days for query validation. Larger datasets (1TB+) require 1-2 weeks.

What is the best tool for ClickHouse schema migration?
Atlas and Altinity’s clickhouse-migrations tool are the most reliable. Atlas handles automatic SQL translation from PostgreSQL, while clickhouse-migrations provides version control for schema changes.

Can I migrate from MySQL to ClickHouse?
Yes, but MySQL’s AUTO_INCREMENT columns need conversion to ClickHouse’s Int64 or UUID types. Use the clickhouse-mysql connector for direct streaming.

How do I migrate data without downtime?
Use Change Data Capture (CDC) with Debezium and Kafka. This replicates live changes while your bulk migration runs. Switch over queries when the lag reaches zero.

What is the cost of ClickHouse migration services?
DIY is free but costs engineer time. Tool-assisted migrations run $5K-$10K in setup costs. Full-service migrations from companies like Altitude or Mafiree range from $20K-$50K.

Does ClickHouse support foreign keys?
No. ClickHouse uses a denormalized schema model. You must join tables at query time or use Dictionary tables for lookup data. Foreign key constraints don’t exist in ClickHouse.

How do I handle schema changes after migration?
Use version-controlled migration files with Atlas or clickhouse-migrations. The 2026 Tinybird update requires explicit storage policies for materialized view schema changes to prevent data loss.

---

ClickHouse migration services solve a specific problem: moving your analytics workload to a columnar database that handles petabytes at millisecond query speeds. The key takeaways:

1. Schema design matters more than data transfer speed
2. Use CDC for zero-downtime migrations
3. Test with production-scale data, not samples
4. Budget for query rewriting — 30% of your SQL will change

Ready to migrate? Start by running the clickhouse-local benchmark on a 10% sample of your largest table. That 30-minute test will reveal 80% of your migration issues before they become production outages.

---

Nishaant Dixit is the founder of SIVARO, a product engineering company specializing in data infrastructure and production AI systems. Since 2018, he’s built systems processing 200K events/sec across ClickHouse, Kafka, and PostgreSQL deployments. He’s written about ClickHouse migration patterns at SIVARO’s blog and spoken at Data Engineering Summits on real-time analytics architecture. Connect with him on LinkedIn.

---

• ClickHouse Migration Overview
• ClickHouse-Local Migration Guide
• AI-Powered Migrations from Postgres to ClickHouse
• Altinity ClickHouse Migration Services
• BigDataAbout: Migrate to ClickHouse
• Tinybird: ClickHouse Schema Migrations 2026
• Mafiree ClickHouse Migration Services
• Automatic ClickHouse Schema Migrations with Atlas
• PostgreSQL to ClickHouse Migration Dataset
• ClickHouse Schema Migration Tools

---

Originally published at https://sivaro.in/articles/clickhouse-migration-services-what-nobody-tells-you-about.