A three-way benchmark comparing Vanilla PostgreSQL, TimescaleDB, and InfluxDB against an 18,144,000-row IoT sensor dataset. 30 devices at 1-second intervals over 7 days. Five queries, each targeting a different TSDB capability. The experiment produces concrete performance numbers that answer the question data engineers actually face in production: when does a time series database justify the operational complexity of adding it to your stack?
Three-way comparison instead of two. The original scope was TimescaleDB vs InfluxDB. That comparison answers the wrong question — it assumes a TSDB is already justified and only asks which one to pick. Adding vanilla PostgreSQL as the baseline makes the experiment honest. The real question is whether a TSDB is worth it at all, and the answer depends entirely on which query you care about. Without the baseline, the 76.4× Q4 result has no context.
Parquet as the single source of truth for all three ingests.
generate_sensor.py writes once to data/sensors.parquet. All three ingest scripts read from this file. This guarantees the three databases contain identical data — same row count, same values, same timestamp distribution. Without this, performance differences could be attributed to data differences rather than database differences.
Seeded generation with per-device baselines. Each device has its own fixed temperature, humidity, and pressure baseline drawn from a seeded RNG. This produces realistic variation — sensor_001 and sensor_030 have different characteristic readings — rather than 30 devices returning identical values. The battery drain modelled as a linear decrease over 7 days means Q5 range scans return a visible trend rather than flat noise.
Chunk interval of 1 hour on the hypertable. A 1-hour chunk means 168 chunks across 7 days. This was chosen deliberately to make chunk pruning visible in the benchmark. A larger interval (1 day = 7 chunks) would reduce per-query file open overhead and make TimescaleDB look faster on Q1 and Q5. A smaller interval (15 minutes = 672 chunks) would increase overhead further. 1 hour is the standard recommendation for sub-daily IoT workloads and produces honest results.
Indexing after bulk load on vanilla PG.
ingest_postgres.py loads all 18M rows via COPY before building the btree indexes. PostgreSQL builds the entire index in a single pass after load — significantly faster than updating the btree incrementally on every insert. The same approach is used in TimescaleDB but the hypertable handles chunk management automatically. The index build time is reported in the ingest summary so the full cost is visible.
Fixed timestamp anchoring for benchmark queries.
The dataset is static — generated once and never updated. All queries that used NOW() were rewritten to reference the actual data range (2026-05-13 to 2026-05-19). NOW() returning 0 rows is a misleading result that looks like a query bug but is actually a data staleness issue. Anchoring to fixed timestamps makes the benchmark reproducible and honest regardless of when it is run.
Suppressing the MissingPivotFunction warning rather than fixing it.
The InfluxDB client emits a MissingPivotFunction warning on Q2 and Q3 because those Flux queries do not call pivot(). The warning is cosmetically noisy but technically harmless for those queries — Q2 uses timedMovingAverage which returns a single field, not multiple fields that need pivoting. Silencing it with warnings.simplefilter("ignore", MissingPivotFunction) is the right call rather than adding an unnecessary pivot that would change the query semantics.
InfluxDB token lost on every docker compose down -v.
The operator token is generated at container init time and stored in the InfluxDB volume. Running docker compose down -v destroys the volume and the token with it. Every fresh stack start requires retrieving a new token via docker exec influxdb influx auth list. This is not a bug — it is correct behaviour — but it is operationally painful and caught me multiple times. The fix for a long-running environment is to set DOCKER_INFLUXDB_INIT_ADMIN_TOKEN in the compose file as a static token that survives restarts.
InfluxDB timeout at batch 952 of 1815 during first ingest.
The initial BATCH_SIZE = 10_000 was too large for the container to handle under sustained load. InfluxDB timed out mid-write after ~9.5M rows. Reducing to BATCH_SIZE = 5_000 resolved this. The root cause is that InfluxDB converts each batch of line protocol strings to its internal TSM storage format synchronously before returning a response. Larger batches mean longer server-side processing per HTTP request and higher timeout risk on a memory-constrained machine.
TimescaleDB chunk report query using wrong column name.
pg_stat_user_indexes.indexname does not exist — the correct column is indexrelname. This threw a UndefinedColumn error at the storage report step of ingest_postgres.py. The ingest itself completed successfully. The fix was straightforward but it came after the 18M row load had already completed — the error appeared only at the final reporting step.
Port collisions from the original shared compose file.
The project was initially scoped with a shared docker-compose.yml that accumulated services from Projects 03 and 04. This caused port confusion — TimescaleDB was configured at port 5434 in some scripts and 5433 in others depending on whether the shared or silo compose was in effect. Switching to a fully isolated silo compose file per project resolved this and is now the enforced standard for all remaining projects.
Q2 and Q5 returning 0 rows on first benchmark run.
Both queries used NOW() as the time anchor. The dataset ends at 2026-05-19 23:59:59 UTC. By the time the benchmark ran, NOW() had moved several days past that boundary, returning 0 rows. Fixed by anchoring all queries to the actual data range. This is a fundamental issue with static benchmark datasets and relative time predicates — always anchor to the data, not to the clock.
The continuous aggregate multiplier is real and large.
Q4 at 76.4× over vanilla PG is not a rounding error or a warm cache effect. TimescaleDB reads sensor_readings_hourly — a pre-computed materialised view — and returns 50 rows in 0.003s. Vanilla PG aggregates 18,144,000 raw rows and returns the same 50 rows in 0.206s. InfluxDB is slower than vanilla PG on the same query at 0.349s. The continuous aggregate is the single most impactful optimisation in the entire project. If your dominant workload is aggregate queries over historical time series data, this result alone justifies TimescaleDB.
TimescaleDB is not universally faster than vanilla PG.
Q1 showed TimescaleDB at 9.3s versus vanilla PG at 2.6s — 3.6× slower on the same query. Q5 showed TimescaleDB at 0.32s versus vanilla PG at 0.19s — 1.65× slower. Both results come from the same mechanism: chunk file overhead. For DISTINCT ON (device_id) ORDER BY ts DESC, TimescaleDB must open 168 hourly chunk files and find the latest row in each before returning the result. Vanilla PG walks one contiguous btree index. Hypertables trade single-file scan efficiency for time-range pruning efficiency. On queries that do not benefit from pruning — latest value lookups, single-series narrow range scans — the chunk overhead is pure cost.
InfluxDB's last() is genuinely purpose-built.
83.9× faster than vanilla PG on Q1 is not a coincidence. InfluxDB maintains a separate last-value index per series. The answer to "what is the latest temperature reading for sensor_001" is a direct index lookup — O(1) regardless of how many rows exist. This is the correct data structure for the query pattern. PostgreSQL has to find the answer by searching.
Gap detection is a poor fit for InfluxDB at scale.
Q3 timed out on InfluxDB across all three runs. The elapsed() function in Flux computes inter-row time differences, which requires materialising and ordering all rows per series before filtering. At 18M rows across 30 series, this exceeds InfluxDB's default 10-second query timeout. This is not a solvable tuning problem — it is an architectural mismatch. InfluxDB is built for bounded time-range queries on specific series. Unbounded cross-series temporal analysis is the wrong use case.
Vanilla PG is a reasonable choice at moderate scale with the right index.
Q5 at 0.194s for 86,400 rows using a composite (device_id, ts DESC) btree index is not slow. Q2 at 0.078s for a 3,600-row rolling window is fast. A well-indexed PostgreSQL table handles IoT sensor data at this scale without any extension. The case for TimescaleDB becomes compelling specifically at the intersection of: high row counts, wide historical time ranges, and aggregate query patterns. Below that threshold, vanilla PG is simpler and competitive.
Parameterise the benchmark against multiple dataset sizes. Running the same 5 queries against 1M, 5M, and 18M rows would show where each database's performance characteristics diverge. The TimescaleDB chunk pruning advantage on Q3 was 1.3× at 18M rows. At 100M rows it would likely be 5-10×. The InfluxDB timeout on Q3 might appear earlier — at 5M rows rather than 18M. A size curve makes the results more useful for production capacity decisions.
Add write throughput to the benchmark. This project only benchmarks reads. In production, IoT workloads are write-heavy — sensors produce data continuously. InfluxDB's line protocol is designed for sustained high-throughput writes. TimescaleDB's COPY is fast at bulk load but chunk creation adds overhead for streaming inserts. Vanilla PG handles appends well with a partial index on recent data. A sustained write benchmark — 10,000 rows/s for 60 seconds — would round out the picture.
Set DOCKER_INFLUXDB_INIT_ADMIN_TOKEN in the compose file.
The token retrieval step after every docker compose down -v is operational friction that would not exist in a real environment. A static token defined at compose time survives restarts and makes the pipeline fully scriptable without manual intervention.