Tantangan Skalabilitas Data Traces pada Relational Engine

Data trace dan log dari OpenTelemetry (OTel) memiliki dua karakteristik utama: throughput tulis berbasis deret waktu yang tinggi dan skema metadata dinamis (atribut span). Menggunakan PostgreSQL sebagai storage engine backend sering memicu bottleneck performa seiring membesarnya ukuran tabel. Query pencarian root span atau filtering log pada service tertentu umumnya mengalami degradasi akibat sequential scan pada tabel puluhan gigabyte dan overhead I/O saat membaca data historis.

Untuk menjaga query latency tetap stabil pada angka milidetik, rancangan skema database memerlukan dua fondasi: declarative range partitioning berbasis rentang waktu dan konfigurasi indeks yang tepat untuk memisahkan indexing kolom terstruktur dari payload semi-terstruktur (JSONB).

Skema Tabel: Range Partitioning dan Composite B-Tree

PostgreSQL declarative partitioning membagi tabel besar menjadi partisi fisik yang lebih kecil. Database engine mengeksekusi partition pruning, yaitu mengeliminasi pembacaan disk blok pada partisi di luar cakupan rentang timestamp query.

CREATE TABLE spans (
    trace_id VARCHAR(32) NOT NULL,
    span_id VARCHAR(16) NOT NULL,
    parent_span_id VARCHAR(16),
    service_name VARCHAR(100) NOT NULL,
    span_name VARCHAR(255) NOT NULL,
    start_time TIMESTAMPTZ NOT NULL,
    duration_ms DOUBLE PRECISION NOT NULL,
    status_code VARCHAR(20),
    attributes JSONB NOT NULL DEFAULT '{}'::jsonb,
    PRIMARY KEY (start_time, trace_id, span_id)
) PARTITION BY RANGE (start_time);

-- Contoh partisi harian
CREATE TABLE spans_y2026m03d29 PARTITION OF spans
    FOR VALUES FROM ('2026-03-29 00:00:00+00') TO ('2026-03-30 00:00:00+00');

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

Filter query trace umumnya menyertakan service_name dan diurutkan berdasarkan waktu terbaru. Buat composite B-Tree index secara eksplisit pada master table agar secara otomatis diterapkan ke setiap partisi turunan:

CREATE INDEX idx_spans_service_time 
ON spans (service_name, start_time DESC);

Optimasi Metadata Fleksibel Menggunakan GIN Index

Kolom attributes menampung konteks dinamis OpenTelemetry (misalnya gen_ai.system, http.status_code, atau client.id). Indeks B-Tree standar tidak dapat mengevaluasi operator key-value dinamis di dalam JSONB secara efisien. Solusinya adalah Generalized Inverted Index (GIN).

Gunakan operator class jsonb_path_ops alih-alih operator default. Operator class ini mengabaikan indexing key yang berdiri sendiri dan hanya menghash pasangan path dan value. Hasilnya, ukuran indeks menjadi lebih kecil hingga 40-50% dan query containment (@>) berjalan lebih cepat.

CREATE INDEX idx_spans_attributes_path_ops 
ON spans USING GIN (attributes jsonb_path_ops);
Trade-off: jsonb_path_ops hanya mendukung operator containment (@>). Jika aplikasi membutuhkan operator pengecekan keberadaan key tunggal (?, ?|, ?&), gunakan default operator class jsonb_ops dengan konsekuensi ukuran indeks yang lebih besar pada disk.

Analisis Eksekusi Query: EXPLAIN ANALYZE

Skenario pengujian: Mengambil 50 trace terbaru dari service ai-agent-orchestrator yang mengalami eksekusi LLM berulang (atribut gen_ai.system = 'ollama') dalam rentang 1 jam terakhir.

EXPLAIN ANALYZE
SELECT trace_id, span_id, span_name, duration_ms, attributes
FROM spans
WHERE start_time >= '2026-03-30 10:00:00+00' 
  AND start_time < '2026-03-30 11:00:00+00'
  AND service_name = 'ai-agent-orchestrator'
  AND attributes @> '{"gen_ai": {"system": "ollama"}}'
