Switching from PostgreSQL to ClickHouse for Improved Performance and Scalability

2026-07-07 · Source: InfoQ · Field: Technology & Digital — Software Development & Engineering, Artificial Intelligence & Machine Learning, Cloud Computing & IT Infrastructure · Depth: Intermediate, quick

Summary

Momentic, an AI-driven software testing platform company, rearchitected its caching system by migrating from PostgreSQL to ClickHouse to address performance limitations. The cache table grew from 80,000 to 1 billion entries, causing high resource usage and lock contention in PostgreSQL. By transitioning to the column-oriented ClickHouse, Momentic scaled its cache to 20 billion entries, handling over 2 million queries per day with an average response latency of 250 ms. Key to this improvement was ClickHouse's sparse primary indexes, which allowed efficient lookups when Momentic designed a primary key incorporating test ID, step ID, Momentic version, git branch, and commit timestamp. They also used materialized views to optimize main branch queries and simplified cache updates to single INSERTS with ReplacingMergeTree, eliminating a Redis layer. The migration involved dual writes and shadow querying for correctness.

Key takeaway

For MLOps Engineers or Data Architects struggling with PostgreSQL performance in high-volume caching systems, consider ClickHouse as a scalable alternative. Its column-oriented architecture and sparse indexes can significantly improve query latency and handle billions of entries. You should design primary keys carefully and leverage materialized views to optimize complex lookups. This transition can eliminate auxiliary caching layers like Redis, simplifying your infrastructure while maintaining sub-second response times.

Key insights

Migrating from PostgreSQL to ClickHouse significantly scales high-volume caching systems by leveraging column-oriented architecture and sparse indexes.

Principles

• ClickHouse's sparse indexes offer superior scalability for high-cardinality lookups.
• Materialized views can optimize queries on large, frequently accessed datasets.
• Asynchronous deduplication simplifies cache management and reduces query complexity.

Method

Migrate by dual-writing to both old and new databases, shadow querying the new system, diffing results for correctness, then gradually shifting production traffic.

In practice

• Design primary keys to align with common query patterns for sparse index efficiency.
• Use ClickHouse's ReplacingMergeTree for efficient, asynchronous cache deduplication.
• Implement materialized views to precompute data for complex, high-volume queries.

Topics

• PostgreSQL
• ClickHouse
• Database Migration
• Caching Systems
• Columnar Databases
• Performance Optimization

Best for: AI Architect, MLOps Engineer, Software Engineer

Related on AIssential

• 10 GitHub Repositories for Modern Database Systems and Tools — Modern database ecosystems extend beyond storage, offering specialized open-source tools for diverse application needs from analytics to AI agent memory.
• Why Your Data Pipeline Is Slow: A Practical Guide to Indexes — Indexes align logical query intent with physical data access, preventing PostgreSQL from doing unnecessary work at scale.
• Nextdoor’s Database Evolution: A Scaling Ladder — Nextdoor's database architecture evolved from a single PostgreSQL instance to a sophisticated distributed system to support millions of users, addressing scaling challenges through a disciplined progr…
• Protect Performance and Reduce Surprise Costs with Default Warehouse — Default Warehouse automates SQL warehouse selection for ad hoc queries, optimizing cost and performance.
• How I Engineered a 1.7M Row Product Safety Database to Serve Pages in 66ms on a Single Free VM — Optimizing large-scale database performance and cost on minimal infrastructure requires strategic data management and query pattern alignment.
• Why Anthropic, Meta, and Tesla All Chose the Same Database | Aaron Katz, ClickHouse — ClickHouse's success stems from its open-source foundation, developer-first cloud strategy, and focus on real-time analytics.

Open in AIssential →

Editorial summary, takeaway, and curation by AIssential. Original article published by InfoQ.