Time-Series Databases - Chronological Data Storage

calendar_today November 30, 2025 schedule 17 min read

Learning Objectives: By the end of this lesson, you will be able to:

Understand time-series data characteristics and storage requirements

Design efficient schemas for time-series workloads

Implement data collection, querying, and aggregation patterns

Choose between popular time-series databases (InfluxDB, TimescaleDB, Prometheus)

Apply best practices for retention, downsampling, and performance optimization

1. Welcome and Introduction (5 minutes)

Icebreaker Activity

Consider your fitness tracker. Every few seconds, it records your heart rate, steps, sleep patterns, and location. Over a year, that’s millions of data points—all timestamped, all sequential. Now imagine a factory with 10,000 sensors, each recording data every second. That’s 864 million data points per day! Traditional databases would buckle under this load. How do you store, query, and analyze this flood of chronological data efficiently?

Welcome to the world of time-series databases—purpose-built for the relentless march of time-stamped data.

Lesson Overview

Duration: 90 minutes
Prerequisites: Basic database knowledge, understanding of timestamps and time zones
Materials Needed: Docker, InfluxDB or TimescaleDB installed, command line access

2. Learning Objectives and Session Outline (5 minutes)

What We’ll Cover Today:

Time-Series Fundamentals - Understanding time-series data characteristics
Storage Architecture - How time-series databases optimize for temporal data
Data Modeling - Designing schemas for time-series workloads
Query Patterns - Time-based queries, aggregations, and analysis
Popular Systems - InfluxDB, TimescaleDB, Prometheus comparison
Production Considerations - Retention, downsampling, and scaling

Success Criteria:

By the end of this session, you should be able to design a time-series data model, write efficient temporal queries, and choose the appropriate time-series database for specific use cases.

3. Foundation Knowledge (15-20 minutes)

Core Concepts

What is Time-Series Data?

Time-series data is a sequence of data points collected or recorded at successive points in time, usually at uniform intervals. Each data point consists of a timestamp and one or more values.

Key Characteristics:

Time-Ordered: Data arrives in chronological order
Append-Only: New data is primarily added, rarely updated
High Volume: Often millions or billions of data points
Recent Relevance: Recent data queried more frequently than historical
Aggregation Focus: Analysis often involves aggregations over time windows

Real-World Analogy: Think of time-series data like a continuous video recording. Each frame (data point) has a timestamp, you mostly add new frames, you rarely edit old frames, and when reviewing, you often fast-forward through old footage but watch recent events in detail.

Why This Matters: IoT, monitoring, financial markets, and countless other domains generate time-series data. Traditional databases struggle with the write patterns, query requirements, and storage efficiency needed for time-series workloads.

Why Traditional Databases Struggle

Relational Database Challenges:

Write Amplification: B-tree indexes require updates on every insert
Storage Inefficiency: Row-oriented storage doesn’t compress time-series well
Query Performance: Complex JOINs and indexing not optimized for time ranges
Scalability: Vertical scaling limits for high-throughput writes

Time-Series Database Optimizations:

Append-Optimized Storage: Write paths optimized for sequential inserts
Columnar Compression: Same-type data compresses extremely well
Time-Based Partitioning: Data organized by time for efficient range queries
Automatic Downsampling: Built-in aggregation for long-term storage
Retention Policies: Automatic deletion of old data

Key Terminology

Metric: A measured value over time (e.g., CPU usage, temperature)
Tag/Label: Metadata that identifies the source (e.g., host, sensor_id)
Field: The actual measured values (e.g., temperature=22.5)
Timestamp: When the measurement was taken
Series: A unique combination of metric name and tags
Bucket/Chunk: A partition of data covering a time range
Downsampling: Reducing data resolution by aggregating over time
Retention Policy: Rules for how long data is kept
Continuous Query: Background query that runs periodically
Cardinality: Number of unique series (tag combinations)

4. Detailed Explanation and Examples (25-30 minutes)

Time-Series Data Model

The Anatomy of Time-Series Data

┌─────────────────────────────────────────────────────────────┐
│  Measurement: cpu_usage                                      │
├─────────────────────────────────────────────────────────────┤
│  Tags (indexed):        │  Fields (not indexed):            │
│  - host: server01       │  - value: 45.2                    │
│  - region: us-east      │  - user_cpu: 30.1                 │
│  - environment: prod    │  - system_cpu: 15.1               │
├─────────────────────────────────────────────────────────────┤
│  Timestamp: 2024-01-15T10:30:00Z                            │
└─────────────────────────────────────────────────────────────┘

