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 mininsertblocksizerows / maxinsertblocksizerows

Async Inserts

• SET asyncinsert = 1; SET waitforasyncinsert = 1; (1=durable, 0=fire-and-forget)
• Tune asyncinsertmaxdatasize (1M) and asyncinsertbusytimeoutms (10s) for batch window

Query Cache (v23.5+)

• SET allowexperimentalquerycache = 1; SET usequery_cache = 1;
• TTL: querycachettl; share across users: querycachesharedbetweenusers = 1
• Control: enablewritestoquerycache, enablereadsfromquerycache

Connection Pooling

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

Production Settings

• maxthreads — lower for concurrent loads; maxinsert_threads — raise for parallel inserts
• maxexecutiontime / maxmemoryusage — per-query limits
• joinalgorithm — prefer gracehash or auto for large joins
• inputformatallowerrorsnum / 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)