zurück zum Artikel

PostgreSQL 16 erweitert die Konfiguration und lernt neue Funktionen

Andreas Scherbaum
Elefant

(Bild: David Davies CC BY-SA 2.0)

Die Datenbank erlaubt reguläre Ausdrücke in Konfigurationsdateien und bekommt neue Funktionen für den Umgang mit JSON-Inhalten.

PostgreSQL 16 ist nach rund 15 Monaten Entwicklungszeit erschienen. Diese Version bringt Neuerungen bei der Konfiguration, der Authentifizierung und dem Zusammenspiel mit JSON. Vor dem Upgrade muss der ein oder andere jedoch auch seine Hausaufgaben machen.

Reguläre Ausdrücke in der Konfiguration

In der für die Client-Authentifizierung zuständigen Konfigurationsdatei pg_hba.conf (und in pg_ident.conf) kann man den Namen der Datenbank und der Rolle jetzt als Regex angeben. Das ermöglicht komplexe Konfigurationen, speziell in Umgebungen, in denen viele Anwendungen oder Benutzer beispielsweise in LDAP automatisiert verwaltet werden.

# TYPE  DATABASE                USER                    METHOD
  host  "/^anwendung\d{1,6}$"   "/^benutzer\d{1,6}$"    scram-sha-256

Des Weiteren können Admins über diese Option Usern mehr als eine Datenbank zuweisen, ohne umständlich die Authentifizierung zu erweitern. Im folgenden Beispiel darf die Rolle andreas sich in jede Datenbank einloggen, deren Name mit andreas beginnt und von einer bis sechs Zahlen gefolgt ist. Das eignet sich unter anderen für das Zusammenspiel mit Entwicklungsumgebungen:

# TYPE  DATABASE                USER                    METHOD
host    "/^andreas\d{1,6}$"     andreas                 scram-sha-256

Vor PostgreSQL 16 musste man die Namen aller Datenbanken entweder einzeln oder kommasepariert auflisten oder sie einzeln in eine separate Datei schreiben.

Eingebundene Dateien in der Konfiguration

Neuerdings lassen sich zudem in pg_hba.conf und pg_ident.conf Dateien einbinden. Bisher war es lediglich möglich, für Rollen und Datenbanken eine Liste mit den Einträgen anzugeben, aber nicht möglich, Teile der Konfiguration in separate Dateien auszulagern. Daher mussten im Zusammenspiel mit Automatisierungswerkzeugen die Einträge in pg_hba.conf jeweils in der richtigen Reihenfolge enthalten sein, was umständliche Regex-Operationen nach sich zog oder komplexe Templates erforderte.

Mit include, include_if_exists und include_dir lässt sich die Konfiguration für pg_hba.conf nun sauber in einzelne Dateien verteilen und getrennt verwalten.

include /pfad/zur/config/pg_hba_dev.conf​

Wenn die in der include-Option angegebene Datei nicht existiert, spuckt PostgreSQL eine Fehlermeldung aus. Das stellt sicher, dass alle Dateien existieren. Der Befehl include_if_exists liest Dateien ein, sofern sie vorhanden sind. Andernfalls gibt es einen Logeintrag, aber keine Fehlermeldung. Schließlich liest include_dir alle Dateien in einem Verzeichnis ein, die auf *.conf enden und nicht mit einem Punkt beginnen.

Für alle drei Optionen gilt: Die Inhalte der Datei(en) werden an der Stelle der include*-Option eingesetzt. Da die Reihenfolge in der pg_hba.conf wichtig für die Authentifizierung ist, müssen Admins also weiterhin auf die passende Stelle achten. Ebenfalls ist die Benennung der Konfigurationsdateien in einem Verzeichnis wichtig, da PostgreSQL sie alphabetisch sortiert einliest.

Methoden zur Authentifizierung

Jeder Client, der libpq verwendet, kann neuerdings angeben, welche Authentifizierungsmethoden zugelassen oder verboten sind. Bisher konnte nur der Server die Methoden vorgeben. Wenn der Client eine Liste an Methoden schickt und der Server keine davon akzeptiert, findet kein Authentifizierungsversuch statt. Damit lässt sich verhindern, dass der Server eine unsichere Methode verwendet. Folgende Angabe schließt die Authentifizierung mittels Klartextpasswörtern und MD5-gehashten Passwörtern explizit aus.

