Why We Added ClickHouse Alongside Aurora Instead of Moving Everything to Postgres

By Ordinal Science · 2026-05-25

Here is a situation we see more often than you might think. A company runs a fleet of vehicles. Each vehicle sends telematics data back home: location, speed, braking, engine status and so on. Across the fleet, that adds up to a few million records every day.

The company also has to keep those records for up to five years. That is a regulatory requirement, so deleting old data is not an option.

All of this data landed in a transactional database, Amazon Aurora. Aurora is an excellent database. It is built for the everyday work of running a business: creating records, updating them and looking up individual items quickly. It is not built to scan billions of rows to answer a single question.

That same Aurora database also ran the rest of the fleet operation. Vehicle records, maintenance, assignments and scheduling all lived there. Fleet management made up about 80% of what the system did. It worked well, and it had to stay.

The problem showed up in driver behavior analysis. The business wanted to understand how drivers behaved over time. Do they brake hard? Do they speed in certain areas? Are habits getting better or worse? With the data stuck in Aurora, this analysis could only look back 30 days. Anything longer was too slow to be useful.

Then a business case came up for extending that window to five years. That is a big jump. A few million records a day over five years means billions of rows. To analyze that much data quickly, we needed a column-based system. Column-based databases store each field together, rather than storing each full record together. That makes them lightening fast when you need to read a few fields across a huge number of rows, which is exactly what behavior analysis does.

We had two realistic paths. The first was to move the entire setup to Postgres. The second was to keep Aurora and run ClickHouse alongside it. Both deserve a fair look.

Moving everything to Postgres had real appeal. You end up with one database instead of two. Your team only has to learn, manage and monitor one system. There is no pipeline to copy data between databases, so there is nothing to fall out of sync. Postgres is mature, well understood and widely supported. It also has extensions that help with large data. Some add better handling of time-based data. Others add column-style storage. In theory, you could tune Postgres to handle both the fleet work and the analytics.

But the downsides were serious. First, a full migration puts the 80% of the system that already works at risk. Moving a live business system is never trivial. It means testing every process, planning a cutover and accepting some chance of disruption.

Second, Postgres is still a row-based database at heart. Extensions help, but you are asking one system to be great at two very different jobs.

Third, heavy analytics and day-to-day operations would compete for the same resources. A long-running query over five years of data could slow down the people trying to schedule maintenance or assign vehicles. Finally, storing billions of rows in a row-based format takes a lot of space, and that space costs money.

The second path was to leave Aurora alone and launch ClickHouse next to it. ClickHouse is a column-based database designed for exactly this kind of workload. It is built to scan very large tables and return answers quickly.

The biggest advantage here is that nothing breaks. The fleet management side keeps running as it always has. There is no risky migration and no cutover weekend. Each system does the job it is best at. Aurora handles the transactions. ClickHouse handles the analysis.

ClickHouse also compresses data very well. Telematics data is repetitive. The same vehicle IDs, the same sensor types and similar values show up again and again. Column storage takes advantage of that, so five years of data takes far less space than it would in a row-based database. ClickHouse also has built-in support for automatically removing data after a set period. That makes the five-year retention rule easy to enforce.

And because analytics run on their own system, a heavy query never slows down the operations team.

This path has its own costs. You now run two databases instead of one. Someone has to maintain both. You need a reliable way to get data into ClickHouse, whether that means sending telematics data there directly or copying it over from Aurora. That pipeline needs monitoring. There can be a short delay between when data arrives and when it shows up in reports. Some analysis needs fleet data, like which driver was assigned to which vehicle. That data lives in Aurora, so it has to be brought over too.

ClickHouse also has limits. It is not designed for frequent updates or deletes of individual records. It is a poor fit for the transactional work Aurora does well. Your team may need time to learn its quirks.

We chose ClickHouse running alongside Aurora. Here is why.

The fleet system was the core of the business. It worked. Rebuilding it to solve an analytics problem would have meant taking on a lot of risk for little gain on that side. Adding ClickHouse let us solve the actual problem without touching the part that was fine.

The workload was also a near-perfect match for ClickHouse. Telematics data is written once and rarely changed. It arrives in time order. It is queried in large batches, usually by vehicle, driver and date range. That is exactly what column-based systems are built for. Going from a 30-day window to a five-year window is not a small tuning job. It needs a tool designed for that scale.

The extra work of running two systems was real, but it was manageable. A data pipeline is a well-understood problem. The risk of a full migration was much harder to control.

So when does this approach make sense more broadly? It is a strong choice when a few things are true at once. You have a transactional system that works well and supports most of the business. You also have a growing pile of data that is mostly written once and read in bulk. Think sensor readings, event logs, clickstreams, financial ticks or machine data. And you need to analyze that data over long periods, often because of regulation or because long-term trends matter to the business.

In those cases, forcing one database to do everything usually means it does both jobs just well enough, at best. Adding a dedicated analytics database next to your existing system lets each one do what it does best. You protect what already works, and you unlock analysis that was out of reach before.

It is not always the right call. If your data is small, or your analytics needs are light, one well-tuned database is simpler and probably enough. But when the volume grows and the questions get bigger, running the right tool alongside the one you already trust is often the cleanest way forward.