TimescaleDBTimescaleDB (시계열 PostgreSQL)

하이퍼테이블(Hypertable):
- 시간 기준으로 자동 청크(Chunk) 분할
- 청크 단위로 인덱스·압축·만료 정책 적용
- 일반 PostgreSQL 테이블처럼 쿼리

연속 집계(Continuous Aggregate):
- 미리 계산된 집계 결과를 물질화 뷰로 저장
- 실시간으로 자동 갱신

CREATE EXTENSION IF NOT EXISTS timescaledb;

CREATE TABLE sensor_data (
    time        TIMESTAMPTZ NOT NULL,
    sensor_id   TEXT NOT NULL,
    temperature DOUBLE PRECISION,
    humidity    DOUBLE PRECISION
);

SELECT create_hypertable('sensor_data', 'time',
    chunk_time_interval => INTERVAL '7 days');

INSERT INTO sensor_data VALUES
    (NOW(), 'sensor_001', 23.5, 65.2),
    (NOW() - INTERVAL '1h', 'sensor_001', 22.8, 64.5);

SELECT
    time_bucket('1 hour', time) AS bucket,
    sensor_id,
    AVG(temperature) AS avg_temp,
    MAX(temperature) AS max_temp
FROM sensor_data
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY bucket, sensor_id
ORDER BY bucket DESC;

CREATE MATERIALIZED VIEW hourly_avg
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', time) AS bucket,
    sensor_id,
    AVG(temperature) AS avg_temp
FROM sensor_data
GROUP BY bucket, sensor_id;

ALTER TABLE sensor_data SET (
    timescaledb.compress,
    timescaledb.compress_orderby = 'time DESC',
    timescaledb.compress_segmentby = 'sensor_id'
);

SELECT add_compression_policy('sensor_data', INTERVAL '7 days');