require_auth=!password,!md5

Folgende Zeile lässt die Authentifizierung ausschließlich über Salted Challenge Response Authentication Mechanism (SCRAM) zu. Sollte der Server diese Methode nicht unterstützen, kommt keine Verbindung zustande.

require_auth=scram-sha-256

Load Balancing in libpq

Eine weitere Neuerung in libpq ist das eingebaute Load Balancing über unterschiedlich konfigurierte Datenbankverbindungen. Seit PostgreSQL 10 [1] kann man mehr als eine Verbindung konfigurieren, die libpq der Reihe nach durchprobiert hat, bis eine Verbindung funktioniert. Das hat in Folge die Last der Anfragen auf die erste(n) Einträge in dieser Liste verteilt.

Mit load_balance_hosts=random kann libpq die Einträge in der Liste zufällig sortieren und einen davon auswählen. Das verteilt die Last gleichmäßig auf alle Server. Die Option load_balance_hosts kennt neben random die Angabe disable, die die zufällige Verteilung deaktiviert und immer die erste verfügbare Verbindung verwendet.

Die Option ist in PostgreSQL nur sinnvoll, wenn man lesende Anfragen auf Replicas verteilt. Schreibende Anfragen müssen weiterhin zum Primary gehen.

Der passende Systemuser

Das im SQL-Standard definierte SYSTEM_USER ist jetzt auch in PostgreSQL verfügbar. Es zeigt an, welcher System-User mit welcher Authentifizierungsmethode für die Verbindung eingesetzt wurde:

postgres=# SELECT CURRENT_USER, SYSTEM_USER;
 current_user | system_user
--------------+-------------
 andreas      | peer:ads

Die aktuell eingeloggte Rolle ist andreas, und die Verbindung kam vom Systemuser ads. Das Log-in erfolgte mit peer-Authentifizierung. Diese Information ist für Audit-Zwecke hilfreich, um nachzuvollziehen, wer sich in die Datenbank eingeloggt hat.

Ein weiteres aus dem SQL-Standard in PostgreSQL 16 übernommene Feature ist ANY_VALUE. Es dient in Grouping-Anfragen dazu, einen beliebigen Wert aus der angegebenen Spalte zu extrahieren. Ohne dieses Aggregat muss man spezifizieren, welchen Wert man gruppieren möchte. Das sollte Migrationen von anderen Datenbanken wie MySQL vereinfachen, die SQL-Queries mit nicht aggregierten Spalten aus Bequemlichkeit und Geschwindigkeitsgründen erlaubt haben. In PostgreSQL muss man weiterhin die Spalte aggregieren, aber ANY_VALUE erlaubt die Auswahl eines beliebigen Wertes.

SELECT nutzerid, count(*), ANY_VALUE(nutzername)
  FROM nutzer GROUP BY nutzerid;

Logical Replication mit verbesserter Sicherheit

PostgreSQL führt die neue Rolle pg_create_subscription ein, die Subscriptions für Logical Replication erzeugen kann. Damit ist es nicht mehr erforderlich, einen Superuser Account für die Replikation zu verwenden.

GRANT pg_create_subscription TO replica_user;​

Die DML-Befehle (Data Manipulation Language) INSERT, UPDATE und DELETE sowie alle SELECT-Operationen in einer logischen Replikation laufen jetzt unter den Rechten des Eigentümers der Tabelle. Das vermeidet Schwachstellen, wenn die Rolle für die Replikation zu viele Rechte besitzt. Wer das vorherige Verhalten beibehalten möchte, muss die Option run_as_owner=true setzen.

Verspätete JSON-Neuerungen

Kurz vor dem Release von PostgreSQL 15 [2] hat die Community eine ganze Reihe neuer Funktionen für JSON wieder aus dem Release entfernt, weil es kurzfristig Qualitätsprobleme mit dem Code gab. Version 16 bringt die Neuerungen nun mit.

JSON-Konstruktoren sind im SQL Standard definiert und jetzt verfügbar: JSON_OBJECT, JSON_OBJECTAGG, JSON_ARRAY und JSON_ARRAYAGG.

