ClickHouse migration from Elasticsearch or PostgreSQL

What a ClickHouse migration involves

Most moves to ClickHouse start with one of four pressures: log or event volumes that have outgrown an Elasticsearch cluster, analytical queries that have slowed to unacceptable times on PostgreSQL, cost or latency problems on a cloud data warehouse, or a decision to move between ClickHouse Cloud and a self-hosted deployment. Each trigger points to a different source system, so the first task is to write down which one applies, which workloads depend on it, and which of those workloads must keep running during cutover.

The source cannot simply be copied. Query syntax and functions differ from Elasticsearch Query DSL, PostgreSQL SQL and warehouse dialects, so every dashboard, alert and report needs a rewrite and a result comparison. Schemas must be redesigned around MergeTree table engines, partitioning and sort keys chosen for the dominant query filters, rather than mirroring normalized or document-shaped source tables. Ingestion changes from row-level writes or index updates to batched inserts, and retention moves into table-level TTL rules that need to match what the business has agreed to keep.

Teams with prior columnar or ClickHouse experience can often run the project in-house, provided they have a pre-production environment with representative data and queries. Teams without that experience usually benefit from a partner for schema and sort key design, query porting and a parallel run in which old and new results are compared before cutover. Ask any partner to show how it will reconcile row counts, aggregates and sampled query results between systems, and what it will hand over so your own staff can operate the result.

What the work includes

The migration runs as eight workstreams, from inventory through cutover and decommissioning, and closes with a set of deliverables your team keeps. The scope of each workstream is agreed in writing before work starts.

Workload inventory and query catalog

We list every index, table, model, dashboard, alert and scheduled query that reads from or writes to the source, with owners, refresh patterns and latency expectations. The catalog defines what must match after cutover. Check that it includes queries run by people and tools outside the data team, such as BI extracts and ad hoc notebooks.

Target schema design on ReplicatedMergeTree

We design ClickHouse tables on ReplicatedMergeTree for your cluster topology, choosing sort keys, partitioning and TTL rules from the query catalog rather than from the source layout. Replication is coordinated by ClickHouse Keeper, so the design also covers how many Keeper nodes and hosts your environment needs. Confirm the planned layout against your own failure domains.

Schema translation from source models

We translate Elasticsearch mappings, PostgreSQL tables or warehouse models into ClickHouse column types, nullability, nested structures and materialized columns. Differences in text analysis, null handling and timestamp precision are recorded as explicit decisions. Review these decisions with the owners of downstream reports, since they change query results.

Ingestion and historical backfill

We build the pipelines that load new events and the historical backfill, then run the backfill as a repeatable procedure with checkpoints, partition-level retries and row counts per partition. Plan the load window and source read impact with the team that owns the source system. Ask for the backfill procedure to be tested on a representative subset before full volume.

Query rewrites and result validation

We rewrite each cataloged query into ClickHouse SQL and compare its results with the source output on agreed samples and date ranges. Differences in aggregation, ordering, rounding and time zones are logged with the reason and the accepted outcome. Each query's validation result is recorded in the migration log.

Dual running and reconciliation

Both systems receive production traffic for an agreed period, and we reconcile row counts, aggregates and sampled records between them each cycle. Discrepancies are assigned to a cause and fixed before cutover is scheduled. Confirm with your owners how long dual running must last and who signs off the reconciliation results.

Dashboards, alerts and cutover runbook

We re-point dashboards, alert rules and API consumers to ClickHouse and write a cutover runbook with go and rollback criteria, step order, owners and communication steps. Alerts on ingestion lag and replica health are added so the new platform is watched from its first day. Ask who is authorized to call a rollback during the cutover window.

Restore test, handover and decommissioning

We run a restore test of backups on a spare cluster, since a backup that has never been restored is unlikely to restore when needed, and then hand the platform to its owners with documentation and training. Source decommissioning follows only after the owners accept the handover and the retention period for the old data is agreed. Check that the source archive and its access are covered by your retention policy.

What drives cost and duration

This table lists the nine drivers that set the effort and duration of a ClickHouse migration, and each one is quoted from the written scope.

