Tales of Two Databases
On the Quant Data team, we operate two primary databases: ClickHouse and MongoDB. One handles analytics, the other transactions. They serve fundamentally different purposes, and trying to make one do the other’s job would be a mistake.
This isn’t a deep dive into storage engine internals — B+ trees vs. LSM trees, WiredTiger page faults, or ClickHouse’s MergeTree mechanics. It’s about how each database fits into the business, what tradeoffs we’ve accepted, and what we’ve learned operating both at scale.
ClickHouse: The Analytical Engine
Our ClickHouse cluster runs a 2-shard, 2-replica topology — four nodes supporting several hundred terabytes of data. As with most systems, the Pareto principle applies: we have hundreds of databases and tables, but roughly 20% of them account for over 80% of storage. L2 and L3 order book snapshots are the biggest offenders, followed by market trades across multiple exchanges.
ClickHouse is a columnar database, optimised for analytical workloads — aggregations, scans, and joins across large datasets. The queries it serves are varied: PnL reports, post-trade analysis, slippage measurements, cross-exchange depth comparisons, and joins across master securities and account reference tables. Some tables hold raw market data; others hold derived datasets. It also acts as a data lake, receiving ETL’d data from MongoDB for historical analysis.
The cluster runs a multi-leader setup. Inserts can go to any of the four nodes; data is routed by sharding key and replicated to the corresponding replica. This is a classic CAP theorem tradeoff — we sacrifice strict consistency for availability. If a node goes down due to OOM or CPU exhaustion (both have happened), the remaining nodes continue serving reads and writes. Production workloads are minimally impacted while the failed node takes a few minutes to restart.
I’ll be honest — I didn’t fully appreciate the HA design until I watched it work under failure. A node went down, production jobs on that node failed, but the recovery mechanism redirected traffic to healthy nodes and everything else kept running. Without that setup, it would have been a stop-the-world event until the node recovered. Seeing it recover gracefully — rather than bringing everything to a halt — was the moment I appreciated the architecture and HA stopped being just a theory.
When ClickHouse Chooses Availability Over Consistency
The eventual consistency model also means read-your-writes isn’t guaranteed — especially under high load. When nodes are under pressure, replication lag increases, and a read immediately after a write may hit a replica that hasn’t caught up yet. This is fine for analytical workloads where you’re querying data that’s minutes or hours old. It becomes a real problem if you try to use ClickHouse as a backend for API systems that write a record and immediately read it back for validation. We hit this: a service would insert, then query to confirm the insert, and intermittently get empty results. The data was there — just not on the node that served the read. The fix wasn’t in ClickHouse; it was in accepting that ClickHouse isn’t the right tool for that pattern.
MongoDB: The Transactional Core
MongoDB runs a 1-primary, 4-read-replica topology in our system. The primary handles all writes; the replicas exist to support higher read throughput. This is just one example of more than a dozen clusters used across different desks and purposes.
To ensure strong consistency, we set the quorum to 3 reads and 3 writes. Since R + W > N, this guarantees that reads return the latest committed data in virtually all cases — excluding the razor-thin edge where a read and write land at precisely the same moment.
This matters because MongoDB is our OLTP layer. It stores transactional data — positions, risk metrics, account state — that downstream systems depend on for hedging decisions. If a hedge is calculated against stale position data, you over-hedge or under-hedge. Either way, that’s real PnL impact. Consistency isn’t a nice-to-have here; it’s a business requirement.
MongoDB also handles data where the schema evolves over time. Trade formats change, new fields get added, reference data structures shift. Document databases absorb this well. When the shape of the data stabilises and the analytical value becomes clear, we ETL it into ClickHouse.
When MongoDB Chooses Consistency Over Availability
MongoDB’s consistency-first design has a flip side: when it has to choose between consistency and availability, availability loses.
We learned this the hard way when an index was added in the foreground on a collection with significant historical data. The index build locked the collection, and production writes ground to a halt. The data stayed correct — no inconsistencies, no corruption — but the system was effectively unavailable until the index finished building.
The lesson was straightforward: all index operations must run in the background. But the deeper takeaway was a reminder of what MongoDB optimises for. It will always protect data correctness, even at the cost of availability. That’s exactly the behaviour you want for transactional workloads — until you forget it applies to administrative operations too.
The Boundary Between Them
The split is clean in principle: MongoDB handles the present, ClickHouse handles the past.
MongoDB is the source of truth for live state — current positions, active orders, real-time risk. Systems that need to act on this data (hedging engines, risk monitors, order management) read from MongoDB because they need consistency and low latency on recent writes.
ClickHouse is where data goes to be analysed. Once trades are settled, once positions are snapshotted, once market data has been captured — it flows into ClickHouse where it can be sliced, aggregated, and joined at scale. The queries here are broad, not deep: “What was the average slippage across all desks last week?” rather than “What is Desk A’s current net position?”
In practice, data flows in one direction: MongoDB → ETL → ClickHouse. Some data also arrives in ClickHouse independently — raw exchange feeds, derived metrics from processing pipelines — but the transactional core always starts in MongoDB.
The Cost of Two Databases
Running two databases isn’t free. There’s operational overhead — separate monitoring, separate backup strategies, separate upgrade cycles. Schema management across both systems requires discipline. The ETL pipeline between them is another component that can fail, lag, or drift.
But the alternative — forcing one system to do both jobs — would be worse. A single database optimised for transactions would buckle under analytical query load. A single database optimised for analytics would provide unacceptable consistency guarantees for hedging.
Two databases is a complexity tax we pay willingly, because the failure modes of the alternative are more expensive.
What I’ve Learned
Appreciate HA before you need it. High availability setups feel like over-engineering — until a node goes down during market hours and production keeps running. The first time you see it work is the last time you question it.
Consistency has a cost, and it’s not always obvious. MongoDB will protect your data’s correctness at the expense of availability. That’s the right default for transactions, but it means every administrative operation needs to be evaluated for its impact on the write path.
Let each system do what it’s good at. ClickHouse is exceptional at analytical queries over large datasets. MongoDB is reliable for transactional consistency. Trying to stretch either beyond its design point creates problems that are harder to diagnose than the complexity of running both.
The boundary is a feature, not a bug. Having a clear line between “live state” and “historical analysis” forces you to think carefully about data flow, freshness requirements, and consistency guarantees. That clarity makes the overall system easier to reason about, even if it’s more components to operate.
Two databases, two sets of tradeoffs.