December 2024 Summaries

Monitoring your database system in a production environment is crucial to prevent or solve outages, and ClickHouse provides an Advanced Dashboard that offers real-time visibility into its performance and health. The dashboard is available by default in both OSS (Open Source Software) and Cloud deployments, allowing users to monitor metrics such as queries per second, selected rows per second, inserted rows per second, and more. Users can customize the dashboard's visualizations using SQL queries, add custom charts, and explore out-of-box visualizations for ClickHouse-specific, system health, and Cloud storage-related metrics. By leveraging the Advanced Dashboard, users can identify issues such as unbatched inserts, resource-intensive queries, and bad primary key designs, enabling them to take proactive measures to improve their deployment's performance and overall reliability.

At ClickHouse, a single Support Services organization provides all support, chat, community, learning, consultative support, and professional services for both open-source and Cloud customers, aiming to share knowledge and help users achieve their results quickly and fully near real-time. The team addresses long-standing problems in the technical industry by providing seamless experience from trial to production, accessible knowledge tools, and advocating for user needs through product roadmap insights. Proactive support is also a focus, with monitoring and alerting systems in place to notify users of potential issues before they become major problems.

The December issue of the ClickHouse newsletter covers various topics including query optimization, SQL-based observability with Grafana, and the introduction of new features such as parallel hash join and vector similarity indexes. The newsletter also announces upcoming events, product updates, and community contributions, including a featured community member Azat Khuzhin who has been working on performance optimization and feature enhancements across various components. Additionally, there are resources available for training and learning about ClickHouse, including free virtual training sessions and the upcoming ClickHouse User Conference in San Francisco.

Gumlet, a media hosting and streaming solution, handles over 2 billion video and image requests daily, serving more than 100 million users per day. The platform tracks various metrics like playback time, rebuffer rates, and bandwidth usage to fine-tune performance and keep users engaged. However, managing insights across 900 billion rows of data became a costly and complex challenge as the company scaled. Initially relying on BigQuery, Gumlet faced issues with spiraling costs, query limits, and storage costs due to its pricing model. After evaluating alternatives, they opted for ClickHouse Cloud, which offered scalable storage, predictable costs, and excellent performance. The migration required redesigning their ingestion process, taking advantage of built-in integrations, and using materialized views to pre-aggregate data for common analytics requests. By migrating to ClickHouse, Gumlet achieved cost predictability, improved ingestion speed, rapid response times, and simplified operations.

The Medallion architecture is a widely used data workflow that organizes data into three layers: Bronze, Silver, and Gold. The Bronze layer acts as an entry point for raw, unprocessed data, optimized for high-throughput ingestion using ClickHouse's flexible and performant constructs. It serves as a historical archive, preserving raw data for lineage, debugging, or reprocessing without requiring complete cleansing or deduplication upfront. The Silver layer transforms raw data from the Bronze layer into a more consistent and well-structured form, addressing data quality issues such as filtering out invalid rows, standardizing schemas, and performing transformations. The Gold layer represents the final stage in the Medallion architecture, where data is curated into fully denormalized, business-ready datasets optimized for consumption by end-user applications and analytics. With ClickHouse, each layer can be implemented using native constructs, eliminating the need for external frameworks or tooling, and providing a flexible and evolving approach to managing data pipelines through tiered transformations.

The author, who works on the product marketing engineering team at ClickHouse, shares their experience with optimizing query performance using the database's built-in tools and data schema changes. They start by identifying slow queries using query logs and then use tools like EXPLAIN indexes = 1 to analyze query execution plans. The author discusses various optimization techniques, including disabling the filesystem cache, reducing nullable columns, making best use of LowCardinality data type, optimizing data types, and choosing primary keys that filter in most queries. By applying these optimizations, they demonstrate significant improvements in query performance, with some changes resulting in a 34% reduction in disk space for one table. The author concludes by emphasizing the importance of understanding how ClickHouse works and utilizing its features to unlock full performance potential.

ClickHouse, Inc., a leader in real-time analytics, has signed a five-year strategic collaboration agreement with Amazon Web Services (AWS) to accelerate innovation in real-time data warehousing, observability, business intelligence, machine learning, and generative AI solutions. The partnership will expand on ClickHouse’s relationship with AWS and introduce new integrations between ClickHouse Cloud and various AWS services, making it easier for customers to build and manage high-performance analytics and generative AI applications. Additionally, the collaboration will enable organizations to harness the power of ClickHouse's real-time analytics engine alongside AWS services to drive transformational outcomes in industries such as finance, e-commerce, and gaming. The two companies will work together on joint go-to-market activities to empower enterprises with advanced tools to extract actionable insights from their data at scale.

