Audit Query Metadata AI: Index, LIKE, dan Pagination di SQL

Audit Query Metadata AI penting ketika fitur AI di backend mulai menyimpan banyak teks dan metadata: prompt, hasil ekstraksi, ringkasan, tag, label klasifikasi, nama dokumen, bahasa sumber, confidence score, hingga status proses. Pada skala kecil, semuanya tampak baik. Namun saat tabel bertambah besar, halaman listing, pencarian, dan endpoint admin sering menjadi lambat karena query yang dulu masih murah kini memindai terlalu banyak baris.

Masalah utamanya biasanya bukan di model AI, fine-tuning, atau proses translasi itu sendiri, melainkan pada cara aplikasi menyimpan dan mengambil metadata hasil AI. Query dengan LIKE/ILIKE, index yang tidak sesuai urutan filter, pagination berbasis OFFSET, dan COUNT(*) yang mahal adalah pola yang paling sering muncul. Artikel ini fokus pada audit praktis untuk kasus backend yang menyimpan data turunan AI dan makin lambat seiring pertumbuhan data.

Konteks: fitur AI menambah beban query metadata

Dalam sistem yang terinspirasi dari alur fine-tuning atau translasi model, aplikasi biasanya tidak hanya menyimpan file asli, tetapi juga banyak artefak turunan. Contohnya:

prompt yang dikirim ke model,
hasil ekstraksi seperti entitas, kategori, ringkasan, atau terjemahan,
tag yang dipakai untuk filter dan pencarian,
metadata dokumen seperti bahasa, sumber, status review, project_id, tenant_id, created_at, updated_at,
audit log untuk debugging dan evaluasi hasil model.

Masalah muncul ketika kebutuhan produk berkembang:

admin ingin mencari dokumen berdasarkan potongan teks hasil AI,
user ingin memfilter per project, status, bahasa, dan tag,
dashboard menampilkan total hasil per filter,
API listing memakai sorting terbaru dengan halaman yang bisa lompat jauh.

Secara fungsional semua masuk akal. Secara performa, kombinasi ini mudah berubah menjadi query berat jika tidak diaudit.

Gejala nyata yang perlu dicurigai

Sebelum masuk ke tuning, kenali dulu gejalanya. Pada sistem metadata AI, tanda-tanda berikut sering menunjukkan masalah ada di query:

halaman daftar dokumen lambat hanya pada tenant atau project yang datanya besar,
search endpoint cepat untuk kata tertentu, tetapi sangat lambat untuk kata umum,
CPU database naik saat admin membuka halaman dengan filter kompleks,
latensi memburuk pada page 100 atau page 1000,
query timeout muncul setelah fitur pencarian bebas ditambahkan,
replica ikut sibuk karena listing dan count dipanggil terlalu sering,
aplikasi terasa lambat walau inference model berjalan async.

Kesalahan umum adalah menyalahkan proses AI lebih dulu. Padahal inference sudah selesai, tetapi hasilnya disimpan dalam skema dan pola query yang tidak efisien.

Contoh skema dan query yang sering bermasalah

Misalkan ada tabel untuk menyimpan hasil pemrosesan dokumen:

CREATE TABLE ai_document_metadata (
  id BIGINT PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  document_id BIGINT NOT NULL,
  status VARCHAR(32) NOT NULL,
  source_lang VARCHAR(16),
  target_lang VARCHAR(16),
  title TEXT,
  prompt_text TEXT,
  extracted_text TEXT,
  tags TEXT,
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL
);

Lalu endpoint listing melakukan query seperti ini:

SELECT id, document_id, status, title, created_at
FROM ai_document_metadata
WHERE tenant_id = 42
  AND status = 'done'
  AND (
    title ILIKE '%korean%'
    OR extracted_text ILIKE '%korean%'
    OR tags ILIKE '%translation%'
  )
ORDER BY created_at DESC
LIMIT 50 OFFSET 5000;

Query ini terlihat biasa, tetapi mengandung beberapa sumber masalah sekaligus:

ILIKE '%...%' sulit memakai index B-tree biasa,
OR di beberapa kolom teks memperbesar biaya scan,
OFFSET 5000 memaksa database melewati banyak baris lebih dulu,
sorting created_at DESC perlu index yang sesuai dengan filter.

Mulai dari EXPLAIN, bukan tebakan

Audit yang baik dimulai dari EXPLAIN atau EXPLAIN ANALYZE di lingkungan yang aman. Tujuannya bukan sekadar melihat apakah query memakai index, tetapi memahami berapa banyak baris yang dipindai, difilter, dan diurutkan.

Apa yang perlu dilihat di EXPLAIN

Seq Scan: tanda tabel dibaca berurutan. Tidak selalu buruk, tetapi mencurigakan pada tabel besar.
Index Scan / Index Only Scan: biasanya lebih baik bila cocok dengan pola filter dan sorting.
Rows dan actual rows: jika perkiraan dan hasil nyata jauh berbeda, statistik bisa kurang akurat atau distribusi data tidak merata.
Filter: cek kondisi mana yang masih disaring setelah pembacaan index.
Sort: sorting tambahan dapat mahal jika database harus mengurutkan banyak hasil antara.
Buffers atau indikator I/O lain bila tersedia: membantu melihat apakah query berat di disk atau CPU.

Contoh audit EXPLAIN

EXPLAIN ANALYZE
SELECT id, document_id, status, title, created_at
FROM ai_document_metadata
WHERE tenant_id = 42
  AND status = 'done'
ORDER BY created_at DESC
LIMIT 50;

Jika query sederhana ini saja masih melakukan scan besar lalu sort, kemungkinan index dasar untuk listing belum ada. Untuk pola ini, index komposit biasanya jauh lebih relevan dibanding membuat banyak index tunggal.

Composite index: urutan kolom menentukan hasil

Pada backend metadata AI, query listing sering punya pola tetap: filter berdasarkan tenant/project/status, lalu urutkan berdasarkan waktu atau id. Di sini composite index lebih efektif daripada index terpisah di setiap kolom.

Contoh index yang cocok untuk listing

CREATE INDEX idx_ai_meta_tenant_status_created_at
ON ai_document_metadata (tenant_id, status, created_at DESC, id DESC);

Mengapa urutan ini berguna?

tenant_id diletakkan di depan karena hampir selalu ada dalam filter.
status ikut dimasukkan agar subset data makin sempit.
created_at DESC mendukung urutan listing terbaru.
id DESC menjadi tie-breaker agar urutan stabil saat beberapa baris punya timestamp sama.

Namun perlu diingat, index terbaik bergantung pada pola query dominan. Jika aplikasi lebih sering memfilter project_id, source_lang, atau document_id, susun index berdasarkan kombinasi yang paling sering dipakai bersama.

Kesalahan umum saat membuat index

Membuat banyak index tunggal dan berharap optimizer selalu bisa menggabungkannya secara efisien.
Menaruh kolom sorting di depan padahal filter utama justru tenant atau status.
Mengindeks kolom ber-cardinality rendah tanpa konteks query, misalnya hanya status.
Membuat terlalu banyak index sehingga write, update, dan vacuum ikut terbebani.

Index mempercepat baca, tetapi setiap insert dan update ke metadata AI juga harus memperbarui index. Karena data hasil AI sering ditulis dalam volume tinggi, tambahkan index secara selektif.

Masalah LIKE dan ILIKE pada kolom teks

Fitur AI sering mendorong tim produk menambah pencarian bebas terhadap title, prompt_text, extracted_text, atau tags. Di sinilah banyak sistem mulai melambat.

Mengapa LIKE/ILIKE sering lambat

Query seperti di bawah ini tampak sederhana:

SELECT id, title
FROM ai_document_metadata
WHERE tenant_id = 42
  AND extracted_text ILIKE '%old korean%'
ORDER BY created_at DESC
LIMIT 20;

Masalahnya, pola '%kata%' memiliki wildcard di depan, sehingga index B-tree biasa umumnya tidak bisa membantu secara efektif. Database sering perlu membaca banyak baris lalu memeriksa kecocokan satu per satu.

Kapan LIKE masih aman

Pola prefix seperti title LIKE 'trans%' lebih mudah dioptimalkan dibanding '%trans%'.
Tabel kecil atau subset yang sangat sempit kadang masih cukup cepat.
Pencarian hanya dipakai untuk debugging internal, bukan endpoint utama trafik tinggi.

Strategi perbaikan yang praktis

Batasi ruang pencarian dulu
Selalu filter tenant, project, atau status sebelum pencarian teks. Ini tidak menyelesaikan semua masalah, tetapi sangat membantu mengurangi baris kandidat.
Pisahkan kolom untuk kebutuhan exact/filterable
Jangan pakai ILIKE untuk hal yang sebenarnya bisa menjadi enum, boolean, atau kode normalisasi. Contoh: bahasa, status review, tipe dokumen, sumber model.
Normalisasi tag
Jika tags disimpan sebagai string gabungan lalu dicari dengan LIKE, pertimbangkan tabel relasi terpisah seperti document_tags. Filter berbasis join dan index biasanya lebih stabil daripada substring search pada satu kolom teks panjang.
Gunakan full-text search saat kebutuhan memang pencarian teks
Jika pengguna perlu mencari kata atau frasa pada hasil ekstraksi atau terjemahan, LIKE '%...%' bukan solusi jangka panjang. Di titik itu, pertimbangkan full-text search bawaan database atau search engine terpisah.

Kapan perlu full-text search

Pakai full-text search ketika:

pencarian teks adalah fitur inti, bukan filter tambahan,
kolom yang dicari berisi teks panjang hasil ekstraksi atau translasi,
pengguna mengharapkan hasil relevan, bukan sekadar substring match,
query harus tetap cepat saat data tumbuh besar.

Jangan buru-buru pindah ke search engine eksternal jika problem utamanya masih listing dan filtering terstruktur. Sering kali bottleneck awal justru ada di index komposit dan pagination, bukan di mesin pencari.

Pagination: keyset lebih stabil daripada OFFSET

OFFSET nyaman dipakai karena sederhana, tetapi biayanya naik seiring nomor halaman. Untuk data metadata AI yang terus bertambah, ini cepat menjadi bottleneck.

Masalah OFFSET

SELECT id, title, created_at
FROM ai_document_metadata
WHERE tenant_id = 42
  AND status = 'done'
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 5000;

Database tetap perlu menemukan dan melewati 5000 baris sebelum mengembalikan 50 baris berikutnya. Pada halaman jauh, latensi naik walau user hanya melihat sedikit data.

Gunakan keyset pagination

Keyset pagination memakai nilai baris terakhir dari halaman sebelumnya sebagai penanda lanjutan.

SELECT id, title, created_at
FROM ai_document_metadata
WHERE tenant_id = 42
  AND status = 'done'
  AND (created_at, id) < ('2026-07-01 10:00:00', 987654)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Mengapa lebih cepat?

Database tidak perlu membuang ribuan baris karena langsung melanjutkan dari posisi tertentu.
Query lebih selaras dengan index komposit seperti (tenant_id, status, created_at DESC, id DESC).
Performa cenderung lebih konsisten antar halaman.

Trade-off keyset pagination

Tidak cocok jika UI harus lompat bebas ke nomor halaman arbitrer seperti page 237.
Perlu cursor atau token di API, bukan hanya page dan per_page.
Sorting harus stabil; biasanya perlu kolom tie-breaker seperti id.

Untuk dashboard internal, API infinite scroll, atau listing audit hasil AI, keyset hampir selalu pilihan lebih aman dibanding OFFSET besar.

COUNT sering lebih mahal dari daftar datanya

Banyak endpoint melakukan dua query: satu untuk mengambil data, satu lagi untuk menghitung total. Pada tabel besar dengan filter kompleks, COUNT(*) bisa menjadi bagian termahal.

Contoh pola mahal

SELECT COUNT(*)
FROM ai_document_metadata
WHERE tenant_id = 42
  AND status = 'done'
  AND extracted_text ILIKE '%korean%';

Jika filter teks tidak ramah index, database mungkin tetap harus memeriksa banyak baris hanya untuk menghitung total.

Alternatif yang lebih pragmatis

Hindari total presisi jika tidak benar-benar dibutuhkan. Untuk banyak UI, tombol next/previous cukup.
Tampilkan estimasi atau batas seperti “1000+ hasil” jika pengalaman pengguna masih masuk akal.
Cache agregat untuk filter yang relatif stabil, misalnya hitung per status atau per project.
Precompute summary table jika dashboard sering menampilkan metrik yang sama.

Trade-off utamanya adalah kompleksitas dan konsistensi data. Namun dalam praktik, menghapus kebutuhan total yang presisi sering memberi dampak besar pada latensi API.

Strategi migrasi tanpa downtime

Audit query biasanya berujung pada perubahan index, skema, atau pola baca. Pada sistem produksi, perubahan ini harus dilakukan hati-hati agar tidak mengunci tabel terlalu lama atau merusak kompatibilitas API.

Langkah migrasi yang aman

Identifikasi query dominan
Gunakan slow query log, metrik APM, atau statistik query untuk memilih target yang benar-benar paling mahal.
Tambahkan index baru terlebih dahulu
Buat index yang dibutuhkan sebelum mengubah query aplikasi. Hindari langsung menghapus index lama.
Deploy query baru secara bertahap
Gunakan feature flag atau rollout bertahap untuk endpoint yang sensitif.
Backfill struktur baru jika perlu
Misalnya memecah tags dari string ke tabel relasi atau menambahkan kolom normalisasi pencarian.
Dual-read atau dual-write sementara
Jika migrasi besar, baca dari struktur lama dan baru untuk verifikasi hasil sebelum cutover penuh.
Validasi dengan EXPLAIN dan metrik nyata
Jangan mengandalkan asumsi. Pastikan query baru memang menurunkan scan, sort, dan latensi.
Hapus index lama setelah observasi cukup
Index yang tidak dipakai menambah biaya write dan storage.

Contoh migrasi tag tanpa downtime

Misalnya sebelumnya tag disimpan sebagai teks:

tags = 'translation,old-korean,reviewed'

Lalu Anda ingin memindahkannya ke tabel relasi:

CREATE TABLE document_tags (
  tenant_id BIGINT NOT NULL,
  document_id BIGINT NOT NULL,
  tag VARCHAR(64) NOT NULL
);

CREATE INDEX idx_document_tags_tenant_tag_doc
ON document_tags (tenant_id, tag, document_id);

Tahapannya bisa seperti ini:

buat tabel dan index baru,
backfill data tag lama per batch kecil,
saat write baru, simpan ke kolom lama dan tabel baru,
ubah query baca agar memakai tabel baru,
setelah stabil, hentikan dual-write dan hapus kolom lama bila sudah aman.

Pendekatan ini lebih aman daripada migrasi sekali jalan yang berisiko mengganggu trafik.

Checklist audit query metadata AI

Gunakan checklist berikut saat mengaudit backend yang menyimpan prompt, hasil ekstraksi, tag, atau metadata dokumen:

Apakah query paling sering dipanggil sudah dianalisis dengan EXPLAIN?
Apakah filter utama seperti tenant_id, project_id, atau status ada di depan composite index?
Apakah sorting utama selaras dengan index yang sama?
Apakah ada OFFSET besar di endpoint listing?
Apakah ada COUNT(*) mahal yang sebenarnya tidak wajib presisi?
Apakah LIKE/ILIKE dipakai pada kolom teks panjang secara berlebihan?
Apakah tag atau metadata yang bisa dinormalisasi masih disimpan sebagai string bebas?
Apakah kolom teks hasil AI dicari dengan substring padahal kebutuhan sebenarnya full-text?
Apakah jumlah index sudah terlalu banyak untuk pola write yang tinggi?
Apakah perbaikan diuji pada data yang mendekati distribusi produksi, bukan data lokal yang kecil?

Pola query yang biasanya layak diprioritaskan

Jika waktu audit terbatas, perbaiki pola berikut lebih dulu:

listing utama dengan filter tenant/status dan urutan terbaru,
search endpoint yang memakai ILIKE '%...%' pada teks panjang,
pagination dengan OFFSET besar,
query total count untuk halaman admin,
filter berbasis tag yang masih disimpan sebagai string gabungan.

Sering kali kombinasi 2-3 perubahan kecil sudah cukup terasa: satu composite index yang benar, migrasi ke keyset pagination, dan pengurangan COUNT(*) presisi.

Kesimpulan

Audit Query Metadata AI bukan soal mengoptimalkan model, melainkan memastikan data turunan AI tidak membuat database menjadi bottleneck. Ketika prompt, hasil ekstraksi, tag, dan metadata dokumen terus bertambah, masalah paling umum adalah index yang tidak cocok, pencarian LIKE/ILIKE pada teks panjang, pagination berbasis OFFSET, dan query count yang mahal.

Mulailah dari query yang paling sering atau paling lambat, baca EXPLAIN dengan disiplin, lalu sesuaikan struktur index dengan pola filter dan sorting nyata. Jika pencarian teks memang menjadi fitur inti, pertimbangkan full-text search. Jika masalahnya ada pada listing, composite index dan keyset pagination biasanya memberi dampak lebih cepat dan lebih aman.

Quick Search

shopping_cart Keranjang