ClickHouse Cloud performance optimization playbook

Use this playbook to design a new ClickHouse Cloud deployment or improve an existing workload. It connects schema, query, ingestion, and infrastructure decisions to measurements you can validate. Most schema and query recommendations also apply to self-managed ClickHouse.

Choose a starting point

Your task	Start here
Design a new deployment	Profile the workload, then design the schema and plan Cloud resources.
Improve an existing workload	Diagnose the bottleneck, change one factor, and benchmark the result.
Investigate a slow query	Inspect its history and execution plan with the query optimization guides.
Prepare for launch	Complete the deployment checklist and production readiness guide.

Profile the workload

Record the current latency, throughput, and resource use, then define a measurable target. Distinguish query latency from ingestion latency and data freshness: improving one can add work to another. Capture representative queries and filter values, execution frequency, concurrency, ingestion rate, data volume, growth, and retention. Include differences between tenants and peak load. For a regression, record changes to the data, schema, version, service size, or client behavior. In Cloud, Query insights provides a view of query patterns and resource consumption. For custom analysis, query system.query_log across replicas:

SELECT
    normalized_query_hash,
    count() AS executions,
    round(avg(query_duration_ms)) AS avg_ms,
    quantile(0.95)(query_duration_ms) AS p95_ms,
    sum(query_duration_ms) AS total_duration_ms,
    avg(read_rows) AS avg_read_rows,
    max(memory_usage) AS max_memory_bytes,
    any(query) AS example_query
FROM clusterAllReplicas('default', merge('system', '^query_log'))
WHERE event_time >= now() - INTERVAL 1 DAY
    AND type = 'QueryFinish'
    AND is_initial_query = 1
    AND query_kind = 'Select'
GROUP BY normalized_query_hash
ORDER BY total_duration_ms DESC
LIMIT 20;

This ranks successful query patterns by accumulated duration. Also inspect failed queries in Query insights; successful runs alone can hide timeouts and memory-limit exceptions. The query requires access to the system tables on each replica. Logs flush asynchronously, so recent executions may not appear immediately. The default cluster is available in ClickHouse Cloud. For a single-node deployment, replace the FROM expression with system.query_log. See Diagnose slow queries for detailed analysis.

Diagnose the bottleneck

ClickHouse stores MergeTree data in sorted, compressed parts. Background merges consolidate parts. A sparse primary index helps queries skip granules within each part. Effective optimization reduces the data read or the work performed on it. Start with the table definition and query plan. Replace the table and query below with your own:

SHOW CREATE TABLE database_name.table_name;

EXPLAIN indexes = 1
SELECT column_name
FROM database_name.table_name
WHERE filter_column = 'value';

Inspect the selected parts and granules relative to the totals. Compare these with actual rows read, bytes read, duration, and memory usage from the query log. A large scan can be appropriate for a whole-table aggregation; judge it against the query’s requirements.

Observation	Investigate	Possible next step
A selective filter reads most granules	Primary-key pruning in `EXPLAIN`	Align the ordering key with filters, or evaluate a projection or skipping index.
Active parts keep increasing	Insert size, partition fan-out, and merge activity in `system.parts` and `system.merges`	Batch inserts, use asynchronous inserts, or reduce partition fragmentation.
Repeated runs are much faster	Cache activity and service lifecycle	Compare cold and warm runs separately; reduce the working set or precompute repeated work.
Joins consume excessive memory	Join cardinality, inputs, and algorithm	Reduce inputs, evaluate a dictionary, or test a memory-bounded algorithm.
Latency rises under concurrency	CPU, memory, ingestion, and competing queries	Reduce per-query work, then evaluate scaling or isolation.

Use Isolate query bottlenecks to test a hypothesis before changing the schema or settings.

Design the schema

Choose an engine for correctness

Select an engine based on how records change and how queries must interpret them.

Engine	Typical use
`MergeTree`	General append-only analytics.
`ReplacingMergeTree`	Multiple versions of a record, with eventual deduplication by sorting key. Use appropriate query-time deduplication when correctness requires it.
`AggregatingMergeTree`	Aggregate states, commonly populated by an incremental materialized view.
`SummingMergeTree`	Additive counters and rollups. Queries may still need to aggregate rows that have not merged.
`CollapsingMergeTree` or `VersionedCollapsingMergeTree`	Explicit state and cancellation records, with engine-specific query requirements.

