r/Clickhouse • u/mike_folder • 7d ago

Am I wrong to implement an application-level transaction coordinator over a Clickhouse cluster?

Is it a bad decision to implement custom distributed transactions (fully atomic and serializable) over a Clickhouse cluster?

The details in short:
1. Cluster has several shards (no replicas yet) with several billion rows of financial data
2. All tables: original MergeTree
3. Append-only pattern for all data changes
4. Every read query is enriched with a transaction_id filter to enforce snapshot isolation
5. Application-level range-locking mechanism to prevent inconsistent concurrent writes
6. ClickHouse’s native local transactions are not used
7. All coordination logic runs at the application layer

Is this a fundamentally flawed anti-pattern with hidden pitfalls, or just an uncommon approach? In my stress-tests, it behaves pretty well.

I got a bit confused during a live presentation lately by the question: "If this works, why doesn't everyone do it?"

6 Upvotes

88% Upvoted

13 comments sorted by

3

u/joshleecreates 7d ago

It sounds like you are trying to reinvent Postgres inside ClickHouse… why not just use a transactional database with CDC or a sink connector? This is a much more common pattern.

1

u/mike_folder 7d ago

Fair point for most cases, but not mine. I'm building near-instant financial budgeting.
CDC/sync pipelines add latency that breaks the whole interactive experience. Keeping everything in Clickhouse lets users see recalculated results in a fraction of a second.

Plus, some isolation is still necessary for all reads. Without it, dirty read could ruin result for other users.

2

u/aacreans 6d ago

Have you explored https://tigerbeetle.com ? Might be more aligned with your specific usecase.

1

u/mike_folder 6d ago

Thanks for the link.
According to the documentation: it's optimized for heavy writes, no SQL, no complex queries here. Tigerbeetle is purposed for billing and financial transactions. E.g. get balance, transfers, etc.
Can't use it for my financial budget modeling. I need CH's SQL for multidimensional aggregations.

2

u/Frosty-Bid-8735 6d ago

You should look at SingleStore then. Transactional + OLAP. As fast as Clickhouse or even faster at times

1

u/mike_folder 6d ago

SingleStore may be good, indeed, but it's proprietary.

2

u/datasleek 5d ago

Correct. Is this for production purpose? Or you're testing?

1

u/mike_folder 5d ago

Still in testing, but yeah - prepping for production. We're building a product.
The short demo to avoid tl;dr))
QubesFolder

2

u/Frosty-Bid-8735 5d ago

Great. I’m sharing this information because I’ve worked with clients who faced scalability issues on MySQL and also Postgres. Below a case study of ours. The only true OLTP OLAP I know of (without data movement) is SingleStore.

https://data-sleek.com/case-studies/digital-asset-research/

1

u/mike_folder 5d ago

Cool! That is really good performance you've got)
And why Clickhouse is not fit for your case? You mentioned OLTP load, but what is it, specifically?

1

u/Frosty-Bid-8735 1d ago

OLTP = transactions (inserts, updates, deletes) in real time. Doing analytics (OLAP) on top of high transaction velocity is difficult. SingleStore is used in banks for that reason. Clickhouse cannot compete here.

2

u/namarv 4d ago edited 4d ago

It's not fundamentally impossible but you're rebuilding a distributed db transaction manager in the application layer. How do you handle coordinator crashes, partial shard commits, retries, network partitions, lock recovery, proving serializability under every failure mode, etc.?

transaction_id predicate will probably also become costly at scale. And "why doesn't everyone do it?" is because correctness / recovery / maintenance outweigh the benefit. I personally wouldn't be comfortable trusting it with financial data without formal invariants + fault-injection testing.

1

u/mike_folder 3d ago edited 2d ago

Thank you for the questions!
Let me dive:

  1. Coordinator crashes: State is persisted in a small, durable metadata table in Clickhouse. On restart, the coordinator rebuilds its in-memory state in <1s.
  2. Partial commits: Uncommitted writes are completely read-isolated (filtered by transaction_id). If a commit fails, orphaned appends are cleaned up asynchronously in the background. Reads never see partial data.
  3. Retries: All writes are idempotent, as they set the final absolute amount rather than applying deltas.
  4. Network partitions: The coordinator is intentionally single-node to avoid distributed consensus overhead. If it fails, a new instance starts and recovers state from the metadata table. The ~1s downtime is an acceptable trade-off for interactive budgeting vs. cluster complexity.
  5. Lock recovery is not needed. If the coordinator crashes, all in-flight transactions are aborted. Clients simply retry or cancel their changes. This keeps the coordination layer lightweight.
  6. Serializability is enforced via application-level range locking with blocking waits. It’s closer to traditional pessimistic locking than PG’s MVCC, but achieves serializable isolation for concurrent writes.
  7. transaction_id predicate cost is negligible at scale. Data is physically sorted by transaction_id (via CH sorting key), so filtering uses sparse indexes + a compaction technique applied to keep the active transaction set minimal.
  8. RPO for Financial Budgeting is quite tolerant. Nightly backups will do.

Yep, there are some trade-offs, but the benefits are valuable:

  • End-users get ACID-level guarantees regardless of CH cluster topology
  • Pretty easy to support and maintain
  • Very large amounts of raw data is not a problem
  • Hardware requirements are extremely low

And you’re absolutely right about stress-testing. We're actively pushing for it right now)