Design Principles:

Tags for filtering: Use tags for dimensions you’ll filter on
Fields for values: Use fields for the actual measurements
Avoid high cardinality tags: Tags like user_id with millions of values hurt performance
Consistent schemas: Keep measurements consistent for efficient queries

Popular Time-Series Databases

InfluxDB

Type: Purpose-built time-series database
Query Language: Flux (modern), InfluxQL (SQL-like, legacy)
Storage Engine: TSM (Time-Structured Merge Tree)
Best For: Metrics, IoT, real-time analytics
Unique Features: Built-in dashboarding, alerting, tasks

TimescaleDB

Type: PostgreSQL extension
Query Language: Standard SQL
Storage Engine: Hypertables (automatic partitioning)
Best For: When you need SQL compatibility, complex queries
Unique Features: Full PostgreSQL compatibility, JOINs, foreign keys

Prometheus

Type: Monitoring-focused time-series database
Query Language: PromQL
Storage Engine: Custom, chunk-based
Best For: Infrastructure monitoring, Kubernetes, alerting
Unique Features: Pull-based collection, service discovery, AlertManager

QuestDB

Type: High-performance time-series database
Query Language: SQL
Storage Engine: Column-oriented, memory-mapped
Best For: Financial data, high-throughput scenarios
Unique Features: Extremely fast ingestion, SQL support

Common Misconceptions

Misconception: “Any database can handle time-series data with proper indexing” Reality: General-purpose databases lack the compression, retention policies, and query optimizations that time-series databases provide.
Misconception: “Time-series databases are only for metrics” Reality: They’re excellent for any temporal data: financial trades, user events, sensor readings, logs, and more.
Misconception: “High cardinality is fine with proper indexing” Reality: High cardinality (millions of unique series) degrades performance significantly in most time-series databases.

5. Live Coding Demonstration (20-25 minutes)

Example 1: InfluxDB with Flux

// Create a bucket (database) with retention policy
// Via CLI: influx bucket create --name sensors --retention 30d

// Write data using line protocol
// Format: measurement,tag1=value1,tag2=value2 field1=value1,field2=value2 timestamp

// Example line protocol:
// temperature,sensor_id=sensor001,location=warehouse_a value=22.5 1705312200000000000
// temperature,sensor_id=sensor001,location=warehouse_a value=22.7 1705312260000000000
// temperature,sensor_id=sensor002,location=warehouse_b value=18.3 1705312200000000000

// Basic query - get last hour of data
from(bucket: "sensors")
  |> range(start: -1h)
  |> filter(fn: (r) => r._measurement == "temperature")

// Filter by tags
from(bucket: "sensors")
  |> range(start: -24h)
  |> filter(fn: (r) => r._measurement == "temperature")
  |> filter(fn: (r) => r.location == "warehouse_a")
  |> filter(fn: (r) => r._field == "value")

// Aggregation - average temperature per hour
from(bucket: "sensors")
  |> range(start: -7d)
  |> filter(fn: (r) => r._measurement == "temperature")
  |> filter(fn: (r) => r._field == "value")
  |> aggregateWindow(every: 1h, fn: mean, createEmpty: false)
  |> yield(name: "hourly_avg")

// Multiple aggregations
from(bucket: "sensors")
  |> range(start: -24h)
  |> filter(fn: (r) => r._measurement == "temperature")
  |> filter(fn: (r) => r._field == "value")
  |> aggregateWindow(every: 15m, fn: mean)
  |> group(columns: ["location"])
  |> reduce(
      fn: (r, accumulator) => ({
        count: accumulator.count + 1,
        total: accumulator.total + r._value,
        max: if r._value > accumulator.max then r._value else accumulator.max,
        min: if r._value < accumulator.min then r._value else accumulator.min
      }),
      identity: {count: 0, total: 0.0, max: -999.0, min: 999.0}
    )

// Downsampling task (continuous query)
option task = {name: "downsample_temperature", every: 1h}

from(bucket: "sensors")
  |> range(start: -1h)
  |> filter(fn: (r) => r._measurement == "temperature")
  |> aggregateWindow(every: 5m, fn: mean)
  |> to(bucket: "sensors_downsampled")

