11  Time-Series Databases for IoT

import {InlineKnowledgeCheck} from "../assets/js/iotclass-inline-kc.js"

Prerequisites: Data Storage and Databases

This enables: Stream Processing | Anomaly Detection

11.1 Learning Objectives

By the end of this chapter series, you will be able to:

• Explain why traditional databases fail for high-velocity IoT time-series data
• Compare InfluxDB, TimescaleDB, and Prometheus architectures and use cases
• Design appropriate retention policies and downsampling strategies for IoT deployments
• Implement efficient queries for common IoT sensor data patterns
• Calculate storage requirements and optimize compression for production systems
• Select the right time-series database for specific IoT application requirements

In 60 Seconds

Time-series databases like InfluxDB, TimescaleDB, and QuestDB are purpose-built for the high-velocity, timestamped data streams IoT systems generate. Unlike relational databases, they automatically partition data by time, apply columnar compression, and provide built-in retention policies that delete or downsample old data. The selection decision hinges on three factors: cardinality (number of unique device+metric combinations), query complexity (SQL vs custom language), and existing infrastructure (PostgreSQL ecosystem vs purpose-built).

11.2 Key Concepts

• InfluxDB: A purpose-built time-series database using the Flux query language, optimized for extremely high cardinality (millions of unique tag combinations) and providing built-in tasks for alerting and downsampling
• TimescaleDB: A PostgreSQL extension converting tables into time-partitioned hypertables with automatic compression and continuous aggregates, offering full SQL compatibility for teams already using PostgreSQL
• QuestDB: A high-performance time-series database with a SQL dialect and SIMD-accelerated columnar storage, optimized for extremely fast ingestion (millions of rows/second) and time-series analytics
• Cardinality: The number of unique combinations of tag/label values in a time-series database – high cardinality (millions of unique device+metric pairs) can cause memory exhaustion in some databases
• Tag vs Field: In InfluxDB, tags are indexed metadata (device ID, location) enabling fast filtering, while fields are unindexed measurement values (temperature, pressure) stored in columnar format for compression
• Line Protocol: InfluxDB’s optimized text format for bulk writes: measurement,tag=value field=value timestamp – designed for minimal parsing overhead at high ingest rates
• Measurement: The InfluxDB equivalent of a database table, grouping related time-series data (e.g., ‘temperature’ measurement containing readings from thousands of sensors identified by tags)
• Batch Writing: Grouping multiple sensor readings into a single write request to reduce per-record overhead – essential for achieving high ingest rates; individual writes at high frequency cause HTTP connection overhead that limits throughput

11.3 MVU: Minimum Viable Understanding

Core concept: Time-series databases are purpose-built storage systems optimized for timestamped data with high write throughput, time-based queries, and efficient compression.

Why it matters: IoT sensors generate millions of readings per hour; traditional databases collapse under this load while TSDBs handle it with 10-100x better performance and 90%+ storage savings.

Key takeaway: Choose InfluxDB for pure metrics, TimescaleDB when you need SQL compatibility, or Prometheus for Kubernetes monitoring–and always implement retention policies from day one.

Sensor Squad: The Time-Traveling Data Keepers!

Hey there, future data scientists! Let’s learn about time-series databases with the Sensor Squad!

Meet the Squad:

• Sammy the Sensor - measures temperature every second
• Lila the Lightbulb - tracks energy usage all day
• Max the Motor - records speed and power constantly
• Bella the Battery - monitors charge levels non-stop

The Problem: Too Many Measurements!

Imagine Sammy takes a temperature reading EVERY SECOND. That’s: - 60 readings per minute - 3,600 readings per hour - 86,400 readings per day - Over 31 MILLION readings per year!

Now multiply that by thousands of sensors in a factory. That’s BILLIONS of measurements!

Why Regular Databases Struggle:

Think of a regular database like a filing cabinet:

📁 Traditional Database = Filing Cabinet
├── Find "Customer: Alice" ✅ Easy!
├── Find "Order #12345" ✅ Easy!
└── Find "All readings from 3pm to 4pm
    on Tuesday across 1000 sensors" ❌ SLOW!

It’s organized for looking up individual items, not for sorting through millions of time-stamped readings.

Time-Series Databases Are Special:

A TSDB is like a super-organized timeline:

📅 Time-Series Database = Smart Timeline
├── 3:00 PM → Sensor1: 72°F, Sensor2: 68°F, Sensor3: 75°F
├── 3:01 PM → Sensor1: 73°F, Sensor2: 68°F, Sensor3: 74°F
├── 3:02 PM → Sensor1: 72°F, Sensor2: 69°F, Sensor3: 75°F
└── ... millions more, all in perfect time order!

Real-World Example:

Your smart watch: - Records your heart rate every second - Tracks your steps throughout the day - Monitors your sleep patterns all night

That’s time-series data! A TSDB stores it efficiently so your watch can show you graphs of your activity over weeks and months.

Sammy the Sensor says: “Time-series databases are like having a super-organized diary that never forgets when things happened - even if you write millions of entries!”

For Beginners: What Makes Time-Series Data Special?

Time-series data has four distinctive properties that set it apart from typical business data:

• Timestamped: Every entry records when something happened – “At 3:45 PM, the temperature was 72 degrees F”
• Append-only: New data is always added at the end; you almost never update or delete past readings
• High volume: Thousands of sensors each producing readings every second adds up to millions of rows per day
• Time-range queries: The most common question is “show me what happened between time A and time B,” not “find record #12345”

Why can’t we use regular databases?

Traditional databases (like MySQL or PostgreSQL without extensions) use B-tree indexes optimized for looking up individual rows by primary key – think of finding a single book in a library catalog. Time-series workloads are fundamentally different: they need to scan large time ranges efficiently and write thousands of new rows per second without slowing down.

A regular B-tree index must be updated for every insert, causing write amplification (each new row triggers multiple disk writes to maintain the index). Time-series databases solve this with structures like Log-Structured Merge Trees (LSM Trees) that batch writes in memory and flush them sequentially – much faster for append-heavy workloads.

That’s why we need specialized time-series databases!

Interactive: Time Series Database Write Path

11.4 Chapter Overview

This chapter has been organized into five focused sections for easier learning. Work through them in order, or jump to the topic most relevant to your current needs.

11.4.1 Time-Series Database Architecture

The following diagram illustrates how time-series databases fit into an IoT data pipeline, from sensor data ingestion through storage, querying, and visualization:

Key architectural components:

Component	Purpose	Why It Matters
Write-Optimized Ingestion	High-throughput data acceptance	Handles millions of writes/second
Time-Partitioned Storage	Data organized by time intervals	Fast range queries, easy retention
Compression Engine	Reduces storage 10-100x	Cost savings, faster queries
Time-Based Indexing	Quick timestamp lookups	Sub-second query response
Query Engine	Aggregations, downsampling	Real-time analytics

11.4.2 Data Volume Reality Check

The following diagram illustrates the exponential growth of IoT data and why traditional databases cannot keep pace:

11.4.3 1. Time-Series Fundamentals

Why traditional databases fail and how TSDBs solve it

Your smart factory generates 18 million sensor readings per hour. Traditional databases struggle with this volume because they optimize for transactional consistency, not the append-only, time-stamped nature of sensor data. This section explains:

• Why row-based storage causes write amplification
• How LSM trees and columnar storage optimize for IoT workloads
• The three architectural pillars of time-series databases

11.4.4 2. Time-Series Platforms Comparison

InfluxDB vs TimescaleDB vs Prometheus

Not all time-series databases are created equal. Each platform makes different trade-offs between performance, query capabilities, and operational complexity. This section covers:

• InfluxDB: Native TSDB with Flux query language
• TimescaleDB: PostgreSQL extension with full SQL support
• Prometheus: Pull-based monitoring for Kubernetes environments
• Decision framework for platform selection

11.4.5 3. Retention and Downsampling Strategies

Managing data lifecycle and time synchronization

IoT data grows exponentially. Without retention policies, storage costs spiral out of control. This section addresses:

• Multi-tier retention policies (hot/warm/cold)
• Downsampling strategies with continuous queries
• Time synchronization pitfalls and clock drift
• Edge processing for intelligent data reduction

11.4.6 4. Query Optimization for IoT

Writing efficient queries and an interactive demo

Time-series queries differ fundamentally from traditional SQL. This section teaches:

• Common query patterns: last-value, time-range, anomaly detection
• Optimization techniques for high-cardinality data
• Best practices for production IoT workloads
• Interactive query builder and performance comparison tool

11.4.7 5. Time-Series Practice

Case study, worked examples, and hands-on lab

