PostgreSQL-Volltextsuche: Richtig gemacht ist sie schnell (der Irrtum, sie sei langsam)

 (blog.vectorchord.ai)
33 Punkte von GN⁺ 2025-04-10 | 3 Kommentare | Auf WhatsApp teilen
  • Die eingebaute Full-Text Search (FTS) von PostgreSQL gilt oft als langsam, kann mit der richtigen Optimierung jedoch sehr schnell arbeiten
  • Im Blog von Neon wird die Rust-basierte Erweiterung pg_search mit der eingebauten FTS verglichen und behauptet, letztere sei langsam
  • Dieser Vergleich wurde jedoch wahrscheinlich in einem Zustand durchgeführt, in dem grundlegende, für PostgreSQL FTS essenzielle Optimierungen fehlten
  • Dieser Beitrag belegt mit Zahlen, dass sich bereits mit einfachen Optimierungen der Standard-FTS eine 50-fache Leistungssteigerung erzielen lässt

Überblick über das Benchmark-Setup

  • Getestet wurde auf Basis einer Tabelle mit 10 Millionen Log-Datensätzen 
    CREATE TABLE benchmark_logs (  
    id SERIAL PRIMARY KEY,  
    message TEXT,  
    country VARCHAR(255),  
    severity INTEGER,  
    timestamp TIMESTAMP,  
    metadata JSONB  
);
  • Die problematische Query-Struktur: 
    SELECT country, COUNT(*)  
FROM benchmark_logs  
WHERE to_tsvector('english', message) @@ to_tsquery('english', 'research')  
GROUP BY country  
ORDER BY country;
    • to_tsvector() wird innerhalb der Query ausgeführt → sehr ineffizient
    • Selbst mit GIN-Index wird dieser nicht richtig genutzt

Testumgebung (Nachbildung der Standardeinstellungen)

  • EC2-Instanz i7ie.xlarge mit lokaler NVMe SSD
  • 4 vCPUs, PostgreSQL 16 (Docker)
  • Wichtige PostgreSQL-Einstellungen: 
    -c shared_buffers=8GB  
-c maintenance_work_mem=8GB  
-c max_parallel_workers=4  
-c max_worker_processes=4
  • Begrenzung der Parallelverarbeitung: max_parallel_workers_per_gather = 2 (Neon verwendet 8)

Leistungsproblem 1: tsvector-Berechnung zur Laufzeit

  • Wenn to_tsvector() innerhalb der Query ausgeführt wird:
  • werden Text-Parsing, morphologische Analyse usw. jedes Mal erneut durchgeführt
  • kann der Index überhaupt nicht genutzt werden

  • Lösung: tsvector-Spalte vorab erzeugen und indizieren

    • 1. tsvector-Spalte hinzufügen
    ALTER TABLE benchmark_logs ADD COLUMN message_tsvector tsvector;
    • 2. Daten befüllen 
      UPDATE benchmark_logs SET message_tsvector = to_tsvector('english', message);
    • 3. Index erzeugen (fastupdate deaktivieren) 
      CREATE INDEX idx_gin_logs_message_tsvector  
ON benchmark_logs USING GIN (message_tsvector)  
WITH (fastupdate = off);
    • 4. Query anpassen 
      SELECT country, COUNT(*)  
FROM benchmark_logs  
WHERE message_tsvector @@ to_tsquery('english', 'research')  
GROUP BY country  
ORDER BY country;

Leistungsproblem 2: GIN-Index mit fastupdate=on

  • fastupdate=on ist für die Schreibperformance vorteilhaft, wirkt sich aber negativ auf die Suchperformance aus
  • Für schreibgeschützte oder suchlastige Datensätze ist fastupdate=off essenziell
  • Der Index ist kleiner und schneller, außerdem entfällt die Verarbeitung der Pending List

  • So erstellt man einen optimierten GIN-Index

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

Leistungsgewinn: mehr als 50-fache Verbesserung

  • Vor der Optimierung: ca. 41,3 Sekunden (41.301 ms)
  • Nach der Optimierung: ca. 0,88 Sekunden (877 ms)
  • Das entspricht einer rund 50-fachen Leistungssteigerung
  • Diese Performance ist auch in Umgebungen mit wenig Parallelverarbeitung erreichbar

Die Performance von ts_rank kann tatsächlich langsam sein

  • ts_rank oder ts_rank_cd bewerten alle Ergebnisse und sortieren sie anschließend, weshalb sie relativ langsam sein können
  • Besonders bei großen Ergebnismengen ist die CPU-/IO-Last hoch

Erweiterte Ranking-Funktion: Erweiterung VectorChord-BM25

  • Wenn Sortiergenauigkeit und Geschwindigkeit wichtig sind, ist eine spezialisierte Erweiterung oft effektiver
  • VectorChord-BM25 ist eine PostgreSQL-Erweiterung, die Ranking auf Basis des BM25-Algorithmus bietet
  • Berichten zufolge ist sie 3-mal schneller als Elasticsearch

Vorteile von VectorChord-BM25

  • BM25-Algorithmus: ein weiterentwickelter Suchranking-Algorithmus gegenüber TF-IDF
  • Spezielles Indexformat: für schnelle Suche optimiert, etwa mit Block WeakAnd
  • Bietet den Typ bm25vector: speichert tokenisierte Repräsentationen
  • Verbessert sowohl Suchgenauigkeit als auch Geschwindigkeit

