Tantangan Skalabilitas Log Percakapan Agen AI

Aplikasi berbasis agen AI menghasilkan volume log yang tinggi dalam format semi-terstruktur. Setiap interaksi mencakup prompt pengguna, respons model, pemanggilan fungsi (tool calls), token count, dan metadata feedback evaluasi. Ketika ukuran tabel mencapai puluhan juta baris, query untuk menganalisis kegagalan respons atau metadata feedback (seperti skor kepuasan dan kategori topik) menjadi bottleneck performa.

Masalah utama terletak pada pembengkakan ukuran tabel dan indeks B-Tree standar. Pencarian sequential scan pada kolom JSONB berukuran besar menguras I/O disk dan RAM buffer cache. Untuk mengatasi hal ini, kita dapat menggabungkan tiga strategi native PostgreSQL: declarative table partitioning, indeks BRIN untuk data berbasis urutan waktu, dan indeks GIN dengan operator class jsonb_path_ops.

Struktur Data dan Skema Partisi Berbasis Waktu

Data log agen AI bersifat append-only dan memiliki korelasi alami dengan waktu pembuatan. Menggunakan partisi berbasis rentang waktu (RANGE partitioning) membatasi cakupan scan hanya pada partisi yang relevan melalui partition pruning.

-- Buat tabel utama dengan partisi RANGE berdasarkan timestamp
CREATE TABLE agent_logs (
    id UUID NOT NULL,
    session_id UUID NOT NULL,
    agent_name VARCHAR(64) NOT NULL,
    payload JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Contoh partisi bulanan
CREATE TABLE agent_logs_2025_01 PARTITION OF agent_logs
    FOR VALUES FROM ('2025-01-01 00:00:00+00') TO ('2025-02-01 00:00:00+00');

CREATE TABLE agent_logs_2025_02 PARTITION OF agent_logs
    FOR VALUES FROM ('2025-02-01 00:00:00+00') TO ('2025-03-01 00:00:00+00');

Contoh dokumen payload JSONB yang disimpan:

{
  "model": "gpt-4o",
  "tokens": {"prompt": 1400, "completion": 220},
  "feedback": {
    "rating": 1,
    "flagged": true,
    "reason": "hallucination"
  },
  "tool_calls": ["retrieval_search", "calc_engine"]
}

Optimasi Timestamp Menggunakan BRIN Index

Indeks B-Tree pada kolom waktu log berukuran besar memakan ruang memori yang signifikan. Karena data log di-insert secara berurutan, posisi fisik baris data di disk (block range) berkorelasi langsung dengan nilai created_at.

Block Range Index (BRIN) hanya menyimpan nilai minimum dan maksimum dari rentang halaman disk (page ranges), bukan memetakan setiap baris seperti B-Tree. Hal ini menghasilkan footprint indeks hingga 99% lebih kecil.

-- Pasang indeks BRIN pada partisi atau tabel induk
CREATE INDEX idx_agent_logs_created_at_brin 
ON agent_logs USING BRIN (created_at) 
WITH (pages_per_range = 128);
Catatan Teknis: Parameter pages_per_range menentukan granularitas. Nilai 128 berarti satu entri indeks memetakan 128 halaman disk (sekitar 1 MB). Jika data di-insert tidak berurutan secara temporal, BRIN kehilangan efektivitasnya.

Pencarian Metadata Menggunakan GIN Index jsonb_path_ops

Untuk menyaring interaksi berdasarkan filter tertentu—seperti mencari respons berperingkat rendah (payload->'feedback'->>'rating' = '1')—diperlukan Generalized Inverted Index (GIN).

Secara default, GIN pada JSONB menggunakan operator class jsonb_ops yang mengindeks key, path, dan value secara terpisah. Untuk kasus penggunaan log yang mayoritas filternya menggunakan operator containment (@>), gunakan jsonb_path_ops.

-- Indeks GIN yang dioptimasi khusus operator containment
CREATE INDEX idx_agent_logs_payload_path_ops 
ON agent_logs USING GIN (payload jsonb_path_ops);

Kelebihan jsonb_path_ops dibanding default jsonb_ops:

  • Hanya menyimpan hash dari seluruh path hingga value (misal: hash dari feedback.flagged = true).
  • Ukuran indeks di disk jauh lebih ringkas (sering kali 30–50% lebih kecil dari default GIN).
  • Performa lookup operator @> lebih cepat dan hemat cache memory.
  • Trade-off: Tidak mendukung operator keberadaan key tunggal seperti ?, ?|, atau ?&.

Analisis Query Plan: Sebelum vs Sesudah Indexing

Skenario analitik: Menemukan log percakapan pada rentang waktu tertentu yang ditandai memiliki feedback halusinasi.

-- Query filter log
EXPLAIN ANALYZE
SELECT id, payload->'feedback' AS feedback
FROM agent_logs
WHERE created_at >= '2025-01-15 00:00:00+00' 
  AND created_at < '2025-01-20 00:00:00+00'
  AND payload @> '{"feedback": {"flagged": true, "reason": "hallucination"}}';

Sebelum Optimasi (Tanpa Indeks Khusus)

Gather (cost=1000.00..185420.50 rows=120 width=84) (actual time=18.420..1420.312 rows=140 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  -> Parallel Seq Scan on agent_logs_2025_01 (cost=0.00..184408.50 rows=50 width=84) (actual time=21.110..1412.050 rows=47 loops=3)
        Filter: ((created_at >= '...'::timestamptz) AND (created_at < '...'::timestamptz) AND (payload @> '...'))
Planning Time: 0.285 ms
Execution Time: 1421.105 ms

Database terpaksa melakukan sequential scan pada jutaan baris data, membutuhkan I/O masif dengan waktu eksekusi di atas 1,4 detik.

Sesudah Optimasi (Partition Pruning + BRIN + GIN jsonb_path_ops)

Bitmap Heap Scan on agent_logs_2025_01 (cost=45.12..312.40 rows=120 width=84) (actual time=1.840..4.215 rows=140 loops=1)
  Recheck Cond: (payload @> '{"feedback": {"flagged": true, "reason": "hallucination"}}'::jsonb)
  Filter: ((created_at >= '...'::timestamptz) AND (created_at < '...'::timestamptz))
  -> Bitmap Index Scan on idx_agent_logs_payload_path_ops (cost=0.00..45.09 rows=120 width=0) (actual time=1.210..1.210 rows=140 loops=1)
        Index Cond: (payload @> '{"feedback": {"flagged": true, "reason": "hallucination"}}'::jsonb)
Planning Time: 0.310 ms
Execution Time: 4.410 ms

Planner secara otomatis membuang partisi di luar Januari 2025. Selanjutnya, Bitmap Index Scan langsung menyasar tuple target melalui jsonb_path_ops, memangkas waktu eksekusi dari 1.421 ms menjadi 4,4 ms (peningkatan kecepatan hingga ~300x).

Rangkuman Strategi Desain

  1. Gunakan Range Partitioning berdasarkan timestamp log agar data historis mudah di-purge (cukup jalankan DROP TABLE per partisi lama) dan memperkecil ruang kerja scanning.
  2. Terapkan BRIN Index pada kolom urutan waktu jika tabel berukuran sangat besar untuk menghemat memori ketimbang B-Tree konvensional.
  3. Terapkan GIN Index dengan jsonb_path_ops untuk pemfilteran atribut bersarang pada JSONB, dan pastikan aplikasi menyusun query menggunakan operator @>, bukan ekstraksi teks ->>.