Überblick zu PostgreSQL 14.4
Weitergedacht
Veröffentlicht in Ausgabe 09/2022 - SCHWERPUNKT
Viele Entwickler konzentrieren sich auf viele unterschiedliche Bereiche der Datenbank, was zeigt, wie stark das Engagement von Unternehmen und freiwilligen Mitgliedern der Community ist. PostgreSQL ist mittlerweile aus vielen unternehmenskritischen Bereichen nicht mehr wegzudenken und ist bei vielen Clouddiensten häufig die relationale Datenbank der ersten Wahl. Hier überzeugt sicherlich die flexible, freie Lizenz die nahezu keine Einschränkungen für die Nutzer kennt. Auch die äußerst breite Unterstützung von professionellen Dienstleistern mit Tools und Support hilft Unternehmen enorm, um PostgreSQL erfolgreich in die eigene Infrastruktur zu integrieren.
Unterstützung von OUT-Parametern
Prozeduren, die auch in prozeduralem Code eingebettete Transaktionskontrolle unterstützen, wurden bereits mit der Version 11 eingeführt. Mit PostgreSQL 14 ist es möglich, Ergebnisse aus der Prozedur zurückzugeben. Bisher war es zwar kein Problem, Daten innerhalb der Prozedur flexibel zu verarbeiten, aber nicht möglich, Ergebnisse beispielsweise außerhalb der Prozedur wiederzuverwenden. Hierzu können Sie nun sogenannte OUT- beziehungsweise INOUT-Parameter verwenden, die Sie beim Anlegen in der Argumentliste deklarieren:
CREATE OR REPLACE PROCEDURE add(op1 IN bigint, op2 IN bigint, result_add OUT bigint)
LANGUAGE SQL
AS
$$
SELECT op1 + op2;
$$;
Reine OUT-Parameter müssen Sie – obwohl sie keine Werte an die Prozedur übergeben – dennoch in der Aufrufskonvention des CALL-Kommandos berücksichtigen. Aktuell ist angeraten, hier einfach
NULL als Argument an CALL zu übergeben, da noch Änderungen in zukünftigen PostgreSQL Versionen zu erwarten sind. Dies funktioniert auch mit mehreren Argumenten gleichzeitig, beispielsweise wenn Sie mehrere Ergebnisse berechnen und zurückgeben möchten:Viele Entwickler konzentrieren sich auf viele unterschiedliche Bereiche der Datenbank, was zeigt, wie stark das Engagement von Unternehmen und freiwilligen Mitgliedern der Community ist. PostgreSQL ist mittlerweile aus vielen unternehmenskritischen Bereichen nicht mehr wegzudenken und ist bei vielen Clouddiensten häufig die relationale Datenbank der ersten Wahl. Hier überzeugt sicherlich die flexible, freie Lizenz die nahezu keine Einschränkungen für die Nutzer kennt. Auch die äußerst breite Unterstützung von professionellen Dienstleistern mit Tools und Support hilft Unternehmen enorm, um PostgreSQL erfolgreich in die eigene Infrastruktur zu integrieren.
Unterstützung von OUT-Parametern
Prozeduren, die auch in prozeduralem Code eingebettete Transaktionskontrolle unterstützen, wurden bereits mit der Version 11 eingeführt. Mit PostgreSQL 14 ist es möglich, Ergebnisse aus der Prozedur zurückzugeben. Bisher war es zwar kein Problem, Daten innerhalb der Prozedur flexibel zu verarbeiten, aber nicht möglich, Ergebnisse beispielsweise außerhalb der Prozedur wiederzuverwenden. Hierzu können Sie nun sogenannte OUT- beziehungsweise INOUT-Parameter verwenden, die Sie beim Anlegen in der Argumentliste deklarieren:
CREATE OR REPLACE PROCEDURE add(op1 IN bigint, op2 IN bigint, result_add OUT bigint)
LANGUAGE SQL
AS
$$
SELECT op1 + op2;
$$;
Reine OUT-Parameter müssen Sie – obwohl sie keine Werte an die Prozedur übergeben – dennoch in der Aufrufskonvention des CALL-Kommandos berücksichtigen. Aktuell ist angeraten, hier einfach
NULL als Argument an CALL zu übergeben, da noch Änderungen in zukünftigen PostgreSQL Versionen zu erwarten sind. Dies funktioniert auch mit mehreren Argumenten gleichzeitig, beispielsweise wenn Sie mehrere Ergebnisse berechnen und zurückgeben möchten:CREATE OR REPLACE PROCEDURE add_mul(op1 IN bigint, op2 IN bigint, result_add OUT bigint, result_mul OUT bigint)
LANGUAGE SQL
AS
$$
SELECT op1 + op2, op1 * op2;
$$;
Inline-SQL-Deklarationen
Bei Funktionen und Prozeduren mussten Datenbankentwickler in PostgreSQL bisher ein sogenanntes Dollar-Quoting verwenden, um einen Funktions- oder Prozedurblock bei einer entsprechenden Deklaration einzuleiten. Beispielsweise sehen wir in den obigen Listings das $$-Literal, das den entsprechenden Block einleitet und abschließt. Das Dollar-Quoting kann dabei nicht nur allein aus der Zeichenfolge "$$" bestehen, sondern flankiert von "$" auch jede beliebige Zeichenkette enthalten, beispielsweise "$meine funktion$".
Wichtig ist in diesem Fall, dass ein entsprechender Block mit identischem Bezeichner auch wieder abgeschlossen wird. Entsprechende Funktions- und Prozedurblöcke werden dann zur Ausführungszeit geparsed und gestartet. In PostgreSQL 14 ist es nun möglich, entsprechende SQL-Funktionen und Prozeduren bereits bei Definition zu parsen und in entsprechender Form im Datenbankkatalog zu speichern. Folgendes Beispiel zeigt die neue Syntax im Vergleich zur bereits bekannten Prozedur aus dem ersten Listing:
CREATE OR REPLACE PROCEDURE add(op1 IN bigint, op2 IN bigint, result OUT bigint)
LANGUAGE SQL
BEGIN ATOMIC
SELECT op1 + op2;
END;
BEGIN ATOMIC leitet nun den SQL-Block ein und mittels END schließen Sie ihn ab. Im Moment funktioniert dies nur mit reinen SQL-Routinen – PL/PgSQL oder andere Sprachen lassen sich so nicht anlegen.Wichtig zu beachten ist außerdem, dass die Datenbank SQL-Blöcke sofort beim Anlegen parst und entsprechend im Katalog hinterlegt. Dies erlaubt zusätzlich, Abhängigkeiten der Funktionen beziehungsweise Prozeduren zu speichern. Legen Sie die Funktion oder Prozedur mittels Dollar-Quoting an, sind Abhängigkeiten in dieser Form nicht möglich.
Es zeigen sich unterschiedliche Resultate, wenn Sie eine Tabelle löschen wollen, die innerhalb einer entsprechend angelegten Prozedur verwendet wird. Leiten Sie die Prozedur mit Dollar-Quoting ein, lässt sich auch nach dem Anlegen dieser Prozedur die Tabelle erfolgreich löschen. Eine mittels
BEGIN ATOMIC definierte Prozedur speichert jedoch die Abhängigkeit auf Objekte – das Löschen der Tabelle ist nicht möglich.Listing 1: Einfaches JSONB-Subscripting
SELECT ('{
"title": "2001 - A Space Odysee",
"director": "Stanley Kubrick",
"published": 1968
}'::jsonb)['director'];
jsonb
───────────────────
"Stanley Kubrick"
(1 row)
JSONB-Subscripting
JSON-Dokumente, die Sie als JSONB in PostgreSQL 14 abspeichern, können Sie nun mit sogenannten Subscripting-Ausdrücken abfragen. Unter Subscripting versteht man in PostgreSQL den Array-
ähnlichen Zugriff auf Elemente eines komplexen Datentyps, den Sie mittels der Klammern "[]" syntaktisch einleiten. Listing-Kasten 1 zeigt ein simples Beispiel, das per Subscripting-Ausdruck den Regisseur (Schlüssel "director") des Films extrahiert.
Das funktioniert wie im Listing-Kasten 2 ersichtlich auch mit JSON-Dokumenten, auf die Sie in mehreren geschachtelten Ebenen zugreifen wollen. Auch Zuweisungen zum Ändern von Schlüsselwerten (zum Beispiel bei einem
UPDATE einer entsprechenden Spalte) können nun über diese Subscripting-Ausdrücke erfolgen. Bild 2 zeigt eine Änderung, die dem JSON-Dokument einen neuen Eintrag unter "main actors" hinzufügt.
Listing 2: Geschachteltes JSONB-Subscripting
SELECT ('{
"title": "2001 - A Space Odysee",
"director": "Stanley Kubrick",
"published": 1968
"main actors":
{
"Dr. David Bowman": "Keir Dullea",
"Dr. Frank Poole": "Gary Lockwood",
"Dr. Heywood R. Floyd": "William
}
}'::jsonb) ['main actors']['Dr. David Bowman'];
jsonb
───────────────────
"Keir Dullea"
(1 row)
TRUNCATE, SEARCH, CYCLE und VACUUM
Der Foreign Data Wrapper (FDW) von PostgreSQL unterstützt nun
TRUNCATE beim Zugriff auf Foreign Tables. Dies ermöglicht das schnellere Löschen von Datenbeständen auf entfernten PostgreSQL-Servern. Common Table Expressions (CTE), auch bekannt als WITH-Kommando, unterstützen nun SEARCH und CYCLE. SEARCH vereinfacht das Durchsuchen von Suchbäumen in rekursiven Abfragen erheblich und erlaubt das einfache Definieren von Tiefen- oder Breitensuche innerhalb eines Suchbaums mit einer rekursiven Abfrage. CYCLE hilft dem Anwendungsentwickler, eine zyklische Abhängigkeit beim Durchlaufen eines Suchbaumes in einer rekursiven Abfrage zu erkennen und damit gegebenenfalls ein endloses Durchlaufen der rekursiven Abfrage zu verhindern.Der PostgreSQL-eigene Garbage Collector, verantwortlich unter anderem für das Aufräumen und Freigeben von gelöschten oder aktualisierten Zeilen, arbeitet durch das Anpassen des Konfigurationsparameter "vacuum_cost_page_ miss" in PostgreSQL 14 nun mit deutlich höherem Lesedurchsatz.
Der neue Parameter
PROCESS_TOAST für das VACUUM-Kommando erlaubt nun das Überspringen von TOAST-Tabellen. Ferner kann VACUUM nun das Bearbeiten von Indexen automatisch überspringen, falls der jeweilige Index aktuell nur wenige gelöschte oder aktualisierte Zeilen enthält. Da die interne Transaktions-ID (auch XID genannt) nur eine Wertigkeit von 32 Bit hat, können Datenbanken in bestimmten Situationen gefährlich nahe an einen Überlauf der XID geraten. Da jede Zeile in den Tabellen von PostgreSQL die XID enthält, ist VACUUM seit jeher auch dafür verantwortlich, diesem Überlauf durch rechtzeitiges "Einfrieren" entsprechender Zeilen entgegenzuwirken.Geraten Sie in eine Situation, in dem das unmittelbare Überlaufen der XIDs droht, schaltet
VACUUM in PostgreSQL 14 nun deutlich aggressiver in einen sogenannten Failsafe-Modus. So geht das automatische VACUUM direkt in den performanteren Arbeitsmodus ohne sogenanntes Cost-based Delay über – was Autovacuum ansonsten beschränkt, um nicht zu viele I/O-Ressourcen zu nutzen. Indexe werden automatisch übersprungen, so wie alle anderen Aufgaben, die nicht unbedingt notwendig sind. Das spart Zeit und erlaubt Autovacuum so schnell wie möglich alle Zeilen mit kritischen XID zu behandeln.Verbesserungen im Bereich Geschwindigkeit
PostgreSQL 14 steht im Zeichen signifikanter Geschwindigkeitsverbesserungen. So kann der Optimizer nun parallelisierte sequentielle Scans auf Tabellen deutlich schneller verarbeiten. Physische Datenbankblöcke werden nicht mehr pro Block gelesen, sondern in Gruppen dem jeweiligen Worker zugewiesen. Dadurch kommen sich die Worker für einen parallelen, sequentiellen Scan auf einer Tabelle nicht mehr in die Quere und das verbessert den Durchsatz besonders auf SSDs deutlich.
PostgreSQL 14 verfügt nun des Weiteren über die Fähigkeit, Resultate während der Verarbeitung einer Abfrage zwischenzuspeichern. Im Moment funktioniert dies für parametrisierte Nested-Loop-Joins, wo anzunehmen ist, dass die rechte Seite der Verbundoperation auf jeden Fall überwiegend Verbundtreffer mit der linken Seite enthält, also negative Treffer eher selten sind. Dies erspart für mehrfach auftretende Verbundschlüsselwerte das mehrfache Prüfen der rechten Seite.
Die Vergleichsoperation passiert über die passende Hash-Funktion, was das Feld "Cache Mode: logical" reflektiert. Lässt sich ein Cache-Lookup nicht über eine Hash-Funktion abbilden, erscheint hier "Cache Mode: binary", die Schlüsselwerte werden dann bitweise verglichen. Der "Index Only Scan" auf der Tabelle "a" läuft nur fünfmal durch und der vorgeschaltete Cache ermittelt die doppelten Treffer mittels "Memoize", sodass ein Scan für den entsprechenden Schlüsselpartner entfällt – erkennbar an den "loops" der jeweiligen Planknoten.
Dies beschleunigt entsprechende Abfragen erheblich, da redundantes Prüfen eines Schlüsselpartners über den Memoize-Cache erfolgen kann. Allerdings kann es hier noch zu Ungenauigkeiten kommen. Möglichst aktuelle und gute Statistiken per
ANALYZE oder Autoanalyze beziehungsweise Autovacuum sind daher extrem wichtig. Der Cache lässt sich über den Parameter "enable_memoize" auch abschalten.Ein weitere interessante Funktion des Executors ist die Fähigkeit, Abfragen mit Scans auf mehrere Foreign Tables zu parallelisieren. Bisher wurden entsprechende Abfragen strikt sequentiell abgewickelt, bei mehreren involvierten Foreign Tables eine nach der anderen. In PostgreSQL 14 erfolgen entsprechende Scans nun nebenläufig. Ob die Funktionalität unterstützt wird, hängt allerdings vom verwendeten Foreign Data Wrapper ab. Der in PostgreSQL 14 enthaltene "postgres_fdw" unterstützt dieses Feature bereits. Hierzu müssen Sie entweder die SERVER-Definition oder das Statement zum Erzeugen der Foreign Table mit der Option "async_ capable" ausstatten. Listing-Kasten 3 zeigt beispielartig die Verwendung der Option. Zu beachten ist, dass das Setzen von "async_capable" auf der Foreign Table die jeweilige Einstellung der Serverdefinition überschreibt. Die Standardeinstellung ist in jedem Fall ausgeschaltet.
Listing 3: Nutzung der Option "async_capable"
CREATE SERVER another_pgserver
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS ( host 'pg.at.another.vm',dbname 'bernd', port '5432',async_capable 'false' );
CREATE FOREIGN TABLE remote_tab(val bigint not null)
SERVER another_pgserver
OPTIONS (table_name 'a', async_capable 'true');
Logical Replication kann nun Transaktionen während ihrer Ausführung replizieren. Bisher wurden logische Änderungen erst dann repliziert, wenn die entsprechende Transaktion erfolgreich abgeschlossen war. Bei Transaktionen mit sehr vielen Änderungen führte dies oft zu einem hohen Rückstau im Transaktionslog, weshalb eine sorgfältige Überwachung auf dem Quellserver unabdingbar war, um etwaige Probleme etwa durch knappen Speicherplatz zu verhindern. Die neue Option "streaming=true" für
CREATE SUBSCRIPTION aktiviert die neue Funktionalität.Ebenfalls neu für Subscriptions ist die Möglichkeit, den Datentransfer über die Option "binary=true" im Binärformat abzuwickeln. Dies ist in der Regel effizienter und birgt Geschwindigkeitsvorteile gegenüber der herkömmlichen textbasierten Übertragung. Allerdings sollten Sie dies sorgfältig testen, da bei Replikation zwischen unterschiedlichen PostgreSQL-Hauptversionen unter Umständen Kompatibilitätsprobleme auftreten können.
Partitionen lassen sich nun mittels
ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY, ohne exklusives Sperren und nebenläufige Transaktionen zu stören, aus der Partitionshierarchie herauslösen. Dabei wird die Partition selbst in zwei Phasen aus der Hierarchie entfernt und kann daher nicht in einer expliziten Transaktion zur Anwendung kommen. Schlägt die Operation fehl, weil beispielsweise der Administrator das Kommando abbricht, so müssen Sie diese mit ALTER TABLE ... DETACH PARTITION FINALIZE zurücksetzen. UPDATE- und DELETE-Operationen sind auf partitionierten Tabellen nun schneller und profitieren von "Partition Pruning", also dem Entfernen nicht relevanter Partitionen für das Durchführen der jeweiligen Operation.Inkompatible Änderungen
Auch in PostgreSQL 14 gab es einige zu vorhergehenden Hauptversionen inkompatible Änderungen. Diese wirken sich besonders auf Upgrades mittels
pg_upgrade und den Anwendungsbetrieb aus. Für eine vollständige Auflistung inkompatibler Änderungen sei an dieser Stelle auf die Release Notes von PostgreSQL 14 verwiesen [1].Mit dem Upgrade auf PostgreSQL 14 müssen Sie benutzerdefinierte Funktionen, Prozeduren oder Operatoren, die die Funktionen "array_append", "array_prepend", "array_cat", "array_position", "array_positions", "array_remove", "array_replace" sowie "width_bucket" verwenden, vor dem Upgrade entfernen und anschließend nach erfolgreichem Upgrade wieder anlegen. Dies muss unbedingt vor dem Ausführen von
pg_upgrade erfolgen, da selbst ein Testlauf des Tools (Parameter "-c") dies nicht detektieren kann und ein realer Lauf dann mit einem Fehler aussteigt. Während des Verfassens dieses Artikels diskutierte die Community bereits einen Patch, der eine entsprechende Funktionalität nachrüstet. An dieser Stelle sei ein Blick in etwaige in der Zwischenzeit erschienene aktualisierte Versionen von PostgreSQL 14 empfohlen.Die Funktionen
to_tsquery und websearch_to_tsquery produzierten in älteren Versionen manchmal fehlerhafte Resultate bei bestimmten Eingabewerten, da fälschlicherweise bestimmte Tokens wie "_" nicht korrekt berücksichtigt wurden. Bild 3 zeigt die Auswirkung im Vergleich zu PostgreSQL 13. Dies müssen Sie nach Upgrades einplanen.
Wer mit PostgreSQL immer noch Warm-Standby-Server mittels der alten Technik rund um
pg_standby betreibt, sollte vor dem Upgrade zu PostgreSQL 14 sehr gut aufpassen, denn pg_standby befindet sich nicht mehr im Funktionsumfang von PostgreSQL.Ausblick auf PostgreSQL 15
Mit Erscheinen dieses Artikels steht bereits PostgreSQL 15 vor der Tür. Einige interessante Neuerungen haben gute Chancen, es in das finale Release zu schaffen. Hier eine kleine Liste der zu erwartenden Features – vieles mehr findet sich in den Release Notes zu PostgreSQL 15 [2].- Unterstützung für die SQL Operation MERGE. Dies ermöglicht ähnlich zu INSERT ... ON CONFLICT ein Zusammenführen bestehender beziehungsweise neuer Datensätze, allerdings in einer deutlich flexibleren und mächtigeren Form.- Die Version der Sortiervorgabe wird nun pro Datenbank gespeichert. Dies gibt Hinweise auf drohende Inkonsistenzen bei Upgrades von Betriebssystemen mit inkompatiblen Locale-Definitionen in Indexen. Vor allem Indexe auf Spalten mit Texttypen sind hiervon betroffen. Ändern sich die Sortiervorgaben des Betriebssystems, enthält der Index korrupte Daten, weshalb ein REINDEX für diese Indexe notwendig ist.- Das standardmäßig vorhandene PUBLIC-Schema hat keine PUBLIC-Berechtigungen mehr. Besitzer des Schemas ist nun die neue Standardrolle "pg_database_owner".- Serverseitige Basissicherungen über das Streaming-Protokoll lassen sich nun per LZ4 oder Zstandard komprimieren.
Fazit
PostgreSQL 14 bietet wie gewohnt viel Neues. Geschwindigkeit und Funktionalität haben wichtige und nützliche Verbesserungen und Erweiterungen erfahren, die Nutzer sicher zu schätzen wissen. Insgesamt ist die Liste der Neuerungen deutlich länger, als dieser Artikel abbilden kann. Interessierten Datenbank-Admins sei daher noch einmal der Blick in die Release Notes von PostgreSQL 14 ans Herz gelegt.
(ln)
Link-Codes
[1] Release Notes zu PostgreSQL 14: https://www.postgresql.org/docs/14/release-14.html/
[2] Release Notes zu PostgreSQL 15: https://www.postgresql.org/docs/15/release-15.html/