Apply your knowledge with real-world examples:

• Tesla case study: 12 billion events per day with InfluxDB
• Worked example: Industrial sensor monitoring design
• Hands-on ESP32 lab: Stream sensor data to InfluxDB Cloud

Try It: InfluxDB Flux Query Examples for IoT

Objective: InfluxDB uses the Flux query language, purpose-built for time-series operations. These examples show common IoT query patterns: last-value lookups, downsampling, retention policies, and anomaly window queries. Compare the expressiveness with equivalent SQL below.

# InfluxDB Flux Query Examples for IoT Sensor Data
# These queries run against InfluxDB 2.x via the influxdb-client-python library
# To test: pip install influxdb-client, then point to your InfluxDB instance

# ── 1. Last Value Query (most recent reading per sensor) ──
# Use case: Dashboard showing current state of all sensors
flux_last_value = '''
from(bucket: "iot_sensors")
  |> range(start: -5m)
  |> filter(fn: (r) => r._measurement == "temperature")
  |> filter(fn: (r) => r.location == "factory_floor_1")
  |> last()
  |> yield(name: "current_readings")
'''

# ── 2. Downsampling: 1-second data to 5-minute averages ──
# Use case: Reduce storage 300x for historical data
flux_downsample = '''
from(bucket: "iot_sensors")
  |> range(start: -24h)
  |> filter(fn: (r) => r._measurement == "temperature")
  |> aggregateWindow(every: 5m, fn: mean, createEmpty: false)
  |> yield(name: "5min_averages")
'''

# ── 3. Continuous Aggregate (runs automatically) ──
# Use case: Retention policy - keep raw for 7 days, aggregates forever
flux_continuous = '''
// This is a Flux task that runs every 5 minutes
option task = {name: "downsample_temp", every: 5m}

from(bucket: "iot_sensors")
  |> range(start: -10m)
  |> filter(fn: (r) => r._measurement == "temperature")
  |> aggregateWindow(every: 5m, fn: mean, createEmpty: false)
  |> to(bucket: "iot_sensors_downsampled", org: "my-org")
'''

# ── 4. Anomaly Detection: Moving average with threshold ──
# Use case: Alert when temperature deviates from 1-hour moving average
flux_anomaly = '''
import "math"

base = from(bucket: "iot_sensors")
  |> range(start: -2h)
  |> filter(fn: (r) => r._measurement == "temperature")

moving_avg = base
  |> timedMovingAverage(every: 1m, period: 1h)

// Join current values with moving average, flag deviations > 5C
join(tables: {current: base, avg: moving_avg}, on: ["_time"])
  |> map(fn: (r) => ({r with
    deviation: math.abs(x: r._value_current - r._value_avg),
    is_anomaly: math.abs(x: r._value_current - r._value_avg) > 5.0
  }))
  |> filter(fn: (r) => r.is_anomaly == true)
  |> yield(name: "anomalies")
'''

# ── 5. Retention Policy Configuration ──
flux_retention = '''
// Create buckets with different retention periods
// Raw data: 7 days retention
// influx bucket create --name iot_sensors --retention 7d

// Downsampled: 1 year retention
// influx bucket create --name iot_sensors_downsampled --retention 365d

// Summary: forever (0 = infinite retention)
// influx bucket create --name iot_sensors_summary --retention 0
'''

# Print all queries for reference
queries = [
    ("Last Value (current sensor state)", flux_last_value),
    ("Downsampling (5-min averages)", flux_downsample),
    ("Continuous Aggregate (auto-downsample)", flux_continuous),
    ("Anomaly Detection (moving avg deviation)", flux_anomaly),
    ("Retention Policy Config", flux_retention),
]

print("=== InfluxDB Flux Queries for IoT ===\n")
for name, query in queries:
    print(f"--- {name} ---")
    for line in query.strip().split('\n'):
        print(f"  {line}")
    print()

What to Observe:

1. Flux uses a pipe-forward (|>) syntax that chains operations – reads naturally as “get data, filter, aggregate, output”
2. aggregateWindow is purpose-built for time-series downsampling – one line replaces complex GROUP BY in SQL
3. Retention policies are set per-bucket, not per-table – InfluxDB automatically deletes data past the retention period
4. The anomaly query joins current values with a moving average – achievable in SQL only with complex window functions and subqueries
5. Continuous tasks run on a schedule inside InfluxDB, eliminating the need for external cron jobs

