May 2024 Summaries

Prefect's orchestration and observability platform helps developers build and understand their data pipelines, enabling them to react to failures. The company is focusing on an event-driven architecture and has integrated ClickHouse into its product suite for enhanced workflow observability. Prefect aims to provide more comprehensive metrics and triggers, allowing users to fix errors at a larger scale. The adoption of ClickHouse has simplified operations, increased resilience, and reduced costs from $12,000 per month to less than $8,000 per month.

Lago, an open-source usage-based billing platform, switched from a Postgres-only database stack to a hybrid one with ClickHouse for streamed events and Postgres for other data needs. The decision was made due to the heavy load times experienced as they began ingesting millions of events per minute. They chose ClickHouse because it is an OLAP (online analytical processing) database designed for efficiently answering complex read queries with approximate accuracy and batch processing a large number of write queries. Lago's performance improved significantly after moving to ClickHouse, with some main queries showing improvements of up to 137x. Other open-source projects that have made the switch from Postgres to ClickHouse include PostHog and GitLab.

The May ClickHouse newsletter highlights various updates in real-time data warehouses over the last month. Key points include the introduction of recursive CTEs in the 24.4 release, the launch of ClickHouse developer certification, and real-time fraud detection at Instacart using ClickHouse as a primary datastore. Other topics covered are K-Means clustering with ClickHouse, simplified Kubernetes logging with Fluentbit and ClickHouse, the new building blocks of observability, and using ClickHouse for financial charts. The newsletter also features Dan Goodman as the community member of the month and his blog post on finding trends with approximate embedding clustering.

ClickHouse has expanded its cloud analytics platform by making it available on Microsoft Azure. This move enhances the company's commitment to providing accessible and flexible options for running data analytics at scale in the cloud. Users can choose from two deployment options - Production and Dedicated, each with different features and capabilities. The Public Beta release offers end-to-end encryption, Azure Blob Storage integration, strict endpoint security, single sign-on (SSO), compliance coverage, and setup automation. ClickHouse also provides a redesigned Cloud Console for easier usage and an extensive catalog of integrations with various data onboarding, transformation, visualization, language clients, and SQL clients. The General Availability of ClickHouse Cloud on Azure is expected to be announced soon, including Azure Marketplace Subscription and Service Level Agreements (SLAs).

ClickHouse has introduced Automatic Query Endpoints, a feature that enables developers to create APIs directly from saved SQL queries in the ClickHouse Cloud Console with just a few clicks. This feature aims to provide an easy way for front-end and microservices to consume data returned by queries. The new tooling is part of ClickHouse's efforts to improve the developer experience around programmatic data consumption, following the launch of ClickPipes last year. Query-based API endpoints address two key challenges: wrangling language clients/drivers and authentication and access control. The feature is currently in Beta and will be further enhanced with support for JWT authentication and a streaming API.

The first official ClickHouse certification, ClickHouse Certified Developer, has been released. This hands-on, performance-based exam validates a candidate's knowledge of ClickHouse by assessing their ability to model tables, insert and analyze data, and optimize queries. The exam consists of approximately 10 tasks to be completed within a 2-hour timeframe. To prepare for the exam, individuals can work through the 12 modules of the ClickHouse Developer training course available on-demand in the ClickHouse Academy. Upon passing the exam, candidates receive a digital badge issued by Credly and an official ClickHouse Certified Developer t-shirt and stickers. This certification marks the beginning of a new era for database frameworks and offers individuals the opportunity to become subject matter experts on this exciting project.

ClickHouse Cloud has introduced configurable backup controls in private preview, allowing users to customize the start time, retention, and frequency of backups for their Production and Dedicated tier services. This feature aims to provide more flexibility and control over data protection and disaster recovery plans. Users can modify these settings through the service settings page in the Cloud Console. The cost impact of backup configurations is displayed under the "Organization" section on the Cloud Console, with additional features such as on-demand backups, cross-region exports, and continuous backups with point-in-time restores planned for future updates. New users can receive $300 in credits by signing up for ClickHouse Cloud today.

ClickHouse has launched an updated version of its Cloud Console, which aims to provide a seamless experience for developers and analysts who work with data every day. The new console combines the legacy ClickHouse Cloud Console and the SQL Console into a single application, offering a unified interface for users. The design process involved creating a connected system where changes made in Figma could flow through the code and into the product without requiring developers to make updates. The company also built an open-source React component library called Click UI, which is growing with over 40 components. The new Cloud Console has been rolled out globally after undergoing testing and iteration during private preview.

Launch week for ClickHouse Cloud features a new feature every day. Today's announcement is the beta release of an Amazon Kinesis connector for ClickPipes, allowing hassle-free ingestion of data from Kinesis Data Streams into ClickHouse Cloud service. This integration complements existing batch data loading connectors for Amazon S3 and Google Cloud Storage (GCS). The Lambda Architecture combines stream and batch processing, while the Kappa Architecture simplifies this by relying only on stream processing. With ClickPipes, users can focus on deriving insights from their data without worrying about building complex data pipelines. Upcoming features in the ClickPipes ecosystem include PostgreSQL Change Data Capture (CDC) connector, continuous mode for batch data loading connectors, offset control for Kafka Connector, and more.

In this blog post, the performance of ClickHouse and Elasticsearch for count aggregation queries over large data volumes is compared. It is found that ClickHouse outperforms Elasticsearch in terms of hardware efficiency, resulting in at least 5 times lower latencies for aggregating large datasets. The reasons behind this are explained in detail, including the parallelization approaches used by both systems and their impact on performance. Additionally, the built-in mechanisms for pre-calculating counts in ClickHouse and Elasticsearch are discussed, highlighting the advantages of ClickHouse materialized views over Elasticsearch transforms. The post concludes with a summary of the key findings and a suggestion to read the accompanying blog post for more information on ClickHouse's high-performance aggregations.

1. ClickHouse is faster than Elasticsearch for aggregation queries on large data sets. 2. ClickHouse requires less storage space compared to Elasticsearch. 3. ClickHouse can run aggregation queries over raw and pre-aggregated data sets more efficiently. 4. ClickHouse's continuous data summarization technique is more storage- and compute-efficient than Elasticsearch's approach.

ClickHouse, a free and open-source columnar DBMS (Database Management System), has released version 24.4 with several new features and improvements. These include support for the QUALIFY clause in SQL queries, better performance for specific JOIN use cases, and more efficient memory usage during query execution. The QUALIFY clause allows users to filter data based on window functions without needing to introduce a Common Table Expression (CTE). This can make certain types of queries simpler and easier to understand. In terms of join performance improvements, there are two main changes in 24.4. The first is better predicate pushdown, which means that the analyzer works out when a filter condition can be applied to both sides of a JOIN. This can lead to significant speedups for certain types of queries. The second improvement involves automatically converting an OUTER JOIN to an INNER JOIN if the predicate after the JOIN filters out any non-joined rows. This can also result in faster query execution times. Overall, these enhancements aim to make ClickHouse even more powerful and efficient for handling large datasets and complex queries.