Fazit: Auch die Standard-FTS von PostgreSQL ist schnell genug

  • Mit einer tsvector-Spalte und einem passenden GIN-Index (fastupdate=off) sind auch mit der eingebauten FTS sehr schnelle Suchen möglich
  • Leistungsvergleiche sollten auf optimierten Grundlagen erfolgen
  • Wenn erweiterte Ranking-Funktionen nötig sind, lohnt sich der Einsatz von Erweiterungen wie VectorChord-BM25
  • Die Kernbotschaft: Nicht das Tool ist langsam, sondern möglicherweise die Konfiguration

Verwandte Beiträge

3 Kommentare

 
stadia 2025-06-03

Dadurch habe ich das Query-Tuning durchgeführt.
 
pcj9024 2025-04-10

Die Meinungen auf Hacker News sind ja furchteinflößend ... „Zehn Millionen? Ein Witz?“
 
GN⁺ 2025-04-10
Hacker-News-Kommentare

  • Als Maintainer von pg_search gilt laut der Postgres-Dokumentation sowohl der Neon/ParadeDB-Artikel als auch die hier verwendete Strategie als valide Alternative

    • Das Problem von Postgres FTS besteht nicht darin, eine einzelne Query zu optimieren, sondern bei einer Vielzahl realer Queries eine Performance auf Elastic-Niveau zu liefern
    • pg_search wurde entwickelt, um Letzteres zu lösen, und die Benchmarks spiegeln das wider
    • Der Neon/ParadeDB-Benchmark umfasst insgesamt 12 Queries und ist für realistische Anwendungsfälle unrealistisch
    • pg_search funktioniert mit verschiedenen „Elastic-Style“-Queries und Postgres-Typen allein mit einer einfachen Indexdefinition

  • tsvector in Echtzeit zu berechnen, ist ein großer Fehler

    • Als ich Postgres FTS in einem privaten Projekt implementiert habe, habe ich die Dokumentation gelesen und die Anweisungen befolgt
    • Die Dokumentation erklärt klar, wie man zuerst einen nicht optimierten Basisfall erstellt und ihn dann optimiert
    • Wer diesen Fehler gemacht hat, hat entweder die Dokumentation nicht gelesen oder wollte Postgres FTS absichtlich falsch darstellen

  • Ich verstehe den Drang nicht, alles in Postgres zu packen

  • Ich freue mich, mehr Postgres-native Implementierungen für Volltextsuche zu sehen

    • Alternative Lösungen (lucene/tantivy) sind für unveränderliche Segmente ausgelegt und können in Kombination mit Postgres-Heap-Tabellen eine schlechtere Lösung sein

  • Ohne Explain-Plan ist schwer zu verstehen, was passiert

    • Wenn die Query den Index verwendet, wird die erneute Prüfung des tsvector in Echtzeit nur auf Treffer angewendet, und da die Benchmark-Query LIMIT 10 verwendet, gibt es nur wenige Rechecks
    • Die Query-Bedingung enthält Prädikate für zwei GIN-Indizes, daher scheint der Planner zunächst alle Treffer erneut zu prüfen

  • Vor ein paar Jahren wollte ich native FTS verwenden, bin aber gescheitert

    • In einer Tabelle mit Tausenden Inserts pro Sekunde wurden vollständige Updates so langsam, dass Transaktionen in ein Timeout liefen
    • Ich habe Indizes hinzugefügt, aber sobald der zweite Index fertig war, traten im System Timeouts auf
    • Ich musste die Indizes wieder entfernen und bekam nie die Gelegenheit, die tatsächliche FTS-Performance zu testen

  • Ich habe RPM/DEB-Pakete für die Erweiterungen pg_search und vchord_bm25 gebaut

    • Ich teile den Link für alle, die selbst Benchmarks durchführen möchten

  • Ich habe viele Teams direkt zu Elasticsearch oder Meilisearch wechseln sehen

    • Bei richtiger Nutzung kann man aus nativem PG FTS viel Performance herausholen
    • Ich frage mich, ob man mit SQLite + FTS5 + Wasm im Browser eine ähnliche Performance erreichen könnte

  • 10 Millionen Datensätze sind ein Spielzeug-Datensatz

    • Große Text-Datensätze wie die komplette Wikipedia oder Reddit-Kommentare von vor 2022 wären für Benchmarks besser geeignet

  • Ich habe pg Volltextsuche um 2008 herum zum ersten Mal verwendet

    • Das Problem der Postgres-Volltextsuche ist nicht, dass sie zu langsam ist, sondern dass sie zu unflexibel ist
    • Für das Hinzufügen einfacher Suche ist sie gut, aber zum Feinabstimmen der Suche reicht sie nicht aus
    • Mit Solr und Elasticsearch lassen sich komplexe Index- und Suchverarbeitungen konfigurieren
    • Postgres könnte solche Funktionen übernehmen, bietet derzeit aber nichts dergleichen
    • Postgres trennt anhand von Leerzeichen, und Stopwörter sowie Stemming können manuell verwendet werden
    • Eine Bewertung der Suchergebnisse auf Basis von Feldgewichtungen ist nicht möglich
    • Im Vergleich zu Alternativen ist es ein Spielzeugsystem