Driver	Why it matters	How to keep it down
Queries and dashboards to port	Each Elasticsearch query, SQL view or dashboard needs a ClickHouse equivalent and a result check.	Inventory usage first, retire unused dashboards, then group remaining queries by shared logic.
Historical data volume and retention	Backfill time and target storage grow with how much history moves and how long it is kept.	Agree retention per table and backfill only the time window consumers actually query.
Ingestion sources and change capture	Each source adds connectors, schema mapping and change capture from PostgreSQL logs or streaming pipelines.	Start with sources that feed required reports and schedule the rest in later phases.
Updates, deletes and joins in the source	Mutable rows and joins call for different table designs, denormalized tables or dictionaries, which adds design work.	Classify each dataset as append-only or mutable before design, and model joins into target tables early.
Dual-running period and reconciliation rules	Both systems run in parallel until results match agreed rules, so the length of that period drives effort.	Write row count, aggregate and sample comparison rules into the scope before a cutover date is set.
Replicas and Keeper nodes in the target	Replicated tables depend on ClickHouse Keeper, and the replication guide recommends at least three Keeper nodes in production.	Confirm replica count from availability needs and place Keeper on dedicated hosts, as the guide advises.
Compliance and evidence requirements	Audit trails, access reviews, encryption settings and backup restore test records add build and documentation work.	Name required controls and evidence formats in the scope and reuse existing audit templates where your assessor accepts them.
Teams and consumers that sign off	Each owning team must test and approve its dashboards, pipelines or reports, which adds review rounds.	List approvers and their cutover windows up front, and group consumers by shared data products.
Source edition and deployment model	Self-managed Apache-2.0 ClickHouse, a managed service or ClickHouse Cloud change who operates, patches and bills the platform.	Decide the deployment model before design starts and ask for each option to be quoted separately in writing.

Project phases and outputs

The sequence moves a workload from its current platform to ClickHouse in checked stages, so each phase ends with an artifact the client can review and sign off before the next one starts.

Discover sources and inventory workloads
DeployOpen catalogs the existing query patterns, data volumes, retention rules, ingestion paths and downstream consumers on Elasticsearch, PostgreSQL or the cloud warehouse. The output is a workload inventory and query catalog that the client confirms, so that every later check has a defined scope.
Design the target and document the mapping
The team designs ClickHouse tables, engines, sort keys and partitioning against the inventoried queries, and translates source schemas into a documented field-by-field mapping. The output is an approved target design and mapping document, including every type change and every field that is dropped or derived.
Build a pre-production cluster on a supported version
DeployOpen builds a pre-production environment on a ClickHouse version listed as supported in the project security policy, loaded with representative data. The output is a tested build with recorded version, configuration and sizing, which the client can compare with the production plan before anything goes live.
Set up ingestion and backfill with count and checksum checks
Ingestion pipelines are configured and historical data is backfilled in controlled batches. Each batch is verified by row counts and checksums against the source, and mismatches are investigated before the next batch runs. The output is a backfill report showing that every partition reconciles to the source.
Validate query results against the source
Rewritten queries are run on both the source and ClickHouse with the same inputs, and results are compared for values, ordering, null handling and time zones. The output is a validation log of each catalogued query, with every difference either fixed or accepted in writing by the client.
Run dual-running and reconcile within agreed tolerance
Both platforms receive live data while consumers read from the source, and reconciliation jobs compare results on a schedule. The client and DeployOpen agree tolerances in advance, and the output is a reconciliation record showing that differences stay inside those tolerances before cutover is approved.
Cut over with a rollback path and tested restore
A cutover runbook defines the switch steps, the rollback path and the owners of each step. Backups are restored to a spare cluster and the restored data is checked before go-live. After go-live, the source is kept for the agreed retention period and then decommissioned. The outputs are the signed runbook, the restore test record and a decommissioning record.

What teams keep in-house and what they outsource

This table shows who usually owns each piece of a ClickHouse migration, which skills the work needs, and which of those skills teams most often lack internally, so you can decide where a partner adds the most value.

Work area	Usual owner and skills needed	Scarce internally?
Query and dashboard owners	Keep in-house. Analysts and product owners who know what each metric means, which filters are business rules and which reports can be retired. Needs deep knowledge of the current Elasticsearch queries, PostgreSQL views or warehouse models.	Rarely scarce, but often overloaded during cutover
Source data owners and ingestion engineers	Keep in-house. Owners of the Elasticsearch indices, PostgreSQL tables or warehouse datasets, plus engineers who know the existing pipelines, field meanings, late-arriving data and deletes.	Ingestion skills are often scarce if pipelines are undocumented
Target schema and sort key design	Often outsourced when the team lacks columnar experience. Needs MergeTree table engine knowledge, sort key and partition choices driven by real query patterns, and data type selection for compression.	Usually scarce internally
Query translation and result validation	Shared. SQL dialect knowledge covering ClickHouse functions, differences from PostgreSQL or warehouse SQL, and replacements for Elasticsearch query DSL. Validation needs scripted row count, aggregate and sample comparisons signed off by query owners.	SQL dialect knowledge is often scarce for Elasticsearch-only teams
Backfill engineering	Often outsourced. Batch loading of history without overloading sources, idempotent reloads, partition-level retries, and throughput tuning on the target cluster.	Usually scarce internally for large historical loads
Dual-running reconciliation	Shared. Engineers run both systems in parallel and compare outputs over an agreed window. Owners of dashboards decide which differences are acceptable. Expect discrepancies from timestamp handling and null semantics.	Reconciliation tooling is often built ad hoc, so skills are moderately scarce
Cutover and rollback	Shared, with the platform team owning the switch. Requires a written rollback path back to the old system, freeze windows for writers, and DNS, connection string or client configuration changes tested in advance.	Coordination skills are often scarce across several teams
Backup and restore testing	Often outsourced when no one has restored a ClickHouse backup. Needs BACKUP and RESTORE knowledge, destination storage access, and regular restore tests on a spare cluster. Replication alone does not protect against human error.	Restore testing is commonly skipped and is scarce internally
Keeper and replication operations	Platform engineers own this after cutover. Needs ClickHouse Keeper operation on dedicated hosts, replica recovery monitoring, and identity integration for users and service accounts.	Keeper and replication operations are usually scarce internally

