Das Problem des Table- und Index-Bloats aufgrund fehlgeschlagener Einfügungen bei Unique Constraints ist bekannt und wurde in verschiedenen Artikeln im Internet diskutiert. Allerdings mangelt es diesen Diskussionen manchmal an einem klaren, praktischen Beispiel mit Messungen, um die Auswirkungen zu veranschaulichen. Und trotz der Vertrautheit mit diesem Problem sehen wir dieses Designmuster – oder besser gesagt Anti-Pattern – immer noch häufig in realen Anwendungen. Entwickler verlassen sich oft auf Unique Constraints, um zu verhindern, dass doppelte Werte in Tabellen eingefügt werden. Obwohl dieser Ansatz unkompliziert, vielseitig und allgemein als effektiv angesehen wird, führen in PostgreSQL Einfügungen, die aufgrund von Verletzungen von Unique Constraints fehlschlagen, leider immer zu Table- und Index-Bloat. Und auf stark frequentierten Systemen kann dieser unnötige Bloat die Disk-I/O und die Häufigkeit von Autovacuum-Läufen erheblich erhöhen. In diesem Artikel möchten wir dieses Problem noch einmal hervorheben und ein einfaches Beispiel mit Messungen zur Veranschaulichung geben. Wir schlagen eine einfache Verbesserung vor, die dazu beitragen kann, dieses Problem zu mildern und die Autovacuum-Auslastung und die Disk-I/O zu reduzieren.

Zwei Ansätze zur Duplikatsvermeidung

In PostgreSQL gibt es zwei Hauptmethoden, um doppelte Werte mithilfe von Unique Constraints zu verhindern:

1. Standard-Insert-Befehl (INSERT INTO table)

Der übliche INSERT INTO table-Befehl versucht, Daten direkt in die Tabelle einzufügen. Wenn das Einfügen zu einem doppelten Wert führen würde, schlägt es mit einem Fehler „duplicate key value violates unique constraint“ fehl. Da der Befehl keine Duplikatsprüfungen spezifiziert, fügt PostgreSQL intern sofort die neue Zeile ein und beginnt erst dann mit der Aktualisierung der Indizes. Wenn eine Verletzung eines Unique Index auftritt, löst dies den Fehler aus und löscht die neu hinzugefügte Zeile. Die Reihenfolge der Indexaktualisierungen wird durch ihre Beziehungs-IDs bestimmt, sodass das Ausmaß des Index-Bloats von der Reihenfolge abhängt, in der Indizes erstellt wurden. Bei wiederholten Fehlern aufgrund von „unique constraint violation“ sammeln sich sowohl in der Tabelle als auch in einigen Indizes gelöschte Datensätze an, was zu Bloat führt, und die resultierenden Schreiboperationen erhöhen die Disk-I/O, ohne ein nützliches Ergebnis zu erzielen.

2. Konfliktbewusstes Einfügen (INSERT INTO table … ON CONFLICT DO NOTHING)

Der Befehl INSERT INTO table ON CONFLICT DO NOTHING verhält sich anders. Da er spezifiziert, dass ein Konflikt auftreten könnte, prüft PostgreSQL zuerst auf potenzielle Duplikate, bevor versucht wird, Daten einzufügen. Wenn ein Duplikat gefunden wird, führt PostgreSQL die angegebene Aktion aus – in diesem Fall „DO NOTHING“ – und es tritt kein Fehler auf. Diese Klausel wurde in PostgreSQL 9.5 eingeführt, aber einige Anwendungen laufen entweder noch auf älteren PostgreSQL-Versionen oder behalten Legacy-Code bei, wenn die Datenbank aktualisiert wird. Infolgedessen wird diese Option zur Konfliktbehandlung oft zu wenig genutzt.

Testbeispiel

Um Tests durchführen zu können, müssen wir PostgreSQL mit „autovacuum=off“ starten. Andernfalls verarbeitet Autovacuum bei meist inaktiver Instanz aufgeblähte Objekte sofort, und es wäre nicht möglich, Statistiken zu erfassen. Wir erstellen ein einfaches Testbeispiel mit mehreren Indizes:

CREATE TABLE IF NOT EXISTS test_unique_constraints(
id serial primary key,
unique_text_key text,
unique_integer_key integer,
some_other_bigint_column bigint,
some_other_text_column text);

CREATE INDEX test_unique_constraints_some_other_bigint_column_idx ON test_unique_constraints (some_other_bigint_column );
CREATE INDEX test_unique_constraints_some_other_text_column_idx ON test_unique_constraints (some_other_text_column );
CREATE INDEX test_unique_constraints_unique_text_key_unique_integer_key__idx ON test_unique_constraints (unique_text_key, unique_integer_key, some_other_bigint_column );
CREATE UNIQUE test_unique_constraints_unique_integer_key_idx INDEX ON test_unique_constraints (unique_text_key );
CREATE UNIQUE test_unique_constraints_unique_text_key_idx INDEX ON test_unique_constraints (unique_integer_key );

Und nun füllen wir diese Tabelle mit eindeutigen Daten:

DO $$
BEGIN
 FOR i IN 1..1000 LOOP 
 INSERT INTO test_unique_constraints
 (unique_text_key, unique_integer_key, some_other_bigint_column, some_other_text_column)
 VALUES (i::text, i, i, i::text);
 END LOOP;
END;
$$;

Im zweiten Schritt verwenden wir ein einfaches Python-Skript, um eine Verbindung zur Datenbank herzustellen, zu versuchen, widersprüchliche Daten einzufügen, und die Sitzung nach einem Fehler zu schließen. Zuerst sendet es 10.000 INSERT-Anweisungen, die mit dem Index „test_unique_constraints_unique_int_key_idx“ in Konflikt stehen, dann weitere 10.000 INSERTs, die mit „test_unique_constraints_unique_text_key_idx“ in Konflikt stehen. Der gesamte Test wird in wenigen Dutzend Sekunden durchgeführt, danach inspizieren wir alle Objekte mit der Erweiterung „pgstattuple“. Die folgende Abfrage listet alle Objekte in einer einzigen Ausgabe auf:

WITH maintable AS (SELECT oid, relname FROM pg_class WHERE relname = 'test_unique_constraints')
SELECT m.oid as relid, m.relname as relation, s.*
FROM maintable m
JOIN LATERAL (SELECT * FROM pgstattuple(m.oid)) s ON true
UNION ALL
SELECT i.indexrelid as relid, indexrelid::regclass::text as relation, s.*
FROM pg_index i
JOIN LATERAL (SELECT * FROM pgstattuple(i.indexrelid)) s ON true
WHERE i.indrelid::regclass::text = 'test_unique_constraints'
ORDER BY relid;

Beobachtete Ergebnisse

Nach mehrmaligem Ausführen des gesamten Tests beobachten wir Folgendes:

• Die Haupttabelle „test_unique_constraints“ hat immer 1.000 Live-Tupel und 20.000 zusätzliche Dead-Records, was zu ca. 85 % Dead-Tupeln in der Tabelle führt
• Der Index auf dem Primärschlüssel zeigt immer 21.000 Tupel an, ohne zu wissen, dass 20.000 dieser Datensätze in der Haupttabelle als gelöscht markiert sind.
• Andere nicht eindeutige Indizes zeigen in verschiedenen Läufen unterschiedliche Ergebnisse, die zwischen 3.000 und 21.000 Datensätzen liegen. Die Zahlen hängen von der Verteilung der Werte ab, die das Skript für die zugrunde liegenden Spalten generiert. Wir haben sowohl wiederholte als auch vollständig eindeutige Werte getestet. Wiederholte Werte führten zu weniger Datensätzen in Indizes, vollständig eindeutige Werte führten zu einer vollständigen Anzahl von 21.000 Datensätzen in diesen Indizes.
• Unique Indizes zeigten wiederholt Tupelanzahlen nur zwischen 1.000 und 1.400 in allen Tests. Der Unique Index auf dem „unique_text_key“ zeigt immer einige Dead-Tupel in der Ausgabe. Eine genaue Erklärung dieser Zahlen würde eine eingehendere Untersuchung dieser Beziehungen und des Codes der pgstattuple-Funktion erfordern, was den Rahmen dieses Artikels sprengen würde. Aber auch hier wird ein geringer Bloat gemeldet.
• Von der pgstattuple-Funktion gemeldete Zahlen warfen Fragen nach ihrer Genauigkeit auf, obwohl die Dokumentation zu dem Schluss zu führen scheint, dass die Zahlen auf Tupelebene genau sein sollten.
• Die anschließende manuelle Vacuum-Operation bestätigt 20.000 Dead-Records in der Haupttabelle und 54 Seiten, die aus dem Primärschlüsselindex entfernt wurden, sowie bis zu mehreren Dutzend Seiten, die aus anderen Indizes entfernt wurden – unterschiedliche Zahlen in jedem Lauf in Abhängigkeit von der Gesamtzahl der Tupel in diesen Beziehungen, wie oben beschrieben.
• Jeder fehlgeschlagene Insert erhöht auch die Transaktions-ID und damit das Transaktionsalter der Datenbank.

Hier ist ein Beispielausgabe aus der oben gezeigten Abfrage nach dem Testlauf, der eindeutige Werte für alle Spalten verwendete. Wie wir sehen können, kann der Bloat von nicht eindeutigen Indizes aufgrund fehlgeschlagener Inserts groß sein.

 relid | relation | table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
-------+-----------------------------------------------------------------+-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
 16418 | test_unique_constraints | 1269760 | 1000 | 51893 | 4.09 | 20000 | 1080000 | 85.06 | 5420 | 0.43
 16424 | test_unique_constraints_pkey | 491520 | 21000 | 336000 | 68.36 | 0 | 0 | 0 | 51444 | 10.47
 16426 | test_unique_constraints_some_other_bigint_column_idx | 581632 | 16396 | 326536 | 56.14 | 0 | 0 | 0 | 168732 | 29.01
 16427 | test_unique_constraints_some_other_text_column_idx | 516096 | 16815 | 327176 | 63.39 | 0 | 0 | 0 | 101392 | 19.65
 16428 | test_unique_constraints_unique_text_key_unique_integer_key__idx | 1015808 | 21000 | 584088 | 57.5 | 0 | 0 | 0 | 323548 | 31.85
 16429 | test_unique_constraints_unique_text_key_idx | 57344 | 1263 | 20208 | 35.24 | 2 | 32 | 0.06 | 15360 | 26.79
 16430 | test_unique_constraints_unique_integer_key_idx | 40960 | 1000 | 16000 | 39.06 | 0 | 0 | 0 | 4404 | 10.75
(7 rows)

In einem zweiten Test modifizieren wir das Skript, um die Klausel ON CONFLICT DO NOTHING in den INSERT-Befehl aufzunehmen, und wiederholen beide Tests. Diesmal führen Inserts nicht zu Fehlern; stattdessen geben sie einfach „INSERT 0 0“ zurück, was anzeigt, dass keine Datensätze eingefügt wurden. Die Überprüfung der Transaktions-ID nach diesem Test zeigt nur einen minimalen Anstieg, der durch Hintergrundprozesse verursacht wird. Versuche, widersprüchliche Daten einzufügen, führten nicht zu einer Erhöhung der Transaktions-ID (XID), da PostgreSQL zuerst nur eine virtuelle Transaktion startete, um auf Konflikte zu prüfen, und weil ein Konflikt gefunden wurde, die Transaktion abbrach, ohne eine neue XID zugewiesen zu haben. Die „pgstattuple“-Ausgabe bestätigt, dass alle Objekte nur Live-Daten enthalten, diesmal ohne Dead-Tupel.

Zusammenfassung

Wie gezeigt, bläht jeder fehlgeschlagene Insert die zugrunde liegende Tabelle und einige Indizes auf und erhöht die Transaktions-ID, da jeder fehlgeschlagene Insert in einer separaten Transaktion erfolgt. Folglich wird Autovacuum gezwungen, häufiger zu laufen, was wertvolle Systemressourcen verbraucht. Daher sollten Anwendungen, die sich immer noch ausschließlich auf einfache INSERT-Befehle ohne ON CONFLICT-Bedingungen verlassen, diese Implementierung überdenken. Aber wie immer sollte die endgültige Entscheidung auf den spezifischen Bedingungen jeder Anwendung basieren.

Veranstaltungsort

Die vierzehnte PGConf.EU fand in Athen statt und erfreut sich weiterhin steigender Beliebtheit. Mit 782 Besuchern konnte die PGConf.EU einmal mehr ihren Besucherrekord übertreffen. Damit ist sie die bisher größte PostgreSQL Konferenz weltweit. Veranstaltungsort war das Divani Caravel Hotel, welches viel Platz für Teilnehmer, Speaker und Sponsoren bot. Als NetApp Open Source Services waren wir zu dritt auf der Konferenz vertreten. Neben mir waren noch Patrick Lauer und Michael Banck vor Ort.

Talks

Nach einer kurzen Begrüßung durch das Organisationsteam machte Stacey Haysler mit der Keynote „The PostgreSQL license fee“ den Anfang, indem Sie hervorhob, wovon PostgreSQL lebt und was es so erfolgreich macht: Die Community, die ihre PostgreSQL Beiträge auf vielfältige Arten leistet und würdigte damit weit mehr als die reine Code-Entwicklung. Anschließend erwartete uns das Programm mit 51 Talks, verteilt auf 3 Tracks, plus extra Sponsorentrack mit zusätzlichen 14 Talks. Die Entscheidung für den einen oder anderen Talk fiel nicht immer leicht.

Michael Banck aus unserem Datenbankteam und Maintainer von Patroni für Debian und Ubuntu, gab in seinem Vortag „Patroni Deployment Patterns“ einen groben Überblick über Patroni und zeigte zusätzlich verschiedene Einsatzmöglichkeiten auf, sowie die Probleme, die dabei auftreten können. Es entwickelte sich ein konstruktiver Austausch mit dem Publikum über die Möglichkeiten, Probleme und die zukünftige Entwicklung von Patroni.

Patroni Deployment Patterns, Michael Banck, PGConf.EU 24

Rafael Thofehrn Castro von EDB stellte in seinem Talk „Debugging active queries with mid-flight instrumented explain plans“ einen Patch vor mit dem es ihm möglich ist, Querypläne zur Laufzeit zu beobachten. Das ist überaus hilfreich bei Queries mit minutenlangen Laufzeiten oder wie im vorgeführten Beispiel mit einer Laufzeit von über einer Stunde. Live Debugging und Optimierung von Queries, die nie fertig werden würden, ist damit denkbar. Die Begeisterung am Ende war nicht zu überhören. Ob und wann das allerdings in PostgreSQL Core landet, ist aktuell noch unklar.

PostgreSQL hat sich laut der aktuellen Stack Overflow Umfrage inzwischen zu Platz 1 der beliebtesten Datenbanken entwickelt. Migrationen beispielsweise von Oracle zu PostgreSQL bleiben daher auch in Zukunft ein wichtiges Thema. Teresa Lopes von Adyen beschrieb in ihrem Vortrag „PostgreSQL for Oracle DBAs – A walk in the park?“ ihre Erfahrungen dabei, was ihr besonders gut an PostgreSQL gefällt und welche gewohnten Features aus Oracle sie noch vermisst.

