Scaling a Caching System: Migrating from PostgreSQL to ClickHouse for Analytical Workloads

The Challenge: PostgreSQL's Limitations with Growing Analytical Cache

Momentic, an AI-driven software testing platform, faced severe performance and scalability issues with its caching system built on PostgreSQL. As the cache table grew from 80,000 to over a billion entries, PostgreSQL struggled with a high-write, high-read workload, leading to elevated resource usage and significant lock contention. This scenario is common when attempting to use a row-oriented, general-purpose relational database for analytical-heavy caching or data warehousing use cases that demand extreme scale and read performance.

Why ClickHouse: Leveraging Columnar Storage for Scale

The decision to migrate to ClickHouse was driven by its column-oriented architecture, which is inherently optimized for analytical queries and high-volume data ingestion. Unlike PostgreSQL's B-tree indexes where query costs can grow with data size, ClickHouse uses sparse primary indexes. This allows it to efficiently narrow searches to a small number of "granules" (data parts) when key values are known, significantly improving efficiency at scale. This fundamental difference is crucial for systems dealing with large datasets and needing fast aggregations or lookups across many rows.

Key Architectural Adaptations for ClickHouse

• Primary Key Design: A carefully designed primary key (test ID, step ID, Momentic version, git branch, commit timestamp) was essential to leverage ClickHouse's sparse indexes for efficient lookups, especially for the 90% of queries targeting feature branches.
• Materialized Views for Outliers: For "main branch" queries that would otherwise access a large number of data parts, materialized views were used to precompute available commit timestamps per test ID. This allowed direct jumps to relevant data parts, mitigating spiky memory usage and disk operations.
• Optimized Write Patterns with ReplacingMergeTree: Instead of multiple queries, Momentic switched to a single INSERT pattern combined with ClickHouse's `ReplacingMergeTree` table engine. This asynchronously deduplicates entries, effectively handling TTL extensions and new cache inserts. This simplification even allowed the elimination of a Redis caching layer, which had become redundant.
• Migration Strategy: A dual-write approach was employed, writing to both PostgreSQL and ClickHouse, with shadow querying and diffing of results to ensure correctness before gradually shifting production traffic. Dual writes were maintained post-cutover for safe rollback capability.

By making these architectural changes, Momentic successfully scaled its caching system to 20 billion entries, handling over two million queries daily with an average resolution latency of 250 ms, demonstrating a robust solution for high-volume, analytical-style caching.