TimescaleDB 시계열 데이터베이스 운영 가이드 — 하이퍼테이블, 연속 집계, 데이터 보존

# Ubuntu/Debian 기준 설치
sudo apt install timescaledb-2-postgresql-16

# PostgreSQL 설정 파일에 shared_preload_libraries 추가
sudo timescaledb-tune --yes

# PostgreSQL 재시작
sudo systemctl restart postgresql

# 데이터베이스에서 확장 활성화
psql -d mydb -c "CREATE EXTENSION IF NOT EXISTS timescaledb;"

사용자 관점:
+-------------------------------------------+
|        metrics (Hypertable)                |
|  SELECT * FROM metrics                     |
|  WHERE time > now() - interval '1 hour'    |
+-------------------------------------------+

내부 구조:
+-----------+-----------+-----------+-----------+
| chunk_1   | chunk_2   | chunk_3   | chunk_4   |
| 02-15~21  | 02-22~28  | 03-01~06  | 03-07~now |
| [압축됨]  | [압축됨]  | [압축예정]| [활성]    |
+-----------+-----------+-----------+-----------+

-- 일반 테이블 생성
CREATE TABLE sensor_data (
    time        TIMESTAMPTZ NOT NULL,
    device_id   TEXT        NOT NULL,
    temperature DOUBLE PRECISION,
    humidity    DOUBLE PRECISION,
    battery     DOUBLE PRECISION
);

-- 하이퍼테이블로 변환 (청크 간격 1일)
SELECT create_hypertable(
    'sensor_data',
    by_range('time', INTERVAL '1 day')
);

-- 공간 파티셔닝 추가 (선택사항, 대규모 환경)
SELECT add_dimension(
    'sensor_data',
    by_hash('device_id', 4)
);

-- 청크 간격 변경
SELECT set_chunk_time_interval('sensor_data', INTERVAL '12 hours');

-- 현재 청크 목록 확인
SELECT chunk_name, range_start, range_end,
       pg_size_pretty(total_bytes) AS size,
       is_compressed
FROM timescaledb_information.chunks
WHERE hypertable_name = 'sensor_data'
ORDER BY range_start DESC
LIMIT 10;

-- 하이퍼테이블별 상세 정보
SELECT hypertable_name,
       num_chunks,
       pg_size_pretty(hypertable_size(format('%I.%I',
           hypertable_schema, hypertable_name)::regclass)) AS total_size,
       pg_size_pretty(hypertable_size(format('%I.%I',
           hypertable_schema, hypertable_name)::regclass)
           - pg_total_relation_size(format('%I.%I',
               hypertable_schema, hypertable_name)::regclass)) AS chunk_size
FROM timescaledb_information.hypertables;

-- 청크 수가 과도하게 많은 하이퍼테이블 확인 (1000개 이상이면 주의)
SELECT hypertable_name, num_chunks
FROM timescaledb_information.hypertables
WHERE num_chunks > 1000;

-- 1시간 단위 집계 뷰 생성
CREATE MATERIALIZED VIEW sensor_hourly
WITH (timescaledb.continuous) AS
SELECT
    device_id,
    time_bucket('1 hour', time) AS bucket,
    AVG(temperature)   AS avg_temp,
    MAX(temperature)   AS max_temp,
    MIN(temperature)   AS min_temp,
    AVG(humidity)      AS avg_humidity,
    COUNT(*)           AS sample_count
FROM sensor_data
GROUP BY device_id, bucket
WITH NO DATA;

-- 1일 단위 집계 (계층형 연속 집계 - 연속 집계 위에 연속 집계)
CREATE MATERIALIZED VIEW sensor_daily
WITH (timescaledb.continuous) AS
SELECT
    device_id,
    time_bucket('1 day', bucket) AS bucket,
    AVG(avg_temp)      AS avg_temp,
    MAX(max_temp)      AS max_temp,
    MIN(min_temp)      AS min_temp,
    AVG(avg_humidity)  AS avg_humidity,
    SUM(sample_count)  AS sample_count
FROM sensor_hourly
GROUP BY device_id, bucket
WITH NO DATA;

-- 시간별 집계: 지난 3일 범위를 1시간 간격으로 갱신
SELECT add_continuous_aggregate_policy('sensor_hourly',
    start_offset  => INTERVAL '3 days',
    end_offset    => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour'
);

-- 일별 집계: 지난 1개월 범위를 1일 간격으로 갱신
SELECT add_continuous_aggregate_policy('sensor_daily',
    start_offset  => INTERVAL '1 month',
    end_offset    => INTERVAL '1 day',
    schedule_interval => INTERVAL '1 day'
);

