Hypertable TimescaleDB

Apa Itu TimescaleDB?

Hypertabel

CREATE EXTENSION IF NOT EXISTS timescaledb;

CREATE TABLE metrics (
  ts TIMESTAMPTZ NOT NULL,
  device_id BIGINT NOT NULL,
  value DOUBLE PRECISION
);

SELECT create_hypertable('metrics', 'ts');
-- metrics is now a hypertable, chunks created automatically.

Interval Waktu Potongan

SELECT create_hypertable('metrics', 'ts', chunk_time_interval => INTERVAL '1 day');
-- One chunk per day, smaller and faster to drop.

Menyisipkan Data

INSERT INTO metrics (ts, device_id, value)
VALUES (NOW(), 1, 42.5);

Membuat Kueri pada Hypertabel

SELECT * FROM metrics
WHERE ts >= NOW() - INTERVAL '7 days'
ORDER BY ts DESC LIMIT 100;
-- Only the relevant chunks are read.

time_bucket: date_trunc yang Lebih Cerdas

SELECT time_bucket('1 hour', ts) AS hour,
       AVG(value) AS avg_value
FROM metrics
WHERE device_id = 42
  AND ts >= NOW() - INTERVAL '24 hours'
GROUP BY hour
ORDER BY hour;

Pemartisian Multi-Kolom

SELECT create_hypertable('metrics', 'ts', 'device_id', 4);
-- Hash-partitions device_id into 4 space partitions per time chunk.

Membuat Kueri dengan Kedua Filter

SELECT * FROM metrics
WHERE device_id = 42
  AND ts >= NOW() - INTERVAL '7 days';
-- Prunes by both time and space.

Indeks pada Hypertabel

CREATE INDEX metrics_device_idx ON metrics (device_id, ts DESC);
-- Composite index ideal for 'recent data for one device' queries.

Memperbarui Hypertabel

Memigrasikan dari Tabel Biasa

SELECT create_hypertable('metrics', 'ts', migrate_data => true);

Ringkasan

Pemeriksaan Singkat