Time-Series Databases for IoT and Monitoring Applications

In This Guide

• 1. What Makes Time-Series Data Different
• 2. Database Comparison — InfluxDB, TimescaleDB, QuestDB
• 3. InfluxDB — The Time-Series Specialist
• 4. TimescaleDB — PostgreSQL for Time-Series
• 5. QuestDB — Speed-First Design
• 6. IoT Data Architecture Patterns
• 7. Frequently Asked Questions

Writing Sensor Data (Line Protocol)

# InfluxDB Line Protocol: measurement,tags fields timestamp
temperature,sensor=sensor-001,location=mumbai value=28.5 1706918400000000000
temperature,sensor=sensor-002,location=delhi value=22.1 1706918400000000000
humidity,sensor=sensor-001,location=mumbai value=65.2 1706918400000000000

# Python client
from influxdb_client import InfluxDBClient, Point
from influxdb_client.client.write_api import SYNCHRONOUS

client = InfluxDBClient(url="http://localhost:8086", token="my-token", org="my-org")
write_api = client.write_api(write_options=SYNCHRONOUS)

# Write individual points
point = Point("temperature") \
    .tag("sensor", "sensor-001") \
    .tag("location", "mumbai") \
    .field("value", 28.5) \
    .time(datetime.utcnow())
write_api.write(bucket="iot-data", record=point)

# Batch write (much faster — always batch in production)
points = [
    Point("temperature").tag("sensor", f"sensor-{i:03d}").field("value", reading)
    for i, reading in enumerate(sensor_readings)
]
write_api.write(bucket="iot-data", record=points)

Querying — SQL and Flux

-- InfluxDB 3.0 SQL: Average temperature per hour, last 24 hours
SELECT
    date_bin('1 hour', time) AS hour,
    sensor,
    AVG(value) AS avg_temp,
    MAX(value) AS max_temp,
    MIN(value) AS min_temp
FROM temperature
WHERE time > now() - INTERVAL '24 hours'
    AND location = 'mumbai'
GROUP BY hour, sensor
ORDER BY hour DESC;

Setup and Schema

-- Create a regular PostgreSQL table
CREATE TABLE sensor_data (
    time        TIMESTAMPTZ NOT NULL,
    sensor_id   TEXT NOT NULL,
    location    TEXT,
    temperature DOUBLE PRECISION,
    humidity    DOUBLE PRECISION,
    battery     DOUBLE PRECISION
);

-- Convert to hypertable (automatic time-based partitioning)
SELECT create_hypertable('sensor_data', by_range('time'));

-- Compression policy (10x storage reduction)
ALTER TABLE sensor_data SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id',
    timescaledb.compress_orderby = 'time DESC'
);
SELECT add_compression_policy('sensor_data', INTERVAL '7 days');

-- Retention policy (auto-delete old data)
SELECT add_retention_policy('sensor_data', INTERVAL '90 days');

-- Continuous aggregate (pre-computed hourly rollups)
CREATE MATERIALIZED VIEW sensor_hourly
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', time) AS hour,
    sensor_id,
    AVG(temperature) AS avg_temp,
    MAX(temperature) AS max_temp,
    MIN(temperature) AS min_temp,
    COUNT(*) AS readings
FROM sensor_data
GROUP BY hour, sensor_id;

-- Auto-refresh the continuous aggregate
SELECT add_continuous_aggregate_policy('sensor_hourly',
    start_offset => INTERVAL '3 hours',
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');

-- Create partitioned table
CREATE TABLE sensor_data (
    timestamp TIMESTAMP,
    sensor_id SYMBOL,          -- SYMBOL type: dictionary-encoded for fast grouping
    location SYMBOL,
    temperature DOUBLE,
    humidity DOUBLE
) timestamp(timestamp) PARTITION BY DAY WAL;

-- SAMPLE BY: time-series specific aggregation
SELECT
    timestamp,
    sensor_id,
    avg(temperature) AS avg_temp,
    max(temperature) AS max_temp,
    count() AS readings
FROM sensor_data
WHERE timestamp IN '2026-02'
SAMPLE BY 1h
FILL(LINEAR);    -- Interpolate missing data points

-- ASOF JOIN: join two time-series by closest timestamp
SELECT t.timestamp, t.temperature, h.humidity
FROM temperature_readings t
ASOF JOIN humidity_readings h ON (t.sensor_id = h.sensor_id);
-- Matches each temperature reading with the closest-in-time humidity reading

-- LATEST ON: get most recent value per sensor (common IoT query)
SELECT * FROM sensor_data
LATEST ON timestamp PARTITION BY sensor_id;

IoT Sensors (1000s of devices)
    │
    ├── MQTT Broker (Mosquitto / HiveMQ)
    │       │
    │       ▼
    │   Message Router (Kafka / NATS)
    │       │
    │       ├──→ Time-Series DB (raw data, 30-day retention)
    │       │       │
    │       │       ├──→ Continuous aggregates (hourly rollups, 1-year retention)
    │       │       └──→ Grafana dashboards (real-time monitoring)
    │       │
    │       ├──→ Rules Engine (threshold alerts)
    │       │       └──→ Alerting (PagerDuty, Telegram)
    │       │
    │       └──→ Cold Storage (S3 / data lake, archive)
    │
    └── Edge Processing (filter noise, aggregate at device)

Related Articles