TL;DR — ClickHouse is still the tool I reach for when data is written once and never changes: events, logs, clicks. But a big part of our analytics data is a copy of OLTP tables, and those rows get updated and deleted all the time. For that job I moved to StarRocks Primary Key tables: upserts and deletes are applied at write time, and a plain
SELECTreturns the current row. This post covers why, the trade-offs, and the honest list of things that went wrong.
The problem: mutable data
Some data never changes after it's written: a click, a log line, a page view. You append it and you aggregate it.
Other data changes all the time. An order is created, paid, packed, delivered, and sometimes cancelled or deleted. When you replicate tables like that from MySQL with CDC (change data capture), you get a stream of inserts, updates and deletes, and the analytics copy must always show the current row.
These two kinds of data need different things from a database. This post is about the second kind.
Why ClickHouse was painful for this job
Let me be fair first: ClickHouse is excellent. It's fast, stable and mature, and for append-only data I still think it's the best choice. The friction came from asking it to do something it wasn't designed for: keeping a mutable copy of an OLTP database.
1. "Current state" is eventual, not immediate
The usual CDC pattern in ClickHouse is ReplacingMergeTree(version). Every update is a new row, and duplicates are removed when parts merge, at some unknown time later. Until then the table holds several versions of the same key.
So every query has to choose:
-- Option A: FINAL (correct, but it does merge work on every read)
SELECT status, count() FROM orders FINAL GROUP BY status;
-- Option B: argMax per key (correct, but every query gets more complex)
SELECT status, count() FROM (
SELECT id, argMax(status, _version) AS status,
argMax(is_deleted, _version) AS is_deleted
FROM orders GROUP BY id
) WHERE is_deleted = 0
GROUP BY status;
Forget FINAL in one dashboard query and you double-count orders. Analysts will forget.
2. Deletes are a flag, not a delete
A CDC delete becomes a tombstone row (is_deleted = 1) that every query must filter out. Newer versions help (ReplacingMergeTree(ver, is_deleted), lightweight deletes), but the read-side discipline is still there.
3. Joins across many mutable tables
Reports join many of these tables: orders × order items × payments × customers... When every side of the join needs FINAL or an argMax subquery, the SQL gets heavy and the planner has less room to optimize.
4. Three objects per table
Kafka engine table → materialized view → target table. Multiply by a few hundred tables and schema changes become painful.
5. Small Avro surprises
Two I hit on ClickHouse 25.3:
- A Debezium
topic.prefixwith a hyphen (likemy-app) produces an Avro namespace that ClickHouse's Avro reader rejects (Invalid namespace). Fix:schema.name.adjustment.mode: avroon the connector, or just no hyphens. - An Avro
string(Debeziumdecimal.handling.mode=string) can't be read straight into aDecimalcolumn. The Kafka table needsNullable(String), and the MV casts it.
None of these is a dealbreaker on its own. Together, they meant that every consumer of the data had to know it came from CDC. I wanted a database where SELECT * FROM orders WHERE id = 42 just returns the current row.
Why StarRocks fits mutable data
Primary Key tables: upsert and delete at write time
CREATE TABLE orders (
id BIGINT NOT NULL,
created_at DATETIME NOT NULL,
status VARCHAR(32) NULL,
total_price DECIMAL(18,2) NULL,
updated_at DATETIME NULL,
_version BIGINT NOT NULL DEFAULT "0"
)
PRIMARY KEY (id, created_at)
PARTITION BY date_trunc('month', created_at)
DISTRIBUTED BY HASH(id);
A Primary Key table does delete-then-insert when the data is written. No merge-on-read and no FINAL. A reader always sees exactly one row per key: the latest one.
Deletes use a special column, __op (0 = upsert, 1 = delete), so a CDC delete really removes the row. With a Kafka load it's one line in the column mapping:
CREATE ROUTINE LOAD shop.load_orders ON orders
COLUMNS (
id, created_at, status, total_price, updated_at, op, ts_ms,
_version = ts_ms,
__op = if(op = 'd', 1, 0) -- the important line
)
PROPERTIES ("format" = "avro")
FROM KAFKA (
"kafka_topic" = "mysql.shop.orders",
"confluent.schema.registry.url" = "http://schema-registry:8081"
);
Ordering comes from Kafka: Debezium keys messages by primary key, so all events for one row land in one partition, in order.
Conditional updates for back-fills
When you back-fill history from a snapshot while the live stream keeps running, an old snapshot row must not overwrite a newer live row. StarRocks handles this with merge_condition:
PROPERTIES ("merge_condition" = "_version")
-- the incoming row is applied only if its _version >= the stored one
It speaks MySQL
StarRocks uses the MySQL wire protocol and a very similar SQL dialect. Reconciliation queries run unchanged on the source and on the copy, and analysts connect with the same DBeaver/Metabase drivers. If you check a replica against MySQL often, that matters more than it sounds.
Joins are a first-class citizen
A cost-based optimizer, shuffle/broadcast/colocate joins, and async materialized views. Tables that share a key (orders and order_items on order_id) can be colocated, so the join never moves data across the network.
Partial updates and schema changes
Partial column updates (fill one column without rewriting the row) saved me several times when I had to repair one column across millions of rows. fast_schema_evolution makes ADD COLUMN nearly instant.
The cons (the honest part)
StarRocks is not magic. Here's what bit me or what you should plan for.
1. Silent NULLs with strict_mode = false
With strict mode off, a value that doesn't fit its column becomes NULL instead of failing the load:
-
VARCHAR(n)in StarRocks counts bytes, MySQL counts characters. Persian text is 2 bytes per character, so long values becameNULLwith no error. Size VARCHARs as MySQL length × 4. - A MySQL
ENUM('NO','EXTERNAL',...)mapped toBOOLEAN→ 100% NULL. Zero errors.
Lesson: after loading a table, compare NULL rates per column with the source. "Loaded rows" and "0 error rows" don't prove the values are right.
2. Your primary key must never change at the source
In a Primary Key table the partition column has to be part of the key, and you may add a parent id to the key so related tables can be colocated. If one of those columns changes in MySQL, the update arrives with a new key: StarRocks inserts a new row and the old one stays forever. I found thousands of such "ghost rows" on one table after a bulk change of a parent id.
Lesson: build the PK only from columns that never change (id, created_at). If you can't, plan a regular check for keys that exist in StarRocks but not in MySQL.
3. merge_condition can't protect against a delete
Conditional updates compare the incoming row with the stored one, and they don't work together with deletes, so use them for back-fills only. Side effect: if MySQL deletes a row while a back-fill snapshot is running, the snapshot can re-insert it later, because there is no stored row left to compare against. It's rare, but real. Compare keys with the source after every back-fill.
4. The primary key index costs memory
Fast upserts come from a primary key index. Keep keys small (integers, not long strings), and turn on the persistent (on-disk) index for big tables. A wide VARCHAR key on a billion-row table gets expensive.
5. Throughput scales with backends
Every upsert does a key lookup, and compaction runs after big loads. On a small cluster, back-fills of hundreds of millions of rows take hours, and running them in parallel doesn't help much. Slice them by primary-key range and plan the time.
6. Smaller ecosystem
ClickHouse has a bigger community, more integrations, more Stack Overflow answers and more managed offerings. With StarRocks I read source code more than once to understand a behavior.
7. Now there are two databases to run
Keeping ClickHouse for event data means two systems to operate, monitor and upgrade. For us the clean split was worth it, but it is a real cost.
Side by side
| ClickHouse | StarRocks | |
|---|---|---|
| Best at | Append-only, immutable data | Mutable data, upserts, CDC |
| Updates | New version row, deduplicated at merge time | Delete+insert at write time (PK table) |
| Deletes | Tombstone flag or mutation | Real delete via __op
|
| Read latest state |
FINAL / argMax
|
Plain SELECT
|
| Joins | Improving fast; best denormalized | CBO, colocate/shuffle joins |
| Protocol | Native + HTTP | MySQL wire protocol |
| Kafka ingest | Kafka engine + MV (per table) | Routine Load or Stream Load |
| Out-of-order guard | Version column | merge_condition |
| Ecosystem | Large, mature | Smaller, growing |
| Watch out for | Forgotten FINAL, merge lag |
Silent NULLs, PK design, PK index memory |
Where we ended up
- ClickHouse stays for immutable, high-volume event data. It's still the best tool for that.
- StarRocks holds the mutable tables we replicate from MySQL.
- Analysts query current state with plain SQL. No
FINAL, nois_deletedfilters, no "remember this table comes from CDC".
The image at the top says it better than I can: two bottles, two openers. Don't force one database to do both jobs.
Checklist if you're doing the same
- Pick the engine by how the data changes, not by benchmark charts.
- Build primary keys only from columns that never change.
- Test deletes end to end before going live, not just inserts.
- Check NULL rates and values against the source after loading. "0 error rows" is not "correct".
- Use
merge_conditionfor back-fills, and check for re-inserted deleted rows afterwards. - Monitor freshness (newest update time per table), not just load status.
Have you run CDC into ClickHouse or StarRocks? I'd love to hear what bit you. Let me know in the comments. 👇
Top comments (1)
Wonderful work Shahab 💡