TimescaleDB 시계열 데이터베이스 운영 가이드: Hypertable 설계부터 연속 집계와 압축 최적화까지

사용자 관점:
┌───────────────────────────────────────────┐
│           sensor_data (Hypertable)         │
│  SELECT * FROM sensor_data                │
│  WHERE time > now() - interval '1 hour'   │
└───────────────────────────────────────────┘

내부 구조:
┌────────────────┬────────────────┬────────────────┬────────────────┐
│  _hyper_1_1    │  _hyper_1_2    │  _hyper_1_3    │  _hyper_1_4    │
│  (2026-02-15   │  (2026-02-22   │  (2026-03-01   │  (2026-03-07   │
│   ~02-21)      │   ~02-28)      │   ~03-06)      │   ~현재)       │
│  [압축됨]      │  [압축됨]      │  [압축 예정]   │  [활성]        │
└────────────────┴────────────────┴────────────────┴────────────────┘

# Ubuntu/Debian 기준 설치
sudo apt install gnupg postgresql-common apt-transport-https lsb-release wget

# TimescaleDB 패키지 저장소 추가
echo "deb https://packagecloud.io/timescale/timescaledb/ubuntu/ $(lsb_release -c -s) main" \
  | sudo tee /etc/apt/sources.list.d/timescaledb.list
wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | sudo apt-key add -
sudo apt update

# PostgreSQL 17용 TimescaleDB 설치
sudo apt install timescaledb-2-postgresql-17

# 자동 튜닝 실행 (postgresql.conf 최적화)
sudo timescaledb-tune --yes

# PostgreSQL 재시작
sudo systemctl restart postgresql

-- 데이터베이스에 확장 활성화
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- 설치 확인
SELECT extversion FROM pg_extension WHERE extname = 'timescaledb';
-- 결과: 2.17.2 (2026년 3월 기준 최신)

# timescaledb-tune이 자동 설정하지만 수동 확인이 필요한 항목
shared_preload_libraries = 'timescaledb'
max_worker_processes = 32          # 병렬 쿼리 + 백그라운드 워커
max_parallel_workers_per_gather = 4
max_parallel_workers = 16
timescaledb.max_background_workers = 16  # 압축, 리오더 등 백그라운드 작업

# 시계열 워크로드 최적화
random_page_cost = 1.1             # SSD 기준
effective_io_concurrency = 200     # SSD 기준
checkpoint_completion_target = 0.9
wal_buffers = 64MB

-- 일반 테이블 생성 (PostgreSQL 표준)
CREATE TABLE sensor_data (
    time        TIMESTAMPTZ      NOT NULL,
    sensor_id   INTEGER          NOT NULL,
    location    TEXT             NOT NULL,
    temperature DOUBLE PRECISION,
    humidity    DOUBLE PRECISION,
    pressure    DOUBLE PRECISION,
    battery     DOUBLE PRECISION
);

-- Hypertable로 변환
SELECT create_hypertable(
    'sensor_data',           -- 테이블 이름
    'time',                  -- 시간 컬럼
    chunk_time_interval => INTERVAL '1 day',  -- 청크 간격
    if_not_exists => TRUE
);

-- 다차원 파티셔닝 (시간 + 공간)
-- 센서 수가 매우 많고 센서별 조회가 빈번한 경우 유용
SELECT create_hypertable(
    'sensor_data',
    'time',
    chunk_time_interval => INTERVAL '1 day',
    partitioning_column => 'sensor_id',  -- 공간 차원 추가
    number_partitions => 4               -- 해시 파티션 수
);

-- 센서별 + 시간 복합 인덱스 (가장 빈번한 쿼리 패턴)
CREATE INDEX idx_sensor_time ON sensor_data (sensor_id, time DESC);

-- 위치별 조회가 빈번한 경우
CREATE INDEX idx_location_time ON sensor_data (location, time DESC);

-- 최근 데이터 위주의 부분 인덱스 (인덱스 크기 절약)
CREATE INDEX idx_recent_temp ON sensor_data (sensor_id, time DESC)
    WHERE time > now() - INTERVAL '30 days';

-- 시간별 집계 Continuous Aggregate 생성
CREATE MATERIALIZED VIEW sensor_hourly
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', time)   AS bucket,
    sensor_id,
    location,
    AVG(temperature)              AS avg_temp,
    MIN(temperature)              AS min_temp,
    MAX(temperature)              AS max_temp,
    AVG(humidity)                 AS avg_humidity,
    COUNT(*)                      AS sample_count
FROM sensor_data
GROUP BY bucket, sensor_id, location
WITH NO DATA;  -- 생성 시 즉시 집계하지 않음

-- 자동 갱신 정책 설정
-- 1시간마다 실행, 최근 3시간 데이터를 갱신 대상으로 설정
SELECT add_continuous_aggregate_policy('sensor_hourly',
    start_offset    => INTERVAL '3 hours',   -- 얼마나 과거부터 갱신할 것인가
    end_offset      => INTERVAL '1 hour',    -- 얼마나 최근까지 갱신할 것인가
    schedule_interval => INTERVAL '1 hour'   -- 갱신 주기
);

-- 일별 집계 (시간별 집계 위에 계층적으로 구성 가능)
CREATE MATERIALIZED VIEW sensor_daily
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 day', bucket) AS bucket,
    sensor_id,
    location,
    AVG(avg_temp)                AS avg_temp,
    MIN(min_temp)                AS min_temp,
    MAX(max_temp)                AS max_temp,
    SUM(sample_count)            AS sample_count
FROM sensor_hourly
GROUP BY time_bucket('1 day', bucket), sensor_id, location
WITH NO DATA;

SELECT add_continuous_aggregate_policy('sensor_daily',
    start_offset    => INTERVAL '3 days',
    end_offset      => INTERVAL '1 day',
    schedule_interval => INTERVAL '1 day'
);

-- 실시간 모드 (기본값) - 항상 최신 데이터 반영
ALTER MATERIALIZED VIEW sensor_hourly
    SET (timescaledb.materialized_only = false);

-- 비실시간 모드 - 마지막 갱신 시점까지의 데이터만 반환 (더 빠름)
ALTER MATERIALIZED VIEW sensor_hourly
    SET (timescaledb.materialized_only = true);

-- 압축 설정 활성화
ALTER TABLE sensor_data SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id, location',  -- 세그먼트 기준
    timescaledb.compress_orderby = 'time DESC'               -- 정렬 기준
);

-- 자동 압축 정책: 7일 이상 된 청크를 자동 압축
SELECT add_compression_policy('sensor_data', INTERVAL '7 days');

-- 수동 압축 (특정 청크 즉시 압축)
SELECT compress_chunk(c.chunk_name)
FROM timescaledb_information.chunks c
WHERE c.hypertable_name = 'sensor_data'
  AND c.range_end < now() - INTERVAL '7 days'
  AND NOT c.is_compressed;

-- 압축 상태 확인
SELECT
    chunk_name,
    range_start,
    range_end,
    is_compressed,
    pg_size_pretty(before_compression_total_bytes) AS before_size,
    pg_size_pretty(after_compression_total_bytes)  AS after_size,
    ROUND(
        (1 - after_compression_total_bytes::numeric
             / NULLIF(before_compression_total_bytes, 0)) * 100, 1
    ) AS compression_ratio_pct
FROM timescaledb_information.compressed_chunk_stats
WHERE hypertable_name = 'sensor_data'
ORDER BY range_end DESC
LIMIT 10;

-- 90일 이상 된 원본 데이터 자동 삭제
SELECT add_retention_policy('sensor_data', INTERVAL '90 days');

-- 시간별 집계는 1년 보존
SELECT add_retention_policy('sensor_hourly', INTERVAL '1 year');

-- 일별 집계는 3년 보존
SELECT add_retention_policy('sensor_daily', INTERVAL '3 years');

-- 보존 정책 확인
SELECT * FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention';

-- 수동으로 특정 시점 이전 데이터 즉시 삭제
SELECT drop_chunks('sensor_data', older_than => INTERVAL '90 days');

-- time_bucket: 시간 단위 그룹화 (가장 핵심적인 함수)
SELECT
    time_bucket('15 minutes', time) AS bucket,
    sensor_id,
    AVG(temperature) AS avg_temp,
    percentile_cont(0.95) WITHIN GROUP (ORDER BY temperature) AS p95_temp
FROM sensor_data
WHERE time > now() - INTERVAL '6 hours'
    AND sensor_id = 42
GROUP BY bucket, sensor_id
ORDER BY bucket DESC;

-- time_bucket_gapfill: 데이터가 없는 시간대도 채워서 반환
SELECT
    time_bucket_gapfill('1 hour', time) AS bucket,
    sensor_id,
    AVG(temperature) AS avg_temp,
    COALESCE(AVG(temperature), locf(AVG(temperature))) AS filled_temp
FROM sensor_data
WHERE time > now() - INTERVAL '24 hours'
    AND sensor_id = 42
GROUP BY bucket, sensor_id
ORDER BY bucket;

-- first/last: 시간 기준 첫 번째/마지막 값
SELECT
    time_bucket('1 hour', time) AS bucket,
    sensor_id,
    first(temperature, time) AS opening_temp,
    last(temperature, time)  AS closing_temp,
    MAX(temperature) - MIN(temperature) AS temp_range
FROM sensor_data
WHERE time > now() - INTERVAL '24 hours'
GROUP BY bucket, sensor_id;

-- 쿼리 실행 계획에서 청크 제외 확인
EXPLAIN ANALYZE
SELECT AVG(temperature)
FROM sensor_data
WHERE time > now() - INTERVAL '2 hours'
    AND sensor_id = 42;

-- 결과에서 "Chunks excluded" 항목 확인
-- Append (actual rows=...)
--   -> Index Scan on _hyper_1_42_chunk (actual rows=...)
--        Index Cond: (time > ...)
--   -> (N chunks excluded)  <-- 이 부분이 핵심

-- 1. Hypertable별 전체 크기 확인
SELECT
    hypertable_name,
    pg_size_pretty(hypertable_size(format('%I.%I', hypertable_schema, hypertable_name)::regclass)) AS total_size,
    pg_size_pretty(pg_total_relation_size(format('%I.%I', hypertable_schema, hypertable_name)::regclass)
        - pg_relation_size(format('%I.%I', hypertable_schema, hypertable_name)::regclass)) AS index_size,
    num_chunks,
    compression_enabled
FROM timescaledb_information.hypertables
ORDER BY hypertable_size(format('%I.%I', hypertable_schema, hypertable_name)::regclass) DESC;

-- 2. 청크별 상세 정보 (크기, 압축 여부, 행 수)
SELECT
    chunk_name,
    range_start,
    range_end,
    is_compressed,
    pg_size_pretty(
        pg_total_relation_size(format('%I.%I', chunk_schema, chunk_name)::regclass)
    ) AS chunk_size
FROM timescaledb_information.chunks
WHERE hypertable_name = 'sensor_data'
ORDER BY range_end DESC
LIMIT 20;

-- 3. 백그라운드 작업(Jobs) 상태 모니터링
SELECT
    job_id,
    application_name,
    proc_name,
    hypertable_name,
    schedule_interval,
    last_run_status,
    last_run_started_at,
    last_run_duration,
    next_start,
    total_successes,
    total_failures
FROM timescaledb_information.job_stats js
JOIN timescaledb_information.jobs j USING (job_id)
ORDER BY last_run_started_at DESC;

-- 4. 압축 효율 종합 리포트
SELECT
    hypertable_name,
    pg_size_pretty(SUM(before_compression_total_bytes)) AS before_compression,
    pg_size_pretty(SUM(after_compression_total_bytes))  AS after_compression,
    ROUND(
        (1 - SUM(after_compression_total_bytes)::numeric
             / NULLIF(SUM(before_compression_total_bytes), 0)) * 100, 1
    ) AS savings_pct,
    COUNT(*) AS compressed_chunks
FROM timescaledb_information.compressed_chunk_stats
GROUP BY hypertable_name;

-- 실패한 작업 상세 확인
SELECT
    job_id,
    proc_name,
    last_run_status,
    last_run_started_at,
    last_run_duration,
    config
FROM timescaledb_information.job_stats js
JOIN timescaledb_information.jobs j USING (job_id)
WHERE last_run_status = 'Failed';

-- 작업 로그에서 오류 메시지 확인 (PostgreSQL 로그)
-- 일반적인 원인: 메모리 부족(work_mem), 디스크 공간 부족, 락 경합

-- 수동으로 연속 집계 갱신 실행 (디버깅 목적)
CALL refresh_continuous_aggregate('sensor_hourly',
    now() - INTERVAL '3 hours',
    now() - INTERVAL '1 hour'
);

-- 특정 청크 압축 해제
SELECT decompress_chunk('_timescaledb_internal._hyper_1_3_chunk');

-- 데이터 INSERT/UPDATE 수행 후 다시 압축
SELECT compress_chunk('_timescaledb_internal._hyper_1_3_chunk');

-- 청크 간격 변경 (새 청크부터 적용)
SELECT set_chunk_time_interval('sensor_data', INTERVAL '1 hour');