PostgreSQL 16 erweitert die Konfiguration und lernt neue Funktionen
Die Datenbank erlaubt reguläre Ausdrücke in Konfigurationsdateien und bekommt neue Funktionen für den Umgang mit JSON-Inhalten.
- Andreas Scherbaum
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 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.
Beliebige Werte
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 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-INSERT
s, 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.
Breaking Changes
Einige Änderungen führen zu Inkompatibilitäten. Die meisten der in den Release Notes vollständig aufgeführten Breaking Changes sollten die meisten Anwenderinnen und Anwender jedoch nicht betreffen. Die einschneidendsten Änderungen sind
promote_trigger_file
entfällt: Bisher hat die Konfigurationsoption den Pfad zu einer Datei angegeben, die einen Replica zu einem vollwertigen Datenbankserver promoted hat. Stattdessen kann man nunpg_ctl promote
auf der Kommandozeile oderSELECT pg_promote()
in der Datenbank verwenden.NULL
s in Primärschlüsseln: Bisher war es möglich, Primärschlüssel alsNULLS NOT DISTINCT
zu definieren. Das bedeutet, dass jederNULL
-Wert gleich betrachtet wird. Für Primärschlüssel ist das nicht mehr erlaubt, aber weiterhin für Unique Indexes.postmaster
-Link entfällt: Lange war der Datenbankserver sowohl unter dem Programmnamenpostgres
als auchpostmaster
verfügbar, wobei Letzteres ein Symlink auf Ersteres ist. Als Teil der Umstellung auf das Meson-Buildsystem ist der Link nun endgültig entfallen, nachdem dieser für viele Jahre bereits als überholt (deprecated) markiert war.
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:
- Dump & Restore ist üblicherweise der einfachste Weg: Man nutzt das
pg_dump
-Tool von PostgreSQL 16, um die alte Datenbank zu exportieren. Dabei kann man das Ergebnis in eine Datei oder direkt in die neue Datenbank schreiben. Das kann allerdings bei großen Datenbanken sehr lange dauern, und in der Zwischenzeit sind keine Änderungen erlaubt, da diese nicht mit migriert werden. - Das
pg_upgrade
-Tool hilft dabei, eine ältere Datenbank direkt in die neue Datenbank zu migrieren. In der Voreinstellung kopiert es die Datendateien. Das erfordert ausreichend Speicherplatz und kostet Zeit für die Operationen auf der Festplatte. Mit der Option--link
verlinktpg_upgrade
die Dateien stattdessen über Hardlinks. Das geht sehr schnell, erlaubt aber nach dem Start der neuen Version keine Rückkehr zur alten. Backups und ausreichende Tests sind also notwendig. - Logical Replication erlaubt es, Datenbanken zwischen verschiedenen PostgreSQL-Versionen zu kopieren. Man setzt eine Replica mit der neuen Version auf, lässt sie alle Daten aus dem Primary kopieren. Anschließend schaltet man den Primary aus und befördert die Replica zum neuen primären System.
Blick nach vorn und zurück
Mit dem Release von PostgreSQL 16 verliert die 2018 erschienene Version 11 ihren bei der Datenbank üblichen fünfjährigen Support. Derweil hat die Arbeit an Version 17 bereits begonnen.
Weitere Details zu PostgreSQL 16 lassen sich der offiziellen Ankündigung entnehmen. Auf der Download-Seite 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 und schreibt einen Blog.
(rme)