Arsitektur autonomous AI agent mengeksekusi instruksi dalam siklus berulang: thought, tool calling, observation, dan reflection. Engine harness seperti peerd atau LangGraph mencatat setiap siklus ke tabel audit trail database relasional. Pada sistem produksi, ribuan eksekusi agent dapat menghasilkan puluhan juta baris data dengan cepat.

Masalah performa muncul saat tim engineer atau dashboard monitoring melakukan audit trail querying. Penggunaan query pagination tradisional berbasis OFFSET memicu pembacaan disk masif dan fallback ke Sequential Scan. Artikel ini menyajikan solusi teknis untuk mengoptimasi query log trajectory menggunakan composite index, keyset pagination, dan table partitioning di PostgreSQL.

Akar Masalah: Mengapa OFFSET Memicu Degenerasi I/O

Pola pagination standar pada SQL API umumnya berbentuk:

SELECT * FROM agent_steps 
WHERE session_id = 'a1b2c3d4-e5f6-7a8b-9c0d-1e2f3a4b5c6d' 
ORDER BY step_number DESC 
LIMIT 20 OFFSET 50000;

Database tidak dapat langsung melompat ke baris ke-50.001. PostgreSQL harus mengevaluasi kriteria pencarian, mengurutkan data, membaca 50.020 baris pertama ke dalam buffer memory, lalu membuang 50.000 baris pertama hanya untuk mengembalikan 20 baris terakhir.

Ketika rasio data yang harus dilewati (offset) meningkat melebihi kapasitas memori kerja (work_mem) atau saat index yang ada tidak menutupi predikat sortir, query planner menganggap pembacaan index acak lebih mahal daripada pembacaan sekuensial penuh. Akibatnya, query planner memilih Seq Scan, memicu I/O thrashing dan saturasi CPU.

Desain Skema DDL: agent_steps

Struktur DDL PostgreSQL yang dirancang untuk trajectory logging agent membutuhkan identifikasi sesi yang jelas dan nomor langkah berurutan secara deterministik:

CREATE TABLE agent_steps (
    id BIGSERIAL PRIMARY KEY,
    session_id UUID NOT NULL,
    step_number INT NOT NULL,
    step_type VARCHAR(32) NOT NULL, -- e.g. 'thought', 'tool_call', 'observation'
    tool_name VARCHAR(64),
    payload JSONB NOT NULL DEFAULT '{}'::jsonb,
    prompt_tokens INT DEFAULT 0,
    completion_tokens INT DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_session_step UNIQUE (session_id, step_number)
);

Analisis Plan: Baseline Query dengan OFFSET

Berikut hasil analisis query deep pagination menggunakan EXPLAIN (ANALYZE, BUFFERS) tanpa composite index yang tepat pada tabel berisi 2.000.000 baris:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM agent_steps
WHERE session_id = 'a1b2c3d4-e5f6-7a8b-9c0d-1e2f3a4b5c6d'
ORDER BY step_number DESC
LIMIT 20 OFFSET 50000;

Hasil execution plan:

Limit  (cost=45812.00..45812.05 rows=20 width=312) (actual time=142.110..142.115 rows=20 loops=1)
  Buffers: shared hit=1820 read=12400
  ->  Sort  (cost=45687.00..45837.00 rows=60000 width=312) (actual time=138.410..141.220 rows=50020 loops=1)
        Sort Key: step_number DESC
        Sort Method: top-N heapsort  Memory: 38kB
        Buffers: shared hit=1820 read=12400
        ->  Seq Scan on agent_steps  (cost=0.00..42500.00 rows=60000 width=312) (actual time=0.045..98.420 rows=60000 loops=1)
              Filter: (session_id = 'a1b2c3d4-e5f6-7a8b-9c0d-1e2f3a4b5c6d'::uuid)
              Rows Removed by Filter: 1940000
              Buffers: shared hit=1820 read=12400
Planning Time: 0.150 ms
Execution Time: 142.180 ms

Diagnosa: Query planner melakukan pembacaan sekuensial pada 2.000.000 baris untuk memfilter data berdasarkan session_id. Operasi ini menghasilkan 12.400 blok pembacaan disk fisik (read) dengan waktu eksekusi 142 ms.

Solusi: Keyset Pagination dengan Composite Index

Untuk meniadakan beban operasi OFFSET, terapkan Keyset Pagination (Cursor Pagination) menggunakan penanda titik data terakhir yang dilihat klien (dalam kasus ini: step_number).

1. Buat Composite B-Tree Index

CREATE INDEX idx_agent_steps_session_step 
ON agent_steps (session_id, step_number DESC);

Indeks komposit ini mengorganisir data secara terurut pada disk B-Tree: pertama berdasarkan kecocokan mutlak session_id, lalu secara fisik terurut menurun (DESC) berdasarkan step_number.

2. Implementasi Keyset Query

Daripada menggunakan pergeseran baris (OFFSET), gunakan klausa WHERE untuk menyaring langsung ke titik data berikutnya:

SELECT * FROM agent_steps
WHERE session_id = 'a1b2c3d4-e5f6-7a8b-9c0d-1e2f3a4b5c6d'
  AND step_number < 1000 -- Cursor nilai step_number dari record terakhir halaman sebelumnya
ORDER BY step_number DESC
LIMIT 20;

3. Hasil EXPLAIN (ANALYZE, BUFFERS) Setelah Optimasi

Limit  (cost=0.43..2.85 rows=20 width=312) (actual time=0.038..0.054 rows=20 loops=1)
  Buffers: shared hit=4
  ->  Index Scan using idx_agent_steps_session_step on agent_steps  (cost=0.43..1210.50 rows=10000 width=312) (actual time=0.036..0.050 rows=20 loops=1)
        Index Cond: ((session_id = 'a1b2c3d4-e5f6-7a8b-9c0d-1e2f3a4b5c6d'::uuid) AND (step_number < 1000))
        Buffers: shared hit=4
Planning Time: 0.082 ms
Execution Time: 0.075 ms

Hasil:

  • Waktu eksekusi turun drastis dari 142.180 ms menjadi 0.075 ms (>1800x lebih cepat).
  • Disk buffer reads berkurang dari 14.220 pages menjadi hanya 4 pages yang diambil langsung dari RAM cache (shared hit=4, read=0).
  • Kompleksitas pencarian berubah dari O(N) menjadi O(log N).

Manajemen Data Growth: Range Partitioning

Trajectory agent beroperasi write-heavy. Seiring bertambahnya volume data, ukuran file index pada disk akan melampaui memori shared_buffers, menyebabkan cache evictions. Terapkan PostgreSQL Declarative Partitioning berdasarkan rentang waktu created_at.

Implementasi DDL Partitioning

-- Buat master table dengan partisi rentang tanggal
CREATE TABLE agent_steps_partitioned (
    id BIGSERIAL,
    session_id UUID NOT NULL,
    step_number INT NOT NULL,
    step_type VARCHAR(32) NOT NULL,
    tool_name VARCHAR(64),
    payload JSONB NOT NULL DEFAULT '{}'::jsonb,
    prompt_tokens INT DEFAULT 0,
    completion_tokens INT DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (created_at, session_id, step_number)
) PARTITION BY RANGE (created_at);

-- Composite index otomatis diterapkan ke semua partisi turunan
CREATE INDEX idx_part_session_step 
ON agent_steps_partitioned (session_id, step_number DESC);

-- Partisi bulanan
CREATE TABLE agent_steps_y2026m03 PARTITION OF agent_steps_partitioned
    FOR VALUES FROM ('2026-03-01 00:00:00+00') TO ('2026-04-01 00:00:00+00');

CREATE TABLE agent_steps_y2026m04 PARTITION OF agent_steps_partitioned
    FOR VALUES FROM ('2026-04-01 00:00:00+00') TO ('2026-05-01 00:00:00+00');

Keuntungan Operasional

  • Partition Pruning: Query yang menyertakan rentang created_at hanya akan memindai partisi yang relevan, mengabaikan partisi lama secara total.
  • Instant Data Purging: Menghapus trajectory lama tidak lagi memerlukan DELETE FROM yang memicu overhead WAL dan bloat table. Cukup jalankan perintah DDL cepat: DROP TABLE agent_steps_y2026m03;.

Batasan dan Pertimbangan Arsitektur

  • No Arbitrary Page Jumping: Keyset pagination tidak mendukung fungsionalitas melompat langsung ke halaman acak (misalnya, loncat ke halaman 40). UI harus disesuaikan menggunakan pola infinite scroll atau tombol navigasi Next / Previous.
  • Kandidat Kolom Cursor: Kolom yang digunakan sebagai cursor harus memiliki urutan deterministik dan nilai unik. Kombinasi (session_id, step_number) adalah kandidat ideal karena memiliki integritas urutan yang terisolasi per sesi.