Pencarian teks lengkap PostgreSQL: jika dilakukan dengan benar, hasilnya cepat (meluruskan anggapan bahwa ini lambat)

• Ada anggapan bahwa Full-Text Search (FTS) bawaan PostgreSQL lambat, tetapi jika dioptimalkan dengan tepat, performanya bisa sangat cepat
• Blog Neon membandingkan ekstensi pg_search berbasis Rust dengan FTS bawaan dan menyatakan yang terakhir lebih lambat
• Namun, besar kemungkinan perbandingan ini dilakukan saat optimasi dasar yang wajib untuk PostgreSQL FTS belum diterapkan
• Tulisan ini membuktikan dengan angka bahwa hanya dengan optimasi sederhana pada konfigurasi FTS bawaan, performa bisa meningkat 50x

Ringkasan pengaturan benchmark

• Pengujian dilakukan berdasarkan tabel dengan 10 juta data log

  CREATE TABLE benchmark_logs (  
      id SERIAL PRIMARY KEY,  
      message TEXT,  
      country VARCHAR(255),  
      severity INTEGER,  
      timestamp TIMESTAMP,  
      metadata JSONB  
  );  
  

• Struktur kueri yang bermasalah:

  SELECT country, COUNT(*)  
  FROM benchmark_logs  
  WHERE to_tsvector('english', message) @@ to_tsquery('english', 'research')  
  GROUP BY country  
  ORDER BY country;  
  

  • Menjalankan to_tsvector() di dalam kueri → sangat tidak efisien
  • Meski ada indeks GIN, indeks tersebut tidak bisa dimanfaatkan dengan baik

Lingkungan pengujian (meniru konfigurasi dasar)

• Instance EC2 i7ie.xlarge, menggunakan NVMe SSD lokal
• 4 vCPU, menggunakan PostgreSQL 16 (Docker)
• Pengaturan PostgreSQL utama:

  -c shared_buffers=8GB  
  -c maintenance_work_mem=8GB  
  -c max_parallel_workers=4  
  -c max_worker_processes=4  
  

• Batas pemrosesan paralel: max_parallel_workers_per_gather = 2 (Neon menggunakan 8)

Faktor penurunan performa 1: perhitungan tsvector secara real-time

• Saat to_tsvector() dijalankan di dalam kueri:
• Parsing teks, analisis morfologi, dan proses lain dilakukan setiap kali
• Indeks sama sekali tidak bisa dimanfaatkan

• Solusi: buat kolom tsvector terlebih dahulu dan indekskan

  • 1. Tambahkan kolom tsvector

  ALTER TABLE benchmark_logs ADD COLUMN message_tsvector tsvector;  
  

  • 2. Isi datanya

    UPDATE benchmark_logs SET message_tsvector = to_tsvector('english', message);  
    

  • 3. Buat indeks (nonaktifkan fastupdate)

    CREATE INDEX idx_gin_logs_message_tsvector  
    ON benchmark_logs USING GIN (message_tsvector)  
    WITH (fastupdate = off);  
    

  • 4. Ubah kuerinya

    SELECT country, COUNT(*)  
    FROM benchmark_logs  
    WHERE message_tsvector @@ to_tsquery('english', 'research')  
    GROUP BY country  
    ORDER BY country;  
    

Faktor penurunan performa 2: pengaturan indeks GIN fastupdate=on

• fastupdate=on menguntungkan untuk performa tulis, tetapi berdampak buruk pada performa pencarian
• Untuk dataset read-only atau yang berfokus pada pencarian, fastupdate=off pada dasarnya wajib
• Indeks menjadi lebih kecil dan lebih cepat, serta tidak perlu memproses pending list

• Cara membuat indeks GIN yang dioptimalkan

  CREATE INDEX idx_gin_logs_message_tsvector  
  ON benchmark_logs USING GIN (message_tsvector)  
  WITH (fastupdate = off);  
  

Peningkatan performa: lebih dari 50x

• Sebelum optimasi: sekitar 41,3 detik (41.301 ms)
• Setelah optimasi: sekitar 0,88 detik (877 ms)
• Menunjukkan peningkatan performa sekitar 50x
• Performa ini tetap bisa dicapai bahkan di lingkungan dengan jumlah worker paralel yang lebih sedikit

Performa ts_rank memang bisa lambat dalam praktik

• ts_rank atau ts_rank_cd bisa relatif lambat karena harus mengevaluasi semua hasil lalu mengurutkannya
• Terutama saat menangani hasil dalam jumlah besar, beban CPU/IO menjadi tinggi

Fitur ranking lanjutan: ekstensi VectorChord-BM25

• Jika akurasi dan kecepatan pengurutan penting, menggunakan ekstensi khusus bisa lebih efektif
• VectorChord-BM25 adalah ekstensi untuk PostgreSQL yang menyediakan fungsi penilaian ranking berbasis algoritme BM25
• Ada laporan bahwa ini 3x lebih cepat daripada Elasticsearch

Kelebihan VectorChord-BM25

