Momentic, the company behind an AI-driven software testing platform, recently rearchitected its caching system to handle over 2 million queries per day across 20 billion total entries, while maintaining an average response latency of around 250 ms. This improvement was made possible by transitioning from PostgreSQL to the column-oriented database ClickHouse.
The primary driver for adopting a new caching architecture was the rapid growth of the cache table from approximately 80,000 to 1 billion entries, which began to expose PostgreSQL’s performance limitations:
Because of the high-write, high-read workload, we were running into both elevated resource usage as well as lock contention from queries trying to read and write to the cache concurrently, and as we increased the number of entries by several orders of magnitude, this only became worse.
When Momentic decided to migrate to ClickHouse, it was handling roughly 600,000 cache lookups per day with a requirement of sub-second latency. A key architectural advantage ClickHouse offered over PostgreSQL was its use of sparse primary indexes rather than B-tree indexes. In PostgreSQL, query costs tend to grow with data size, whereas ClickHouse can narrow searches to a small number of “granules” when key values are known, significantly improving efficiency at scale.
By carefully designing its primary key, incorporating test ID, step ID, Momentic version, git branch, and commit timestamp, Momentic was able to achieve this efficient lookup behavior in 90% of cases, namely with feature branches. However, main branches required accessing a potentially huge number of entries:
This meant that most queries were reading 1-2 data parts, but some outliers were reading almost all of the parts on every query, leading to spiky memory usage and disk operations.
To address this limitation, Momentic leveraged materialized views to precompute available commit timestamps per test ID, enabling them jump straight to the right data part.
Another significant change was reducing the number of queries for every test run, as the three separate queries used in PostgreSQL did not play well with ClickHouse.
Instead we switched to using only INSERTS combined with ClickHouse’s ReplacingMergeTree: SELECT to get the caches, re-INSERT the caches that were used to extend TTL, INSERT the new caches after the test run, and let ClickHouse take care of deduplicating entries asynchronously. This was such an improvement that we were able to fully eliminate the Redis layer, which at this point had limited value due to the higher cache key cardinality.
To carry through the migration, Momentic ran ClickHouse alongside PostgreSQL, duplicating all writes to both systems. Production traffic continued to be served from PostgreSQL while ClickHouse was "shadow queried", but results from both databases were diffed to ensure correctness. Once Momentic gained confidence in the new setup, traffic was gradually shifted from PostgreSQL to ClickHouse. Even after the cutover, dual writes were maintained for a period to allow for a safe rollback if needed.
As a result of this rearchitecture, Momentic was able to scale its cache to 20 billion entries, handling over two million queries per day while maintaining an average resolution latency of around 250 ms. For a deeper dive into the migration and its design decisions, refer to the original article.