# TimescaleDB + Grafana

> TimescaleDB time-series database built on PostgreSQL with Grafana for visualization.

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

## Overview

TimescaleDB extends PostgreSQL with specialized time-series capabilities, combining the reliability and SQL compatibility of PostgreSQL with automatic time-based partitioning, continuous aggregates, and up to 90% data compression. Originally developed by Timescale Inc. to address the limitations of traditional databases when handling massive volumes of time-stamped data, TimescaleDB has become a leading solution for IoT applications, financial markets, and infrastructure monitoring where you need both time-series performance and relational data features. Grafana complements TimescaleDB by providing rich visualization capabilities specifically designed for time-series data, creating dynamic dashboards that can display metrics, events, and trends stored in TimescaleDB hypertables. This combination leverages PostgreSQL's mature ecosystem while delivering the performance characteristics needed for high-volume time-series workloads, with Grafana's templating and alerting features providing operational insights. Organizations managing IoT sensor networks, financial trading systems, or infrastructure monitoring benefit from this stack because it offers the analytical power of SQL joins between time-series and relational data, automatic data lifecycle management through retention policies, and professional-grade visualizations that can handle millions of data points without sacrificing query performance.

## Features

- Automatic time-based partitioning (hypertables) for optimal query performance across large datasets
- Full PostgreSQL compatibility allowing use of existing PostgreSQL tools, extensions, and ORMs
- Continuous aggregates for real-time materialized views that automatically update as new data arrives
- Data compression achieving 90%+ storage reduction using TimescaleDB's columnar compression
- Native PostgreSQL data source integration in Grafana with optimized time-series query support
- Advanced Grafana templating with dashboard variables for dynamic filtering across time ranges and dimensions
- Built-in data retention policies for automatic deletion of old data based on time or storage constraints
- Time_bucket() functions for efficient time-based aggregations and downsampling in visualizations

## Use cases

- IoT sensor data collection and monitoring with contextual device metadata and real-time dashboards
- Financial market data storage combining tick data with reference information and trading analytics
- Infrastructure monitoring for long-term storage of Prometheus metrics with custom business dashboards
- Application performance monitoring with database-backed alerting and historical trend analysis
- Energy management systems tracking consumption patterns with billing integration and forecasting
- Manufacturing process monitoring combining sensor readings with production schedules and quality metrics
- Smart city applications aggregating traffic, weather, and utility data with public-facing dashboards

## Prerequisites

- Minimum 2.5GB available RAM (2GB for TimescaleDB, 512MB for Grafana) for production workloads
- Docker and Docker Compose installed with sufficient disk space for time-series data growth
- Ports 3000 (Grafana) and 5432 (TimescaleDB) available on the host system
- Basic SQL knowledge for creating hypertables and time-series queries in TimescaleDB
- Understanding of time-series data concepts like timestamps, metrics, and aggregation windows
- Familiarity with PostgreSQL connection parameters for configuring Grafana data sources

## docker-compose.yml

```yaml
services:
  timescaledb:
    image: timescale/timescaledb:latest-pg16
    container_name: timescaledb
    restart: unless-stopped
    environment:
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: ${POSTGRES_DB}
    volumes:
      - timescale_data:/var/lib/postgresql/data
    ports:
      - "5432:5432"
    networks:
      - timescale-network

  grafana:
    image: grafana/grafana:latest
    container_name: grafana
    restart: unless-stopped
    environment:
      GF_SECURITY_ADMIN_PASSWORD: ${GRAFANA_PASSWORD}
    volumes:
      - grafana_data:/var/lib/grafana
    ports:
      - "3000:3000"
    depends_on:
      - timescaledb
    networks:
      - timescale-network

volumes:
  timescale_data:
  grafana_data:

networks:
  timescale-network:
    driver: bridge
```

## .env

```bash
POSTGRES_USER=postgres
POSTGRES_PASSWORD=changeme
POSTGRES_DB=timeseries
GRAFANA_PASSWORD=admin
```

## Usage notes

- Docs: https://docs.timescale.com/
- Grafana at http://localhost:3000 (admin/admin) | TimescaleDB on port 5432
- Create hypertable: SELECT create_hypertable('metrics', 'time');
- Full PostgreSQL compatibility - use any PostgreSQL client/ORM
- Add Grafana data source: PostgreSQL, host=timescaledb:5432
- Compression: ALTER TABLE metrics SET (timescaledb.compress);

## Troubleshooting

- Grafana shows 'database connection failed': Verify POSTGRES_USER and POSTGRES_PASSWORD match in both services and TimescaleDB container is fully started
- TimescaleDB queries are slow on large tables: Ensure tables are converted to hypertables using SELECT create_hypertable('table_name', 'time_column') before inserting data
- Grafana dashboards timeout on large time ranges: Use time_bucket() aggregation functions instead of raw data queries and enable TimescaleDB compression
- TimescaleDB container fails to start with 'data directory not empty': Remove the timescale_data volume or check for conflicting PostgreSQL installations on port 5432
- Grafana shows 'plugin not found' for PostgreSQL: Restart Grafana container as the PostgreSQL data source plugin is built-in and may need reinitialization
- High memory usage in TimescaleDB: Tune shared_buffers and effective_cache_size in postgresql.conf, or add custom configuration via volume mount