Zu jedem Talk hat man die Möglichkeit Feedback zu geben, welches sehr wertvoll für die Speaker und das Organisationsteam ist. Im letzten Jahr schienen nur wenige davon Gebrauch gemacht zu haben, weshalb sich das Organisationsteam dieses Jahr dazu entschieden hat einen zusätzlichen Anreiz zu geben. Für jedes abgegebene Feedback steigert man seine Chance auf ein Gratis-Ticket für die nächste PGConf.EU. Alle Talks wurden aufgezeichnet, sofern der jeweilige Speaker der Aufnahme nicht widersprochen hat. Einige wurden bereits auf YouTube veröffentlicht.

Das alljährliche Social-Event fand wieder am Mittwoch, dem ersten Konferenztag, im Konferenzhotel statt und bot Gelegenheit zum Austausch zwischen Teilnehmern, Speakern und Sponsoren. Insgesamt wuchs die Zahl der Sponsoren in diesem Jahr auf insgesamt 35.

Ausblick

Einen besonderen Dank möchten wir dem Organisationsteam aussprechen, welches erneut eine sehr lehrreiche Konferenz auf die Beine gestellt hat. Wir freuen uns bereits auf die nächste PGConf.EU. Bis dahin werden wir auch auf der nächsten PGConf.DE wieder anzutreffen sein.

TOAST (The Oversized Attribute Storage Technique) ist der Mechanismus von PostgreSQL zur Handhabung großer Datenobjekte, die die 8-KB-Datenseitenbegrenzung überschreiten. Eingeführt in PostgreSQL 7.1, ist TOAST eine verbesserte Version des Out-of-Line-Speichermechanismus, der in Oracle-Datenbanken zur Handhabung großer Objekte (LOBs) verwendet wird. Beide Datenbanken speichern Daten variabler Länge entweder inline innerhalb der Tabelle oder in einer separaten Struktur. PostgreSQL begrenzt die maximale Größe eines einzelnen Tupels auf eine Datenseite. Wenn die Größe des Tupels, einschließlich komprimierter Daten in einer Spalte variabler Länge, einen bestimmten Schwellenwert überschreitet, wird der komprimierte Teil in eine separate Datendatei verschoben und automatisch in Chunks aufgeteilt, um die Leistung zu optimieren.

TOAST kann zur Speicherung von langen Texten, Binärdaten in Bytea-Spalten, JSONB-Daten, langen HSTORE-Schlüssel-Wert-Paaren, großen Arrays, umfangreichen XML-Dokumenten oder benutzerdefinierten zusammengesetzten Datentypen verwendet werden. Sein Verhalten wird von zwei Parametern beeinflusst: TOAST_TUPLE_THRESHOLD und TOAST_TUPLE_TARGET. Der erste ist ein fest codierter Parameter, der im PostgreSQL-Quellcode in der Datei heaptoast.h definiert ist, basierend auf der Funktion MaximumBytesPerTuple, die für vier TOAST-Tupel pro Seite berechnet wird, was zu einer Begrenzung von 2000 Byte führt. Dieser fest codierte Schwellenwert verhindert, dass Benutzer zu kleine Werte im Out-of-Line-Speicher ablegen, was die Leistung beeinträchtigen würde. Der zweite Parameter, TOAST_TUPLE_TARGET, ist ein Speicherparameter auf Tabellenebene, der mit demselben Wert wie TOAST_TUPLE_THRESHOLD initialisiert wird, aber für einzelne Tabellen angepasst werden kann. Er definiert die minimale Tupellänge, die erforderlich ist, bevor versucht wird, lange Spaltenwerte zu komprimieren und in TOAST-Tabellen zu verschieben.

In der Quelldatei heaptoast.h erklärt ein Kommentar: „Ist ein Tupel größer als TOAST_TUPLE_THRESHOLD, versuchen wir, es durch Komprimieren komprimierbarer Felder und Verschieben von EXTENDED- und EXTERNAL-Daten Out-of-Line auf nicht mehr als TOAST_TUPLE_TARGET Bytes zu „toasten“. Die Zahlen müssen nicht identisch sein, obwohl sie es derzeit sind. Es ist nicht sinnvoll, dass TARGET THRESHOLD überschreitet, aber es könnte nützlich sein, es kleiner zu machen.“ Das bedeutet, dass in realen Tabellen direkt im Tupel gespeicherte Daten komprimiert sein können oder auch nicht, abhängig von ihrer Größe nach der Komprimierung. Um zu überprüfen, ob Spalten komprimiert sind und welcher Algorithmus verwendet wird, können wir die PostgreSQL-Systemfunktion pg_column_compression verwenden. Zusätzlich hilft die Funktion pg_column_size, die Größe einzelner Spalten zu überprüfen. PostgreSQL 17 führt eine neue Funktion, pg_column_toast_chunk_id, ein, die anzeigt, ob der Wert einer Spalte in der TOAST-Tabelle gespeichert ist.

In den neuesten PostgreSQL-Versionen werden zwei Kompressionsalgorithmen verwendet: PGLZ (PostgreSQL LZ) und LZ4. Beide sind Varianten des LZ77-Algorithmus, wurden aber für unterschiedliche Anwendungsfälle entwickelt. PGLZ eignet sich für gemischte Text- und numerische Daten, wie XML oder JSON in Textform, und bietet ein Gleichgewicht zwischen Kompressionsgeschwindigkeit und -rate. Es verwendet einen Gleitfenstermechanismus, um wiederholte Sequenzen in den Daten zu erkennen, und bietet ein angemessenes Gleichgewicht zwischen Kompressionsgeschwindigkeit und Kompressionsrate. LZ4 hingegen ist eine schnelle Kompressionsmethode, die für Echtzeitszenarien entwickelt wurde. Es bietet Hochgeschwindigkeitskomprimierung und -dekomprimierung, was es ideal für leistungskritische Anwendungen macht. LZ4 ist deutlich schneller als PGLZ, insbesondere bei der Dekomprimierung, und verarbeitet Daten in festen Blöcken (typischerweise 64 KB) unter Verwendung einer Hash-Tabelle, um Übereinstimmungen zu finden. Dieser Algorithmus zeichnet sich bei Binärdaten wie Bildern, Audio- und Videodateien aus.

In meinem internen Forschungsprojekt, das darauf abzielte, die Leistung von JSONB-Daten unter verschiedenen Anwendungsfällen zu verstehen, habe ich mehrere Leistungstests für Abfragen durchgeführt, die JSONB-Daten verarbeiten. Die Ergebnisse einiger Tests zeigten interessante und manchmal überraschende Leistungsunterschiede zwischen diesen Algorithmen. Die vorgestellten Beispiele sind jedoch anekdotisch und können nicht verallgemeinert werden. Ziel dieses Artikels ist es, das Bewusstsein dafür zu schärfen, dass es enorme Leistungsunterschiede geben kann, die je nach spezifischen Daten und Anwendungsfällen sowie der spezifischen Hardware variieren. Daher können diese Ergebnisse nicht blind angewendet werden.

JSONB-Daten werden als binäres Objekt mit einer Baumstruktur gespeichert, wobei Schlüssel und Werte in separaten Zellen gespeichert werden und Schlüssel auf derselben JSON-Ebene in sortierter Reihenfolge abgelegt sind. Verschachtelte Ebenen werden als zusätzliche Baumstrukturen unter ihren entsprechenden Schlüsseln der höheren Ebene gespeichert. Diese Struktur bedeutet, dass das Abrufen von Daten für die ersten Schlüssel in der obersten JSON-Ebene schneller ist als das Abrufen von Werten für stark verschachtelte Schlüssel, die tiefer im Binärbaum gespeichert sind. Obwohl dieser Unterschied normalerweise vernachlässigbar ist, wird er bei Abfragen, die sequentielle Scans über den gesamten Datensatz durchführen, signifikant, da diese kleinen Verzögerungen die Gesamtleistung kumulativ beeinträchtigen können.

Der für die Tests verwendete Datensatz bestand aus historischen GitHub-Ereignissen, die als JSON-Objekte von gharchive.org verfügbar waren und die erste Januarwoche 2023 abdeckten. Ich habe drei verschiedene Tabellen getestet: eine mit PGLZ, eine mit LZ4 und eine mit EXTERNAL-Speicher ohne Komprimierung. Ein Python-Skript lud die Daten herunter, entpackte sie und lud sie in die jeweiligen Tabellen. Jede Tabelle wurde separat geladen, um zu verhindern, dass frühere Operationen das PostgreSQL-Speicherformat beeinflussen.

Die erste bemerkenswerte Beobachtung war der Größenunterschied zwischen den Tabellen. Die Tabelle mit LZ4-Komprimierung war die kleinste mit etwa 38 GB, gefolgt von der Tabelle mit PGLZ mit 41 GB. Die Tabelle mit externem Speicher ohne Komprimierung war mit 98 GB deutlich größer. Da die Testmaschinen nur 32 GB RAM hatten, passte keine der Tabellen vollständig in den Speicher, was die Festplatten-I/O zu einem wichtigen Leistungsfaktor machte. Etwa ein Drittel der Datensätze wurde in TOAST-Tabellen gespeichert, was eine typische Datengrößenverteilung widerspiegelte, wie sie von unseren Kunden beobachtet wird.

Um Caching-Effekte zu minimieren, habe ich mehrere Tests mit mehreren parallelen Sitzungen durchgeführt, die Testabfragen mit jeweils zufällig gewählten Parametern ausführten. Zusätzlich zu Anwendungsfällen mit verschiedenen Indextypen führte ich auch sequentielle Scans über die gesamte Tabelle durch. Die Tests wurden mit unterschiedlicher Anzahl paralleler Sitzungen wiederholt, um genügend Datenpunkte zu sammeln, und dieselben Tests wurden an allen drei Tabellen mit verschiedenen Kompressionsalgorithmen durchgeführt.

Die erste Grafik zeigt die Ergebnisse von SELECT-Abfragen, die sequentielle Scans durchführen und JSON-Schlüssel abrufen, die am Anfang des JSONB-Binärobjekts gespeichert sind. Wie erwartet bietet externer Speicher ohne Komprimierung (blaue Linie) eine nahezu lineare Leistung, wobei die Festplatten-I/O der Hauptfaktor ist. Auf einer 8-Kern-Maschine liefert der PGLZ-Algorithmus (rote Linie) unter geringeren Lasten eine recht gute Leistung. Sobald jedoch die Anzahl der parallelen Abfragen die Anzahl der verfügbaren CPU-Kerne (8) erreicht, beginnt seine Leistung abzunehmen und wird schlechter als die Leistung unkomprimierter Daten. Unter höheren Lasten wird er zu einem ernsthaften Engpass. Im Gegensatz dazu verarbeitet LZ4 (grüne Linie) parallele Abfragen außergewöhnlich gut und behält eine bessere Leistung als unkomprimierte Daten bei, selbst bei bis zu 32 parallelen Abfragen auf 8 Kernen.

Der zweite Test zielte auf JSONB-Schlüssel ab, die an verschiedenen Positionen (Anfang, Mitte und Ende) innerhalb des JSONB-Binärobjekts gespeichert sind. Die Ergebnisse, gemessen auf einer 20-Kern-Maschine, zeigen, dass PGLZ (rote Linie) von Anfang an langsamer ist als die unkomprimierte Tabelle. In diesem Fall verschlechtert sich die Leistung von PGLZ linear statt geometrisch, liegt aber immer noch deutlich hinter LZ4 (grüne Linie). LZ4 übertraf während des gesamten Tests durchweg die unkomprimierten Daten.

Wenn wir uns jedoch entscheiden, den Kompressionsalgorithmus zu ändern, ändert das einfache Erstellen einer neuen Tabelle mit der Einstellung default_toast_compression auf „lz4“ und das Ausführen von INSERT INTO my_table_lz4 SELECT * FROM my_table_pglz; den Kompressionsalgorithmus bestehender Datensätze nicht. Jeder bereits komprimierte Datensatz behält seinen ursprünglichen Kompressionsalgorithmus bei. Sie können die Systemfunktion pg_column_compression verwenden, um zu überprüfen, welcher Algorithmus für jeden Datensatz verwendet wurde. Die Standard-Komprimierungseinstellung gilt nur für neue, unkomprimierte Daten; alte, bereits komprimierte Daten werden unverändert kopiert.

Um alte Daten wirklich in einen anderen Kompressionsalgorithmus zu konvertieren, müssen wir sie über Text umwandeln. Für JSONB-Daten würden wir eine Abfrage wie diese verwenden: INSERT INTO my_table_lz4 (jsonb_data, …) SELECT jsonb_data::text::jsonb, … FROM my_table_pglz; Dies stellt sicher, dass alte Daten mit der neuen LZ4-Komprimierung gespeichert werden. Dieser Prozess kann jedoch zeit- und ressourcenintensiv sein, daher ist es wichtig, die Vorteile abzuwägen, bevor man ihn in Angriff nimmt.

Zusammenfassend lässt sich sagen: Meine Tests zeigten signifikante Leistungsunterschiede zwischen den Algorithmen PGLZ und LZ4 zur Speicherung komprimierter JSONB-Daten. Diese Unterschiede sind besonders ausgeprägt, wenn die Maschine unter hoher paralleler Last steht. Die Tests zeigten eine starke Leistungsverschlechterung bei Daten, die mit dem PGLZ-Algorithmus gespeichert wurden, wenn die Anzahl der parallelen Sitzungen die Anzahl der verfügbaren Kerne überschritt. In einigen Fällen schnitt PGLZ von Anfang an schlechter ab als unkomprimierte Daten. Im Gegensatz dazu übertraf LZ4 sowohl unkomprimierte als auch PGLZ-komprimierte Daten durchweg, insbesondere unter hoher Last. Die Festlegung von LZ4 als Standardkomprimierung für neue Daten scheint die richtige Wahl zu sein, und einige Cloud-Anbieter haben diesen Ansatz bereits übernommen. Diese Ergebnisse sollten jedoch nicht blind auf bestehende Daten angewendet werden. Sie sollten Ihre spezifischen Anwendungsfälle und Daten testen, um festzustellen, ob eine Konvertierung den Zeit- und Ressourcenaufwand wert ist, da die Datenkonvertierung ein Umwandeln erfordert und ein ressourcenintensiver Prozess sein kann.

Einführung

Das Ausführen von ANALYZE (entweder explizit oder über Auto-Analyze) ist sehr wichtig, um aktuelle Datenstatistiken für den Postgres-Query-Planer zu haben. Insbesondere nach In-Place-Upgrades über muss ausgeführt werden, um überhaupt Abfragestatistiken zu erhalten. Da ANALYZE nur Teile der Blöcke in einer Tabelle abtastet, ähnelt das I/O-Muster eher einem Direktzugriff als einem sequenziellen Lesen. Version 14 von Postgres hat die Möglichkeit erhalten, Prefetching zu verwenden (falls verfügbar, was aber unter Linux der Fall ist), um dem Betriebssystemkernel mitzuteilen, welche Blöcke als Nächstes betrachtet werden. Dies wird über den Konfigurationsparameter maintenenance_io_concurrency gesteuert, der standardmäßig auf 10 gesetzt ist (im Gegensatz zu effective_io_concurrency, der standardmäßig auf 1 gesetzt ist).

Benchmark

Um die Änderungen zwischen Version 13 und 14 zu testen und zu demonstrieren, haben wir einige kurze Benchmarks mit den aktuellen Wartungsversionen (13.16 und 14.13) auf Debian 12 mit Paketen von https://apt.postgresql.org durchgeführt. Hardwareseitig wurde ein ThinkPad T14s Gen 3 mit einer Intel i7-1280P CPU mit 20 Kernen und 32 GB RAM verwendet. Die Basis ist eine pgbench-Datenbank, die mit einem Skalierungsfaktor von 1000 initialisiert wurde:

    $ pgbench -i -I dtg -s 1000 -d pgbench

Dadurch werden 100 Millionen Zeilen erstellt, was zu einer Datenbankgröße von etwa 15 GB führt. Um etwas mehr Arbeit zu geben, erhöhen wir von den standardmäßigen 100 auf den gleichen Wert wie den pgbench-Skalierungsfaktor (d. h. 1000). Dies führt dazu, dass etwa 20 % aller Blöcke scannt. Anschließend analysieren wir die pgbench-Haupttabelle, pgbench_accounts:

    $ vacuumdb -Z -v -d pgbench -t pgbench_accounts
    INFO:  analyzing "public.pgbench_accounts"
    INFO:  "pgbench_accounts": scanned 300000 of 1639345 pages,
           containing 18300000 live rows and 0 dead rows;
           300000 rows in sample, 100000045 estimated total rows

Zwischen den Durchläufen wird der Dateisystem-Seitencache über echo 3 | sudo tee /proc/sys/vm/drop_caches gelöscht und alle Durchläufe werden dreimal wiederholt. Die folgende Tabelle listet die Laufzeiten (in Sekunden) des obigen vacuumdb-Befehls für verschiedene Einstellungen von maintenance_io_concurrency auf:

Analyse

Zwei Dinge gehen aus diesen Zahlen deutlich hervor: Erstens ändern sich die Laufzeiten für Version 13 nicht, der Wert von maintenance_io_concurrency hat für diese Version keine Auswirkung. Zweitens, sobald das Prefetching für Version 14 einsetzt ( ist 5 oder mehr), wird um ein Vielfaches schneller, bis zu einem Faktor von 6-7x. Der Standardwert von von 10 ist bereits 3-4x schneller, und Werte größer als 50 zeigen nur geringfügige weitere Verbesserungen, zumindest für diesen Benchmark auf dieser Hardware. Bemerkenswert ist auch, dass die Laufzeiten, wenn Prefetching deaktiviert ist (maintenance_io_concurrency=0) oder nur auf 1 gesetzt ist, schlechter sind als bei Version 13, aber da der Standardwert für maintenance_io_concurrency 10 ist, sollte dies in der Praxis niemanden betreffen.

Fazit

Das Aktivieren von Prefetching für ANALYZE in Version 14 von PostgreSQL hat die Statistikabtastung erheblich beschleunigt. Der Standardwert von 10 für ist bereits recht gut, aber wir empfehlen, ihn auf 20-50 (oder höher) zu erhöhen, falls hochleistungsfähiger lokaler NVME-Speicher verwendet wird. In einem zukünftigen kurzen Benchmark planen wir, die -Leistung für die Hauptversionen seit 14 zu vergleichen. Insbesondere die kommende Version 17 verspricht aufgrund der neuen Streaming-I/O-Schnittstelle weitere Verbesserungen für ANALYZE.

• Tomas Vondra präsentierte „The Past and the Future of the Postgres Community“, wobei er erklärte, wie die Arbeit an PostgreSQL-Änderungen und -Korrekturen in Commitfests organisiert ist und zukünftige Entwicklungsideen innerhalb der Community diskutierte.
• Laurenz Albes Vortrag, „Sicherheitsattacken auf PostgreSQL“, beleuchtete mehrere potenzielle Angriffsvektoren in PostgreSQL und erregte mit überraschenden Beispielen große Aufmerksamkeit.
• Chris Engelberts Präsentation, „PostgreSQL on Kubernetes: Dos and Don’ts“, behandelte die Hauptprobleme im Zusammenhang mit dem Betrieb von PostgreSQL auf Kubernetes und diskutierte Lösungen, einschließlich der Vor- und Nachteile bestehender PostgreSQL Kubernetes Operatoren.
• Maurizio De Giorgi und Ismael Posada Trobo diskutierten „Solving PostgreSQL Connection Scalability Issues: Insights from CERN’s GitLab Service“ und erläuterten die Herausforderungen und Lösungen für die Skalierbarkeit in CERNs riesiger Datenbankumgebung.
• Dirk Krautschicks Vortrag, „Warum sich PostgreSQL-Fans auch für Kafka und Debezium interessieren sollten?“, zeigte Beispiele für die Verwendung von Debezium-Konnektoren und Kafka mit PostgreSQL für verschiedene Anwendungsfälle, einschließlich Datenmigrationen.
• Patrick Stählin diskutierte „Wie wir einen Datenkorruptions-Bug mit der Hilfe der Community gefunden und gefixt haben“, wobei er Probleme mit Free-Space-Map-Dateien nach der Migration auf PostgreSQL 16 ansprach.
• Marion Baumgartners Präsentation, „Geodaten-Management mit PostGIS“, lieferte interessante Details zur Verarbeitung von Geodaten in PostgreSQL mithilfe der PostGIS-Erweiterung.
• Prof. Stefan Keller, einer der Hauptorganisatoren und Professor für Data Engineering an der OST Universität Rapperswil, präsentierte „PostgreSQL: A Reliable and Extensible Multi-Model SQL Database“, wobei er die Multi-Modell-Struktur von PostgreSQL inmitten des schwindenden Interesses an NoSQL-Lösungen diskutierte.
• Luigi Nardi von DBTune präsentierte „Lessons Learned from Autotuning PostgreSQL“ und beschrieb ein KI-basiertes Performance-Tuning-Tool, das von seinem Unternehmen entwickelt wurde.
• Kanhaiya Lal und Belma Canik gingen auf „Beyond Keywords: AI-powered Text Search with pgvector for PostgreSQL“ ein und untersuchten die Verwendung der pgvector-Erweiterung zur Verbesserung der Volltextsuchfunktionen in PostgreSQL.
• Gabriele Bartolini, der Entwickler des PostgreSQL Kubernetes Operators „CloudNativePG“, diskutierte in seinem Vortrag „Unleashing the Power of PostgreSQL in Kubernetes“ die Geschichte und Fähigkeiten dieses Operators.

Die Deutsche Postgres Konferenz fand dieses Jahr zum 8. Mal statt, dieses Mal am 12. April in München. Es war die bisher größte PGConf.DE mit 270 Registrierungen, 16 mehr als letztes Jahr. Auf der Konferenz waren wir mit sieben Kollegen vertreten, sodass wir uns über die drei parallelen Tracks plus einen Sponsor-Track aufteilen konnten. Einer der drei Tracks hatte deutschsprachige Vorträge, wo auch Michael Banck in seinem Vortrag die drei führenden Cloud Provider (Amazon RDS, Google Cloud SQL, Microsoft Azure Database) miteinander verglich zudem es auch positives Feedback gab.

Michael Banck bei seinem Vortrag – PostgreSQL-As-A-Service: Vergleich von Cloud Providern

Simon Riggs, eine wichtige Person in der PostgreSQL-Community verstarb leider zwei Wochen vor der Konferenz. In den Vorträgen gab es große Anteilnahme an seinem Verlust und Gülçin Jelinek gedachte seiner gleich zu Beginn der Konferenz. Das letzte mal trafen wir ihn Ende 2023 in Prag auf der PGConf.EU, wo er die Keynote hielt.

Am Tag vor der Konferenz nahm mein Kollege Michael Banck an dem zweiten Patroni Contributor Meeting teil. Das erste Meeting war letztes Jahr vor der letzten Deutschen Postgres Konferenz in Essen. Zu dem Treffen kamen die beiden Patroni-Maintainer Alexander Kukushkin und Polina Bungina, sowie Verteter von EnterpriseDB, Cybertec und Data Egret. Es wurden einige organisatorische Fragen (z.B. über das Logo) geklärt, die Roadmap besprochen sowie einige vorgeschlagene Features für Multi-Site Patroni diskutiert. Nach dem Patroni Meeting war am Abend das Organiser- und Speaker-Dinner im Gasthaus Eder in der Nähe des Konferenz-Hotels.

Der Feature Freeze für PostgreSQL 17 war nur wenige Tage vor der Konferenz am 08. April. Daher wurden die damit verbundenen Verbesserungen in den Vorträgen berücksichtigt oder befassten sich vollständig mit einem neuen Feature. Dazu zählen unter anderem native inkrementelle Backups und Migration von logical replication Slots bei Major Upgrades. Die Veröffentlichung von PostgreSQL 17 ist für September geplant. Bis dahin könnten einzelne Features noch entfernt werden, falls Probleme auftreten, die nicht rechtzeitig gelöst werden können.

Insgesamt war die Qualität der Vorträge sehr hoch, sodass man aus jedem Vortrag etwas interessantes mitnehmen konnte. Deshalb freuen wir uns bereits auf die kommende PGConf.EU im Oktober in Athen.

Übersicht

Was ist das Hauptproblem bei der Aufblähung von Systemtabellen?

Beispiel für eine Berichtstabelle in einer Buchhaltungssoftware

CREATE TEMP TABLE pivot_temp_table (
  id serial PRIMARY KEY,
  inserted_at timestamp DEFAULT current_timestamp,
  client_id INTEGER,
  name text NOT NULL,
  address text NOT NULL,
  loyalty_program BOOLEAN DEFAULT false,
  loyalty_program_start TIMESTAMP,
  orders_202301_count_of_orders INTEGER DEFAULT 0,
  orders_202301_total_price NUMERIC DEFAULT 0,
  ...
  orders_202312_count_of_orders INTEGER DEFAULT 0,
  orders_202312_total_price NUMERIC DEFAULT 0);

CREATE INDEX pivot_temp_table_idx1 ON pivot_temp_table (client_id);
CREATE INDEX pivot_temp_table_idx2 ON pivot_temp_table (name);
CREATE INDEX pivot_temp_table_idx3 ON pivot_temp_table (loyalty_program);
CREATE INDEX pivot_temp_table_idx4 ON pivot_temp_table (loyalty_program_start);

Tabelle pg_attribute

SELECT
  c.relname as table_name,
  o.rolname as table_owner,
  c.relkind as table_type,
  a.attname as column_name,
  a.attnum as column_number,
  a.atttypid::regtype as column_data_type,
  pg_get_expr(adbin, adrelid) as sql_command
FROM pg_attrdef ad
JOIN pg_attribute a ON ad.adrelid = a.attrelid AND ad.adnum = a.attnum
JOIN pg_class c ON c.oid = ad.adrelid
JOIN pg_authid o ON o.oid = c.relowner
WHERE c.relname = 'pivot_temp_table'
ORDER BY table_name, column_number;

  table_name | table_owner | table_type | column_name | column_number | column_data_type | sql_command
------------------+-------------+------------+-------------------------------+---------------+-----------------------------+----------------------------------------------
  pivot_temp_table | postgres | r | id | 1 | integer | nextval('pivot_temp_table_id_seq'::regclass)
  pivot_temp_table | postgres | r | inserted_at | 2 | timestamp without time zone | CURRENT_TIMESTAMP
  pivot_temp_table | postgres | r | loyalty_program | 6 | boolean | false
  pivot_temp_table | postgres | r | orders_202301_count_of_orders | 8 | integer | 0
  pivot_temp_table | postgres | r | orders_202301_total_price | 9 | numeric | 0
-->  bis zur Spalte "orders_202312_total_price"

Tabelle pg_class

Zusammenfassung dieses Beispiels

Beispiel für einen Online-Shop

Analyse des Datenverkehrs

WITH tablenames AS (SELECT tablename FROM (VALUES('pg_attribute'),('pg_attrdef'),('pg_class')) as t(tablename))
SELECT
  tablename,
  now() as checked_at,
  pg_relation_size(tablename) as relation_size,
  pg_relation_size(tablename) / (8*1024) as relation_pages,
  a.*,
  s.*
FROM tablenames t
JOIN LATERAL (SELECT * FROM pgstattuple(t.tablename)) s ON true
JOIN LATERAL (SELECT last_autovacuum, last_vacuum, last_autoanalyze, last_analyze, n_live_tup, n_dead_tup
FROM pg_stat_all_tables WHERE relname = t.tablename) a ON true
ORDER BY tablename

  tablename | pg_attribute
  checked_at | 2024-02-18 10:46:34.348105+00
  relation_size | 44949504
  relation_pages | 5487
  last_autovacuum | 2024-02-16 20:07:15.7767+00
  last_vacuum | 2024-02-16 20:55:50.685706+00
  last_autoanalyze | 2024-02-16 20:07:15.798466+00
  last_analyze | 2024-02-17 22:05:43.19133+00
  n_live_tup | 3401
  n_dead_tup | 188221
  table_len | 44949504
  tuple_count | 3401
  tuple_len | 476732
  tuple_percent | 1.06
  dead_tuple_count | 107576
  dead_tuple_len | 15060640
  dead_tuple_percent| 33.51
  free_space | 28038420
  free_percent | 62.38

WITH pages AS (
  SELECT * FROM generate_series(0, (SELECT pg_relation_size('pg_attribute') / 8192) -1) as pagenum),
tuples_per_page AS (
  SELECT pagenum, nullif(sum((t_xmin is not null)::int), 0) as tuples_per_page
  FROM pages JOIN LATERAL (SELECT * FROM heap_page_items(get_raw_page('pg_attribute',pagenum))) a ON true
  GROUP BY pagenum)
SELECT
  count(*) as pages_total,
  min(tuples_per_page) as min_tuples_per_page,
  max(tuples_per_page) as max_tuples_per_page,
  round(avg(tuples_per_page),0) as avg_tuples_per_page,
  mode() within group (order by tuples_per_page) as mode_tuples_per_page
FROM tuples_per_page

  pages_total | 5487
  min_tuples_per_page | 1
  max_tuples_per_page | 55
  avg_tuples_per_page | 23
  mode_tuples_per_page | 28

Die PostgreSQL 2024Q1 Back-Branch-Releases 16.2, 15.6, 14.11, 13.14 und 12.18 wurden am 8. Februar 2024 veröffentlicht. Neben der Behebung eines Sicherheitsproblems (CVE-2024-0985) und der üblichen Fehler sind sie insofern einzigartig, als sie zwei Leistungsprobleme durch das Backporting von Korrekturen beheben, die bereits zuvor in den Master-Branch eingeführt wurden. In diesem Blogbeitrag beschreiben wir zwei kurze Benchmarks, die zeigen, wie sich die neuen Punkt-Releases verbessert haben. Die Benchmarks wurden auf einem ThinkPad T14s Gen 3 durchgeführt, das über eine Intel i7-1280P CPU mit 20 Kernen und 32 GB RAM verfügt.

Skalierbarkeitsverbesserungen bei hoher Konkurrenz

Die Leistungsverbesserungen in den 2024Q1 Punkt-Releases betreffen Verbesserungen der Locking-Skalierbarkeit bei hohen Client-Zahlen, d.h. wenn das System unter starker Konkurrenz steht. Benchmarks hatten gezeigt, dass die Leistung bei einem pgbench-Lauf mit mehr als 128 Clients dramatisch abnahm. Der ursprüngliche Commit zum Master (der anschließend mit Version 16 veröffentlicht wurde) stammt vom November 2022. Er wurde nun in die Back-Branches eingeführt, da Version 16 bereits getestet wurde und die Ergebnisse vielversprechend waren.

Der von uns verwendete Benchmark ist an diesen Beitrag des Patch-Autors angepasst und besteht aus einem engen pgbench-Lauf, der einfach SELECT txid_current() für jeweils fünf Sekunden bei steigender Client-Anzahl ausführt und die Transaktionen pro Sekunde misst:

$ cat /tmp/txid.sql
SELECT txid_current();
$ for c in 1 2 4 8 16 32 64 96 128 192 256 384 512 768 1024 1536;
> do echo -n "$c ";pgbench -n -M prepared -f /tmp/txid.sql -c$c -j$c -T5 2>&1|grep '^tps'|awk '{print $3}';
> done

Die folgende Grafik zeigt die durchschnittlichen Transaktionen pro Sekunde (tps) über 3 Läufe mit steigender Client-Anzahl (1-1536 Clients), unter Verwendung der Debian 12 Pakete für Version 15, wobei das 2023Q4-Release (15.5, Paket postgresql-15_15.5-0+deb12u1) mit dem 2024Q1-Release (15.6, Paket postgresql-15_15.6-0+deb12u1) verglichen wird:

Die tps-Werte sind im Wesentlichen bis zu 128 Clients gleich, während danach die Transaktionszahlen von 15.5 vom Spitzenwert von 650.000 um das 10-fache auf 65.000 sinken. Das neue 15.6-Release hält die Transaktionszahl wesentlich besser und erreicht immer noch etwa 300.000 tps bei 1536 Clients, was eine 4,5-fache Steigerung des 2024Q1-Releases im Vergleich zu zuvor darstellt.

Dieser Benchmark ist natürlich ein Best-Case, ein künstliches Szenario, aber er zeigt, dass das neueste Punkt-Release von Postgres die Skalierbarkeit bei stark umkämpften Locking-Szenarien dramatisch verbessern kann.

JIT-Speicherverbrauchsverbesserungen

JIT (Just-in-Time-Kompilierung mit LLVM) wurde in Version 11 von Postgres eingeführt und in Version 13 zum Standard gemacht. Seit langem ist bekannt, dass lang laufende PostgreSQL-Sitzungen, die wiederholt JIT-Abfragen ausführen, Speicherlecks aufweisen. Es gab mehrere Fehlerberichte dazu, darunter einige weitere im Debian-Bugtracker und wahrscheinlich auch anderswo.

Dies wurde als auf JIT-Inlining zurückzuführen diagnostiziert, und ein Workaround besteht darin, jit_inline_above_cost von dem Standardwert 500.000 auf -1 zu setzen. Dies deaktiviert jedoch das JIT-Inlining vollständig. Die 2024Q1 Back-Branch-Releases enthalten einen Backport einer Änderung, die in Version 17 enthalten sein wird: Nach jeweils 100 Abfragen werden die LLVM-Caches gelöscht und neu erstellt, wodurch das Speicherleck behoben wird.

Um zu zeigen, wie sich der Speicherverbrauch verbessert hat, verwenden wir den Testfall aus diesem Fehlerbericht. Der Benchmark wird wie folgt vorbereitet:

CREATE TABLE IF NOT EXISTS public.leak_test
(
   id integer NOT NULL,
   CONSTRAINT leak_test_pkey PRIMARY KEY (id)
);

INSERT INTO leak_test(id)
   SELECT id
   FROM generate_series(1,100000) id
ON CONFLICT DO NOTHING;

Anschließend wird die Prozess-ID des Backends notiert und die im Fehlerbericht erwähnte SQL-Abfrage 5000 Mal in einer Schleife ausgeführt:

=> SELECT pg_backend_pid();
 pg_backend_pid
----------------
         623404

=> DO $$DECLARE loop_cnt integer;
-> BEGIN
->   loop_cnt := 5000;
->   LOOP
->     PERFORM
->       id,
->       (SELECT count(*) FROM leak_test x WHERE x.id=l.id) as x_result,
->       (SELECT count(*) FROM leak_test y WHERE y.id=l.id) as y_result
->       /* Leaks memory around 80 kB on each query, but only if two sub-queries are used. */
->     FROM leak_test l;
->     loop_cnt := loop_cnt - 1;
->     EXIT WHEN loop_cnt = 0;
->   END LOOP;
-> END$$;

Dabei wird der Speicherverbrauch des Postgres-Backends über pidstat aufgezeichnet:

pidstat -r -hl -p 623404 2 | tee -a leak_test.log.15.6
Linux 6.1.0-18-amd64 (mbanck-lin-0.credativ.de)     15.02.2024  _x86_64_    (20 CPU)

# Time        UID       PID  minflt/s  majflt/s     VSZ     RSS   %MEM  Command
12:48:56      118    623404      0,00      0,00  381856   91504   0,28  postgres: 15/main: postgres postgres [local] SELECT
12:48:58      118    623404      0,00      0,00  381856   91504   0,28  postgres: 15/main: postgres postgres [local] SELECT
12:49:00      118    623404      0,00      0,00  381856   91504   0,28  postgres: 15/main: postgres postgres [local] SELECT
12:49:02      118    623404      0,00      0,00  381856   91504   0,28  postgres: 15/main: postgres postgres [local] SELECT
12:49:04      118    623404   7113,00      0,00  393632  109252   0,34  postgres: 15/main: postgres postgres [local] SELECT
12:49:06      118    623404  13219,00      0,00  394556  109508   0,34  postgres: 15/main: postgres postgres [local] SELECT
12:49:08      118    623404  14376,00      0,00  395384  108228   0,33  postgres: 15/main: postgres postgres [local] SELECT
[...]

Der Benchmark wird erneut für die Debian 12 Pakete 15.5 und 15.6 wiederholt (die beide gegen LLVM-14 gelinkt sind) und der RSS-Speicherverbrauch, wie von pidstat berichtet, wird gegen die Zeit aufgetragen:

Während der Speicherverbrauch der 15.5-Sitzung linear über die Zeit von 100 auf 600 MB ansteigt, bleibt er bei 15.6 mehr oder weniger konstant bei etwa 100 MB. Dies ist eine große Verbesserung, die JIT für größere Installationen mit lang laufenden Sitzungen, bei denen bisher die übliche Empfehlung darin bestand, JIT vollständig zu deaktivieren, wesentlich nutzbarer machen wird.

Fazit

Das 2024Q1 Patch-Release enthält wichtige Leistungsverbesserungen für die Lock-Skalierbarkeit und den JIT-Speicherverbrauch, die wir in diesem Blogbeitrag demonstriert haben. Darüber hinaus enthält das Patch-Release weitere wichtige Fehlerbehebungen und einen Sicherheitspatch für CVE-2024-0985. Dieses Sicherheitsproblem ist auf materialisierte Views beschränkt, und ein Administrator muss dazu verleitet werden, eine bösartige materialisierte View im Namen eines Angreifers neu zu erstellen. Es hat jedoch einige deutsche Presseberichterstattung erfahren, sodass einige unserer Kunden besonders darauf aufmerksam gemacht wurden und uns um Unterstützung bei ihren kleineren Upgrades baten. Im Allgemeinen sind Postgres-Patch-Releases risikoarm und unaufdringlich (einfach die aktualisierten Pakete installieren und die Postgres-Instanzen neu starten, falls das Paket dies nicht selbst getan hat), sodass sie immer so schnell wie möglich eingesetzt werden sollten.

Mit der Volltextsuche von PostgreSQL® werden die Indizierung von Dokumenten in natürlicher Sprache und die Identifizierung von indizierten Dokumenten, die einer bestimmten Anfrage entsprechen, erleichtert. Übereinstimmende Dokumente können nach ihrer Relevanz für die Suchanfrage sortiert werden, und es können Auszüge aus den Dokumenten erstellt werden, in denen die übereinstimmenden Begriffe hervorgehoben sind. Es wird eine Reihe von SQL-Datentypen, -Operatoren und -Funktionen bereitgestellt, um die Indizierung, Abfrage und Einstufung von Dokumenten zu unterstützen.

PostgreSQL verwendet den Begriff Dokument für jedes Textfragment in natürlicher Sprache – im Wesentlichen Zeichenfolgen, die lesbare Wörter enthalten und durch Leerzeichen und Interpunktion getrennt sind. Dokumente sind oft als Text-Spalten gespeichert, können aber auch dynamisch erzeugt werden, z. B. durch Verkettung mehrerer Spalten (auch aus mehreren Tabellen).

-- concatenating multiple columns to form a document.
-- the table contains chapters from eBooks, one row per chapter
SELECT book_title || ' ' || book_author || ' ' || title || ' ' || body_html AS document
FROM chapters

Einige Beispiele für natürlichsprachliche Texte sind Blogbeiträge (wie der, den Sie gerade lesen), Bücher, Aufsätze, Benutzerkommentare, Forenbeiträge, Nachrichten in sozialen Medien, E-Mails, Newsgroup- und Chat-Nachrichten, Zeitungs- und Zeitschriftenartikel sowie Produktbeschreibungen in Katalogen. Diese Arten von Dokumenten sind typische Kandidaten für die Indizierung durch die Volltextsuche.

Beachten Sie, dass nicht alle lesbaren Zeichenfolgen natürliche Sprache enthalten. So sind beispielsweise Benutzernamen, Kennwörter und URLs oft lesbar, enthalten aber in der Regel keine natürliche Sprache.

Was gibt es an LIKE nicht zu mögen?

Der Datentyp Text verfügt über mehrere Operatoren zur Durchführung grundlegender Zeichenfolgenmuster-Abgleiche, insbesondere LIKE/ ILIKE (SQL-Platzhalterübereinstimmung unter Beachtung bzw. Nichtbeachtung von Groß- und Kleinschreibung), SIMILAR TO (SQL-Regex) und ~ (POSIX-Regex).

Zwar können mit diesen Operatoren sehr einfache Suchvorgänge durchgeführt werden, jedoch ist der Musterabgleich aufgrund verschiedener Einschränkungen nicht gerade ideal für die Implementierung nützlicher Suchvorgänge. Diesen Operatoren mangelt es an linguistischer Unterstützung, z. B. dem Verstehen der Textstruktur (einschließlich Zeichensetzung), dem Erkennen von Wortvarianten und Synonymen und dem Ignorieren häufig verwendeter Wörter. Sie sind zudem nicht in der Lage, die Ergebnisse nach ihrer Relevanz für die Abfrage zu ordnen, und vor allem können sie aufgrund der begrenzten Indizierungsunterstützung langsam sein.

Um uns mit einigen dieser Einschränkungen zu befassen, sehen wir uns ein paar typische Anforderungen an eine umfassende Suchfunktion einer Anwendung/Website an:

• Die Benutzer geben Suchbegriffe ein, die in Abfragen an die Datenbank umgewandelt werden. Anschließend werden dem Benutzer die Ergebnisse angezeigt.
• Die Suche sollte unabhängig von Groß- und Kleinschreibung erfolgen.
• Wörter in der Abfrage sollten mit Varianten (wie z. B. Suffixen) dieses Wortes im Dokument übereinstimmen, z. B. „cat“ sollte mit „cats“ übereinstimmen (und umgekehrt).
• Dokumente mit verwandten Wörtern/Synonymen sollten gefunden werden, z. B. Dokumente, die „feline“ oder „kitten“ enthalten, sollten bei der Suche nach „cat“ ebenfalls gefunden werden.
• Es kann nach Phrasen gesucht werden (oft in doppelten Anführungszeichen, z. B. „the fat black cat“).
• Die Benutzer können bestimmte Wörter markieren, die ausgeschlossen werden sollen (z. B., indem sie dem Wort einen Bindestrich voranstellen: cat -fat).
• Die Ergebnisse sind nach einer Art Relevanzmetrik geordnet, die sich auf die Suchanfrage des Nutzers bezieht. Wenn z. B. ein Dokument das Wort „cat“ (oder Varianten davon) mehrfach enthält und ein anderes Dokument „cat“ nur einmal erwähnt, wird das erste Dokument höher eingestuft.

Diese Anforderungen wurden absichtlich vage gehalten, da sie oft von den Details der Anwendung abhängen.

Für die folgenden Beispielabfragen wurden folgende Tabellendefinition und Daten verwendet:

CREATE TABLE example AS
  SELECT * FROM (
      VALUES ('the fat black cat chased after the rat'),
             ('one cat, two cats, many cats!'),
             ('the kitten and the dog played together'),
             ('that is one fine feline'),
             ('it is raining cats and dogs'),
             ('after eating the whole lasagne, he was a fat cat.'),
             ('don''t go into the catacombs after dark'),
             ('the bobcat has a spotted coat'),
             ('check the library catalog'),
             ('for a filesystem with deduplication look at zfs'),
             ('add one or more predicates to the query')
  ) AS t (document);

Würde die Anwendung Suchvorgänge mit dem ILIKE-Operator (der die Groß- und Kleinschreibung nicht berücksichtigt) durchführen, könnte die Abfrage eines Benutzers nach „cat“ in SQL wie folgt umgeschrieben werden: ILIKE '%cat%'. Damit würde jedes Dokument gefunden werden, das die Zeichenfolge „cat“ enthält. Diese Abfrage findet jedoch auch Dokumente mit Wörtern, die die Teilzeichenfolge „cat“ enthalten. Einige Übereinstimmungen, wie z. B. „cats“ wären relevant für die Anfrage. Andere, wie „cathode“, „catalog“, „deduplication“ und „predicate“, sind jedoch eher keine relevanten Ergebnisse.

-- this finds too many irrelevant results.
SELECT document, document ILIKE '%cat%' AS matches FROM example;

+-------------------------------------------------+-------+
|document                                         |matches|
+-------------------------------------------------+-------+
|the cat chased after the rat                     |true   |
|one cat, two cats, many cats!                    |true   |
|the kitten and the dog played together           |false  |
|that is one fine feline                          |false  |
|it is raining cats and dogs                      |true   |
|after eating the whole lasagne, he was a fat cat.|true   |
|don't go into the catacombs after dark           |true   |
|the bobcat has a spotted coat                    |true   |
|check the library catalog                        |true   |
|for a filesystem with deduplication look at zfs  |true   |
|add one or more predicates to the query          |true   |
+-------------------------------------------------+-------+

Der Versuch, die Abfrage mit ILIKE '% cat %' einzugrenzen, macht die Sache auch nicht besser. Wörter wie „catacombs“ und „deduplication“ sind zwar keine Treffer mehr. Aber auch wird „cats“ nicht mehr gefunden, und Sätze, die mit „cat“ beginnen und enden, sowie Satzzeichen außer Leerzeichen verursachen ebenfalls Probleme – „cat!“, „cat,“ und „cat.“ werden ignoriert.

-- too specific. doesn't even match 'cats'.
SELECT document, document ILIKE '% cat %' AS matches FROM example

+-------------------------------------------------+-------+
|document                                         |matches|
+-------------------------------------------------+-------+
|the cat chased after the rat                     |true   |
|one cat, two cats, many cats!                    |false  |
|the kitten and the dog played together           |false  |
|that is one fine feline                          |false  |
|it is raining cats and dogs                      |false  |
|after eating the whole lasagne, he was a fat cat.|false  |
|don't go into the catacombs after dark           |false  |
|the bobcat has a spotted coat                    |false  |
|check the library catalog                        |false  |
|for a filesystem with deduplication look at zfs  |false  |
|add one or more predicates to the query          |false  |
+-------------------------------------------------+-------+

ILIKE ist ebenfalls wenig hilfreich bei der Suche nach Synonymen oder verwandten Wörtern. Wenn die Anwendung für die Abfrage „cat“ Dokumente finden möchte, die „feline“, „kitten“ und „cat“ enthalten, müssen in SQL zusätzliche ILIKE-Operatoren mit OR verbunden werden. Einige Wörter können viele Varianten haben, was diesen Ansatz mühsam macht.

-- this still has all the other shortcomings to, such as '%cat%' matching too many results
SELECT document, (document ILIKE '%cat%' OR document ILIKE '%kitten%' OR document ILIKE '%feline%') AS matches FROM example;

Und schließlich gibt ILIKE nur einen booleschen Wert zurück, der angibt, ob eine Zeichenfolge mit dem Muster übereinstimmt. Er liefert keine Bewertungsmetrik, mit der die Ergebnisse nach Relevanz eingestuft werden können.

Ähnliche Probleme gibt es mit den Regex-Operatoren. Sie sind zwar leistungsfähiger als LIKE, und bestimmte Schwachpunkte von LIKE können mit kreativen Regex-Mustern behoben werden, aber sie führen noch immer einen Musterabgleich durch und haben die gleichen grundlegenden Einschränkungen.

Um die Einschränkungen dieser Operatoren zu umgehen, müsste man wahrscheinlich große Teile der in PostgreSQL integrierten Volltextsuche neu implementieren! Sehen wir uns stattdessen an, was PostgreSQL zu bieten hat.

Grundlagen der Volltextsuche

Dokumente für die Suche vorbereiten

Die Volltextsuche von PostgreSQL arbeitet nicht direkt mit Dokumenten, die mit dem Datentyp text gespeichert sind. Stattdessen müssen die Dokumente zunächst in den Datentyp tsvector, ein für die Suche optimiertes Format, konvertiert werden.

Um ein Dokument, das als text gespeichert ist, in tsvector zu konvertieren, sollte die Funktion to_tsvector verwendet werden:

to_tsvector([ config regconfig, ] document text) → tsvector

Die Funktion to_tsvector führt eine Reihe von Verarbeitungsschritten am Dokumententext durch. Auf oberster Ebene unterteilt to_tsvector das Dokument zunächst mit einem Parser in Wörter. Jedes Wort wird dann in einem oder mehreren Wörterbüchern nachgeschlagen. Ein Wörterbuch ist eine Zuordnung von Wörtern zu ihren normalisierten Formen. Diese normalisierten Formen werden Lexeme genannt. Wenn das Wort mit einem Wörterbucheintrag übereinstimmt, wird das Lexem dieses Eintrags dem tsvector hinzugefügt.

Der resultierende tsvector ist eine alphabetisch geordnete Sammlung von Lexemen, die im Quelldokument vorhanden sind. Jedes Lexem im tsvector enthält außerdem Positionsinformationen. Dies ist eine Liste von Ganzzahlen, die die Position jedes Quellworts angeben. Diese Positionsinformationen sind für die Suche nach Phrasen erforderlich und dienen dazu, Übereinstimmungen anhand ihrer Nähe in eine Rangfolge zu bringen.

Der Prozess der Normalisierung von Wörtern in Lexeme ist wörterbuchspezifisch, umfasst aber fast immer eine Umwandlung aller Großbuchstaben in Kleinbuchstaben und die Entfernung von Suffixen, um Wörter auf ihre Stammform zu reduzieren (aus „cats“ wird z. B. „cat“). Es können anwendungsspezifische Wörterbücher erstellt werden, mit denen der Normalisierungsprozess angepasst werden kann (z. B., um domänenspezifische Synonyme und Phrasen einem gemeinsamen Begriff zuzuordnen, wie „kitten“ und „feline“ zu „cat“). Dieser Normalisierungsprozess beschleunigt und vereinfacht das Auffinden aller Varianten eines Wortes, ohne dass jede Variante in der Abfrage angegeben werden muss.

Wörterbücher enthalten zudem in der Regel keine Einträge für sehr häufig gebrauchte Wörter (wie „der“ oder „und“). Diese allgemeinen Wörter werden Stoppwörter genannt und sind für die Suche nicht besonders nützlich. Indem sie übersprungen werden, wird der generierte tsvector kleiner, was die Leistung verbessert.

Die Wahl des Parsers und der Wörterbücher wird als Konfiguration bezeichnet und kann mit dem Parameter config für to_tsvector angegeben werden. Dieser Parameter ist optional, es empfiehlt sich aber, ihn immer anzugeben, um sicherzustellen, dass die richtige Konfiguration verwendet wird. Wird er weggelassen, kommt der globale Wert default_text_search_config zum Einsatz. Zwar gibt es verschiedene Standardkonfigurationen ( \dF in psql zeigt eine Liste an), aber meist sollte die Konfiguration angepasst werden.

In Abschnitt 20.11.2 und Abschnitt 12.1.3 der PostgreSQL-Dokumentation finden Sie genauere Details über die Option default_text_search_config und zur Konfiguration der Textsuche im Allgemeinen.

Hier ist ein Beispiel für den Aufruf von to_tsvector für ein Textfragment mithilfe der integrierten Konfiguration english:

SELECT to_tsvector('english', 'Hello world. ' ||
                              'The quick brown fox jumped over the lazy dog. ' ||
                              'Cats and kittens (and dogs too) love to chase mice. ' ||
                              'Hello world!')

Das Ergebnis ist der folgende tsvector:

'brown':5 'cat':12 'chase':20 'dog':11,16 'fox':6 'hello':1,22 'jump':7 'kitten':14 'lazi':10 'love':18 'mice':21 'quick':4 'world':2,23

Alle Lexeme wurden in Kleinbuchstaben umgewandelt, und mehrere Wörter wurden auf ihren Wortstamm reduziert („cats“ → „cat“). Die Satzzeichen („!“ und „.“) und Stoppwörter („the“, „and“ und „to“) wurden entfernt. Wörter, die mehrfach in der Eingabe vorkommen („dog“, „hello“ und „world“), haben mehr als einen Positionseintrag.

Die Erstellung von benutzerdefinierten Konfigurationen und Wörterbüchern ist nicht Gegenstand dieses Artikels.

Abfragen schreiben

Die Volltextsuche von PostgreSQL verwendet eine spezialisierte Mini-Abfragesprache (DSL), die das Schreiben komplexer und fortgeschrittener Abfragen ermöglicht, wobei boolesche Logik, Gruppierung und Phrasen-/Umgebungssuche kombiniert werden. Eine in dieser DSL geschriebene Abfrage wird mit dem Datentyp tsquery gespeichert. Abfragen, die als tsquery gespeichert sind, können hinsichtlich des tsvector eines Dokuments ausgewertet werden, um festzustellen, ob das Dokument der Abfrage entspricht.

Die DSL besteht aus einzelnen Lexemen, die durch Operatoren getrennt werden. Mithilfe von Klammern können Operatoren und Lexeme gruppiert werden, um Vorrang/Bindung zu erzwingen. In Abschnitt 8.11.2 der PostgreSQL-Dokumentation finden Sie eine detaillierte Dokumentation der tsquery-DSL.

Boolesches UND prüft, ob beide Lexeme im Dokument vorhanden sind. Es wird eine Übereinstimmung gefunden, wenn beide Lexeme vorhanden sind.

Boolesches ODER prüft, ob eines oder beide Lexeme im Dokument vorhanden sind. Eine Übereinstimmung wird gefunden, wenn eines der beiden Lexeme vorhanden ist.

Boolesches NICHT prüft, ob das Lexem nicht im Dokument vorhanden ist. Eine Übereinstimmung wird gefunden, wenn das Lexem nicht vorhanden ist.

Followed by prüft, ob beide Lexeme im Dokument vorhanden sind und ob der Abstand zwischen den beiden Lexemen dem angegebenen Wert entspricht. Eine Übereinstimmung wird gefunden, wenn die beiden Lexeme existieren und genau N Wörter voneinander entfernt sind.

Es gibt eine Reihe von Funktionen zur Umwandlung von Abfragetext in eine tsquery: to_tsquery, plainto_tsquery, phraseto_tsquery und websearch_to_tsquery.

Jede dieser Funktionen führt eine Normalisierung und die Entfernung von Stoppwörtern am eingegebenen Abfragetext durch, und zwar mit dem gleichen Verfahren wie bei to_tsvector. Das Parsen des Abfragetextes ist von der Funktion abhängig.

to_tsquery

to_tsquery([ config regconfig, ] querytext text) → tsquery

to_tsquery versteht die vollständige DSL. Die Funktion erwartet, dass die Eingabe wohlgeformt ist und der Syntax von tsquery folgt. Alle Syntaxfehler werden zu SQL-Ausnahmen.

SELECT to_tsquery('english', 'The & Quick & Brown & Foxes')

'quick' & 'brown' & 'fox'

plainto_tsquery

plainto_tsquery([ config regconfig, ] querytext text) → tsquery

plainto_tsquery gibt eine Abfrage zurück, die prüft, ob alle Nicht-Stoppwort-Lexeme im Dokument vorhanden sind. Die Funktion analysiert den Abfragetext mit demselben Verfahren wie to_tsvector. Die resultierenden Lexeme werden mit dem booleschen Operator UND kombiniert.

SELECT plainto_tsquery('english', 'Jumped Over The Lazy Dogs')

'jump' & 'lazi' & 'dog'

phraseto_tsquery

phraseto_tsquery([ config regconfig, ] querytext text) → tsquery

phraseto_tsquery gibt eine Abfrage zurück, die prüft, ob die angegebene Phrase im Dokument existiert. Dies ist nützlich für die „Phrasensuche“. Es verhält sich ähnlich wie phraseto_tsquery mit dem Unterschied, dass die Lexeme mit dem Operator Followed By kombiniert werden. Wenn zwischen den Lexemen im Dokument Stoppwörter vorhanden sind, wird dies durch den Operator Followed By berücksichtigt.

SELECT phraseto_tsquery('english', 'Jumped Over The Lazy Dogs')

'jump' <3> 'lazi' <-> 'dog'

In diesem Beispiel werden „over“ und „the“ als Stoppwörter betrachtet. „lazy“ ist das dritte Wort nach „jumped“, daher hat der Followed By Operator zwischen den Lexemen „jump“ und „lazi“ einen Distanzwert von 3. „dogs“ folgt unmittelbar auf „lazy“, so dass der Operator Followed By zwischen den Lexemen „lazi“ und „dog“ einen Abstand von 1 hat.

websearch_to_tsquery

websearch_to_tsquery([ config regconfig, ] querytext text) → tsquery

websearch_to_tsquery funktioniert ein wenig anders als die anderen to_tsquery-Funktionen. Sie versteht eine alternative Syntax, bei der es sich um eine grundlegende Variante dessen handelt, was üblicherweise in Web-Suchmaschinen verfügbar ist. Sie kombiniert Teile von plainto_tsquery und phraseto_tsquery, wobei sie auch einige grundlegende Operatoren versteht.

Phrasen ohne Anführungszeichen werden in Lexeme umgewandelt, die mit dem Booleschen UND (wie in plainto_tsquery) verbunden sind. Phrasen mit doppelten Anführungszeichen werden in Lexeme umgewandelt, die mit Followed By (wie in phraseto_tsquery) verbunden sind. or und - (ein Bindestrich) werden in Boolesches ODER bzw. Boolesches NICHT umgewandelt.

SELECT query_text, websearch_to_tsquery(query_text)
FROM (VALUES
    ('the quick brown fox'),
    ('"the quick brown fox"'),
    ('brown or fox'),
    ('"quick brown" fox -jumped')
) AS t(query_text)

+-------------------------+-------------------------------------+
|query_text               |websearch_to_tsquery                 |
+-------------------------+-------------------------------------+
|the quick brown fox      |'quick' & 'brown' & 'fox'            |
|"the quick brown fox"    |'quick' <-> 'brown' <-> 'fox'        |
|brown or fox             |'brown' | 'fox'                      |
|"quick brown" fox -jumped|'quick' <-> 'brown' & 'fox' & !'jump'|
+-------------------------+-------------------------------------+

websearch_to_tsquery ist wahrscheinlich die am besten geeignete integrierte Funktion für die Erstellung einer tsquery aus Benutzereingaben.

Beachten Sie, dass „Phrasensuche“-Abfragen, die Followed By Operatoren verwenden, wie die von phraseto_tsquery und websearch_to_tsquery, manchmal unerwartete Ergebnisse liefern können. Näheres hierzu unter Ungenaue Phrasensuche weiter unten.

Abfragen auswerten

Eine tsquery kann hinsichtlich eines tsvector ausgewertet werden, um festzustellen, ob das Dokument der Abfrage entspricht. Die Auswertung erfolgt mithilfe des SQL-Operators @@. Dieser Operator nimmt eine tsquery und einen tsvector (in beliebiger Reihenfolge) als Operanden und gibt einen booleschen Wert zurück, der angibt, ob eine Übereinstimmung vorliegt.

tsvector @@ tsquery → boolean
tsquery @@ tsvector → boolean


Hier wird beispielsweise ein Dokument über eine tsquery mit einem einzelnem Lexem nach einem Wort durchsucht:

WITH input AS (
    SELECT to_tsvector('english', 'The quick brown fox jumped over the lazy dog.') AS document,
           to_tsquery('english', 'Dogs') AS query
)
SELECT document, query, document @@ query AS matches FROM input;

+-----------------------------------------------------+-----+-------+
|document                                             |query|matches|
+-----------------------------------------------------+-----+-------+
|'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2|'dog'|true   |
+-----------------------------------------------------+-----+-------+

In diesem Fall entspricht „dogs“ dem Wort „dog“, da „dogs“ in der Abfrage auf das Lexem „dog“ normalisiert ist, das auch im Dokument vorhanden ist.

Wie erwartet, wenn die tsquery nicht mit dem tsvector übereinstimmt, gibt der Operator @@ false zurück.

WITH input AS (
    SELECT to_tsvector('english', 'The quick brown fox jumped over the lazy dog.') AS document,
           websearch_to_tsquery('english', 'cats or kittens') AS query
)
SELECT document, query, document @@ query AS matches FROM input;

+-----------------------------------------------------+----------------+-------+
|document                                             |query           |matches|
+-----------------------------------------------------+----------------+-------+
|'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2|'cat' | 'kitten'|false  |
+-----------------------------------------------------+----------------+-------+

Um die Abfrage etwas komplexer zu gestalten, wird hier ein Dokument nach einer Phrase durchsucht:

WITH input AS (
    SELECT to_tsvector('english', 'The quick brown fox jumped over the lazy dog.') AS document,
           phraseto_tsquery('english', 'Jumped Over The Lazy') AS query
)
SELECT document, query, document @@ query AS matches FROM input;

+-----------------------------------------------------+-----------------+-------+
|document                                             |query            |matches|
+-----------------------------------------------------+-----------------+-------+
|'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2|'jump' <3> 'lazi'|true   |
+-----------------------------------------------------+-----------------+-------+

Und wie erwartet, wird eine Übereinstimmung gefunden.

Dokumente ablegen und indizieren

Bislang haben wir gesehen, wie man Dokumente in tsvector sowie Abfragetext in tsquery konvertiert und wie man sie miteinander kombiniert, um festzustellen, ob ein Dokument einer Abfrage entspricht. Die Beispiele waren jedoch nur auf ein einziges Dokument bezogen, und der Text des Dokuments wurde immer linear eingefügt.

Typischer bei der Volltextsuche ist es jedoch, eine ganze Tabelle von Dokumenten in einem Durchgang zu durchsuchen:

SELECT title FROM documents WHERE to_tsvector(body) @@ to_tsquery('...')

Ohne die entsprechenden Indizes wird nun to_tsvector bei jedem SELECT auf den Inhalt der Spalte angewendet. Die Ausführung in jeder Zeile einer großen Tabelle bei jeder Suche führt zu einer miserablen Leistung.

Einen Ausdrucksindex hinzuzufügen, ist eine Möglichkeit, die Leistung zu verbessern. Wie dies im Einzelnen erfolgt, soll jedoch dem Leser überlassen bleiben (siehe Abschnitt 12.2.2. Creating Indexes der PostgreSQL-Dokumentation). Wir betrachten den unten beschriebenen Ansatz, bei dem eine separate, generierte tsvector-Spalte verwendet wird, der eine Reihe von Vorteilen gegenüber einem reinen Ausdrucksindex-basierten Ansatz bietet, insbesondere eine bessere Leistung. Bei der Verwendung von Indizes muss to_tsvector möglicherweise erneut ausgeführt werden, um Indexübereinstimmungen zu überprüfen.

Da tsvector ein Datentyp ist, kann in einer Tabelle eine Spalte vom Typ tsvector erstellt werden, in der die Ergebnisse von to_tsvector gespeichert werden. Deshalb ist es üblich, den tsvector des Dokuments zusammen mit dem Original zu speichern:

CREATE TABLE documents (
    name text PRIMARY KEY,
    body text NOT NULL,
    tsv tsvector NOT NULL
);

Die tsvector-Spalte als GENERATED (PostgreSQL 12 und höher) zu markieren, vereinfacht die Dinge für Client-Anwendungen, wenn sie ein INSERT oder UPDATE durchführen. Bei älteren Versionen von PostgreSQL, die keine Unterstützung für generierte Spalten bieten, sind zwei Trigger-Funktionen enthalten, die dazu dienen, den tsvector aus anderen Spalten in einer Tabelle zu generieren (siehe Abschnitt 12.4.3. Triggers for Automatic Updates der PostgreSQL-Dokumentation). Natürlich kann auch eine benutzerdefinierte Trigger-Funktion verwendet werden. Die spezifischen Vor- und Nachteile von GENERATED-Spalten im Vergleich zu BEFORE INSERT- ODER UPDATE-Triggern sind nicht Gegenstand dieses Artikels. Wir sind jedoch der Meinung, dass generierte Spalten einfacher zu verstehen sind und verwendet werden sollten, solange die Einschränkungen der generierten Spalten die Verwendung von Trigger-Funktionen erzwingen.

-- using GENERATED
CREATE TABLE documents_generated (
    name text PRIMARY KEY,
    body text NOT NULL,
    tsv tsvector NOT NULL GENERATED ALWAYS AS (to_tsvector('english', name || ' ' || body)) STORED
);

Anschließend kann ein Index über die tsvector-Spalte generiert werden, um Abfragen zu beschleunigen:

CREATE INDEX textsearch_idx ON documents USING GIN (tsv);

Sobald die tsvector-Spalte eingerichtet ist, kann sie ganz normal in Abfragen verwendet werden:

SELECT title FROM documents WHERE tsv @@ to_tsquery('...')

Und das war es auch schon mit der einfachen Textsuche. Zusammenfassend kann man sagen, dass es 3 Hauptschritte gibt:

1. Jedes Dokument wird in einen tsvector konvertiert, wofür to_tsvector verwendet wird. Normalerweise wird dies einmal bei INSERT/UPDATE durchgeführt, und der resultierende tsvector wird in einer Spalte neben dem Originaldokument gespeichert. Indizes für diese Spalte beschleunigen die Suche.
2. Der Abfragetext wird in eine tsquery mit einer der ...to_tsquery-Funktionen konvertiert.
3. Die tsquery wird hinsichtlich des tsvector des Dokuments ausgewertet, wofür der Operator @@ verwendet wird.

Einige Fallstricke

Text in tsvector/tsquery umwandeln

Es ist möglich, Text direkt in tsvector und tsquery umzuwandeln. Das Ergebnis ist allerdings nicht, was Sie erwarten!

Wie die meisten PostgreSQL-Datentypen haben tsvector und tsquery ein kompaktes binäres Format und eine textuelle Darstellung. Die Umwandlung von Text erwartet eine Zeichenfolge im textuellen Darstellungsformat von tsvector/ tsquery (siehe Abschnitt 8.11. Text Search Types der PostgreSQL-Dokumentation). Wenn die Zeichenfolge wohlgeformt ist, klappt die Umwandlung, aber es werden keine Stoppwörter entfernt, Wörter normalisiert oder andere Vorverarbeitungsschritte durchgeführt – es wird davon ausgegangen, dass die Lexeme in der Eingabe bereits normalisiert wurden.

WITH input AS (
    SELECT 'the QuIcK brown!! fox'::tsvector AS document,
           plainto_tsquery('quick brown fox') AS query
)
SELECT document, query, document @@ query AS matches FROM input

+-----------------------------+-------------------------+-------+
|document                     |query                    |matches|
+-----------------------------+-------------------------+-------+
|'QuIcK' 'brown!!' 'fox' 'the'|'quick' & 'brown' & 'fox'|false  |
+-----------------------------+-------------------------+-------+

Ungenaue Phrasensuche

Die Phrasensuche verhält sich möglicherweise nicht wie erwartet, wenn der Abfragetext Stoppwörter enthält.

Im folgenden Beispiel berücksichtigt die integrierte english-Konfiguration „over“ und „the“ als Stoppwörter, und phraseto_tsquery ersetzt sie durch den Operator Followed By mit Distanz 3 ( <3>). Folglich stimmt diese Abfrage mit jedem Dokument überein, in dem das Lexem „jump“, gefolgt von zwei beliebigen Wörtern, z. B. „lazi“, vorkommt:

WITH input AS (
    SELECT to_tsvector('english', 'The quick brown fox jumped past a lazy dog. ') AS document,
           phraseto_tsquery('english', 'Jumped Over The Lazy') AS query
)
SELECT document, query, document @@ query AS matches FROM input;

+--------------------------------------------------------------+-----------------+-------+
|document                                                      |query            |matches|
+--------------------------------------------------------------+-----------------+-------+
|'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'past':6 'quick':2|'jump' <3> 'lazi'|true   |
+--------------------------------------------------------------+-----------------+-------+

Hier passt „jumped over the lazy“ zu „jumped past a lazy dog“. Der Abgleich ist erfolgreich, weil der tsvector die Lexeme „jump“ und „lazi“ an den Positionen 5 bzw. 8 enthält – ein Abstand von 3 Wörtern.

Einschränkungen

Bei der Verwendung der PostgreSQL-Volltextsuche gibt es eine Reihe von Einschränkungen zu beachten.

Solange Sie nicht versuchen, einen Artikel über das längste englische Wort der Welt zu indizieren (der chemische Name für Titin, der ~189819 Buchstaben lang ist!), ist es unwahrscheinlich, dass Sie die Grenze der Lexemgröße erreichen. Die Indizierung großer Textfragmente kann jedoch die Größenbeschränkung für tsvector von ~1 MiB überschreiten. Beachten Sie, dass diese Grenze auf der Größe der Binärdarstellung basiert (die zur Speicherung eines tsvector im Speicher und auf der Festplatte verwendet wird). Eine typische Abhilfemaßnahme ist, das Dokument in einzelne Abschnitte aufzuteilen (z. B. bei Büchern in Kapitel).

Wenn Ihre Anwendung die Phrasensuche verwenden soll, achten Sie sorgfältig auf die Beschränkungen von tsvector im Hinblick auf Lexempositionen. Es wird kein Fehler und keine Warnung ausgegeben, wenn einer dieser Grenzwerte bei der Generierung von tsvector erreicht wird. Ein tsvector kann nur 255 Positionseinträge pro Lexem speichern. Wenn ein Lexem mehr als 255-mal in einem Dokument vorkommt, werden nur die ersten 255 Positionen gespeichert. Jeder dieser Positionseinträge hat zudem einen Maximalwert, der auf 16.383 begrenzt ist. Wenn ein Lexem nach der 16.383. Position im Dokument auftritt, wird seine Position auf 16.383 gerundet. Beachten Sie, dass Stoppwörter zwar nicht im tsvector enthalten sind, die Lexempositionen jedoch beeinflussen.

Gesamtzahl der Lexeme pro tsvector

Im folgenden Beispiel wird das Wort „fox“ 300-mal im Quelldokument wiederholt, aber nur 255 Positionen werden im tsvector aufgezeichnet. ( unnest ist eine integrierte Hilfsfunktion, die einen tsvector in eine Tabelle umwandelt, sodass verschiedene Abfragen darauf durchgeführt werden können.)

WITH document AS (
    SELECT repeat('fox ', 300) as body
), lexemes AS (
    SELECT (unnest(to_tsvector(document.body))).* FROM document
), positions AS (
    SELECT lexeme, unnest(positions) AS position FROM lexemes
)
SELECT * FROM positions

+------+--------+
|lexeme|position|
+------+--------+
|fox   |1       |
|fox   |2       |
|fox   |3       |
|fox   |4       |

|fox   |254     |
|fox   |255     |
+------+--------+

Maximaler Wert eines Eintrags für eine Lexemposition

Hier wird ein Dokument, bestehend aus „hello“, gefolgt von 20.000 Kopien von „the“ und endend mit „world“, in einen tsvector umgewandelt. „the“ wurde als Lückenfüller gewählt, weil es ein Stoppwort ist. Wenngleich kein Lexem für „the“ im tsvector vorhanden ist, beeinflusst es dennoch die Lexempositionen von „hello“ und „world“. „world“ ist das 20.001. Wort im Dokument, doch da die maximale Positionswertgrenze erreicht ist, rundet PostgreSQL den Positionswert auf 16.383:

WITH document AS (
    SELECT 'hello ' || repeat('the ', 20000) || 'world' as body
), lexemes AS (
    SELECT (unnest(to_tsvector(document.body))).* FROM document
), positions AS (
    SELECT lexeme, unnest(positions) AS position FROM lexemes
)
SELECT * FROM positions

+------+--------+
|lexeme|position|
+------+--------+
|hello |1       |
|world |16383   |
+------+--------+

Beispiel für Einschränkungen der Phrasensuche und großer Dokumente

Die Phrasensuche arbeitet bei großen Textfragmenten möglicherweise unzuverlässig, da die Informationen zur Lexemposition entweder falsch sind (auf 16.383 gerundet) oder ganz fehlen, wie das folgende Beispiel zeigt:

SELECT name,
       to_tsvector(t.body) @@ phraseto_tsquery('hello world') AS phrase_matches,
       to_tsvector(t.body) @@ plainto_tsquery('hello world') AS plain_matches
FROM (
      VALUES ('phrase', 'hello world'),
             ('positions discarded', repeat('hello ', 300) || ' hello world'),
             ('positions rounded', repeat('the ', 20000) || ' hello world')
     ) AS t(name, body)

+-------------------+--------------+-------------+
|name               |phrase_matches|plain_matches|
+-------------------+--------------+-------------+
|phrase             |true          |true         |
|positions discarded|false         |true         |
|positions rounded  |false         |true         |
+-------------------+--------------+-------------+

In den beiden Tests positions discarded und positions rounded steht phrase_matches auf false, weil der Operator Followed By (wie von phraseto_tsquery generiert) den tsvector prüft und auf der Grundlage der verfügbaren Informationen fälschlicherweise schlussfolgert, dass es keinen Fall gibt, in dem ein „world“-Lexem direkt auf ein „hello“ folgt (d. h. eine Positionsdifferenz von 1 hat).

Der Phrasentest für positions discarded schlägt fehl, weil nur die ersten 255 Positionseinträge des Lexems „hello“ im tsvector behalten werden. Die letzte aufgezeichnete „hello“-Position ist 255, aber „world“ hat eine Position von 301, was eine Differenz von 46 ergibt.

Der Phrasentest positions rounded schlägt fehl, weil die 20.000 Instanzen des Stoppworts „the“ die Positionswerte der Lexeme „hello“ und „world“ beeinflussen. Beide Positionswerte überschreiten 16.383 und werden auf diesen Wert gerundet, sodass sie eine Differenz von 0 haben.

Erweiterte Ranking-Strategien

Ein wesentlicher Vorteil der PostgreSQL-Volltextsuche gegenüber einfachen LIKE-Operatoren ist die Möglichkeit, Suchergebnisse nach ihrer Relevanz zu bewerten und zu sortieren. PostgreSQL bietet hierfür mehrere integrierte Ranking-Funktionen, die verschiedene Aspekte der Dokumentenrelevanz berücksichtigen.

ts_rank() – Grundlegendes Relevanz-Scoring

Die Funktion ts_rank() berechnet einen numerischen Relevanz-Score basierend auf der Häufigkeit der übereinstimmenden Lexeme im Dokument:

ts_rank([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ]) → float4

Hier ist ein praktisches Beispiel, das zeigt, wie Dokumente nach Relevanz sortiert werden können:

  WITH documents AS (  
    SELECT * FROM (VALUES     
      ('Ein Artikel über Katzen und ihre Gewohnheiten'),    
      ('Katzen sind wunderbare Haustiere. Katzen lieben es zu spielen.'),    
      ('Der Hund und die Katze spielten zusammen im Garten'),    
      ('Katzenfutter ist wichtig für die Gesundheit von Katzen')  
    ) AS t(content)
  )
  SELECT   
    content,  
    ts_rank(to_tsvector('german', content), 
    to_tsquery('german', 'Katzen')) as relevance_score
  FROM documents 
  WHERE to_tsvector('german', content) @@ to_tsquery('german', 'Katzen')
  ORDER BY relevance_score DESC;

  +----------------------------------------------------------+----------------+
  |content                                                   |relevance_score |
  +----------------------------------------------------------+----------------+
  |Katzen sind wunderbare Haustiere. Katzen lieben es zu...  |0.30396757      |
  |Katzenfutter ist wichtig für die Gesundheit von Katzen    |0.15198378      |
  |Ein Artikel über Katzen und ihre Gewohnheiten             |0.15198378      |
  |Der Hund und die Katze spielten zusammen im Garten        |0.15198378      |
  +----------------------------------------------------------+----------------+

ts_rank_cd() – Cover Density Ranking

Die Funktion ts_rank_cd() verwendet einen anderen Algorithmus, der die „Cover Density“ berechnet – wie dicht die Suchbegriffe im Dokument beieinander stehen:

ts_rank_cd([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ]) → float4

Diese Funktion ist besonders nützlich für Phrasensuchen oder wenn die Nähe der Suchbegriffe zueinander wichtig ist:

  SELECT
    content,
    ts_rank_cd(to_tsvector('german', content),
    to_tsquery('german', 'Katzen & Hund')) as density_score
  FROM documents 
  WHERE to_tsvector('german', content) @@ to_tsquery('german', 'Katzen & Hund')
  ORDER BY density_score DESC;

Gewichtete Suche für verschiedene Dokumentbereiche

In realen Anwendungen haben verschiedene Teile eines Dokuments unterschiedliche Wichtigkeit. Ein Treffer im Titel sollte höher bewertet werden als ein Treffer im Fließtext. PostgreSQL unterstützt dies durch gewichtete tsvector-Erstellung:

  CREATE TABLE articles (
    id SERIAL PRIMARY KEY,  
    title TEXT,
    summary TEXT,
    content TEXT,  
    search_vector tsvector GENERATED ALWAYS AS (
      setweight(to_tsvector('german', coalesce(title,'')), 'A') ||
      setweight(to_tsvector('german', coalesce(summary,'')), 'B') ||
      setweight(to_tsvector('german', coalesce(content,'')), 'C')
    ) STORED
  );

Die Gewichtungen A, B, C, D entsprechen verschiedenen Wichtigkeitsstufen, wobei A die höchste Priorität hat. Bei der Ranking-Berechnung können diese Gewichtungen berücksichtigt werden:

  SELECT title,
        ts_rank('{0.1, 0.2, 0.4, 1.0}', search_vector, query) as weighted_rank
  FROM articles, to_tsquery('german', 'PostgreSQL') query
  WHERE search_vector @@ query
  ORDER BY   weighted_rank DESC;

Benutzerdefinierte Ranking-Algorithmen

Für komplexere Anwendungsfälle können Sie eigene Ranking-Algorithmen entwickeln, die mehrere Faktoren kombinieren:

  SELECT
    title,
    content,
    (
      ts_rank(search_vector, query) * 0.5 +                         -- Grundrelevanz
      (CASE WHEN title ILIKE '%PostgreSQL%' THEN 0.3 ELSE 0 END) +  -- Titel-Boost
      (extract(days from (CURRENT_DATE - created_at)) * -0.01)      -- Aktualitäts-Faktor
    ) as custom_score
  FROM articles, to_tsquery('german', 'PostgreSQL') query
  WHERE search_vector @@ query
  ORDER BY custom_score DESC;

Normalisierung und Performance-Optimierung

Beide Ranking-Funktionen unterstützen einen optionalen Normalisierungsparameter, der die Scores an die Dokumentlänge anpasst:

• 0 (Standard): Keine Normalisierung
• 1: Durch 1 + Logarithmus der Dokumentlänge dividieren
• 2: Durch Dokumentlänge dividieren
• 4: Durch mittlere harmonische Distanz zwischen Extents dividieren
• 8: Durch Anzahl eindeutiger Wörter im Dokument dividieren
• 16: Durch 1 + Logarithmus der eindeutigen Wörter dividieren
• 32: Durch Rang + 1 dividieren

Diese Werte können kombiniert werden, indem sie addiert werden:

  SELECT title,
         ts_rank(search_vector, query, 1|2|8) as normalized_rank
  FROM articles, to_tsquery('german', 'PostgreSQL') query
  WHERE search_vector @@ query
  ORDER BY normalized_rank DESC;

Für optimale Performance bei großen Datenmengen sollten Sie Indizes auf den berechneten Ranking-Werten in Betracht ziehen und die Ranking-Berechnung nur für bereits gefilterte Ergebnisse durchführen.

Testen Sie die PostgreSQL®-Volltextsuche

Die in PostgreSQL integrierten Volltextsuchfunktionen sind mehr als geeignet, um grundlegende Textsuchfunktionen für eine Anwendung zur Verfügung zu stellen. Entwickler greifen oft zuerst zu schwergewichtigen Lösungen, wie OpenSearch. Wenn Sie PostgreSQL bereits als Datenspeicher für Ihre Anwendung verwenden, sollten Sie zunächst die integrierte Textsuche ausprobieren, bevor Sie weitere Abhängigkeiten zu Ihrer Anwendung hinzufügen. Es ist einfacher, die indizierten Dokumente mit den Originalen zu synchronisieren, wenn sie zusammen gespeichert sind, und generierte Spalten vereinfachen dies noch weiter. Volltext-Suchabfragen können mit anderen SQL-Operatoren kombiniert werden, was leistungsstarke Abfrage-, Filter- und Sortierfunktionen innerhalb einer Datenbank ermöglicht.

Der Originalartikel stammt von Adam Zegelin und wurde auf Instaclustr.com am 9. November 2022 publiziert und von Carsten Meskes ins Deutsche übertragen.

… mit Kafka® Connect und dem Debezium PostgreSQL® Quellconnector

Moderne, verteilte ereignis- und streamingbasierte Systeme machen sich die Idee zu eigen, dass Änderungen unvermeidlich und sogar wünschenswert sind! Ohne Änderungsbewusstsein sind Systeme unflexibel, können sich nicht weiterentwickeln oder reagieren und sind schlichtweg nicht in der Lage, mit Echtzeitdaten aus der realen Welt Schritt zu halten. In einer früheren 2-teiligen Blogserie (Teil 1, Teil 2) haben wir herausgefunden, wie man mit dem Debezium Cassandra Connector Änderungsdaten aus einer Apache Cassandra®-Datenbank erfasst und Echtzeit-Ereignis-Streams in Apache Kafka® erzeugt.

Aber wie kann man einen „Elefanten“ (PostgreSQL®) auf das Tempo eines „Geparden“ (Kafka) bringen?

Geparden sind die schnellsten Landtiere (Spitzengeschwindigkeit 120 km/h, Beschleunigung von 0 auf 100 km/h in 3 Sekunden) – 3-mal schneller als Elefanten (40 km/h). (Quelle: Shutterstock)

1. Der Debezium PostgreSQL Connector

Ähnlich wie der Debezium Cassandra Connector (Blog Teil 1, Teil 2) erfasst auch der Debezium PostgreSQL Connector Datenbankänderungen auf Zeilenebene und überträgt den Stream über Kafka Connect an Kafka. Ein wesentlicher Unterschied besteht jedoch darin, dass dieser Connector als Kafka-Quellconnector ausgeführt wird. Wie lässt sich also vermeiden, dass auf dem PostgreSQL-Server benutzerdefinierter Code ausgeführt werden muss? Aus der Dokumentation geht Folgendes hervor:

„Ab PostgreSQL 10 gibt es einen logischen Replikations-Stream-Modus, genannt pgoutput, der nativ von PostgreSQL unterstützt wird. Das bedeutet, dass ein Debezium PostgreSQL Connector diesen Replikations-Stream nutzen kann, ohne dass zusätzliche Plug-ins erforderlich sind.“

Somit kann der Connector einfach als PostgreSQL Streaming Replication Client ausgeführt werden. Um den Connector auszuführen, müssen Sie ihn herunterladen, in Ihrer Kafka Connect-Umgebung installieren, konfigurieren, PostgreSQL einrichten und dann wie folgt ausführen.

1.1. Debezium PostgreSQL Connector herunterladen

Der Connector kann hier heruntergeladen werden.

1.2. Debezium PostgreSQL Connector installieren

Ich werde hier den Dienst Instaclustr Managed Kafka Connect verwenden. Mit diesem Dienst können benutzerdefinierte Connectors verwendet werden, allerdings müssen sie zunächst in einen AWS S3 Bucket geladen und dann über die Instaclustr-Verwaltungskonsole synchronisiert werden. (Ich habe in meinem S3 Bucket einen Ordner mit dem Namen debezium-connector-postgres erstellt und alle Jars aus dem ursprünglichen Download in diesen Ordner hochgeladen.)
Wenn alles funktioniert hat, sehen Sie in der Liste der verfügbaren Connectors auf der Konsole einen neuen Connector mit dem Namen io.debezium.connector.postgresql.PostgresConnector.

1.3. PostgreSQL konfigurieren

Hier sind die erforderlichen PostgreSQL-Servereinstellungen:

1. Prüfen Sie wal_level. Wenn dies nicht auf logical steht, setzen Sie es auf logical. (Dazu ist ein Server-Neustart und bei einem verwalteten Dienst ggf. Unterstützung erforderlich.)
2. Für PostgreSQL > 10+ sind keine zusätzlichen Plug-ins erforderlich, da pgoutput verwendet wird (Sie müssen jedoch das Standard-Plug-in plugin.name in der Konfiguration des Connectors überschreiben, siehe unten).
3. Benutzerberechtigungen konfigurieren
   a. Laut Anweisungen soll ein Debezium-Benutzer erstellt werden, der über die erforderlichen Mindestrechte verfügt (REPLICATION- und LOGIN-Rechte),
   b. und um pgoutput zu verwenden, benötigen Sie weitere Berechtigungen.

Beachten Sie, dass für diese Einstellungen Administratorrechte für PostgreSQL nötig sind. Wenn Sie also einen verwalteten Dienst verwenden, müssen Sie möglicherweise die Hilfe Ihres Dienstanbieters in Anspruch nehmen, um die notwendigen Änderungen vorzunehmen.

1.4. Debezium PostgreSQL Connector konfigurieren und ausführen

Damit Sie den Connector ausführen können, finden Sie hier ein Beispiel für eine Connector-Konfiguration. Beachten Sie, dass der Standardwert von plugin.name nicht pgoutput ist, weshalb Sie ihn explizit angeben müssen (geben Sie die IP-Adresse, den Benutzernamen und das Passwort für den Kafka Connect-Cluster und die IP-Adresse, den Benutzernamen und das Passwort für die PostgreSQL-Datenbank an):

curl https://KafkaConnectIP:8083/connectors -X POST -H 'Content-Type: application/json' -k -u kc_username:kc_password -d '{
  "name": "debezium-test1",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "PG_IP",
    "database.port": "5432",
    "database.user": "pg_username",
    "database.password": "pg_password",
    "database.dbname" : "postgres",
    "database.server.name": "test1",
    "plugin.name": "pgoutput"
  }
}

Wenn das korrekt funktioniert hat, sehen Sie in der Instaclustr Kafka Connect-Konsole einen einzelnen laufenden Task für debezium-test1. Beachten Sie, dass der Standardwert und auch der einzige zulässige Wert für tasks.max genau 1 ist, sodass Sie ihn nicht explizit festlegen müssen.

1.5. Tabellen-Themen-Zuordnungen mit Debezium PostgreSQL Connector

Vielleicht fällt Ihnen auf, dass in der Konfiguration keine Tabellennamen oder Themen angegeben sind. Das liegt daran, dass der Connector standardmäßig Änderungen für alle Nicht-System-Tabellen erfasst und Ereignisse für eine einzelne Tabelle in ein einzelnes Kafka-Thema schreibt.
Standardmäßig lautet der Name des Kafka-Themas serverName.schemaName.tableName, wobei:
• serverName der logische Name des Connectors wie im Konfigurationsmerkmal des Connectors database.server.name angegeben ist (und eindeutig sein muss)
• schemaName der Name des Datenbankschemas ist
• tableName der Name der Datenbanktabelle ist
Es gibt eine Reihe von Konfigurationsoptionen zum Ein- oder Ausschließen von Schemata, Tabellen und Spalten. (Verwenden Sie nur eine für jedes Objekt.)
Ich konnte keine PostgreSQL Connector-spezifischen Konfigurationsoptionen finden, um die standardmäßige Tabellen-Thema-Zuordnung zu ändern; das liegt jedoch daran, dass Sie generische Debezium Single Message Transforms, SMTs für benutzerdefiniertes Topic Routing, verwenden müssen.

2. Daten-Änderungsereignisse von Debezium PostgreSQL Connector kennenlernen

Ein furchterregender „Giraffosaurus“! (Oder eine T-Raffe?) (Quelle: Shutterstock)

Wenn alles richtig funktioniert, sehen Sie einige Daten-Änderungsereignisse in einem Kafka-Thema. Bei einer Tabelle mit dem Namen test1 lautet der Themenname beispielsweise test1.public.test1. Die Tabelle hat 3 ganzzahlige Spalten (id, v1, v2); id ist der Primärschlüssel.
Wie sehen nun die Kafka-Daten aus? Auf den ersten Blick sehen sie etwas unheimlich aus – in was haben sich die einfachen CRUD-Operationen der Datenbank verwandelt? Dies ist das Ereignis für eine Einfügung:

Struct{after=Struct{id=1,v1=2,v2=3},source=Struct{version=1.6.1.Final,connector=postgresql,name=test1,ts_ms=1632457564326,db=postgres,sequence=["1073751912","1073751912"],schema=public,table=test1,txId=612,lsn=1073751968},op=c,ts_ms=1632457564351}

Für eine Aktualisierung erhalten wir dieses Ereignis:

Struct{after=Struct{id=1,v1=1000,v2=3},source=Struct{version=1.6.1.Final,connector=postgresql,name=test1,ts_ms=1632457801633,db=postgres,sequence=["1140858536","1140858536"],schema=public,table=test1,txId=627,lsn=1140858592},op=u,ts_ms=1632457801973}

Und nach einer Löschung erhalten wir dieses Ereignis:

Struct{before=Struct{id=1},source=Struct{version=1.6.1.Final,connector=postgresql,name=test1,ts_ms=1632457866810,db=postgres,sequence=["1140858720","1140858720"],schema=public,table=test1,txId=628,lsn=1140858776},op=d,ts_ms=1632457867187}
Null

Was fällt uns bei diesen Ereignissen auf? Wie erwartet, entspricht der Operationstyp (c, u, d) der PostgreSQL-Operationssemantik (create – für ein insert, update, delete). Für create und update gibt es einen after-Datensatz, der die ID und die Werte anzeigt, nachdem die Transaktion durchgeführt wurde. Für delete gibt es einen before-Datensatz, der nur die ID enthält, und ein Null für die after-Werte. Außerdem gibt es viele Metadaten, darunter die Zeit, datenbankspezifische Sequenz- und „lsn“-Informationen und eine Transaktions-ID. Mehrere Ereignisse können sich eine Transaktions-ID teilen, wenn sie im selben Transaktionskontext aufgetreten sind.  Wofür ist die Transaktions-ID nützlich? Transaktions-Metadaten , die der txId entsprechen, können in topics mit dem Postfix .transaction geschrieben werden (provide.transaction.metadata ist standardmäßig false).
Diese Daten haben mich zunächst überrascht, da ich nach der ersten Lektüre der Dokumentation etwas besser lesbare (JSON) Änderungsereignisdaten einschließlich Schlüssel- und Wertschemata sowie Nutzdaten erwartet hatte. Aber das „Kleingedruckte“ besagt:

„Daraus, wie Sie den Kafka Connect Converter konfigurieren, den Sie in Ihrer Anwendung verwenden möchten, ergibt sich die Darstellung dieser vier Teile in Änderungsereignissen.“

Offensichtlich war also meine Konfiguration unvollständig. Mit ein bisschen Suchen entdeckte ich die folgenden zusätzlichen Konfigurationseinstellungen: key/value.converter und key/value.schemas.enable werden benötigt, um die Schlüssel- und Werteschemata in die Daten aufzunehmen, und das JSON-Format sollte verwendet werden:

"value.converter": "org.apache.kafka.connect.json.JsonConverter"

"value.converter.schemas.enable": "true"

"key.converter": "org.apache.kafka.connect.json.JsonConverter"

"key.converter.schemas.enable": "true"

Nach der Änderung der Konfiguration und einem Neustart des Connectors sind die generierten Daten zwar viel ausführlicher, aber zumindest jetzt wie erwartet im JSON-Format. Bei einer insert-Operation erhalten wir zum Beispiel dieses lange Ereignis:

{"schema":{"type":"struct","fields":[{"type":"struct","fields":[{"type":"int32","optional":false,"field":"id"},{"type":"int32","optional":true,"field":"v1"},{"type":"int32","optional":true,"field":"v2"}],"optional":true,"name":"test1.public.test1.Value","field":"before"},{"type":"struct","fields":[{"type":"int32","optional":false,"field":"id"},{"type":"int32","optional":true,"field":"v1"},{"type":"int32","optional":true,"field":"v2"}],"optional":true,"name":"test1.public.test1.Value","field":"after"},{"type":"struct","fields":[{"type":"string","optional":false,"field":"version"},{"type":"string","optional":false,"field":"connector"},{"type":"string","optional":false,"field":"name"},{"type":"int64","optional":false,"field":"ts_ms"},{"type":"string","optional":true,"name":"io.debezium.data.Enum","version":1,"parameters":{"allowed":"true,last,false"},"default":"false","field":"snapshot"},{"type":"string","optional":false,"field":"db"},{"type":"string","optional":true,"field":"sequence"},{"type":"string","optional":false,"field":"schema"},{"type":"string","optional":false,"field":"table"},{"type":"int64","optional":true,"field":"txId"},{"type":"int64","optional":true,"field":"lsn"},{"type":"int64","optional":true,"field":"xmin"}],"optional":false,"name":"io.debezium.connector.postgresql.Source","field":"source"},{"type":"string","optional":false,"field":"op"},{"type":"int64","optional":true,"field":"ts_ms"},{"type":"struct","fields":[{"type":"string","optional":false,"field":"id"},{"type":"int64","optional":false,"field":"total_order"},{"type":"int64","optional":false,"field":"data_collection_order"}],"optional":true,"field":"transaction"}],"optional":false,"name":"test1.public.test1.Envelope"},"payload":{"before":null,"after":{"id":10,"v1":10,"v2":10},"source":{"version":"1.6.1.Final","connector":"postgresql","name":"test1","ts_ms":1632717503331,"snapshot":"false","db":"postgres","sequence":"[\"1946172256\",\"1946172256\"]","schema":"public","table":"test1","txId":1512,"lsn":59122909632,"xmin":null},"op":"c","ts_ms":1632717503781,"transaction":null}}

Die expliziten Schema-Metadaten machen die Sache ziemlich komplex, also schalten wir sie folgendermaßen ab:

"value.converter.schemas.enable": "false"

"key.converter.schemas.enable": "false"

Dies ergibt einen besser lesbaren Datensatz, der nur die Nutzdaten enthält (oben hervorgehoben, aber beachten Sie, dass „payload“ nicht mehr angezeigt wird):

{"before":null,"after":{"id":10,"v1":10,"v2":10},"source":{"version":"1.6.1.Final","connector":"postgresql","name":"test1","ts_ms":1632717503331,"snapshot":"false","db":"postgres","sequence":"[\"1946172256\",\"1946172256\"]","schema":"public","table":"test1","txId":1512,"lsn":59122909632,"xmin":null},"op":"c","ts_ms":1632717503781,"transaction":null}

Beachten Sie, dass wir jetzt ein before-Feld und ein after-Feld für create-Operationen haben.
Beachten Sie auch, dass ohne explizites Schema der Kafka-Sink-Connector in der Lage sein muss, die Nutzdaten ohne zusätzlichen Kontext zu verstehen, oder Sie müssen alternativ eine Schema Registry verwenden und konfigurieren. Hier sind die Anweisungen für die Verwendung einer Kafka Schema Registry mit dem von Instaclustr verwalteten Kafka-Dienst. Änderungen an der Konfiguration des Debezium-Quellconnectors müssen Folgendes enthalten:

"value.converter": "io.confluent.connect.avro.AvroConverter"

"value.converter.schema.registry.url": "http://schema-registry:8081"

Ich war neugierig, was nach einer truncate-Operation an einer Tabelle passieren würde, aber überraschenderweise wurden überhaupt keine Ereignisse generiert. Ist ein truncate nicht semantisch gleichwertig mit mehreren delete-Operationen? Wie sich herausstellt, sind truncate-Ereignisse standardmäßig ausgeschaltet („truncate.handling.mode“ : „skip“ – nicht „bytes“, wie fälschlicherweise dokumentiert; „include“, um sie einzuschalten).
Die andere Überlegung ist, dass der Kafka-Sink-Connector in der Lage sein muss, truncate-Ereignisse vernünftig zu verarbeiten, was anwendungsspezifisch und/oder Sink-system-spezifisch sein kann. (Z. B. könnte es für Elasticsearch sinnvoll sein, als Reaktion auf ein truncate-Ereignis einen gesamten Index zu löschen. Für die Stream-Verarbeitung hingegen ist es nicht offensichtlich, was eine sinnvolle Reaktion wäre. Allerdings tritt das gleiche Problem vielleicht auch bei Löschungen und Aktualisierungen auf.)

3. Durchsatz des Debezium PostgreSQL Connectors

Wie schnell kann ein Debezium PostgreSQL Connector ausgeführt werden? (Quelle: Shutterstock)

Eine Einschränkung des Debezium PostgreSQL Connectors ist, dass er nur als einzelner Task ausgeführt werden kann. Ich habe einige Auslastungstests durchgeführt und festgestellt, dass ein einzelner Task maximal 7.000 Daten-Änderungsereignisse pro Sekunde verarbeiten kann. Dies entspricht auch den Transaktionen pro Sekunde, solange es nur ein Änderungsereignis pro Transaktion gibt. Bei mehreren Ereignissen pro Transaktion ist der Transaktionsdurchsatz geringer. In einem früheren Blog (Pipeline-Blogserie Teil 9) haben wir 41.000 Einfügungen pro Sekunde in PostgreSQL erreicht. Davon sind 7.000 lediglich 17 %. Dieser Teil der CDC-Pipeline ist also in der Praxis eher ein Elefant als ein Gepard. Typische PostgreSQL-Workloads bestehen jedoch aus eine Mischung an Schreib- und Lesevorgängen, sodass die Schreibrate wesentlich geringer sein kann, was den Debezium PostgreSQL Connector zu einer praktikableren Lösung macht.

Ich habe noch ein weiteres, etwas merkwürdiges Verhalten festgestellt, das Sie vielleicht beachten sollten. Wenn zwei (oder mehr) Tabellen auf Änderungsereignisse überwacht werden und die Last nicht gleichmäßig auf die Tabellen verteilt ist (z. B., wenn ein Batch von Änderungen in einer Tabelle kurz vor der nächsten auftritt), dann verarbeitet der Connector alle Änderungen der ersten Tabelle, bevor er mit den Änderungen für die zweite Tabelle beginnt. Bei dem von mir entdeckten Beispiel kam es zu einer Verzögerung von 10 Minuten. Ich bin mir nicht ganz sicher, was da vor sich geht, aber es sieht so aus, als ob der Connector alle Änderungen für eine Tabelle verarbeiten muss, bevor er zur nächsten Tabelle übergeht. Bei normalen, ausgeglichenen Workloads mag dies in Ordnung sein, aber bei Spitzen-/Batch-Lasten, die eine einzelne Tabelle stark auslasten, kann es Probleme bei der rechtzeitigen Verarbeitung von Änderungsereignissen aus anderen Tabellen verursachen.

Eine mögliche Lösung ist, mehrere Connectors zu verwenden. Dies scheint möglich zu sein (siehe z. B. diesen nützlichen Blog) und kann auch dazu beitragen, das Limit für die Verarbeitung von 7.000 Ereignissen pro Sekunde zu beseitigen. Allerdings würde es wahrscheinlich nur funktionieren, wenn sich die Tabellen zwischen den Connectors nicht überschneiden, und Sie müssten mehrere Replication-Slots haben, damit es funktioniert (es gibt eine Konfigurationsoption des Connectors für slot.name).

4. Debezium PostgreSQL Connector Datenänderungs-Erfassungsereignisse mit Kafka Sink-Connectors in Elasticsearch streamen

Die endgültige Metamorphose – vom Gepard (Kafka) zum Nashorn! (Sink-System, z. B. Elasticsearch) (Quelle: Shutterstock)

Es ist natürlich nicht das Ziel, genügend Daten-Änderungsereignisse in Kafka zu erhalten und sie zu verstehen, sondern sie in ein oder mehrere Sink-Systems zu streamen.

Ich wollte jedoch eine einfache Möglichkeit haben, das komplette End-to-End-System zu testen. Insbesondere mit einem Ansatz, der keine benutzerdefinierten Kafka Connect Sink-Connectors benötigt, um komplexe Daten-Änderungsereignisse und die Semantik des Sink-Systems zu interpretieren, oder eine Schema Registry betreiben muss (was wahrscheinlich auch einen benutzerdefinierten Quellconnector erfordern würde). Deshalb habe ich die Elasticsearch Sink-Connectors aus der letzten Pipeline-Blogserie wiederverwendet. Dieser Ansatz hat sich bereits beim Lesen von JSON-Daten ohne Schema bewährt und schien daher auch für diesen Anwendungsfall ideal.

Das „erste Taxi in der Schlange“ (in Zeiten von Fahrgemeinschafts-Apps eine anachronistische Wendung) ist der Apache Camel Kafka Elasticsearch Sink-Connector. Dies war der Connector, der sich bei früheren Experimenten als am robustesten erwiesen hatte. Leider fehlte dieses Mal eine Klasse (org.elasticsearch.rest.BytesRestResponse), was ich nicht weiter zu beheben versucht habe. Wahrscheinlich wäre ich kein guter Spion, denn jeder, der sich im Spionagehandwerk auskennt, weiß, dass man nicht das erstbeste Taxi nehmen sollte, das um die Ecke kommt!

Für meinen zweiten Versuch verwendete ich einen weiteren quelloffenen Elasticsearch Sink-Connector von lenses.io, der standardmäßig im Managed Kafka Connect-Dienst von Instaclustr enthalten ist.

Hier eine Beispielkonfiguration für diesen Connector (geben Sie die IP-Adressen von Kafka Connect und Elasticsearch sowie Benutzernamen und Passwörter an):

curl https://KC_IP:8083/connectors/elastic-sink-tides/config -k -u KC_user:KC_password -X PUT -H 'Content-Type: application/json' -d '
{
    "connector.class" : "com.datamountaineer.streamreactor.connect.elastic7.ElasticSinkConnector",
    "tasks.max" : 100,
    "topics" : "test1.public.test1",
    "connect.elastic.hosts" : "ES_IP",
    "connect.elastic.port" : 9201,
    "connect.elastic.kcql" : "INSERT INTO test-index SELECT * FROM test1.public.test",
    "connect.elastic.use.http.username" : "ES_user",
    "connect.elastic.use.http.password" : "ES_password"
  }
}'

Der Task wurde korrekt ausgeführt. Beachten Sie, dass wir für die Verarbeitung von 7.000 Ereignissen pro Sekunde mehrere Sink-Connector-Tasks benötigen, und Sie müssen auch die Anzahl der Kafka-Partitionen entsprechend erhöhen (Partitionen >= Tasks).

Eine Einschränkung dieser Connector-Konfiguration besteht darin, dass sie alle Ereignisse als insert-Ereignisse verarbeitet. Unsere Daten-Änderungsereignisse können jedoch before– und after-Felder haben, von denen er nichts weiß, wodurch Sie „Junk“ im Elasticsearch-Index erhalten, den Sie anschließend interpretieren müssen. Eine einfache Lösung ist die Verwendung einer SMT (Single Message Transformation) auf dem Sink-Connector, um nur die after-Felder zu extrahieren. Ich habe den ExtractNewRecordState SMT zum „Abflachen der Ereignisse“ verwendet. Hier ist die endgültige Konfiguration des Debezium PostgreSQL-Quellconnectors einschließlich des SMT:

curl https://KC_IP:8083/connectors -X POST -H 'Content-Type: application/json' -k -u kc_user:kc_password -d '{
  "name": "debezium-test1",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "pg_ip",
    "database.port": "5432",
    "database.user": "pg_user",
    "database.password": "pg_password",
    "database.dbname" : "postgres",
    "database.server.name": "test1",
    "plugin.name": "pgoutput",
    "value.converter": "org.apache.kafka.connect.json.JsonConverter",
    "value.converter.schemas.enable": "false",
    "key.converter": "org.apache.kafka.connect.json.JsonConverter",
    "key.converter.schemas.enable": "false",
    "transforms": "unwrap",
    "transforms.unwrap.type":     "io.debezium.transforms.ExtractNewRecordState"
      }
}
'

Um schließlich alles zu überprüfen, habe ich meine ursprünglichen NOAA-Pipeline-Daten und -Themen wiederverwendet. Dadurch konnte ich auch prüfen, ob die JSON-Daten wie erwartet in Elasticsearch indiziert wurden (obwohl ich dieses Mal nur Standard-Zuordnungen verwendet habe), und ich konnte auch prüfen, ob es einen Unterschied im Durchsatz zwischen PostgreSQL JSON- und JSONB-Lesevorgängen (mit einem GIN-Index) gab – ich freue mich, berichten zu können, dass es keinen gab.

Wie ich jedoch in der Pipeline-Blogserie Teil 8 entdeckte, haben Elasticsearch Sink-Connectors Schwierigkeiten, mehr als 1.800 Einfügungen pro Sekunde zu indizieren, was weit unter der Task-Beschränkung bei Single-Thread-Connectors von 7.000 Ereignissen pro Sekunde liegt (in Teil 9 haben wir jedoch mit einem Workaround und der BULK-API bessere Ergebnisse erzielt), womit jeglicher Unterschied zwischen JSON- und JSONB-Performance eventuell maskiert wird, aber das ist sicherlich nicht das Haupt-Performanceproblem.

5. Fazit

In diesem Blog haben wir erfolgreich eine Test-CDC-Pipeline von PostgreSQL zu einem Beispiel-Sink-System (z. B. Elasticsearch) unter Verwendung des Debezium PostgreSQL Connectors und des Instaclustr Managed Kafka Connect und OpenDistro Elasticsearch-Dienst bereitgestellt, konfiguriert und ausgeführt. Für viele Anwendungsfälle werden Sie komplexere Kafka Sink-Connectors benötigen, um die Semantik des Daten-Änderungsereignisses und ihre Anwendung auf verschiedene Sink-Systems zu interpretieren, und es gibt noch viele weitere Konfigurationsoptionen, die ich nicht berücksichtigt habe. In Anbetracht der potenziellen Einschränkungen im Single-Task-Betrieb und anderer potenzieller Eigenarten in Bezug auf die Performance sollten Sie ebenfalls einen geeigneten Leistungs- und Verzögerungstest mit realistischen Daten und angemessen dimensionierten Systemen durchführen, bevor Sie in die Produktion gehen.

Hinweis: Die Experimente in diesem Blog wurden in einer Entwicklungsumgebung durchgeführt. Dabei wurde eine Kombination aus Open-Source-/selbstverwaltetem PostgreSQL (nicht unser verwalteter PostgreSQL-Dienst) in Verbindung mit den verwalteten Diensten Kafka Connect und Elasticsearch von Instaclustr verwendet. Derzeit haben wir Kunden, die Debezium in einer privaten Preview für unseren verwalteten Cassandra-Dienst verwenden, aber zum Zeitpunkt der Veröffentlichung wird Debezium noch nicht für unseren verwalteten PostgreSQL-Dienst angeboten.

Der Orininalartikel stammt von Paul Brebner und wurde auf Instaclustr.com am 9. August 2022 veröffentlicht.