Time-Series Database Selection

Choosing the right engine for IoT data ingestion and analysis – a deep dive into InfluxDB, TimescaleDB, and Prometheus.

Introduction

The world is awash with streams of data that change over time: temperature readings from a hive of bees, vibration signatures from a wind turbine, latency metrics from a fleet of autonomous drones. In the IoT era, those streams become the lifeblood of decision‑making, predictive maintenance, and, increasingly, self‑governing AI agents that act on the edge.

But a raw stream is useless without a place to store it efficiently, query it quickly, and retain it for the right amount of time. That place is a time‑series database (TSDB). Selecting the right TSDB is not a “pick‑a‑flavor” decision; it determines how fast you can ingest millions of points per second, how precisely you can slice data for a 10‑minute window, and whether your analytics pipelines can scale from a single apiary to a continent‑wide sensor network.

This article walks you through the three most widely‑adopted open‑source TSDBs—InfluxDB, TimescaleDB, and Prometheus—with a focus on IoT data ingestion and analysis. We’ll explore their architectures, performance numbers, query languages, operational models, and real‑world suitability for projects ranging from bee‑monitoring bee-monitoring to AI‑driven edge orchestration ai-agent-architecture. By the end, you’ll have a concrete framework for matching your data‑characteristics and business goals to the database that will keep your time‑series humming.

1. What Makes Time‑Series Data Unique?

Before we compare databases, it helps to understand the properties that set time‑series data apart from “regular” relational data.

A concrete IoT example

Imagine a regional apiary that equips 100 hives with 10 sensors each (temperature, humidity, CO₂, acoustic, weight, etc.). Each sensor streams 1 sample per second. The raw point rate is:

100 hives × 10 sensors × 1 sample/s = 1 000 points/s
≈ 86.4 M points per day
≈ 31.5 B points per year

Even if you compress each point to 16 bytes, that’s 500 GB of raw data per year—far beyond what a traditional relational database can handle without specialized storage tricks. The TSDB must:

• ingest > 1 k points/s continuously,
• support ad‑hoc queries like “average temperature per hive over the last 7 days”,
• provide down‑sampling pipelines to keep only hourly aggregates after 30 days,
• expose metrics to a Prometheus‑compatible scraper for alerting.

These requirements are the yardstick we’ll use for each database.

2. Core Selection Criteria

Choosing a TSDB is a multi‑dimensional decision. Below are the six criteria we consider most decisive for IoT workloads.

2.1 Write Throughput & Ingestion Model

• Peak write rate (points / second) the engine can sustain on commodity hardware.
• Batch vs. streaming ingestion: does the database accept line‑protocol batches, HTTP writes, or gRPC streams?
• Back‑pressure handling – can the system buffer spikes without dropping data?

2.2 Query Performance & Language

• Time‑range query latency for both narrow (single series) and wide (millions of series) scans.
• Richness of query language: native SQL, Flux, PromQL, or custom DSLs.
• Support for joins, sub‑queries, and complex analytics (e.g., moving averages, percentile estimators).

2.3 Storage Efficiency & Compression

• On‑disk size per point after compression.
• Retention policies and down‑sampling mechanisms.
• Cold‑storage integration (e.g., object storage for long‑term archiving).

2.4 Scalability & High Availability

• Horizontal scaling (sharding, clustering).
• Replication factor and automatic failover.
• Operational complexity: does the system require a separate orchestrator (Kubernetes, Consul, etc.)?

2.5 Ecosystem & Tooling

• Visualization (Grafana, Chronograf, etc.).
• Export/Import (CSV, Parquet, Kafka connectors).
• Community and commercial support (enterprise editions, SLA).

2.6 Cost of Ownership

• Hardware footprint (CPU, RAM, SSD).
• Licensing (open source vs. paid features).
• Operational overhead (backup, upgrade, monitoring).

We will evaluate InfluxDB, TimescaleDB, and Prometheus against each of these criteria, citing real‑world benchmark numbers wherever available.

3. InfluxDB – The Purpose‑Built Time‑Series Engine

InfluxDB is often the first name that pops up when you Google “time‑series database”. Its evolution from a single‑node, line‑protocol store (v0.8) to the modern InfluxDB 2.x platform reflects a shift from pure storage to a full observability stack (Flux, UI, alerting).

3.1 Architecture Overview

InfluxDB’s line protocol (measurement,tag1=val1,tag2=val2 field1=123,field2="abc" 1627846260) is lightweight (≈ 30 bytes per point) and can be sent via UDP, HTTP, or gRPC. The protocol’s simplicity makes it ideal for constrained edge devices.

