ClickHouse vs TimescaleDB vs InfluxDB: Time Series Battle

-- ClickHouse table optimized for time series
CREATE TABLE metrics (
    timestamp DateTime64(3),
    metric_name LowCardinality(String),
    value Float64,
    tags Map(String, String),
    host LowCardinality(String)
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (timestamp, metric_name, host)
SETTINGS index_granularity = 8192;

-- Efficient aggregation queries
SELECT 
    toStartOfHour(timestamp) as hour,
    metric_name,
    avg(value) as avg_value,
    quantile(0.95)(value) as p95_value
FROM metrics 
WHERE timestamp >= now() - INTERVAL 24 HOUR
GROUP BY hour, metric_name
ORDER BY hour;

-- TimescaleDB hypertable creation
CREATE TABLE metrics (
    time TIMESTAMPTZ NOT NULL,
    metric_name TEXT NOT NULL,
    value DOUBLE PRECISION,
    tags JSONB,
    host TEXT
);

-- Convert to hypertable with automated partitioning
SELECT create_hypertable('metrics', 'time', chunk_time_interval => INTERVAL '1 day');

-- Continuous aggregates for real-time dashboards
CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS bucket,
       metric_name,
       AVG(value) as avg_value,
       MAX(value) as max_value,
       MIN(value) as min_value
FROM metrics
GROUP BY bucket, metric_name
WITH NO DATA;

// InfluxDB 2.0 with Flux query language
from(bucket: "metrics")
  |> range(start: -24h)
  |> filter(fn: (r) => r._measurement == "cpu_usage")
  |> group(columns: ["host"])
  |> aggregateWindow(every: 1h, fn: mean)
  |> yield(name: "hourly_cpu_avg")

// Line protocol for efficient ingestion
cpu_usage,host=server01,region=us-east value=45.2 1651234567000000000
memory_usage,host=server01,region=us-east value=78.5 1651234567000000000

-- Optimized table structure for time series
CREATE TABLE sensor_data (
    timestamp DateTime64(3),
    sensor_id UInt32,
    temperature Decimal(5,2),
    humidity Decimal(5,2),
    location_id UInt16
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (timestamp, sensor_id)
TTL timestamp + INTERVAL 90 DAY
SETTINGS 
    index_granularity = 8192,
    merge_max_block_size = 8192;

-- Materialized views for real-time aggregation
CREATE MATERIALIZED VIEW sensor_hourly_mv TO sensor_hourly AS
SELECT 
    toStartOfHour(timestamp) 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;

-- Advanced TimescaleDB configuration
SELECT create_hypertable(
    'sensor_data', 'timestamp',
    chunk_time_interval => INTERVAL '6 hours',
    create_default_indexes => FALSE
);

-- Custom indexing strategy
CREATE INDEX ON sensor_data (timestamp DESC, sensor_id);
CREATE INDEX ON sensor_data (sensor_id, timestamp DESC);

-- Compression policies
ALTER TABLE sensor_data SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id',
    timescaledb.compress_orderby = 'timestamp DESC'
);

-- Automated compression
SELECT add_compression_policy('sensor_data', INTERVAL '1 day');

# InfluxDB configuration optimization
[data]
  dir = "/var/lib/influxdb/data"
  max-series-per-database = 1000000
  max-values-per-tag = 100000

[coordinator]
  write-timeout = "30s"
  max-concurrent-queries = 0
  query-timeout = "0s"

[http]
  max-row-limit = 0
  max-connection-limit = 0

-- Advanced analytics with ClickHouse functions
SELECT 
    sensor_id,
    lagInFrame(avg_temp) OVER (PARTITION BY sensor_id ORDER BY hour) as prev_hour_temp,
    avg_temp - prev_hour_temp as temp_delta,
    exponentialMovingAverage(0.1)(avg_temp) OVER (PARTITION BY sensor_id ORDER BY hour) as ema_temp
FROM sensor_hourly
WHERE hour >= now() - INTERVAL 7 DAY
ORDER BY sensor_id, hour;

-- Anomaly detection with statistical functions
SELECT 
    hour,
    sensor_id,
    avg_temp,
    quantileExact(0.95)(avg_temp) OVER (PARTITION BY sensor_id) as p95_threshold,
    if(avg_temp > p95_threshold * 1.2, 'anomaly', 'normal') as status
FROM sensor_hourly;

-- Complex time series analysis
WITH temperature_stats AS (
    SELECT 
        time_bucket('1 hour', timestamp) as hour,
        sensor_id,
        AVG(temperature) as avg_temp,
        STDDEV(temperature) as temp_stddev
    FROM sensor_data
    WHERE timestamp >= NOW() - INTERVAL '7 days'
    GROUP BY hour, sensor_id
)
SELECT 
    hour,
    sensor_id,
    avg_temp,
    LAG(avg_temp) OVER (PARTITION BY sensor_id ORDER BY hour) as prev_temp,
    CASE 
        WHEN ABS(avg_temp - LAG(avg_temp) OVER (PARTITION BY sensor_id ORDER BY hour)) > temp_stddev * 2
        THEN 'anomaly'
        ELSE 'normal'
    END as anomaly_status
FROM temperature_stats;

// Advanced time series transformations
from(bucket: "sensors")
  |> range(start: -7d)
  |> filter(fn: (r) => r._measurement == "temperature")
  |> group(columns: ["sensor_id"])
  |> aggregateWindow(every: 1h, fn: mean, createEmpty: false)
  |> duplicate(column: "_value", as: "current")
  |> timeShift(duration: 1h)
  |> drop(columns: ["_time"])
  |> rename(columns: {"_value": "previous"})
  |> join(tables: {current: current, previous: previous}, on: ["sensor_id"])
  |> map(fn: (r) => ({
      r with 
      delta: r.current - r.previous,
      anomaly: if math.abs(x: r.current - r.previous) > 5.0 then "yes" else "no"
  }))

-- Monitor table sizes and compression
SELECT 
    database,
    table,
    formatReadableSize(sum(bytes_on_disk)) as size_on_disk,
    formatReadableSize(sum(data_uncompressed_bytes)) as uncompressed_size,
    round(sum(data_uncompressed_bytes) / sum(bytes_on_disk), 2) as compression_ratio
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY sum(bytes_on_disk) DESC;

-- Query performance monitoring
SELECT 
    query_duration_ms,
    read_rows,
    read_bytes,
    query
FROM system.query_log
WHERE event_time > now() - INTERVAL 1 HOUR
ORDER BY query_duration_ms DESC
LIMIT 10;

-- Chunk and compression status
SELECT 
    chunk_schema,
    chunk_name,
    pg_size_pretty(total_bytes) as size,
    pg_size_pretty(compressed_total_bytes) as compressed_size,
    compression_status
FROM timescaledb_information.chunks
WHERE hypertable_name = 'sensor_data'
ORDER BY total_bytes DESC;

-- Automated maintenance policies
SELECT add_retention_policy('sensor_data', INTERVAL '1 year');
SELECT add_compression_policy('sensor_data', INTERVAL '1 week');

# High-frequency sensor data ingestion
import clickhouse_connect

client = clickhouse_connect.get_client(host='localhost', port=8123)

# Batch insert for optimal performance
sensor_data = [
    ('2025-04-15 10:00:00', 'temp_01', 23.5, 'greenhouse_a'),
    ('2025-04-15 10:00:01', 'temp_01', 23.6, 'greenhouse_a'),
    # ... thousands of readings
]

client.insert('sensor_readings', sensor_data, 
               column_names=['timestamp', 'sensor_id', 'value', 'location'])

-- TimescaleDB for APM data
CREATE TABLE application_metrics (
    time TIMESTAMPTZ NOT NULL,
    service_name TEXT,
    endpoint TEXT,
    response_time_ms INTEGER,
    status_code INTEGER,
    user_id TEXT
);

-- Real-time alerting views
CREATE VIEW slow_endpoints AS
SELECT 
    time_bucket('5 minutes', time) as bucket,
    service_name,
    endpoint,
    AVG(response_time_ms) as avg_response_time,
    COUNT(*) as request_count
FROM application_metrics
WHERE time >= NOW() - INTERVAL '1 hour'
GROUP BY bucket, service_name, endpoint
HAVING AVG(response_time_ms) > 1000;

// InfluxDB for trading data
from(bucket: "market_data")
  |> range(start: -1d)
  |> filter(fn: (r) => r._measurement == "stock_price")
  |> filter(fn: (r) => r.symbol == "AAPL")
  |> aggregateWindow(every: 1m, fn: mean)
  |> movingAverage(n: 20)
  |> yield(name: "sma_20")

# ClickHouse Kubernetes deployment
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: clickhouse
spec:
  serviceName: clickhouse
  replicas: 3
  template:
    spec:
      containers:
      - name: clickhouse
        image: clickhouse/clickhouse-server:latest
        resources:
          requests:
            memory: "4Gi"
            cpu: "2"
          limits:
            memory: "8Gi"
            cpu: "4"
        volumeMounts:
        - name: data
          mountPath: /var/lib/clickhouse