karawaci.kode

2026-08-06 · 10 min

TimescaleDB untuk Analytics Time-Series: Setup dan Query Pattern Dashboard Real-Time

Dashboard real-time yang lambat hampir selalu punya akar masalah yang sama: query GROUP BY atas tabel event yang terus tumbuh, dijalankan ulang setiap kali ada user yang buka halaman. Di klien Jakarta yang menangani ratusan ribu transaksi per hari, saya mengganti pendekatan itu dengan TimescaleDB — dan hasilnya cukup dramatis tanpa perlu migrasi stack.

Kenapa Time-Series Butuh Perlakuan Berbeda

Data time-series punya karakteristik yang tidak cocok dengan tabel relasional biasa: data terus di-insert (append-heavy), hampir tidak pernah di-update, dan query hampir selalu berbentuk “beri saya agregat dalam rentang waktu X”. PostgreSQL standar sudah bisa menyimpan ini, tapi tanpa optimasi khusus, query GROUP BY date_trunc('hour', created_at) atas tabel 100 juta baris akan lambat karena harus scan penuh bahkan dengan indeks.

Alternatifnya ada tiga: InfluxDB, ClickHouse, atau TimescaleDB. Saya memilih TimescaleDB karena satu alasan utama — klien sudah pakai Postgres, tim sudah kenal SQL, dan saya tidak ingin memperkenalkan query language baru untuk permasalahan yang bisa diselesaikan dengan ekstensi.

Setup Dasar: Ekstensi, Hypertable, Chunk

Instalasi di Docker Compose:

services:
  timescaledb:
    image: timescale/timescaledb:latest-pg16
    environment:
      POSTGRES_USER: appuser
      POSTGRES_PASSWORD: secret
      POSTGRES_DB: analytics
    ports:
      - "5432:5432"
    volumes:
      - timescale_data:/var/lib/postgresql/data

Atau kalau sudah punya Postgres dan mau tambah ekstensi:

CREATE EXTENSION IF NOT EXISTS timescaledb;

Buat tabel event, lalu konversi ke hypertable:

CREATE TABLE transaction_events (
  id          BIGSERIAL,
  occurred_at TIMESTAMPTZ NOT NULL,
  user_id     UUID NOT NULL,
  merchant_id UUID NOT NULL,
  amount      NUMERIC(15, 2) NOT NULL,
  currency    CHAR(3) NOT NULL DEFAULT 'IDR',
  status      TEXT NOT NULL,
  channel     TEXT NOT NULL
);

-- Konversi ke hypertable dengan partisi per 7 hari
SELECT create_hypertable(
  'transaction_events',
  by_range('occurred_at', INTERVAL '7 days')
);

create_hypertable() mengubah tabel biasa menjadi hypertable yang dipartisi secara otomatis ke “chunk” berdasarkan rentang waktu. Dari luar tetap terlihat seperti tabel biasa — INSERT, SELECT, JOIN semua berjalan normal. Yang berubah adalah cara Postgres menyimpan dan mengakses data di bawahnya: query dengan filter WHERE occurred_at BETWEEN ... hanya menyentuh chunk yang relevan, bukan seluruh tabel.

Indeks yang perlu dibuat:

-- Indeks komposit: waktu + kolom yang sering di-filter
CREATE INDEX idx_tx_occurred_user    ON transaction_events (occurred_at DESC, user_id);
CREATE INDEX idx_tx_occurred_merchant ON transaction_events (occurred_at DESC, merchant_id);
CREATE INDEX idx_tx_occurred_status  ON transaction_events (occurred_at DESC, status);

Continuous Aggregate: Fondasi Dashboard yang Cepat

Ini fitur yang paling membuat perbedaan di production. Continuous aggregate adalah materialized view inkremental — TimescaleDB hanya me-refresh bucket waktu yang baru atau berubah, bukan recompute seluruh history.

Contoh: kita butuh volume transaksi per jam per merchant untuk dashboard:

CREATE MATERIALIZED VIEW tx_hourly_by_merchant
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', occurred_at) AS bucket,
  merchant_id,
  COUNT(*)                            AS tx_count,
  SUM(amount)                         AS total_amount,
  AVG(amount)                         AS avg_amount,
  COUNT(*) FILTER (WHERE status = 'success') AS success_count,
  COUNT(*) FILTER (WHERE status = 'failed')  AS failed_count
FROM transaction_events
GROUP BY bucket, merchant_id
WITH NO DATA;

-- Refresh otomatis: setiap 10 menit, untuk data 1 jam terakhir
SELECT add_continuous_aggregate_policy(
  'tx_hourly_by_merchant',
  start_offset => INTERVAL '2 hours',
  end_offset   => INTERVAL '10 minutes',
  schedule_interval => INTERVAL '10 minutes'
);

Catatan penting soal end_offset: TimescaleDB tidak meng-aggregate data yang terlalu dekat dengan “now” karena chunk belum selesai. Kalau dashboard Anda butuh data real-time detik terakhir, Anda perlu query gabungan antara continuous aggregate dan raw data untuk interval terakhir — saya tunjukkan di bagian query pattern.

Retention Policy: Storage Terkontrol

Tanpa retention policy, storage akan terus tumbuh. TimescaleDB drop seluruh chunk sekaligus — jauh lebih cepat dari DELETE ... WHERE occurred_at < ... yang menghasilkan bloat:

-- Raw events: simpan 90 hari
SELECT add_retention_policy(
  'transaction_events',
  drop_after => INTERVAL '90 days'
);

-- Hourly aggregate: simpan 2 tahun (data sudah di-aggregate, kecil)
SELECT add_retention_policy(
  'tx_hourly_by_merchant',
  drop_after => INTERVAL '2 years'
);

Untuk data yang perlu disimpan lebih lama tapi jarang diakses, pakai tiered storage (TimescaleDB Cloud) atau buat tabel daily aggregate yang terpisah:

CREATE MATERIALIZED VIEW tx_daily_by_merchant
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 day', occurred_at) AS bucket,
  merchant_id,
  SUM(amount)  AS total_amount,
  COUNT(*)     AS tx_count
FROM transaction_events
GROUP BY bucket, merchant_id
WITH NO DATA;

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

-- Daily aggregate disimpan selamanya (atau sangat lama)
-- Tidak perlu retention policy untuk ini

Query Pattern untuk Dashboard

1. Volume per jam, 24 jam terakhir

Query ini memukul continuous aggregate, bukan raw table. P95 di bawah 50ms bahkan dengan ribuan merchant:

SELECT
  bucket,
  SUM(tx_count)    AS total_tx,
  SUM(total_amount) AS total_amount
FROM tx_hourly_by_merchant
WHERE
  bucket >= NOW() - INTERVAL '24 hours'
  AND merchant_id = $1
GROUP BY bucket
ORDER BY bucket;

2. Real-time: gabung aggregate dengan raw data terbaru

Untuk menampilkan data yang belum di-aggregate (10 menit terakhir), gabungkan dua sumber:

WITH recent_raw AS (
  SELECT
    time_bucket('1 hour', occurred_at) AS bucket,
    COUNT(*)                            AS tx_count,
    SUM(amount)                         AS total_amount
  FROM transaction_events
  WHERE
    occurred_at >= NOW() - INTERVAL '1 hour'
    AND merchant_id = $1
  GROUP BY bucket
),
historical AS (
  SELECT bucket, tx_count, total_amount
  FROM tx_hourly_by_merchant
  WHERE
    bucket >= NOW() - INTERVAL '25 hours'
    AND bucket < NOW() - INTERVAL '1 hour'
    AND merchant_id = $1
)
SELECT * FROM historical
UNION ALL
SELECT * FROM recent_raw
ORDER BY bucket;

3. Perbandingan periode: minggu ini vs minggu lalu

SELECT
  time_bucket('1 day', bucket)         AS day,
  SUM(total_amount) FILTER (
    WHERE bucket >= date_trunc('week', NOW())
  ) AS this_week,
  SUM(total_amount) FILTER (
    WHERE bucket >= date_trunc('week', NOW()) - INTERVAL '1 week'
      AND bucket <  date_trunc('week', NOW())
  ) AS last_week