Background merges do not guarantee that all versions or aggregate rows are consolidated before a query. Review the engine semantics in the MergeTree family reference.

Align the ordering key with queries

The table’s ORDER BY defines physical sort order. The primary key, which defaults to the sorting key, determines the sparse index. Neither enforces uniqueness. Prioritize columns used in frequent, selective filters. Among useful filter columns, lower-cardinality columns early in the key can improve locality and compression. Cardinality alone is not a reason to include a column. For example, queries that consistently filter by tenant and time may benefit from (tenant_id, event_time). Queries across all tenants may need a different order. A timestamp or identifier can be an appropriate leading column when the access pattern justifies it. Keep the key focused and test candidates against representative queries. ORDER BY tuple() provides no primary-index pruning, so use it only when that tradeoff is deliberate. See Choosing a primary key.

Use types that preserve meaning

Smaller representations can reduce storage, memory, and scan costs. Choose them without losing information:

Use integer widths that cover the expected range, including growth, and Decimal when exact decimal arithmetic is required.
Consider LowCardinality(String) for repeated strings and Enum for a controlled set of values.
Use DateTime64 when subsecond precision is required; otherwise consider DateTime or Date.
Use Nullable when absence has a distinct meaning. Replace missing values with zero or an empty string only when those are valid domain defaults.
Use explicit typed columns for stable, frequently queried fields. Evaluate JSON for evolving structures and Map for suitable key-value data.

Measure compressed size and query performance after changes. See Selecting data types, Using JSON, and Compression in ClickHouse.

Partition for data management

Choose partitions around retention and data-management operations. Parts in different partitions never merge, so excessive partition cardinality can create many small parts and increase overhead. No partitioning, monthly partitioning, or yearly partitioning can be good starting points. Daily partitions can also be appropriate when volume and retention justify them. Evaluate the number and size of parts before partitioning by high-cardinality identifiers. Partition pruning helps when queries constrain the partition expression, but does not replace a useful ordering key. See Choosing a partitioning key.

Precompute repeated work

Approach	Use when	Account for
Incremental materialized view	Repeated aggregations or transformations can move to insert time.	Target-table storage, insert work, and backfilling existing data. It processes newly inserted blocks rather than automatically reflecting every source-table change.
Projection	Another access pattern needs an alternative sort order or supported precomputed representation.	Storage and insert overhead, materialization of existing data, and compatibility with the query and ClickHouse version.

Check that queries use the intended projection. Validate aggregate and update semantics for materialized views. See Materialized views versus projections and Backfilling data.

Reduce join and scan work

Denormalize frequently accessed attributes when the update model permits it. Consider dictionaries for key-value lookups, preserving the required behavior for duplicate keys and changing dimensions. For joins that remain, inspect the plan and input cardinalities. Modern ClickHouse versions can reorder joins, so check the actual build side instead of relying only on SQL order. Start with the default algorithm. Test alternatives when memory or execution time identifies a specific problem; supported join types differ by algorithm. Select only needed columns. Apply selective filters, and inspect whether PREWHERE and primary-index pruning reduce reads. Add skipping indexes when the distribution and predicates let them skip enough granules to offset storage and evaluation costs. See Minimize and optimize joins, Data skipping indices, and Query optimization.

Optimize ingestion and retention

Small, frequent inserts can create parts faster than merges consolidate them. Prefer client-side batches or asynchronous inserts. Choose batch size using row size, throughput, and freshness requirements, then monitor active parts and merge activity. Inspect effective settings rather than assuming defaults:

SELECT name, value, changed
FROM system.settings
WHERE name IN
(
    'async_insert',
    'wait_for_async_insert',
    'async_insert_max_data_size',
    'async_insert_busy_timeout_ms'
);