• Algoritme BM25: algoritme ranking pencarian yang lebih maju daripada TF-IDF
• Format indeks khusus: optimasi pencarian cepat seperti Block WeakAnd
• Menyediakan tipe bm25vector: menyimpan representasi yang sudah ditokenisasi
• Meningkatkan akurasi dan kecepatan pencarian sekaligus

Kesimpulan: FTS bawaan PostgreSQL juga cukup cepat

• Dengan menggunakan kolom tsvector dan indeks GIN yang tepat (fastupdate=off), pencarian dengan FTS bawaan pun bisa sangat cepat
• Perbandingan performa harus dilakukan berdasarkan kondisi yang sudah dioptimalkan
• Jika membutuhkan fitur ranking lanjutan, pertimbangkan penggunaan alat ekstensi seperti VectorChord-BM25
• Pesan intinya: yang lambat belum tentu alatnya, bisa jadi konfigurasinya yang bermasalah

[stadia]

Berkat itu, saya melakukan tuning kueri.

[pcj9024]

Pendapat di Hacker News ngeri juga... "Sepuluh juta? Bercanda?"

[GN⁺]

Komentar Hacker News

• Sebagai maintainer pg_search, menurut dokumentasi Postgres, baik artikel Neon/ParadeDB maupun strategi yang digunakan di sini sama-sama disajikan sebagai alternatif yang valid

  • Masalah FTS Postgres bukanlah mengoptimalkan satu kueri tunggal, melainkan memberikan performa setingkat Elastic untuk beragam kueri dunia nyata
  • pg_search dirancang untuk menyelesaikan masalah yang kedua, dan benchmark-nya juga mencerminkan hal itu
  • Benchmark Neon/ParadeDB mencakup total 12 kueri, dan tidak realistis untuk kasus penggunaan nyata
  • pg_search bekerja untuk beragam kueri bergaya "Elastic" dan tipe Postgres hanya dengan definisi indeks yang sederhana

• Menghitung tsvector secara real-time adalah kesalahan besar

  • Saat mengimplementasikan Postgres FTS di proyek pribadi, saya membaca dokumentasinya dan mengikuti panduannya
  • Dokumentasi menjelaskan dengan jelas proses membuat kasus dasar yang belum dioptimalkan lalu mengoptimalkannya
  • Orang yang melakukan kesalahan ini tampaknya tidak membaca dokumentasinya atau berniat salah menggambarkan Postgres FTS

• Saya tidak paham kecenderungan untuk mencoba memasukkan semuanya ke Postgres

• Senang melihat lebih banyak implementasi pencarian teks penuh yang native di Postgres

  • Solusi alternatif (lucene/tantivy) dirancang untuk segmen immutable, jadi jika digabungkan dengan tabel heap Postgres justru bisa menjadi solusi yang lebih buruk

• Sulit memahami apa yang terjadi karena tidak ada explain plan

  • Jika kueri menggunakan indeks, pemeriksaan ulang tsvector real-time hanya diterapkan pada item yang cocok, dan karena kueri benchmark memakai LIMIT 10, jumlah pemeriksaan ulangnya sedikit
  • Karena kondisi kueri memiliki syarat pada 2 indeks GIN, planner tampaknya memeriksa ulang semua item yang cocok terlebih dahulu

• Beberapa tahun lalu, saya ingin memakai FTS native tetapi gagal

  • Pada tabel dengan ribuan insert/detik, pembaruan penuh menjadi lambat sehingga transaksi timeout
  • Saya menambahkan indeks, tetapi saat indeks kedua selesai, timeout mulai terjadi di sistem
  • Saya harus menghapus indeks itu lagi, dan tidak sempat menguji performa FTS yang sebenarnya

• Saya telah memaketkan ekstensi RPM/DEB untuk pg_search dan vchord_bm25

  • Saya memberikan tautan bagi orang yang ingin menjalankan benchmark sendiri

• Saya melihat banyak tim langsung beralih ke Elasticsearch atau Meilisearch

  • Jika digunakan dengan tepat, FTS PG native bisa memberikan performa yang besar
  • Saya penasaran apakah performa serupa bisa didapat di browser dengan SQLite + FTS5 + Wasm

• 10 juta record adalah dataset mainan

  • Dataset teks besar seperti seluruh Wikipedia atau komentar Reddit sebelum 2022 lebih cocok untuk benchmark

• Saya pertama kali menggunakan pencarian teks penuh pg sekitar tahun 2008

  • Masalah pencarian teks penuh Postgres bukan karena terlalu lambat, tetapi karena terlalu tidak fleksibel
  • Ini bagus untuk menambahkan pencarian sederhana, tetapi kurang memadai jika ingin menyetel pencarian
  • Solr dan Elasticsearch memungkinkan pengaturan indeks dan pemrosesan pencarian yang kompleks
  • Postgres bisa saja mengadopsi fitur-fitur semacam ini, tetapi saat ini belum menyediakan apa pun
  • Postgres memecah berdasarkan spasi, dan Anda bisa memakai stopword serta stemming secara manual
  • Tidak memungkinkan melakukan penilaian skor pencarian berdasarkan bobot field
  • Dibandingkan alternatifnya, ini adalah sistem mainan