Benchmarking five databases with 1.4 billion energy readings

Near the end of my internship at CAPE, I worked on a question that looked simple enough: should one of our clients move its energy data out of PostgreSQL, and if so, where should it go?

The answer turned into a benchmark involving five databases, 17 queries and about 1.4 billion rows. It also became the most useful thing I built during the internship.

The database was outgrowing its server

CAPE works with an energy company that receives smart-meter readings every 15 minutes. Each reading records either consumption (E17) or injection back into the grid (E18). Across thousands of meters, that adds about 1.9 million rows a day. Two years of history comes to roughly 1.4 billion rows.

The data lived in PostgreSQL on Azure. By the time our project started, the database occupied 174 GB and ordinary dashboard queries were running it out of memory. CAPE had recently doubled the server from 16 GB to 32 GB of RAM, roughly doubling the database bill in the process. More data arrived every day, so another hardware upgrade would only buy time.

We needed to decide whether a time-series database would handle this workload better. Our candidates were PostgreSQL, TimescaleDB, InfluxDB 2, QuestDB and ClickHouse.

A generic benchmark would answer the wrong question

Our initial plan was to review the literature, compare the candidates on a standard set of criteria and recommend one. That would have produced a respectable report, but I was not convinced it would produce a useful decision.

Benchmarks such as TPC-H and TSBS measure generic workloads. CAPE's workload was unusually specific. Its dashboard had to serve quick lookups for one meter, scan the full dataset for aggregate reports and group readings through a supplier hierarchy. Those hierarchy queries joined the measurements to metadata with time-validity windows, because a meter's place in the hierarchy could change over time.

Ingest speed mattered less. The client received readings in daily batches and had enough time to load them before they became visible in the dashboard. Query behaviour, especially the hierarchy joins, was the real constraint.

I wanted the recommendation to rest on that workload rather than on whichever database won a benchmark designed for something else, so I built our own test harness.

The benchmark

The main interface was a small FastAPI application running at localhost:8000. Its frontend was a single embedded HTML page with no build step or static-file server. I put it behind a Cloudflare tunnel so the team could use it from home or the office without opening ports on the test machine. From the UI, we could run individual queries, inspect charts, start the full benchmark and monitor data loading.

A separate CLI runner called the same API. It ran all 17 queries five times against each database, saved the results as JSON and generated comparisons for the first, best and median runs. Setup scripts handled the less visible work: converting the source data, generating a two-year dataset, building the hierarchy table and loading each database.

The client gave us a 4 GB JSON export as a starting point. My first attempt to inspect it ended with my editor crashing after I pressed Ctrl+A. That settled the question of whether I could work with it by hand.

I converted the export into a 28 MB Parquet seed containing 15 days of readings. Another script replayed that seed 48 times with shifted timestamps and a random variance of plus or minus 15 percent per block. The result was 1.3 GB of Parquet files representing about 1.4 billion rows and almost two years of history. Each database received the same generated dataset, and I verified every load with a row count.

Why InfluxDB 3 did not make the final benchmark

We also tried to include InfluxDB 3. It is a rewrite of InfluxDB in Rust, stores data with Apache Arrow and Parquet, and adds SQL support. On paper, it looked like a promising successor to InfluxDB 2. In my test environment, I could not get it into a state that I trusted enough to benchmark.

The first problem was a limit in the free Core edition on the number of Parquet files a query could scan. Loading the full dataset produced 432 files, so every query failed. I moved to the 30-day Enterprise trial, which removed that limit.

The next problem arrived 266 million rows into the ingest: the process ran out of memory and the container died. This happened several times. I changed the memory limits, WAL flush intervals, compaction settings and a Parquet cache flag before the load finally completed. Those changes reduced the ingest rate from about 95,000 rows per second to 20,000, turning every retry into an overnight job.

After the load, I ran one verification pass per query. Some queries failed, but enough worked that a full benchmark still seemed possible. The following day I used docker compose up -d --force-recreate to apply a configuration change. The command created a new container instance with the existing node-id.