What goes wrong and how to prevent it

Most migration problems come from assumptions carried over from the source system. Each risk below has a check you can run before cutover.

Translated values that differ from source

Functions, NULL handling, time zone conversion and aggregation rules often differ between Elasticsearch, PostgreSQL or a cloud warehouse and ClickHouse. Timestamps and numeric fields can also change type during translation, which may cause precision loss in decimals. Prevent this with a golden query set run against both systems, comparing row counts, sums and sampled rows under written tolerances before any report is repointed.

Updates and deletes assumed to work as before

Teams often expect row-level UPDATE and DELETE to take effect immediately, as they do in PostgreSQL, or expect Elasticsearch document versioning to carry over. ClickHouse MergeTree tables handle changes differently, and the visible effect of mutations or deletes can lag behind the write. Confirm the behavior for each table engine and version, redesign change handling for the workload, and test that reads return the expected state after changes.

Sort key copied from the source key

Copying the source primary key or index into the ClickHouse ORDER BY clause often produces a layout suited to point lookups, while analytics depend on range scans and group-bys. Queries then read far more data than they need. Choose the sort key from the most frequent query patterns, filter columns and column cardinality, and measure those queries on a production-sized sample before loading everything.

Backfill and live load collide at the seam

A backfill running at full speed can starve live ingestion, and loads that overlap at the cutover point can insert duplicate rows or skip rows. Set backfill concurrency and time windows that leave headroom for live writes, and record a watermark per source table. After the load, reconcile row counts and key checksums for the boundary window in both directions.

Dual running without exit criteria or a checked version

Dual running often continues indefinitely because nobody wrote down what passing means. Define pass criteria before starting, covering agreed query results, latency targets, ingestion lag, and a fixed end date with a named owner. Run the dual period on a ClickHouse version listed as supported in the security policy, and read the production FAQ guidance on changelogs before choosing it.

Backups that were never restored

ClickHouse BACKUP and RESTORE can create full and incremental backups, but the documentation warns that a backup never test-restored will probably not restore properly when needed. Replication also does not protect against human error or data corruption. Schedule restore tests on a spare cluster, record restore time and row-count verification, and treat a failed restore as a blocker for cutover.

How the work differs by starting point

Elasticsearch migrations center on log and search workloads, where the data model is shaped by index design and query habits.

Field mappings: Translate dynamic and explicit mappings into typed columns, and decide which fields are filtered on, which are kept as semi-structured data and which are dropped.
Index lifecycle to partitions and TTL: Replace time-based indices and rollover policies with partition keys, sort keys and TTL rules that match the retention the business needs.
Query translation: Rewrite Query DSL, aggregations and full-text searches as SQL, and confirm which search behaviors, such as relevance scoring or fuzzy matching, still matter.
Client confirmation: Confirm which dashboards, alerts and API consumers depend on Elasticsearch response shapes and retention periods.

What you need to provide

Before a migration plan can be written, DeployOpen needs read access to the source system, its query logs or dashboard definitions, and a representative sample of the data. For Elasticsearch, that means index mappings and the search and aggregation requests that dashboards send. For PostgreSQL, it means schema DDL and the slowest or most frequent analytical statements from pg_stat_statements or equivalent. For a cloud warehouse, it means the query history and the views or scheduled transformations that feed reports. Alongside this, provide a volume and retention profile: daily ingest rate, current data size, and the retention window each team actually relies on.

Name one owner for each consumer team and each source system. These owners confirm which dashboards, alerts and exports must keep working and which can be retired. Decide in writing which queries and retention windows are in scope for the first cutover, because unbounded scope is the most common reason a migration overruns. Provide access to a pre-production environment with target infrastructure that resembles production in node count and storage type, along with decisions on object storage for backups and the network paths between source, ingestion and ClickHouse.

Agree the criteria for dual running and cutover before the build starts. Typical criteria include row count and aggregate agreement over an agreed window, query latency targets for named dashboards, and a rollback decision owner. Write these down with the people who will sign off.

