Why ClickHouse is Superior to PostgreSQL for Analytics Workloads

PostgreSQL is the world's most loved general-purpose database, and for good reason - it's reliable, feature-rich, and powers a huge share of modern applications. ClickHouse is an open-source columnar database built specifically for online analytical processing (OLAP). When the workload is analytics over large volumes of data, the architectural differences between the two matter enormously. In this post, we'll break down why ClickHouse consistently outperforms PostgreSQL for analytical queries, and where PostgreSQL remains the right choice.

Columnar Storage vs Row-Oriented Storage#

PostgreSQL stores data row by row. To compute a simple aggregate like the sum of one column over a billion rows, it has to read every row in its entirety - every column, whether the query needs it or not.

ClickHouse stores data column by column. An analytical query reads only the columns it references, which often means touching a fraction of the data on disk. For the wide tables and aggregate-heavy queries typical of analytics, this single design decision is responsible for order-of-magnitude performance differences.

Superior Data Compression#

Because values in a column share the same type and often have similar distributions, columnar data compresses exceptionally well. ClickHouse layers general-purpose codecs like LZ4 and ZSTD on top of specialized ones, routinely achieving dramatic reductions in storage footprint compared to the raw data size.

PostgreSQL's row-oriented layout compresses far less effectively. Its TOAST mechanism handles oversized values, but it's designed for storage limits, not for analytical compression. Less compression means more disk, more I/O, and slower scans.

Vectorized Query Execution#

ClickHouse processes data in vectors - batches of column values - rather than tuple by tuple. This vectorized execution model keeps CPUs busy with tight, cache-friendly loops and lets the engine parallelize aggregation across cores with very little overhead.

PostgreSQL's executor is designed for transactional workloads, where queries typically touch a small number of rows and must respect MVCC visibility rules. That per-row machinery is exactly what you want for OLTP, and exactly what you don't want when aggregating billions of events.

Scalability and Real-Time Ingestion#

ClickHouse is built to ingest high-velocity data and make it queryable immediately, which is why it shows up behind so many real-time analytics systems (we covered this in Real-time Analytics with ClickHouse). It also scales horizontally - tables can be sharded and replicated across nodes, and queries are distributed automatically.

PostgreSQL can be tuned for heavy read workloads, and extensions or partitioning help, but scaling analytical queries horizontally across a cluster is not its native model. Teams often end up bolting on read replicas and materialized rollups, then maintaining that complexity themselves.

Where PostgreSQL Still Wins#

None of this makes PostgreSQL the wrong tool - it makes it the right tool for a different job. If your workload is transactional - frequent small reads and writes, updates and deletes, strict consistency, foreign keys, complex constraints - PostgreSQL is excellent and ClickHouse is a poor fit. ClickHouse favors append-heavy data and handles updates and deletes as asynchronous mutations, not fast transactional operations.

The pattern that works well in practice: PostgreSQL as your system of record, ClickHouse as your analytics engine.

How GraphJSON Uses ClickHouse#

GraphJSON stores your JSON events in ClickHouse so you get all of these benefits without managing any infrastructure. Log events with a single POST, then explore them with the point-and-click visualizer or write raw ClickHouse SQL in our notebooks - the columnar performance is just there, even over billions of events. It's the same architecture that lets Cloudflare analyze millions of requests per second with ClickHouse.