Add continuous aggregate profiles for standardized time-series metrics and dashboards

[jonatas]

Hey Timescale community! 👋

A few days ago, I was discussing with @intermittentnrg about the actual continuous aggregates macro, and it's still not trustable if we handle data directly from the model.

I'd like to propose enhancing our continuous aggregates API to support metric profiles - predefined configurations that map common time-series patterns to both aggregation logic and visualization metadata. This would make it easier to set up standardized metrics across different hypertables while maintaining consistent visualization options.

Proposed API

Timescaledb.continuous_aggregate_profile(:metrics) do |profile|
  # Define standard metric types that can be applied to any numeric column
  profile.define_metric :gauge do |m|
    m.aggregations = {
      avg: 'AVG(%{column})',
      min: 'MIN(%{column})',
      max: 'MAX(%{column})',
      p95: 'PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY %{column})',
      stddev: 'STDDEV(%{column})'
    }
    
    # Define standard timeframes for this metric type
    m.timeframes = {
      minute: { interval: '1 minute', retention: '1 day' },
      hour: { interval: '1 hour', retention: '1 week' },
      day: { interval: '1 day', retention: '1 year' }
    }
    
    # Define visualization metadata
    m.visualizations = {
      default_view: :line_chart,
      suggested_views: [:area_chart, :heatmap],
      default_aggregation: :avg,
      tooltip_aggregations: [:min, :max, :p95]
    }
  end
  
  profile.define_metric :counter do |m|
    m.aggregations = {
      sum: 'SUM(%{column})',
      rate: 'SUM(%{column})::float / EXTRACT(epoch FROM %{bucket_width})',
      count: 'COUNT(%{column})'
    }
    
    m.timeframes = {
      minute: { interval: '1 minute', retention: '1 day' },
      hour: { interval: '1 hour', retention: '1 week' },
      day: { interval: '1 day', retention: '1 year' }
    }
    
    m.visualizations = {
      default_view: :bar_chart,
      suggested_views: [:line_chart],
      default_aggregation: :rate,
      tooltip_aggregations: [:sum, :count]
    }
  end
end

# Applying the profile to a hypertable
Timescaledb.apply_metric_profile(:metrics) do |metrics|
  metrics.hypertable = 'sensor_readings'
  metrics.time_column = 'timestamp'
  
  # Map columns to metric types
  metrics.map_column 'temperature', to: :gauge, tags: ['sensor_id', 'location']
  metrics.map_column 'energy_consumption', to: :counter, tags: ['sensor_id']
  
  # Optional: Override default timeframes
  metrics.override_timeframes(
    minute: { retention: '2 days' }
  )
  
  # Optional: Custom refresh policies
  metrics.refresh_policy = {
    start_offset: '1 day',
    end_offset: '1 hour',
    schedule_interval: '1 hour'
  }
end

This would generate continuous aggregates with standardized naming and structure:

-- For gauge metrics (temperature)
CREATE MATERIALIZED VIEW metrics_temperature_minute
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 minute', timestamp) as bucket,
  sensor_id,
  location,
  AVG(temperature) as avg,
  MIN(temperature) as min,
  MAX(temperature) as max,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY temperature) as p95,
  STDDEV(temperature) as stddev
FROM sensor_readings
GROUP BY bucket, sensor_id, location;

-- For counter metrics (energy_consumption)
CREATE MATERIALIZED VIEW metrics_energy_consumption_hour
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', timestamp) as bucket,
  sensor_id,
  SUM(energy_consumption) as sum,
  SUM(energy_consumption)::float / EXTRACT(epoch FROM INTERVAL '1 hour') as rate,
  COUNT(energy_consumption) as count
FROM sensor_readings
GROUP BY bucket, sensor_id;

Benefits

1. Standardized Metrics: Common patterns for different types of measurements
2. Dashboard Integration: Built-in visualization metadata makes it easier to generate consistent dashboards
3. Flexible Configuration: Easy to customize while maintaining consistent structure
4. Best Practices: Enforces proven patterns for different metric types
5. Rollup: Automatic rollup using hierarchical

Implementation Considerations

• Need to handle updates to profile definitions
• Should support custom metric types beyond gauge/counter
• Consider adding more specialized profiles (e.g., :events, :logs)
• Need to validate tag combinations for efficiency
• Implement rollup as part of it

Open Questions

1. Should we support composite metrics that combine multiple columns?
2. How should we handle metric dependencies (e.g., derived metrics)?
3. What additional visualization metadata would be useful?
4. Should we support automatic dashboard generation based on profiles?

Next Steps

1. Collect feedback on the metric types and their configurations
2. Define the full schema for visualization metadata
3. Create a proof-of-concept implementation
4. Document best practices for profile creation

Looking forward to your thoughts on this approach! Having standardized metric profiles could really help teams set up consistent monitoring and visualization patterns across their TimescaleDB deployments.