We Replaced ClickHouse with PostgreSQL and Got Faster

We recently shipped a fairly significant overhaul of Reflag’s data layer, and you should feel the difference immediately. The site is noticeably faster, search is snappier, and working with flags and segments feels smoother across the board.

This change started with a mismatch between how our system was originally designed and how we actually use it today.

How We Ended Up on ClickHouse

Due to earlier architectural decisions, we were storing targetable users and companies in ClickHouse. At the time, this made sense. Our ingestion pipeline was already writing event streams into ClickHouse, and extending that system to also power targeting felt like a natural architectural extension. We were already processing large volumes of event data there, so keeping related data in the same system reduced friction.

ClickHouse is fantastic for high-volume analytical queries across massive datasets. It’s built for aggregations and scanning billions of rows. And early on, that aligned well with what we were doing.

When the Workload Changed

But over time, our workload shifted. Instead of primarily running large analytical scans, most of the real-time traffic in Reflag became highly selective, relational queries powering targeting search: things like company/user search, and estimating targeted counts.

Those are index-heavy, low-latency lookups. They’re not wide analytical scans.

ClickHouse can handle them, but it’s not what it’s optimized for. As targeting became more central to the product, we increasingly felt that we were using the wrong tool for the job.

Consolidating on PostgreSQL

We were already heavy PostgreSQL users for our core flag and segment data, so consolidating became the obvious next step.

We migrated our targeting data into PostgreSQL and redesigned the schema around indexed lookups and relational filtering. With the right indices in place, PostgreSQL performs extremely well for the kinds of queries that now dominate Reflag.

Search is faster. For some customers, simple lookup queries that previously took 4–8 seconds now return in less than 200ms. As a side effect of consolidating the stack, our infrastructure costs for this part of the system dropped by roughly 50%.

Rewriting the Ingestion Pipeline

Because ClickHouse was tightly coupled to our ingestion layer, moving away from it wasn’t just a database swap.

We took the opportunity to rewrite the ingestion pipeline from the ground up so it writes directly into PostgreSQL. That allowed us to remove an entire layer of architectural indirection and simplify how data flows through the system.

The result is moving parts, less operational overhead, easier debugging, and faster iteration cycles, in addition to the performance improvements you see in the product.

As Reflag evolved, our needs shifted. Consolidating onto PostgreSQL reduced complexity while improving performance.

Happy shipping!