How ClickHouse Uses Sparse Indexes: A Deep Dive into primary.idx and Granules

  

In ClickHouse, MergeTree tables use sparse primary indexes for fast data access, where as a granule is a logical block of rows within a data part.

 

primary.idx

In ClickHouse, the primary.idx file is part of the sparse primary index used by MergeTree tables. It stores the first row’s primary key value of each granule, enable fast filtering during query execution.

 

index_granularity = N

index_granularity = N means ClickHouse stores an index entry for every N rows. This allows ClickHouse to skip entire granules when scanning data using the primary key.

 

To understand this concept better, let’s create a table and experiment with it.

 

Step 1: Create a database demo_db.

CREATE DATABASE IF NOT EXISTS demo_db;

   

Step 2: Create a Table with index_granularity = 4.

 

CREATE TABLE demo_db.orders_granule_example
(
    order_id UInt32,
    user_id UInt32,
    order_date Date,
    amount Float64
)
ENGINE = MergeTree
ORDER BY (user_id)
SETTINGS index_granularity = 4;

   

Step 3: Insert 12 records into the orders_granule_example table.

 

INSERT INTO demo_db.orders_granule_example VALUES
(1, 101, '2025-05-10', 200.0),
(2, 102, '2025-05-11', 300.0),
(3, 103, '2025-05-12', 400.0),
(4, 104, '2025-05-13', 500.0),
(5, 105, '2025-05-14', 600.0),
(6, 106, '2025-05-15', 700.0),
(7, 107, '2025-05-16', 800.0),
(8, 108, '2025-05-17', 900.0),
(9, 109, '2025-05-18', 1000.0),
(10, 110, '2025-05-19', 1100.0),
(11, 111, '2025-05-20', 1200.0),
(12, 112, '2025-05-21', 1300.0);

krishna :) SELECT * FROM demo_db.orders_granule_example;

SELECT *
FROM demo_db.orders_granule_example

Query id: 28bdc975-5b01-4963-a057-382595950c31

    ┌─order_id─┬─user_id─┬─order_date─┬─amount─┐
 1. │        1 │     101 │ 2025-05-10 │    200 │
 2. │        2 │     102 │ 2025-05-11 │    300 │
 3. │        3 │     103 │ 2025-05-12 │    400 │
 4. │        4 │     104 │ 2025-05-13 │    500 │
 5. │        5 │     105 │ 2025-05-14 │    600 │
 6. │        6 │     106 │ 2025-05-15 │    700 │
 7. │        7 │     107 │ 2025-05-16 │    800 │
 8. │        8 │     108 │ 2025-05-17 │    900 │
 9. │        9 │     109 │ 2025-05-18 │   1000 │
10. │       10 │     110 │ 2025-05-19 │   1100 │
11. │       11 │     111 │ 2025-05-20 │   1200 │
12. │       12 │     112 │ 2025-05-21 │   1300 │
    └──────────┴─────────┴────────────┴────────┘

12 rows in set. Elapsed: 0.005 sec. 

   

What Happens When You Insert Data?

Here we inserted 12 rows into a MergeTree table with index_granularity is equal to 4. For every 4 rows, ClickHouse creates a granule, and writes an entry to the primary index (primary.idx) for the first row in that granule.

 

Granule #	Indexed user_id (1st Row)	Rows Included
1	101	Rows 1–4 (user_id 101–104)
2	105	Rows 5–8 (user_id 105–108)
3	109	Rows 9–12 (user_id 109–112)

 

Contents of primary.idx

primary.idx will contain:

Entry 1 → user_id = 101  (1st row of Granule 1)

Entry 2 → user_id = 105  (1st row of Granule 2)

Entry 3 → user_id = 109  (1st row of Granule 3)

 

These are used by ClickHouse to seek to the correct granule during query execution.

 

For a query like: 

SELECT * FROM orders_granule_example WHERE user_id = 106;

   

ClickHouse looks at primary.idx and finds:

·      101 → Granule 1

·      105 → Granule 2

·      109 → Granule 3

 

It skips Granule 1 (106 > 104), reads Granule 2, since 106 is in range 105–108, skips Granule 3, since 106 < 109. In this approach, only one granule is read from disk instead of scanning all rows!

 

 

 

Previous                                                    Next                                                    Home