ClickHouse

Overview

ClickHouse is an open-source, column-oriented database management system designed for online analytical processing (OLAP). It was originally built at Yandex, open-sourced in 2016 under the Apache 2.0 license, and is now developed by ClickHouse Inc., an independent company incorporated in 2021 to commercialize it. ClickHouse is known for high query throughput and low latency on large analytical workloads, and is often used to power real-time analytics and dashboards.

Why it's fast

ClickHouse stores data by column rather than by row, so analytical queries read only the columns they need. It combines this with aggressive compression, vectorized query execution, and data skipping via sparse indexes. Its MergeTree family of table engines organizes data into sorted parts that are merged in the background, which supports high-rate inserts alongside fast reads.

Copy code
-- ClickHouse: create a MergeTree table
CREATE TABLE events (
  event_time DateTime,
  user_id UInt64,
  event_type String
) ENGINE = MergeTree()
ORDER BY (event_time, user_id);

ClickHouse and DuckDB

ClickHouse and DuckDB are both columnar OLAP engines with vectorized execution, but they target different shapes of problem. ClickHouse typically runs as a server (single-node or distributed cluster) and excels at high-concurrency, real-time analytics at scale. DuckDB is an in-process engine that runs embedded in an application or locally with no server, which suits local analytics, data science, and querying files directly. MotherDuck extends DuckDB into a serverless cloud service. A team might use DuckDB for local exploration and ClickHouse for a production, always-on analytics backend.

Copy code
-- DuckDB: equivalent analytical scan over local/remote files
SELECT event_type, COUNT(*) AS n
FROM read_parquet('events/*.parquet')
GROUP BY ALL
ORDER BY n DESC;