3.2 Performance Numbers

• Write throughput – In benchmark tests on a 4‑vCPU, 16 GB RAM instance with NVMe SSD, InfluxDB 2.0 sustained 1.2 M points/s with 99.9 % durability (source: InfluxData internal benchmark, 2023).
• Query latency – A 7‑day range query over 10 M series returned in ≈ 850 ms when using the built‑in Flux optimizer.
• Compression – TSM files achieve 3–5× compression over raw line protocol; a typical IoT dataset (float + tags) compresses to ~12 bytes/point.

3.3 Retention & Down‑sampling

InfluxDB offers Retention Policies (RPs) that automatically drop data after a configurable period. Combined with Continuous Queries (CQs) or Tasks (Flux scripts), you can create down‑sampled aggregates:

option task = {name: "hourly_agg", every: 1h}
from(bucket: "raw")
  |> range(start: -1h)
  |> filter(fn: (r) => r._measurement == "temperature")
  |> aggregateWindow(every: 1h, fn: mean, createEmpty: false)
  |> to(bucket: "downsampled")

3.4 Ecosystem Highlights

• Grafana and Chronograf native plugins for dashboards.
• Telegraf agents for seamless sensor ingestion (supports MQTT, Modbus, BLE).
• Kafka Connect source/sink connectors for streaming pipelines.
• Enterprise clustering (up to 20 nodes) with Raft‑based consensus for HA.

3.5 Suitability for Bee‑Conservation IoT

• Edge‑friendly ingestion – Telegraf can run on Raspberry Pi‑class devices attached to hives, sending line protocol over LTE.
• Rich analytics – Flux’s statistical functions (e.g., holtWinters, percentile) enable predictive modeling of hive temperature trends.
• Alerting – InfluxDB’s integrated alerting can trigger webhook notifications to a self‑governing AI agent that adjusts ventilation fans.

Bottom line: InfluxDB shines when you need high write throughput, a flexible query language, and an all‑in‑one observability stack. Its enterprise clustering adds HA, but the open‑source core remains single‑node, which may limit horizontal scaling for massive deployments.

4. TimescaleDB – PostgreSQL with Time‑Series Power

TimescaleDB takes a different philosophy: extend PostgreSQL with time‑series capabilities. It leverages the maturity, tooling, and SQL familiarity of PostgreSQL while adding automatic partitioning and compression.

4.1 Architecture Overview

Because TimescaleDB is a PostgreSQL extension, you interact with it using standard SQL (SELECT, JOIN, WINDOW FUNCTIONS). This makes it approachable for teams already versed in relational databases.

4.2 Performance Numbers

• Write throughput – On a 8‑vCPU, 32 GB RAM machine with RAID‑10 SSDs, TimescaleDB 2.10 achieved 850 k points/s for a 5‑field schema (benchmark from TimescaleDB blog, 2024).
• Compression – For a dataset of 2 float fields + 3 tags, TimescaleDB’s native compression reduced storage to ~9 bytes/point, a ~3× improvement over uncompressed PostgreSQL and ~1.3× over InfluxDB’s TSM.
• Query latency – A 30‑day range query over 5 M series returned in ≈ 420 ms using PostgreSQL’s query planner with Timescale’s time‑bucket functions.

4.3 Retention & Down‑sampling

TimescaleDB provides policy‑based data management via the add_retention_policy and add_compression_policy functions:

SELECT add_retention_policy('temperature', INTERVAL '90 days');
SELECT add_compression_policy('temperature', INTERVAL '30 days');

Down‑sampling can be performed with time_bucket:

SELECT time_bucket('1 hour', ts) AS hour,
       avg(temp) AS avg_temp
FROM temperature
WHERE ts >= now() - INTERVAL '7 days'
GROUP BY hour
ORDER BY hour;

Because the down‑sampled data lives in the same PostgreSQL instance, you can JOIN it with relational tables (e.g., hive metadata) without leaving the database.

4.4 Ecosystem Highlights

• PostGIS – Spatial extensions enable geo‑analytics (e.g., mapping hive locations).
• pgAdmin, psql, and SQLAlchemy for familiar tooling.
• TimescaleDB Toolkit – a collection of pre‑built functions for forecasting (forecast_arima), anomaly detection, and more.
• Kubernetes Operator – simplifies HA deployments with automated scaling and backups.

4.5 Suitability for Bee‑Conservation IoT

• Relational joins – Easily combine sensor data with hive inventory tables (hive_id, species, owner).
• Complex analytics – Use PostgreSQL window functions to compute rolling 24‑hour averages, then feed results to an AI model that predicts brood health.
• Compliance – If you need to store data alongside regulatory metadata (e.g., GPS coordinates for protected habitats), Timescale’s native PostgreSQL compliance simplifies audits.

Bottom line: TimescaleDB is the go‑to choice when you want the power of SQL, strong relational features, and high compression, especially in environments where you already run PostgreSQL. Its write throughput is slightly lower than InfluxDB’s peak, but its query flexibility and ecosystem often outweigh that gap.

5. Prometheus – The Monitoring‑First TSDB

Prometheus originated in the cloud‑native monitoring space (Google’s Borgmon). It is pull‑based, storing metrics as time‑series identified by a metric name and a set of key‑value labels. While it excels at monitoring infrastructure, its design choices affect suitability for generic IoT ingestion.

5.1 Architecture Overview

Prometheus stores data in chunks of 2‑hour blocks, each compressed using Snappy. The storage format is immutable; new data is appended, and old blocks are deleted based on a retention time (default 15 days).

5.2 Performance Numbers

• Scrape rate – A single Prometheus server can handle ~40 k samples per second (≈ 3.5 M samples per minute) on a 16‑vCPU, 64 GB RAM node (official benchmark, 2023).
• Write latency – Since Prometheus pulls data, latency is bounded by scrape interval (commonly 15 s).
• Storage size – Roughly 2 KB per metric per hour after compression. For the hive example (1 k points/s = 86.4 M points/day), you’d need ~ 2.5 TB per year on a single node—far higher than InfluxDB or TimescaleDB.

5.3 Retention & Down‑sampling

Prometheus does not provide built‑in down‑sampling. Retention is a hard delete after a configurable period (--storage.tsdb.retention.time). To keep long‑term data, you must ship metrics to a remote storage such as Thanos or Cortex, which add global query, down‑sampling, and HA capabilities.

5.4 Ecosystem Highlights

• Grafana – First‑class Prometheus data source, with built‑in alert rule editors.
• Service Discovery – Automatic discovery of Kubernetes pods, Consul services, static targets.
• Exporters – Thousands of community exporters (node_exporter, blackbox_exporter) for pulling metrics from virtually any system.
• Alertmanager – Handles deduplication across multiple Prometheus instances.

5.5 Suitability for Bee‑Conservation IoT

• Pull‑based model – Requires each hive sensor to expose an HTTP endpoint that Prometheus can scrape. This is feasible for high‑power nodes but burdensome for low‑energy, battery‑operated devices.
• Metric semantics – Prometheus assumes monotonic counters or gauge values. Complex multi‑field records (e.g., acoustic FFT bins) become cumbersome.
• Scaling limits – Without remote storage, a single Prometheus instance cannot hold the multi‑year data needed for climate‑impact studies.

Bottom line: Prometheus is unbeatable for monitoring the health of services (CPU, memory, request latency) and for building alerting pipelines. For large‑scale, heterogeneous IoT sensor fleets, its pull‑based architecture and limited retention make it a secondary choice unless you already have a Thanos/Cortex stack in place.

6. Real‑World Benchmarks & Comparative Table

Below is a consolidated view of the three databases under a standardized benchmark that mimics the apiary scenario (1 k points/s, 10 fields per point, 30 days of data). All tests run on identical hardware: Intel Xeon E5‑2680 v4, 64 GB RAM, 2 × NVMe 2 TB.

Interpretation:

• If write velocity and edge‑friendly ingestion are paramount, InfluxDB leads.
• If relational joins, high compression, and SQL familiarity are needed, TimescaleDB wins.
• If you already run a Kubernetes‑centric monitoring stack and need instant alerts, Prometheus (with Thanos) can be leveraged, but expect to offload long‑term storage elsewhere.

7. Integration with IoT, Bees, and Self‑Governing AI Agents

A TSDB does not exist in isolation. Let’s walk through a realistic pipeline that ties sensor data, a TSDB, and an AI agent that autonomously regulates hive conditions.

7.1 Edge Data Collection

• Hardware – Raspberry Pi 4 with a Sense HAT (temperature, humidity, pressure) and a microphone for acoustic monitoring.
• Software – Telegraf (InfluxDB) or Prometheus Node Exporter (Prometheus) runs as a systemd service, reading sensor values every second.
• Transport – Data is sent over MQTT to a broker; Telegraf’s MQTT consumer forwards the line protocol to InfluxDB, while a custom exporter pulls the same MQTT topic for Prometheus.

7.2 Central Ingestion Layer

7.3 AI‑Driven Decision Engine

An edge AI agent (containerized Python service) subscribes to a Kafka topic that streams aggregated metrics