Optimasi Query Timeseries Robot Simulator: BRIN Index & Partition

Bottleneck Telemetri Robot Simulator

Simulator robot berbasis web seperti Bittlex-Sim menghasilkan data telemetri berfrekuensi tinggi (50–100 Hz). Setiap tick simulator merekam joint angle, angular velocity, dan motor torque. Pada 100 instance simulator simultan, database menerima jutaan row baru setiap jam.

Masalah utama PostgreSQL pada skala ini bukan hanya throughput INSERT, melainkan overhead index maintenance. Penggunaan B-Tree index pada kolom timestamp menyebabkan buffer pool cache cepat jenuh karena B-Tree harus menyeimbangkan pohonnya untuk jutaan row acak, sehingga IOPS disk melonjak dan query agregasi melambat.

Skema Database: Declarative Range Partitioning

Pemisahan data berdasarkan rentang waktu mencegah satu tabel membengkak hingga puluhan gigabyte. PostgreSQL menyediakan declarative range partitioning untuk memecah tabel utama menjadi partisi harian atau mingguan.

CREATE TABLE robot_telemetry (
    recorded_at TIMESTAMPTZ NOT NULL,
    robot_id UUID NOT NULL,
    joint_index SMALLINT NOT NULL,
    position_rad REAL NOT NULL,
    velocity_rads REAL NOT NULL,
    torque_nm REAL NOT NULL,
    CONSTRAINT pk_robot_telemetry PRIMARY KEY (recorded_at, robot_id, joint_index)
) PARTITION BY RANGE (recorded_at);

Buat partisi untuk menampung rentang data spesifik:

CREATE TABLE robot_telemetry_2026_03_30 PARTITION OF robot_telemetry
    FOR VALUES FROM ('2026-03-30 00:00:00+00') TO ('2026-03-31 00:00:00+00');

CREATE TABLE robot_telemetry_2026_03_31 PARTITION OF robot_telemetry
    FOR VALUES FROM ('2026-03-31 00:00:00+00') TO ('2026-04-01 00:00:00+00');

BRIN Index vs B-Tree pada Data Timeseries

Data telemetri robot ditulis secara sekuensial berdasarkan waktu. Karakteristik ini membuat BRIN (Block Range Index) jauh lebih optimal dibanding B-Tree. BRIN hanya menyimpan nilai minimum dan maksimum per blok disk (default 128 block / 1 MB), bukan indexing per row individu.

Implementasi index BRIN pada tabel partisi:

CREATE INDEX idx_telemetry_recorded_at_brin
    ON robot_telemetry USING BRIN (recorded_at) WITH (pages_per_range = 64);

Catatan: Parameter pages_per_range = 64 memperkecil rentang blok yang discan. Nilai default adalah 128. Menguranginya meningkatkan presisi filter dengan trade-off sedikit penambahan ukuran index.

Analisis Kueri: EXPLAIN ANALYZE

Berikut perbandingan eksekusi query penghitungan mean torque per joint dalam rentang waktu 30 menit.

1. Query Sebelum Tuning (Tabel Tunggal + B-Tree Jenuh)

EXPLAIN ANALYZE
SELECT joint_index, AVG(torque_nm) AS avg_torque
FROM unpartitioned_telemetry
WHERE recorded_at BETWEEN '2026-03-30 10:00:00+00' AND '2026-03-30 10:30:00+00'
GROUP BY joint_index;

-- Output Execution Plan:
HashAggregate  (cost=142340.50..142340.62 rows=12 width=36) (actual time=1820.412..1820.418 rows=12 loops=1)
  Group Key: joint_index
  ->  Index Scan using idx_unpartitioned_time on unpartitioned_telemetry (cost=0.56..135840.00 rows=1300100 width=6)
        Index Cond: ((recorded_at >= '2026-03-30 10:00:00+00') AND (recorded_at <= '2026-03-30 10:30:00+00'))
Planning Time: 0.150 ms
Execution Time: 1821.102 ms

2. Query Setelah Tuning (Partitioning + BRIN)

EXPLAIN ANALYZE
SELECT joint_index, AVG(torque_nm) AS avg_torque
FROM robot_telemetry
WHERE recorded_at BETWEEN '2026-03-30 10:00:00+00' AND '2026-03-30 10:30:00+00'
GROUP BY joint_index;

-- Output Execution Plan:
HashAggregate  (cost=18450.20..18450.32 rows=12 width=36) (actual time=82.115..82.121 rows=12 loops=1)
  Group Key: joint_index
  ->  Append  (cost=42.10..17800.00 rows=1300100 width=6)
        ->  Bitmap Heap Scan on robot_telemetry_2026_03_30  (cost=42.10..11299.50 rows=1300100 width=6)
              Recheck Cond: ((recorded_at >= '2026-03-30 10:00:00+00') AND (recorded_at <= '2026-03-30 10:30:00+00'))
              Rows Removed by Index Recheck: 1240
              ->  Bitmap Index Scan on idx_telemetry_recorded_at_brin  (cost=0.00..35.20 rows=1300100 width=0)
                    Index Cond: ((recorded_at >= '2026-03-30 10:00:00+00') AND (recorded_at <= '2026-03-30 10:30:00+00'))
Planning Time: 0.285 ms
Execution Time: 83.040 ms

Optimasi memangkas waktu eksekusi dari 1821 ms menjadi 83 ms. Fitur Partition Pruning memastikan hanya partisi hari terkait yang diakses, sedangkan BRIN index memangkas transfer data block ke RAM secara presisi.

Pruning Partisi Lama Tanpa Lock Contention

Menghapus data usang via DELETE FROM robot_telemetry WHERE recorded_at < NOW() - INTERVAL '7 days'; menyebabkan table bloat, memicu I/O disk tinggi, dan mengunci baris data (lock contention).

Gunakan pendekatan partisi dua tahap: DETACH CONCURRENTLY lalu DROP TABLE. Pendekatan ini tidak menahan exclusive table lock pada tabel utama, sehingga simulator tetap bisa menulis data tanpa latency spike.

-- Langkah 1: Lepaskan partisi dari tabel utama secara non-blocking (PostgreSQL 14+)
ALTER TABLE robot_telemetry 
    DETACH PARTITION robot_telemetry_2026_03_23 CONCURRENTLY;

-- Langkah 2: Hapus tabel yang sudah terpisah secara aman
DROP TABLE robot_telemetry_2026_03_23;

Kesimpulan Implementasi

Kombinasi declarative range partitioning dan BRIN index mengatasi scaling issue database timeseries pada simulator robot:

• Throughput Tulis Terjaga: BRIN index tidak membutuhkan rebalancing berkelanjutan seperti B-Tree.
• Partition Pruning: PostgreSQL melewati pembacaan disk untuk rentang tanggal di luar target filter query.
• Retensi Data Murah: Data kadaluarsa dibersihkan via partisi detach dan drop tanpa vacuum overhead atau lock contention.