- A transactional row store and an analytical column store make different promises; your application was written against one and will now be read against the other.
- The reads split, the write path moves to replication, the schema is reshaped, queries get rewritten, updates become eventual, and the ORM, fixtures and tests all change. Each item is a code change somebody has to own.
- It breaks the same way every time: a dual-write period that never ends, a load-bearing dashboard, a rewritten query that is right and different, a migration that finishes at eighty percent.
- Run the application side as a lifecycle: model the system first, scope each consumer as its own Change, cut over in waves by blast radius, a named sign-off at every gate, retire the old path on a date.
- The data moves with your data team or a partner. CloudGeometry changes the application that sits on top.
Moving analytics from a transactional database to a column store is a good decision that gets scoped as a data project and paid for as an application project. This is about the second half.
Everyone at the Rows & Columns Summit last month was moving data. Transactional stores on one side, columnar analytics on the other, and a day of argument about whether the two converge or stay apart. CloudGeometry was the only services company in the building, and the conversation that kept coming back to our table was not about the databases at all.
It was about the code sitting on top of them.
Moving analytics from a transactional database to ClickHouse is a good decision for a lot of teams. It is also a decision that gets scoped as a data project and paid for as an application project. This post is about the second half: what changes in your application when the analytical workload moves to a column store, where it breaks, and how to run the change so it finishes.
Two engines, two contracts
Start with what each engine promises, because the application was written against one promise and will now be read against another.
A transactional database is built for short, frequent, row-level operations: an order placed, an inventory count decremented, a customer record updated. AWS describes the pattern plainly: OLTP “helps you manage and process real-time transactions,” with responses “typically in milliseconds,” on normalized relational models. An analytical engine is built for the opposite shape: large scans, heavy aggregation, “complex queries on large volumes of data,” where the unit of work is a question across millions of rows rather than a change to one.
ClickHouse is a column store. It stores data by column, sorts it by an ordering key you choose, compresses it aggressively, and answers aggregate questions over billions of rows in the time a row store takes to find one. It is also append-oriented. Updates and deletes exist, but they are not the cheap, immediate, row-level operations your application assumes today.
That is the whole problem in one sentence. Your application has a contract with a row store, and you are about to put part of it in front of a column store.
What changes in the application
Here is the mechanical list. Every item on it is a code change that somebody has to own.
The reads split. Not every query moves. The dashboard aggregations, the reporting jobs, the “how many of X last quarter” endpoints move to ClickHouse. The “load this customer’s profile” queries stay where they are. Somebody has to classify every read path in the application into one bucket or the other, and the classification is rarely clean: the same service often does both in one request.
The write path changes shape. The recommended pattern in ClickHouse’s own migration guide is to keep the transactional database for writes and replicate into ClickHouse through change data capture. Good. That means the application stops writing analytics tables directly, which means every job, sync and script that currently writes them has to be found and retired. Those are the write paths nobody has a list of.
The schema is not your schema. Column stores reward denormalization, a deliberate ordering key, and materialized views that pre-aggregate the questions you ask most. A faithful copy of your normalized tables will work and will be slow. The schema redesign is a data task, but the consequence is an application task: queries written against the old shape get rewritten against the new one.
“Most queries run unmodified” is true and not reassuring. ClickHouse states that most SQL from a Postgres setup runs without modification. It does. The queries that matter are the ones you rewrite anyway, to use the engine properly: argMax instead of a window function, countIf instead of a nested CTE, array functions instead of unnest. Each rewrite is a chance for the numbers on the finance dashboard to change by a rounding rule. Somebody has to prove they did not.
Updates and deletes become a replication concern. Under CDC, an update arrives in ClickHouse as a new row version; deduplication happens in views on read, or in refreshable materialized views. Application code that assumed update-in-place, or read a row back immediately after writing it, now has to live with eventual freshness. Most of it will. The two endpoints that do not are the ones that will page you at 3 a.m.
The ORM has opinions. If your data access layer generates SQL, it generates row-store SQL. The analytical paths either leave the ORM or get a second, hand-written access layer. Either way, it is new code with new tests.
Types, nulls and time. Numeric precision, nullable semantics, timezone handling and string collation all differ enough between engines to produce a result that is “almost right.” Almost-right code is the most expensive kind of wrong, because it ships.
The fixtures. Every integration test that seeded the analytics tables directly needs a new way to seed them.
Where it breaks
The failure modes are predictable, because they are the same every time.
The dual-write period never ends. The team keeps both paths live “until we are confident,” confidence never arrives, and a year later the application writes to two systems and reads from whichever one the author of that endpoint preferred.
A dashboard turns out to be load-bearing. Something that looked like a report feeds a pricing rule, a customer-facing health score, or a partner integration. It is discovered on cut-over day, in production, by the partner.
The rewritten query is right and different. A rounding rule, a timezone boundary, a null that used to be excluded. Finance notices in the monthly close.
The migration finishes at eighty percent. Data moved, performance proved, the last fifth of consumers still pointed at the old system because nobody owned them. The old database cannot be retired, so the cost savings never materialize, so the project is quietly judged a failure that was technically a success.
Why it happens: the migration was scoped as a data project. The data moved. The application changes were never scoped at all, because the people who scoped the migration do not own the application, and the people who own the application were not in the room.
Run it as a lifecycle, not a project
The fix is not a better data team. It is treating the application side as what it is: a set of governed changes to a production system, each of which has a blast radius, an estimate, an owner and a sign-off.
In practice that looks like this:
- Model the system before anything moves. Build the dependency model of what reads and writes each table: services, jobs, dashboards, syncs, tests. We call that model the AppGraph, and it is the first thing a System Assessment produces. You cannot ask for this list. You have to derive it from the artifacts.
- Scope each consumer as its own Change. One endpoint, one job, one dashboard: scoped, estimated in effort and cost, approved before any code is written. At the booth we described it as one schema change producing twenty-eight touch points, found, sized and priced before a line was written. That is the shape of the work.
- Cut over in waves, by blast radius. Low-risk, read-only consumers first; load-bearing paths last, with the old path kept warm until the evidence says otherwise.
- Put a name on every gate. AI can do a great deal of the rewriting, test generation and reconciliation. It should. What it cannot do is sign. A senior engineer validates the evidence at each gate before the change progresses.
- Retire the old path on a date. The migration is finished when nothing reads the old shape, not when the rows land.
This is the work CloudGeometry delivers through AI-MSL, the AI-managed software lifecycle platform through which we run application modernization and data platform engagements: the application changes that follow a database decision, managed, supervised, and priced per outcome. The data moves with your data team or with a partner like ClickHouse. We change the application that sits on top.
The question to ask before you sign
Before the migration is approved, ask one question of whoever is proposing it: which application changes are in scope, who owns each one, and what does each one cost?
If the answer is a list, the migration will finish. If the answer is “we’ll ask around,” write down the date. That is the day it started slipping.
Read next: Why You Need a Semantic Layer, Even With a Really Good Coding Agent and What Your Codebase Knows That Your Team Forgot
Sources: AWS, “What’s the difference between OLAP and OLTP?” (aws.amazon.com/compare/the-difference-between-olap-and-oltp). ClickHouse, PostgreSQL to ClickHouse migration guide, parts 1 and 2.
