TimescaleDB Continuous Aggregates로 424ms 집계 쿼리가 0.4ms가 되는 원리 — Cagg-on-Cagg 계층 설계와 자동 압축 스케줄

저도 처음 PostgreSQL로 IoT 센서 데이터를 다루기 시작했을 때, 수백만 행을 매번 풀스캔하는 집계 쿼리 때문에 대시보드가 버벅거려서 꽤 고생했습니다. 인덱스 튜닝, 파티셔닝 이것저것 다 써봤는데, 결국 근본적인 해결책은 '미리 계산해서 저장한다'는 단순한 아이디어였습니다. TimescaleDB의 **Continuous Aggregates(이하 CAgg)**가 바로 그 아이디어를 PostgreSQL 안에서 자동으로 처리해줍니다.

CAgg는 GROUP BY time_bucket(...) 형태의 집계를 사전 계산해두는 자동 갱신 Materialized View입니다. 동일 집계 쿼리에서 424ms → 0.433ms(약 979배) 응답 속도 개선이 보고된 사례가 있을 만큼, 잘 설계하면 체감 차이가 큽니다(Dev.to, 2024).

이 글에서는 CAgg 위에 CAgg를 쌓는 Cagg-on-Cagg(계층적 연속 집계) 설계법, Refresh Policy로 자동 갱신 스케줄을 조율하는 방법, 그리고 오래된 청크를 컬럼형 저장으로 자동 전환하는 청크 압축 정책까지 실제 코드와 함께 살펴볼 예정입니다. IoT 센서, 금융 틱 데이터, DevOps 메트릭 어느 쪽이든 패턴 자체는 동일하게 적용됩니다.

핵심 개념

Continuous Aggregates: 자동 갱신 Materialized View

"그냥 Materialized View에 크론잡 붙이면 되는 거 아닌가?" 하는 생각이 드실 수 있는데, CAgg는 몇 가지 중요한 점이 다릅니다.

• 증분 갱신: 전체를 재계산하는 게 아니라 변경이 생긴 시간 버킷만 재계산합니다.
• 실시간 혼합: 기본 동작인 materialized_only = false 상태에서, Refresh가 아직 안 된 최신 구간도 실시간 계산값과 투명하게 병합해서 반환됩니다.
• 배치 증분 실행: TimescaleDB 2.28 이상에서는 대형 Refresh 윈도우도 기본적으로 버킷 단위 배치(buckets_per_batch 기본값 10)로 처리해, 잠금(lock) 경합을 줄이고 처리된 구간부터 즉시 노출됩니다.

기본 생성 문법은 아래와 같습니다.

CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', ts) AS bucket,
  device_id,
  avg(value) AS avg_val,
  max(value) AS max_val,
  min(value) AS min_val
FROM metrics
GROUP BY 1, 2
WITH NO DATA;

WITH NO DATA로 생성하면 초기 데이터 없이 뷰 정의만 만들어집니다. 이후 Refresh Policy를 등록하거나 CALL refresh_continuous_aggregate(...) 로 수동으로 채울 수 있습니다.

Cagg-on-Cagg: 계층 롤업 구조

CAgg 위에 또 CAgg를 쌓을 수 있습니다. 원시 데이터를 1시간 CAgg로 집계하고, 그 위에 1일 CAgg, 그 위에 1개월 CAgg를 얹는 식이죠. 각 계층의 상위 CAgg는 원시 하이퍼테이블이 아니라 하위 CAgg의 Materialization 하이퍼테이블을 소스로 삼습니다.

이 구조의 장점은, 1일 집계 쿼리를 실행할 때 원시 데이터 수억 행이 아니라 1시간 CAgg의 수천 행만 읽으면 된다는 점입니다. 계층을 거칠수록 각 레이어가 다루는 행 수가 기하급수적으로 줄어들기 때문입니다.

한 가지 설계 초기에 확인해둬야 할 제약이 있습니다. 하위 CAgg에 JOIN이 포함되어 있으면 상위 CAgg에는 JOIN을 사용할 수 없습니다. 지원 집계 함수도 일부 제한이 있으니, 스키마 설계 시점에 검토해두시면 나중에 리팩토링 비용을 줄일 수 있습니다.

