Time-Series Databases Explained — InfluxDB, TimescaleDB, Prometheus, Use Cases

In this tutorial, you'll learn about Time. We cover key concepts, practical examples, and best practices to help you understand and apply this topic effectively.

Time-series databases are purpose-built database systems optimized for storing, querying, and analyzing time-stamped data points — sensor readings, metrics, logs, and financial ticks — with features like automatic downsampling, retention policies, and continuous aggregation queries.

What You'll Learn

You'll understand time-series Data Modeling with tags and fields, InfluxDB query language (Flux), TimescaleDB hypertables, Prometheus metrics collection, downsampling strategies, retention policies, and when to use a TSDB versus a general-purpose database.

Why It Matters

General-purpose databases struggle with time-series workloads: millions of writes per second, append-only patterns, and range queries over time ranges. Durga Antivirus Pro collects millions of system metrics per second; a TSDB stores and queries this data 10-100x more efficiently than PostgreSQL or MySQL.

Real-World Use

A manufacturing company stored IoT sensor data (temperature, vibration, pressure) in MySQL. Queries for "average temperature over the last month" took 45 seconds. Moving to TimescaleDB reduced the same query to 200ms and cut storage by 60% through built-in compression.

Time-Series Databases Learning Path

flowchart LR
  A[SQL Basics] --> B[Database Design]
  B --> C[Time-Series Databases]
  C --> D[Monitoring & Observability]
  C:::current
  classDef current fill:#f90,color:#fff,stroke:#333,stroke-width:2px

What Makes Time-Series Data Different

Time-series data has unique characteristics that make relational databases suboptimal:

InfluxDB

InfluxDB is a purpose-built TSDB using a SQL-like query language (Flux) and a custom storage engine optimized for time-stamped data.

Data Model

InfluxDB organizes data into buckets, measurements, tags (indexed), and fields (unindexed).

-- InfluxDB Flux query: Write data to a bucket
// Write a temperature reading at a specific time
import "influxdata/influxdb/v1"

// Data point: measurement=sensor, tags={location=warehouse, unit=Celsius}
// fields: temperature=23.5, humidity=65.2
// timestamp: 2026-06-22T10:00:00Z

// Query: Average temperature by location over last hour
from(bucket: "sensor_data")
  |> range(start: -1h)
  |> filter(fn: (r) => r._measurement == "sensor" and r._field == "temperature")
  |> group(columns: ["location"])
  |> mean()

Expected output:

_result: table
 location   | _value
------------+--------
 warehouse  | 23.5
 office     | 24.1
 server_rm  | 22.8

Retention Policies

InfluxDB automatically deletes or downsamples old data.

-- Create a retention policy (InfluxDB v1 syntax)
CREATE RETENTION POLICY "one_year"
ON "my_database"
DURATION 365d
REPLICATION 1
DEFAULT;

-- Create a continuous query to downsample
CREATE CONTINUOUS QUERY "cq_hourly"
ON "my_database"
BEGIN
  SELECT mean(temperature) AS avg_temp
  INTO "one_year"."sensor_hourly"
  FROM "sensor_data"
  GROUP BY time(1h), location
END;

Expected behavior: Raw data is kept for 7 days, hourly averages for 1 year, daily averages for 5 years. Queries for monthly trends hit the aggregated (downsampled) data automatically.

TimescaleDB

TimescaleDB is an extension for PostgreSQL that adds hypertables — transparently partitioned tables optimized for time-series data.

Hypertables

A hypertable automatically partitions data by time and an optional partition key.

-- Create a hypertable for IoT sensor data
CREATE TABLE sensor_data (
    time TIMESTAMPTZ NOT NULL,
    device_id INT NOT NULL,
    location TEXT NOT NULL,
    temperature DOUBLE PRECISION,
    humidity DOUBLE PRECISION,
    pressure DOUBLE PRECISION
);

-- Convert to hypertable, partitioned by time
SELECT create_hypertable('sensor_data', 'time', chunk_time_interval => INTERVAL '1 day');

-- Add an index for common query patterns
CREATE INDEX idx_sensor_location_time ON sensor_data (location, time DESC);

Expected behavior: Each day's data goes into a separate chunk (physical partition). Queries with time filters scan only relevant chunks instead of the entire table.

Advanced Time-Series Queries

-- Time-bucket aggregation: Average temperature by hour
SELECT
    time_bucket('1 hour', time) AS hour,
    location,
    AVG(temperature) AS avg_temp,
    MAX(temperature) AS max_temp,
    MIN(temperature) AS min_temp
FROM sensor_data
WHERE time > NOW() - INTERVAL '7 days'
GROUP BY hour, location
ORDER BY hour DESC;

-- Continuous aggregate: Pre-computed hourly stats
CREATE MATERIALIZED VIEW hourly_stats
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', time) AS hour,
    device_id,
    AVG(temperature) AS avg_temp,
    MAX(temperature) AS max_temp
FROM sensor_data
GROUP BY hour, device_id;

-- Query the continuous aggregate (much faster than raw scan)
SELECT * FROM hourly_stats
WHERE hour > NOW() - INTERVAL '30 days';

Expected performance improvement: The continuous aggregate query runs in milliseconds instead of seconds because TimescaleDB maintains pre-aggregated data automatically.

Compression

-- Enable compression on the hypertable
ALTER TABLE sensor_data SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device_id',
    timescaledb.compress_orderby = 'time DESC'
);

-- Add a compression policy: compress chunks older than 7 days
SELECT add_compression_policy('sensor_data', INTERVAL '7 days');