Konstruktoren wie to_json(), row_to_json(), json_build_array() oder json_build_object() existieren seit geraumer Zeit. Da sie jedoch PostgreSQL-spezifisch sind, lassen sie sich nicht einfach auf andere Datenbanken portieren. Die neuen SQL-Standard-Konstruktoren ermöglichen portablen Code.

SELECT JSON_OBJECT('key': 'value');
{"key": "value"}

Ebenfalls neu sind die JSON-Prädikate IS JSON, IS JSON ARRAY, IS JSON OBJECT und IS JSON SCALAR. Mit ihnen lässt sich unter anderem prüfen, ob ein Element ein JSON-Objekt ist:

SELECT '{}'::JSONB IS JSON OBJECT;

Begrenzter Puffer

Die Option BUFFER_USAGE_LIMIT für VACUUM sorgt dafür, dass ein VACUUM nicht zu viele Speicherseiten im Shared Buffer verwendet. Die Konfigurationsoption vacuum_buffer_usage_limit setzt die Voreinstellung dafür und ist standardmäßig auf 256 kB gesetzt.

VACUUM (BUFFER_USAGE_LIMIT '50MB');​

Ein manuelles VACUUM in PostgreSQL ist nicht limitiert und versucht die angegebenen Tabellen so schnell wie möglich zu bearbeiten. Dafür wird die Datenbank alle notwendigen Speicherseiten in den Shared Buffers Bereich laden und dafür gegebenenfalls vorhandene Pages mit Produktionsdaten entfernen. Das verringert die Performance der Datenbank, da sie die Produktionsdaten später wieder laden muss.

Statistiken für Scans und Storage-Typen

Die pg_stat_*_tables-Views zeigen neuerdings nicht nur an, wie oft PostgreSQL eine Tabelle sequenziell oder per Index gescannt hat, sondern speichern zusätzlich den Zeitpunkt des letzten Scans. Wenn sich beispielsweise Probleme mit einem EXPLAIN-Plan für eine langsam laufende Query ergeben, ist der Zeitpunkt des letzten kompletten Scans der Tabelle ein guter Anhaltspunkt für weitere Recherchen.

Bisher war es notwendig, erst eine Tabelle zu erstellen und dann den Storage-Typ einer Spalte zu ändern. In Version 16 kann man den Storage-Typ direkt beim Erstellen angeben. Viele werden sich fragen, warum es das nicht schon immer gab.

CREATE TABLE benutzer (
  beschreibung TEXT STORAGE EXTERNAL
);

Flexiblere Zahlendarstellung

Nicht-dezimale Zahlen dürfen jetzt in verschiedenen Formaten – binär, oktal oder hexadezimal – angegeben werden:

SELECT 0b100, 0o10, 0x10;
 4 | 8 | 16

Außerdem ist zur besseren Lesbarkeit neuerdings die Unterteilung mit Unterstrichen erlaubt:

SELECT 2_147_483_647;

Schlankere Backups

Das Tool pg_dump Tool erlaubt nun die Angabe eines Kompressionsalgorithmus und der Kompressionsrate. Welche Algorithmen sich nutzen lassen, hängt davon ab, mit welchen Libraries PostgreSQL kompiliert wurde.

Folgender Befehl verwendet lz4 statt gzip mit der höchsten verfügbaren Kompressionsrate 9:

pg_dump -Z lz4:9

Blick auf die Performance

Wie üblich gibt es ein paar Änderungen, die auf eine bessere Performance abzielen. PostgreSQL 16 bringt zwar keine bahnbrechenden Neuerungen, aber einige sinnvolle Updates.

Page Level Freezing

Ein Pain Point in PostgreSQL ist VACUUM, das nach Änderungen in einer Tabelle die nicht mehr benötigten Zeilen aufräumt. Dank Page Level Freezing hat die Datenbank bessere Informationen über die Zahl der geänderten Speicherseiten (Pages) und das Alter der Änderungen. Damit kann das System entscheiden, ob Autovacuum eine Tabelle agressiv (eager) bearbeiten soll oder sichtbare Pages überspringt (lazy). Das soll in Datenbanken mit vielen Änderungen dafür sorgen, dass Autovacuum nicht irgendwann vor einem "Transaction ID Wraparound" steht und genau dann anfängt sämtliche Tabellen nacheinander zu scannen und so die Performance im Produktionsbetrieb zu reduzieren.

