draft-imran-systems-and-arguments-45Internet-Draft
← draft-imran-systems-and-arguments-45
Off the Bit Implementation 3.4 Category: System State: complete Share: https://mosthofaimran.com/l/3-4
Analytics migration to ClickHouse

Analytics migration to ClickHouse

Moving analytical workload off a row store onto a columnar one without downtime, under dual writes, with the rollback built before the cutover rather than after it.

Stack

ClickHouse · Kafka Connect · Airflow

Text

How to read this note. This is the reference design for moving an analytical workload from a row store to a columnar one under live traffic: the constraint, the decisions that follow, and the failure modes this migration has. It is a solution path for a system like the one built rather than a disclosure of that system's internals. Table names, volumes, schedules and the specific queries are deliberately absent, and the pipeline components in the stack describe the shape of the thing rather than a certified inventory.

The figures this page carried until 2026-09-03 (fourteen billion rows, a p95 falling from 9.4 seconds to 380 milliseconds, and a rollback executed at 02:40) were the handoff prototype's and are removed under erratum 7.19. No replacements are invented.

1. The constraint

Analytical queries on a transactional database work until they do not, and the transition is not gradual.

A row store fetches whole rows. An analytical query wants three columns out of forty across a year of history, so the engine reads roughly thirteen times the data it needs, and the cost grows with total table size rather than with the size of the answer. For a long time this is fine and the fix is an index. Then a dashboard that took two seconds takes forty, and the honest description is that the storage model stopped matching the question.

The migration itself is not the hard part. The hard part is that the analytics were already load-bearing. Reporting surfaces sit in front of customers, operators run their day from them, and regulated clients have reporting obligations that do not pause. So there is no window, no maintenance weekend, and no version of this where the numbers are allowed to be wrong for an afternoon while a cutover settles.

2. The decisions, and where each one is enforced

writes one source row store source of truth until cutover columnar store written, not yet read reconciliation counts, checksums, per partition divergence blocks the read move READS MOVE ONE QUERY AT A TIME, AND MOVE BACK THE SAME WAY query one dashboard per-query flag default is the row store clean for longer than the reporting window, then move one flag flip returns it, with no deploy Backfill runs behind the live stream and is allowed to overlap it, because the write is idempotent on a natural key. A pipeline safe to run twice is a pipeline safe to resume.
Figure 1. Nothing here is a cutover. It is a period during which both stores are correct, and a sequence of small reversible decisions about which one answers.

2.1. Dual write, and the old store stays authoritative. Both stores take every write from the beginning. The new one is not read from until reconciliation has been clean for longer than the longest reporting window, because a discrepancy that only appears in a monthly close is invisible for a month. Authority is enforced at the read path rather than agreed in a plan: the query layer’s default target is the row store, and moving a query is an explicit act.

2.2. Reads move one query at a time, behind a flag. Not one table, and never all at once. A dashboard is moved, watched against the old answer, and either kept or returned by flipping a flag with no deploy. This is slower than a cutover and it is the only version where “the numbers look wrong” has a same-minute answer.

2.3. The rollback is built first and exercised on a schedule. Before any read moves, the path back is written and run. Then it keeps being run, against production, on a timer, because a rollback that has not executed this month is a plan and not a capability. Failure 5.5 is what happens when this lapses, and it lapses by default.

2.4. The schema is designed for the query, not translated from the source. A columnar store rewards denormalisation, a sort key matching the dominant access pattern, and partitioning on the dimension that queries filter on first, usually time. Porting the normalised OLTP schema across is the fastest way to build something that is slower than what you left, and it is the most common way this migration fails.

2.5. Every load is idempotent on a natural key. This one property makes backfill resumable, makes stream and backfill safe to overlap, and makes a duplicated batch harmless. It costs a key design decision at the start and it removes an entire category of incident.

3. Why this is a product decision and not a storage one

Analytics that answer in under a second and analytics that answer in forty are different products, not the same product at different speeds.

For the customers this served, reporting is not a convenience feature. A telecom operator sizes staffing from it, a bank reconciles against it, and a regulated client has obligations that assume it returns. When a dashboard takes forty seconds, people stop opening it and start asking a person, which converts an infrastructure cost into a support cost and hides it.

The migration is also what makes the next thing possible. Query patterns that nobody proposes because they are known to be too expensive (cohorting across a full history, per-tenant breakdowns over a year) become ordinary requests once the storage model fits them. The measured benefit is the dashboards that got faster. The unmeasured one is the analysis that starts getting asked for.

4. Figures

This note reports none. The four that would matter are rows migrated, query latency at p95 before and against after on the same query shapes, the divergence rate observed during dual writes, and the elapsed time from first dual write to full cutover.

They exist for the deployment and are not published here. The figures this page did carry were the prototype’s, and erratum 7.19 removes them. Publishing a plausible substitute would be the same defect in better clothes, which is the argument erratum 7.13 made and this note inherits.

5. What I would do differently

Build the differential test harness before moving the first query. Failure 5.3 is the one that damages trust rather than uptime, and it is caught by running the same query against both stores and diffing the result, not by watching for errors. That harness is a day of work and it wants to exist before the first dashboard moves, not after the first argument about a number.

Keep a scheduled read from the old store for the whole dual-write period. Failure 5.5 is open here for the honest reason: the discipline is easy to describe and it is the first thing that lapses once the migration looks finished. A cron job that reads production from the old store, weekly, is what keeps the rollback real, and it costs nothing.

Decide the sort key with a query log, not a design meeting. The dominant access pattern is an empirical fact sitting in the existing database’s logs. It is routinely guessed at instead, and failure 5.4 is the bill for guessing.

Known failure modes

5.1, fixed. Dual writes diverge, because there is no transaction spanning two stores. One write succeeds, the other fails on a timeout or a schema rejection, and nothing surfaces it: both systems are up, both are serving, and the numbers drift apart slowly. The answer is continuous reconciliation rather than a spot check at the end, comparing counts and column checksums per partition on a schedule, with the old store remaining the source of truth until the comparison has been clean for longer than the longest reporting window.
5.2, fixed. The seam between backfill and live stream produces duplicates or a gap, and which one you get depends on an ordering nobody controls. Starting the stream after the backfill loses everything written during it; starting it before duplicates the overlap. The standard answer is to make the write idempotent on a natural key and deliberately overlap: start the stream first, backfill behind it, and let the overlap collide harmlessly. A pipeline that cannot be run twice safely cannot be resumed after a failure either, which is the same property.
5.3, open. Query semantics are not identical between engines and the differences are quiet. NULL ordering, integer division, timezone handling at DST boundaries, float against decimal accumulation, and approximate DISTINCT that is exact enough for a dashboard and wrong for a finance report. Nothing fails. A number changes in the third significant figure and is discovered by somebody who reconciles by hand. The only defence is differential testing on real query shapes, and the coverage is never complete.
5.4, accepted. The sort key is chosen against the queries that exist at migration time and is expensive to change afterwards, because changing it means rewriting the table. Query patterns move, and a store that was fast becomes a store that is fast for the old dashboard. The decision is to accept a periodic rewrite as a cost of the architecture rather than to pretend a single ordering serves everything, and to size the tables so that a rewrite fits in a maintenance window.
5.5, open. The rollback path decays from the moment it stops being exercised. Dual writes are kept for a period after cutover so that reverting is possible, and during that period nobody reads from the old store, so a schema change or a broken consumer goes unnoticed until the day it is needed. An untested rollback is not a rollback. Running production reads against the old store on a schedule is the only thing that keeps it honest, and it is the first thing dropped when the migration is declared finished.
ImranImplementation 3.4complete