That instance wrote a blank compaction summary at the active pointer. The previous 1,882 compaction cycles were no longer visible, which also made 3,337 compacted Parquet files invisible to the query engine. There was no warning or CLI repair command that I could find. Queries only revealed the damage later, when even simple selects began exhausting memory.

I cannot say with certainty whether this was a product bug, a configuration mistake or an interaction between the two. My reading of the failure is that reusing the node-id allowed a new instance to replace the compactor index without checking the existing state. Recovering meant loading the full dataset again, and the project deadline did not leave room for another attempt.

I excluded InfluxDB 3 rather than publish results from an environment I could not reproduce. In the smaller tests that did run, its timings were close to those of InfluxDB 2, so its absence did not leave us without an InfluxDB comparison.

What the measurements showed

I ran each of the 17 queries five times per database. The tables below show the best run, which indicates peak performance, and the first cold run before the database could benefit from cached data. Each database ran alone in an isolated Docker container with 20 GB of RAM on the same physical machine.

PostgreSQL remained excellent at indexed point lookups. It struggled when a query had to scan most of the 174 GB dataset, since only a small part of it could fit in memory.

TimescaleDB compressed the same data from 174 GB to 8 GB. That 21x reduction helped with full scans, but decompression made point lookups and queries that opened many chunks at once slower than plain PostgreSQL.

ClickHouse was the most consistent analytical database in the test. It was particularly strong on hierarchy joins and stayed competitive on range and full-dataset queries. Its weak spot was a lookup for one EAN: without a per-EAN index, it scanned more data and took just under a second.

QuestDB returned the fastest SQL point lookup at 2 ms and completed Q5 in 887 ms at its best. Its all-time hierarchy joins were much slower, taking between 96 and 107 seconds.

InfluxDB 2 won most queries in the first three tiers. Its problem was the workload that mattered most to the client. Flux materialised both sides of a join in memory, and the hierarchy queries took close to 3,000 seconds each.

Best run

Tier 1: Point queries (single meter, time-windowed)

Tier 2: Range aggregations (all meters, time-bucketed)

Tier 3: Full-scan aggregations (no time filter)

Tier 4: Hierarchy join queries (with time window)

Tier 5: Hierarchy join queries (all time, no window)

Cold run

Tier 1: Point queries

Tier 2: Range aggregations

Tier 3: Full-scan aggregations

Tier 4: Hierarchy join queries (with time window)

Tier 5: Hierarchy join queries (all time, no window)

* Tier 4 and 5 InfluxDB 2 queries were run once due to extreme execution time; the same result appears in both tables.

The fastest database was not the best choice

InfluxDB 2 had the best raw performance on most point, range and full-scan queries. We still did not recommend it as the main migration target.

The client's dashboard depends on aggregations by supplier, category and sub-category. To answer them correctly, the database must join readings to a validity-window table that records where each meter belonged at a given time. InfluxDB 2 took nearly an hour on those queries. Winning the simpler tiers did not compensate for failing the workload the dashboard used most.

Our final recommendation had two parts. ClickHouse was the better choice when analytical throughput mattered most. TimescaleDB offered the safer migration path: it kept the PostgreSQL driver and SQL interface, was available through the same Azure managed service, and supported continuous aggregates for predictable dashboard queries. Precomputing those results could reduce response times to milliseconds while avoiding decompression during each request.

What changed for me

Before this project, I thought of benchmarking mainly as a way to compare technologies. I now see it as a way to make the decision itself more precise. Once we translated the client's dashboard into 17 concrete queries, vague questions such as "Which database is fastest?" stopped being useful. The better question was "Which database is fast at the work this team cannot avoid?"

The project also felt different from a university assignment. Its scope changed as we learned about the real schema, infrastructure limits and budget. More importantly, someone could act on our recommendation. That made every unexplained result and failed load harder to wave away.

I left the internship wanting more of that kind of work: decisions with consequences, imperfect systems and people around me who can point out what I have missed.

The benchmark runner, setup scripts and queries are available in the tsdb-benchmark repository.