# TimescaleDB IoT Analytics

> TimescaleDB for time-series with MQTT and Grafana.

- Category: Database Stacks
- Difficulty: intermediate
- Components: timescaledb, grafana, mosquitto
- Tags: timescaledb, iot, mqtt, time-series
- Source: https://docker.recipes/databases/timescaledb-iot-stack

## Overview

TimescaleDB is a PostgreSQL extension specifically designed for time-series data that combines the reliability and SQL capabilities of PostgreSQL with specialized time-series functionality including automatic partitioning, continuous aggregates, and data compression. Originally developed to handle the massive scale of time-series data in IoT and monitoring applications, TimescaleDB provides up to 90% data compression while maintaining full PostgreSQL compatibility. This IoT analytics stack combines TimescaleDB with Eclipse Mosquitto MQTT broker and Grafana to create a complete pipeline for collecting, storing, and visualizing IoT sensor data in real-time. The architecture allows MQTT devices to publish sensor readings to Mosquitto, which can then be processed and stored in TimescaleDB's hypertables for efficient time-series queries, while Grafana provides rich dashboards and alerting capabilities. This combination is particularly valuable for IoT deployments because it handles the entire data lifecycle from ingestion through visualization while providing enterprise-grade reliability and SQL query capabilities that make it easy to correlate time-series data with relational business data.

## Features

- Automatic time-based partitioning through TimescaleDB hypertables for efficient IoT data storage
- Up to 90% data compression with TimescaleDB's native compression algorithms
- Full PostgreSQL compatibility allowing complex SQL joins between time-series and relational data
- MQTT broker with Eclipse Mosquitto supporting IoT device connectivity and pub/sub messaging
- Continuous aggregates for real-time computation of averages, sums, and other metrics
- Native PostgreSQL extensions work with TimescaleDB including PostGIS for location data
- Rich time-series visualizations in Grafana with built-in TimescaleDB data source support
- Data retention policies for automatic cleanup of old IoT sensor data

## Use cases

- Smart building management systems collecting temperature, humidity, and energy consumption data
- Industrial IoT monitoring for equipment performance, vibration analysis, and predictive maintenance
- Agricultural sensor networks tracking soil moisture, weather conditions, and crop health metrics
- Vehicle fleet tracking with GPS coordinates, fuel consumption, and engine diagnostics
- Environmental monitoring stations collecting air quality, noise levels, and weather data
- Smart city infrastructure monitoring traffic patterns, parking availability, and utility usage
- Home automation systems integrating multiple IoT devices with long-term trend analysis

## Prerequisites

- Minimum 3GB RAM for all services (TimescaleDB 2GB, Grafana 512MB, Mosquitto 256MB)
- Docker and Docker Compose installed with support for multi-container networking
- Available ports 5432 (TimescaleDB), 1883 (MQTT), and 3000 (Grafana) or custom port configuration
- Basic understanding of SQL and PostgreSQL for creating hypertables and queries
- MQTT protocol knowledge for configuring IoT device connections
- Understanding of time-series data concepts like retention policies and continuous aggregates

## docker-compose.yml

```yaml
services:
  timescaledb:
    image: timescale/timescaledb:latest-pg15
    container_name: timescaledb
    restart: unless-stopped
    ports:
      - "${TSDB_PORT:-5432}:5432"
    environment:
      - POSTGRES_USER=${TSDB_USER}
      - POSTGRES_PASSWORD=${TSDB_PASSWORD}
      - POSTGRES_DB=iot_data
    volumes:
      - timescale_data:/var/lib/postgresql/data

  mosquitto:
    image: eclipse-mosquitto:2
    container_name: iot-mqtt
    restart: unless-stopped
    ports:
      - "${MQTT_PORT:-1883}:1883"
    volumes:
      - ./mosquitto.conf:/mosquitto/config/mosquitto.conf:ro

  grafana:
    image: grafana/grafana:latest
    container_name: iot-grafana
    restart: unless-stopped
    ports:
      - "${GRAFANA_PORT:-3000}:3000"
    environment:
      - GF_SECURITY_ADMIN_PASSWORD=${GRAFANA_PASSWORD}
    volumes:
      - grafana_data:/var/lib/grafana

volumes:
  timescale_data:
  grafana_data:
```

## .env

```bash
# TimescaleDB IoT
TSDB_PORT=5432
TSDB_USER=iot
TSDB_PASSWORD=iot_password
MQTT_PORT=1883
GRAFANA_PORT=3000
GRAFANA_PASSWORD=admin
```

## Usage notes

- TimescaleDB at localhost:5432
- MQTT broker at localhost:1883
- Grafana at http://localhost:3000
- Create hypertables for time-series

## Troubleshooting

- TimescaleDB connection refused: Ensure POSTGRES_USER and POSTGRES_PASSWORD environment variables are set and container has fully initialized
- Mosquitto authentication failures: Check mosquitto.conf file permissions and verify it's mounted read-only to /mosquitto/config/mosquitto.conf
- Grafana TimescaleDB datasource connection errors: Use service name 'timescaledb' as hostname, not localhost, due to Docker networking
- Hypertable creation fails: Connect to TimescaleDB first and run 'CREATE EXTENSION IF NOT EXISTS timescaledb;' before creating hypertables
- MQTT messages not persisting: Verify database connection from your MQTT consumer and check that hypertables are created with proper time column
- High memory usage in TimescaleDB: Enable compression on older data chunks and configure appropriate retention policies for IoT data lifecycle