You keep final sign-off on the business meaning of results. DeployOpen can show that a ClickHouse query returns the same numbers as the source for the agreed sample, but only your analysts and data owners can confirm that a metric definition still means what the business expects. Confirm this responsibility in the statement of work so that disagreements about meaning do not stall the cutover.

Frequently asked questions

What drives the cost of a ClickHouse migration and how do we get a fixed-scope quote?

Cost follows the workload inventory: the number of source tables, indexes or event streams, the volume of history to backfill, how many queries and dashboards need rewriting, and how many ingestion pipelines must be rebuilt. The cluster itself adds recurring infrastructure cost for compute, storage, replicas and Keeper nodes. To get a fixed-scope quote, list every source, consumer and report in a workload inventory, then ask for a written scope with deliverables and timelines. DeployOpen agrees scope in writing before work starts and does not publish a price list, so the quote follows from that scope.

How long does a ClickHouse migration take and what makes it longer?

Duration depends on the inventory, so ask for a phased plan in the written scope rather than a generic estimate. The biggest factors are the number of source systems, the volume of historical data to backfill, the number of queries that depend on functions or behavior specific to the old engine, and unresolved questions about deduplication and event ordering. Staff availability, change freezes and the length of parity testing and dual running also lengthen the schedule. Confirm each of these in your own environment before agreeing dates.

Can our team run the migration alone and what must our staff provide?

Some teams can do it alone, especially if they already operate analytical databases and replicated clusters. Most teams need help translating schemas and sort keys from Elasticsearch documents or PostgreSQL tables, rewriting queries, designing ingestion so backfill and live data do not duplicate, and setting up Keeper, replication and tested backups. Your staff should provide a named owner for each source system, access to schemas, query logs and dashboards, infrastructure access in your accounts or data centers, security approval for roles and network paths, and time to review translated schemas and parity results.

Will ClickHouse query results match the old system exactly and how is parity tested?

Not automatically. ClickHouse can differ from Elasticsearch, PostgreSQL or a warehouse in null handling, time zones, sort order, approximate functions and text matching, so exact match should be defined report by report. Parity testing runs agreed queries against the same data window in the old and new systems, records each difference and classifies it as a defect, an accepted change or a query that needs rewriting. Run this against representative data and queries in a pre-production environment before sign-off, and document every accepted difference.

Do we always need dual running and how long should it last?

Not always. Dual running is most useful when dashboards, alerts or downstream consumers depend on the old system and results must be compared on live traffic. Lower-risk workloads with a passed parity test may cut over sooner. The length depends on how many reporting cycles you need to see matching results and how quickly differences are fixed. Set the duration and exit criteria in the written scope, and take a restore-tested backup of both sides before cutover.

Does moving from ClickHouse Cloud change the migration and backup plan?

Yes, mainly in who operates the platform. ClickHouse server is open source under Apache-2.0, so the engine and SQL carry over, but ClickHouse Cloud is a managed service, and moving to your own cluster means your team owns backups, Keeper nodes, upgrades and access control. Plan backups with BACKUP and RESTORE to local disk, S3-compatible storage or Azure Blob Storage, and run a restore test on a spare cluster before cutover. Replication alone does not protect against human error or corruption, so confirm this approach in your environment.

How DeployOpen delivers this migration

DeployOpen scopes, builds, tests and hands over ClickHouse migrations from Elasticsearch, PostgreSQL or a cloud warehouse inside infrastructure you control. Each step is written down, verified against your own data and queries, and documented for the team that runs the system afterwards.

How we scope

The written scope lists source systems, analytical queries to move, and the ClickHouse target design agreed before work starts.
Schema translation covers table engines, sort keys and data types mapped from each source to ClickHouse MergeTree tables.
Engagements are fixed-scope, so backfill volume, query rewrites and dual-running duration are agreed in writing up front.

How we build

Replicated tables are built with ClickHouse Keeper on dedicated hosts, with replica and shard layout matched to your data volumes.
Backfill runs from the source system in batches, with row counts and checksums compared against the source before cutover.
Dual running compares results from the old and new query paths on representative queries before any consumer is switched over.

How we hand over

Handover documents the schema, ingestion pipelines, retention settings, access model and the rewritten queries that replaced the old ones.
Backup jobs using BACKUP and RESTORE are configured, and a restore test on a spare cluster is included in the handover evidence.
Runbooks cover replica recovery, Keeper node failure, version pinning and the pre-production test path for future upgrades.

How we support

Ongoing support, upgrades and continuous testing are available as a retainer after the migration is complete.
Upgrade planning follows the stable or LTS release line you choose, with changelog review and pre-production testing before each step.
Support scope, response expectations and review cadence are written into the agreement so your team knows what is covered.

ClickHouse migration from Elasticsearch, PostgreSQL or a cloud warehouse