Pencarian PostgreSQL "di mana saja": full-text, trigram, partial index

Apa arti “search everywhere” untuk alat internal

Di layar internal, “search everywhere” biasanya berarti: “Bantu saya menemukan rekaman yang saya pikirkan, cepat, meskipun saya tidak mengingatnya dengan sempurna.” Orang tidak sedang menjelajah. Mereka mencoba langsung lompat ke satu customer, tiket, invoice, atau perangkat.

Itulah kenapa pencarian yang lambat terasa lebih menyebalkan daripada satu halaman yang lambat. Muat halaman terjadi sekali. Pencarian terjadi berulang kali, seringkali saat seseorang sedang di telepon atau melakukan triage. Jika hasil butuh 2–3 detik, pengguna mengubah query, menghapus, mencoba kata lain, dan Anda berakhir dengan beban lebih dan frustrasi.

Dari satu kotak pencarian, pengguna mengharapkan beberapa perilaku: pencocokan parsial ("alex" menemukan "Alexander"), toleransi untuk typo kecil ("microsfot" masih menemukan "Microsoft"), urutan “hasil terbaik” yang masuk akal (ID atau email yang tepat berada di atas), sedikit bias pada recency, dan filter yang berlaku secara default (tiket terbuka, pelanggan aktif).

Yang rumit adalah satu input sering menyembunyikan beberapa intent. Seorang agen mungkin menempelkan nomor tiket, mengetik fragmen nama, mencari email, atau memasukkan nomor telepon. Setiap intent menginginkan strategi berbeda, index berbeda, dan kadang aturan ranking yang berbeda.

Jadi jangan mulai dari index. Mulailah dengan mendaftar beberapa intent pencarian yang benar-benar dimiliki pengguna Anda, dan pisahkan field identitas (ID, email) dari field fuzzy (nama, subjek) dan teks panjang (catatan).

Mulai dengan memberi nama data dan perilaku pencarian

Sebelum memilih index, tuliskan apa yang sebenarnya diketik orang. “PostgreSQL search everywhere” terdengar seperti satu fitur, tapi biasanya itu campuran dari beberapa jenis pencarian yang sangat berbeda.

Alat internal memadukan identitas “keras” (order ID, nomor tiket, kode invoice) dengan teks “lunak” (nama pelanggan, email, catatan, tag). Kelompok-kelompok itu berperilaku berbeda di PostgreSQL, jadi memperlakukan mereka sama adalah jalan cepat menuju query lambat.

Selanjutnya, pisahkan perilakunya:

• Exact lookup: seseorang yang mencari TCK-104883 mengharapkan satu hasil presisi.
• Fuzzy lookup: seseorang mengetik john smth menginginkan pencocokan yang lunak di nama (dan mungkin email) dan akan memindai daftar pendek.
• Filter-driven search: seseorang memilih “Status = Open” dan “Assigned to = Me” kebanyakan hanya memfilter; kotak teksnya sekunder.

Putuskan sejak awal apakah hasil perlu di-rank (hasil terbaik di atas) atau sekadar difilter. Ranking penting untuk catatan dan deskripsi panjang. Untuk ID dan email, ranking sering terasa acak dan menambah biaya.

Checklist singkat biasanya cukup:

• Field mana yang dicari setiap hari?
• Input mana yang exact (ID, kode), fuzzy (nama), atau teks panjang (catatan)?
• Filter mana yang berlaku hampir untuk setiap pencarian?
• Apakah Anda perlu urutan “best match”, atau kecocokan apa pun diterima?
• Seberapa cepat tabel akan tumbuh: ribuan, ratusan ribu, atau jutaan?

Jika Anda menentukan keputusan itu sejak awal, pilihan index nanti tidak terasa seperti tebak-tebakan.

Baseline: exact matches dan kenapa ILIKE sering menyakiti

Amankan kemenangan mudah dulu. Untuk banyak layar internal, index B-tree biasa sudah memberi hasil instan untuk pencarian exact seperti ID, nomor order, email, dan referensi eksternal.

Jika orang menempelkan nilai exact, pastikan query Anda benar-benar exact. WHERE id = ... atau WHERE email = ... bisa sangat cepat dengan index normal. Index unik pada email sering bayar dua kali: kecepatan dan kualitas data yang lebih baik.

Masalah mulai ketika “search everywhere” diam-diam berubah menjadi ILIKE. Query seperti name ILIKE '%ann%' memiliki wildcard di depan, jadi PostgreSQL tidak bisa menggunakan index B-tree biasa. Ia akhirnya memeriksa banyak baris, dan akan melambat saat tabel tumbuh.

Pencarian prefix bisa bekerja, tapi hanya ketika pola berjangkar di awal: name ILIKE 'ann%'. Bahkan begitu, detail penting (kolasi, penanganan huruf besar/kecil, dan apakah Anda mengindeks ekspresi yang sama seperti yang Anda query). Jika UI Anda harus case-insensitive, pendekatan umum adalah query lower(name) dan membuat index yang cocok pada lower(name).

Juga berguna untuk menyepakati apa arti “snappy”:

• Kira-kira 200 ms atau kurang untuk kerja database pada cache hangat
• Kurang dari 1 detik end-to-end termasuk jaringan dan rendering
• Tidak ada status loading yang terlihat untuk pencarian umum

Dengan target seperti itu, lebih mudah memutuskan apakah Anda bisa bertahan dengan exact dan prefix match, atau saatnya pakai full-text search atau index trigram.

Kapan full-text search adalah alat yang tepat

Full-text search paling cocok ketika orang mengetik bahasa alami dan mengharapkan sistem menemukan item yang tepat, bukan hanya kecocokan exact. Pikirkan pesan tiket, catatan internal, deskripsi panjang, artikel basis-pengetahuan, dan log panggilan.

Keuntungan besar adalah ranking. Alih-alih mengembalikan daftar panjang di mana hasil terbaik terkubur, full-text search bisa mengurutkan menurut relevansi. Di alat internal, itu penting: seseorang butuh jawaban dalam detik, bukan setelah memindai 50 baris.

Secara garis besar, full-text search punya tiga bagian bergerak:

• tsvector (teks yang bisa dicari, disimpan atau dihasilkan)
• tsquery (apa yang diketik pengguna, diubah menjadi query)
• Konfigurasi bahasa (cara kata dinormalisasi)

Konfigurasi bahasa adalah tempat perilaku menjadi terlihat. PostgreSQL menghapus stop words umum (seperti “the” atau “and”) dan menerapkan stemming, jadi “pay”, “paid”, dan “payment” bisa saling mencocokkan. Itu bagus untuk catatan dan pesan, tapi bisa mengejutkan ketika seseorang mencari kata pendek umum dan tidak mendapatkan apa-apa.

Sinonim adalah titik pengambilan keputusan lain. Mereka membantu ketika perusahaan Anda menggunakan kata berbeda untuk hal yang sama (misalnya, “refund” vs “chargeback”), tapi membutuhkan perawatan dari waktu ke waktu. Jaga daftar sinonim pendek dan berdasarkan apa yang tim support atau ops benar-benar ketik.

Contoh praktis: mencari “can’t login after reset” seharusnya menarik tiket di mana pesan mengatakan “cannot log in after password reset”, meskipun kata-katanya berbeda. Perilaku “menemukan yang relevan” seperti itu adalah inti full-text search, dan biasanya pilihan yang lebih baik daripada mencoba membuat ILIKE berperilaku seperti mesin pencari.

Kapan trigram index menang

Index trigram sangat cocok ketika pengguna mengetik fragmen, membuat typo, atau hanya ingat “sesuatu seperti itu.” Mereka unggul pada field teks pendek di mana full-text terlalu ketat: nama orang, nama perusahaan, subjek tiket, SKU, nomor order, dan kode produk.

Trigram adalah potongan teks 3-karakter. PostgreSQL membandingkan dua string berdasarkan berapa banyak trigram yang mereka bagi. Itulah sebabnya ia bisa mencocokkan "Jon Smth" ke "John Smith", atau "ACM" ke "ACME", dan menemukan hasil ketika query berada di tengah kata.

Ini sering menjadi jalur tercepat ke kotak pencarian yang bersahabat ketika tugas adalah “temukan baris yang tepat,” bukan “temukan dokumen tentang topik.”

Di mana trigram mengungguli full-text

Full-text bagus untuk teks panjang dan pengurutan berdasarkan makna, tetapi tidak secara alami menangani string parsial dan typo kecil pada field pendek. Trigram dibangun untuk jenis fuzziness seperti itu.

Jaga biaya penulisan tetap wajar

Index trigram lebih besar dan menambah overhead pada operasi tulis, jadi selektiflah. Indeks kolom yang benar-benar digunakan orang:

• Nama, email, perusahaan, username
• Identifier pendek (SKU, kode, referensi)
• Field judul ringkas (bukan field catatan/komentar besar)

Jika Anda bisa menyebutkan kolom tepat yang tim Anda ketik di kotak pencarian, Anda biasanya bisa menjaga index trigram tetap kecil dan cepat.

Partial indexes untuk filter yang sering dipakai

Kotak “search everywhere” biasanya punya default tersembunyi. Orang mencari di dalam workspace, pada item aktif, dan mengesampingkan yang terhapus. Jika filter itu hadir di hampir setiap request, buat kasus umum cepat dengan mengindeks hanya baris yang memenuhinya.

Partial index adalah index biasa dengan klausa WHERE. PostgreSQL menjaganya lebih kecil karena hanya menyimpan entri untuk baris yang Anda pedulikan. Itu sering berarti lebih sedikit page yang dibaca dan tingkat hit cache yang lebih baik.

Target partial index yang umum termasuk baris aktif (status = 'active'), soft delete (deleted_at IS NULL), pembatasan tenant, dan jendela “terkini” (misalnya, 90 hari terakhir).

Kuncinya adalah cocokkan dengan UI Anda. Jika layar selalu menyembunyikan baris terhapus, query Anda harus selalu menyertakan deleted_at IS NULL, dan partial index Anda harus memakai kondisi yang sama. Ketidaksesuaian kecil, seperti menggunakan is_deleted = false di satu tempat dan deleted_at IS NULL di tempat lain, bisa membuat planner tidak memakai index.

Partial index juga bekerja berdampingan dengan full-text dan trigram. Misalnya, mengindeks text search hanya untuk baris non-terhapus menjaga ukuran index tetap terkendali.

Trade-off: partial index kurang membantu untuk query yang jarang. Jika seseorang sesekali mencari di seluruh baris terhapus atau di semua workspace, PostgreSQL mungkin kembali ke rencana yang lebih lambat. Tangani itu dengan jalur admin khusus, atau tambahkan index kedua hanya jika query langka menjadi umum.

Mencampur pendekatan tanpa membuat pencarian jadi misteri

Kebanyakan tim akhirnya mencampur teknik karena satu kotak pencarian harus menangani intent berbeda. Tujuannya adalah membuat urutan operasi jelas sehingga hasil terasa dapat diprediksi.

Urutan prioritas sederhana membantu, apakah Anda mengimplementasikannya sebagai query terpisah atau satu query dengan logika CASE yang jelas.

Tangga prioritas yang dapat diprediksi

Mulai ketat, lalu longgar kalau perlu:

• Exact match dulu (ID, email, nomor tiket, SKU) menggunakan B-tree index
• Prefix match berikutnya di tempat yang masuk akal
• Trigram match setelah itu untuk typo dan fragmen pada nama dan judul
• Full-text search terakhir untuk catatan panjang, deskripsi, dan konten bebas

Jika Anda konsisten pada tangga yang sama, pengguna belajar apa arti kotak itu. Mereka berhenti beranggapan sistem rusak ketika “12345” menemukan tiket seketika sementara “refund policy” mencari teks yang lebih panjang.

Filter dulu, lalu fuzz

Pencarian fuzzy jadi mahal ketika harus mempertimbangkan seluruh tabel. Persempit himpunan kandidat dengan filter yang sebenarnya digunakan orang (status, tim yang ditugaskan, rentang tanggal, akun), lalu jalankan trigram atau full-text pada sisa. Bahkan index trigram yang cepat bisa terasa lambat jika diminta menilai jutaan baris.

Juga berguna menulis aturan satu paragraf yang bisa dimengerti rekan non-teknis, misalnya: “Kita mencocokkan nomor tiket secara exact, lalu nama pelanggan dengan toleransi typo, lalu mencari catatan.” Definisi bersama itu mencegah argumen nanti tentang kenapa sebuah baris muncul.

Langkah demi langkah: pilih pendekatan dan implementasikan dengan aman

Kotak “search everywhere” yang cepat adalah serangkaian keputusan kecil. Tuliskan dulu, dan pekerjaan database jadi lebih sederhana.

1. Tentukan input. Apakah hanya satu kotak, atau satu kotak plus filter (status, owner, rentang tanggal)?
2. Pilih tipe pencocokan per field. ID dan kode ingin exact match. Nama dan email sering butuh prefix atau fuzzy. Catatan panjang dan deskripsi lebih cocok dengan pencarian bahasa alami.
3. Tambahkan index yang tepat dan konfirmasi penggunaannya. Buat index, lalu cek query nyata Anda dengan EXPLAIN (ANALYZE, BUFFERS).
4. Tambahkan ranking atau pengurutan yang sesuai intent. Jika pengguna mengetik “invoice 1042”, exact match harus naik. Jika mereka mengetik nama yang salah eja, ranking berdasarkan similarity harus menang.
5. Uji dengan query nyata. Coba typo, istilah sangat pendek (seperti “al”), teks panjang yang ditempel, input kosong, dan mode “hanya filter”.

Untuk deploy aman, ubah satu hal pada satu waktu dan siapkan rollback mudah. Untuk index baru pada tabel besar, lebih suka CREATE INDEX CONCURRENTLY agar tidak memblokir penulisan. Jika bisa, rilis di balik feature flag dan bandingkan latensi sebelum dan sesudah.

Polanya: exact match dulu (cepat dan presisi), trigram untuk field “manusia” yang sering salah eja, dan full-text untuk teks panjang yang mendapat manfaat dari ranking.

Contoh realistis: satu kotak pencarian di panel admin support

Bayangkan panel admin support di mana tim punya satu kotak pencarian, tapi mengharapkan menemukan customer, tiket, bahkan catatan. Ini masalah klasik “satu input, banyak makna”.

Kemenangan pertama adalah membuat intent terlihat tanpa menambah friksi. Jika query terlihat seperti email atau nomor telepon, perlakukan sebagai pencarian pelanggan. Jika terlihat seperti ID tiket (misal, "TKT-10482"), rute langsung ke tiket. Sisanya fallback ke pencarian teks di subject tiket dan catatan.

Untuk pencarian pelanggan, index trigram biasanya terasa paling cocok. Nama dan perusahaan berantakan, dan orang mengetik fragmen. Index trigram membuat pencarian seperti “jon smi” atau “acm” cepat dan lunak.

Untuk catatan tiket, gunakan full-text search. Catatan biasanya kalimat nyata, dan Anda biasanya butuh kecocokan relevan, bukan sekadar “mengandung substring.” Ranking membantu ketika puluhan tiket menyebut kata kunci yang sama.

Filter lebih penting daripada yang kebanyakan tim duga. Jika agen hidup di “tiket terbuka”, tambahkan partial index yang hanya mencakup baris terbuka. Lakukan hal yang sama untuk “pelanggan aktif.” Itu menjaga index tetap kecil dan membuat jalur umum cepat.

Query sangat pendek berhak mendapatkan aturan, kalau tidak database mengerjakan pekerjaan mahal untuk noise:

• 1–2 karakter: tampilkan tiket terbuka terbaru dan pelanggan yang baru diupdate
• 3+ karakter: jalankan trigram untuk field pelanggan dan full-text untuk teks tiket
• Tidak ada intent jelas: tampilkan daftar campuran, tapi batasi tiap grup (misalnya, 10 pelanggan dan 10 tiket)

Kesalahan umum yang membuat pencarian lambat atau membingungkan

Kebanyakan bug “kenapa pencarian lambat?” terjadi karena tindakan tim sendiri. Tujuan bukan mengindeks semuanya, melainkan mengindeks apa yang orang lakukan.

Perangkap umum adalah menambahkan index pada banyak kolom “sekadar jaga-jaga.” Bacaan bisa membaik, tapi setiap insert dan update kini punya kerja tambahan. Di alat internal yang catatannya berubah sepanjang hari (tiket, order, user), kecepatan tulis penting.

Kesalahan lain adalah memakai full-text ketika yang sebenarnya dibutuhkan adalah lookup toleran-typo pada nama atau email. Full-text bagus untuk dokumen dan deskripsi. Ia bukan solusi ajaib untuk “Jon” vs “John” atau “gmail.con” vs “gmail.com.” Itu biasanya domain trigram.

Filter juga bisa diam-diam merusak rencana. Jika sebagian besar pencarian terjadi dengan filter tetap (seperti status = 'open' atau org_id = 42), index terbaik mungkin partial index yang cocok dengan kondisi itu. Jika Anda lupa, PostgreSQL bisa memindai jauh lebih banyak baris dari yang Anda kira.

Beberapa kesalahan yang sering muncul:

• Menambah banyak index tanpa mengukur biaya tulis
• Mengira full-text akan berperilaku seperti autocomplete yang toleran-typo
• Mengabaikan bagaimana filter umum mengubah index yang bisa dipakai
• Menguji pada data kecil dan bersih, bukan frekuensi istilah nyata (kata umum vs ID yang langka)
• Mengurutkan oleh kolom tanpa index pendukung, memaksa sort yang lambat

Contoh: layar support mencari tiket berdasarkan subject, nama pelanggan, dan nomor tiket, lalu mengurutkan berdasarkan aktivitas terbaru. Jika latest_activity_at tidak diindeks untuk himpunan yang difilter (misalnya, tiket terbuka), sort itu bisa menghapus kecepatan yang Anda dapatkan dari index pencarian.

Pemeriksaan cepat sebelum rilis

Sebelum menyatakan fitur “search everywhere” selesai, jelaskan perilaku yang Anda janjikan.

• Apakah orang mencari record dengan identifier exact (nomor tiket, email)?
• Apakah mereka mengharapkan pencocokan fuzzy untuk typo?
• Apakah mereka ingin hasil yang diurutkan berdasarkan relevansi dari catatan panjang dan deskripsi?

Jika Anda mencampur mode, putuskan mana yang menang saat konflik.

Lalu identifikasi 2–3 field yang menggerakkan sebagian besar pencarian. Jika 80% pencarian oleh email, nama, dan ID tiket, optimalkan itu dulu dan anggap yang lain sekunder.

Checklist pra-rilis singkat:

• Konfirmasi mode pencocokan utama per field (exact lookup, fuzzy match, atau ranked text)
• Daftar filter yang dipakai pengguna sehari-hari dan pastikan index cocok dengan kombinasi itu
• Putuskan cara menangani query sangat pendek dan input kosong (misalnya, minta 2–3 karakter untuk fuzzy search; tampilkan “recent” untuk kosong)
• Buat pengurutan bisa dijelaskan: paling baru, kecocokan teks terbaik, atau aturan gabungan sederhana

Terakhir, uji dengan ukuran data dan timing yang realistis, bukan hanya kebenaran. Query yang terasa instan pada 1.000 baris bisa melambat pada 1.000.000.

Langkah berikutnya: ubah rencana menjadi layar pencarian internal yang cepat

Kotak pencarian tetap cepat ketika tim sepakat apa yang harus dilakukan. Tuliskan aturan dengan bahasa sederhana: apa arti “cocok” (exact, prefix, toleran-typo), field mana yang dicari, dan bagaimana filter mengubah himpunan hasil.

Jaga satu set uji kecil dari pencarian nyata dan perlakukan itu seperti suite regresi. Sepuluh sampai dua puluh query biasanya cukup: beberapa nama umum, beberapa email parsial, satu typo, cuplikan catatan panjang, dan satu kasus “hasil kosong.” Jalankan sebelum dan sesudah perubahan supaya pekerjaan performa tidak diam-diam merusak relevansi.

Jika Anda membangun alat internal dengan AppMaster (appmaster.io), membantu untuk mendefinisikan aturan pencarian itu bersamaan dengan model data dan logika bisnis, sehingga perilaku UI dan pilihan database tidak menyimpang saat kebutuhan berubah.