June 2023 Summaries

Modeo, a French data engineering firm, has developed Stash, a solution that leverages the power of ClickHouse for its scalability and storage efficiency to deliver sustainable data management. The company's challenge was to manage growing data volumes while accurately measuring real-time carbon emissions generated by data usage and storage. Stash synchronizes metadata and logs from customers' data platforms into ClickHouse, providing insights into data usage and cost, ultimately leading to better business outcomes and data adoption. Modeo selected ClickHouse for its scalability, real-time analytics, customer satisfaction, cost-efficiency, comprehensive SQL support, intuitive design, and user-friendly interface. The implementation of ClickHouse has brought significant benefits for Modeo, including cost reduction, improved data adoption, and enhanced business outcomes for customers.

The direct join algorithm offers the fastest execution time and is applicable when the underlying storage for the right-hand side table supports low-latency key-value requests, and when LEFT ANY JOIN semantics is adequate. The hash join algorithm is fast but memory-bound, while the parallel hash join can be faster with large right-hand side tables but requires more memory. The full sorting merge join is a non-memory bound version that spills data temporarily to disk without requiring any sorting of the data, offering good control over memory usage vs. join speed. The partial merge join is optimized for minimizing memory usage when large tables are joined at the expense of join speed. The grace hash join offers flexible control of memory usage based on the configured amount of buckets and can be faster or slower than other non-memory bound algorithms depending on data volume, data types, and value distribution of the join key columns. The choice of join algorithm mainly depends on three factors: performance, memory, and join type support.

ExitLag, a gaming company, optimized its online gaming experience with ExitLag by developing a sophisticated method for sending connection packets through different routes to minimize lag and packet loss. To achieve this, they transitioned from MySQL to ClickHouse, which offered exceptional performance, scalability, and efficient data compression capabilities. With ClickHouse, ExitLag can quickly process billions of lines of data in a short time, analyzing user behavior on their service and mapping possible network routes. The tool's materialized views provided faster access to aggregated data, reducing the need for repetitive computations. The transition resulted in significant cost savings due to efficient data compression and optimized resource utilization. ExitLag plans to leverage ClickHouse's analytical and machine learning capabilities to deepen their understanding of user behavior and improve their services. With Datacosmos Consultoria's help, they redefined their ability to provide a superior gaming experience.

Inigo, a pioneering company in GraphQL API management, explored various database alternatives before settling on ClickHouse paired with Materialized Views to handle their high-performance analytics needs. They found that ClickHouse's columnar design and automatic indexing simplifies data analysis, while its scalability and open-source nature make it an attractive choice for large-scale analytics. The introduction of Materialized Views allows datasets to be pre-aggregated, significantly improving analytical query performance. By leveraging these features, Inigo was able to reduce database load, improve UI responsiveness, and enhance their development process with stronger typing guarantees and faster integration testing.

This summary provides an overview of how Confluent Cloud's Custom Connector can be used to build a zero-code Kafka pipeline for moving data from Google Pub/Sub to ClickHouse Cloud, specifically for the Ethereum blockchain dataset. The process involves creating a Pub/Sub subscription, deploying the Confluent Cloud Custom Connector, and configuring it to send messages to a Kafka topic. The Kafka Connect Google Cloud Pub/Sub Source Connector reads messages from the Pub/Sub topic and writes them to the Kafka topic. A ClickHouse connector is then deployed to receive and transform inserts from the Kafka topic into the desired format for insertion into ClickHouse Cloud. The workflow demonstrates how this architecture can be used to maintain a public dataset in near real-time, with various hints and tricks provided for optimizing performance and troubleshooting issues.

The latest release of ClickHouse, version 23.5, brings a plethora of exciting features and improvements to the popular open-source database management system. Among these are enhancements to the Azure Blob Storage table function, allowing users to query files hosted in Azure Blob Storage with improved performance and functionality. Additionally, ClickHouse Keeper Client is introduced, providing a simple tool for introspecting your ClickHouse Keeper, making administrative tasks more convenient. The release also includes significant improvements to Parquet reading speed, including out-of-order reading of rows, which can result in faster query execution times. Furthermore, geographical data types and functions are now production-ready, and compressed marks and indices on disk are available by default. The Query Results Cache is also considered production-ready, allowing for faster query execution times. Overall, the release aims to address various user needs and improve performance, making ClickHouse an even more attractive option for developers and users alike.

We are thrilled to announce that ClickHouse Cloud is now Generally Available on Google Cloud Platform (GCP) in three geographies - United States (Iowa), Europe (Netherlands) and Asia (Singapore). This release offers users more flexibility and choice for deploying ClickHouse services. The service includes features such as integration with Google Cloud Storage, end-to-end encryption, endpoint security controls, daily backups, private service connect, disaster recovery, and service level agreements. ClickHouse Cloud also offers a 30-day trial with $300 in usage credits and different tier selections to cater to various deployment needs. The service is designed for production environments and workloads that require additional storage and memory, as well as a dedicated tier for demanding workloads requiring performance and isolation guarantees.