Schlauer sortiert

Bisher haben DISTINCT und ORDER BY intern immer das Ergebnis sortiert. Wenn PostgreSQL 16 einen vorsortierten Index findet, muss es das Zwischenergebnis nicht erneut sortieren.

Operationen, die viele Einträge in dieselbe partitionierte Tabelle kopieren, beispielsweise mit COPY oder Multi-row-INSERTs, profitieren von einem gecachten Tabellennamen. Da die Datenbank den Namen bei Bulk-Operationen zwischenspeichert, muss sie nicht jedes Mal aufwendig den Partitionsnamens auflösen. Die Optimierung funktioniert für Range- und List-, aber nicht für Hash-Partitionen.

Angepasste Fensterfunktionen

Für einige Window Functions ist es egal, ob ROWS oder RANGE verwendet wird. ROWS ist in der Regel schneller, aber RANGE der Default. Das betrifft unter anderem das häufig verwendete

row_number() OVER (ORDER BY spalte)

Intern können Fensterfunktionen das jetzt selbstständig auf RANGE umstellen. Das betrifft folgende Funktionen: row_number(), rank(), dense_rank(), percent_rank(), ntile(), cume_dist().

Die Änderung erfolgt transparent, sodass User von der verbesserten Performance profitieren.

Einige Änderungen führen zu Inkompatibilitäten. Die meisten der in den Release Notes [3] vollständig aufgeführten Breaking Changes sollten die meisten Anwenderinnen und Anwender jedoch nicht betreffen. Die einschneidendsten Änderungen sind

Upgrade auf Version 16

Vor einem Upgrade der Produktionsdatenbank sollte man sicherheitshalber das erste oder zweite Dot-Release (16.1 oder 16.2) abwarten. Bis dahin zeigt sich, ob es gravierende Probleme gibt. Üblicherweise spricht jedoch nichts dagegen, die neue Version in der Entwicklung zu testen.

Für das eigentliche Upgrade stehen mehrere Wege zur Verfügung:

Blick nach vorn und zurück

Mit dem Release von PostgreSQL 16 verliert die 2018 erschienene Version 11 [4] ihren bei der Datenbank üblichen fünfjährigen Support [5]. Derweil hat die Arbeit an Version 17 bereits begonnen.

Weitere Details zu PostgreSQL 16 lassen sich der offiziellen Ankündigung entnehmen [6]. Auf der Download-Seite [7] finden sich ein Link zum Git-Repository mit dem Source-Code sowie Binaries für Linux, macOS, Windows. BSD und Solaris.

Andreas Scherbaum
arbeitet seit 1997 mit PostgreSQL. Er ist in diversen Projekten in der Community involviert, einer der Gründer und Direktoren von PostgreSQL Europe, hilft bei der Veranstaltung von Konferenzen und Meetups und hat irgendwann ein Buch über PostgreSQL geschrieben. Außerdem veröffentlicht er wöchentliche Interviews mit Mitgliedern der Community [8] und schreibt einen Blog [9].

(rme [10])

URL dieses Artikels:
https://www.heise.de/-9305955

Links in diesem Artikel:
[1] https://www.heise.de/news/PostgreSQL-10-ist-fertig-Teile-und-herrsche-3851147.html
[2] https://www.heise.de/news/Datenbank-PostgreSQL-15-fuehrt-endlich-MERGE-ein-7308296.html
[3] https://www.postgresql.org/docs/16/release-16.html
[4] https://www.heise.de/news/Datenbank-PostgreSQL-11-erweitert-parallele-Queries-und-Stored-Procedures-4196049.html
[5] https://www.postgresql.org/support/versioning/
[6] https://www.postgresql.org/about/news/postgresql-16-released-2715/
[7] https://www.postgresql.org/download/
[8] https://postgresql.life/
[9] https://andreas.scherbaum.la/
[10] mailto:rme@ix.de

Copyright © 2023 Heise Medien