Momentic's Leap: PostgreSQL to ClickHouse for 20B Entries

Scaling Caching with ClickHouse

Momentic's successful migration from PostgreSQL to ClickHouse for their AI-driven software testing platform's caching layer highlights a common pain point for rapidly scaling applications: the limitations of traditional relational databases under high-throughput, high-cardinality workloads. The key insights revolve around ClickHouse's columnar architecture and its sparse primary index implementation, which drastically outperform PostgreSQL's B-tree indexes for analytical-style queries and massive datasets. The article effectively illustrates how careful primary key design, coupled with materialized views and optimized write patterns (using ReplacingMergeTree and eliminating the Redis layer), unlocked significant performance gains, allowing them to handle 20 billion entries and over 2 million daily queries with sub-300ms latency. The phased migration strategy, involving dual writes and shadow querying, is also a commendable practice for ensuring data integrity and a safe transition.

However, while the article showcases impressive results, it implicitly points to the complexity and specialized knowledge required for such a migration. The reliance on specific ClickHouse features like ReplacingMergeTree and materialized views, along with the nuanced understanding of its data part management and primary key implications, means this solution isn't a simple drop-in replacement for all PostgreSQL use cases. The article could benefit from a more explicit discussion on the operational overhead of managing ClickHouse, especially concerning its distributed nature and potential tuning complexities compared to a more mature relational database like PostgreSQL. Furthermore, the article focuses heavily on read performance for the cache; a deeper dive into write performance characteristics under extreme load, beyond the deduplication aspect, would offer a more complete picture. The decision to eliminate Redis entirely, while beneficial for Momentic's specific cache key cardinality, might not be universally applicable and could be a point of contention for systems that leverage Redis for its broader feature set beyond simple key-value storage.

Key Points

• Momentic migrated their caching system from PostgreSQL to ClickHouse to handle massive data growth (80,000 to 1 billion entries) and high query volumes (over 2 million per day).
• The primary drivers for the migration were PostgreSQL's performance limitations under high-write, high-read workloads, including elevated resource usage and lock contention.
• ClickHouse's columnar architecture and sparse primary indexes (compared to PostgreSQL's B-tree) significantly improved lookup efficiency at scale.
• Momentic optimized primary key design (test ID, step ID, version, branch, commit timestamp) for efficient lookups, using materialized views to handle outliers on main branches.
• The write strategy was rearchitected to use ReplacingMergeTree for deduplication and TTL extension, eliminating the need for a separate Redis layer.
• A careful, phased migration with dual writes and shadow querying ensured data integrity and a safe cutover to ClickHouse.
• The result was a scalable cache handling 20 billion entries with an average latency of around 250 ms.

---

📖 Source: Switching from PostgreSQL to ClickHouse for Improved Performance and Scalability