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;

CREATE EXTENSION IF NOT EXISTS timescaledb_toolkit;CREATE MATERIALIZED VIEW cpu_percentilesWITH (timescaledb.continuous) ASSELECT    time_bucket('1 hour', time) AS bucket,    device_id,    percentile_agg(cpu_pct) AS cpu_distFROM metricsGROUP BY bucket, device_id;SELECT bucket, device_id,       approx_percentile(0.95, cpu_dist) AS p95_cpu,       approx_percentile(0.50, cpu_dist) AS median_cpuFROM cpu_percentilesWHERE bucket > now() - INTERVAL '1 day';

SELECT set_chunk_time_interval('metrics', INTERVAL '1 day');SELECT chunk_name, range_start, range_end, is_compressedFROM timescaledb_information.chunksWHERE hypertable_name = 'metrics'ORDER BY range_start DESCLIMIT 10;-- Chunks matching a predicate are excluded from scans by the planner-- automatically; verify with EXPLAIN that only relevant chunks are touchedEXPLAIN (ANALYZE, BUFFERS)SELECT avg(cpu_pct) FROM metricsWHERE time > now() - INTERVAL '2 hours';

-- Default: materialized_only = false, so queries transparently union-- finalized buckets with a live query over the raw hypertable for-- the not-yet-refreshed tailALTER MATERIALIZED VIEW metrics_hourly SET (timescaledb.materialized_only = false);-- Disable real-time blending for large dashboards where a few minutes-- of staleness is acceptable and you want a guaranteed-fast queryALTER MATERIALIZED VIEW metrics_hourly SET (timescaledb.materialized_only = true);-- Force an immediate manual refresh of a specific windowCALL refresh_continuous_aggregate('metrics_hourly', now() - INTERVAL '3 hours', now());

SELECT job_id, application_name, schedule_interval, next_start, configFROM timescaledb_information.jobsWHERE hypertable_name = 'metrics';SELECT job_id, succeeded, start_time, finish_time, total_runtimeFROM timescaledb_information.job_historyORDER BY start_time DESCLIMIT 10;-- Change a policy's cadence after the factSELECT alter_job(job_id, schedule_interval => INTERVAL '30 minutes')FROM timescaledb_information.jobsWHERE hypertable_name = 'metrics' AND proc_name = 'policy_compression';