PostgreSQL excels at transactional workloads and small-scale analytics, but for real-world large-scale analytical queries ClickHouse is purpose-built to be faster, more efficient, and significantly more economical.

PostgreSQL is one of the most capable and well-loved relational databases in existence. It handles transactions reliably, supports a rich SQL dialect, has an enormous ecosystem of extensions, and runs just about everywhere. For many teams, it's the default starting point for any new project - and rightfully so.

But PostgreSQL was designed as an OLTP (Online Transaction Processing) database. Its architecture - row-oriented storage, MVCC-based concurrency, B-tree indexes - is optimized for reading and writing individual records with full ACID guarantees. When the workload shifts from "fetch this user's profile" to "compute the 95th percentile response time across 500 million events grouped by region and service," PostgreSQL starts struggling in ways that no amount of tuning can fully resolve. ClickHouse, a columnar OLAP (Online Analytical Processing) database, was built from the ground up for exactly that kind of query. This post covers where each database excels, why a PostgreSQL read replica can work for lightweight analytics, and at what point ClickHouse becomes the clearly superior - and more economical - choice.

Different Tools for Different Jobs

The core architectural difference between PostgreSQL and ClickHouse is the storage model. PostgreSQL stores data row by row: all columns for a single record live together on disk. This is ideal for transactional workloads where you typically read or write complete records - inserting an order, updating a user's email, or looking up a product by its primary key. Row-oriented storage means a single disk read retrieves everything about one entity.

ClickHouse stores data column by column: all values for a given column are packed together, independently from other columns. This layout is useless for point lookups on individual records, but it's dramatically more efficient when a query needs to scan millions of rows but only touch a few columns - which is exactly what analytical queries do. A query like SELECT region, avg(latency) FROM events WHERE timestamp > now() - INTERVAL 7 DAY GROUP BY region only needs to read three columns. In PostgreSQL, the engine must read entire rows from disk even though most columns are irrelevant.

This difference cascades into everything else. Columnar storage enables far better compression because values in the same column share the same data type and often similar values - ClickHouse routinely achieves 10x-30x compression ratios, and specific columns can compress over 100x. PostgreSQL's TOAST mechanism compresses oversized values but cannot match column-level compression across billions of rows. ClickHouse also leverages vectorized query execution, processing data in batches across CPU cores using SIMD instructions, while PostgreSQL processes rows one at a time (with limited parallel query support added in version 9.6 and incrementally improved since, but still far from columnar-level throughput for analytical work).

The write model differs too. PostgreSQL supports arbitrary updates and deletes with full ACID compliance - essential for transactional systems. ClickHouse is optimized for append-only, bulk insert workloads. Updates and deletes exist but are implemented as background mutations that rewrite parts asynchronously, making them expensive and unsuitable for high-frequency row-level modifications. This is by design: analytical workloads are overwhelmingly insert-heavy (events, logs, metrics, clickstreams) and rarely require modifying historical records.

Where PostgreSQL Read Replicas Reach Their Limits

A common and pragmatic first step for adding analytics to a PostgreSQL-backed application is to set up a read replica. Route your dashboards and reporting queries to the replica, keep transactional load on the primary, and you've got a clean separation without adding another database to your stack. For small-scale analytics - say, a few million rows, a handful of dashboards, queries that run in seconds rather than sub-second - this works well. It's simple, operationally familiar, and introduces no new technology.

The problems start when data volume grows beyond the tens of millions of rows, or when the analytical queries become more complex. PostgreSQL's row-oriented storage means analytical aggregations perform full table scans reading every column, regardless of how many the query actually needs. As PostHog discovered when building their analytics platform on PostgreSQL in 2020, "hack-y" workarounds like aggressive indexing, materialized views, and query restructuring can delay the inevitable, but they don't change the fundamental mismatch between the storage engine and the workload.

Benchmarks demonstrate the crossover point clearly. For datasets around 10K rows, PostgreSQL actually outperforms ClickHouse - with indexing, PostgreSQL handles small analytical queries about 2x faster due to lower overhead. At 50K rows, the two are roughly equivalent. But at 1M rows, ClickHouse is approximately 9x faster. At 10M rows, it's nearly 17x faster. And the gap only widens from there because the performance difference is structural, not incidental.

There are also extensions that attempt to bridge this gap. TimescaleDB adds time-series optimizations with chunked storage and continuous aggregates. Citus enables distributed query execution. The columnar access method (from Citus/Hydra) adds basic columnar storage to PostgreSQL. These are genuine improvements for specific use cases, but they are bolted onto a row-oriented engine. They don't give you ClickHouse's native columnar compression ratios, vectorized execution, specialized MergeTree table engines (SummingMergeTree, AggregatingMergeTree), or the ability to query billions of rows in sub-second latency. You're still paying for the overhead of PostgreSQL's MVCC, transaction management, and row-oriented internals even when you don't need any of it for your analytical workload.

The read replica approach also carries hidden operational costs. Running analytical queries against a replica means that replica needs enough CPU and memory to handle both replication and heavy aggregations. Materialized views in PostgreSQL require manual refreshes - and the fast refresh workarounds come with their own limitations. As data grows, the replica's hardware needs to scale vertically, and PostgreSQL's vertical scaling has practical limits that columnar databases are designed to surpass.