Refresh Policy: 자동 갱신 스케줄 설계

add_continuous_aggregate_policy()로 등록하는 백그라운드 자동 갱신 작업입니다. 세 가지 파라미터가 핵심입니다.

end_offset이 있는 이유는, 가장 최신 버킷은 아직 데이터가 들어오는 중일 수 있어서 완전히 확정되지 않은 집계값을 저장하지 않기 위함입니다. 보통 end_offset은 버킷 크기와 같거나 약간 크게 설정하는 편이 좋습니다.

Cagg-on-Cagg에서 Refresh 순서 의존성을 이해하는 것이 중요합니다. TimescaleDB는 두 정책의 실행 순서를 자동으로 보장하지 않습니다. 1일 CAgg 정책이 예정 시각에 실행될 때, 1시간 CAgg 정책이 아직 실행 중이거나 약간 지연되었을 경우 최신 버킷이 누락된 채 롤업됩니다. 실제 완화책은 schedule_interval 주기를 맞추는 것이 아니라, 상위 CAgg의 start_offset을 충분히 크게 설정해서 하위 CAgg가 갱신을 완료할 시간 여유를 두는 것입니다.

아래 다이어그램은 올바른 실행 순서와 순서가 역전되었을 때 발생하는 문제를 나란히 보여줍니다.

실용적인 설정 예시를 들면, 1시간 CAgg는 schedule_interval = '1 hour', end_offset = '1 hour'로 잡고, 1일 CAgg는 schedule_interval = '1 day', start_offset = '3 days'로 충분히 여유를 두는 방식입니다. start_offset이 넉넉할수록 하위 CAgg가 예정보다 늦게 완료되더라도 안전하게 커버됩니다.

청크 압축 정책

TimescaleDB의 청크 압축은 오래된 청크를 **행 기반(Row Store) → 컬럼형(Column Store)**으로 자동 전환합니다. 경우에 따라 90~98%에 달하는 압축률이 보고되며, 실제 프로덕션에서 150GB → 15GB(90% 감소) 사례가 공개된 바 있습니다(Dev.to, 2024).

압축 설정은 두 단계로 이루어집니다.

-- 압축 옵션 설정
ALTER TABLE metrics SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'device_id',
  timescaledb.compress_orderby   = 'ts DESC'
);
 
-- 7일 이상 된 청크 자동 압축
SELECT add_compression_policy('metrics', compress_after => INTERVAL '7 days');

compress_segmentby는 쿼리 필터에서 자주 쓰는 컬럼을 지정합니다. 이 값이 잘 설정되면 압축된 청크에서도 해당 컬럼 기준으로 빠르게 세그먼트를 찾을 수 있습니다. compress_after는 청크 인터벌(chunk_interval)의 2배 이상으로 잡는 게 안전합니다. 아직 데이터가 들어오는 Hot 청크가 압축 대상에 걸리면 성능이 오히려 나빠질 수 있기 때문입니다.

CAgg 자체에도 압축 정책을 적용할 수 있어서 장기 보관 비용을 크게 줄일 수 있습니다. 또한 Hypercore(TimescaleDB 2.18에 소개된 하이브리드 행-컬럼 스토리지 엔진)는 신규 데이터를 Row Store에 빠르게 인제스트하고, 설정 나이를 초과한 청크를 Column Store로 자동 전환하는 통합된 방식을 제공합니다. 압축률 98% 달성 사례가 보고되어 있으며, 신규 설치에서는 검토해볼 만한 선택지입니다(공식 블로그 기준).

실전 적용

IoT 센서 모니터링: 다계층 집계 전체 구성

수만 개 센서에서 초당 수천 건이 들어오는 상황을 가정하겠습니다. 1시간 → 1일 Cagg-on-Cagg를 구성하고 각 레이어에 압축 정책까지 붙이는 전체 흐름입니다.

-- 원시 하이퍼테이블 생성
CREATE TABLE metrics (
  ts        TIMESTAMPTZ NOT NULL,
  device_id TEXT,
  value     DOUBLE PRECISION
);
 
SELECT create_hypertable('metrics', 'ts',
  chunk_time_interval => INTERVAL '1 day');
 
-- [1단계] 1시간 CAgg
-- sum_val을 함께 저장해 상위 계층에서 정확한 가중평균 계산에 사용
CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', ts) AS bucket,
  device_id,
  avg(value) AS avg_val,
  max(value) AS max_val,
  min(value) AS min_val,
  sum(value) AS sum_val,
  count(*)   AS cnt
FROM metrics
GROUP BY 1, 2
WITH NO DATA;
 
SELECT add_continuous_aggregate_policy('metrics_hourly',
  start_offset      => INTERVAL '3 hours',
  end_offset        => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour');
 
-- [2단계] 1일 CAgg-on-Cagg (하위 CAgg를 소스로 지정)
-- avg(avg_val) 대신 sum_val/cnt로 가중평균을 계산해 정확도를 보장
CREATE MATERIALIZED VIEW metrics_daily
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 day', bucket) AS day_bucket,
  device_id,
  sum(sum_val) / sum(cnt) AS avg_val,
  max(max_val)            AS max_val,
  min(min_val)            AS min_val,
  sum(sum_val)            AS sum_val,
  sum(cnt)                AS cnt
FROM metrics_hourly   -- 원시 테이블이 아닌 1시간 CAgg 참조
GROUP BY 1, 2
WITH NO DATA;
 
-- start_offset을 3일로 충분히 여유 있게 설정해
-- 1시간 CAgg 갱신이 완료될 시간을 확보
SELECT add_continuous_aggregate_policy('metrics_daily',
  start_offset      => INTERVAL '3 days',
  end_offset        => INTERVAL '1 day',
  schedule_interval => INTERVAL '1 day');
 
-- 원시 테이블 압축
ALTER TABLE metrics SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'device_id',
  timescaledb.compress_orderby   = 'ts DESC'
);
SELECT add_compression_policy('metrics',
  compress_after => INTERVAL '7 days');
 
-- 1시간 CAgg 압축 (30일 이후)
ALTER MATERIALIZED VIEW metrics_hourly SET (timescaledb.compress = true);
SELECT add_compression_policy('metrics_hourly',
  compress_after => INTERVAL '30 days');

sum(sum_val) / sum(cnt) 패턴을 쓰는 이유는 단순한 avg(avg_val)이 버킷마다 건수가 다를 때 수학적으로 부정확하기 때문입니다. sum_val과 cnt를 상위 CAgg에도 함께 전달하면, 나중에 월별 롤업 레이어를 추가할 때도 동일한 방식으로 정확한 평균을 계산할 수 있습니다.

금융 OHLCV 집계: 틱 데이터에서 캔들스틱 차트까지

암호화폐나 주식 틱 데이터를 예로 들면, 1분 CAgg로 OHLCV를 선계산하고 그 위에 1시간 캔들을 쌓는 패턴이 일반적입니다. 이 구조를 적용하면 집계 쿼리 응답이 수백~수천 배 단축되는 사례가 알려져 있습니다.

CREATE TABLE ticks (
  ts     TIMESTAMPTZ NOT NULL,
  symbol TEXT,
  price  NUMERIC(18, 8),
  volume NUMERIC(18, 8)
);
 
SELECT create_hypertable('ticks', 'ts',
  chunk_time_interval => INTERVAL '1 day');
 
-- 1분 OHLCV CAgg
CREATE MATERIALIZED VIEW ohlcv_1m
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 minute', ts) AS bucket,
  symbol,
  first(price, ts) AS open,
  max(price)       AS high,
  min(price)       AS low,
  last(price, ts)  AS close,
  sum(volume)      AS volume
FROM ticks
GROUP BY 1, 2
WITH NO DATA;
 
SELECT add_continuous_aggregate_policy('ohlcv_1m',
  start_offset      => INTERVAL '10 minutes',
  end_offset        => INTERVAL '1 minute',
  schedule_interval => INTERVAL '1 minute');
 
