Migrate to ClickHouse

Migrate from MySQL to ClickHouse

MySQL stays as the operational database. ClickHouse takes the reporting, dashboards and analytics, fed continuously by CDC, and answers in milliseconds what MySQL answers in minutes.

How the migration works
Sub second
Queries over billions of rows, from columnar storage and vectorised execution.
50%+
Cost savings observed in the migrations we have delivered from SQL Server, OpenSearch and Snowflake.
100s
Of concurrent users served with consistent latency, so analytics can go into your product.
Anywhere
Open source. Run it on any cloud, on premise or as ClickHouse Cloud.
Why teams move

Why MySQL users come to us

MySQL runs a large share of the web. It is simple, reliable and well understood, and it is very good at the short reads and writes an application makes. It was never designed for the analytical queries that get bolted on later: the admin dashboards, the customer reports, the GROUP BYs over a year of events.

As those tables pass into the tens and hundreds of millions of rows, the pattern is always the same. Queries slow down, replicas are added to keep reporting away from production, summary tables are maintained by cron jobs, and the team spends its time managing the symptoms.

We see this most often in SaaS products, ecommerce, gaming and adtech. The fix is not a bigger MySQL. It is to leave MySQL doing the transactions and replicate the data into ClickHouse, where the analytical queries run natively on columnar storage.

Sound familiar?
  • [01]Reports and admin dashboards time out or have to be restricted to short date ranges.
  • [02]Read replicas exist purely to keep analytics away from the production database.
  • [03]Pre aggregated summary tables are maintained by scheduled jobs and drift from the source.
  • [04]Growing event and log tables are being pruned to keep MySQL responsive.
  • [05]Product teams want customer facing analytics but the database cannot support the concurrency.
What changes

MySQL to ClickHouse: faster, cheaper, simpler

ClickHouse is the fastest open source analytical database on the market. These are the differences that matter most for teams coming from this platform.

[01]

Orders of magnitude faster analytics

ClickHouse is built for exactly the aggregation, filtering and time series queries MySQL struggles with. Columnar storage, vectorised execution and sparse indexes mean sub second results over billions of rows.

[02]

Fewer, smaller servers

ClickHouse typically compresses data five to ten times, and the analytical load leaves MySQL entirely. Reporting replicas go away and the primary can usually be downsized.

[03]

No more summary table jobs

Materialised views in ClickHouse maintain aggregates incrementally as rows arrive, replacing the cron jobs and the drift that comes with them.

[04]

Keep the application as it is

The write path does not change. CDC replicates inserts, updates and deletes from MySQL into ClickHouse within seconds, and analytics queries are pointed at the new database.

How we migrate

Accelerated migration with CDC

ClickPipes includes a MySQL CDC connector that snapshots the tables and then streams changes from the binlog, which is the quickest route for ClickHouse Cloud. Supermetal offers the same capability as a single binary for self hosted deployments or where you want full control over the replication process. Either way, the migration runs alongside production with no downtime.

  1. 01

    Assess

    We review the schemas, the query workload and the cost of the current platform, then agree the scope, the target architecture and the business case before anything is built.

  2. 02

    Design the ClickHouse schema

    Tables are redesigned for columnar storage rather than copied across one to one. Sorting keys, partitioning, codecs and materialised views are chosen against your real queries.

  3. 03

    Load and replicate

    Historical data is bulk loaded, then changes are streamed continuously so ClickHouse stays in line with the source for as long as the two need to run side by side.

  4. 04

    Run in parallel

    Dashboards, applications and reports are pointed at ClickHouse and validated against the source. Performance and cost are measured rather than assumed.

  5. 05

    Cut over

    Workloads move across in stages, the old platform is scaled down and the remaining spend is retired. Nothing is switched off until the numbers reconcile.

  6. 06

    Support

    After go live we can monitor the environment, tune it as the workload grows and provide an escalation path under SLA through our proactive support service.

Tooling we use
ClickPipes

The managed ingestion service built into ClickHouse Cloud. Its CDC connectors take an initial snapshot and then stream every insert, update and delete, with no infrastructure for you to run.

Supermetal

A high performance CDC engine from supermetal.io that ships as a single binary and replicates from Postgres, MySQL, SQL Server, Oracle and MongoDB into ClickHouse, including self hosted clusters.

We are tool agnostic. The right choice depends on whether you are going to ClickHouse Cloud or self hosted, how much control you want over the pipeline and what else is in the stack. Zero downtime is the norm, not the exception.

Beyond the migration

One database, many use cases

Moving off MySQL is usually the start. Because ClickHouse is so versatile, the same platform tends to pick up more workloads once it is in place.

Real time analytics

Dashboards and embedded analytics over data that is seconds old, served to many users at once.

Data warehousing

A warehouse that is fast enough to query directly, without the overnight batch or the cube layer.

Observability

Logs, metrics and traces at a fraction of the cost of Datadog, Splunk or Elastic, with ClickStack on top.

Time series

Market data, IoT, telemetry and events at hundreds of thousands of rows per second with long retention.

Search and security analytics

Full text search, SIEM and threat hunting over high volume event data using plain SQL.

Agentic AI

A data platform fast and cheap enough for agents that turn one question into dozens of queries.

Related work

Migrations we have delivered

Gaming

Helping An Online Gaming Company Migrate From Postgres To ClickHouse Cloud

Building a real-time data warehouse for a sports betting and gaming platform processing ~300 million events per day, scaling to trillions of rows.

Telco

Migrating A Contact Centre Analytics Platform From OpenSearch To ClickHouse Cloud

Migrating a workforce performance analytics platform from Postgres and OpenSearch to ClickHouse Cloud, achieving 50% cost reduction and real-time intraday analytics.

Further reading

Ready to move off MySQL?

Start with a short call. We will look at your schemas, queries and current spend, tell you honestly whether ClickHouse is the right fit, and put a number on the performance and cost you can expect.

Support after go live