April 2023 Summaries

The Parquet format is a column-based storage format that offers great compression relative to other interchange formats. It relies on three principal concepts: row groups, column chunks, and pages. Understanding these concepts allows users to make decisions when writing files, which will directly impact the level of compression and subsequent read performance. The format includes metadata, such as references back to row groups, chunks, and pages, which can be exploited by query engines to skip column chunks. Parquet offers various encoding techniques, including dictionary encoding, Run Length Encoding (RLE), and Delta encoding, which can be applied to different data types. The encoding used for a column cannot be controlled with settings, but future improvements aim to address this limitation. ClickHouse supports Parquet files through the file function, and recent developments have improved parallelization within a single file, assigning a thread to each row group that is responsible for reading and decoding. This improvement significantly improves performance when reading large files. The optimal number of row groups can impact performance; too few row groups may not utilize available CPU cores, while too many row groups may lead to high memory consumption. A balance between parallel decoding and efficient reading is required. ClickHouse also supports multiple files in a directory, but this approach has limitations, such as requiring file listing operations and limited support for schema evolution or write consistency. Modern data formats like Apache Iceberg aim to address these challenges by providing SQL table-like functionality to files in a data lake. The blog post concludes that Parquet continues to evolve and improve its support, with planned improvements including exploiting metadata for conditions in any WHERE clause and improving logical type support.

AdGreetz, a leading AdTech and MarTech personalization platform, leverages ClickHouse Cloud to manage its high-volume ad impression events, providing high-performance analytics at a lower cost than Snowflake. With a reach across 26 diverse channels, AdGreetz processes millions of ad impressions daily, utilizing Cloudflare workers for individual event processing and enrichment before sending data to ClickHouse. The platform's seamless integration with ClickHouse enables efficient data management, secure customer separation, and scalable architecture, ultimately delivering personalized advertising experiences.

We've recently updated our website and brand to refresh our look. Our latest version of ClickHouse v23.3 has been released with 22 new features, 14 performance optimizations, and 61 bug fixes. Lightweight Deletes are now generally available, allowing users to efficiently remove specific rows from tables using the DELETE FROM table syntax. This feature is particularly useful for handling updates in non-immutable data and can simplify complex workflows such as incremental materializations. Our newsletter has also been reordered to prioritize the query of the month, which explores the potential impact of Lightweight Deletes on query performance and workflow. Additionally, we've made improvements to our Grafana plugin, and parallel replicas have been introduced to scale query performance by processing data in parallel across multiple ClickHouse hosts. Various upcoming events are available for users to learn more about ClickHouse and its capabilities.

Adevinta, an online classifieds specialist, operates 25 platforms across 11 countries, with a mission to provide the best user experience for buying and selling goods and services online. To achieve this, they required a centralized analytics and dashboarding tool to monitor seller's advertisements and improve performance in real-time. After evaluating various cloud-based database services, Adevinta determined that ClickHouse Cloud was the most cost-effective option that could provide high performance and scalability across multiple marketplaces. The company implemented ClickHouse Cloud as a self-managed environment initially but later migrated to it after evaluating its benefits and scalability. With ClickHouse Cloud, Adevinta can monitor advertisements in real-time, track interactions, and improve performance, while eliminating the need for a dedicated site reliability support team. The solution has proven successful in meeting their needs, providing a reliable, scalable, and efficient real-time analytics solution for their online classifieds business.

Observability has become a critical aspect of understanding system performance, identifying issues, and ensuring seamless user experiences. Highlight.io is an open-source observability platform that utilizes ClickHouse for data storage and retrieval, providing developers with insights ranging from user button clicks in the frontend to backend errors and infrastructure analysis. With just a few lines of code, developers can integrate Highlight.io into their systems, gaining access to real-time information and streamlining the troubleshooting process. As Highlight.io continues to evolve, it has added logging capabilities powered by ClickHouse, enabling deeper insights into applications by capturing and analyzing server-side logs. The platform's integration with ClickHouse allows for high data ingestion rates, ensuring developers can access up-to-date information in real-time, while also providing superior performance and lower data storage costs. Highlight.io has chosen ClickHouse due to its exceptional performance, real-time analytics features, and versatile deployment options, making it an ideal solution for delivering a robust and scalable observability solution.

