Pipeline inferensi kode berbasis Abstract Syntax Tree (AST) Jacquard menghasilkan ribuan entri validasi sintaks dan keamanan per menit ke tabel antrean. Pada arsitektur penjaminan mutu kode buatan model AI, bottleneck performa umum terjadi ketika tabel code_reviews bertambah masif, sementara antrean review manusia membutuhkan kueri berlatensi rendah untuk mengambil item bertarget status = 'pending' secara berurutan.

Ketika data historis mencapai puluhan juta baris, pendekatan pagination berbasis OFFSET standar dan indeks B-tree non-kondisional menyebabkan degradasi I/O berat. Artikel ini membedah mitigasi degradasi tersebut menggunakan Partial Index, Keyset Pagination, dan Autovacuum Tuning pada PostgreSQL.

Problem: Bottleneck Sequential Scan dan Disk Sort

Tabel code_reviews menyimpan representasi AST, metadata model AI, dan status verifikasi manual. Skema dasar umumnya terstruktur sebagai berikut:

CREATE TABLE code_reviews (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ast_hash VARCHAR(64) NOT NULL,
    code_snippet TEXT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending', -- pending, approved, rejected
    created_at TIMESTAMPTZ NOT NULL DEFAULT CLOCK_TIMESTAMP(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CLOCK_TIMESTAMP()
);

Kueri umum yang dieksekusi aplikasi antrean review manusia adalah mengambil 20 item pending berikutnya dengan urutan pembuatan:

SELECT id, ast_hash, code_snippet, created_at
FROM code_reviews
WHERE status = 'pending'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 50000;

Ketika dianalisis menggunakan EXPLAIN (ANALYZE, BUFFERS), rencana eksekusi memperlihatkan dua masalah struktural:

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Limit  (cost=14205.12..14210.82 rows=20 width=128) (actual time=182.412..182.435 rows=20 loops=1)
  Buffers: shared hit=8420 read=14532, temp read=2100 written=2105
  ->  Sort  (cost=14190.87..14245.22 rows=21740 width=128) (actual time=178.112..181.201 rows=50020 loops=1)
        Sort Key: created_at DESC, id DESC
        Sort Method: external merge  Disk: 16840kB
        Buffers: shared hit=8420 read=14532, temp read=2100 written=2105
        ->  Bitmap Heap Scan on code_reviews  (cost=420.15..12450.30 rows=21740 width=128) (actual time=12.450..95.310 rows=65000 loops=1)
              Recheck Cond: ((status)::text = 'pending'::text)
              Buffers: shared hit=410 read=14532
              ->  Bitmap Index Scan on idx_code_reviews_status  (cost=0.00..414.71 rows=21740 width=0) (actual time=8.110..8.110 rows=65000 loops=1)
                    Index Cond: ((status)::text = 'pending'::text)
                    Buffers: shared hit=410
Planning Time: 0.185 ms
Execution Time: 184.215 ms

Dua titik kegagalan utama:

  1. External Merge Disk Sort: Memori work_mem tidak mencukupi untuk menampung seluruh baris pending yang harus diurutkan sebelum OFFSET diterapkan. PostgreSQL terpaksa melakukan tumpahan data ke disk (temp files), menyebabkan lonjakan latency dan overhead I/O tinggi.
  2. Wasteful Block Reads pada OFFSET: Engine harus membaca, memvalidasi, dan mengabaikan 50.000 baris pertama sebelum mengembalikan 20 baris target. Biaya I/O bertambah secara linier seiring bertambahnya nilai offset (O(N)).

Solusi 1: Partial Index untuk Data Skew

Pada pipeline pemrosesan antrean, distribusi data sangat timpang (skewed). Baris berstatus pending biasanya hanya mencakup < 5% dari total tabel, sedangkan 95% sisanya berstatus approved atau rejected.

Indeks B-Tree penuh pada kolom status atau (status, created_at) memboroskan ruang penyimpanan dan buffer cache karena mencatat puluhan juta baris data historis yang tidak akan pernah diakses oleh kueri antrean kerja. Gunakan Partial Index:

CREATE INDEX idx_code_reviews_pending_seek 
ON code_reviews (created_at DESC, id DESC) 
WHERE status = 'pending';

Keunggulan teknis indeks ini:

  • Ukuran Kompak: Ukuran indeks hanya sebanding dengan jumlah baris yang pending. Seluruh indeks dapat menetap di shared_buffers (RAM).
  • Pre-sorted: Indeks telah terurut secara fisik menurut created_at DESC, id DESC, mengeliminasi kebutuhan operasi Sort oleh query planner.
  • Predikat Otomatis: PostgreSQL mengenali klausa WHERE status = 'pending' dan langsung memilih indeks ini tanpa scan ke partisi status lain.

Solusi 2: Keyset Pagination (Cursor Seek)

Untuk meniadakan operasi pemindaian baris yang dibuang oleh OFFSET, ubah pagination ke model keyset (seek method). Alih-alih melompati baris, kueri mencari titik akhir dari pemanggilan sebelumnya menggunakan deterministik tuple comparison:

-- Kueri Awal (Halaman Pertama)
SELECT id, ast_hash, code_snippet, created_at
FROM code_reviews
WHERE status = 'pending'
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Kueri Halaman Berikutnya (Menggunakan tuple cursor dari baris terakhir halaman sebelumnya)
SELECT id, ast_hash, code_snippet, created_at
FROM code_reviews
WHERE status = 'pending'
  AND (created_at, id) < ($last_created_at, $last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Evaluasi (created_at, id) < ($last_created_at, $last_id) dipetakan langsung oleh B-tree partial index sebagai operasi traversal O(log N). Engine database langsung melompat ke daun indeks target dan mengambil 20 baris berikutnya secara berurutan tanpa membaca satu pun baris redundan.

Evaluasi Rencana Eksekusi: Sebelum vs Sesudah

Jalankan EXPLAIN (ANALYZE, BUFFERS) pada kueri keyset yang dipadukan dengan partial index:

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Limit  (cost=0.42..3.12 rows=20 width=128) (actual time=0.035..0.052 rows=20 loops=1)
  Buffers: shared hit=6
  ->  Index Scan using idx_code_reviews_pending_seek on code_reviews  (cost=0.42..2854.12 rows=21140 width=128) (actual time=0.034..0.049 rows=20 loops=1)
        Index Cond: (((created_at)::timestamp with time zone < '2024-03-30 10:15:00+00'::timestamptz) OR (((created_at)::timestamp with time zone = '2024-03-30 10:15:00+00'::timestamptz) AND (id < 452190)))
        Buffers: shared hit=6
Planning Time: 0.112 ms
Execution Time: 0.071 ms

Perbandingan metrik performa:

  • Buffer Reads: Turun dari ~22.952 blok (kombinasi disk, hit, dan temp files) menjadi hanya 6 buffer hits di memori.
  • Disk Spill: Dari 16.840 kB temporary disk write menjadi 0 kB (sortir dieliminasi total).
  • Execution Time: Menurun dari ~184 ms menjadi ~0.07 ms (reduksi latensi > 99.9%).

Mitigasi Index Bloat & Autovacuum Tuning

Pola kerja antrean memicu intensitas operasi UPDATE yang tinggi saat reviewer mengubah status = 'pending' menjadi 'approved' atau 'rejected'. Pada tabel dengan partial index, karakteristik mutasi baris ini memiliki implikasi spesifik terhadap MVCC (Multi-Version Concurrency Control):

  1. Siklus Hidup Partial Index: Ketika baris diubah dari pending ke status lain, penunjuk baris dihapus dari partial index. Namun, dead tuple tetap tertinggal di tabel utama (heap).
  2. Table Bloat: Jika dead tuple tidak dibersihkan secara agresif, konkurensi query scan heap untuk data non-index tuple akan melambat.

Skema default autovacuum di PostgreSQL terlalu pasif untuk tabel antrean dinamis. Sesuaikan parameter autovacuum khusus untuk tabel code_reviews:

ALTER TABLE code_reviews SET (
    autovacuum_vacuum_scale_factor = 0.02,  -- Picu vacuum setiap ada 2% perubahan data (default: 20%)
    autovacuum_vacuum_threshold = 1000,     -- Minimum baris mati sebelum vacuum aktif
    autovacuum_vacuum_cost_limit = 2000,    -- Tingkatkan alokasi I/O vacuum per putaran
    fillfactor = 85                         -- Sisakan 15% ruang kosong per page untuk HOT updates
);

Catatan HOT Update: Mengubah nilai kolom status yang terikat pada predikat partial index akan mendiskualifikasi pembaruan tersebut dari Heap-Only Tuple (HOT) update untuk indeks tersebut. Namun, menetapkan fillfactor = 85 tetap melindungi indeks utama lainnya (seperti PRIMARY KEY) dari bloat ketika kolom lain seperti updated_at ikut dimodifikasi.

Kesimpulan Implementasi

Mengelola antrean human-in-the-loop berkecepatan tinggi pada PostgreSQL membutuhkan pemisahan tegas antara data operasional aktif dan data historis. Menghindari full B-tree, meniadakan OFFSET lewat keyset cursor, serta memperketat ambang batas autovacuum menjamin latensi akses tetap konsisten di bawah 1 milidetik terlepas dari seberapa besar volume kode AI yang di-ingest ke sistem.