-- 1시간 OHLCV CAgg-on-Cagg
CREATE MATERIALIZED VIEW ohlcv_1h
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', bucket) AS bucket,
  symbol,
  first(open, bucket)  AS open,
  max(high)            AS high,
  min(low)             AS low,
  last(close, bucket)  AS close,
  sum(volume)          AS volume
FROM ohlcv_1m
GROUP BY 1, 2
WITH NO DATA;
 
SELECT add_continuous_aggregate_policy('ohlcv_1h',
  start_offset      => INTERVAL '3 hours',
  end_offset        => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour');

first()와 last()는 TimescaleDB가 제공하는 시간 순서 기반 집계 함수입니다. Cagg-on-Cagg에서 재사용할 때는 first(open, bucket) 처럼 버킷 컬럼을 정렬 기준으로 명시해줘야 합니다.

데이터 수명주기 전체 관리

보존 정책(Retention Policy)까지 결합하면 원시 데이터 경로와 CAgg 경로 각각의 수명주기를 독립적으로 자동화할 수 있습니다.

-- 원시 데이터 90일 보존
SELECT add_retention_policy('metrics',
  drop_after => INTERVAL '90 days');
 
-- 1시간 CAgg 1년 보존
SELECT add_retention_policy('metrics_hourly',
  drop_after => INTERVAL '1 year');
 
-- 1일 CAgg는 장기 보관 (정책 미설정)

한 가지 주의할 점이 있습니다. CAgg의 start_offset을 수동으로 크게 늘려서 히스토리를 재집계하려 할 때, 보존 정책으로 이미 삭제된 원시 청크 구간과 겹칠 경우 해당 구간에 대한 집계값이 0이나 NULL로 오염될 수 있습니다. 이 예제처럼 start_offset을 짧게 유지하면 문제가 없지만, 장기 재집계가 필요한 경우에는 삭제된 청크 범위를 먼저 확인해두시는 게 좋습니다.

초기 Backfill

WITH NO DATA로 생성한 뒤 Refresh Policy만 등록하면, 정책의 start_offset 이전 과거 데이터는 자동으로 채워지지 않습니다. 과거 데이터가 필요하다면 수동으로 백필해야 합니다.

이때 두 가지 주의사항이 있습니다.

첫째, 시작점을 NULL로 넘기면 하이퍼테이블의 첫 번째 청크부터 전체 스캔합니다. 데이터가 많으면 수 시간이 걸릴 수 있으니, 실제로 필요한 기간을 명시하는 것이 낫습니다.

둘째, Cagg-on-Cagg 구조에서는 반드시 하위 CAgg 백필을 먼저 완료한 후에 상위 CAgg 백필을 실행해야 합니다.

-- 1. 하위 CAgg 먼저 백필
CALL refresh_continuous_aggregate(
  'metrics_hourly',
  NOW() - INTERVAL '1 year',
  NOW()
);
 
-- 2. 상위 CAgg는 하위 완료 후에 실행
CALL refresh_continuous_aggregate(
  'metrics_daily',
  NOW() - INTERVAL '1 year',
  NOW()
);

Refresh 작업 모니터링

자동화했다고 믿고 넘어가면, 나중에 Refresh가 조용히 실패하고 있는 걸 늦게 알게 되는 경우가 있습니다. 아래 뷰를 주기적으로 확인해두시면 좋습니다.

-- 작업 최근 실행 결과 확인
SELECT job_id, hypertable_name, last_run_status, last_run_duration
FROM timescaledb_information.job_stats
ORDER BY last_run_started_at DESC;
 
-- 등록된 정책 목록 확인
SELECT *
FROM timescaledb_information.jobs
WHERE proc_name IN (
  'policy_refresh_continuous_aggregate',
  'policy_compression'
);

last_run_status가 Failed인 행이 있다면 즉시 원인을 확인해보시는 게 좋습니다.

장단점 분석

장점

단점 및 주의사항

현장에서 자주 보이는 실수들

compress_after를 너무 짧게 잡는 경우. 청크 인터벌이 1일인데 compress_after = '1 day'로 설정하면 아직 쓰고 있는 청크가 압축에 걸릴 수 있습니다. 최소 청크 인터벌의 2배, 가능하면 데이터 지연(late write) 시간까지 고려해서 여유를 두시면 좋습니다.

상위 CAgg의 start_offset을 너무 짧게 잡는 경우. 1시간 CAgg와 1일 CAgg가 모두 빡빡하게 설정되어 있으면, 1일 CAgg 정책이 실행될 시점에 1시간 CAgg가 아직 처리 중일 수 있습니다. 상위 CAgg의 start_offset을 충분히 크게 잡아서 하위 CAgg 갱신 완료를 기다릴 여유를 두는 것이 핵심입니다.

초기 Backfill을 잊는 경우. WITH NO DATA로 생성한 뒤 Refresh Policy만 등록하면, 정책이 커버하는 구간(start_offset 이전의 과거 데이터)은 자동으로 채워지지 않습니다. 계층이 있는 경우엔 하위 CAgg 백필을 먼저 완료한 후 상위 CAgg 백필을 실행해야 올바른 롤업 값을 얻을 수 있습니다.

마치며

CAgg 하나를 도입하는 것만으로도 집계 쿼리가 수백 배 빨라지는 경험을 할 수 있습니다. 여기에 Cagg-on-Cagg 계층을 추가하면 장기 추세 쿼리도 빠르게 처리할 수 있고, 압축 정책까지 붙이면 스토리지 비용이 90% 이상 줄어드는 효과를 기대할 수 있습니다.

핵심을 정리하면:

• CAgg는 변경된 버킷만 증분 재계산하며, 미갱신 구간은 실시간 계산과 투명하게 혼합됩니다.
• Cagg-on-Cagg에서 Refresh 순서는 TimescaleDB가 자동 보장하지 않습니다. 상위 CAgg의 start_offset을 충분히 크게 설정해서 하위 갱신이 완료될 여유를 확보하는 것이 올바른 완화 방법입니다.
• avg(avg_val) 대신 sum(sum_val) / sum(cnt) 패턴으로 가중평균을 계산하고, sum_val과 cnt를 모든 계층에 전달해두어야 롤업 정확도를 유지할 수 있습니다.
• compress_after는 청크 인터벌의 2배 이상으로 설정하고, Hot 청크가 압축에 걸리지 않도록 여유를 두는 게 중요합니다.
• timescaledb_information.job_stats로 Refresh·압축 작업 성공 여부를 정기적으로 확인하는 모니터링 루틴을 함께 갖추면 훨씬 안정적으로 운용할 수 있습니다.

시작점 3가지:

1. 기존 하이퍼테이블에서 가장 자주 실행하는 집계 쿼리 하나를 골라 1시간 CAgg를 만들고 응답 속도를 비교해보시면 좋습니다.
2. 1시간 CAgg가 안정화되면 1일 Cagg-on-Cagg를 추가해서 장기 추세 대시보드에 연결해볼 수 있습니다. 이때 sum_val과 cnt 전달 패턴도 함께 적용해두세요.
3. compress_after 정책을 등록하고 일주일 뒤 pg_size_pretty(pg_total_relation_size('metrics'))로 변화를 확인해보시면 압축 효과를 실감할 수 있습니다.

참고 자료

도메인 안내: 아래 참고 자료에 tigerdata.com과 timescale.com이 모두 등장합니다. 두 도메인은 동일 서비스의 공식 문서 사이트로, 어느 쪽에서 접근해도 같은 내용을 확인할 수 있습니다.

• TimescaleDB GitHub Releases & CHANGELOG
• 공식 문서 — Continuous Aggregates 개요
• 공식 문서 — Refresh Policies
• 공식 문서 — add_compression_policy()
• 공식 문서 — Hypercore
• Incremental CAgg Refresh Policy PR #7790
• TimescaleDB Compression: From 150GB to 15GB (Dev.to, 2024)
• Real-Time Analytics for Time Series — Medium/Timescale
• How Cloudflare Uses TimescaleDB
• Storing 100k Events/sec with TimescaleDB — Medium
• TimescaleDB Hypertables, Continuous Aggregates & Compression Production Guide
• Hypercore: Hybrid Row-Columnar Storage Engine (공식 블로그)
• TimescaleDB Compression Guide — DEV Community