ClickHouse joins use various algorithms to optimize performance and memory usage. The Hash join algorithm is fast but single-threaded, while the Parallel hash join algorithm can be faster with large right-hand side tables but requires more memory. The Grace hash join algorithm is non-memory bound and uses a two-phase approach to joining data, splitting it into buckets that are processed in-memory sequentially or offloaded to disk. ClickHouse automatically chooses one of 30+ variations for join algorithms based on query specifics, including support for multiple join keys and strictness settings. The choice of algorithm can significantly impact performance and memory usage, with the Grace hash join offering a good balance between execution time and memory consumption.

The Parquet file format is a column-oriented data storage format designed for efficient data storage and retrieval. It has become widely adopted as a file interchange format, with ClickHouse being one of the popular tools used to read and write this format. ClickHouse Local, an easy-to-use version of ClickHouse, can be used to query and transform Parquet files using SQL without writing any Python code. The file function in ClickHouse allows users to read schemas, query local and S3-hosted Parquet files, and perform various operations such as filtering, grouping, and aggregating data. Writing Parquet files with ClickHouse can be achieved through the INTO FUNCTION clause, which supports writing to local files or object storage like S3. The PARTITION BY clause allows users to write Parquet files in a partitioned format, which can help limit file sizes. ClickHouse also provides an efficient way to convert files between formats using the file function. Additionally, ClickHouse can load data from local Parquet files or S3 using the INFILE and s3 functions, respectively. The performance of these operations is significantly better when using ClickHouse with a MergeTree table schema, as it allows for indexes and format-specific optimizations to be exploited.

Resmo uses ClickHouse to store and manage its tracing data, leveraging its SQL support, compression capabilities, and query performance. With a production-ready solution deployed within three months, Resmo has optimized their data storage and analysis process, allowing for efficient exploration of configuration data from various APIs and SaaS tools. The implementation has been cost-effective, with a single server hosting over 4 billion spans, demonstrating the efficiency of ClickHouse in handling large-scale tracing data.

The latest update for ClickHouse brings several exciting features and improvements. The release includes 22 new features, 14 performance optimizations, and 61 bug fixes. One of the key updates is the introduction of "Parallel Replicas," which allows users to process data in parallel across multiple replicas of the same shard. This feature can significantly improve query performance by scaling distinct subsets of data across multiple ClickHouse hosts. Additionally, Lightweight Deletes are now generally available, making it easier to delete data in ClickHouse while preserving space savings. MySQL compatibility has also been improved, allowing for more efficient date parsing and other functions. Furthermore, the latest release supports Traces in Grafana, enabling users to visualize query execution steps and identify costly execution paths. Overall, these updates aim to enhance performance, ease of use, and developer experience for ClickHouse users.

I fell into product management somewhat by accident, studying computer science and working as a technology consultant before realizing I wanted to bring new products to life. I researched online and interviewed at big companies in Seattle before being referred to a set of technical founders who brought a startup out of stealth mode. This led to my first role as a sales engineer at ExtraHop, where I became the de-facto product manager due to my close customer relationships. I later joined Elastic as the second PM on their team and worked my way up to head of product for their revenue-generating observability product line. My experience has taught me that product management is about aligning the organization around a vision and plan, building respectful relationships with technical leaders, and taking responsibility for making the product successful in the market. I believe that a good PM is a leader who thinks about how to make their product successful every morning and does what it takes to make it happen.

This blog post discusses the optimization of LZ4 decompression speed in ClickHouse, a column-oriented database management system. The authors explore various techniques to accelerate decompression, including copying data in larger blocks (16 bytes instead of 8), using shuffle instructions for vectorized operations, and removing unnecessary checks. They also present a novel approach called "multi-armed bandits" that uses statistical sampling to select the best algorithm based on real-world data. The results show an average acceleration of 12-20% on modern x86 processors, with some datasets experiencing more than doubling in performance. However, the usefulness of these optimizations is limited in practice, and other factors such as dataset format and compression ratio play a significant role. The authors conclude that while LZ4 decompression can be accelerated, further research is needed to optimize algorithms for specific use cases.

Hex.tech has made ClickHouse available as a data source, enabling users to quickly and easily build real-time applications. The authors built a simple application called "ClickHub" using Hex.tech's tools, collecting the full commit history for 50k GitHub repositories. They utilized the KeeperMap table engine, which provides linearizable writes and sequentially consistent reads, to model a queue that prevents duplicate jobs from being scheduled and ensures atomic updates. The application uses ClickHouse Cloud for data storage and can be easily reproduced on smaller self-managed or development instances. Hex.tech offers a 14-day trial, allowing users to try the platform without committing to it. The authors recommend using Hex.tech mainly for proof-of-concept work, notebooks, and internal-facing analytical applications, with plans to expand its capabilities in future iterations.