CODEWITHSUNDEEP
clickhouse
ruxtain
ruxtain

Reputation: 194

When should I change the index_granularity in Clickhouse?

For example, if I have a table containing 10 billion rows, should I increase the index_granularity or decrease index_granularity? Does it even matter to the query efficiency? Or should we always keep it as its default value? Any input is welcome. Thanks.

Upvotes: 3

Views: 3258

Answers (1)

Denny Crane
Denny Crane

Reputation: 13300

In general you should not worry about index_granularity and set it to default (8192).

For the modern ClickHouse index_granularity=8192 is a goal not the hard rule, adaptive granularity makes granules less than 8192 rows, if rows are very wide, then index_granularity_bytes defines the granularity not index_granularity.

Sometimes it needs to reduce index_granularity to 256 or 512 if you store small number of rows and data is very wide (like uniqStates or quatilesStates) (adaptive granularity does not work for States). It does not have sense to set index_granularity lower than 256, it will waste RAM, and will not improve performance, because other constraints (compression block size) start to play here.

Sometimes it needs to do the opposite index_granularity = 32768 (or 65536), because you need to store 10 trillions very narrow rows and PRIMARYKEY index is superhuge (because 10 trillions / 8192 == very large number). By the setting to index_granularity = 32768, you reduce memory usage by 4 times (usage by the primary key index). For this use case usually index_granularity = 32768 does not impact query performance.

Upvotes: 6

Related Questions