ClickHouse Optimization: Partitioning, Indexes, and Materialized Views

ClickHouse Optimization: Partitioning, Indexes, and Materialized Views

A SELECT query on a 2-trillion-row table takes 40 seconds. Disk I/O at 100%, the server is humming. Sounds familiar? Usually, this means the primary key is poorly chosen, partitioning doesn't match the query pattern, or data is queried without leveraging skip indexes. We are a team of engineers with 10+ years of experience running ClickHouse in production. Our approach delivers measurable results: typical query execution time savings of 5–10x, and disk load reduction up to 70%. Contact us for a schema audit—we will assess the optimization potential within 1 day.

Why Partitioning Is Not a Silver Bullet

A common mistake: creating a table with PARTITION BY toYYYYMM(event_date) and expecting miracles. Partitioning in ClickHouse is primarily a data management tool (TTL, dropping old partitions), not a way to speed up queries. A query filtering on a specific month does skip irrelevant partitions, but if the date filter is loose or absent—ClickHouse reads everything.

Real story: a client partitioned an events table by toYYYYMMDD(ts)—365 partitions per year. This caused thousands of tiny parts to be created on every nightly insert, the MergeTree couldn't keep up with merges, and queries started opening hundreds of file descriptors. The system log was full of Too many parts (300). Merges are processing significantly slower than inserts. Rolling back to monthly partitioning plus tuning merge_with_ttl_timeout fixed the issue in an hour.

Optimal partition granularity: a partition should contain data for a period convenient for dropping. Use daily partitions only if daily data volume exceeds a few gigabytes; otherwise stick with monthly.

How to Choose the Right ORDER BY?

In ClickHouse, ORDER BY defines the physical data sort order on disk and serves as the primary key (if PRIMARY KEY is not explicitly specified). The default granularity is 8192 rows. Each granule is described in a sparse index, and ClickHouse decides which granules to read based on this index.

If the first element of ORDER BY is user_id UUID, but all queries filter on event_type and ts, the index is useless—ClickHouse reads all granules.

The rule for key selection: first put columns with low cardinality that appear most frequently in WHERE, then high-cardinality columns. For example:

CREATE TABLE events (
    event_type LowCardinality(String),
    project_id UInt32,
    ts DateTime,
    user_id UUID,
    payload String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(ts)
ORDER BY (event_type, project_id, ts)
SETTINGS index_granularity = 8192;

A query like WHERE event_type = 'click' AND project_id = 42 AND ts >= now() - INTERVAL 1 YEAR now reads only the needed granule range.

How Do Skip Indexes Work?

minmax, set, bloom_filter, tokenbf_v1 are the four main types of skip indexes. They do not replace the primary key but complement it for columns not in ORDER BY.

bloom_filter is indispensable for UUID or hash lookups:

ALTER TABLE events ADD INDEX idx_user_id user_id TYPE bloom_filter(0.01) GRANULARITY 4; 

After materialization (MATERIALIZE INDEX), a query like WHERE user_id = '...' skips granules that definitely lack that UUID. With a 1% false positive rate and 1000 granules, only 10-50 are actually read instead of all.

tokenbf_v1 works on string tokens—useful for substring searches in logs without LIKE '%pattern%'.

What Are Materialized Views and When to Use Them?

If the same aggregating query runs frequently—move the aggregation to insert time. MVs in ClickHouse are triggers on INSERT that route data to a target aggregate table.

CREATE MATERIALIZED VIEW events_hourly_mv TO events_hourly AS
SELECT
    toStartOfHour(ts) AS hour,
    event_type,
    project_id,
    count() AS cnt,
    uniqHLL12(user_id) AS uniq_users
FROM events
GROUP BY
    hour,
    event_type,
    project_id;

The target table uses AggregatingMergeTree with AggregateFunction:

CREATE TABLE events_hourly (
    hour DateTime,
    event_type LowCardinality(String),
    project_id UInt32,
    cnt AggregateFunction(count),
    uniq_users AggregateFunction(uniqHLL12, UUID)
) ENGINE = AggregatingMergeTree()
ORDER BY (hour, event_type, project_id);

Queries on aggregated data run 10-100 times faster than on raw data. But note: MVs only update on new inserts. Historical data must be added manually via INSERT INTO ... SELECT.

How to Profile Queries?

Before making changes, check system.query_log:

SELECT query_id, query, read_rows, read_bytes, result_rows, elapsed, memory_usage
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query_start_time >= now() - INTERVAL 1 HOUR
ORDER BY elapsed DESC
LIMIT 20;

read_bytes / result_bytes gives the over-read ratio. If you read 100 GB for a 1 MB result—the index is not working or missing.

system.parts shows partition state and number of parts. If count(*) > 300 for one partition—MergeTree can't keep up with merges; check background_pool_size and max_bytes_to_merge_at_max_space_in_pool.

How We Optimize ClickHouse

We start with an audit: collect the top-20 heavy queries from system.query_log, review table schemas, and profile using EXPLAIN indexes=1, actions=1. This reveals 80% of the issues.

Then we design: propose changes to ORDER BY/PARTITION BY, a list of skip indexes, and MVs. We agree with your team—changing the sort key requires table recreation with data migration.

Implementation: create new tables, set up MVs, migrate data in batches via INSERT INTO new_table SELECT ... FROM old_table WHERE partition_key = X. Parallel performance tests compare elapsed before and after on identical queries.

After migration, we provide documentation: which index for which queries, how to monitor system.parts, when to run OPTIMIZE TABLE ... FINAL.

Estimated Timelines and Scope

Pricing is determined individually after an infrastructure audit and data schema analysis. Typical infrastructure cost savings from reduced load can reach 40% on cloud instances.

What's Included

• Schema analysis and top-20 heavy query profiling (EXPLAIN, system.query_log)
• Design of partitioning, primary key, and skip indexes
• Creation and configuration of materialized views
• Data migration with minimal downtime (rolling migration)
• Operations documentation: monitoring recommendations, typical queries, runbook
• Team training: how to work with the new schema, how to add new fields

Our guarantees: all changes are tested on a data copy, results are measured in numbers. We don't just change the schema—we prove acceleration on your queries. Leave a request for an audit—we'll contact you within a day.