Try It: TimescaleDB SQL Queries for IoT

Objective: TimescaleDB extends PostgreSQL with time-series optimizations while keeping full SQL compatibility. This means your existing SQL knowledge, tools (pgAdmin, psql), and ORMs work unchanged. These examples show equivalent IoT queries in SQL.

-- TimescaleDB SQL Queries for IoT Sensor Data
-- These queries run on TimescaleDB (PostgreSQL extension)
-- Install: CREATE EXTENSION IF NOT EXISTS timescaledb;

-- ── Schema: Create a hypertable for sensor readings ──
-- Hypertables automatically partition data by time for fast range queries
CREATE TABLE sensor_readings (
    time        TIMESTAMPTZ NOT NULL,
    sensor_id   TEXT NOT NULL,
    location    TEXT,
    temperature DOUBLE PRECISION,
    humidity    DOUBLE PRECISION
);

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

-- Create index for common query pattern
CREATE INDEX idx_sensor_location ON sensor_readings (sensor_id, location, time DESC);

-- ── 1. Last Value: Current state of all sensors ──
-- TimescaleDB's last() is optimized to skip full table scans
SELECT DISTINCT ON (sensor_id)
    sensor_id,
    time,
    temperature,
    humidity
FROM sensor_readings
WHERE time > NOW() - INTERVAL '5 minutes'
ORDER BY sensor_id, time DESC;

-- ── 2. Downsampling: 5-minute averages ──
-- time_bucket() is TimescaleDB's built-in time grouping function
SELECT
    time_bucket('5 minutes', time) AS bucket,
    sensor_id,
    AVG(temperature) AS avg_temp,
    MIN(temperature) AS min_temp,
    MAX(temperature) AS max_temp,
    COUNT(*) AS reading_count
FROM sensor_readings
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY bucket, sensor_id
ORDER BY bucket DESC;

-- ── 3. Continuous Aggregate (materialized view, auto-refreshed) ──
-- Runs in background, keeps 5-min aggregates always up to date
CREATE MATERIALIZED VIEW sensor_5min
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('5 minutes', time) AS bucket,
    sensor_id,
    AVG(temperature) AS avg_temp,
    AVG(humidity) AS avg_humidity,
    COUNT(*) AS samples
FROM sensor_readings
GROUP BY bucket, sensor_id;

-- Auto-refresh policy: refresh last 1 hour every 5 minutes
SELECT add_continuous_aggregate_policy('sensor_5min',
    start_offset    => INTERVAL '1 hour',
    end_offset      => INTERVAL '5 minutes',
    schedule_interval => INTERVAL '5 minutes');

-- ── 4. Retention Policy: Auto-delete raw data after 30 days ──
SELECT add_retention_policy('sensor_readings', INTERVAL '30 days');

-- Keep aggregates for 1 year
SELECT add_retention_policy('sensor_5min', INTERVAL '365 days');

-- ── 5. Anomaly Query: Readings outside 3-sigma of rolling average ──
-- Uses window functions (standard SQL, works because TimescaleDB = PostgreSQL)
SELECT
    time,
    sensor_id,
    temperature,
    avg_temp,
    stddev_temp,
    ABS(temperature - avg_temp) / NULLIF(stddev_temp, 0) AS z_score
FROM (
    SELECT
        time,
        sensor_id,
        temperature,
        AVG(temperature) OVER w AS avg_temp,
        STDDEV(temperature) OVER w AS stddev_temp
    FROM sensor_readings
    WHERE time > NOW() - INTERVAL '2 hours'
    WINDOW w AS (
        PARTITION BY sensor_id
        ORDER BY time
        ROWS BETWEEN 60 PRECEDING AND CURRENT ROW
    )
) sub
WHERE ABS(temperature - avg_temp) / NULLIF(stddev_temp, 0) > 3.0
ORDER BY time DESC;

-- ── 6. Storage Compression (TimescaleDB native) ──
-- Enable compression on chunks older than 7 days
ALTER TABLE sensor_readings SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id',
    timescaledb.compress_orderby = 'time DESC'
);

SELECT add_compression_policy('sensor_readings', INTERVAL '7 days');

What to Observe:

1. time_bucket() replaces complex DATE_TRUNC logic – purpose-built for time-series grouping
2. Continuous aggregates run in the background and are queried like regular tables – no application-level cron needed
3. Retention policies automatically drop old chunks (not row-by-row DELETE), making cleanup fast and non-blocking
4. The anomaly query uses standard SQL window functions – any PostgreSQL developer can read and modify it
5. Compression is chunk-level (typically 90%+ for sensor data) and transparent to queries
6. The key advantage over InfluxDB: full SQL JOINs with relational data (e.g., JOIN sensor metadata, customer records)

Try It: TSDB Query Pattern Comparison

Objective: Compare how the same IoT queries look in InfluxDB Flux vs TimescaleDB SQL vs Prometheus PromQL to help you choose the right platform.

# Side-by-side TSDB Query Comparison for IoT Workloads

queries = {
    "Last Value (current sensor state)": {
        "InfluxDB (Flux)": '''from(bucket:"sensors")
  |> range(start:-5m) |> last()''',
        "TimescaleDB (SQL)": '''SELECT DISTINCT ON (sensor_id) *
FROM readings WHERE time > NOW() - INTERVAL '5 min'
ORDER BY sensor_id, time DESC;''',
        "Prometheus (PromQL)": '''temperature{location="floor1"}''',
    },
    "5-Minute Average": {
        "InfluxDB (Flux)": '''from(bucket:"sensors")
  |> range(start:-1h)
  |> aggregateWindow(every:5m, fn:mean)''',
        "TimescaleDB (SQL)": '''SELECT time_bucket('5 min', time),
  AVG(temp) FROM readings
  WHERE time > NOW() - INTERVAL '1 hour'
  GROUP BY 1;''',
        "Prometheus (PromQL)": '''avg_over_time(
  temperature[5m])''',
    },
    "Anomaly (>3 sigma)": {
        "InfluxDB (Flux)": '''// Requires join + math.abs
// ~15 lines of Flux code''',
        "TimescaleDB (SQL)": '''SELECT * FROM (
  SELECT *, AVG(temp) OVER w,
    STDDEV(temp) OVER w
  FROM readings WINDOW w AS (...))
WHERE z_score > 3;''',
        "Prometheus (PromQL)": '''// Not natively supported
// Use recording rules + Alertmanager''',
    },
}

print("=== TSDB Query Comparison for IoT ===\n")
for query_name, platforms in queries.items():
    print(f"  Query: {query_name}")
    print(f"  {'─' * 50}")
    for platform, code in platforms.items():
        lines = code.strip().split('\n')
        print(f"    {platform}:")
        for line in lines:
            print(f"      {line}")
    print()

# Decision matrix
print("=== Platform Selection Guide ===\n")
matrix = [
    ("Requirement",              "InfluxDB",    "TimescaleDB", "Prometheus"),
    ("SQL compatibility",        "No (Flux)",   "Full SQL",    "No (PromQL)"),
    ("JOIN with relational data","No",          "Yes",         "No"),
    ("Write throughput",         "Excellent",   "Very Good",   "Good"),
    ("Ad-hoc analytics",        "Good",        "Excellent",   "Limited"),
    ("Kubernetes-native",        "Via plugin",  "Via plugin",  "Native"),
    ("Edge deployment",          "InfluxDB OSS","TimescaleDB", "Not ideal"),
    ("Managed cloud",            "InfluxDB Cloud","Timescale Cloud","Grafana Cloud"),
    ("Learning curve",           "Medium",      "Low (SQL)",   "Low"),
    ("Ecosystem maturity",       "Established", "Growing",     "Established"),
]
for row in matrix:
    print(f"  {row[0]:<28} {row[1]:<15} {row[2]:<15} {row[3]}")

What to Observe:

1. InfluxDB Flux is most concise for pure time-series operations (aggregateWindow in one line)
2. TimescaleDB SQL is most powerful for complex analytics (JOINs, subqueries, window functions)
3. Prometheus PromQL is simplest for monitoring queries but lacks analytics capabilities
4. Choose InfluxDB when your team works exclusively with time-series data
5. Choose TimescaleDB when you need to JOIN sensor data with relational data (customer records, asset metadata)
6. Choose Prometheus when monitoring Kubernetes infrastructure with Grafana dashboards

11.5 Quick Reference: Platform Selection

Requirement	Recommended Platform
Pure metrics, simple deployment	InfluxDB
SQL compatibility, complex analytics	TimescaleDB
Kubernetes monitoring, pull-based	Prometheus
Edge deployment, minimal resources	InfluxDB Edge or QuestDB
Enterprise scale, managed service	InfluxDB Cloud or TimescaleDB Cloud