Expected behavior: Data older than 7 days is compressed automatically. TimescaleDB achieves 90-97% compression for time-series data using delta-delta and run-length encoding.

Prometheus

Prometheus is a monitoring system and TSDB designed for reliability, with a pull-based metrics collection model and PromQL query language.

# prometheus.yml: Configure scrape targets
scrape_configs:
  - job_name: 'node'
    static_configs:
      - targets: ['localhost:9100']  # Node exporter

  - job_name: 'app'
    static_configs:
      - targets: ['app-server:8080/metrics']

# PromQL: Query CPU usage over the last hour
avg by (instance) (
    rate(node_cpu_seconds_total{mode="user"}[5m])
)

# Alert when memory usage exceeds 90%
node_memory_MemAvailable_bytes / node_memory_MemTotal_bytes < 0.1

Expected output (PromQL query):

{instance="localhost:9100"}  0.23
{instance="app-server:9090"} 0.45

Choosing a Time-Series Database

Downsampling Strategies

Downsampling reduces storage by storing aggregated data for older time periods.

-- TimescaleDB: Downsampling with continuous aggregates
CREATE MATERIALIZED VIEW daily_metrics
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 day', time) AS day,
    device_id,
    AVG(temperature) AS avg_temp,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY temperature) AS p95_temp,
    COUNT(*) AS sample_count
FROM sensor_data
GROUP BY day, device_id;

Expected behavior: Raw data (1-second resolution) is kept for 7 days. Daily aggregates are kept forever. A query for "average temperature last month" hits the 86-row daily aggregate instead of scanning 2.6 million raw rows.

Common Time-Series Database Errors

1. Using Too Many Tags

In InfluxDB, each unique tag combination creates a new series. With 5 tags each having 100 values, you get 100^5 = 10 billion series. Keep tag cardinality under control.

2. Not Setting Retention Policies

Without retention policies, storage grows unbounded. Set appropriate retention for each data type: high-resolution for recent data, downsampled for historical.

3. Writing Too Many Points Per Batch

Writing one data point per HTTP request creates massive overhead. Batch writes (5,000-10,000 points per batch) improve throughput 10-100x.

# BAD: One point per write
for reading in sensor_readings:  # 10,000 readings
    tsdb.write(reading)           # 10,000 HTTP requests

# GOOD: Batch write
from influxdb_client import InfluxDBClient

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

# Send all points in one batch
batch = []
for reading in sensor_readings:
    point = {
        "measurement": "sensor",
        "tags": {"device_id": reading.device_id},
        "fields": {"temperature": reading.temp},
        "time": reading.timestamp
    }
    batch.append(point)

write_api.write(bucket="sensors", record=batch)

Expected behavior: One HTTP request for 10,000 data points instead of 10,000 requests. Throughput increases from a few hundred to hundreds of thousands of points per second.

4. Querying Without Time Filters

Time-series databases rely on time-based Partitioning. A SELECT * FROM sensor_data without a time range scans all chunks and is extremely slow.

5. Using String Field Values

String fields are slower to query and compress poorly. Store categorical data as tags (indexed) and numeric data as fields (efficiently compressed).

6. Ignoring Schema Design

Time-series schema design matters. In InfluxDB, put frequently-filtered columns as tags and numeric metrics as fields. In TimescaleDB, choose the right partition column and chunk interval.

7. Not Monitoring TSDB Performance

A TSDB under load can exhibit high disk I/O, slow queries, or write timeouts. Monitor query latency, write throughput, disk usage, and series cardinality.

Practice Questions

1. What is a hypertable in TimescaleDB?

A hypertable is a PostgreSQL table that is automatically partitioned by time (and optionally by a partition key). Each partition (chunk) contains data for a specific time range. Queries with time filters scan only relevant chunks.

2. How do retention policies work in InfluxDB?

Retention policies define how long data is kept and how many copies to maintain. Data older than the retention period is automatically deleted. They can also trigger continuous queries for downsampling before deletion.

3. What is the difference between tags and fields in InfluxDB?

Tags are indexed metadata used for filtering and grouping (e.g., device_id, location). Fields are unindexed numeric values (e.g., temperature, pressure). Tags support efficient lookups; fields support aggregation functions.

4. When would you choose TimescaleDB over InfluxDB?

When you need SQL compatibility, JOINs with relational data, advanced analytics (window functions), or integration with existing PostgreSQL tools. TimescaleDB is better for mixed workloads; InfluxDB is better for pure high-throughput metrics.

5. Challenge: Design a TSDB architecture for a server monitoring platform.

Your platform monitors 10,000 servers collecting 100 metrics each every 10 seconds. Design the storage, retention, and query Strategy. Answer: Use Prometheus for metrics collection and alerting. Store raw data for 7 days. Downsample to 1-minute averages (retention: 30 days) and 1-hour averages (retention: 1 year). Use Grafana for dashboards. Alert on anomaly detection queries. Use InfluxDB or TimescaleDB for long-term historical analysis. Batch writes in groups of 5,000 points. Monitor series cardinality and disk usage.

FAQ

Try It Yourself

Set up a time-series database with TimescaleDB:

1. Install PostgreSQL and the TimescaleDB extension
2. Create a hypertable for stock price data
3. Insert 100,000 stock ticks with timestamps, symbol, price, and volume
4. Query 1-minute OHLC (Open, High, Low, Close) bars using time_bucket
5. Enable compression and compare storage size before and after
6. Create a continuous aggregate for hourly stats
7. Query the continuous aggregate and compare performance to raw scan

What's Next

Built by the developers of Doda Browser, DodaZIP, and Durga Antivirus Pro.