How do you migrate from Elasticsearch or PostgreSQL to ClickHouse for analytics workloads?

Think of moving from a small city apartment (PostgreSQL) or a flexible co-working space (Elasticsearch) to a purpose-built warehouse (ClickHouse). You cannot just move your furniture as-is. The apartment had rooms for different purposes (normalized tables) that you need to consolidate into one open floor plan (denormalized wide table). The co-working space had flexible desk arrangements (schema-on-read) that you need to commit to a fixed layout (schema-on-write). The reorganization effort pays off in operational efficiency.

The data model transformation is the most critical step. PostgreSQL analytics typically involve multiple normalized tables with JOINs (users JOIN orders JOIN products). ClickHouse performs best with wide denormalized tables where all dimensions are pre-joined at insert time. Instead of users and orders tables with a foreign key relationship, create a single orders_analytics table that includes user_name, user_country, product_category inline. For Elasticsearch migrations, nested JSON objects must be flattened into typed columns, and full-text search fields need bloom filter indexes (tokenbf_v1) for approximate substring search capability.

Internally, the migration pipeline has two phases: historical backfill and ongoing real-time sync. For PostgreSQL, use clickhouse-local or INSERT INTO...SELECT FROM postgresql() table function for the initial bulk load, then set up Debezium CDC to capture ongoing INSERT/UPDATE/DELETE events as a Kafka stream consumed into ClickHouse with ReplacingMergeTree for mutable dimensions. For Elasticsearch, use elasticdump or scrolled queries to extract historical data in batches, then redirect the application's indexing pipeline to write to both Elasticsearch and ClickHouse during a transition period.

At production scale, the query rewrite layer is often the longest-running workstream. Elasticsearch aggregations (terms, date_histogram, percentiles) map to ClickHouse GROUP BY with toStartOfHour and quantile functions. PostgreSQL window functions (ROW_NUMBER, LAG, LEAD) work in ClickHouse with identical syntax. However, PostgreSQL CTEs that depend on previous CTE results must often be restructured as subqueries because ClickHouse CTEs are substituted inline (not materialized). JOINs in ClickHouse prefer the smaller table on the right side and use hash join by default, so query patterns from PostgreSQL that JOIN large tables may need restructuring or pre-materialization.

The non-obvious gotcha is that migration is not all-or-nothing. The most successful migrations follow a gradual pattern: keep the source system running, dual-write to ClickHouse, build new dashboards against ClickHouse while keeping existing dashboards on the old system, and only decommission the old system after 2-4 weeks of validation. Teams that attempt a big-bang cutover frequently discover data quality issues, missing query patterns, or performance edge cases that would have been caught in a parallel-run phase. Also, ClickHouse's eventual consistency (ReplacingMergeTree dedup timing, materialized view processing) requires adjusting expectations from users accustomed to PostgreSQL's immediate consistency.