11.5.1 Platform Comparison Diagram

11.5.2 TSDB Feature Comparison

Understanding the architectural differences between the three major platforms helps in making informed decisions:

---

11.6 Knowledge Check

Test your understanding of time-series database concepts before diving into the detailed sections:

---

11.7 IoT TSDB Storage Calculator

Use this calculator to estimate storage requirements for your IoT deployment. Adjust the parameters to see how sensor count, sampling rate, and compression affect total storage needs.

viewof sensorCount = Inputs.range([1, 50000], {
  value: 1000,
  step: 1,
  label: "Number of sensors"
})

viewof sampleIntervalSec = Inputs.range([1, 3600], {
  value: 5,
  step: 1,
  label: "Sample interval (seconds)"
})

viewof bytesPerReading = Inputs.range([8, 256], {
  value: 32,
  step: 1,
  label: "Bytes per reading"
})

viewof compressionRatio = Inputs.range([1, 50], {
  value: 10,
  step: 1,
  label: "Compression ratio (x:1)"
})

viewof retentionDays = Inputs.range([1, 1825], {
  value: 365,
  step: 1,
  label: "Retention period (days)"
})

{
  const readingsPerDay = sensorCount * (86400 / sampleIntervalSec);
  const rawBytesPerDay = readingsPerDay * bytesPerReading;
  const compressedBytesPerDay = rawBytesPerDay / compressionRatio;
  const totalStorageBytes = compressedBytesPerDay * retentionDays;

  function formatBytes(bytes) {
    if (bytes >= 1e12) return (bytes / 1e12).toFixed(2) + " TB";
    if (bytes >= 1e9) return (bytes / 1e9).toFixed(2) + " GB";
    if (bytes >= 1e6) return (bytes / 1e6).toFixed(2) + " MB";
    if (bytes >= 1e3) return (bytes / 1e3).toFixed(2) + " KB";
    return bytes.toFixed(0) + " bytes";
  }

  function formatNumber(n) {
    if (n >= 1e9) return (n / 1e9).toFixed(2) + " billion";
    if (n >= 1e6) return (n / 1e6).toFixed(2) + " million";
    if (n >= 1e3) return (n / 1e3).toFixed(1) + "K";
    return n.toFixed(0);
  }

  const writesPerSec = sensorCount / sampleIntervalSec;

  const container = htl.html`<div style="background: #f8f9fa; border: 1px solid #dee2e6; border-radius: 8px; padding: 20px; margin-top: 10px; font-family: Arial, sans-serif;">
    <h4 style="margin: 0 0 15px 0; color: #2C3E50;">Storage Estimate Results</h4>
    <table style="width: 100%; border-collapse: collapse;">
      <tr style="border-bottom: 1px solid #dee2e6;">
        <td style="padding: 8px 0; color: #555;">Readings per day</td>
        <td style="padding: 8px 0; font-weight: bold; text-align: right;">${formatNumber(readingsPerDay)}</td>
      </tr>
      <tr style="border-bottom: 1px solid #dee2e6;">
        <td style="padding: 8px 0; color: #555;">Write throughput required</td>
        <td style="padding: 8px 0; font-weight: bold; text-align: right;">${formatNumber(writesPerSec)} writes/sec</td>
      </tr>
      <tr style="border-bottom: 1px solid #dee2e6;">
        <td style="padding: 8px 0; color: #555;">Raw storage per day</td>
        <td style="padding: 8px 0; font-weight: bold; text-align: right;">${formatBytes(rawBytesPerDay)}</td>
      </tr>
      <tr style="border-bottom: 1px solid #dee2e6;">
        <td style="padding: 8px 0; color: #555;">Compressed storage per day</td>
        <td style="padding: 8px 0; font-weight: bold; text-align: right; color: #16A085;">${formatBytes(compressedBytesPerDay)}</td>
      </tr>
      <tr style="border-bottom: 1px solid #dee2e6;">
        <td style="padding: 8px 0; color: #555;">Total storage (${retentionDays} days)</td>
        <td style="padding: 8px 0; font-weight: bold; text-align: right; color: #2C3E50; font-size: 1.1em;">${formatBytes(totalStorageBytes)}</td>
      </tr>
      <tr>
        <td style="padding: 8px 0; color: #555;">Savings from compression</td>
        <td style="padding: 8px 0; font-weight: bold; text-align: right; color: #E67E22;">${formatBytes(rawBytesPerDay * retentionDays - totalStorageBytes)} saved</td>
      </tr>
    </table>
    <div style="margin-top: 15px; padding: 10px; background: ${writesPerSec > 100000 ? '#fce4e4' : writesPerSec > 10000 ? '#fff3e0' : '#e8f5e9'}; border-radius: 4px; font-size: 0.9em;">
      <strong>Platform recommendation:</strong> ${
        writesPerSec > 100000
          ? "At " + formatNumber(writesPerSec) + " writes/sec, you need InfluxDB or a multi-node TimescaleDB cluster."
          : writesPerSec > 10000
          ? "At " + formatNumber(writesPerSec) + " writes/sec, both InfluxDB and TimescaleDB handle this well. Choose based on query needs."
          : "At " + formatNumber(writesPerSec) + " writes/sec, any TSDB (InfluxDB, TimescaleDB, or Prometheus) can handle this load."
      }
    </div>
  </div>`;
  return container;
}

