ClickHouse Migration Services: Postgres, Elasticsearch and Warehouses

Where the data comes from decides the route

ClickHouse's own migration guides cover PostgreSQL, BigQuery, Snowflake, Elasticsearch and Redshift, with remoteSecure, backup and restore, clickhouse-local, ETL tools and object storage as other routes. The tools move rows. What differs by source is what has to be redesigned.

Why a lift-and-shift schema fails, and what to design instead

ClickHouse stores a table as parts sorted by the ORDER BY key and reads it through a sparse primary index over blocks of 8,192 rows, called granules. The key you choose decides which queries skip data and which scan it. ORDER BY also sets the primary key unless you say otherwise. Copy a relational schema and the key becomes an afterthought, which is how a fast database ends up with slow dashboards.

• Sorting key — Chosen from the filters your queries use, with the columns you filter by most and cardinality in mind. A long key costs insert speed and memory, per the MergeTree documentation. Adding a new column to both the table and the key is lightweight, and a different key generally means a new table and a reload, so it is decided before the load.
• Partitioning — Most tables need none. ClickHouse's documentation says partitioning does not speed up queries the way the sorting key does and recommends nothing finer than monthly when it is used. Partitioning by customer or tenant is the usual mistake.
• Updates and deletes — Source rows change, and ClickHouse prefers appending. CDC pipes to ClickHouse default to ReplacingMergeTree, so reads need FINAL and a filter on the deleted flag, or a design where the question is asked of an aggregate. We pick the model per table, with the query cost in view.
• Types — Timestamps with sub-second precision, decimals, low-cardinality strings, enums and nullable columns each have a cost or a trap. The type map is written down before data moves, as the validation step compares against it.
• Joins and wide tables — Large multi-table joins that were cheap in a row store can be expensive in ClickHouse. Denormalizing into a wide table, or precomputing in a materialized view, is a design decision per query, not a default.
• Approximate functions — ClickHouse offers fast approximate aggregates such as uniq, alongside exact ones such as uniqExact. Where the source returned an exact distinct count, the choice is stated and the validation tolerance with it.

Keeping the data in sync during the move

A one-time bulk load is enough when the source can be frozen for the copy, or when the history is loaded once and only new events arrive afterwards. When the source keeps changing, you need change data capture.

For PostgreSQL, ClickHouse's documentation describes CDC through ClickPipes for ClickHouse Cloud and through PeerDB for self-managed ClickHouse as the most efficient route for most migrations. ClickPipes takes an initial snapshot and then replicates continuously, with a default sync interval of 60 seconds. The source needs logical replication enabled and a replication user. Poolers and proxies such as PgBouncer, RDS Proxy and Supabase Pooler are not supported in the CDC path, so the connection goes to the database host itself. A pipe must be paused to be updated, and only some settings can change after creation, which is why settings are chosen on the rehearsal.

When a Kafka topic already carries the events, ingesting from the topic is usually cleaner than copying from the source database, because the same stream can feed the old and new platforms during the parallel run. The same applies to Debezium topics from a relational source.

Self-managed or ClickHouse Cloud, and which version to land on

For self-managed estates the target version matters. ClickHouse versions are numbered by year and month, a stable release comes about monthly, the three latest stable releases receive backported fixes, and an LTS is released twice a year and supported for a year. As of 4 November 2026 the LTS lines are 26.3 and 26.8, and the latest release is 26.10. A migration target is chosen on that rule, with the upgrade cadence agreed before go-live rather than after the first release falls out of support.

ClickHouse Cloud plan and pricing questions are answered from the vendor's own pages during the assessment. AceMQ is independent of ClickHouse Inc. and does not resell the service.

Who this migration service is not for

• Transactional workloads — ClickHouse is a columnar analytical database. Moving an OLTP workload to it is the wrong project.
• Small data on a healthy PostgreSQL — If the analytical queries are modest and the database copes, indexes, partitioning or a read replica may be all you need. We say so after the assessment.
• Search with relevance ranking — If the Elasticsearch workload depends on scoring, fuzzy matching and highlighting, ClickHouse is not a replacement. A search engine, or a split of the workload, is a better answer.
• A data copy only — If the schema is simple, the route is documented and your engineers have time, ClickHouse's own migration guides are enough.
• A fixed price and date before assessment — The workloads and queries decide the effort. We scope after the assessment and a proof on one workload, and quotes come back within 1 hour of getting your source, data volume and query shapes.