Data Engineering·4 min read·May 15, 2025

When we reach for ClickHouse instead of Postgres

Postgres is our default for almost everything. ClickHouse is not a replacement for it - it is the answer to a specific question, and knowing which question matters more than knowing either database.

We default to Postgres. We have said this enough times that it is almost a running joke on our projects. So when we put ClickHouse into a stack, it is worth being clear about why — because it is not "Postgres but better", and treating it that way leads people somewhere bad.

They are answers to different questions

Postgres is a row store built for transactions. It is brilliant at "give me this user, update this order, fetch these twenty records by id, keep all of it consistent". The overwhelming majority of what an application does, all day, is that. This is why Postgres is our default and why it deserves to be most people's default.

ClickHouse is a column store built for analytical scans. It is brilliant at "scan a billion rows and give me the aggregate", and it is brilliant at it in a way a row store fundamentally cannot match, because the storage model is different all the way down. Columnar storage means an aggregate over one column does not have to read the other forty. Compression on a single column of similar values is dramatic. The whole engine is shaped around the large read-mostly scan.

So the real question is never "ClickHouse or Postgres". It is "is the workload I am about to add a transactional one or an analytical one". Get that right and the database almost picks itself.

The signal that it is time

In practice, the moment we start thinking about ClickHouse is a specific and recognisable one: analytical queries — dashboards, reports, large aggregations over historical data — have grown to the point where they are straining the same Postgres instance that is also trying to serve the application.

This is a genuine conflict, not a tuning problem. The big analytical scan and the fast transactional read want opposite things from a database. Run them on the same instance at scale and they degrade each other — the heavy report makes the app feel slow, the app's load makes the report unpredictable. That is the signal. It is not "Postgres is too slow", it is "Postgres is being asked to be two different databases at once".

On work involving large-scale analytics, that is exactly the split we make: Postgres keeps doing the transactional job it is excellent at, and the heavy analytical workload moves to ClickHouse, which is built for precisely that shape.

The pitfalls

Treating ClickHouse like Postgres. It is not a drop-in anything. It has a different consistency model, it is not built for frequent small updates and deletes, and a workload of many tiny transactional writes is close to the worst thing you can hand it. Teams that approach it as "Postgres with a faster GROUP BY" hit this within a week. ClickHouse rewards data that arrives in batches and is read in big scans. Design for that or do not bring it in.

Adding it too early. ClickHouse is a second database. That means a second thing to operate, a second place data lives, and a pipeline to keep them in sync. Below a certain scale, a well-indexed Postgres handles analytical queries perfectly well, and a read replica handles the contention. Bringing in ClickHouse before you actually have the problem is just buying yourself operational overhead with no payoff yet.

Forgetting the sync is now part of the system. Once analytical data lives in ClickHouse and transactional data lives in Postgres, the pipeline that moves data between them is a real component — it can lag, it can break, it can drift. It needs the same monitoring and idempotency thinking as any other pipeline. The two-database setup is not free; the price is that pipeline, and you should pay it knowingly.

What we would tell you

  • Postgres is the default. Start there. It will take you much further than people expect.
  • The trigger for ClickHouse is contention, not raw slowness — analytical scans and transactional reads fighting over one instance.
  • Match the database to the workload shape. Transactional means rows. Analytical-scan means columns. That is the whole decision.
  • Respect what ClickHouse is not. It is not for small frequent writes. Feed it batches, query it with big scans.

ClickHouse is a fantastic tool for the job it is built for, and the job it is built for is specific. Knowing which question you are asking is worth more than knowing either database inside out.

ClickHousePostgreSQLAnalyticsData EngineeringDatabases

Working on something similar?

We have probably hit the exact scaling or architectural bottlenecks you are navigating. Book a call and we'll walk through the technical trade-offs.