PostgreSQL + Timescale 전용 시계열 기능 정리 가이드
Hypertable 생성 및 관리
create_hypertable()
일반 테이블을 시계열 최적화 테이블(Hypertable)로 변환
SELECT create_hypertable('metrics', 'time');
SQL
복사
- metrics : 기존 테이블 이름
- time : 시간 컬럼(타임스태프 기반 파티셔닝 기준)
•
내부적으로 자동 chunking(시간 단위 분할) 수행
•
여러 노드(분산 환경) 사용 시에는 create_distributed_hypertable() 사용
옵션 예시
SELECT create_hypertable('metrics', 'time',
chunk_time_interval => interval '1 day',
if_not_exists => TRUE);
SQL
복사
- chunk_time_interval : 청크 크기 지정(1h, 1d, 7d 등)
- if_not_exists : 이미 있으면 무시
- partitioning_column : 공간 파티셔닝 추가 가능(예 : device_id)
Continuous Aggregates(연속 집계)
CREATE MATERIALIZED VIEW … WITH (timescaledb.continuous)
시계열 데이터를 자동으로 시간 구간별로 미리 집계하는 뷰
CREATE MATERIALIZED VIEW cpu_hourly
WITH (timescaledb.continuous) AS
SELECT
time_bucket('1 hour', time) AS bucket,
host,
AVG(usage) AS avg_usage
FROM cpu_metrics
GROUP BY bucket, host;
SQL
복사
time_bucket()으로 시간 구간을 나누고 Timescale이 자동으로 새 데이터를 집계에 반영
자동 갱신 정책 추가
SELECT add_continuous_aggregate_policy('cpu_hourly',
start_offset => INTERVAL '7 days',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '1 hour');
SQL
복사
시간 그룹화 & 보간 함수
time_bucket()
지정된 간격으로 시간을 그룹화(PostgreSQL의 date_trunc() 보다 유연)
SELECT time_bucket('5 minutes', time) AS bucket, avg(temp)
FROM sensor
GROUP BY bucket;
SQL
복사
time_bucket_gapfill()
누락된 시간 구간을 자동으로 채워줌
SELECT
time_bucket_gapfill('1 minute', time) AS bucket,
loc,
AVG(temp)
FROM sensor
WHERE time > now() - interval '1 hour'
GROUP BY bucket, loc
ORDER BY bucket;
-- interpolate()와 함께 사용
SELECT
time_bucket_gapfill('1 minute', time) AS bucket,
interpolate(avg(temp)) AS temp
FROM sensor
GROUP BY bucket;
SQL
복사
시계열 분석 함수
함수 | 설명 | 예시 |
approx_percentile() | 근사 백분위수 계산 | approx_percentile(0.9, value) |
histogram() | 값 분포 히스토그램 계산 | histogram(temp, 10) |
time_weight() | 시간 가중 평균용 구조체 | time_weight('linear', time, value) |
average() | 시간 가중 평균 계산 | average(time_weight('linear', time, value)) |
counter_agg() | 누적형 데이터(계속 증가하는 카운터) 처리 | counter_agg(time, bytes) |
delta() | 구간별 증분 계산 | delta(counter_agg(...)) |
rate() | 초당 변화율 계산 | rate(counter_agg(...)) |
irate() | 순간 변화율 (instantaneous rate) | irate(counter_agg(...)) |