Postgres is a remarkable database, but it was designed for transactional workloads: row-oriented storage, MVCC, and ACID semantics. When analytics queries start scanning tens of millions of rows, joining multiple large tables, and running aggregations across time ranges, Postgres begins to show structural limits that no amount of indexing or query tuning can fully overcome. ClickHouse is designed for exactly this shape of work.
Why Columnar Storage Changes the Equation
Postgres stores each row together on disk. An analytics query that needs three columns from a 50-column table still reads all 50 columns per row. ClickHouse stores each column separately, compressed. A query reading three columns only touches three columns on disk. For analytics workloads where queries aggregate millions of rows across a handful of dimensions, this alone delivers order-of-magnitude performance improvements before any other optimisation.
When to Make the Switch
The signal is usually one of four things: dashboard queries exceeding two seconds, analytic queries affecting operational Postgres performance, the need for real-time aggregation over event streams, or storage costs becoming unsustainable as data volume grows. The migration decision is not just about query speed. It is about what your engineering team can operate and what your data access patterns actually look like.
What ClickHouse Does Not Replace
ClickHouse is not a Postgres replacement. It does not support transactions in the ACID sense. UPDATE and DELETE are expensive operations. It is not suited for high-write-concurrency OLTP workloads, row-level access patterns, or complex relational joins between many small tables. The typical production architecture is Postgres for operational data and ClickHouse as the analytical layer, fed by a CDC replication stream or a Kafka pipeline.
Migration Patterns That Work
- Dual-write with backfill — write to both systems in parallel, backfill historical data, validate query parity before switching dashboards
- CDC via Debezium — capture Postgres changes and stream to ClickHouse via Kafka, maintaining near-real-time sync without touching the Postgres write path
- Materialised views in ClickHouse — aggregate raw event streams into pre-computed dimensions at ingest time, making dashboard queries near-instant
- Schema redesign — denormalisation is expected in ClickHouse; resist the urge to replicate your normalised Postgres schema
Operational Considerations
ClickHouse Cloud removes most operational overhead: automatic sharding, replication, and scaling without managing clusters. For self-hosted deployments, plan for disk I/O as the primary bottleneck, use MergeTree family engines correctly, and understand that ClickHouse compaction happens asynchronously in the background. Monitoring merge lag and part counts matters more than CPU monitoring in most production setups.
The teams that migrate successfully treat it as a data architecture project, not a database swap. The query performance gains are real: sub-second aggregations over hundreds of millions of rows are routine, but only when the data model is designed for how ClickHouse actually works.