Migrate to ClickHouse

Migrate from Postgres to ClickHouse

Keep Postgres for transactions and move the analytics to ClickHouse. Sub second queries over billions of rows, a fraction of the compute, and a CDC pipeline that keeps the two in line.

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 Postgres users come to us

Postgres is the most common database we see in ClickHouse projects, and for good reason. It is an excellent transactional store. The problem starts when the application grows and the analytical queries grow with it. Dashboards that used to load instantly take seconds, then tens of seconds, and the usual fixes of adding indexes, read replicas and bigger instances buy less time each round.

The underlying issue is that Postgres stores rows, and analytics reads columns. Once a table reaches tens or hundreds of millions of rows, aggregations have to scan far more data than they need, and they compete with the transactional workload for the same CPU, memory and I/O.

ClickHouse does not try to replace Postgres as the system of record. The pattern we deliver is to leave the transactional workload where it is, replicate the data into ClickHouse continuously, and run the reporting, dashboards and customer facing analytics there instead.

Sound familiar?
  • [01]Aggregation queries over large tables take seconds or minutes, and get slower every month.
  • [02]Reporting traffic contends with the application, so the only fix is a bigger instance or more read replicas.
  • [03]Indexes and partial materialised views are being added for individual dashboards rather than solving the problem.
  • [04]Customer facing analytics cannot be offered because the latency and concurrency are not there.
  • [05]Retention has to be cut or data archived because storage and vacuum overheads grow with the table.
What changes

Postgres 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]

Queries that were seconds become milliseconds

ClickHouse reads only the columns a query touches and compresses them heavily, so the same aggregation over the same rows scans a fraction of the data. Queries that take tens of seconds in Postgres typically return in well under a second.

[02]

Postgres gets its headroom back

Once the analytical queries move, the transactional database is doing only the job it was designed for. Instances can usually be downsized and replicas retired, which is where a lot of the saving comes from.

[03]

Real time, not nightly

CDC replication keeps ClickHouse within seconds of Postgres, so there is no overnight batch and no reporting database that is a day behind. Materialised views aggregate data as it lands.

[04]

Scale without the rewrite

The application does not change. Postgres remains the write path and ClickHouse becomes the read path for analytics, with a familiar SQL dialect and drivers for every language and BI tool.

How we migrate

Accelerated migration with CDC

Postgres to ClickHouse is the best supported migration route there is. ClickHouse Inc acquired PeerDB and built it into ClickHouse Cloud as the Postgres CDC connector for ClickPipes, and we have used it to replicate hundreds of millions of rows in under an hour. For self hosted ClickHouse, or where a single binary you control is preferred, we use Supermetal.

  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 Postgres 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.

Capital Markets

Supporting A Trade Compliance Vendor In Their Migration From Postgres To ClickHouse

Helping a trade compliance and surveillance software vendor migrate from PostgreSQL to ClickHouse to handle multi-billion row datasets and petabyte scale workloads.

Further reading

Ready to move off Postgres?

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