Penelusuran teks lengkap menggunakan pg_textsearch

Ekstensi pg_textsearch di Cloud SQL untuk PostgreSQL menyediakan penelusuran teks lengkap menggunakan algoritma penilaian BM25 (Best Matching 25) standar industri untuk memberikan penilaian relevansi yang sangat akurat. Ekstensi ini adalah project open source yang tersedia di GitHub.

Ekstensi pg_textsearch memerlukan PostgreSQL 17 atau yang lebih baru.

Karena pg_textsearch mendukung frekuensi dokumen terbalik, saturasi frekuensi istilah, dan normalisasi panjang dokumen, fungsi ini dapat menghasilkan hasil yang lebih relevan daripada fungsi ts_rank bawaan PostgreSQL. Selain itu, karena beroperasi langsung di halaman penyimpanan PostgreSQL standar, Anda tidak perlu menginstal mesin eksternal seperti Elasticsearch, atau mengkhawatirkan pemeliharaan cluster ganda dan sinkronisasi data. Dengan pg_textsearch, Anda dapat membangun pengalaman penelusuran yang andal, skalabel, dan sangat relevan tanpa meninggalkan ekosistem PostgreSQL.

Ada beberapa opsi untuk penelusuran teks di Cloud SQL untuk PostgreSQL:

  • Pencocokan persis: Dengan penelusuran SQL standar, Anda menggunakan pernyataan LIKE dan ILIKE untuk memindai urutan karakter yang persis. Contoh kueri adalah, ILIKE '%smart fitness watches%', yang akan menemukan instance seperti:

    • "Jelajahi smartwatch kebugaran baru kami yang sedang promo."
    • "Smartwatch kebugaran adalah hadiah yang bagus."

    Namun, pencarian akan gagal menemukan:

    • "Pelari menginginkan smartwatch kebugaran tahan air."
    • "Smartwatch ini sangat pintar dalam memantau kebugaran."

  • Penelusuran teks lengkap: Dengan menggunakan algoritma BM25 dan tsvector, penelusuran teks lengkap memecah teks menjadi kata dasar, mengabaikan kata filter, dan memberi skor relevansi. Model ini memahami aturan bahasa, termasuk bentuk jamak dan tata bahasa, serta memperhitungkan frekuensi kata. Penelusuran teks lengkap untuk smart AND fitness AND watches akan menemukan instance yang tidak ditemukan oleh contoh kueri pencocokan persis, dan juga instance yang lebih rumit seperti ini:

    • "Smartwatch sangat cocok untuk rutinitas kebugaran harian Anda."
    • "Tidak semua jam tangan ini pintar dalam hal kebugaran."

    Namun, pencarian akan gagal menemukan:

    • "Smartwatch yang cerdas dan sadar kesehatan dapat membantu program latihan Anda."
    • "Gelang pelacak latihan fisik cerdas ini sangat murah."

  • Penelusuran semantik: Dengan menggunakan model AI, penelusuran semantik mengonversi teks menjadi vektor dan mengukur jarak kemiripan. Solusi ini memahami makna, maksud, konteks, sinonim, dan konsep terkait. Penelusuran semantik untuk smart fitness watches akan menemukan contoh instance yang tidak ditemukan oleh metode penelusuran lainnya. Penelusuran ini akan memahami bahwa "smartwatch yang cerdas dan sadar kesehatan" sama dengan "smartwatch kebugaran", dan "gelang pelacak latihan fisik yang cerdas" juga bisa relevan. Perangkat ini mungkin keliru mencocokkan tali pedometer jika tidak memprioritaskan "jam tangan" secara memadai.

Menginstal ekstensi pg_textsearch

Lakukan langkah-langkah berikut untuk menginstal dan mengaktifkan pg_textsearch:

  1. Tetapkan flag cloudsql.enable_pg_textsearch ke on seperti yang dijelaskan di Mengonfigurasi flag database. Tindakan ini akan menambahkan pg_textsearch ke shared_preload_libraries.

  2. Menginstal ekstensi pg_textsearch

      CREATE EXTENSION pg_textsearch;

  3. Verifikasi penginstalannya:

      SELECT extversion FROM pg_extension WHERE extname = 'pg_textsearch'

Menelusuri menggunakan pg_textsearch

Misalkan tabel contoh berikut telah diisi dengan data:

CREATE TABLE documents (
    doc_id TEXT PRIMARY KEY,
    content TEXT,
    text_embedding vector(3072)
    GENERATED ALWAYS AS (embedding('gemini-embedding-001', content)) STORED 
  );

Sebelum menggunakan penelusuran teks lengkap, buat indeks BM25 terlebih dahulu:

CREATE INDEX idx_docs_bm25
      ON documents
      USING bm25 (content)
      WITH (text_config = 'english');

Kemudian, Anda dapat membuat kueri penelusuran teks lengkap seperti ini:

SELECT doc_id, content, content <@> 'database system'
      AS score FROM documents
      ORDER BY content <@> 'database system'
      ASC LIMIT 5;

Melakukan penelusuran campuran menggunakan pg_textsearch dan vector

Anda dapat menggunakan pg_textsearch bersama dengan ekstensi penelusuran semantik vector untuk melakukan penelusuran campuran. Instal ekstensi penelusuran semantik vector seperti ini:

CREATE EXTENSION IF NOT EXISTS vector CASCADE;

Buat kueri penelusuran semantik dan teks lengkap campuran seperti ini:

WITH
  -- Semantic search results
  vector_search AS (
    SELECT doc_id,
      RANK () OVER (ORDER BY text_embedding <=>
                             google_ml.embedding('gemini-embedding-001',
                                                 'database')::VECTOR ) AS rank
      FROM documents
      ORDER BY text_embedding <=> google_ml.embedding('gemini-embedding-001',
                                                      'database')::VECTOR
    LIMIT 10
  ),
  -- Full text search results
  text_search AS (
    SELECT doc_id,
      RANK () OVER (ORDER BY content <@> 'database' ASC) AS rank
    FROM documents
    ORDER BY content <@> 'database' ASC
    LIMIT 10
  )
  -- RRF combining both semantic and full text search results
  SELECT
    COALESCE(vector_search.doc_id, text_search.doc_id) AS id,
    COALESCE(1.0 / (60 + vector_search.rank), 0.0) +
    COALESCE(1.0 / (60 + text_search.rank), 0.0) AS rrf_score
  FROM vector_search
    FULL OUTER JOIN text_search ON vector_search.doc_id = text_search.doc_id
  ORDER BY rrf_score DESC
  LIMIT 5;

Flag untuk mengonfigurasi ekstensi pg_textsearch

Gunakan flag berikut untuk mengonfigurasi penelusuran teks lengkap pg_textsearch: