Fitur live search pada data table aplikasi berbasis Inertia.js sering kali memicu degradasi performa database. Pola interaksi reaktif yang mengirimkan HTTP request pada setiap keystroke menghasilkan lonjakan query konkuren ke backend. Jika backend mengeksekusi filter pencarian teks berbasis wildcard dengan operator ILIKE '%keyword%', PostgreSQL secara default memintas indeks B-tree konvensional dan menjalankan Sequential Scan (Seq Scan). Akibatnya, penggunaan CPU database melonjak hingga 100%, disk I/O tercekik, dan latensi respons props Inertia meningkat drastis.

Akar Masalah: Wildcard ILIKE dan Keterbatasan Indeks B-tree

Indeks standar PostgreSQL menggunakan struktur data B-tree. B-tree dirancang untuk operasi perbandingan sekuensial terurut seperti =, <, >, atau pencarian awalan (prefix matching) seperti LIKE 'keyword%'. Ketika query menggunakan karakter wildcard di awal pola (leading wildcard), contohnya ILIKE '%keyword%', engine database tidak dapat menentukan titik awal traversal pada pohon indeks.

Kondisi ini memaksa PostgreSQL beralih ke Sequential Scan, yaitu membaca setiap block dan tuple tabel secara berurutan langsung dari disk atau shared buffers. Pada tabel berskala ratusan ribu hingga jutaan baris, operasi ini menghabiskan throughput I/O secara masif dan memperlambat antrean request lainnya.

Audit Eksekusi Query Menggunakan EXPLAIN (ANALYZE, BUFFERS)

Langkah pertama identifikasi adalah menganalisis execution plan query pencarian langsung di PostgreSQL menggunakan perintah EXPLAIN (ANALYZE, BUFFERS):

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, name, email, created_at
FROM users
WHERE name ILIKE '%pratama%';

Hasil eksekusi pada tabel tanpa indeks trigram menunjukkan pola berikut:

Seq Scan on users  (cost=0.00..24850.00 rows=1050 width=88) (actual time=0.045..98.321 rows=1200 loops=1)
  Filter: (name ~~* '%pratama%'::text)
  Rows Removed by Filter: 998800
  Buffers: shared hit=12350
Planning Time: 0.125 ms
Execution Time: 98.450 ms

Metrik kritis yang perlu diperhatikan:

  • Node Type: Seq Scan menandakan pemindaian menyeluruh ke seluruh baris tabel.
  • Rows Removed by Filter: 998.800 baris dibaca dan dibuang, menghasilkan pemborosan komputasi CPU.
  • Buffers: shared hit=12350 berarti ada ribuan buffer page (setara ~96 MB) yang di-scan hanya untuk satu query live search.
  • Execution Time: 98.45 ms untuk satu eksekusi. Di bawah beban 50 request konkuren, throughput aplikasi akan ambruk.

Implementasi Ekstensi pg_trgm dan Indeks GIN

Solusi teknis untuk mendukung substring matching pada PostgreSQL adalah memanfaatkan ekstensi pg_trgm (trigram) bersama Generalized Inverted Index (GIN). Trigram memecah string menjadi irisan 3 karakter berturut-turut, sedangkan indeks GIN memetakan setiap trigram ke baris tabel terkait.

1. Aktifkan Ekstensi pg_trgm

Jalankan perintah SQL berikut atau bungkus ke dalam migration file framework:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

2. Buat Indeks GIN pada Kolom Target

Gunakan operator class gin_trgm_ops saat membuat indeks. Gunakan opsi CONCURRENTLY di production untuk menghindari locking tabel:

CREATE INDEX CONCURRENTLY idx_users_name_trgm_gin
ON users USING gin (name gin_trgm_ops);

Catatan: Indeks GiST (Generalized Search Tree) juga dapat digunakan dengan gist_trgm_ops. GiST membutuhkan alokasi disk lebih kecil dan waktu update lebih cepat, tetapi performa pembacaan GIN lebih superior untuk skenario live search teks berskala besar.

Penyesuaian Query Builder di Sisi Backend

Pastikan query builder backend menghasilkan operator yang didukung oleh gin_trgm_ops. Operator LIKE, ILIKE, dan operator similarity % secara otomatis didukung oleh indeks trigram.

Selain indeks, pasang guard clause berupa validasi panjang karakter minimum di backend controller. Query dengan 1–2 karakter menghasilkan selectivity yang sangat rendah dan tetap membebani indeks:

// App/Http/Controllers/UserController.php (Laravel / Inertia Stack)
public function index(Request $request)
{
    $search = $request->input('search');

    $users = User::query()
        ->select(['id', 'name', 'email', 'created_at'])
        ->when($search && mb_strlen($search) >= 3, function ($query) use ($search) {
            $query->where('name', 'ILIKE', "%{$search}%");
        })
        ->paginate(15)
        ->withQueryString();

    return inertia('Users/Index', [
        'users' => $users,
        'filters' => ['search' => $search],
    ]);
}

Proteksi Sisi Frontend: Debounce dan Partial Reload Inertia

Optimasi database harus dibarengi dengan pengendalian frekuensi request di frontend. Terapkan mekanisme debounce dan manfaatkan fitur partial reload Inertia untuk meminimalkan beban komputasi server.

// Script implementasi di Vue 3 / Inertia.js
import { ref, watch } from 'vue';
import { router } from '@inertiajs/vue3';
import debounce from 'lodash/debounce';

const props = defineProps({
    users: Object,
    filters: Object,
});

const search = ref(props.filters.search || '');

const handleSearch = debounce((value) => {
    // Cegah request jika panjang karakter di bawah batas minimum (kecuali kosong/reset)
    if (value.length > 0 && value.length < 3) {
        return;
    }

    router.get(
        route('users.index'),
        { search: value },
        {
            preserveState: true,
            replace: true,
            only: ['users'], // Partial reload: hanya minta props users
        }
    );
}, 300); // Tunda eksekusi selama 300ms dari keystroke terakhir

watch(search, (val) => handleSearch(val));
  • preserveState: true: Menjaga state komponen lokal tetap utuh tanpa reset UI.
  • replace: true: Menghindari penambahan riwayat URL berlebih pada browser history stack.
  • only: ['users']: Mencegah eksekusi props lain yang berat di controller, menghemat query backend yang tidak diperlukan.

Evaluasi Kinerja: Metrik Sebelum vs Sesudah Optimasi

Jalankan ulang EXPLAIN (ANALYZE, BUFFERS) setelah indeks GIN aktif:

Bitmap Heap Scan on users  (cost=12.25..1250.30 rows=1050 width=88) (actual time=1.850..2.920 rows=1200 loops=1)
  Recheck Cond: (name ~~* '%pratama%'::text)
  Buffers: shared hit=480
  ->  Bitmap Index Scan on idx_users_name_trgm_gin  (cost=0.00..11.98 rows=1050 width=0) (actual time=1.620..1.620 rows=1200 loops=1)
        Index Cond: (name ~~* '%pratama%'::text)
        Buffers: shared hit=28
Planning Time: 0.180 ms
Execution Time: 3.010 ms

Perbandingan performa pada dataset 1.000.000 baris:

  • Metode Akses: Berubah dari Seq Scan menjadi Bitmap Index Scan dilanjutkan dengan Bitmap Heap Scan.
  • Waktu Eksekusi SQL: Turun dari 98.45 ms menjadi 3.01 ms (peningkatan kecepatan ~32x).
  • Buffer I/O Reads: Turun dari 12.350 pages menjadi 508 pages (pengurangan konsumsi memori/disk sebesar ~95%).
  • Latensi HTTP Inertia: Respons prop round-trip turun drastis, menghilangkan lag antarmuka pengguna pada saat mengetik di input live search.

Trade-offs dan Pertimbangan Maintenance

Penerapan GIN trigram index memberikan akselerasi drastis pada query baca, namun memiliki konsekuensi teknis yang harus dikelola:

  • Overhead Tulis (Write Amplification): Operasi INSERT, UPDATE, dan DELETE pada kolom berindeks GIN memerlukan kalkulasi ulang dan pembaruan struktur trigram. Ini menurunkan throughput batch insert.
  • Ukuran Disk: Ukuran indeks GIN trigram sering kali mencapai 50% hingga 100% dari ukuran data kolom itu sendiri. Pastikan kapasitas disk server mencukupi.
  • GIN Fastupdate & Autovacuum: Secara default, PostgreSQL menggunakan parameter fastupdate = on untuk menunda pembaruan indeks GIN ke pending list. Pastikan proses autovacuum berjalan optimal agar pending list tidak membengkak dan memperlambat waktu pencarian.