연속 집계

연속 집계란

연속 집계 만들기

CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT device_id,
       time_bucket('1 hour', ts) AS hour,
       AVG(value) AS avg_value,
       COUNT(*)   AS samples
FROM metrics
GROUP BY device_id, hour;

새로 고침 정책

SELECT add_continuous_aggregate_policy('metrics_hourly',
  start_offset => INTERVAL '1 day',
  end_offset   => INTERVAL '1 hour',
  schedule_interval => INTERVAL '15 minutes');
-- Every 15 minutes, refresh the window from 1 day ago to 1 hour ago.

조회

SELECT * FROM metrics_hourly
WHERE device_id = 42
  AND hour >= NOW() - INTERVAL '7 days'
ORDER BY hour;

실시간 집계

구체화 데이터 전용 모드

ALTER MATERIALIZED VIEW metrics_hourly
  SET (timescaledb.materialized_only = true);

다중 계층 집계

-- daily aggregate from the hourly cagg
CREATE MATERIALIZED VIEW metrics_daily
WITH (timescaledb.continuous) AS
SELECT device_id, time_bucket('1 day', hour) AS day,
       AVG(avg_value) AS avg_value
FROM metrics_hourly
GROUP BY device_id, day;

하이퍼테이블을 압축해도 집계가 차단되지 않습니다

연속 집계가 구체화된 뷰보다 나은 경우

호환성

과거 데이터 채우기

CALL refresh_continuous_aggregate('metrics_hourly',
  NULL, NOW());
-- Recompute from the beginning of time.

복습

빠른 확인