> ## Documentation Index
> Fetch the complete documentation index at: https://clickhouse.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# ClickHouse Cloud performance optimization playbook

> Measure, diagnose, and improve ClickHouse Cloud performance through schema design, ingestion, caching, repeatable benchmarks, and deployment planning.

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](#profile-the-workload), then [design the schema](#design-the-schema) and [plan Cloud resources](#plan-cloud-resources). |
| Improve an existing workload | [Diagnose the bottleneck](#diagnose-the-bottleneck), change one factor, and [benchmark the result](#benchmark-the-result). |
| Investigate a slow query | Inspect its history and execution plan with the [query optimization guides](https://clickhouse.com/docs/guides/clickhouse/performance-and-monitoring/query-optimization.md). |
| Prepare for launch | Complete the [deployment checklist](#deployment-checklist) and [production readiness guide](https://clickhouse.com/docs/products/cloud/guides/production-readiness.md). |

## 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](https://clickhouse.com/docs/products/cloud/features/sql-console-features/query-insights.md) provides a view of query patterns and resource consumption. For custom analysis, query [`system.query_log`](https://clickhouse.com/docs/reference/system-tables/query_log) across replicas:

```sql theme={null}
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](https://clickhouse.com/docs/guides/clickhouse/performance-and-monitoring/diagnose-slow-queries.md) 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:

```sql theme={null}
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](https://clickhouse.com/docs/guides/clickhouse/performance-and-monitoring/isolate-query-bottlenecks.md) 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](https://clickhouse.com/docs/reference/engines/table-engines/mergetree-family).

### 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](https://clickhouse.com/docs/concepts/best-practices/choosing-a-primary-key.md).

### 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](https://clickhouse.com/docs/concepts/best-practices/select-data-type.md), [Using JSON](https://clickhouse.com/docs/concepts/best-practices/json-type.md), and [Compression in ClickHouse](https://clickhouse.com/docs/guides/clickhouse/data-modelling/compression/compression-in-clickhouse.md).

### 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](https://clickhouse.com/docs/concepts/best-practices/partitioning-keys.md).

### 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](https://clickhouse.com/docs/concepts/features/projections/materialized-views-versus-projections.md) and [Backfilling data](https://clickhouse.com/docs/guides/clickhouse/data-modelling/backfilling.md).

### 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](https://clickhouse.com/docs/concepts/best-practices/minimize-optimize-joins.md), [Data skipping indices](https://clickhouse.com/docs/concepts/best-practices/using-data-skipping-indices.md), and [Query optimization](https://clickhouse.com/docs/guides/clickhouse/performance-and-monitoring/query-optimization.md).

## Optimize ingestion and retention

Small, frequent inserts can create parts faster than merges consolidate them. Prefer client-side batches or [asynchronous inserts](https://clickhouse.com/docs/concepts/features/operations/insert/asyncinserts.md). Choose batch size using row size, throughput, and freshness requirements, then monitor active parts and merge activity.

Inspect effective settings rather than assuming defaults:

```sql theme={null}
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](https://clickhouse.com/docs/concepts/best-practices/selecting-an-insert-strategy.md).

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](https://clickhouse.com/docs/concepts/best-practices/avoid-mutations.md), [Avoid optimize final](https://clickhouse.com/docs/concepts/best-practices/avoid-optimize-final.md), and [Manage data with TTL](https://clickhouse.com/docs/concepts/features/operations/delete/ttl.md).

## 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](https://clickhouse.com/docs/products/cloud/features/autoscaling/idling.md) 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](https://clickhouse.com/docs/concepts/features/performance/caches/caches.md) and [Query cache](https://clickhouse.com/docs/concepts/features/performance/caches/query-cache.md).

### 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](https://clickhouse.com/docs/products/cloud/features/autoscaling/overview.md) and [service limits](https://clickhouse.com/docs/products/cloud/guides/best-practices/usagelimits.md) 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](https://clickhouse.com/docs/products/cloud/features/infrastructure/warehouses.md) 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

1. **Save the baseline.** Record the ClickHouse version, service configuration, table definitions, settings, data volume, and query text with representative parameters.
2. **Control the workload.** Keep data and concurrency comparable. Record ingestion, merges, and background activity. Include the client and network path when measuring application latency.
3. **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.
4. **Change one factor.** State the hypothesis, such as reducing granules read by changing the ordering key.
5. **Repeat measurements.** Use enough runs to characterize variability. Report distributions or percentiles with the run count, rather than only the fastest execution.
6. **Check correctness and tradeoffs.** Compare results, freshness, insert throughput, memory, storage, and resource consumption. Include backfill and materialization costs where relevant.
7. **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](https://clickhouse.com/docs/guides/clickhouse/performance-and-monitoring/query-optimization-example.md).

## 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](https://clickhouse.cloud/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.