TimescaleDB Cheat Sheet

CREATE TABLE metrics (    time        TIMESTAMPTZ NOT NULL,    device_id   TEXT NOT NULL,    cpu_pct     DOUBLE PRECISION,    mem_pct     DOUBLE PRECISION);SELECT create_hypertable('metrics', by_range('time'));-- Optional: space-partition by device as wellSELECT add_dimension('metrics', by_hash('device_id', 4));CREATE INDEX ON metrics (device_id, time DESC);

CREATE MATERIALIZED VIEW metrics_hourlyWITH (timescaledb.continuous) ASSELECT    time_bucket('1 hour', time) AS bucket,    device_id,    avg(cpu_pct) AS avg_cpu,    max(cpu_pct) AS max_cpuFROM metricsGROUP BY bucket, device_id;-- Keep it refreshed automaticallySELECT add_continuous_aggregate_policy('metrics_hourly',    start_offset => INTERVAL '3 hours',    end_offset => INTERVAL '1 hour',    schedule_interval => INTERVAL '1 hour');

ALTER TABLE metrics SET (    timescaledb.compress,    timescaledb.compress_segmentby = 'device_id',    timescaledb.compress_orderby = 'time DESC');SELECT add_compression_policy('metrics', INTERVAL '7 days');SELECT add_retention_policy('metrics', INTERVAL '180 days');-- time_bucket_gapfill for dashboards with missing intervalsSELECT time_bucket_gapfill('1 hour', time) AS bucket,       device_id,       interpolate(avg(cpu_pct))FROM metricsWHERE time > now() - INTERVAL '1 day'GROUP BY bucket, device_id;