TimescaleDB untuk Time Series IoT

1. Apa Itu TimescaleDB?

TimescaleDB adalah extension PostgreSQL yang dioptimalkan untuk time-series data. Berbeda dengan database time-series khusus (seperti InfluxDB), TimescaleDB adalah full PostgreSQL — artinya kamu mendapatkan semua fitur PostgreSQL (SQL, JOIN, ACID, indexes) plus optimasi khusus untuk data berbasis waktu.

Keunggulan TimescaleDB adalah kemampuan untuk menyimpan data time-series dan relational dalam satu database. Kamu bisa JOIN data sensor dengan data device, data lokasi, dan data user tanpa perlu ETL ke database berbeda.

TimescaleDB vs Alternatif

2. Instalasi & Setup

Docker Compose

# docker-compose.yml
version: "3.8"
services:
  timescaledb:
    image: timescale/timescaledb:latest-pg16
    ports:
      - "5432:5432"
    environment:
      POSTGRES_PASSWORD: tsdb123
      POSTGRES_DB: iot_data
    volumes:
      - tsdb-data:/var/lib/postgresql/data
    restart: unless-stopped

  grafana:
    image: grafana/grafana:11.0.0
    ports:
      - "3000:3000"
    environment:
      - GF_SECURITY_ADMIN_PASSWORD=admin123
    volumes:
      - grafana-data:/var/lib/grafana
    restart: unless-stopped

volumes:
  tsdb-data:
  grafana-data:

Inisialisasi Database

-- Enable TimescaleDB extension
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Buat tabel untuk sensor data
CREATE TABLE sensor_data (
    time        TIMESTAMPTZ NOT NULL,
    device_id   TEXT NOT NULL,
    location    TEXT,
    temperature DOUBLE PRECISION,
    humidity    DOUBLE PRECISION,
    pressure    DOUBLE PRECISION,
    battery     INTEGER
);

-- Buat tabel untuk device info
CREATE TABLE devices (
    device_id   TEXT PRIMARY KEY,
    name        TEXT NOT NULL,
    type        TEXT,
    location    TEXT,
    firmware    TEXT,
    installed   DATE
);

-- Buat tabel untuk alerts
CREATE TABLE alerts (
    time        TIMESTAMPTZ NOT NULL,
    device_id   TEXT NOT NULL,
    type        TEXT NOT NULL,
    value       DOUBLE PRECISION,
    threshold   DOUBLE PRECISION,
    status      TEXT DEFAULT 'active'
);

-- Convert ke hypertable (time-series optimization)
SELECT create_hypertable('sensor_data', 'time');
SELECT create_hypertable('alerts', 'time');

-- Buat indexes
CREATE INDEX ON sensor_data (device_id, time DESC);
CREATE INDEX ON sensor_data (location, time DESC);
CREATE INDEX ON alerts (device_id, time DESC);
CREATE INDEX ON alerts (status, time DESC);

3. Hypertables

Hypertable adalah abstraksi TimescaleDB di atas PostgreSQL native partitioning. Ketika kamu membuat hypertable, TimescaleDB otomatis mempartisi data berdasarkan waktu (time dimension) dan opsi-opsi space dimension (misalnya device_id).

Cara Kerja Hypertable

# Hypertable mempartisi data ke "chunks":
# - Setiap chunk berisi data untuk rentang waktu tertentu
# - Default chunk interval: 7 hari (auto-tuned)
# - Chunk dibuat otomatis saat data baru masuk
# - Query hanya mengakses chunk yang relevan (chunk exclusion)

# Visualisasi:
# sensor_data (hypertable)
#   ├── _hyper_1_chunk_1  (2026-01-01 to 2026-01-08)
#   ├── _hyper_1_chunk_2  (2026-01-08 to 2026-01-15)
#   ├── _hyper_1_chunk_3  (2026-01-15 to 2026-01-22)
#   └── ...

# Chunk sizing guidelines:
# - Data volume < 1M rows/day → chunk: 1 week
# - Data volume 1M-10M rows/day → chunk: 1 day
# - Data volume > 10M rows/day → chunk: 1 hour

Multi-dimensional Partitioning