Why ClickHouse Dominates at Scale

ClickHouse's performance advantage for analytical workloads at scale comes from several architectural choices working together.

Columnar compression and storage efficiency. Because each column is stored independently, ClickHouse applies column-specific compression codecs (LZ4, ZSTD, Delta, DoubleDelta, Gorilla) that exploit the data patterns within each column. A column of timestamps compresses differently than a column of user IDs or status codes. The result is dramatically smaller data on disk - often 10x-30x smaller than the same data in PostgreSQL. Less data on disk means less data to read from disk, which translates directly to faster queries. This is not a minor optimization; it's the primary reason ClickHouse can query billions of rows interactively.

Vectorized execution. Rather than processing one row at a time, ClickHouse processes data in batches (blocks of columns) that fit in CPU cache and can be operated on using SIMD instructions. This is fundamentally more efficient for aggregations, filters, and group-by operations across large datasets. The query execution is often constrained by network latency rather than data processing time.

Specialized table engines. ClickHouse offers a family of MergeTree-based engines tailored for different workloads. SummingMergeTree automatically sums numeric columns during background merges - perfect for pre-aggregated counters. AggregatingMergeTree stores intermediate aggregation states for real-time rollups. Materialized views in ClickHouse update incrementally and automatically as data is inserted, unlike PostgreSQL where they require explicit refresh. These engines let you match storage and computation to your exact workload instead of forcing everything through a general-purpose row store.

Horizontal scaling. ClickHouse supports native sharding across multiple nodes with distributed query execution, managed by ClickHouse Keeper (a ZooKeeper-compatible coordination service). Adding nodes increases both storage capacity and query throughput. PostgreSQL's scaling story is primarily vertical - while replication and partitioning help, distributing analytical queries across nodes requires external solutions like Citus that don't approach ClickHouse's native distributed capabilities.

Real-world numbers back this up. PostHog, after migrating their analytics from PostgreSQL to ClickHouse, went from struggling with queries over millions of rows to routinely querying datasets of 2.7 billion events and 140 million person records - with individual queries scanning 300+ million rows and 20GB of data in around 20 seconds. They've since processed over 100 billion events on ClickHouse. The migration made their previous data problems, in their words, disappear. TechWolf reported reducing analytics query times from 5 minutes to 300 milliseconds after migrating from PostgreSQL to ClickHouse for 10M+ record datasets.

The Economics of Choosing the Right Tool

Beyond raw performance, there's a strong economic argument for using ClickHouse for large-scale analytics rather than scaling up a PostgreSQL deployment.

ClickHouse's compression ratios mean you store the same data with a fraction of the disk space. A dataset consuming 10TB in PostgreSQL might occupy 500GB-1TB in ClickHouse with equivalent or better query performance. This directly translates to lower storage costs in any cloud environment. The efficiency compounds with query execution - because ClickHouse reads less data from disk and processes it more efficiently through vectorized execution, you need fewer CPU cores and less memory to achieve the same (or better) query latency. Smaller instances, less disk, faster queries - the savings are multiplicative.

The alternative - continuing to scale PostgreSQL for analytics - means continuously upgrading to larger instance types, adding more replicas, building and maintaining materialized view refresh pipelines, and eventually fighting the row-oriented storage model at every turn. The operational complexity grows superlinearly with data volume. As the Hacker News discussion on this topic captured well, experienced engineers consistently recommend a hybrid approach: PostgreSQL for write-heavy OLTP operations (user accounts, settings, transactional data) combined with ClickHouse for analytical workloads, using Change Data Capture (CDC) to sync data between systems. This isn't overhead - it's the architecture that lets each database do what it was designed to do.

The CDC pipeline from PostgreSQL to ClickHouse is well-supported. ClickHouse Cloud offers ClickPipes for managed PostgreSQL CDC, and open-source tools like PeerDB handle on-premises deployments. The migration path is documented and battle-tested. You keep PostgreSQL as your transactional source of truth and replicate the data you need for analytics into ClickHouse, where it gets compressed, indexed, and made available for sub-second analytical queries.

Key Takeaways

PostgreSQL and ClickHouse are not competitors - they're complementary tools built for fundamentally different workloads. Here's the practical decision framework:

  • Small-scale analytics (under ~50K rows, simple dashboards): A PostgreSQL read replica is perfectly adequate. Don't add complexity you don't need.
  • Medium-scale analytics (millions of rows, growing query complexity): Start evaluating ClickHouse. PostgreSQL will still work, but you'll be spending increasing effort on workarounds - materialized views, aggressive indexing, larger hardware.
  • Large-scale analytics (tens of millions to billions of rows, real-time requirements): ClickHouse is the clear choice. The performance gap is 10x-1000x depending on the query pattern, storage costs are a fraction of PostgreSQL, and the architecture is purpose-built for this workload.

The most common production architecture we see - and recommend - is PostgreSQL handling OLTP workloads alongside ClickHouse handling analytics, connected via CDC. Each database operates in its sweet spot, and the overall system is simpler, faster, and cheaper than trying to force either database to do both jobs.

If you're evaluating ClickHouse for your analytics workload or planning a migration from PostgreSQL, reach out to our team. We've helped organizations of all sizes design, deploy, and optimize ClickHouse clusters in production.