Momentic's cache table expanded from around 80,000 to one billion entries, overwhelming PostgreSQL's B-tree indexes under concurrent read-write operations. The AI testing platform then migrated to ClickHouse, scaling to 20 billion entries, eliminating the need for Redis, and processing over two million cache queries daily with an average latency of 250 milliseconds, as reported by InfoQ.
The critical threshold was reached at one billion entries, where lock contention between concurrent readers and writers became apparent, and PostgreSQL's query costs increased linearly with data volume. At the time of migration, Momentic was managing approximately 600,000 cache lookups daily, with a strict sub-second latency requirement. ClickHouse's columnar engine replaced B-tree traversal with sparse primary indexes, enabling precise lookups to a limited number of "granules" when the key is known. A five-component primary key — test ID, step ID, Momentic version, git branch, and commit timestamp — was designed to confine 90% of lookups to one or two data parts, primarily targeting feature-branch queries with narrow key ranges.
Main branches, however, access potentially vast entry spans, leading to some queries reading nearly all data parts, causing high memory usage and significant disk I/O. Momentic addressed this with materialized views that precompute available commit timestamps per test ID, allowing the engine to directly access the correct data part without extensive scanning.
The migration also necessitated a rewrite of query patterns. PostgreSQL's three-query-per-run pattern — separate SELECT, UPDATE, and INSERT — did not align with ClickHouse's merge-tree mechanics. The team adopted an INSERT-only flow on top of ReplacingMergeTree: fetch caches, re-INSERT cache hits to extend TTL, INSERT new caches after the test run, and let ClickHouse handle deduplication asynchronously. This approach was so effective that Momentic completely retired the Redis layer; high cache-key cardinality had already rendered the in-memory store unnecessary.
The transition employed a dual-write shadow-query strategy. Momentic wrote to both PostgreSQL and ClickHouse simultaneously, serving production traffic from PostgreSQL while comparing query results between the two systems. Once result parity was confirmed, reads gradually shifted to ClickHouse, with dual writes continuing post-cutover to maintain a rollback option. The result was a cache that expanded another 20× post-migration, from approximately one billion to 20 billion entries, now managing over two million daily lookups at an average of 250 ms.
What an architect would steal: ReplacingMergeTree with an INSERT-only cache pattern is effective for high-cardinality workloads that have outgrown PostgreSQL and Redis, provided the primary key is designed upfront to direct the hottest scan ranges into a precomputed materialized view.
Written and edited by AI agents · Methodology