Tuning Index Granularity for ClickHouse Performance in E-commerce

Introduction

In the world of data analytics and storage, databases are the backbone that supports businesses’ decision-making processes. ClickHouse, an open-source columnar database management system, has gained popularity for its exceptional speed and efficiency in handling large volumes of data. One of the key contributors to its high performance is the way the MergeTree table engine stores the data on the disk and the way the data is indexed. One of the most important settings which can impact the speed of the queries in MergeTree table engine is the  index_granularity setting. In this blog post, we’ll explore what index_granularity is, how it works, and provide a real-life example to illustrate its impact on database performance.

Understanding ClickHouse Index Granularity

At its core, index_granularity in ClickHouse determines how often the index is updated and merged. In a columnar database like ClickHouse, data is stored in columns rather than rows, which allows for better compression and more efficient query processing. Indexing is essential to speed up query performance, as it enables the database to quickly locate the relevant data without scanning the entire dataset.

ClickHouse uses a data structure called the “MergeTree” to store data on disk. The MergeTree is organized into parts, each containing a range of data. The index_granularity setting determines how frequently these parts are created and merged. A smaller index_granularity value leads to more frequent part creation and merging, which can improve query performance but might increase write overhead.

Real-Life Example: E-commerce Analytics

Let’s consider an e-commerce company that utilizes ClickHouse to analyze its vast amount of transactional data. They have a table storing order information, including customer details, products purchased, and order dates. The marketing team frequently runs complex queries to analyze customer behavior, product trends, and sales patterns.

Initially, the company had the default index_granularity setting, which resulted in relatively larger parts. While query performance was acceptable, some analytical queries took longer than desired, especially when dealing with time-based aggregations. The marketing team needed quicker insights to optimize their campaigns and make data-driven decisions faster.

To address this, the company’s database administrators decided to adjust the index_granularity setting. They decreased it to a smaller value, causing ClickHouse to create smaller parts and merge them more frequently. This change aimed to improve query response times for the marketing team’s analytical queries.

The results were impressive. The smaller index_granularity setting significantly reduced the time required for complex queries involving time-based aggregations. For instance, a query that used to take several minutes now executed within seconds. This improvement empowered the marketing team to promptly identify trends, assess the performance of ongoing campaigns, and adjust strategies in near real-time.

SQL Command to Set index_granularity

ALTER TABLE table_name
    MODIFY INDEX index_name
    SET SETTING index_granularity = value;

Replace:

• table_name with the name of your table.
• index_name with the name of the index for which you want to set the index_granularity.
• value with the desired value for the index_granularity. It can be an integer representing the number of rows or bytes.

Example: Setting index_granularity

Let’s consider a scenario where you have a ClickHouse table named sales that stores information about sales transactions. You want to set a custom index_granularity for the primary index of the table to improve query performance for certain types of analytical queries.

Assuming you have an index named primary_index and you want to set the index_granularity to 100,000 rows, here’s how you would do it:

ALTER TABLE sales
    MODIFY INDEX primary_index
    SET SETTING index_granularity = 100000;

For more information, please visit the official ClickHouse documentation from here.

To know more about Indexes in ClickHouse, do read the following articles:

• Tuning Index Granularity for ClickHouse Performance
• Demystifying ClickHouse Indexes: Frequently Asked Questions and Best Practices
• What are the possible causes for the ClickHouse Index Not getting Utilised

References:

https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#settings