March 2025 Summaries

Poizon`, a Chinese ecommerce platform, faced challenges in scaling its infrastructure to handle the growing demand of its luxury goods marketplace. The platform's original self-hosted setup struggled with cost pressures, poor scalability, and limited disaster recovery capabilities. To address these issues, Poizon turned to `AliCloud ClickHouse Enterprise Edition (also known as ClickHouse Cloud)`, a cloud-based data warehouse solution that offers a storage-compute separation architecture, serverless computing model, and advanced features like SharedMergeTree engine. By adopting this new platform, Poizon has achieved write speeds of up to 20 million rows per second, reduced infrastructure costs by 60%, and improved the scalability and resilience of its observability system. The company's decision to rebuild its data infrastructure with ClickHouse Cloud has enabled it to meet customer expectations and be a force in the luxury ecommerce market for years to come.

The query condition cache is a new feature in ClickHouse that improves performance by caching information about which parts of the data matched (or didn't match) a given filter. This allows ClickHouse to skip large portions of data during repeated query execution, even if the overall query shape changes. The cache operates at the granule level and can be used with selective filters to significantly speed up queries. By reusing the same predicate across multiple queries, users can benefit from the query condition cache without having to manually manage it. The feature is memory-efficient and can store a large number of granule entries, making it suitable for repeated queries with selective filters. With the query condition cache enabled, ClickHouse can achieve faster performance and reduced memory usage compared to running the same query without caching.

The ClickHouse version 25.3 release brings several new features, including query support for AWS Glue and Unity catalogs, a new query condition cache, automatic parallelization when querying S3, and new array functions such as `arraySymmetricDifference`. The JSON data type is now production-ready, providing unmatched performance, compression, and usability compared to existing JSON data stores. Additionally, the release includes improvements in data compression, query acceleration, and scalability, making it an exciting update for ClickHouse users.

The legal industry is being revolutionized by Harvey, a generative AI platform that automates manual tasks such as contract analysis and due diligence, allowing lawyers to focus on high-value work. To protect sensitive client information, Harvey's security team uses network logs for scalable, efficient threat detection with the help of ClickHouse Cloud. The massive volume of daily log data was challenging to process in real time, but ClickHouse's compression capabilities and query efficiency enabled faster processing. The enriched data provides a clear snapshot of network activity, allowing the security team to detect threats with greater confidence and precision. With this new architecture, Harvey can scale securely, giving peace of mind to internal teams and customers, and stay ahead of evolving threats, ultimately improving the efficiency and productivity of the legal profession.

The key to fast queries at scale in real-time dashboards is ensuring that all dashboard queries operate on small, stable, and pre-aggregated data. This can be achieved through the use of incremental materialized views, refreshable materialized views, and in-memory dictionaries. These techniques allow for efficient updates and lookups without scanning the full dataset, resulting in significant speedups and memory reductions. By applying these optimizations to real-time dashboard scenarios, it's possible to achieve sustained performance at any scale, even with billions of JSON documents.

Longbridge Technology, a next-generation social-driven online brokerage firm based in Singapore, has re-architected its market data services to leverage ClickHouse for improved performance, scalability, and cost-effectiveness. The company initially used a complex architecture with PostgreSQL, Redis, DynamoDB, and Aliyun's OSS object storage, but faced challenges such as high storage costs, poor write performance, and complexity in ETL operations. After evaluating several time-series databases, including ClickHouse, TimescaleDB, and TDengine, Longbridge chose ClickHouse for its compatibility with PostgreSQL, outstanding performance advantages, and cloud-native operational benefits. The company conducted performance testing on ClickHouse's bulk data insertion capabilities using 13 years of A-share market data and found significant improvements in write latency. After migrating historical data from DynamoDB, PostgreSQL, and Redis to ClickHouse, Longbridge unified its data storage solution across all markets, reducing storage costs by 4x and improving write performance by 10x. The new architecture also simplified the technology stack, reduced development and operational pressure, and brought about substantial performance improvements, making it an ideal choice for the company's market data business.

The March issue of the ClickHouse newsletter is now available. In this month's newsletter, we have several exciting announcements and updates, including the public beta launch of the Postgres CDC connector for ClickPipes, general availability of Bring Your Own Cloud on AWS, and more. We also have a featured community member spotlight on Matteo Pelati, Co-Founder at LangDB, who shares his experience with ClickHouse and its integration with AI gateway capabilities. Additionally, we've got tips and guides on various topics such as building a data warehouse for contact center analytics, visitor segmentation with Theta Sketches, and more. The newsletter also covers upcoming events like Open House in San Francisco, global events, free training sessions, and regional meetups. Furthermore, ClickHouse 25.2 has been released, delivering performance gains for joins, Parquet Bloom filters, a new backup database engine, and integration with the Delta Rust Kernel. We've also got quick reads on various topics, including building a scalable real-time data pipeline, migrating from Grafana's Loki to ClickHouse, and using ClickHouse's theta sketches for visitor segmentation.

We are thrilled to announce that ClickHouse has acquired HyperDX, a fully open-source observability platform built on top of ClickHouse. This acquisition reinforces our commitment to delivering the fastest and most cost-effective observability platform to developers and enterprises worldwide. By integrating HyperDX's powerful UI and session replay capabilities with ClickHouse’s industry-leading database performance, we’re taking open-source observability to the next level. Observability is fundamentally a data problem, and ClickHouse has been the backbone of observability platforms for years, powering logging, metrics, and tracing solutions at companies like eBay and Netflix or at observability startups like Sentry and Instana. However, a database alone doesn’t make an observability solution; engineers need turnkey data ingestion and visualization tools that "just work." ClickHouse has relied on OpenTelemetry for data collection and an integration with Grafana for visualization to achieve this, but saw room for improvement in streamlining workflows and adding comprehensive features. That's when we found HyperDX, an open-source observability layer built on ClickHouse, which brought together an enhanced query experience with a more intuitive UI for exploratory observability workflows. The acquisition is an acceleration, not a disruption, and the HyperDX team is joining ClickHouse to build the future of open-source observability, with plans to expand the roadmap to bring even more powerful tools to engineers.

ClickHouse has developed a Bring Your Own Cloud (BYOC) offering that allows customers to deploy ClickHouse in their own cloud infrastructure, specifically on AWS. This deployment model provides a balance between autonomy and convenience, enabling customers to maintain control over their networking, security, and compliance while benefiting from the flexibility and scalability of cloud platforms. The BYOC model tackles key challenges such as infrastructure automation, network security, data isolation, and resource management, ensuring a seamless and secure deployment experience. Customers can easily onboard and manage ClickHouse instances within their VPCs using auto-provisioning mechanisms, with the separation of management and data planes ensuring security and compliance. The approach also optimizes resource management within customer-owned EKS clusters, providing efficient resource utilization while maintaining performance. With scheduled upgrade support and a Kubernetes Upgrader for managed EKS maintenance, customers have control over their infrastructure while minimizing operational overhead. BYOC empowers customers with flexibility, security, and scalability, giving them complete control over their cloud infrastructure while offloading operational complexities to ClickHouse.

Tony Burke, a seasoned software engineer, joined SolarWinds in 2023 with nearly three decades of experience solving complex technical issues and architecting solutions for cloud and SaaS companies. At SolarWinds, he faced unique challenges in managing the scale and high-stakes nature of its data operations, processing 3 million telemetry messages per second, averaging 550 megabytes per second, and peaking at bursts of a gigabyte. To tackle these challenges, Tony turned to ClickHouse, fine-tuning their system and optimizing queries for time-sensitive metrics, building a data platform capable of scaling with speed and precision. He identified key areas for optimization, such as reducing index granularity, which led to significant improvements in query performance, including a 60% reduction in query times. However, this came with tradeoffs, including increased memory usage and merge times. To balance gains and tradeoffs, Tony also optimized filesystem reads by switching from the default `pread_threadpool` setting to `pread`. SolarWinds adopted the Bring Your Own Cloud (BYOC) deployment model on AWS to streamline management of its ClickHouse deployments, ensuring greater control over security, network configurations, and data compliance. By leveraging ClickHouse's shared-storage architecture with Amazon S3 for durable storage, SolarWinds can independently scale compute resources, resulting in infrastructure cost savings and simplified maintenance. Ultimately, ClickHouse is the foundation of SolarWinds' real-time observability platform, empowering IT teams to act with speed, precision, and confidence.

Astronomer, a fully managed platform, helps organizations run, scale, and optimize Apache Airflow without the operational burden. Similar to Confluent and Databricks, Astronomer created Astro to provide mission-critical analytics, AI, and software capabilities. With Astro Observe, Astronomer offers a proactive observability solution that quickly identifies and fixes failures, reducing downtime and freeing engineers from endless troubleshooting cycles. The platform uses ClickHouse Cloud as its database, which handles billions of workflow events with low query latency and minimal maintenance. This enables Astronomer to provide a top-notch observability experience without managing database infrastructure. By leveraging ClickHouse's scalability, flexibility, and speed, Astronomer simplifies data management and improves query performance, allowing customers to keep their Airflow environments running smoothly as their data pipelines become increasingly complex.

ClickHouse version 25.2 introduces 12 new features, 15 performance optimizations, and 72 bug fixes. The release includes improved parallel hash join performance, Parquet bloom filter writing, transitive condition inference for queries, a backup database engine, delta Rust kernel integration, and more. A special welcome is extended to the new contributors who have made significant contributions to ClickHouse's community growth. The version also sees improvements in join performance, including faster parallel hash joins, which can fully utilize all CPU cores and complete faster. Additionally, Bloom filters are introduced when writing Parquet files, leading to a 30-40% speed improvement for certain queries. Transitive condition inference is also supported, allowing the query engine to automatically deduce and utilize additional conditions to enhance filtering efficiency. A backup database engine is introduced, enabling users to instantly attach tables/databases from backups in read-only mode. Delta Lake support with the help of a library from Databricks is implemented, although it's still in experimental mode. New formats for HTTP event streaming are also added, including JSONCompactEachRowWithProgress and JSONCompactStringsEachRowWithProgress, which stream newline-delimited JSON events as soon as they appear.

Replicating data from PostgreSQL to ClickHouse using Postgres CDC can be an efficient way to scale business, enabling real-time analysis of large datasets. To achieve this, customers often use Change Data Capture (CDC) to continuously track inserts, updates, and deletes in Postgres and replicate them to ClickHouse. This allows for real-time analytics and reduces the load on PostgreSQL. However, managing duplicate rows and optimizing query performance is crucial. Strategies such as using Refreshable Materialized Views, denormalization, and custom ordering keys can help with deduplication, JOINs queries, and query optimization. By leveraging these best practices, customers can maximize ClickHouse's speed and scalability, and get the most out of Postgres CDC integration.

A mysterious CPU spike in ClickHouse Cloud on GCP led to months of debugging, revealing a deeper issue within the Linux kernel's memory management. The investigation began with an occasional hiccup in ClickHouse Cloud infrastructure that engineers struggled to explain. Initially, it seemed like a random performance degradation issue, but further analysis revealed a hidden livelock caused by excessive contention on the `mmap_lock` spinlock. The lock was held for an exceptionally long time during page fault handling, scanning 1,093,267 pages in an effort to reclaim memory for the cgroup. This led to a situation where nearly all pages were activated, but only 32 pages were successfully reclaimed. A new bug was later discovered, which involved a spinlock called `lru_lock` protecting the struct `lruvec`. The fix for this issue involved enabling the Multi-Gen LRU (MGLRU) mechanism, which is designed to improve memory management and reduce contention on the spinlock. This change resolved the issue and improved overall system performance.

The ClickHouse Cloud Billing API has been released, providing programmatic access to billing and usage data. This allows teams to track and manage costs more efficiently, integrate with observability tools, automate cost reporting for finance teams, and enable usage-based billing for downstream customers. The new API is showcased through an integration with Vantage, a leading cloud cost management platform, which has demonstrated the art of the possible by transforming raw billing data into actionable insights that help teams gain deeper cost visibility and optimize analytics spend. The integration provides users with full visibility into ClickHouse Cloud costs, enabling them to avoid challenges such as inefficient compute scaling, new services unknowingly spun up, and growing data egress fees. With this integration, teams can now automatically ingest and visualize ClickHouse costs directly in Vantage, alongside multiple cloud provider integrations, and use existing features to enable cost alerts, create business metrics on top of ClickHouse Cloud costs, and include ClickHouse costs in cost reports.

Plushcap here, summarizing the text for you. Poolside, a company co-founded by Eiso Kant and Jason Warner, aims to redefine software development by bridging the gap between human and machine intelligence. They're on a mission to empower developers to be more productive and creative while tackling humanity's greatest challenges. To achieve this, they've turned to ClickHouse Cloud, a database solution that enables them to iterate faster, explore new approaches, and refine their AI models with greater precision. By leveraging ClickHouse, Poolside has streamlined their data architecture, making it more scalable and efficient. This transformation allows the team to focus on analyzing data, refining models, and pushing the boundaries of AI at scale. With ClickHouse by their side, Poolside is redefining what's possible in AI and software development, aiming to lower the barrier to entry for wider audiences.