2024年12月のClickHouseニュースレターは、クエリ最適化ガイド、SQLベースのオブザーバビリティの実例、Amazonのre:Inventカンファレンスにあわせた新製品アナウンス、Postgres CDCコネクタ（ClickPipes向け）のプライベートプレビュー開始など、多彩なトピックを掲載しています。今月はグローバルイベントのリリースカール、無料トレーニング、EMEAおよびAPACでのイベントが予定されています。また、SemrushのリードエンジニアであるAzat Khuzhinさんが注目されることがわかります。 24.11リリースは並列ハッシュ結合がデフォルトの結合戦略になったことや、マークキャッシュの事前ウォーム機能、ベクトル検索向けBFloat16データ型、WITH FILLで使えるSTALENESS修飾子などが追加されたものです。 ClickHouse Playgroundでのサンプルクエリのパフォーマンスを向上させたいと思ったLionel Palacinさんが、ClickHouseクエリ最適化のシンプルガイド：パート1を発表しました。

ClickHouse version 24.11 introduces nine new features, fifteen performance optimizations, and sixty-eight bug fixes. The parallel hash join algorithm is now the default join strategy, with a performance optimization using zero-copy instead of copying block columns each time. A STALENESS modifier for ORDER BY WITH FILL has been added to generate rows until the difference from the previous row exceeds a specified numeric expression. The HTTP interface can now reliably detect errors even after the result has been streamed to the client, and the mark cache can be pre-warmed using the mark_cache_prewarm_ratio setting. Additionally, the BFloat16 data type is available for AI and vector searches.

- Designing an efficient schema is crucial for handling large volumes of data in product analytics. - Materialized views can be used to pre-compute complex queries and improve query performance. - Integrating external datasets like billing or Google Analytics data can provide a more comprehensive view of user behavior. - Building your own product analytics stack offers flexibility and control, but requires expertise in database management, data modeling, and SQL.

In 2021, Cayden Meyer led Canva's enterprise, education, and teams efforts. He identified opportunities for conversion, expansion, and retention but found no solution to help him easily focus on these areas. This led him to develop Upollo, a company that predicts who is likely to churn, convert, or expand and why. Upollo serves predictive insights to businesses to help them grow faster and build more effective products. Upollo's mission and offering require the processing and analysis of billions of events, including clicks, page views, billing events, and support interactions. As the company grew, its existing data warehouse, BigQuery, became too costly and inefficient. To keep growing and delivering real-time insights at scale, they needed a more efficient, scalable, and cost-effective solution, which led them to ClickHouse Cloud. Upollo's team established several requirements for their new solution, including affordability, support for real-time data serving without complex workarounds, performance reliability, and an active community backing the platform. They also sought a managed service that would eliminate the need for maintaining the database themselves. After researching alternatives, Upollo decided to give ClickHouse Cloud a shot. The migration was completed in just 30 minutes, and the impact has been significant. ClickHouse's performance made queries up to 20 times faster, unlocked capabilities that were previously unattainable, and simplified Upollo's data architecture by consolidating storage, processing, and serving in one platform. Despite some initial challenges, such as managing memory to avoid out-of-memory (OOM) issues during large queries, Upollo has seen significant improvements in cost efficiency and performance since adopting ClickHouse Cloud. The company plans to continue deepening its relationship with ClickHouse by migrating additional workloads and taking advantage of new features like inverted indexes.

At AWS re:Invent, ClickHouse announced several product updates including Bring-Your-Own-Cloud deployment for AWS (Beta), Postgres CDC connector in ClickPipes (Private Preview), AWS PrivateLink and VPC Lattice (Launch Partner), Dashboards (Beta), Query API Endpoints (GA), Native JSON support (Beta), and Vector search using vector similarity indexes (Early Access). These updates aim to enhance the performance, scalability, and security of ClickHouse Cloud while making it easier for users to integrate with other systems.

ClickPipes now supports AWS's new method for providing private, unidirectional connectivity to individual data sources using AWS PrivateLink and VPC Lattice. This feature allows users to share resources across VPCs and accounts without exposing other resources in the VPC. ClickPipes enables continuous data ingestion from various external data sources like Amazon MSK, S3, or Kinesis, setting up robust and scalable data pipelines quickly and efficiently. With this capability, users can connect their individual resources across VPC and account boundaries or even from on-premise networks with the privacy and security benefits of AWS PrivateLink.