Sistem E2E testing CLI seperti TestSprite CLI menghasilkan volume log tinggi dalam interval singkat. Setiap langkah pengujian menulis status, payload, dan artefak ke basis data secara konkuren. Ketika tabel run log mencapai jutaan baris, operasi polling log via CLI mengalami degradasi performa drastis akibat sequential scan dan disk-based external sort.

Masalah utama terletak pada pembacaan log berdasarkan kombinasi filter execution_id, status pengujian, dan pengurutan timestamp. Artikel ini menyajikan resolusi bottleneck tersebut melalui skema DDL yang tepat, pembuatan compound index tertarget, dan transisi dari OFFSET/LIMIT ke keyset pagination.

Skema Database dan Karakteristik Beban Kerja

Tabel test_run_logs menampung baris log diskrit dari CLI runner. Berikut definisi skema DDL awal yang umum digunakan:

CREATE TABLE test_run_logs (
    id BIGSERIAL PRIMARY KEY,
    execution_id UUID NOT NULL,
    step_name VARCHAR(128) NOT NULL,
    status VARCHAR(32) NOT NULL,
    message TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Indeks standar bawaan
CREATE INDEX idx_logs_execution_id ON test_run_logs(execution_id);

Pada beban puluhan test suite yang berjalan bersamaan, CLI melakukan polling berkala menggunakan query berikut untuk mengambil batch log terbaru:

SELECT id, execution_id, step_name, status, message, created_at
FROM test_run_logs
WHERE execution_id = 'a4b1c2d3-e4f5-6789-0123-abcdef456789'
ORDER BY created_at DESC
LIMIT 50 OFFSET 10000;

Analisis Bottleneck dengan EXPLAIN ANALYZE

Meskipun kolom execution_id telah diindeks via B-Tree tunggal, PostgreSQL tetap harus membaca seluruh index pointer yang cocok, menarik baris dari heap memory, lalu menjalankan in-memory atau on-disk sort untuk mengurutkan berdasarkan created_at DESC.

Hasil profiling menggunakan EXPLAIN (ANALYZE, BUFFERS) pada tabel dengan 5.000.000 baris:

Limit  (cost=12543.20..12543.32 rows=50 width=142) (actual time=84.112..84.125 rows=50 loops=1)
  Buffers: shared hit=4210 read=1890
  ->  Sort  (cost=12518.20..12568.20 rows=20000 width=142) (actual time=82.410..83.520 rows=10050 loops=1)
        Sort Key: created_at DESC
        Sort Method: external merge  Disk: 3120kB
        Buffers: shared hit=4210 read=1890, temp read=390 written=392
        ->  Bitmap Heap Scan on test_run_logs  (cost=230.40..11200.10 rows=20000 width=142) (actual time=2.150..28.450 rows=20000 loops=1)
              Recheck Cond: (execution_id = 'a4b1c2d3-e4f5-6789-0123-abcdef456789'::uuid)
              Buffers: shared hit=820 read=1450
              ->  Bitmap Index Scan on idx_logs_execution_id  (cost=0.00..225.40 rows=20000 width=0) (actual time=1.820..1.821 rows=20000 loops=1)
                    Index Cond: (execution_id = 'a4b1c2d3-e4f5-6789-0123-abcdef456789'::uuid)
                    Buffers: shared hit=45
Planning Time: 0.185 ms
Execution Time: 85.240 ms

Terdapat dua masalah performa utama pada hasil profiling di atas:

  • Sort Spill to Disk: PostgreSQL menggunakan Sort Method: external merge pada temp disk karena data hasil filter melebihi work_mem.
  • Tingginya I/O Buffer: Query harus membaca 6.100 shared block buffers (kombinasi hit dan read) hanya untuk membuang 10.000 baris pertama via OFFSET.

Penerapan Compound Index

Untuk menghilangkan tahap pengurutan manual, buat compound index yang mencakup kolom equality filter dan kolom pengurutan sesuai arah order-nya:

CREATE INDEX idx_logs_execution_id_created_at_desc 
ON test_run_logs (execution_id, created_at DESC);

Indeks ini memanfaatkan struktur B-Tree multi-kolom. PostgreSQL akan mencari node execution_id target secara langsung, kemudian melintasi leaf node yang sudah tersortir menurut created_at DESC secara alami tanpa memicu operasi sort tambahan.

Eliminasi Offset Degradation dengan Keyset Pagination

Compound index belum sepenuhnya optimal jika aplikasi masih menggunakan OFFSET besar. Database tetap harus memindai dan mengabaikan baris OFFSET satu per satu. Solusinya adalah beralih ke Keyset Pagination (Cursor-based Pagination) menggunakan kombinasi tuple (created_at, id).

Kueri pagination dari TestSprite CLI diubah menjadi:

-- Ambil halaman berikutnya berdasarkan rekaman terakhir yang diterima CLI
SELECT id, execution_id, step_name, status, message, created_at
FROM test_run_logs
WHERE execution_id = 'a4b1c2d3-e4f5-6789-0123-abcdef456789'
  AND (created_at, id) < ('2026-03-30 08:14:22.102345+00'::timestamptz, 4120934)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Pastikan compound index diperbarui untuk mencakup kolom ID guna menjamin keunikan saat dua baris log memiliki timestamp identik:

CREATE INDEX idx_logs_execution_created_id 
ON test_run_logs (execution_id, created_at DESC, id DESC);

Verifikasi Hasil dan Perbandingan Buffer I/O

Pengujian ulang kueri keyset pagination dengan compound index baru menghasilkan eksekusi berikut:

Limit  (cost=0.56..8.45 rows=50 width=142) (actual time=0.045..0.082 rows=50 loops=1)
  Buffers: shared hit=6
  ->  Index Scan using idx_logs_execution_created_id on test_run_logs  (cost=0.56..3124.00 rows=19800 width=142) (actual time=0.044..0.078 rows=50 loops=1)
        Index Cond: ((execution_id = 'a4b1c2d3-e4f5-6789-0123-abcdef456789'::uuid) AND (ROW(created_at, id) < ROW('2026-03-30 08:14:22.102345+00'::timestamptz, 4120934)))
Planning Time: 0.120 ms
Execution Time: 0.098 ms

Tabel Perbandingan Efisiensi

  • Waktu Eksekusi: Turun dari 85.240 ms menjadi 0.098 ms (>800x lebih cepat).
  • Shared Buffers (I/O Hit + Read): Berkurang dari 6.100 blok menjadi hanya 6 blok memori.
  • Penggunaan Temp Disk: Turun dari 3.120 kB ke 0 kB (tidak ada external disk sort).

Optimasi ini menjaga respon streaming log pada CLI tetap stabil di bawah 1 milidetik terlepas dari seberapa besar ukuran data historis pada tabel test_run_logs.