Keep wait_for_async_insert = 1 when using asynchronous inserts so acknowledgment waits for the flush and the client receives flush errors. With 0, acknowledgment only confirms buffering; errors can occur after the client receives success. For third-party ingestion tools, validate batching, retries, deduplication, error handling, and backpressure end to end. Verify the connector’s delivery guarantees instead of assuming retries cannot create duplicates. See Selecting an insert strategy. Avoid frequent large mutations and routine OPTIMIZE TABLE ... FINAL: they can create substantial rewrite and merge work. Choose an update strategy that meets query-correctness requirements. Use TTL for supported retention policies and partition-level deletion when a whole partition should be removed. Cloud does not use TTL ... TO VOLUME for storage tiering. See Avoid mutations, Avoid optimize final, and Manage data with TTL.

Plan Cloud resources

Account for cache state

ClickHouse Cloud separates compute from object storage. Cache state can substantially affect latency. A repeated query may read cached data even when the query result cache is disabled. Measure first-run and repeated-run behavior separately. Record service idling, resizing, routing, and background activity that could affect the comparison. Evaluate automatic idling against the required startup latency. Reduce the working set through pruning and pre-aggregation before increasing resources. If prewarming is necessary, use representative queries and include their resource consumption in the cost assessment. The query result cache is distinct from data caches. Enable it only when application freshness requirements permit cached results. See Caches and Query cache.

Size for the measured workload

Evaluate CPU, memory, concurrency, and ingestion together. Test peak load as well as isolated queries. A service that meets a single-query target may miss that target under concurrent queries and ingestion. Vertical scaling changes resources per replica; horizontal scaling changes the number of replicas. Availability and automation depend on tier and profile. Consult the scaling overview and service limits rather than sizing from a fixed ratio of a previous system’s memory. Set scaling bounds appropriate to the workload and budget. Measure total resource consumption: a faster query can still cost more if it uses substantially more compute.

Isolate competing workloads

Use warehouses when ingestion, interactive queries, and batch analytics require dedicated compute. Services in a warehouse share data while providing separate compute resources and endpoints. Route applications and third-party tools to the intended service. Validate concurrency, connection pooling, timeouts, and retries under load. Measure both the isolated workload and the production workload to confirm that isolation meets the objective.

Benchmark the result

Save the baseline. Record the ClickHouse version, service configuration, table definitions, settings, data volume, and query text with representative parameters.
Control the workload. Keep data and concurrency comparable. Record ingestion, merges, and background activity. Include the client and network path when measuring application latency.
Separate cache scenarios. Report first-run and repeated-run results separately. Disable the query result cache when measuring execution improvements, and still account for data caches. Do not clear shared production caches for a benchmark.
Change one factor. State the hypothesis, such as reducing granules read by changing the ordering key.
Repeat measurements. Use enough runs to characterize variability. Report distributions or percentiles with the run count, rather than only the fastest execution.
Check correctness and tradeoffs. Compare results, freshness, insert throughput, memory, storage, and resource consumption. Include backfill and materialization costs where relevant.
Validate under representative load. Retest concurrency and ingestion before rollout, and keep a rollback plan.

When sharing findings with customers or partners, include the dataset or reproducible generation method, exact queries, configuration, and measurement procedure. State the scope: a speedup on one workload is not a general performance guarantee. For a reproducible starting point, see the worked query optimization example.

Deployment checklist

Define latency, throughput, freshness, and availability targets.
Validate engine semantics and correctness for updates and duplicates.
Test the ordering key against representative filters and inspect pruning.
Review data types, partition count, part sizes, and retention.
Measure insert batching, retry behavior, and merge pressure.
Verify the benefit and maintenance cost of views, projections, and indexes.
Test cache behavior, peak concurrency, and competing workloads.
Configure scaling bounds and route integrations to the appropriate endpoints.
Record a repeatable baseline, monitoring criteria, and a rollback procedure.

Get help

Contact ClickHouse Support if the bottleneck remains unclear. Include the query and query ID, table definitions, EXPLAIN indexes = 1 output, time range, service configuration, and a concrete target. Provide a representative sample or reproduction when possible, with sensitive data removed.

Get started

Services and infrastructure

Bring Your Own Cloud

Data and integrations

Connect and query

Access management

Security and compliance

Monitoring and backups

AI tools and agents

Cloud API

Billing

Support

ClickHouse Private