# Clickhouse Best Practices

> Production operational practices: insert batching, async writes, query cache, connection pooling, and recommended settings.

- Skill: `terrylica/clickhouse-best-practices` (Agent Skill)
- Install (CLI): `npx skillmds@latest add terrylica/clickhouse-best-practices`
- Raw SKILL.md: https://api.skillmd.com/api/skills/terrylica/clickhouse-best-practices/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: terrylica (https://skillmd.com/u/terrylica)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/terrylica/clickhouse-best-practices

---


# ClickHouse Best Practices

## Insert Best Practices

- Batch 10k-100k rows per insert; max 1-2 inserts/sec per table
- Sub-1k-row inserts cause part proliferation; insert in sorting-key order to reduce merge work
- For bulk loads, tune `min_insert_block_size_rows` / `max_insert_block_size_rows`

## Async Inserts

- `SET async_insert = 1; SET wait_for_async_insert = 1;` (1=durable, 0=fire-and-forget)
- Tune `async_insert_max_data_size` (1M) and `async_insert_busy_timeout_ms` (10s) for batch window

## Query Cache (v23.5+)

- `SET allow_experimental_query_cache = 1; SET use_query_cache = 1;`
- TTL: `query_cache_ttl`; share across users: `query_cache_shared_between_users = 1`
- Control: `enable_writes_to_query_cache`, `enable_reads_from_query_cache`

## Connection Pooling

- HTTP: set `pool_size`, `max_queries`; keep-alive is critical
- TCP: built-in multiplexing; tune `max_connections`
- Monitor `system.metrics` for `HTTPConnection`/`TCPConnection` to size pools

## Production Settings

- `max_threads` — lower for concurrent loads; `max_insert_threads` — raise for parallel inserts
- `max_execution_time` / `max_memory_usage` — per-query limits
- `join_algorithm` — prefer `grace_hash` or `auto` for large joins
- `input_format_allow_errors_num` / `ratio` — tolerate parse errors in bulk imports

## Query Patterns

- `COLUMNS('pattern')` — regex column selection; `APPLY func` transforms matches
- `clusterAllReplicas('cluster')` — aggregate across all replicas
- `FINAL` — force merge for ReplacingMergeTree; use sparingly (full scan)