using-timeseries-databases

Time-Series Databases

Implement efficient storage and querying for time-stamped data (metrics, IoT sensors, financial ticks, logs).

Database Selection

Choose based on primary use case:

TimescaleDB - PostgreSQL extension

• Use when: Already on PostgreSQL, need SQL + JOINs, hybrid workloads
• Query: Standard SQL
• Scale: 100K-1M inserts/sec

InfluxDB - Purpose-built TSDB

• Use when: DevOps metrics, Prometheus integration, Telegraf ecosystem
• Query: InfluxQL or Flux
• Scale: 500K-1M points/sec

ClickHouse - Columnar analytics

• Use when: Fastest aggregations needed, analytics dashboards, log analysis
• Query: SQL
• Scale: 1M-10M inserts/sec, 100M-1B rows/sec queries

QuestDB - High-throughput IoT

• Use when: Highest write performance needed, financial tick data
• Query: SQL + Line Protocol
• Scale: 4M+ inserts/sec

Core Patterns

1. Hypertables (TimescaleDB)

Automatic time-based partitioning:

CREATE TABLE sensor_data (
  time        TIMESTAMPTZ NOT NULL,
  sensor_id   INTEGER NOT NULL,
  temperature DOUBLE PRECISION,
  humidity    DOUBLE PRECISION
);

SELECT create_hypertable('sensor_data', 'time');

Benefits:

• Efficient data expiration (drop old chunks)
• Parallel query execution
• Compression on older chunks (10-20x savings)

2. Continuous Aggregates

Pre-computed rollups for fast dashboard queries:

-- TimescaleDB: hourly rollup
CREATE MATERIALIZED VIEW sensor_data_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS hour,
       sensor_id,
       AVG(temperature) AS avg_temp,
       MAX(temperature) AS max_temp,
       MIN(temperature) AS min_temp
FROM sensor_data
GROUP BY hour, sensor_id;

-- Auto-refresh policy
SELECT add_continuous_aggregate_policy('sensor_data_hourly',
  start_offset => INTERVAL '3 hours',
  end_offset => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour');

Query strategy:

• Short range (last hour): Raw data
• Medium range (last day): 1-minute rollups
• Long range (last month): 1-hour rollups
• Very long (last year): Daily rollups

3. Retention Policies

Automatic data expiration:

-- TimescaleDB: delete data older than 90 days
SELECT add_retention_policy('sensor_data', INTERVAL '90 days');

Common patterns:

• Raw data: 7-90 days
• Hourly rollups: 1-2 years
• Daily rollups: Infinite retention

4. Downsampling for Visualization

Use LTTB (Largest-Triangle-Three-Buckets) algorithm to reduce points for charts.