Time-Series Database

Time-Series Database

A Time-Series Database (TSDB) is a type of database optimized specifically for storing, retrieving, and analyzing time-stamped data, i.e., data points indexed by time. This is common in monitoring systems, IoT sensors, finance, telemetry, and any system that generates data continuously over time.

Unlike traditional relational databases, TSDBs are optimized for high write throughput (ingesting large amounts of data quickly) and efficient time-based queries.

Key Features of Time-Series Databases

  1. Time-stamped Data: Every record has a timestamp.
  2. High Ingestion Rate: Handles millions of data points per second.
  3. Efficient Aggregation: Supports queries like averages, min/max, sum, over time windows.
  4. Retention Policies: Automatically expire old data to save space.
  5. Compression: Stores data efficiently using time-based compression techniques.

Popular Time-Series Databases

  • InfluxDB – widely used for metrics and monitoring.
  • TimescaleDB – a PostgreSQL extension for time-series.
  • Prometheus – popular in monitoring cloud-native systems.
  • OpenTSDB – built on top of HBase.
  • ClickHouse – columnar DB used for analytics including time-series data.

Examples of Time-Series Queries

1. InfluxDB (InfluxQL) Example

Suppose you have a measurement temperature with fields value and tags location.

-- Query: Get the average temperature in the last 24 hours
SELECT MEAN("value") 
FROM "temperature" 
WHERE time > now() - 24h 
  AND "location" = 'office';

-- Query: Get maximum temperature per hour
SELECT MAX("value") 
FROM "temperature" 
WHERE time > now() - 7d 
GROUP BY time(1h);

2. TimescaleDB (SQL on PostgreSQL) Example

Assume a table sensor_data(timestamp, sensor_id, value).

-- Query: Average sensor value per day
SELECT time_bucket('1 day', timestamp) AS day,
       AVG(value) AS avg_value
FROM sensor_data
WHERE sensor_id = 101
GROUP BY day
ORDER BY day;

-- Query: Get last 10 readings for a sensor
SELECT *
FROM sensor_data
WHERE sensor_id = 101
ORDER BY timestamp DESC
LIMIT 10;

3. Prometheus (PromQL) Example

Suppose you have a metric http_requests_total.

# Query: Total HTTP requests in the last 5 minutes
sum(rate(http_requests_total[5m]))

# Query: Average CPU usage per instance
avg by(instance) (rate(cpu_usage_seconds_total[1m]))

Use Cases

  • IoT & Sensors: Temperature, humidity, and energy readings.
  • Monitoring & Observability: CPU, memory, network metrics.
  • Finance: Stock prices, trade volumes.
  • Log & Event Analysis: Application logs, website hits.

Leave a Reply