What is ClickHouse, and why is it faster than PostgreSQL for analytical queries?

Think of a library where books are organized by topic (row-oriented like PostgreSQL) versus a library where all chapter-1s are stored together, all chapter-2s together, and so on (column-oriented like ClickHouse). If you want to count how many books mention 'analytics' in chapter 3, the column-oriented library lets you scan just the chapter-3 shelf instead of pulling every book off every shelf and opening it to chapter 3.

ClickHouse is an open-source, column-oriented Online Analytical Processing (OLAP) database management system developed originally at Yandex for their web analytics product Metrica. Unlike PostgreSQL which stores entire rows together on disk (great for transactional workloads where you read/write full records), ClickHouse stores each column in a separate file. This means analytical queries that aggregate over a few columns out of hundreds only read the columns they need, dramatically reducing I/O.

Internally, ClickHouse achieves its speed through several architectural choices. Vectorized query execution processes data in batches of columns rather than row-by-row, leveraging CPU cache lines efficiently. SIMD (Single Instruction Multiple Data) instructions process multiple values in a single CPU cycle. Data compression works exceptionally well because columns contain homogeneous data types (all integers together, all strings together), achieving 5-20x compression with LZ4 or ZSTD codecs. The sparse primary index fits entirely in memory, enabling fast data skipping without reading unnecessary granules.

At production scale, ClickHouse handles billions of rows per second for aggregation queries on commodity hardware. A typical deployment ingests clickstream data, logs, or metrics at millions of rows per second and serves analytical dashboards with sub-second response times. Companies like Uber, Cloudflare, and eBay use ClickHouse for real-time analytics where PostgreSQL would require minutes or hours for the same queries.

The non-obvious gotcha is that ClickHouse excels at analytical reads but is fundamentally different from PostgreSQL for writes. There are no transactions, no UPDATE or DELETE in the traditional sense (mutations are asynchronous background operations), and point lookups by primary key are not its strength. Teams migrating from PostgreSQL must redesign their data model around append-only inserts and pre-aggregation rather than expecting CRUD-style operations.