-- 특정 범위를 수동 갱신
CALL refresh_continuous_aggregate('sensor_hourly',
    '2026-03-01', '2026-03-09');

-- 연속 집계 정책 상태 확인
SELECT view_name, schedule_interval,
       config ->> 'start_offset' AS start_offset,
       config ->> 'end_offset'   AS end_offset
FROM timescaledb_information.continuous_aggregate_stats;

-- 연속 집계 쿼리 성능 확인
EXPLAIN ANALYZE
SELECT device_id, bucket, avg_temp
FROM sensor_hourly
WHERE bucket >= now() - INTERVAL '7 days'
  AND device_id = 'sensor-001';

-- 압축 설정: segmentby와 orderby 지정이 핵심
ALTER TABLE sensor_data SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device_id',
    timescaledb.compress_orderby = 'time DESC'
);

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

-- 압축 효과 확인
SELECT
    pg_size_pretty(before_compression_total_bytes) AS before,
    pg_size_pretty(after_compression_total_bytes)  AS after,
    round(
        (1 - after_compression_total_bytes::numeric
             / before_compression_total_bytes) * 100, 1
    ) AS compression_ratio_pct
FROM hypertable_compression_stats('sensor_data');

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

-- 연속 집계에는 더 긴 보존 기간 설정 가능
SELECT add_retention_policy('sensor_hourly', INTERVAL '1 year');
SELECT add_retention_policy('sensor_daily', INTERVAL '5 years');

-- 정책 실행 상태 확인
SELECT application_name, schedule_interval,
       last_run_status, last_run_duration,
       next_start
FROM timescaledb_information.jobs
WHERE application_name LIKE '%retention%'
   OR application_name LIKE '%compress%';

[원시 데이터]  --> [7일 후 압축] --> [90일 후 삭제]
      |
      +-- [시간별 연속 집계] --> [1년 후 삭제]
              |
              +-- [일별 연속 집계] --> [5년 후 삭제]

-- 디바이스별 시간 범위 조회가 빈번한 경우
CREATE INDEX idx_sensor_device_time
ON sensor_data (device_id, time DESC);

-- 특정 조건의 부분 인덱스 (NULL이 아닌 값만)
CREATE INDEX idx_sensor_battery_low
ON sensor_data (device_id, time DESC)
WHERE battery < 20.0;

-- 인덱스 효율 확인
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch,
       pg_size_pretty(pg_relation_size(indexrelid)) AS idx_size
FROM pg_stat_user_indexes
WHERE schemaname = '_timescaledb_internal'
ORDER BY idx_scan DESC
LIMIT 20;

-- GOOD: 시간 범위를 명시하여 청크 제외 활용
SELECT device_id, AVG(temperature)
FROM sensor_data
WHERE time >= now() - INTERVAL '1 hour'
GROUP BY device_id;

-- BAD: 시간 조건이 없으면 모든 청크를 스캔
SELECT device_id, AVG(temperature)
FROM sensor_data
GROUP BY device_id;

-- GOOD: 연속 집계를 활용한 대시보드 쿼리
SELECT device_id, bucket, avg_temp
FROM sensor_hourly
WHERE bucket >= now() - INTERVAL '24 hours'
ORDER BY bucket DESC;

-- 쿼리 성능 분석
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM sensor_data
WHERE time >= now() - INTERVAL '6 hours'
  AND device_id = 'sensor-042';

# telegraf.conf
[agent]
  interval = "10s"
  flush_interval = "10s"

[[inputs.cpu]]
  percpu = true
  totalcpu = true

[[inputs.mem]]

[[inputs.disk]]
  ignore_fs = ["tmpfs", "devtmpfs"]

[[inputs.net]]

[[outputs.postgresql]]
  connection = "host=localhost port=5432 user=telegraf dbname=metrics sslmode=disable"
  create_templates = [
    "CREATE TABLE IF NOT EXISTS {TABLE}({COLUMNS})",
    "SELECT create_hypertable('{TABLE}', by_range('time'), if_not_exists => true)",
  ]
  add_column_templates = [
    "ALTER TABLE {TABLE} ADD COLUMN IF NOT EXISTS {COLUMN} {TYPE}",
  ]
  tag_table_suffix = "_tag"

-- 서버 메트릭 테이블
CREATE TABLE server_metrics (
    time        TIMESTAMPTZ NOT NULL,
    host        TEXT        NOT NULL,
    region      TEXT,
    cpu_usage   DOUBLE PRECISION,
    mem_usage   DOUBLE PRECISION,
    disk_usage  DOUBLE PRECISION,
    net_in      BIGINT,
    net_out     BIGINT
);