// Alerting - detect high temperature
from(bucket: "sensors")
  |> range(start: -5m)
  |> filter(fn: (r) => r._measurement == "temperature")
  |> filter(fn: (r) => r._field == "value")
  |> mean()
  |> filter(fn: (r) => r._value > 30.0)
  // Pipe to alert endpoint

Example 2: TimescaleDB with SQL

-- Enable TimescaleDB extension
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Create a regular table
CREATE TABLE sensor_readings (
    time        TIMESTAMPTZ NOT NULL,
    sensor_id   TEXT NOT NULL,
    location    TEXT NOT NULL,
    temperature DOUBLE PRECISION,
    humidity    DOUBLE PRECISION,
    pressure    DOUBLE PRECISION
);

-- Convert to hypertable (TimescaleDB magic)
SELECT create_hypertable('sensor_readings', 'time');

-- Add compression policy (after 7 days)
ALTER TABLE sensor_readings SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id'
);
SELECT add_compression_policy('sensor_readings', INTERVAL '7 days');

-- Add retention policy (delete after 90 days)
SELECT add_retention_policy('sensor_readings', INTERVAL '90 days');

-- Insert data
INSERT INTO sensor_readings (time, sensor_id, location, temperature, humidity, pressure)
VALUES
    (NOW(), 'sensor001', 'warehouse_a', 22.5, 45.0, 1013.25),
    (NOW() - INTERVAL '1 minute', 'sensor001', 'warehouse_a', 22.3, 44.8, 1013.20),
    (NOW() - INTERVAL '2 minutes', 'sensor001', 'warehouse_a', 22.4, 45.1, 1013.22);

-- Basic query with time range
SELECT * FROM sensor_readings
WHERE time > NOW() - INTERVAL '1 hour'
  AND sensor_id = 'sensor001'
ORDER BY time DESC;

-- Time bucket aggregation (TimescaleDB function)
SELECT
    time_bucket('15 minutes', time) AS bucket,
    sensor_id,
    AVG(temperature) AS avg_temp,
    MAX(temperature) AS max_temp,
    MIN(temperature) AS min_temp,
    COUNT(*) AS readings
FROM sensor_readings
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY bucket, sensor_id
ORDER BY bucket DESC, sensor_id;

-- Last known value per sensor
SELECT DISTINCT ON (sensor_id)
    sensor_id,
    time,
    temperature,
    humidity
FROM sensor_readings
ORDER BY sensor_id, time DESC;

-- Using TimescaleDB's last() aggregate
SELECT
    sensor_id,
    last(temperature, time) AS latest_temp,
    last(humidity, time) AS latest_humidity,
    max(time) AS last_reading
FROM sensor_readings
WHERE time > NOW() - INTERVAL '1 hour'
GROUP BY sensor_id;

-- Gap filling for missing data points
SELECT
    time_bucket_gapfill('5 minutes', time) AS bucket,
    sensor_id,
    locf(AVG(temperature)) AS temperature -- Last observation carried forward
FROM sensor_readings
WHERE time > NOW() - INTERVAL '1 hour'
GROUP BY bucket, sensor_id
ORDER BY bucket;

-- Continuous aggregate (materialized view that auto-updates)
CREATE MATERIALIZED VIEW sensor_hourly
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', time) AS hour,
    sensor_id,
    location,
    AVG(temperature) AS avg_temp,
    MAX(temperature) AS max_temp,
    MIN(temperature) AS min_temp,
    AVG(humidity) AS avg_humidity,
    COUNT(*) AS reading_count
FROM sensor_readings
GROUP BY hour, sensor_id, location;

