Belajar SQL PostgreSQL - Full-Text Search (FTS) & Vector Extension (pgvector)

#Pendahuluan

Selamat datang di episode 17 series Belajar SQL PostgreSQL! Pada episode 5 kita menggunakan ILIKE '%kata%' untuk pencarian teks. Ia bekerja — tapi ada batasnya: lambat di tabel besar, tidak bisa mengurutkan berdasarkan relevansi, dan tidak memahami bahwa "berlari" dan "berlari cepat" adalah konsep terkait. Untuk pencarian yang serius, PostgreSQL punya mesin Full-Text Search (FTS) yang bawaan dan sangat matang.

Dan ada satu lagi yang membuat PostgreSQL semakin relevan di era AI: extension pgvector. Ia mengubah PostgreSQL menjadi vector database — tempat menyimpan embeddings dan melakukan similarity search untuk aplikasi RAG (Retrieval-Augmented Generation), rekomendasi, dan pencarian semantik. Kalian tidak perlu database terpisah untuk membangun aplikasi AI.

Di episode ini, kita akan membahas FTS native dengan to_tsvector dan to_tsquery, konsep stemming dan stop words, ranking hasil pencarian dengan ts_rank, akselerasi dengan GIN index, lalu mengenal pgvector dengan operator L2 distance dan cosine distance untuk similarity search.

#Native Full-Text Search (FTS) di PostgreSQL

#Konsep: Dokumen Menjadi Searchable Vector

Inti FTS adalah mengubah teks mentah menjadi tsvector — representasi kata yang sudah dinormalisasi (huruf kecil, akar kata, tanpa kata umum). Pencarian lalu mencocokkan tsquery terhadap tsvector tersebut.

SELECT to_tsvector('indonesian',
    'Saya berlari sangat cepat di pagi hari');

Hasilnya adalah daftar lexeme — bentuk akar kata yang sudah dibersihkan. Kata "berlari" dan "berlari cepat" menjadi lexeme yang sama. Inilah kekuatan FTS yang tidak dimiliki LIKE.

#to_tsquery: Membangun Query Pencarian

to_tsquery mengubah string query menjadi tsquery dengan operator logika & (AND), | (OR), dan ! (NOT):

SELECT
    to_tsvector('indonesian', 'Saya belajar SQL dan database') @@
    to_tsquery('indonesian', 'sql & database') AS cocok;

Operator @@ mengembalikan TRUE jika tsvector cocok dengan tsquery. Untuk pencarian kata tanpa logika ketat, fungsi plainto_tsquery mengubah input user biasa menjadi tsquery dengan AND otomatis.

SELECT id, title
FROM articles
WHERE to_tsvector('indonesian', title || ' ' || body)
      @@ plainto_tsquery('indonesian', 'cara belajar postgresql');

#Stemming dan Stop Words

Dua konsep yang membuat FTS cerdas:

• Stemming: menurunkan kata ke bentuk akarnya. "Berlari", "berlari", "lari" diperlakukan sebagai konsep yang sama.
• Stop words: kata-kata umum yang dihilangkan karena tidak membawa makna — "yang", "di", "ke", "dari", dan sebagainya.

PostgreSQL punya konfigurasi bahasa bawaan (english, indonesian, dan lainnya). Dengan 'indonesian', stop words bahasa Indonesia otomatis dibuang dan stemming bahasa Indonesia diterapkan. Inilah alasan FTS jauh mengungguli pencarian LIKE berbasis karakter.

#Ranking Search Results dengan ts_rank

Hasil pencarian FTS berupa himpunan — tapi pengguna butuh urutan berdasarkan relevansi. Fungsi ts_rank (atau ts_rank_cd untuk coverage density) menghitung skor seberapa baik dokumen cocok dengan query:

SELECT
    title,
    ts_rank(
        to_tsvector('indonesian', title || ' ' || body),
        plainto_tsquery('indonesian', 'belajar postgresql')
    ) AS skor
FROM articles
WHERE to_tsvector('indonesian', title || ' ' || body)
      @@ plainto_tsquery('indonesian', 'belajar postgresql')
ORDER BY skor DESC;

Dokumen yang memuat kata lebih sering dan lebih awal mendapat skor lebih tinggi. Menyimpan tsvector sebagai generated column (episode 12) akan membuat query ini jauh lebih cepat dan rapi:

ALTER TABLE articles
ADD COLUMN search_vector TSVECTOR
GENERATED ALWAYS AS (
    to_tsvector('indonesian', title || ' ' || body)
) STORED;

#Mengakselerasi FTS dengan GIN Index

Tanpa index, setiap pencarian memindai semua baris. Dengan GIN index pada kolom tsvector, pencarian FTS menjadi Index Scan (konsep episode 14):

CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

Sekarang query WHERE search_vector @@ plainto_tsquery(...) memakai GIN index dan berjalan dalam milidetik bahkan di tabel dengan jutaan artikel.

#Pengenalan Extension pgvector

Sekarang bagian yang paling menarik di era AI: pgvector. Extension ini menambahkan tipe data vector ke PostgreSQL beserta operator untuk similarity search — mengubah database relasional menjadi vector database untuk AI embeddings.

#Mengaktifkan dan Menyimpan Embeddings

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    content TEXT NOT NULL,
    embedding VECTOR(1536) NOT NULL
);

VECTOR(1536) menandakan dimensi embedding — menyesuaikan model embedding yang dipakai (misal OpenAI text-embedding-3-large menghasilkan 3072 dimensi, model umum lainnya 768 atau 1536). Kolom ini diisi oleh aplikasi dari hasil model embedding.

#Similarity Search dengan Operator Distance

pgvector menyediakan tiga operator jarak utama:

SELECT id, content, embedding <=> $1 AS jarak
FROM documents
ORDER BY embedding <=> $1
LIMIT 5;

$1 adalah placeholder untuk embedding query dari aplikasi. Hasilnya: 5 dokumen dengan jarak terkecil — yaitu paling mirip secara semantik dengan pertanyaan user.

#Cara Kerja RAG dengan pgvector

Pipeline lengkap aplikasi RAG (Retrieval-Augmented Generation) memanfaatkan pgvector sebagai berikut:

1. Dokumen dipecah menjadi chunk dan diubah menjadi embedding oleh model AI.
2. Embedding disimpan di kolom VECTOR(1536).
3. Saat user bertanya, pertanyaan juga diubah menjadi embedding.
4. PostgreSQL mencari chunk paling mirip dengan cosine distance.
5. Chunk yang relevan diserahkan ke LLM sebagai konteks untuk menghasilkan jawaban.

Semua langkah retrieval ini terjadi di PostgreSQL — tanpa infrastruktur vector database terpisah.

#Kapan FTS dan Kapan pgvector?

Banyak aplikasi modern menggabungkan keduanya: FTS untuk pencarian kata kunci yang presisi, pgvector untuk "yang mirip" — lalu menggabungkan skor dengan ts_rank dan cosine distance.

#Penutup

Inti yang harus dibawa pulang:

• FTS mengubah teks menjadi lexeme ternormalisasi — jauh lebih pintar dari LIKE.
• Selalu tentukan bahasa di fungsi FTS ('indonesian').
• Simpan tsvector sebagai generated column + GIN index untuk pencarian kilat.
• pgvector mengubah PostgreSQL menjadi vector database untuk AI embeddings.
• <-> (L2) dan <=> (cosine) adalah operator similarity utama; cosine paling cocok untuk teks.

Di episode 18 selanjutnya, kita menangani data raksasa: Table Partitioning untuk Data Skala Besar — mulai dari kapan perlu partitioning, declarative partitioning dengan Range, List, dan Hash, hingga partition pruning yang membuat query hanya menyentuh partisi yang relevan.