Choosing a ClickHouse primary key and ordering key
Picking ORDER BY columns for a MergeTree table: filter columns first, cardinality order, compression effects, and how to test a key change on a copy before migrating production data.
ClickHouse performance tuning and schema design
Schema, ingestion and query techniques that keep self-managed ClickHouse fast: ordering keys, partitions, skip indexes, materialized views, projections, inserts, updates and resource controls.
Choosing a ClickHouse partitioning key
When to partition, by what, and how coarse: partitions for retention and data management, the cost of too many partitions, and aligning partitions with TTL.
Using data skipping indexes in ClickHouse
When minmax, set and Bloom filter skip indexes help, how to confirm they are used with EXPLAIN, and why they come after the ordering key and materialized views.
Speeding up ClickHouse queries with materialized views
Precomputing aggregates at insert time with incremental materialized views: target table engines such as AggregatingMergeTree, backfilling, chaining views, and the insert cost they add.
Refreshable materialized views in ClickHouse
Scheduled rebuilds with REFRESH EVERY for queries that cannot be incremental: full replace versus APPEND, dependencies between views, and scheduling heavy refreshes off-peak.
ClickHouse projections versus materialized views
Using projections as hidden alternate orderings or pre-aggregations that ClickHouse picks automatically, and when a materialized view is the better choice.
Using dictionaries for fast lookups in ClickHouse
Replacing joins with in-memory dictionaries loaded from ClickHouse, PostgreSQL, MySQL, HTTP or files: layouts, refresh lifetimes, memory use, and enrichment at insert time.
Optimizing JOINs in ClickHouse
Reducing join cost in ClickHouse: denormalizing frequently queried attributes, dictionaries, join algorithms and table order, and using materialized views instead of large joins.
Asynchronous inserts for high-volume small writes
Configuring async_insert for many agents sending small batches: flush thresholds, wait_for_async_insert and durability, deduplication, and monitoring buffer flushes.
Updating and deleting data in ClickHouse
Choosing between lightweight UPDATE and DELETE, ALTER mutations, ReplacingMergeTree and partition drops: visibility, cost on large tables, and patterns for frequent corrections.
Deduplicating rows with ReplacingMergeTree
Handling updated and duplicate rows with ReplacingMergeTree: version columns, eventual deduplication, FINAL at query time, and why OPTIMIZE FINAL should not be routine.
Storing semi-structured data with the ClickHouse JSON type
When to use the JSON type, typed columns or a String column for JSON payloads: subcolumn storage, query performance, and schema design for unpredictable keys.
Using the ClickHouse query cache
Caching repeated dashboard queries with use_query_cache: entry lifetimes, stale results, cache size limits, and which queries should never be cached.
Isolating workloads with ClickHouse workload scheduling
Keeping ingestion, dashboards and ad hoc queries from starving each other: CPU, IO and memory resources, workload hierarchies, weights and priorities, and concurrency limits.
Diagnosing slow queries in ClickHouse
A repeatable process for slow ClickHouse queries: finding them in system.query_log, EXPLAIN and rows read, isolating bottlenecks, and choosing between schema, index and query fixes.
Controlling ClickHouse memory use and spilling to disk
Setting per-query and server memory limits, external GROUP BY and sort thresholds, and finding the queries that use the most memory before they fail.
