ClickHouse

ClickHouse is a column-oriented OLAP database for real-time analytics over large datasets — vectorized execution, heavy compression, and massively parallel queries. It is append-mostly and analytical, not a transactional (OLTP) store. This skill covers using it from Rust and Node.js plus patterns/antipatterns; for detailed schema, engine, and ORDER BY design see the clickhouse-js skill.

What It Is Good and Bad At

• Great at: large aggregations/scans, time-series and event/log analytics, append-heavy ingestion, columnar compression, GROUP BY/window/approximate functions over billions of rows.
• Bad at: point lookups by primary key, single-row updates/deletes, high-frequency tiny inserts, transactions across rows, foreign-key integrity, frequent mutations. Do not use it as an application's primary OLTP database.
• Mental model: data lands in immutable parts and is merged in the background (MergeTree family). Dedup, updates, and deletes are eventually applied via merges — design queries to tolerate that (e.g. FINAL or aggregation).

Current Features (worth using, 2024–2026)

• JSON data type (GA): native semi-structured column with per-path subcolumn storage and typed reads — replaces the old Object('json')/string-blob approach. Pairs with Dynamic (one column, many types) and Variant(T1, T2, …) (tagged union) for flexible schemas.
• Async inserts (async_insert=1): the server batches many small inserts server-side into larger parts — the standard fix for "many small producers." Tune wait_for_async_insert, async_insert_max_data_size/busy_timeout_ms.
• Lightweight DELETE is widely available and far cheaper than legacy ALTER TABLE … DELETE mutations. Lightweight / on-the-fly UPDATE is newer and version/settings-gated — not equivalent to DELETE and not OLTP-grade; use sparingly.
• Refreshable materialized views: scheduled full-refresh MVs (in addition to classic insert-triggered incremental MVs) for periodic rollups/denormalization.
• Vector search: approximate-nearest-neighbor indexes for embedding similarity, complementing the analytical core.
• Query cache, parallel replicas, and projections for read scaling and alternate sort orders within one table.