-- Hypertable dengan space partitioning berdasarkan device_id
-- Membagi setiap chunk berdasarkan device_id hash
SELECT create_hypertable(
    'sensor_data',
    'time',
    'device_id',
    number_partitions => 4,
    chunk_time_interval => INTERVAL '1 day'
);

-- Monitor chunks
SELECT * FROM timescaledb_information.chunks
WHERE hypertable_name = 'sensor_data'
ORDER BY range_start DESC;

-- Adjust chunk interval
SELECT set_chunk_time_interval('sensor_data', INTERVAL '12 hours');

4. Continuous Aggregates

Continuous aggregates adalah materialized view yang otomatis di-refresh saat data baru masuk. Ini sangat berguna untuk downsampling data IoT — misalnya membuat data per-menit dari data per-detik.

-- Continuous aggregate: data per 5 menit
CREATE MATERIALIZED VIEW sensor_5min
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('5 minutes', time) AS bucket,
    device_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,
    STDDEV(temperature) AS temp_stddev
FROM sensor_data
GROUP BY bucket, device_id, location
WITH NO DATA;

-- Refresh policy: update setiap 5 menit
SELECT add_continuous_aggregate_policy('sensor_5min',
    start_offset => INTERVAL '1 hour',
    end_offset => INTERVAL '5 minutes',
    schedule_interval => INTERVAL '5 minutes'
);

-- Continuous aggregate: data per jam
CREATE MATERIALIZED VIEW sensor_hourly
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', time) AS bucket,
    device_id,
    AVG(temperature) AS avg_temp,
    MIN(temperature) AS min_temp,
    MAX(temperature) AS max_temp,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY temperature) AS p95_temp,
    AVG(humidity) AS avg_humidity,
    COUNT(*) AS readings,
    MAX(battery) AS latest_battery
FROM sensor_data
GROUP BY bucket, device_id
WITH NO DATA;

-- Continuous aggregate: data per hari
CREATE MATERIALIZED VIEW sensor_daily
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 day', time) AS bucket,
    device_id,
    AVG(temperature) AS avg_temp,
    MIN(temperature) AS min_temp,
    MAX(temperature) AS max_temp,
    AVG(humidity) AS avg_humidity,
    SUM(CASE WHEN temperature > 35 THEN 1 ELSE 0 END) AS high_temp_count,
    COUNT(*) AS total_readings
FROM sensor_data
GROUP BY bucket, device_id
WITH NO DATA;

-- Query continuous aggregate (super cepat!)
SELECT * FROM sensor_hourly
WHERE device_id = 'sensor-01'
  AND bucket > NOW() - INTERVAL '7 days'
ORDER BY bucket DESC;

5. Compression

TimescaleDB memiliki fitur native compression yang bisa mengurangi ukuran data 90-95% untuk time-series data. Compression bekerja per-chunk — data diubah dari row-based ke columnar format.

-- Enable compression pada hypertable
ALTER TABLE sensor_data SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device_id',
    timescaledb.compress_orderby = 'time DESC'
);

-- Compression policy: compress chunk yang lebih dari 7 hari
SELECT add_compression_policy('sensor_data', INTERVAL '7 days');

-- Cek status compression
SELECT * FROM timescaledb_information.compressed_chunk_stats;

-- Manual compress chunk spesifik
SELECT compress_chunk('_hyper_1_chunk_5');

-- Manual decompress (untuk update data lama)
SELECT decompress_chunk('_hyper_1_chunk_5');

-- Cek compression ratio
SELECT
    hypertable_name,
    compression_status,
    before_compression_total_bytes,
    after_compression_total_bytes,
    pg_size_pretty(before_compression_total_bytes) AS before_size,
    pg_size_pretty(after_compression_total_bytes) AS after_size
FROM timescaledb_information.compressed_chunk_stats;

6. Retention Policies

Retention policy menghapus data lama secara otomatis dengan me-drop chunks yang sudah expired. Ini sangat efisien karena dropping entire chunk jauh lebih cepat dari DELETE rows.

-- Retention policy: hapus data raw setelah 30 hari
SELECT add_retention_policy('sensor_data', INTERVAL '30 days');

-- Retention policy untuk alerts: simpan 90 hari
SELECT add_retention_policy('alerts', INTERVAL '90 days');

-- Manual drop chunk
SELECT drop_chunks('sensor_data', INTERVAL '60 days');