---

11.8 Common Pitfalls

Avoid These Time-Series Database Mistakes

1. No Retention Policy from Day One

• Problem: Storage grows unbounded, costs explode
• Solution: Define retention policies BEFORE production deployment
• Example: Raw data (30 days) → Hourly aggregates (1 year) → Daily summaries (forever)

2. High-Cardinality Tags (see detailed example below)

• Problem: Using unique IDs (user_id, session_id) as tags creates millions of series, exhausting memory
• Solution: Use high-cardinality values as fields, not tags; keep tags to dimensions with fewer than 1,000 unique values
• Example: sensor_type=temperature (good tag, ~10 values) vs reading_id=abc123 (bad tag, millions of values)

3. Ignoring Clock Drift

• Problem: Edge devices with unsynchronized clocks produce out-of-order data
• Solution: Implement NTP synchronization and handle late-arriving data
• Example: Configure 5-minute grace period for late writes

4. Wrong Tool Selection

• Problem: Using InfluxDB when you need SQL JOINs, or PostgreSQL for pure metrics
• Solution: Match platform strengths to your actual requirements
• Example: Need to JOIN sensor data with customer records? Use TimescaleDB

5. No Downsampling Strategy

• Problem: Storing 1-second resolution data for 5 years
• Solution: Implement continuous queries that aggregate older data
• Example: Keep 1-second data for 7 days, then downsample to 1-minute

Label the Diagram

💻 Code Challenge

Order the Steps

11.9 Summary

This chapter series provides comprehensive coverage of time-series databases for IoT applications:

:

11.10 Concept Relationships

See Also

Prerequisites - Read these first: - Data Storage and Databases - General database fundamentals - Big Data Overview - Understanding IoT data volume challenges

Dive Deeper - Explore sub-chapters: - Time-Series Fundamentals - LSM trees, columnar storage, write amplification - Time-Series Platforms - InfluxDB vs TimescaleDB vs Prometheus - Time-Series Retention - Multi-tier retention and downsampling - Time-Series Queries - Query optimization patterns - Time-Series Practice - Tesla case study + ESP32 lab

Related Concepts:

• Stream Processing - Real-time processing before storage
• Data Quality Monitoring - Quality metrics for time-series
• Sharding Strategies - Scaling TSDBs horizontally

Practical Applications:

• Worked Examples - Smart building TimescaleDB sizing

11.11 What’s Next

If you want to…	Read this next
Understand WHY traditional databases fail at IoT scale	Time-Series Fundamentals
Choose between InfluxDB, TimescaleDB, and Prometheus	Time-Series Platforms
Design retention policies and downsampling strategies	Time-Series Retention
Write optimized time-series queries	Time-Series Queries
Practice with the Tesla case study and ESP32 lab	Time-Series Practice
Build real-time analytics pipelines	Stream Processing
Detect sensor failures and anomalies	Anomaly Detection

Recommended Learning Path

1. Read Time-Series Fundamentals (20 min)
2. Compare platforms in Time-Series Platforms (15 min)
3. Design retention policies (10 min)
4. Practice queries in the interactive demo (15 min)
5. Apply knowledge in the hands-on lab (30 min)

Total time: ~90 minutes for comprehensive understanding