ORDER BY start_time DESC
LIMIT 50;

Sebelum Optimasi (Tanpa Partitioning & Default B-Tree)

Limit (cost=85420.10..85420.22 rows=50 width=210) (actual time=1420.512..1420.531 rows=50 loops=1)
  -> Sort (cost=85420.10..85425.00 rows=1960 width=210) (actual time=1420.510..1420.522 rows=50 loops=1)
        Sort Key: start_time DESC
        -> Gather (cost=1000.00..85315.40 rows=1960 width=210) (actual time=48.210..1412.300 rows=2100 loops=1)
              Workers Planned: 2
              Workers Launched: 2
              -> Parallel Seq Scan on spans (cost=0.00..84119.40 rows=817 width=210) (actual time=45.102..1405.110 rows=700 loops=3)
                    Filter: ((start_time >= '2026-03-30 10:00:00+00') AND (start_time < '2026-03-30 11:00:00+00') AND ((service_name)::text = 'ai-agent-orchestrator'::text) AND (attributes @> '{"gen_ai": {"system": "ollama"}}'::jsonb))
Planning Time: 0.185 ms
Execution Time: 1421.105 ms

Sesudah Optimasi (Partitioning + B-Tree + GIN jsonb_path_ops)

Limit (cost=12.45..156.80 rows=50 width=210) (actual time=1.840..2.110 rows=50 loops=1)
  -> Bitmap Heap Scan on spans_y2026m03d30 (cost=12.45..540.20 rows=187 width=210) (actual time=1.838..2.095 rows=50 loops=1)
        Recheck Cond: (attributes @> '{"gen_ai": {"system": "ollama"}}'::jsonb)
        Filter: ((start_time >= '2026-03-30 10:00:00+00') AND (start_time < '2026-03-30 11:00:00+00') AND ((service_name)::text = 'ai-agent-orchestrator'::text))
        -> BitmapAnd (cost=12.45..12.45 rows=187 width=0) (actual time=1.810..1.810 rows=0 loops=1)
              -> Bitmap Index Scan on idx_spans_attributes_path_ops (cost=0.00..5.10 rows=400 width=0) (actual time=0.915..0.915 rows=420 loops=1)
                    Index Cond: (attributes @> '{"gen_ai": {"system": "ollama"}}'::jsonb)
              -> Bitmap Index Scan on idx_spans_service_time (cost=0.00..7.12 rows=850 width=0) (actual time=0.880..0.880 rows=910 loops=1)
                    Index Cond: (((service_name)::text = 'ai-agent-orchestrator'::text) AND (start_time >= '2026-03-30 10:00:00+00') AND (start_time < '2026-03-30 11:00:00+00'))
Planning Time: 0.412 ms
Execution Time: 2.185 ms

Database engine melakukan Partition Pruning secara instan ke partisi spans_y2026m03d30, menggabungkan hasil pencarian GIN index dan B-Tree index via BitmapAnd, dan memangkas runtime dari 1421 ms ke 2.1 ms.

Manajemen Retensi Data Tanpa Locking

Menjalankan DELETE FROM spans WHERE start_time < NOW() - INTERVAL '30 days' pada tabel bervolume tinggi memicu I/O spike masif, bloat tabel, dan row-level lock berkepanjangan yang mengganggu ingestion pipeline trace baru.

Dengan time partitioning, retensi data dilakukan pada level DDL metadata melalui decoupling partisi:

-- 1. Lepas partisi dari tabel utama (membutuhkan AccessExclusiveLock singkat pada partisi terkait saja)
ALTER TABLE spans DETACH PARTITION spans_y2026m03d01 CONCURRENTLY;

-- 2. Hapus partisi lama tanpa membebani tabel aktif
DROP TABLE spans_y2026m03d01;

Perintah DETACH PARTITION CONCURRENTLY menghindari lock berkepanjangan pada master table, sehingga proses INSERT trace dari OpenTelemetry Collector tetap berjalan tanpa terinterupsi.