ClickHouse Inc. has announced the general availability (GA) of ClickHouse Cloud on the Google Cloud Platform (GCP), expanding its support to Asia, the European Union, and the United States. This move follows the December 2022 debut of ClickHouse Cloud on AWS and aims to provide developers with increased freedom and flexibility in choosing their cloud provider. The GA offers a serverless, fully managed platform for real-time analytics built on top of ClickHouse, which is designed to deliver robust, secure, and scalable performance. With transparent pricing and easy integrations, ClickHouse Cloud allows users to quickly get started and deploy services within their chosen cloud provider, without worrying about operational complexity. The launch also enables the combination of ClickHouse with Google BigQuery, speeding up queries and improving service quality for customers.

In a ClickHouse database, a table was created to track data from GitHub Actions using GitHub Webhooks for the ClickHouse organization, providing essential metrics on internal workflow job queues. The data was then used to implement a lambda function that dynamically inflates and deflates workers on demand, improving responsiveness and compute savings. A new system was developed where the desired capacity is calculated based on current runners and jobs in progress/queue, with adjustments made proportionally to deficit or reserve. This approach replaced an old system that relied on CloudWatch alarms and GitHub runner API monitoring, resulting in faster job launching and reduced compute time.

This blog post provides an introductory guide to achieving Change Data Capture (CDC) with Postgres and ClickHouse. The solution proposed uses only native features of ClickHouse, without requiring additional components beyond Debezium and Kafka. The approach is push-based, where the source database captures changes and sends them to the target system in near real-time. To achieve this, PostgreSQL's Write-Ahead Log (WAL) and logical decoding are exploited, along with the open-source tool Debezium. Debezium produces row-level change events that can be sent to Kafka for consumption by downstream sinks. The ReplacingMergeTree table engine is used in ClickHouse to efficiently handle updates and deletes, while ensuring data consistency and query performance. The blog post also discusses important considerations for using this CDC pipeline, including partitioning and filtering on primary key columns to improve performance.

The CDC pipeline for Postgres to ClickHouse uses Debezium, Kafka Connect, and a materialized view to capture changes in near real-time. The architecture assumes the user has an instance of Kafka with the Kafka Connect framework, where the connector is configured to track changes at a per table level. The configuration includes settings such as `max.batch.size`, `poll.interval.ms`, and `consumer.override.max.poll.records` to optimize batching and polling intervals. Additionally, users should be aware of other considerations, including WAL disk usage, heartbeat interval, primary key changes, DDL changes, and logical decoding replication slots. The pipeline is designed for production use cases and can be scaled at a table level using a connector per table approach. Users are encouraged to explore other databases supported by Debezium and share their experiences.

SigNoz is an open-source Application Performance Monitoring (APM) solution that was developed in early 2021 to fill the gap in the market for a complete APM experience. The founders, who preferred an open-source model, chose ClickHouse as their data store due to its ease of use and scalability, even on low-powered machines. They also took an early bet on OpenTelemetry, recognizing its potential to standardize telemetry data format and create a larger ecosystem of tooling. SigNoz offers a single pane for metrics, traces, and logs, with a user interface and visualization layer that enables easy correlation and identification of root causes. With superior performance for high-cardinality data thanks to ClickHouse, SigNoz is well-suited for analytics use cases such as Observability. The platform can be easily set up on a single node or Kubernetes cluster, making it accessible to developers and organizations of all sizes.

The Direct Join algorithm is the fastest join algorithm in ClickHouse, applicable when the underlying storage for the right-hand side table supports low latency key-value requests. It beats all other ClickHouse join algorithms with a significant improvement in execution time, especially with large right-hand side tables. The algorithm requires that the right table is backed by a dictionary, which allows for extremely fast key-value lookups with O(1) time complexity. The direct join run from the query using a flat memory layout dictionary is ~25 times faster than the hash join run and ~15 times faster than the parallel hash join run. Even with added dictionary bytes_allocated to peak memory consumption, it remains lower compared to the hash algorithm runs.

ClickHouse Cloud has recently added several exciting features, including support for Google Cloud Platform (GCP), API management, and improved scaling policies. The service now offers more flexibility to run analytics workloads in preferred cloud providers, with capabilities like Private Service Connect and GCP Marketplace support. Additionally, SQL console improvements include materialized views and a heatmap chart type, while an advanced metrics dashboard provides visibility into service health. ClickHouse Grafana datasource supports tracing visualization, and new integrations have been added for dbt, Metabase, and Kafka Connect. The service also offers dedicated services for mission-critical applications requiring isolation, security, and performance consistency. To experience these improvements, users can log into their cloud account or start a free trial.