-- Cek kapan chunk akan dihapus
SELECT
    hypertable_name,
    chunk_name,
    range_start,
    range_end,
    pg_size_pretty(total_bytes) as size
FROM timescaledb_information.chunks
WHERE hypertable_name = 'sensor_data'
ORDER BY range_start;

-- Disable retention policy
SELECT remove_retention_policy('sensor_data');

-- Re-enable
SELECT add_retention_policy('sensor_data', INTERVAL '30 days');

7. Optimized Queries untuk IoT

-- 1. Ambil data terbaru dari semua device
SELECT DISTINCT ON (device_id)
    device_id, time, temperature, humidity, battery
FROM sensor_data
ORDER BY device_id, time DESC;

-- 2. Time bucket query (5 menit rata-rata)
SELECT
    time_bucket('5 minutes', time) AS bucket,
    device_id,
    AVG(temperature) AS avg_temp,
    AVG(humidity) AS avg_humidity
FROM sensor_data
WHERE time > NOW() - INTERVAL '1 hour'
  AND device_id = 'sensor-01'
GROUP BY bucket, device_id
ORDER BY bucket DESC;

-- 3. Moving average (30 menit window)
SELECT
    time,
    device_id,
    temperature,
    AVG(temperature) OVER (
        PARTITION BY device_id
        ORDER BY time
        RANGE BETWEEN INTERVAL '30 minutes' PRECEDING AND CURRENT ROW
    ) AS moving_avg_30m
FROM sensor_data
WHERE device_id = 'sensor-01'
  AND time > NOW() - INTERVAL '24 hours';

-- 4. Anomaly detection (z-score)
WITH stats AS (
    SELECT device_id,
           AVG(temperature) AS mean_temp,
           STDDEV(temperature) AS std_temp
    FROM sensor_data
    WHERE time > NOW() - INTERVAL '7 days'
    GROUP BY device_id
)
SELECT s.time, s.device_id, s.temperature,
       (s.temperature - st.mean_temp) / NULLIF(st.std_temp, 0) AS z_score
FROM sensor_data s
JOIN stats st ON s.device_id = st.device_id
WHERE s.time > NOW() - INTERVAL '1 hour'
  AND ABS((s.temperature - st.mean_temp) / NULLIF(st.std_temp, 0)) > 3
ORDER BY s.time DESC;

-- 5. Latest reading per device dengan LATERAL join
SELECT d.device_id, d.name, d.location, s.time, s.temperature, s.battery
FROM devices d
CROSS JOIN LATERAL (
    SELECT time, temperature, battery
    FROM sensor_data
    WHERE device_id = d.device_id
    ORDER BY time DESC
    LIMIT 1
) s;

8. Integrasi dengan Grafana

# Menambahkan TimescaleDB ke Grafana:
# 1. Grafana → Configuration → Data Sources → Add data source
# 2. Pilih "PostgreSQL"
# 3. Konfigurasi:
#    Host: timescaledb:5432
#    Database: iot_data
#    User: postgres
#    Password: tsdb123
#    SSL Mode: disable (untuk internal network)
# 4. Enable "TimescaleDB" toggle
# 5. Save & Test

# Contoh Grafana query:
# Panel: Temperature time series
SELECT
  $__time(bucket),
  device_id AS metric,
  avg_temp AS "Avg Temperature"
FROM sensor_5min
WHERE $__timeFilter(bucket)
  AND device_id IN ($device_ids)
ORDER BY bucket

# Panel: Device health table
SELECT
  d.device_id AS "Device",
  s.time AS "Last Seen",
  s.temperature AS "Temp (°C)",
  s.battery AS "Battery (%)",
  CASE
    WHEN s.time > NOW() - INTERVAL '5 minutes' THEN '🟢 Online'
    WHEN s.time > NOW() - INTERVAL '1 hour' THEN '🟡 Stale'
    ELSE '🔴 Offline'
  END AS "Status"
FROM devices d
CROSS JOIN LATERAL (
  SELECT time, temperature, battery
  FROM sensor_data WHERE device_id = d.device_id
  ORDER BY time DESC LIMIT 1
) s

9. Quiz: Uji Pemahamanmu!

Setelah membaca tutorial di atas, jawablah 5 pertanyaan berikut untuk menguji pemahamanmu tentang TimescaleDB: