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
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}';
> doneDie 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
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.
| Symbol | Operator | Arity | Precedence |
& |
Boolean AND | Binary | Lowest |
| |
Boolean OR | Binary | Lowest |
!</ |
Boolean NOT | Unary | Highest |
<N> |
Followed By (where N is a positive number) | Binary | High |
<-> |
Followed By (equivalent to <1>) |
Binary | High |
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:
- Jedes Dokument wird in einen
tsvectorkonvertiert, wofürto_tsvectorverwendet wird. Normalerweise wird dies einmal bei INSERT/UPDATE durchgeführt, und der resultierendetsvectorwird in einer Spalte neben dem Originaldokument gespeichert. Indizes für diese Spalte beschleunigen die Suche. - Der Abfragetext wird in eine
tsquerymit einer der...to_tsquery-Funktionen konvertiert. - Die
tsquerywird hinsichtlich destsvectordes 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
| Limit | Value | Behaviour |
| Size of each individual lexeme | 2047 bytes | SQL ERROR raised |
Total size of a tsvector.This includes lexemes and positional information. (type binary repr/on-disk size, for details see ts_type.h) |
1048575 bytes (~1MiB) | SQL ERROR raised |
Total number of lexemes per tsvector |
264 | unknown (size limit is reached first) |
Total number of position entries per lexeme per tsvector |
255 | Position entries after 255th are discarded |
| Maximum value of a lexeme position entry | 16383 | Values greater than 16383 are rounded to 16383 |
Total number of lexemes and operators (nodes) per tsquery |
32768 | SQL ERROR raised |
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 ]) → float4Hier 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 ]) → float4Diese 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:
- Prüfen Sie
wal_level. Wenn dies nicht auflogicalsteht, setzen Sie es auflogical. (Dazu ist ein Server-Neustart und bei einem verwalteten Dienst ggf. Unterstützung erforderlich.) - Für PostgreSQL > 10+ sind keine zusätzlichen Plug-ins erforderlich, da
pgoutputverwendet wird (Sie müssen jedoch das Standard-Plug-inplugin.namein der Konfiguration des Connectors überschreiben, siehe unten). - Benutzerberechtigungen konfigurieren
a. Laut Anweisungen soll ein Debezium-Benutzer erstellt werden, der über die erforderlichen Mindestrechte verfügt (REPLICATION-undLOGIN-Rechte),
b. und umpgoutputzu 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}
NullWas 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.
SQLreduce: Reduzieren Sie ausführliche SQL-Abfragen auf minimale Beispiele
Entwickler sehen sich oft sehr großen SQL-Abfragen gegenüber, die Fehler verursachen. SQLreduce ist ein Werkzeug, um diese Komplexität auf eine minimale Abfrage zu reduzieren.
SQLsmith generiert zufällige SQL-Abfragen
SQLsmith ist ein Werkzeug, das zufällige SQL-Abfragen generiert und sie gegen einen PostgreSQL-Server (und andere DBMS-Typen) ausführt. Die Idee ist, dass durch Fuzz-Testing des Query Parsers und Executors Corner-Case-Bugs gefunden werden können, die sonst bei manuellen Tests oder mit dem festen Satz von Testfällen in der PostgreSQL-Regressionstestsuite unbemerkt bleiben würden. Es hat sich als ein
select
case when pg_catalog.lastval() < pg_catalog.pg_stat_get_bgwriter_maxwritten_clean() then case when pg_catalog.circle_sub_pt(
cast(cast(null as circle) as circle),
cast((select location from public.emp limit 1 offset 13)
as point)) ~ cast(nullif(case when cast(null as box) &> (select boxcol from public.brintest limit 1 offset 2)
then (select f1 from public.circle_tbl limit 1 offset 4)
else (select f1 from public.circle_tbl limit 1 offset 4)
end,
case when (select pg_catalog.max(class) from public.f_star)
~~ ref_0.c then cast(null as circle) else cast(null as circle) end
) as circle) then ref_0.a else ref_0.a end
else case when pg_catalog.circle_sub_pt(
cast(cast(null as circle) as circle),
cast((select location from public.emp limit 1 offset 13)
as point)) ~ cast(nullif(case when cast(null as box) &> (select boxcol from public.brintest limit 1 offset 2)
then (select f1 from public.circle_tbl limit 1 offset 4)
else (select f1 from public.circle_tbl limit 1 offset 4)
end,
case when (select pg_catalog.max(class) from public.f_star)
~~ ref_0.c then cast(null as circle) else cast(null as circle) end
) as circle) then ref_0.a else ref_0.a end
end as c0,
case when (select intervalcol from public.brintest limit 1 offset 1)
>= cast(null as "interval") then case when ((select pg_catalog.max(roomno) from public.room)
!~~ ref_0.c)
and (cast(null as xid) <> 100) then ref_0.b else ref_0.b end
else case when ((select pg_catalog.max(roomno) from public.room)
!~~ ref_0.c)
and (cast(null as xid) <> 100) then ref_0.b else ref_0.b end
end as c1,
ref_0.a as c2,
(select a from public.idxpart1 limit 1 offset 5) as c3,
ref_0.b as c4,
pg_catalog.stddev(
cast((select pg_catalog.sum(float4col) from public.brintest)
as float4)) over (partition by ref_0.a,ref_0.b,ref_0.c order by ref_0.b) as c5,
cast(nullif(ref_0.b, ref_0.a) as int4) as c6, ref_0.b as c7, ref_0.c as c8
from
public.mlparted3 as ref_0
where true;
Wie jedoch in diesem 40-zeiligen, 2,2 KB großen Beispiel sind die von SQLsmith generierten Zufallsabfragen, die einen Fehler auslösen, meist sehr groß und enthalten viel Rauschen, das nicht zum Fehler beiträgt. Bisher waren eine manuelle Überprüfung der Abfrage und eine mühsame Bearbeitung erforderlich, um das Beispiel auf einen minimalen Reproducer zu reduzieren, den Entwickler zur Behebung des Problems verwenden können.
Reduzieren Sie die Komplexität mit SQLreduce
Dieses Problem wird durch SQLreduce gelöst. SQLreduce nimmt als Eingabe eine beliebige SQL-Abfrage entgegen, die dann gegen einen PostgreSQL-Server ausgeführt wird. Es werden verschiedene Vereinfachungsschritte angewendet, wobei nach jedem Schritt geprüft wird, ob die vereinfachte Abfrage noch denselben Fehler von PostgreSQL auslöst. Das Endergebnis ist eine SQL-Abfrage mit minimaler Komplexität.
SQLreduce reduziert effektiv die Abfragen aus ursprünglichen Fehlerberichten von SQLsmith auf Abfragen, die manuell reduzierten Abfragen entsprechen. Zum Beispiel kann SQLreduce die obige Monsterabfrage effektiv auf Folgendes reduzieren:
SELECT pg_catalog.stddev(NULL) OVER () AS c5 FROM public.mlparted3 AS ref_0
Beachten Sie, dass SQLreduce nicht versucht, eine Abfrage abzuleiten, die semantisch identisch mit dem Original ist oder dasselbe Abfrageergebnis liefert – die Eingabe wird als fehlerhaft angenommen, und wir suchen nach der minimalen Abfrage, die dieselbe Fehlermeldung von PostgreSQL erzeugt, wenn sie gegen eine Datenbank ausgeführt wird. Wenn die Eingabeabfrage zufällig keinen Fehler erzeugt, ist die von SQLreduce ausgegebene Minimalabfrage nur SELECT.
Wie es funktioniert
Wir werden eine einfachere Abfrage verwenden, um zu demonstrieren, wie SQLreduce funktioniert und welche Schritte unternommen werden, um Rauschen aus der Eingabe zu entfernen. Die Abfrage ist falsch und enthält ein wenig Durcheinander, das wir entfernen wollen:
$ psql -c 'select pg_database.reltuples / 1000 from pg_database, pg_class where 0 < pg_database.reltuples / 1000 order by 1 desc limit 10'
ERROR: column pg_database.reltuples does not exist
Übergeben wir die Abfrage an SQLreduce:
$ sqlreduce 'select pg_database.reltuples / 1000 from pg_database, pg_class where 0 < pg_database.reltuples / 1000 order by 1 desc limit 10'
SQLreduce beginnt mit dem Parsen der Eingabe mit pglast und libpg_query, die den ursprünglichen PostgreSQL-Parser als Bibliothek mit Python-Bindungen bereitstellen. Das Ergebnis ist ein Parse-Baum, der die Grundlage für die nächsten Schritte bildet. Der Parse-Baum sieht wie folgt aus:
selectStmt
├── targetList
│ └── /
│ ├── pg_database.reltuples
│ └── 1000
├── fromClause
│ ├── pg_database
│ └── pg_class
├── whereClause
│ └── <
│ ├── 0
│ └── /
│ ├── pg_database.reltuples
│ └── 1000
├── orderClause
│ └── 1
└── limitCount
└── 10
Pglast enthält auch einen Query Renderer, der den Parse-Baum als SQL zurückgeben kann, wie in der regenerierten Abfrage unten gezeigt. Die Eingabeabfrage wird gegen PostgreSQL ausgeführt, um das Ergebnis zu bestimmen, in diesem Fall ERROR: column pg_database.reltuples does not exist.
Input query: select pg_database.reltuples / 1000 from pg_database, pg_class where 0 < pg_database.reltuples / 1000 order by 1 desc limit 10
Regenerated: SELECT pg_database.reltuples / 1000 FROM pg_database, pg_class WHERE 0 < ((pg_database.reltuples / 1000)) ORDER BY 1 DESC LIMIT 10
Query returns: ✔ ERROR: column pg_database.reltuples does not exist
SQLreduce arbeitet, indem es neue Parse-Bäume ableitet, die strukturell einfacher sind, SQL daraus generiert und diese Abfragen gegen die Datenbank ausführt. Die ersten Vereinfachungsschritte arbeiten auf dem obersten Knoten, wo SQLreduce versucht, ganze Teilbäume zu entfernen, um schnell ein Ergebnis zu finden. Die erste versuchte Reduktion ist das Entfernen von LIMIT 10:
SELECT pg_database.reltuples / 1000 FROM pg_database, pg_class WHERE 0 < ((pg_database.reltuples / 1000)) ORDER BY 1 DESC ✔
Das Abfrageergebnis ist immer noch ERROR: column pg_database.reltuples does not exist, was durch ein ✔ Häkchen angezeigt wird. Als nächstes wird ORDER BY 1 entfernt, wieder erfolgreich:
SELECT pg_database.reltuples / 1000 FROM pg_database, pg_class WHERE 0 < ((pg_database.reltuples / 1000)) ✔
Nun wird die gesamte Zielliste entfernt:
SELECT FROM pg_database, pg_class WHERE 0 < ((pg_database.reltuples / 1000)) ✔
Diese kürzere Abfrage ist immer noch äquivalent zum Original bezüglich der Fehlermeldung, die zurückgegeben wird, wenn sie gegen die Datenbank ausgeführt wird. Nun wird der erste erfolglose Reduktionsschritt versucht, bei dem die gesamte FROM-Klausel entfernt wird:
SELECT WHERE 0 < ((pg_database.reltuples / 1000)) ✘ ERROR: missing FROM-clause entry for table "pg_database"
Diese Abfrage ist auch fehlerhaft, löst aber eine andere Fehlermeldung aus, so dass der vorherige Parse-Baum für die nächsten Schritte beibehalten wird. Wieder wird ein ganzer Teilbaum entfernt, jetzt die WHERE-Klausel:
SELECT FROM pg_database, pg_class ✘ no error
Wir haben die Eingabeabfrage nun so weit reduziert, dass sie keine Fehler mehr ausgibt. Der vorherige Parse-Baum wird weiterhin beibehalten, der nun wie folgt aussieht:
selectStmt
├── fromClause
│ ├── pg_database
│ └── pg_class
└── whereClause
└── <
├── 0
└── /
├── pg_database.reltuples
└── 1000
Nun beginnt SQLreduce, sich in den Baum einzugraben. Es gibt mehrere Einträge in der pg_database entfernt, aber das funktioniert nicht, also wird pg_class entfernt:
SELECT FROM pg_class WHERE 0 < ((pg_database.reltuples / 1000)) ✘ ERROR: missing FROM-clause entry for table "pg_database"
SELECT FROM pg_database WHERE 0 < ((pg_database.reltuples / 1000)) ✔
Da wir eine neue minimale Abfrage gefunden haben, startet die Rekursion auf oberster Ebene mit einem weiteren Versuch, die WHERE-Klausel zu entfernen. Da das nicht funktioniert, versucht es, den Ausdruck durch NULL zu ersetzen, aber auch das funktioniert nicht.
SELECT FROM pg_database ✘ no error
SELECT FROM pg_database WHERE NULL ✘ no error
Nun wird eine neue Art von Schritt versucht: Expression Pull-up. Wir steigen in die WHERE-Klausel ab, wo wir A < B zuerst durch A und dann durch B ersetzen.
SELECT FROM pg_database WHERE 0 ✘ ERROR: argument of WHERE must be type boolean, not type integer
SELECT FROM pg_database WHERE pg_database.reltuples / 1000 ✔
SELECT WHERE pg_database.reltuples / 1000 ✘ ERROR: missing FROM-clause entry for table "pg_database"
Der erste Versuch hat nicht funktioniert, aber der zweite. Da wir die Abfrage vereinfacht haben, starten wir auf oberster Ebene neu, um zu prüfen, ob die FROM-Klausel entfernt werden kann, aber sie ist immer noch erforderlich.
Von A / B können wir wieder A hochziehen:
SELECT FROM pg_database WHERE pg_database.reltuples ✔
SELECT WHERE pg_database.reltuples ✘ ERROR: missing FROM-clause entry for table "pg_database"
SQLreduce hat die minimale Abfrage gefunden, die immer noch ERROR: column pg_database.reltuples does not exist mit diesem Parse-Baum auslöst:
selectStmt
├── fromClause
│ └── pg_database
└── whereClause
└── pg_database.reltuples
Am Ende des Laufs wird die Abfrage zusammen mit einigen Statistiken ausgegeben:
Minimal query yielding the same error:
SELECT FROM pg_database WHERE pg_database.reltuples
Pretty-printed minimal query:
SELECT
FROM pg_database
WHERE pg_database.reltuples
Seen: 15 items, 915 Bytes
Iterations: 19
Runtime: 0.107 s, 139.7 q/s
Diese minimale Abfrage kann nun überprüft werden, um den Fehler in PostgreSQL oder in der Anwendung zu beheben.
Über credativ
Die credativ GmbH ist ein herstellerunabhängiges Beratungs- und Dienstleistungsunternehmen mit Sitz in Mönchengladbach. Mit über 22 Jahren Entwicklungs- und Serviceerfahrung im open source Bereich kann die credativ GmbH Sie mit beispiellosem und individuell anpassbarem Support unterstützen. Wir sind hier, um Ihnen bei all Ihren open source Infrastruktur-Bedürfnissen zu helfen und Sie zu unterstützen.
Dieser Artikel wurde ursprünglich von Christoph Berg geschrieben.
SQLreduce: Große SQL-Queries auf Minimum reduzieren
Entwicklern begegnen oft große SQL-Queries, die einen Fehler werfen. SQLreduce ist ein Tool, mit dem diese Komplexität auf eine minimale Query reduziert wird.
SQLsmith generiert zufällige SQL-Queries
SQLsmith ist ein Tool, das zufällige SQL-Queries generiert und diese gegen einen PostgreSQL-Server laufen lässt (und andere DBMS-Typen). Die Idee ist, dass im Query-Parser und -Executor mit Fuzz-Testing Corner-Case-Bugs gefunden werden können, die mit manuellen Testen oder der festen Menge an Queries aus der PostgreSQL-Regression-Testsuite nicht gefunden worden wären. Es hat sich als effektives Tool bewährt, das seit 2015 über 100 Bugs in verschiedenen Teilen des PostgreSQL-Servers und anderen Produkten gefunden hat, darunter Security-Bugs, von Executor-Bugs bis zu Segfaults in Typ- und Index-Methoden.
2018 fand SQLsmith beispielsweise, dass die folgende Query einen Segfault in PostgreSQL verursacht:
select
case when pg_catalog.lastval() < pg_catalog.pg_stat_get_bgwriter_maxwritten_clean() then case when pg_catalog.circle_sub_pt(
cast(cast(null as circle) as circle),
cast((select location from public.emp limit 1 offset 13)
as point)) ~ cast(nullif(case when cast(null as box) &> (select boxcol from public.brintest limit 1 offset 2)
then (select f1 from public.circle_tbl limit 1 offset 4)
else (select f1 from public.circle_tbl limit 1 offset 4)
end,
case when (select pg_catalog.max(class) from public.f_star)
~~ ref_0.c then cast(null as circle) else cast(null as circle) end
) as circle) then ref_0.a else ref_0.a end
else case when pg_catalog.circle_sub_pt(
cast(cast(null as circle) as circle),
cast((select location from public.emp limit 1 offset 13)
as point)) ~ cast(nullif(case when cast(null as box) &> (select boxcol from public.brintest limit 1 offset 2)
then (select f1 from public.circle_tbl limit 1 offset 4)
else (select f1 from public.circle_tbl limit 1 offset 4)
end,
case when (select pg_catalog.max(class) from public.f_star)
~~ ref_0.c then cast(null as circle) else cast(null as circle) end
) as circle) then ref_0.a else ref_0.a end
end as c0,
case when (select intervalcol from public.brintest limit 1 offset 1)
>= cast(null as "interval") then case when ((select pg_catalog.max(roomno) from public.room)
!~~ ref_0.c)
and (cast(null as xid) <> 100) then ref_0.b else ref_0.b end
else case when ((select pg_catalog.max(roomno) from public.room)
!~~ ref_0.c)
and (cast(null as xid) <> 100) then ref_0.b else ref_0.b end
end as c1,
ref_0.a as c2,
(select a from public.idxpart1 limit 1 offset 5) as c3,
ref_0.b as c4,
pg_catalog.stddev(
cast((select pg_catalog.sum(float4col) from public.brintest)
as float4)) over (partition by ref_0.a,ref_0.b,ref_0.c order by ref_0.b) as c5,
cast(nullif(ref_0.b, ref_0.a) as int4) as c6, ref_0.b as c7, ref_0.c as c8
from
public.mlparted3 as ref_0
where true;
Allerdings sind die zufälligen Queries von SQLsmith wie in diesem 40-Zeilen, 2,2kB Beispiel oft sehr groß und enthalten viel Rauschen, das zum eigentlichen Fehler nicht beiträgt. Bisher musste die Query manuell inspiziert und aufwändig bearbeitet werden, um das Beispiel auf einen minimalen Reproducer zu reduzieren, der von Entwicklern benutzt werden kann, das Problem zu beheben.
Komplexität reduzieren mit SQLreduce
Dieses Problem wird von SQLreduce gelöst. SQLreduce nimmt eine beliebige SQL-Query als Eingabe und lässt sie gegen einen PostgreSQL-Server laufen. Verschiedene Vereinfachungsschritte werden angewandt und nach jedem Schritt überprüft, ob die vereinfachte Query immer noch den gleichen Fehler im PostgreSQL-Server verursacht. Das Endergebnis ist eine Query mit minimaler Komplexität.
SQLreduce kann die Queries aus den Original-Bugreports von SQLsmith effektiv auf Queries reduzieren, die manuell reduzierten Queries gleichen. Beispielsweise kann SQLreduce die riesen Query von oben auf diese Zeile reduzieren:
SELECT pg_catalog.stddev(NULL) OVER () AS c5 FROM public.mlparted3 AS ref_0
Dabei versucht SQLreduce nicht, eine Query zu erzeugen, die semantisch identisch zum Original ist, oder das gleiche Query-Ergebnis liefert. Die Eingabequery ist sowieso fehlerbehaftet, und es wird eine minimale Query gesucht, die die gleiche Fehlermeldung provoziert, wenn sie gegen PostgreSQL läuft. Sollte die Eingabequery keinen Fehler werfen, so findet SQLreduce einfach SELECT als minimale Query.
Wie es funktioniert
Wir benutzen eine einfachere Query, um zu zeigen, wie SQLreduce arbeitet, und welche Schritte genommen werden, um das Rauschen aus der Eingabe zu entfernen. Diese Query ist fehlerhaft und enthält einige Teile, die entfernt werden sollen:
$ psql -c 'select pg_database.reltuples / 1000 from pg_database, pg_class where 0 < pg_database.reltuples / 1000 order by 1 desc limit 10'
ERROR: column pg_database.reltuples does not exist
Wir geben die Query an SQLreduce:
$ sqlreduce 'select pg_database.reltuples / 1000 from pg_database, pg_class where 0 < pg_database.reltuples / 1000 order by 1 desc limit 10'
SQLreduce parst die Eingabe mit pglast und libpg_query, die den original PostgreSQL-Parse als Bibliothek mit Python-Bindings bereitstellen. Das Ergebnis ist ein Parsetree, der die Basis für die nächsten Schritte bildet. Der Parsetree sieht so aus:
selectStmt
├── targetList
│ └── /
│ ├── pg_database.reltuples
│ └── 1000
├── fromClause
│ ├── pg_database
│ └── pg_class
├── whereClause
│ └── <
│ ├── 0
│ └── /
│ ├── pg_database.reltuples
│ └── 1000
├── orderClause
│ └── 1
└── limitCount
└── 10
Pglast enthält außerdem einen Query-Renderer, der den Parsetree wieder in SQL übersetzen kann, unten als regenerierte Query gezeigt. Die Eingabequery wird mit PostgreSQL ausgeführt, um das Ergebnis zu erhalten, in diesem Fall ERROR: column pg_database.reltuples does not exist.
Input query: select pg_database.reltuples / 1000 from pg_database, pg_class where 0 < pg_database.reltuples / 1000 order by 1 desc limit 10
Regenerated: SELECT pg_database.reltuples / 1000 FROM pg_database, pg_class WHERE 0 < ((pg_database.reltuples / 1000)) ORDER BY 1 DESC LIMIT 10
Query returns: ✔ ERROR: column pg_database.reltuples does not exist
SQLreduce erzeugt neue Parsetrees, die strukturell einfacher sind, generiert davon SQL, und lässt diese Queries mit PostgreSQL laufen. Die ersten Vereinfachungsschritte passieren auf dem Toplevel-Knoten, wo SQLreduce versucht, ganze Teilbäume zu entfernen, um schnell zu Ergebnissen zu kommen. Der erste Reduktions-Versuch ist, LIMIT 10 zu entfernen:
SELECT pg_database.reltuples / 1000 FROM pg_database, pg_class WHERE 0 < ((pg_database.reltuples / 1000)) ORDER BY 1 DESC ✔
Das Query-Ergebnis ist immer noch ERROR: column pg_database.reltuples does not exist, angezeigt durch ein ✔ Häkchen. Als nächstes wird ORDER BY 1 entfernt, wiederum erfolgreich:
SELECT pg_database.reltuples / 1000 FROM pg_database, pg_class WHERE 0 < ((pg_database.reltuples / 1000)) ✔
Nun wird die gesamte Target-Liste entfernt:
SELECT FROM pg_database, pg_class WHERE 0 < ((pg_database.reltuples / 1000)) ✔
Bezogen auf die Fehlermeldung von PostgreSQL ist diese kürzere Query immer noch äquivalent zur Original-Query. Nun wird der erste nicht erfolgreiche Reduktionsschritt versucht, das Entfernen der FROM-Klausel:
SELECT WHERE 0 < ((pg_database.reltuples / 1000)) ✘ ERROR: missing FROM-clause entry for table "pg_database"
Diese Query ist ebenfalls fehlerhaft, aber verursacht eine andere Fehlermeldung, daher wird der bisherige Parsetree beibehalten. Wiederrum wird ein ganzer Teilbaum entfernt, diesmal die WHERE-Klausel:
SELECT FROM pg_database, pg_class ✘ no error
Wir haben die Eingabe nun so weit reduziert, dass gar kein Fehler mehr entsteht. Der bisherige Parsetree wird weiter beibehalten. Er sieht nun so aus:
selectStmt
├── fromClause
│ ├── pg_database
│ └── pg_class
└── whereClause
└── <
├── 0
└── /
├── pg_database.reltuples
└── 1000
SQLreduce beginnt nun, in den Baum abzusteigen. In der FROM-Klausel sind mehrere Einträge, also versucht es, die Liste zu kürzen. Als erstes wird pg_database entfernt, was nicht funktioniert, also wird stattdessen pg_class entfernt:
SELECT FROM pg_class WHERE 0 < ((pg_database.reltuples / 1000)) ✘ ERROR: missing FROM-clause entry for table "pg_database"
SELECT FROM pg_database WHERE 0 < ((pg_database.reltuples / 1000)) ✔
Da wir nun eine neue minimale Query gefunden haben, beginnt die Rekursion erneut beim Toplevel mit einem neuen Versuch, die WHERE-Klausel zu entfernen. Da das nicht funktioniert, versucht es, den Ausdruck durch NULL zu ersetzen, aber das funktioniert auch nicht:
SELECT FROM pg_database ✘ no error
SELECT FROM pg_database WHERE NULL ✘ no error
Nun wird eine neue Art von Schritt versucht: Expression Pull-Up. Wir steigen in die WHERE-Klausel ab, wo wir A < B zunächst durch A und dann durch B ersetzen:
SELECT FROM pg_database WHERE 0 ✘ ERROR: argument of WHERE must be type boolean, not type integer
SELECT FROM pg_database WHERE pg_database.reltuples / 1000 ✔
SELECT WHERE pg_database.reltuples / 1000 ✘ ERROR: missing FROM-clause entry for table "pg_database"
Der erste Versuch funktionierte nicht, aber der zweite. Da die Query vereinfacht wurde, beginnen wir erneut beim Toplevel mit einem Versuch, die FROM-Klausel zu entfernen, aber die wird weiterhin benötigt.
In A / B können wir nun A hochziehen:
SELECT FROM pg_database WHERE pg_database.reltuples ✔
SELECT WHERE pg_database.reltuples ✘ ERROR: missing FROM-clause entry for table "pg_database"
SQLreduce hat die minimale Query gefunden, die immer noch ERROR: column pg_database.reltuples does not exist verursacht. Der Parsetree ist nun:
selectStmt
├── fromClause
│ └── pg_database
└── whereClause
└── pg_database.reltuples
Am Ende des Laufs wird die Query mit ein paar Statistiken ausgedruckt:
Minimal query yielding the same error:
SELECT FROM pg_database WHERE pg_database.reltuples
Pretty-printed minimal query:
SELECT
FROM pg_database
WHERE pg_database.reltuples
Seen: 15 items, 915 Bytes
Iterations: 19
Runtime: 0.107 s, 139.7 q/s
Diese minimale Query kann nun benutzt werden, um den Bug in PostgreSQL oder der Anwendung zu fixen.
Über credativ
Die credativ GmbH ist ein herstellerunabhängiges Beratungs- und Dienstleistungsunternehmen mit Standort in Mönchengladbach. Mit über 22+ Jahren an Entwicklungs- und Dienstleistungserfahrung im Open Source Bereich, kann die credativ GmbH Sie mit einem beispiellosen und individuell konfigurierbaren Support professionell Begleiten und Sie in allen Fragen bei Ihrer Open Source Infrastruktur voll und ganz unterstützen.
Seit dem erfolgreichen Merger mit Instaclustr 2021 ist die credativ GmbH das europäische Hauptquartier der Instaclustr Gruppe und hilft Unternehmen bei der Realisierung eigener Applikationen im großen Umfang dank Managed-Plattform-Solutions für Open Source Technologien wie zum Beispiel Apache Cassandra®, Apache Kafka®, Apache Spark™, Redis™, OpenSearch™, Apache ZooKeeper™, PostgreSQL® und Cadence.
Instaclustr kombiniert eine komplette Dateninfrastruktur-Umgebung mit praktischer Expertise, Support und Consulting um eine kontinuierliche Leistung und Optimierung zu gewährleisten. Durch Beseitigung der Komplexität der Infrastruktur, wird es Unternehmen ermöglicht, ihre internen Entwicklungs- und Betriebsressourcen auf die Entwicklung innovativer kundenorientierter Anwendungen zu geringeren Kosten zu konzentrieren. Zu den Kunden von Instaclustr gehören einige der größten und innovativsten Fortune-500-Unternehmen.
Das Debian-Projekt hat soeben Version 11 („Bullseye“) ihres freien Betriebssystems veröffentlicht. Insgesamt haben über 6,208 Contributor am neuesten Release mitgearbeitet und zur erfolgreichen Umsetzung beigetragen. Wir möchten allen Beteiligten für ihre gemeinsamen Anstrengungen und harte Arbeit, die sie in den letzten Jahren in die Entwicklung dieser neuen Veröffentlichung investiert haben, danken.
Herzlichen Glückwunsch an die Debian-Community die wieder einmal gezeigt hat, dass internationale Zusammenarbeit für Freie Software einfach spitze sein kann.
Darüber hinaus möchten wir auch unseren eigenen Debian-Entwicklern die Anerkennung geben die sie sich verdient haben. Wir schätzen die Arbeit aller unser Kollegen sehr, und unterstützen sie ganz klar Beiträge zu Open Source und Freie Software Projekten weltweit einzureichen.
Das neue release, Debian 11, kann hier heruntergeladen werden:
https://www.debian.org/devel/debian-installer/index.en.html
Neues in Debian 11 „Bullseye“
Debian 11 kommt mit einer Menge Aktualisierungen und Upgrades. Dieser neue Release enthält über 13.370 komplett neue Pakete, insgesamt also über 57.703 Pakete bei der Veröffentlichung. Von diesen wurden 35.532 Softwarepakete auf neuere Versionen aktualisiert, einschließlich einer Aktualisierung des Kernels von 4.19 in „Buster“ auf 5.10 in „Bullseye“.
Weiterhin erweitert „Bullseye“ die Möglichkeiten des treiberlosen Druckens mit CUPS und des treiberlosen Scannens mit SANE. Während es mit „Buster“ möglich war, CUPS für das treiberlose Drucken zu verwenden, kommt „Bullseye“ mit dem Paket ipp-usb, das es erlaubt, ein USB-Gerät als Netzwerkgerät zu behandeln und damit die Möglichkeiten des treiberlosen Druckens zu erweitern. SANE lässt sich damit verbinden, wenn es korrekt eingerichtet und an einen USB-Port angeschlossen ist.
Wie in den letzten releases kommt Debian 11 auch mit einer Debian Edu / Skolelinux version. Debian Edu stellt seit vielen Jahren eine Komplettlösung für Schulen dar. Das gesamte Schulnetzwerk wird durch Debian Edu bereitgestellt und es müssen nach Installation nur noch Benutzer und Maschinen hinzugefügt werden. Dies kann ganz komfortabel über die Weboberfläche GOsa² erledigt werden.
Für weitere Informationen und technische Details zum neuen Debian 11 Release lesen Sie bitte die offiziellen Release Notes auf Debian.org
https://www.debian.org/releases/bullseye/amd64/release-notes/
Beiträge von credativ Mitarbeitern
credativ war schon immer ein aktiver Teil der Debian-Community und hat seit 2004 jede DebConf besucht. Desweiteren findet Debian Gebrauch als Haupt-Betriebssystem in der Managed Platform von Instaclustr.
credativ Mitarbeiter haben diverse Aufgaben im neuen Release von Debian 11 übernommen. Hier ist ein kleiner Ausschnitt an Beiträgen und Mitarbeit unserer Kollegen:
- 90% der PostgreSQL® Paketierung des neuen Release
- Wartung mitarbeit an verschiedenen Paketen
- Unterstützung als Debian-Sys-Admin
- Mitarbeit and Debian Edu / Skolelinux
- Entwicklungsarbeit an Kernel-Images
- Entwicklungsarbeit an Cloud-Images
- Entwicklungsarbeit für diverse Debian Backports
- Mitarbeit und Unterstützung bei salsa.debian.org
Jeder unserer Kollegen hat einen signifikanten Beitrag zum aktuellen Releasegeleistet und deshalb noch einmal einen herzlichen Dank an alle Debianer:
- Adrian Vondendriesch
- Alexander Wirt (Formorer)
- Bastian Blank (Waldi)
- Christoph Berg (Myon)
- Dominik George (Natureshadow)
- Felix Geyer (fgeyer)
- Martin Zobel-Helas (Zobel)
- Michael Banck (azeem)
- Dr. Michael Meskes (feivel)
- Noël Köthe (Noel)
- Sven Bartscher (kritzefitz)
Wie upgraden Sie am besten von Debian 10 zu Debian 11?
Debian 11 „Bullseye“ ist ein weiteres Major Release und als solches empfehlen wir allen Lesern zeitnah zu upgraden. Debian 10 “Buster” wird zwar noch bis zum Juni 2024 mit LTS supported, dennoch macht es Sinn sich jetzt schon mal mit einem möglichen Upgrade auseinander zu setzen.
Da es sich jedoch um ein Major Release handelt, erfordert der Upgrade-Prozess ein gewisses Maß an manueller Arbeit, um ein reibungsloses Upgrade zu gewährleisten.
Eine ausführliche Anleitung ist auf debian.org verfügbar, daher werden wir hier nur die fünf wichtigsten Schritte auflisten:
- Zur Vorbereitung sollten Sie alle Daten, die nicht verloren gehen sollen, sichern und sich auf die Wiederherstellung vorbereiten
- Entfernen Sie alle Nicht-Debian-Pakete und bereinigen Sie übrig gebliebene Dateien und alte Versionen
- Führen Sie das ein Upgrade auf die aktuellste minor Version durch
- Überprüfen Sie Ihre APT-Quelldateien und bereiten Sie sie vor, indem Sie die relevanten Internetquellen oder lokalen Spiegelserver hinzufügen
- Aktualisieren Sie Ihre Pakete und aktualisieren Sie dann Ihr System
Alle bestehenden Kunden, die eine auf Debian basierende Installation betreiben, sind natürlich durch unseren Service und Support abgedeckt und können sich jederzeit an uns wenden.
Wenn Sie an einem Upgrade Ihrer alten Debian-Version interessiert sind oder wenn Sie Fragen zu Ihrer eigenen Open-Source-Infrastruktur haben, zögern Sie nicht, uns eine E-Mail zu schreiben oder uns unter info@credativ.de zu kontaktieren.
Oder Sie können in wenigen Minuten mit einer der Open-Source-Technologien wie Apache Cassandra, Apache Kafka, Redis und OpenSearch auf der Instaclustr Managed Platform loslegen. Melden Sie sich noch heute für eine kostenlose Testversion an.
Moodle ist eine beliebte Open Source Online-Lernplattform. Gerade in Zeiten von COVID19 und Distanzunterricht hat sich die Bedeutung von Moodle für Schulen und Universität weiter verstärkt, da ganze Bundesländer ihren Schulunterricht innerhalb weniger Tage auf Moodle und andere Plattformen wie BigBlueButton umgestellt haben. Dies führt unweigerlich zu Skalierungs-Problemen, wenn plötzlich mehrere zehntausend Schüler auf Moodle zugreifen müssen. Neben der Skalierung der Moodle Applikation selber muss auch die Datenbank bedacht werden, hier kann unter anderem PostgreSQL verwendet werden. Im Folgenden werden Möglichkeiten zum Load-Balancing für Moodle unter PostgreSQL vorgestellt.
Hochverfügbarkeit durch Patroni
Eine Online-Lernplattform ist aus Sicht des Bildungssystems kritische Infrastruktur und sollte dementsprechend Hochverfügbar ausgelegt sein, was insbesondere die Datenbank betrifft. Eine gute Lösung für PostgreSQL ist hier Patroni, über dessen Debian-Integration wir bereits früher berichtet haben. Kurz zusammengefasst verwendet Patroni einen “Distributed Consensus Store” (DCS) um aus einem typischerweise 3-Knoten Cluster einen sogenannten Leader zu wählen bzw. bei Ausfalls eines Leaders ein Failover zu initiieren und einen neuen Leader zu wählen, ohne dabei in ein Split-Brain Szenario zu gelangen. Zusätzlich bietet Patroni eine REST-API, die für die Kommunikation der Knoten untereinander und von dem patronictl Programm verwendet wird, z.B. um die Postgres-Konfiguration Online auf allen Knoten zu ändern oder einen Switchover einzuleiten.
Client-Lösungen für Hochverfügbarkeit
Aus Sicht von Moodle muss allerdings zusätzlich gewährleistet sein, dass es mit dem Leader verbunden ist, da sonst keine schreibenden Transaktionen möglich sind. Herkömmliche Hochverfügbarkeits-Lösungen wie Pacemaker verwenden hier virtuelle IPs (VIPs), die im Failover-Fall zum neuen Primary-Knoten geschwenkt werden. Für Patroni gibt es stattdessen das vip-manager Projekt, welches den Leader-Key im DCS überwacht und Cluster-VIP lokal setzt oder entfernt. Dieses ist ebenfalls in Debian integriert.
Eine Alternative ist die Verwendung von Client-seitigem Failover basierend auf der libpq-Bibliothek von PostgreSQL. Hierfür werden alle Cluster-Mitglieder im Verbindungs-String aufgelistet und die Verbindungs-Option target_session_attrs=read-write gewählt. So konfiguriert versucht der Client bei einem Verbindungs-Abbruch die anderen Knoten zu erreichen, bis ein neuer Primary gefunden wird.
Eine andere Möglichkeit ist HAProxy, ein hoch-skalierender TCP/HTTP Load-Balancer. Durch die Möglichkeit periodische Health-Checks auf die Patroni REST-API der einzelnen Knoten durchzuführen kann es den aktuellen Leader ermitteln und Client-Anfragen dorthin weiterleiten.
Datenbank-Konfiguration von Moodle
Die Verbindung von Moodle zu einer PostgreSQL-Datenbank wird in config.php konfiguriert, z.B. für eine einfache Stand-Alone Datenbank:
$CFG->dbtype = 'pgsql';
$CFG->dblibrary = 'native';
$CFG->dbhost = '192.168.1.1';
$CFG->dbname = 'moodle';
$CFG->dbuser = 'moodle';
$CFG->dbpass = 'moodle';
$CFG->prefix = 'mdl_';
$CFG->dboptions = array (
'dbport' => '',
'dbsocket' => ''
);Hier wird der Standard-Port 5432 verwendet.
Falls Streaming-Replication verwendet wird, können die Standbys zusätzlich als readonly definiert werden und einen eigenen Datenbank-Benutzer (der lediglich Leseberechtigungen benötigt) zugeordnet werden:
$CFG->dboptions = array (
[...]
'readonly' => [
'instance' => [
[
'dbhost' => '192.168.1.2',
'dbport' => '',
'dbuser' => 'moodle_safereads',
'dbpass' => 'moodle'
],
[
'dbhost' => '192.168.1.3',
'dbport' => '',
'dbuser' => 'moodle_safereads',
'dbpass' => 'moodle'
]
]
]
);Failover/Load-Balancing mit libpq
Wenn allerdings ein hochverfügbarer Postgres-Cluster mit Patroni verwendet wird kann wie oben beschrieben der Primary im Failover- oder Switchover-Fall wechseln. Moodle bietet hier keine Möglichkeit generische Datenbank-Optionen zu setzen und damit ist die Einstellung von target_session_attrs=read-write direkt nicht möglich. Wir haben hierfür einen Patch entwickelt und in den Moodle Tracker eingestellt. Dieser erlaubt die zusätzliche Option 'dbfailover' => 1, im $CFG->dboptions-Array, was die nötige Verbindungs-Option target_session_attrs=read-write hinzufügt. Eine angepasste config.php sähe damit z.B. so aus:
$CFG->dbtype = 'pgsql';
$CFG->dblibrary = 'native';
$CFG->dbhost = '192.168.1.1,192.168.1.2,192.168.1.3';
$CFG->dbname = 'moodle';
$CFG->dbuser = 'moodle';
$CFG->dbpass = 'moodle';
$CFG->prefix = 'mdl_';
$CFG->dboptions = array (
'dbfailover' => 1,
'dbport' => '',
'dbsocket' => '',
'readonly' => [
'instance' => [
[
'dbhost' => '192.168.1.1',
'dbport' => '',
'dbuser' => 'moodle_safereads',
'dbpass' => 'moodle'
],
[
'dbhost' => '192.168.1.2',
'dbport' => '',
'dbuser' => 'moodle_safereads',
'dbpass' => 'moodle'
],
[
'dbhost' => '192.168.1.3',
'dbport' => '',
'dbuser' => 'moodle_safereads',
'dbpass' => 'moodle'
]
]
]
);Failover/Load-Balancing mit HAProxy
Wenn stattdessen HAProxy verwendet werden soll, dann muss entsprechend der HAProyx-Host als $CFG->dbhost eingetragen werden, z.B. 127.0.0.1 wenn HAProxy lokal auf dem/den Moodle Server(n) läuft. Zusätzlich kann ein zweiter Port (z.B. 65432) für lesende Anfragen definiert werden, der analog zu einem Streaming-Replication Standby als readonly in $CFG->dboptions konfiguriert wird. Die config.php sähe dann z.B. so aus:
$CFG->dbtype = 'pgsql';
$CFG->dblibrary = 'native';
$CFG->dbhost = '127.0.0.1';
$CFG->dbname = 'moodle';
$CFG->dbuser = 'moodle';
$CFG->dbpass = 'moodle';
$CFG->prefix = 'mdl_';
$CFG->dboptions = array (
'dbport' => '',
'dbsocket' => '',
'readonly' => [
'instance' => [
'dbhost' => '127.0.0.1',
'dbport' => '65432',
'dbuser' => 'moodle_safereads',
'dbpass' => 'moodle'
]
]
);Die HAProxy Konfigurations-Datei haproxy.cfg kann beispielhaft folgendermaßen aussehen:
global
maxconn 100
defaults
log global
mode tcp
retries 2
timeout client 30m
timeout connect 4s
timeout server 30m
timeout check 5s
listen stats
mode http
bind *:7000
stats enable
stats uri /
listen postgres_write
bind *:5432
mode tcp
option httpchk
http-check expect status 200
default-server inter 3s fall 3 rise 3 on-marked-down shutdown-sessions
server pg1 192.168.1.1:5432 maxconn 100 check port 8008
server pg2 192.168.1.2:5432 maxconn 100 check port 8008
server pg3 192.168.1.3:5432 maxconn 100 check port 8008HAProxy erwartet eingehende schreibende Verbindungen (postgres_write) auf Port 5432 und sendet sie an Port 5432 der Cluster-Mitglieder weiter. Der Primary wird durch einen HTTP-Check auf Port 8008 (dem Standard Patroni REST-API Port) ermittelt; Patroni gibt hier für den Primary den Status 200 und für Standbys den Status 503 zurück.
Für die lesenden Abfragen (postgres_read) muss entschieden werden, ob der Primary auch read-only Anfragen beantworten soll oder nicht. Wenn dies der Fall ist kann ein einfacher Postgres-Check verwendet werden; allerdings kann dies zu Einträgen im Postgres-Log bzgl. fehlerhafter oder unvollständiger Logins führen:
listen postgres_read
bind *:65432
mode tcp
balance leastconn
option pgsql-check user haproxy
default-server inter 3s fall 3 rise 3 on-marked-down shutdown-sessions
server pg1 192.168.1.1:5432 check
server pg2 192.168.1.2:5432 check
server pg3 192.168.1.3:5432 checkWenn der Primary nicht am Read-Scaling teilnehmen soll kann einfach der gleiche HTTP-Check wie in der postgres_write Sektion verwendet werden, wobei diesmal der HTTP-Status 503 erwartet wird:
listen postgres_read
bind *:65432
mode tcp
balance leastconn
option httpchk
http-check expect status 503
default-server inter 3s fall 3 rise 3 on-marked-down shutdown-sessions
server pg1 192.168.1.1:5432 check port 8008
server pg2 192.168.1.2:5432 check port 8008
server pg3 192.168.1.3:5432 check port 8008Überarbeitetes Ansible Playbook
HAProxy-Unterstützung wurde auch in Version 0.3 unseres Ansible-Playbooks für die automatisierte Einrichtung eines Drei-Knoten-PostgreSQL-Patroni-Clusters unter Debian implementiert. Mit der neuen Variable haproxy_primary_read_scale kann entschieden werden, ob HAProxy Anfragen auf den Read-Only Port auch an den Primary-Knoten oder nur an die Follower geben soll.
Unterstützung
Falls Sie Unterstützung bei PostgreSQL, Patroni, HAProxy, Moodle oder anderer Software benötigen, steht Ihnen unser Open Source Support Center zur Verfügung – Falls gewünscht auch 24 Stunden am Tag, an 365 Tagen im Jahr.
Wir freuen uns über Ihre Kontaktaufnahme.
Hintergrund
Mit PostgreSQL® 13 lernt das VACUUM–Kommando eine neue Fähigkeit: Das parallele Aufräumen von Indexen. VACUUM in einer PostgreSQL®-Datenbank ist im wesentlichen für mehrere wichtige Aufgaben verantwortlich:
- Garbage Collection, also freigeben von gelöschten Tupel für die Wiederverwendung
- Verhindern von Bloat in einer Datenbank und damit von ausuferndem Plattenplatzbedarfs
- Verhindern des Überlaufs des Transaktionszählers (FREEZE)
- Diverses Aufräumen von Transaktionsinformationen, usw.
Neben dem manuellen VACUUM Kommando gibt es noch den sogenannten Autovacuum Datenbankprozess, der sich automatisch im Hintergrund um diese Aufgaben kümmert. Im Groben entspricht die Arbeitsweise von Autovacuum auch dem Vorgehen bei Ausführung eines manuellem VACUUM-Kommandos. Diese gliedert sich im Wesentlichen in mehrere Phasen:
- Scannen der Tabellenblöcke und Ermitteln von darin enthaltenen, toten Tupeln und, falls notwendig, FREEZE
- Die toten Tupel werden im Speicher gesammelt (maintenance_work_mem ist hier die Obergrenze)
- Löschen der Tupelreferenzen aus entsprechenden Indexen (Vacuum Index Phase)
- Für jeden gescannten Tabellenblock, Löschen der darin enthaltenen toten Tupel
- Für jeden gescannten Tabellenblock, Reorganisieren der lebenden Tupel
- Aktualisieren der Visibility Map und Freespace Map
- Ist maintenance_work_mem voll, bevor das Ende einer Tabelle erreicht wurde, zurück zu 2.)
- Aufräumen (Cleanup Phase), u.a. Aktualisierung der Index-Statistiken für VACUUM
- Löschen von leeren Tabellenblöcken am Ende der Tabelle (falls vorhanden)
- Aktualisieren der Laufzeitstatistiken
- Aufräumen von Transaktionsinformationen
Arbeitsteilung
Mit der neuen Kommandooption PARALLEL kann das VACUUM Kommando nun die Vacuum Index und Cleanup Phase mit mehreren Arbeitsprozessen (Workern) parallel durchführen, z.B.:
VACUUM (ANALYZE, PARALLEL 4) my_table;
Dies führt ein VACUUM mit bis zu vier parallelen Workern mit zusätzlicher Aktualisierung der Optimizer Statistiken durch. Das bisherige Verfahren bleibt für die Tabelle selbst unverändert, mit PARALLEL verarbeitet VACUUM nun mit jeweils einem dedizierten Worker einen spezifischen Index. Zu beachten ist, dass der Leader, also die Session die das VACUUM-Kommando ausführt, ebenfalls entsprechende Arbeit verrichtet. Wird „0“ als Anzahl der Worker spezifiziert, wird die parallele Verarbeitung der Indexe deaktiviert und der bisherige Arbeitsablauf verwendet. Sind weniger als vier Indexe auf der Tabelle vorhanden, so wird die Anzahl der Worker entsprechend angepasst. Auch kann es passieren, dass aufgrund nebenläufiger DDL Kommandos (anderes paralleles VACUUM oder parallelisiertes CREATE INDEX) weniger Worker ausgewählt werden, da die Anzahl der maximal verfügbaren Worker Prozesse entweder durch max_parallel_maintenance_workers oder max_worker_processes begrenzt wird.
Parallel VACUUM als Standardoption
PARALLEL ist in der aktuellen Beta2 von PostgreSQL® 13 als Standardoption aktiviert. Wird die Option weggelassen, richtet sich die Zahl der Worker nach der Anzahl an Indexe pro Tabelle. Ob ein Index überhaupt für die parallele Verarbeitung vorgesehen wird, entscheidet auch der Konfigurationsparameter min_parallel_index_scan_size. Die Standardeinstellung ist 512kB.
Der Administrator sollte bei der Konfiguration von PostgreSQL® darauf achten, entsprechenden Spielraum bei der Parametrisierung von max_worker_processes einzuplanen, da mit dem seit PostgreSQL® 11 verfügbaren parallelisierten CREATE INDEX in PostgreSQL® 13 nun ein neues parallelisierbares DDL-Kommando hinzukommt.
Verbesserung bei der Geschwindigkeit
Das parallele Verarbeiten der Indexe sollte VACUUM gerade bei größeren Mengen an gelöschten Tupel bei entsprechender Anzahl an Indexen deutlich beschleunigen. Um diesen theoretischen Vorteil in der Praxis zu zeigen, verwenden wir an dieser Stelle ein Sample Dataset der IMDb Datenbank. Die Tabelle cast_info hat folgendes Schema:
Table "public.cast_info"
Column │ Type │ Collation │ Nullable │ Default
────────────────┼─────────┼───────────┼──────────┼───────────────────────────────────────
id │ integer │ │ not null │ nextval('cast_info_id_seq'::regclass)
person_id │ integer │ │ not null │
movie_id │ integer │ │ not null │
person_role_id │ integer │ │ │
note │ text │ │ │
nr_order │ integer │ │ │
role_id │ integer │ │ not null │
Indexes:
"cast_info_pkey" PRIMARY KEY, btree (id)
"cast_info_idx_cid" btree (person_role_id)
"cast_info_idx_mid" btree (movie_id)
"cast_info_idx_pid" btree (person_id)
"test_idx" btree (note)
Foreign-key constraints:
"movie_id_exists" FOREIGN KEY (movie_id) REFERENCES title(id)
"person_id_exists" FOREIGN KEY (person_id) REFERENCES name(id)
"person_role_id_exists" FOREIGN KEY (person_role_id) REFERENCES char_name(id)
"role_id_exists" FOREIGN KEY (role_id) REFERENCES role_type(id)
Die Tabelle ist ca. 2759 MByte groß und verfügt über insgesamt fünf Indexe, deren Größen sich wie folgt manifestieren:
SELECT indexrelid::regclass, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_index WHERE indrelid = 'cast_info'::regclass;
indexrelid │ pg_size_pretty
───────────────────┼────────────────
cast_info_pkey │ 1108 MB
cast_info_idx_cid │ 421 MB
cast_info_idx_mid │ 396 MB
cast_info_idx_pid │ 452 MB
test_idx │ 388 MB
(5 rows)
Damit Autovacuum den Test nicht stört, wird er für die Tabelle deaktiviert:
ALTER TABLE cast_info SET(autovacuum_enabled = 'false');
Folgender SQL aktualisiert in der Tabelle ca. 1.05 Mio Tupel in dem es rein fiktiv die Rollenzuordnung von bestimmten Datensätzen ändert. Die Ausgabe ist ein wenig gekürzt, um das Beispiel besser zu veranschaulichen:
UPDATE cast_info SET role_id = 12 WHERE role_id = 6; UPDATE 1055730
Anschließend wird der Parameter max_parallel_maintenance_workers auf 4 gesetzt, um maximal vier parallele Worker für VACUUM zu ermöglichen.
SET max_parallel_maintenance_workers TO 4; VACUUM cast_info ; Time: 6872,329 ms (00:06,872)
Wiederholt man den UPDATE mit umgekehrter Bedingung und anschließendem nicht-parallelisiertem VACUUM ergibt sich folgendes Bild:
UPDATE cast_info SET role_id = 6 WHERE role_id = 12; UPDATE 1055730 VACUUM (PARALLEL 0) cast_info ; Time: 45435,458 ms (00:45,435)
Die parallele Verarbeitung von Indexen in diesem Testfall zeigt einen mehr als deutlichen Vorteil zugunsten des parallelisierten VACUUM. Die folgende Grafik veranschaulicht die Laufzeiten noch einmal:
Fazit
Mit PostgreSQL® 13 lernt VACUUM Indexe parallel zu verarbeiten. Dies sorgt für einen deutlichen Geschwindigkeitsvorteil bei der Wartung von Tabellen. Ein Wermutstropfen aktuell ist, dass der automatische Hintergrundprozess Autovacuum noch nicht von diesem Feature profitieren kann, nur manuelles VACUUM unterstützt aktuell parallele Verarbeitung. Administratoren, die beispielsweise jedoch große Batchverarbeitungen mit Tabellen mit vielen Indexen direkt im Anschluß mit manuellem VACUUM optimieren, können von parallel VACUUM direkt profitieren.
Bei Fragen rund um den Einsatz von PostgreSQL® 13 parallel VACUUM stehen wir Ihnen natürlich gerne zur Verfügung. Sprechen Sie uns an! Unser PostgreSQL® Competence Center ist bei Bedarf 24 Stunden am Tag, an 365 Tagen im Jahr für Sie verfügbar.
Mit der kommenden Version 13 von PostgreSQL® werden B-tree Indexe in der Lage sein, mehrfach identisch vorkommende Spaltenwerte physikalisch kompakter abzuspeichern. Bereits mit Version 12 wurde eine Optimierung eingeführt, die B-tree Indexe ein effizienteres Speichern von wiederholt vorkommenden Spaltenwerten gestattet. Hierzu werden diese Spalten in derselben Reihenfolge auch im Index abgelegt, wie diese auch physikalisch im sogenannten Heap, also der physischen Repräsentation der Tabelle, vorkommen. Dies vereinfacht die Verwaltung dieser Indexeinträge und kann auch bereits mit dieser Maßnahme das Wachstum eines Indexes positiv beeinflussen.
In Version 13 werden nun effektiv wiederholt vorkommende Spaltenwerte zusammengefasst im B-tree Index gespeichert, Deduplication genannt. Eine sogenannte Posting List verwaltet dabei, als alternative Repräsentation eines Index Tupel die physischen Positionen an welcher Stelle in der Tabelle der jeweilige Spaltenwert zu finden ist. Diese Repräsentation ist deutlich kompakter als die Spaltenwerte jeweils für sich zu speichern. Ferner ermöglicht es auch das schnellere Auffinden von entsprechenden Spaltenwerten und VACUUM kann solche Indexe auch effizienter warten.
In schreibenden Workloads, die wenige oder gar keine mehrfach identischen Spaltenwerte enthalten, können unter Umständen kleinere Auswirkungen auf die Performance beobachtet werden. Die Deduplication Funktionalität kann beim Erzeugen eines Indexes mit dem Indexattribut deduplication_items abgeschaltet werden. In der aktuellen Beta 2 von PostgreSQL® 13 ist Deduplication standardmässig aktiviert, was sich jedoch bis zum finalen Release noch ändern kann. Die folgende Syntax schaltet das Feature explizit beim Anlegen eines Index aus:
CREATE INDEX ON table (spalte) WITH(deduplicate_items=off);
Üblicherweise jedoch speichern Anwendungen in der Regel Spaltenwerte pro Spalte häufiger mehrfach ab. Ausnahme sind UNIQUE Constraints oder Primary Keys, aber selbst bei diesen kann Deduplication Vorteile bieten. Kommen mehrere Versionen einer Zeile mit dem spezifischen eindeutigen Spaltenwert vor, kann Deduplication unnötigen Bloat im Index ebenfalls verhindern.
Beispiel von Deduplication
Das Ergebnis der kompakteren Indexstruktur lässt sich mit nachfolgendem Beispiel illustrieren. Die Query in diesem Beispiel erzeugt eine Datenmenge aus 10.000.000 numerischen Werten, die zufällig per random() auf 1000 unterschiedliche Werte begrenzt wird. Dadurch ist die jeweilige Größe der mehrfach vorkommenden Spaltenwerte ungleich verteilt.
INSERT INTO unique_test SELECT floor(generate_series(1, 10000000) % (random() * 1000)::numeric);
Vergleicht man in PostgreSQL® 11.8, 12.3 und 13beta2 nun die Indexgrößen, ergibt sich folgendes Bild:
® 11, 12 und 13″ width=“1024″ height=“576″ /> B-tree Index Größe mit Deduplication, Vergleich zwischen PostgreSQL® 11, 12 und 13Man sieht deutlich die Größenunterschiede, die sich im Vergleich von PostgreSQL® 11 und 12 zu Version 13 ergeben. Deduplication reduziert die Indexgröße hier auf ca. 60 MByte, während bei PostgreSQL® 11 und 12 ca. 220 MByte belegt werden. Die Grafik zeigt auch, dass die in der Einleitung beschriebene Optimierung in PostgreSQL® 12 für diesen simulierten Fall nicht funktionieren. Allerdings werden diese im Laufe des Lebenszyklus des Index dann zum Tragen kommen.
Einschränkungen
Migrationen mit pg_upgrade
Ein wichtiger Punkt für die Nutzung von Deduplication in B-tree Indexen in PostgreSQL® 13 ist bei Migrationen zu beachten. Verwendet man pg_upgrade, um eine bestehende PostgreSQL® Instanz von beispielsweise PostgreSQL® 12 auf 13 zu aktualisieren, werden alle physische Objekte in den neuen Cluster übernommen. Da Deduplication in PostgreSQL® auf jeden Fall eine neue Indexstruktur verwendet, können bestehende Indexe aus dem alten Cluster daher nicht ohne weiteres die Deduplication Funktionalität verwenden und müssen neu angelegt werden. Dies kann beispielsweise mit dem PostgreSQL® Kommando REINDEX oder dem äquivalenten Kommandozeilentool reindexdb erreicht werden. Dies gilt für Upgrades von allen älteren Major-Releases. Auch für Migrationen auf PostgreSQL® 12 müssen B-tree Indexe, die von den dort angesprochenen Optimierungen profitieren möchten, neu angelegt werden. In PostgreSQL® 13 hilft dabei auch die neue Option –jobs von reindexdb diesen Prozess zu beschleunigen, indem Indexe parallel mit der angebenen Anzahl an Prozessen aufgebaut werden können.
Verwendung bestimmer Datentypen
Der numerische Datentyp numeric kann nicht dedupliziert werden, da der Skalierungsfaktor eines jeden Spaltenwertes erhalten bleiben muss. B-tree Indexe auf Spalten dieses Typs werden daher automatisch nicht dedupliziert. Dasselbe gilt im Wesentlichen für den Datentyp jsonb. Da dieser intern numeric verwendet unterliegt jsonb denselben Einschränkungen. Bei dem Datentyp float4 gibt es unterschiedliche Repräsentationen des Wertes 0. Wie bei numeric mit dem entsprechenden scale factor muss bei float4 die jeweils unterschiedliche Repräsentation erhalten bleiben, was die Verwendung von Deduplizierung ausschließt. Spalten vom Typ text die eine Sortierreihenfolge verwenden die nicht-deterministisch ist können ebenfalls nicht dedupliziert werden.
Auch sogenannte Composite, also zusammengesetzte Datentypen, Arrays sowie Range Type verhindern die Nutzung der Funktion, allerdings hat dies keine formalen Gründe, sondern ist aktuell nicht implementiert. B-tree Indexe die mit der INCLUDE Direktive angelegt wurden, können ebenfalls aktuell keine Deduplizierung verwenden.
Bei Fragen rund um den Einsatz von PostgreSQL® 13 stehen wir Ihnen natürlich gerne zur Verfügung. Sprechen Sie uns an! Unser PostgreSQL® Competence Center ist bei Bedarf 24 Stunden am Tag, an 365 Tagen im Jahr für Sie verfügbar.
Das apt.postgresql.org-Repository war ursprünglich mit den beiden Architekturen amd64 und i386 (64- und 32-bit x64) gestartet. Im September 2016 kam dann ppc64el (POWER) hinzu. Über die Zeit gab es immer wieder einzelne Anfragen, ob wir vielleicht auch „arm“ unterstützen würden, womit meistens Raspberry Pi gemeint war. Die sind aber meistens nur 32-bit, und der weit verbreitete „armhf“-Raspbian-Port ist leider nur ARM6, eine ältere Hardware-Version.
Durch HUAWEI Cloud Services wurde der PostgreSQL®-Community jetzt eine „arm64“-Buildmaschine zur Verfügung gestellt, was eine moderne Prozessorarchitektur ist, die auch für PostgreSQL®-Server geeignet ist. Die Maschine wurde dann von uns eingerichtet und das apt.postgresql.org-Repository um diese Architektur erweitert. Bei den unterstützten Distributionen haben wir uns für Debian buster (stable), bullseye (testing) und sid (unstable) sowie Ubuntu bionic (18.04) und focal (20.04) entschieden.
Die Build-Maschine ist sehr performant. Alle Pakete wurden von uns in wenigen Tagen für die neue Architektur gebaut. Spezielle arm-spezifischen Probleme sind dabei nur sehr wenige aufgetreten, was für die Stabilität der Linux-Portierung auf dieser Architektur spricht.
Einem Einsatz von PostgreSQL® auf arm64, auf Debian oder Ubuntu steht damit nichts mehr im Weg.
Parallel haben wir das Repository um den Support für die neue Ubuntu-LTS-Version erweitert: focal (20.04).
Diese Distribution kann damit ab sofort benutzt werden, mit Unterstützung für fünf Jahre bis April 2025.
Über Fragen zum Einsatz von PostgreSQL® auf arm und anderen Architekturen auf Debian, Ubuntu und anderen Betriebssystem freut sich das credativ PostgreSQL® Competence Center natürlich jederzeit. Sprechen Sie uns an!
Dieser Artikel wurde ursprünglich von Christoph Berg geschrieben.
PostgreSQL® ist eine äußerst robuste Datenbank, der auch die meisten unserer Kunden ihre Daten anvertrauen. Kommt es jedoch trotzdem einmal zu Fehlern, so liegen diese meistens am Storage-System, in dem einzelne Bits oder Bytes kippen, oder ganze Blöcke verfälscht werden. Wir zeigen, wie man die Daten aus korrupten Tabellen retten kann.
Der Benutzer ist im Fehlerfall mit Meldungen konfrontiert, die aus dem Storagelayer oder anderen PostgreSQL®-Subsystemen kommen:
postgres=# select * from t;
ERROR: missing chunk number 0 for toast value 192436 in pg_toast_192430
postgres=# select * from a;
ERROR: invalid memory alloc request size 18446744073709551613
Wenn nur einzelne Tupel defekt sind, kann man sich teilweise behelfen, indem man diese einzeln ausliest, z.B. nach id, was in vielen Fällen jedoch auch nicht weiter hilft:
select * from t where id = 1;
Erfolgversprechender ist, die Tupel direkt mit ihrer internen Tupel-ID, in PostgreSQL® ctid genannt, anzusprechen:
select * from t where ctid = '(0,1)';
Um nun alle Tupel auszulesen, nutzen wir eine Schleife in plpgsql:
for page in 0 .. pages-1 loop
for item in 1 .. ??? loop
select * from t where ctid = '('||page||','||item||')' into r;
return next r;
end loop;
end loop;
Wir brauchen noch die Zahl der Seiten in der Tabelle, die bekommen wir von pg_relation_size(), und die Zahl der Tupel auf der Seite, wofür wir die Extension pageinspect bemühen.
select pg_relation_size(relname) / current_setting('block_size')::int into pages;
for page in 0 .. pages-1 loop
for item in select t_ctid from heap_page_items(get_raw_page(relname::text, page)) loop
SELECT * FROM t WHERE ctid=item into r;
if r is not null then
return next r;
end if;
end loop;
end loop;
Jetzt kommt der wichtigste Teil: Der Zugriff auf die beschädigten Tupel oder Seiten verursacht Fehler, die wir mit einem begin..exception..end-Block abfangen müssen. Die Fehlermeldungen geben wir als NOTICE an den Benutzer weiter. Außerdem soll die Funktion nicht nur für eine Tabelle funktionieren, sondern einen Parameter relname erhalten. Die gesamte plpgsql-Funktion sieht dann so aus:
create extension pageinspect;
create or replace function read_table(relname regclass)
returns setof record
as $$
declare
pages int;
page int;
ctid tid;
r record;
sql_state text;
error text;
begin
select pg_relation_size(relname) / current_setting('block_size')::int into pages;
for page in 0 .. pages-1 loop
begin
for ctid in select t_ctid from heap_page_items(get_raw_page(relname::text, page)) loop
begin
execute format('SELECT * FROM %s WHERE ctid=%L', relname, ctid) into r;
if r is not null then
return next r;
end if;
exception -- bad tuple
when others then
get stacked diagnostics sql_state := RETURNED_SQLSTATE;
get stacked diagnostics error := MESSAGE_TEXT;
raise notice 'Skipping ctid %: %: %', ctid, sql_state, error;
end;
end loop;
exception -- bad page
when others then
get stacked diagnostics sql_state := RETURNED_SQLSTATE;
get stacked diagnostics error := MESSAGE_TEXT;
raise notice 'Skipping page %: %: %', page, sql_state, error;
end;
end loop;
end;
$$ language plpgsql;
Da die Funktion „record“ zurück gibt, muss beim Aufruf die Tabellensignatur mitgegeben werden:
postgres =# select * from read_table('t') as t(t text);
NOTICE: Skipping ctid (0,1): XX000: missing chunk number 0 for toast value 192436 in pg_toast_192430
t
───────────────
one
two
three
...
Eine alternative Variante schreibt die gelesenen Daten direkt in eine neue Tabelle:
postgres =# select rescue_table('t');
NOTICE: t: page 0 of 1
NOTICE: Skipping ctid (0,1): XX000: missing chunk number 0 for toast value 192436 in pg_toast_192430
rescue_table
─────────────────────────────────────────────────────────────────────────────────────
rescue_table t into t_rescue: 0 of 1 pages are bad, 1 bad tuples, 100 tuples copied
(1 row)
Die Tabelle t_rescue wurde automatisch angelegt.
create extension pageinspect;
create or replace function rescue_table(relname regclass, savename name default null, "create" boolean default true)
returns text
as $$
declare
pages int;
page int;
ctid tid;
row_count bigint;
good_tuples bigint := 0;
bad_pages bigint := 0;
bad_tuples bigint := 0;
sql_state text;
error text;
begin
if savename is null then
savename := relname || '_rescue';
end if;
if rescue_table.create then
execute format('CREATE TABLE %s (LIKE %s)', savename, relname);
end if;
select pg_relation_size(relname) / current_setting('block_size')::int into pages;
for page in 0 .. pages-1 loop
if page % 10000 = 0 then
raise notice '%: page % of %', relname, page, pages;
end if;
begin
for ctid in select t_ctid from heap_page_items(get_raw_page(relname::text, page)) loop
begin
execute format('INSERT INTO %s SELECT * FROM %s WHERE ctid=%L', savename, relname, ctid);
get diagnostics row_count = ROW_COUNT;
good_tuples := good_tuples + row_count;
exception -- bad tuple
when others then
get stacked diagnostics sql_state := RETURNED_SQLSTATE;
get stacked diagnostics error := MESSAGE_TEXT;
raise notice 'Skipping ctid %: %: %', ctid, sql_state, error;
bad_tuples := bad_tuples + 1;
end;
end loop;
exception -- bad page
when others then
get stacked diagnostics sql_state := RETURNED_SQLSTATE;
get stacked diagnostics error := MESSAGE_TEXT;
raise notice 'Skipping page %: %: %', page, sql_state, error;
bad_pages := bad_pages + 1;
end;
end loop;
error := format('rescue_table %s into %s: %s of %s pages are bad, %s bad tuples, %s tuples copied',
relname, savename, bad_pages, pages, bad_tuples, good_tuples);
raise log '%', error;
return error;
end;
$$ language plpgsql;
Die SQL-Skripte sind auch im git-Repository von pg_dirtyread verfügbar.
Unterstützung
Falls Sie Unterstützung bei der Rettung Ihrer Daten oder dem allgemeinen Einsatz von PostgreSQL® benötigen, steht Ihnen unser PostgreSQL® Competence Center zur Verfügung – Falls gewünscht auch 24 Stunden am Tag, an 365 Tagen im Jahr.
Wir freuen uns auf Ihre Kontaktaufnahme.
Dieser Artikel wurde ursprünglich von Christoph Berg geschrieben.
Der Open Source Summit ist die weltweit größte, allumfassende Open Source Konferenz. Hier werden unter anderem Themen wie aktuelle Infrastruktur-Software, Entwicklungen am Linux-Kernel, aber auch die Arbeit in der Community besprochen. Ein bisher fehlender Bestandteil des Konferenzprogrammes waren Open Source Datenbanken.
Dabei bildet Datenbanksoftware wie PostgreSQL® oder Apache Cassandra eine der wichtigsten Säulen in modernen Open Source Infrastrukturen.
Zusammen mit der Linux Foundation freuen wir uns, ankündigen zu dürfen, dass der diesjährige Open Source Summit North America und Europe jeweils einen eigenen Datenbank-Track haben wird.
Dabei bildet unser Geschäftsführer Dr. Michael Meskes zusammen mit Sunil Kamath (Microsoft) und Divya Bhargov (Pivotal) das Programmkomitee für den neuen Track des Open Source Summit.
In seinem Blog-Beitrag bei der Linux Foundation sagte Dr. Michael Meskes hierzu:
„The open source database track will feature topics specific to databases themselves and their integration to the computing backbone for applications. The track will focus on databases of all kinds, as long as they are open source, and any deployment and integration topics.“
Der vollständige Blogbeitrag kann auf der Seite der Linux Foundation gelesen werden.
Die Linux Foundation und das Programmkomitee freuen sich über alle Einreichungen zum neuen Datenbank-Track. Vorträge können noch bis zum 16. Februar (North America) und 14. Juni (Europe) eingereicht werden.
Der Open Source Summit North America findet in diesem Jahr in Austin, Texas statt. Der Austragungsort des Summit in Europa befindet sich 2020 in Dublin, Irland. Beide Veranstaltungen unterstützt credativ auch in diesem Jahr mit einem Sponsoring.
Dieser Artikel wurde ursprünglich von Philip Haas geschrieben.
