When would you lower index_granularity below the ClickHouse default of 8192 rows?

## What the setting controls `index_granularity` is a `MergeTree` table setting — written as `SETTINGS index_granularity = 8192` in the DDL — that determines how many rows make up one granule. The granule matters because it is the atomic read unit: the sparse primary index has one entry per granule, mark files record one offset per granule per column, and any query that needs one row inside a granule reads the whole granule from every column it references. So the setting is a direct read-amplification knob for selective queries: at 8192, the floor for a single-row match is 8192 rows' worth of the selected columns; at 1024, it is 1024. ## Why 8192 is the default It is a balance point. The index has to stay small enough to keep resident in memory for every active part — a table with billions of rows across hundreds of parts still ends up with an index measured in megabytes at 8192, and eight times that at 1024. Compression also likes larger contiguous runs, and per-granule bookkeeping (mark lookups, decompression setup, per-block execution overhead) is amortised better over more rows. For the analytical scans ClickHouse is built for — aggregate over millions of rows — granule size barely matters, because you are reading nearly all of them anyway. ## When lowering is defensible The honest list is short: - **Near-point serving workloads.** A narrow table whose queries pin the full sorting key and return a few rows, served at high QPS. Here the 8192-row floor dominates latency and a smaller granule measurably helps. - **Very wide rows already read in full.** If a single row is large, a granule is a lot of bytes; fetching 8192 of them for one match is expensive. - **Small dimension-style tables** where the index is tiny in absolute terms, so the memory cost of a finer index is irrelevant. When not to: any table whose queries are aggregations over large ranges, any table where part counts are already high (index memory multiplies per part), and any case where you have not measured the current granule-level read amplification. A candidate who reaches for this setting before checking the sorting key has the priority backwards — a wrong leading key column costs orders of magnitude, granularity costs a factor of a few. ## Adaptive granularity Modern ClickHouse does not use a pure row count. The setting `index_granularity_bytes` caps a granule by uncompressed size, so a granule ends at whichever limit is reached first — the row count or the byte budget. That means wide rows already produce smaller granules automatically, and it is why manually shrinking `index_granularity` on a wide table is often solving a problem the server already solved. Setting `index_granularity_bytes = 0` disables the adaptive behaviour and reverts to a fixed row count, which is occasionally done for compatibility but rarely for performance. ```sql CREATE TABLE lookups ( tenant_id UInt32, key String, value String ) ENGINE = MergeTree ORDER BY (tenant_id, key) SETTINGS index_granularity = 1024; ``` ## Changing it The setting can be altered with `ALTER TABLE ... MODIFY SETTING index_granularity = ...`, but existing parts keep the granularity they were written with — only new parts use the new value, so the effect appears gradually as data turns over, or immediately only if you rewrite the data. For an experiment, the clean approach is a copy of the table at the candidate granularity, loaded with a representative sample, then compare on the real query mix: latency, `read_rows` versus rows returned, and the size of the primary index and marks. ## What to say in an interview Name what the granule is and why it is the read floor; give the one legitimate case (selective lookups on narrow tables at high QPS); name the three costs (index memory per part, mark overhead, compression); mention adaptive granularity via the byte cap; and finish with the priority ordering — sorting key first, partition key second, skipping indexes third, granularity last and only with measurements. This is a differentiator question: nobody is failed for not knowing it, and knowing exactly when *not* to touch it reads better than enthusiasm for tuning it.