FROM tx_daily_by_merchant
WHERE
  bucket >= date_trunc('week', NOW()) - INTERVAL '1 week'
  AND merchant_id = $1
GROUP BY day
ORDER BY day;

4. Moving average untuk anomaly detection sederhana

SELECT
  bucket,
  total_amount,
  AVG(total_amount) OVER (
    ORDER BY bucket
    ROWS BETWEEN 23 PRECEDING AND CURRENT ROW
  ) AS moving_avg_24h
FROM tx_hourly_by_merchant
WHERE
  bucket >= NOW() - INTERVAL '7 days'
  AND merchant_id = $1
ORDER BY bucket;

Monitoring Hypertable dan Chunk

Pantau ukuran chunk dan kompresi secara berkala:

-- Ukuran tiap chunk
SELECT
  chunk_schema,
  chunk_name,
  range_start,
  range_end,
  pg_size_pretty(total_bytes) AS total_size
FROM timescaledb_information.chunks
WHERE hypertable_name = 'transaction_events'
ORDER BY range_start DESC
LIMIT 20;

-- Status continuous aggregate
SELECT
  view_name,
  last_run_started_at,
  last_run_duration,
  next_start
FROM timescaledb_information.job_stats
JOIN timescaledb_information.jobs USING (job_id)
WHERE application_name LIKE 'Refresh Continuous%';

Kalau last_run_duration continuous aggregate mulai lebih dari beberapa menit, biasanya tanda bahwa start_offset terlalu lebar atau data masuk terlalu cepat sehingga refresh tidak selesai sebelum jadwal berikutnya jalan lagi.

Trade-off yang Perlu Diakui

Kompresi tidak gratis. TimescaleDB punya fitur kompresi kolumnar yang bisa mengecilkan storage 10-20x, tapi data yang terkompresi tidak bisa di-UPDATE atau di-DELETE secara langsung — harus decompress dulu. Kalau use case Anda butuh koreksi event historis, pertimbangkan ini sebelum mengaktifkan kompresi.

Continuous aggregate punya lag. Data di aggregate view selalu “tertinggal” sebesar end_offset. Untuk dashboard yang butuh data benar-benar real-time (detik terakhir), Anda tetap perlu query raw table untuk window terkecil. Ini tambahan kompleksitas di query layer.

Upgrade ekstensi perlu hati-hati. TimescaleDB versi major kadang butuh migrasi catalog. Di production, saya selalu test upgrade di staging dua minggu sebelum apply ke production, dan pastikan ada backup chunk yang fresh. Jangan auto-upgrade ekstensi ini seperti library biasa.

Biaya lisensi cloud. TimescaleDB Community (self-host) gratis. Beberapa fitur lanjutan seperti tiered storage dan distributed hypertable ada di Timescale Cloud (berbayar) atau TimescaleDB Enterprise. Kalau self-host cukup, Community sudah sangat capable untuk mayoritas use case.

Verdict

TimescaleDB adalah pilihan tepat kalau Anda sudah Postgres-first dan butuh analytics time-series yang performan tanpa menambah sistem baru ke stack. Setup yang saya paparkan di atas — hypertable dengan chunk 7 hari, dua layer continuous aggregate (hourly dan daily), retention policy berlapis, dan query pattern yang menggabungkan aggregate dengan raw untuk real-time — sudah cukup untuk menangani dashboard dengan jutaan event per hari di VPS biasa.

Jangan pakai TimescaleDB kalau Anda memang butuh ad-hoc analytics kolumnar atas dataset skala terabyte per query — untuk itu ClickHouse memang lebih cocok. Tapi untuk 90% kebutuhan analytics dashboard startup dan scale-up yang timnya sudah di ekosistem Postgres, TimescaleDB menghemat Anda dari memperkenalkan satu sistem baru yang butuh ops tersendiri.

Ditulis oleh Reza Pradipta