SELECT create_hypertable('server_metrics', by_range('time', INTERVAL '1 day'));

-- 압축 설정
ALTER TABLE server_metrics SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'host',
    timescaledb.compress_orderby = 'time DESC'
);

-- 정책 일괄 설정
SELECT add_compression_policy('server_metrics', INTERVAL '3 days');
SELECT add_retention_policy('server_metrics', INTERVAL '30 days');

-- 연속 집계: 5분 단위
CREATE MATERIALIZED VIEW server_metrics_5m
WITH (timescaledb.continuous) AS
SELECT
    host,
    time_bucket('5 minutes', time) AS bucket,
    AVG(cpu_usage)  AS avg_cpu,
    MAX(cpu_usage)  AS max_cpu,
    AVG(mem_usage)  AS avg_mem,
    MAX(mem_usage)  AS max_mem,
    AVG(disk_usage) AS avg_disk
FROM server_metrics
GROUP BY host, bucket
WITH NO DATA;

SELECT add_continuous_aggregate_policy('server_metrics_5m',
    start_offset    => INTERVAL '7 days',
    end_offset      => INTERVAL '5 minutes',
    schedule_interval => INTERVAL '5 minutes'
);

-- 연속 집계: 1시간 단위 (계층형)
CREATE MATERIALIZED VIEW server_metrics_1h
WITH (timescaledb.continuous) AS
SELECT
    host,
    time_bucket('1 hour', bucket) AS bucket,
    AVG(avg_cpu)  AS avg_cpu,
    MAX(max_cpu)  AS max_cpu,
    AVG(avg_mem)  AS avg_mem,
    MAX(max_mem)  AS max_mem,
    AVG(avg_disk) AS avg_disk
FROM server_metrics_5m
GROUP BY host, bucket
WITH NO DATA;

SELECT add_continuous_aggregate_policy('server_metrics_1h',
    start_offset    => INTERVAL '30 days',
    end_offset      => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour'
);

-- 청크에 대한 락 확인
SELECT pid, mode, granted, relation::regclass
FROM pg_locks
WHERE relation IN (
    SELECT format('%I.%I', chunk_schema, chunk_name)::regclass
    FROM timescaledb_information.chunks
    WHERE hypertable_name = 'sensor_data'
      AND NOT is_compressed
);

-- 문제가 되는 세션 종료 (주의: 진행 중인 작업이 롤백됨)
SELECT pg_terminate_backend(<pid>);

-- 임시로 제한 완화 후 수동 압축
SET temp_file_limit = '10GB';
SET maintenance_work_mem = '2GB';
SELECT compress_chunk('<chunk_name>');

-- 백그라운드 워커 재시작
SELECT timescaledb_pre_restore();
SELECT timescaledb_post_restore();

-- 실행 실패한 작업 확인
SELECT job_id, application_name, last_run_status,
       last_run_started_at, last_run_duration,
       total_failures
FROM timescaledb_information.job_stats
WHERE last_run_status = 'Failed';

-- 청크 간격을 적절한 값으로 변경
SELECT set_chunk_time_interval('sensor_data', INTERVAL '1 day');

-- 오래된 작은 청크들을 수동 정리 (보존 정책을 통해)
SELECT drop_chunks('sensor_data', older_than => INTERVAL '30 days');

-- 압축 해제 후 수정
SELECT decompress_chunk('<chunk_name>');
-- UPDATE 또는 DELETE 수행
UPDATE sensor_data SET temperature = NULL
WHERE time = '2026-03-01 12:00:00' AND device_id = 'sensor-bad';
-- 다시 압축
SELECT compress_chunk('<chunk_name>');

-- 긴급 청크 삭제
SELECT drop_chunks('sensor_data', older_than => INTERVAL '7 days');

-- 압축되지 않은 오래된 청크 즉시 압축
SELECT compress_chunk(c.chunk_name)
FROM timescaledb_information.chunks c
WHERE c.hypertable_name = 'sensor_data'
  AND NOT c.is_compressed
  AND c.range_end < now() - INTERVAL '2 days'
ORDER BY c.range_start ASC;

# pg_dump를 사용한 논리 백업
pg_dump -Fc -f backup.dump mydb

# 복구 전 pre_restore 호출 필수
psql -d mydb -c "SELECT timescaledb_pre_restore();"
pg_restore -d mydb backup.dump
psql -d mydb -c "SELECT timescaledb_post_restore();"