-- Add refresh policy for continuous aggregate
SELECT add_continuous_aggregate_policy('sensor_hourly',
    start_offset => INTERVAL '3 hours',
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');

-- Query the continuous aggregate
SELECT * FROM sensor_hourly
WHERE hour > NOW() - INTERVAL '7 days'
ORDER BY hour DESC;

-- Advanced: JOIN with metadata (benefit of SQL!)
CREATE TABLE sensors (
    sensor_id TEXT PRIMARY KEY,
    name TEXT,
    installed_date DATE,
    maintenance_due DATE,
    status TEXT
);

SELECT 
    sr.time,
    sr.sensor_id,
    s.name,
    sr.temperature,
    s.status
FROM sensor_readings sr
JOIN sensors s ON sr.sensor_id = s.sensor_id
WHERE sr.time > NOW() - INTERVAL '1 hour'
  AND s.status = 'active';

Example 3: Prometheus PromQL

# Basic instant query - current value
temperature_celsius{location="warehouse_a"}

# Range query - last 5 minutes
temperature_celsius{location="warehouse_a"}[5m]

# Rate calculation (per-second rate of increase)
rate(http_requests_total[5m])

# Increase over time window
increase(http_requests_total[1h])

# Aggregation by label
sum by (location) (temperature_celsius)

# Average temperature across all sensors
avg(temperature_celsius)

# Maximum temperature per location
max by (location) (temperature_celsius)

# Count of active sensors
count(up{job="sensors"})

# Percentiles (histogram)
histogram_quantile(0.95, rate(http_request_duration_seconds_bucket[5m]))

# Alerting rule example
# In prometheus rules file:
groups:
- name: temperature_alerts
  rules:
  - alert: HighTemperature
    expr: temperature_celsius > 35
    for: 5m
    labels:
      severity: warning
    annotations:
      summary: "High temperature detected"
      description: "Sensor {{ $labels.sensor_id }} has temperature {{ $value }}°C"

# Recording rule (pre-compute expensive queries)
groups:
- name: aggregations  
  rules:
  - record: sensor:temperature:avg5m
    expr: avg_over_time(temperature_celsius[5m])
  
  - record: location:temperature:max
    expr: max by (location) (temperature_celsius)

# Complex query examples
# Request rate by status code
sum by (status_code) (rate(http_requests_total[5m]))

# Error rate percentage
sum(rate(http_requests_total{status_code=~"5.."}[5m])) / sum(rate(http_requests_total[5m])) * 100

# Latency percentiles by endpoint
histogram_quantile(0.99, 
  sum by (endpoint, le) (
    rate(http_request_duration_seconds_bucket[5m])
  )
)

# Predict disk full (linear regression)
predict_linear(node_filesystem_avail_bytes[1h], 4*3600) < 0

# Day-over-day comparison
temperature_celsius - temperature_celsius offset 1d

Example 4: Data Modeling Patterns

-- Pattern 1: Simple Metrics Table
CREATE TABLE metrics (
    time        TIMESTAMPTZ NOT NULL,
    metric_name TEXT NOT NULL,
    tags        JSONB,
    value       DOUBLE PRECISION
);
SELECT create_hypertable('metrics', 'time');

-- Insert
INSERT INTO metrics (time, metric_name, tags, value)
VALUES (NOW(), 'cpu_usage', '{"host": "server01", "region": "us-east"}', 45.2);

-- Query with JSON
SELECT * FROM metrics
WHERE metric_name = 'cpu_usage'
  AND tags->>'host' = 'server01'
  AND time > NOW() - INTERVAL '1 hour';

-- Pattern 2: Narrow Table (one metric per row)
CREATE TABLE readings_narrow (
    time        TIMESTAMPTZ NOT NULL,
    device_id   TEXT NOT NULL,
    metric      TEXT NOT NULL,
    value       DOUBLE PRECISION
);
SELECT create_hypertable('readings_narrow', 'time');

-- Insert multiple metrics
INSERT INTO readings_narrow VALUES
    (NOW(), 'device01', 'temperature', 22.5),
    (NOW(), 'device01', 'humidity', 45.0),
    (NOW(), 'device01', 'pressure', 1013.25);

-- Pivot query
SELECT
    time_bucket('1 hour', time) AS bucket,
    device_id,
    MAX(value) FILTER (WHERE metric = 'temperature') AS temperature,
    MAX(value) FILTER (WHERE metric = 'humidity') AS humidity,
    MAX(value) FILTER (WHERE metric = 'pressure') AS pressure
FROM readings_narrow
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY bucket, device_id;

-- Pattern 3: Wide Table (all metrics in columns)
CREATE TABLE readings_wide (
    time        TIMESTAMPTZ NOT NULL,
    device_id   TEXT NOT NULL,
    temperature DOUBLE PRECISION,
    humidity    DOUBLE PRECISION,
    pressure    DOUBLE PRECISION,
    battery     DOUBLE PRECISION
);
SELECT create_hypertable('readings_wide', 'time');

-- Simpler queries when all metrics collected together
SELECT
    time_bucket('1 hour', time) AS bucket,
    device_id,
    AVG(temperature) AS avg_temp,
    AVG(humidity) AS avg_humidity
FROM readings_wide
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY bucket, device_id;

-- Pattern 4: Event/Log Table
CREATE TABLE events (
    time        TIMESTAMPTZ NOT NULL,
    event_type  TEXT NOT NULL,
    source      TEXT NOT NULL,
    severity    TEXT,
    message     TEXT,
    metadata    JSONB
);
SELECT create_hypertable('events', 'time');

-- Insert event
INSERT INTO events VALUES (
    NOW(),
    'sensor_offline',
    'sensor001',
    'warning',
    'Sensor stopped responding',
    '{"last_reading": "2024-01-15T10:30:00Z", "retry_count": 3}'
);

-- Query recent errors
SELECT * FROM events
WHERE severity IN ('error', 'critical')
  AND time > NOW() - INTERVAL '1 hour'
ORDER BY time DESC;

6. Hands-On Practice (30-40 minutes)

Exercise 1: IoT Sensor System Design (15 minutes)

Objective: Design a schema for a smart building monitoring system

Requirements:

Track temperature, humidity, CO2 levels, and occupancy
100 sensors across 5 floors
Readings every 30 seconds
Need hourly aggregates for dashboards
Keep detailed data for 30 days, aggregates for 1 year

Solution:

-- TimescaleDB Solution
CREATE TABLE building_sensors (
    time        TIMESTAMPTZ NOT NULL,
    sensor_id   TEXT NOT NULL,
    floor       INTEGER NOT NULL,
    room        TEXT NOT NULL,
    temperature DOUBLE PRECISION,
    humidity    DOUBLE PRECISION,
    co2_ppm     INTEGER,
    occupancy   INTEGER
);

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

-- Create indexes for common queries
CREATE INDEX idx_sensor_floor ON building_sensors (floor, time DESC);
CREATE INDEX idx_sensor_id ON building_sensors (sensor_id, time DESC);

-- Continuous aggregate for hourly data
CREATE MATERIALIZED VIEW building_hourly
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', time) AS hour,
    floor,
    room,
    AVG(temperature) AS avg_temp,
    AVG(humidity) AS avg_humidity,
    MAX(co2_ppm) AS max_co2,
    AVG(occupancy) AS avg_occupancy,
    COUNT(*) AS reading_count
FROM building_sensors
GROUP BY hour, floor, room;

-- Refresh policy
SELECT add_continuous_aggregate_policy('building_hourly',
    start_offset => INTERVAL '2 hours',
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');

-- Retention policy for raw data (30 days)
SELECT add_retention_policy('building_sensors', INTERVAL '30 days');

-- Retention policy for aggregates (1 year)
SELECT add_retention_policy('building_hourly', INTERVAL '365 days');

-- Compression after 7 days
ALTER TABLE building_sensors SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id,floor'
);
SELECT add_compression_policy('building_sensors', INTERVAL '7 days');

-- Example queries

-- Current status of all sensors
SELECT DISTINCT ON (sensor_id)
    sensor_id,
    floor,
    room,
    temperature,
    humidity,
    co2_ppm,
    occupancy,
    time AS last_reading
FROM building_sensors
ORDER BY sensor_id, time DESC;

-- Floor averages for last hour
SELECT
    floor,
    AVG(temperature) AS avg_temp,
    MAX(co2_ppm) AS max_co2,
    SUM(occupancy) AS total_occupancy
FROM building_sensors
WHERE time > NOW() - INTERVAL '1 hour'
GROUP BY floor
ORDER BY floor;

-- Alert: High CO2 levels
SELECT
    time,
    sensor_id,
    floor,
    room,
    co2_ppm
FROM building_sensors
WHERE co2_ppm > 1000
  AND time > NOW() - INTERVAL '15 minutes'
ORDER BY co2_ppm DESC;

Exercise 2: Application Metrics Collection (15 minutes)

Objective: Design metrics collection for a web application

Challenge:

Track request latency, error rates, and throughput
Support filtering by endpoint, method, and status code
Enable percentile calculations (p50, p95, p99)

Solution:

// InfluxDB Solution

// Line protocol format for metrics:
// http_requests,endpoint=/api/users,method=GET,status=200 latency=0.045,count=1i 1705312200000000000

// Write histogram buckets for percentiles
// http_request_latency,endpoint=/api/users,le=0.01 count=100i
// http_request_latency,endpoint=/api/users,le=0.05 count=450i
// http_request_latency,endpoint=/api/users,le=0.1 count=890i
// http_request_latency,endpoint=/api/users,le=0.5 count=980i
// http_request_latency,endpoint=/api/users,le=1.0 count=995i
// http_request_latency,endpoint=/api/users,le=+Inf count=1000i

// Query: Request rate by endpoint
from(bucket: "application_metrics")
  |> range(start: -1h)
  |> filter(fn: (r) => r._measurement == "http_requests")
  |> filter(fn: (r) => r._field == "count")
  |> aggregateWindow(every: 1m, fn: sum)
  |> group(columns: ["endpoint"])

// Query: Error rate percentage
errors = from(bucket: "application_metrics")
  |> range(start: -5m)
  |> filter(fn: (r) => r._measurement == "http_requests")
  |> filter(fn: (r) => r.status =~ /^5/)
  |> filter(fn: (r) => r._field == "count")
  |> sum()

total = from(bucket: "application_metrics")
  |> range(start: -5m)
  |> filter(fn: (r) => r._measurement == "http_requests")
  |> filter(fn: (r) => r._field == "count")
  |> sum()

join(tables: {errors: errors, total: total}, on: ["_start", "_stop"])
  |> map(fn: (r) => ({
      _time: r._time,
      error_rate: float(v: r._value_errors) / float(v: r._value_total) * 100.0
  }))

// Query: Average latency by endpoint
from(bucket: "application_metrics")
  |> range(start: -1h)
  |> filter(fn: (r) => r._measurement == "http_requests")
  |> filter(fn: (r) => r._field == "latency")
  |> group(columns: ["endpoint"])
  |> aggregateWindow(every: 5m, fn: mean)

// Alerting task for high error rate
option task = {name: "high_error_rate_alert", every: 1m}

from(bucket: "application_metrics")
  |> range(start: -5m)
  |> filter(fn: (r) => r._measurement == "http_requests")
  |> filter(fn: (r) => r.status =~ /^5/)
  |> count()
  |> filter(fn: (r) => r._value > 100)
  |> to(bucket: "alerts")

Exercise 3: Financial Market Data (10 minutes)

Objective: Store and analyze stock price data

Requirements:

Store OHLCV (Open, High, Low, Close, Volume) data
Support multiple symbols and exchanges
Calculate moving averages and volatility

Solution:

-- TimescaleDB Solution for Stock Data
CREATE TABLE stock_prices (
    time    TIMESTAMPTZ NOT NULL,
    symbol  TEXT NOT NULL,
    exchange TEXT NOT NULL,
    open    DECIMAL(12, 4),
    high    DECIMAL(12, 4),
    low     DECIMAL(12, 4),
    close   DECIMAL(12, 4),
    volume  BIGINT
);

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

-- Index for symbol lookups
CREATE INDEX idx_stock_symbol ON stock_prices (symbol, time DESC);

-- Insert sample data
INSERT INTO stock_prices VALUES
    ('2024-01-15 09:30:00', 'AAPL', 'NASDAQ', 185.50, 186.20, 185.30, 185.90, 1500000),
    ('2024-01-15 09:31:00', 'AAPL', 'NASDAQ', 185.90, 186.50, 185.80, 186.30, 1200000),
    ('2024-01-15 09:32:00', 'AAPL', 'NASDAQ', 186.30, 186.80, 186.10, 186.70, 1800000);

-- OHLCV aggregation to 5-minute bars
SELECT
    time_bucket('5 minutes', time) AS bucket,
    symbol,
    first(open, time) AS open,
    max(high) AS high,
    min(low) AS low,
    last(close, time) AS close,
    sum(volume) AS volume
FROM stock_prices
WHERE symbol = 'AAPL'
  AND time > NOW() - INTERVAL '1 day'
GROUP BY bucket, symbol
ORDER BY bucket;

-- Moving average with window function
SELECT
    time,
    symbol,
    close,
    AVG(close) OVER (
        PARTITION BY symbol
        ORDER BY time
        ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
    ) AS ma_20,
    AVG(close) OVER (
        PARTITION BY symbol
        ORDER BY time
        ROWS BETWEEN 49 PRECEDING AND CURRENT ROW
    ) AS ma_50
FROM stock_prices
WHERE symbol = 'AAPL'
ORDER BY time DESC
LIMIT 100;

-- Daily volatility (standard deviation of returns)
WITH returns AS (
    SELECT
        time,
        symbol,
        close,
        (close - LAG(close) OVER (PARTITION BY symbol ORDER BY time)) / 
            LAG(close) OVER (PARTITION BY symbol ORDER BY time) AS return
    FROM stock_prices
)
SELECT
    time_bucket('1 day', time) AS day,
    symbol,
    STDDEV(return) * SQRT(252) AS annualized_volatility
FROM returns
WHERE return IS NOT NULL
GROUP BY day, symbol
ORDER BY day DESC;

-- VWAP (Volume Weighted Average Price)
SELECT
    time_bucket('1 day', time) AS day,
    symbol,
    SUM(close * volume) / SUM(volume) AS vwap
FROM stock_prices
GROUP BY day, symbol
ORDER BY day DESC;

7. Common Pitfalls and Troubleshooting (10 minutes)

Frequent Mistakes

Problem: High cardinality tags causing memory issues Cause: Using user IDs, session IDs, or other high-cardinality values as tags Solution: Keep tag cardinality low; use fields for high-cardinality data
Problem: Queries timing out on large time ranges Cause: Scanning too much data without proper filtering Solution: Add time bounds to all queries; use continuous aggregates for long-term analysis
Problem: Write performance degradation over time Cause: No compression or retention policies configured Solution: Enable compression, configure retention, and monitor storage
Problem: Incorrect timestamps causing data ordering issues Cause: Using local time instead of UTC, or clock drift Solution: Always use UTC, synchronize clocks with NTP

Debugging Tips

Check cardinality: Monitor the number of unique series
Use EXPLAIN: Analyze query execution plans
Monitor chunk stats: Check compression ratios and chunk sizes
Watch memory usage: High cardinality consumes more memory
Test at scale: Small test datasets hide performance issues

8. Best Practices and Professional Tips (5 minutes)

Industry Standards

Always use UTC: Store all timestamps in UTC, convert in presentation layer
Design for queries: Know your access patterns before designing schemas
Plan retention: Don’t keep high-resolution data forever
Use continuous aggregates: Pre-compute common aggregations
Monitor cardinality: Keep tags limited to meaningful dimensions

Database Selection Guide

Feature	InfluxDB	TimescaleDB	Prometheus	QuestDB
Query Language	Flux	SQL	PromQL	SQL
SQL Support	Limited	Full	No	Yes
JOINs	No	Yes	No	Yes
Clustering	Enterprise	Built-in	Federation	Yes
Best For	Metrics/IoT	SQL users	Monitoring	Finance
Learning Curve	Medium	Low (if SQL)	Medium	Low

Next Steps in Your Learning Journey

Advanced queries: Window functions, continuous aggregates
High availability: Clustering and replication setup
Integration: Grafana dashboards, alerting systems
Optimization: Compression tuning, chunk management

9. Summary and Review (5 minutes)

Key Takeaways

Time-series databases are optimized for append-heavy, time-ordered data with specific compression and query patterns
Schema design should minimize cardinality and organize data for expected queries
Retention and downsampling are essential for managing long-term storage
Choose your database based on query language preference, SQL needs, and specific workload characteristics

Quick Review Questions

What makes time-series data different from regular data? Answer: Time-ordered, append-only nature; need for time-range queries and aggregations; high write volume
Why is high cardinality a problem in time-series databases? Answer: Each unique combination of tags creates a new series, consuming memory for index maintenance
When would you choose TimescaleDB over InfluxDB? Answer: When you need full SQL compatibility, JOINs with other tables, or integration with existing PostgreSQL infrastructure

For Instructors

Teaching Tips

Use real-time demos with live data streaming
Compare query performance between time-series DB and traditional RDBMS
Show Grafana dashboards for visualization
Demonstrate the impact of cardinality on performance

Assessment Ideas

Schema design for various IoT scenarios
Query writing challenges with time-based aggregations
Performance optimization exercises
Database selection justification

Adaptation Notes

For beginners: Focus on one database (TimescaleDB if SQL background)
For advanced students: Cover clustering, high availability, and custom retention strategies
Online delivery: Provide Docker Compose files with pre-configured databases
Time constraints: Core concepts can be covered in 45 minutes; hands-on requires additional time