Am 04.09.2025 fand im Apothekertrakt des Schlosses Schönbrunn in Wien nach den vorherigen Events 2021 und 2022 der dritte pgday Austria statt.
153 Teilnehmer hatten die Möglichkeit, insgesamt 21 Talks und 15 verschiedene Sponsoren zu besuchen und sich über alle möglichen Themen rund um PostgreSQL und der Community zu unterhalten.

Diese mal ebenfalls vertreten war der Sheldrick Wildlife Trust der sich er Rettung von Elefanten und Nashörnern widmet. Es konnte sich über das Projekt informiert werden, Spenden abgegeben und an einem Gewinnspiel teilgenommen werden.

Die Talks reichten dabei von den Themen Benchmarking über Crash-Recovery bis hin zu Big-Data.
Auch unser Kollege war mit seinem Talk „Postgres with many data: To MAXINT and beyond“ vertreten.

Als besonderes Highlight gab es am Ende des Tages vor dem Networking-Event noch neben den fast schon obligatorischen Lightning-Talks ein „Celebrity DB Deathmatch“ bei dem sich diverse Vertreter der Community für ein sehr unterhaltsames Bühnenstück zusammenfanden und in verschiedenen Disziplinen die beste Datenbank zu finden. Zur (zugegebenermaßen nicht großen) Überraschung aller hat PostgreSQL tatsächlich in jeder Kategorie brillieren können.

Wir hatten zusätzlich die Möglichkeit durch unsere Präsenz mit eigenem Stand eine Menge sehr interessante Gespräche und auch Diskussionen mit verschiedenen Mitgliedern der Community sowie auch den Sponsoren und generell den Besuchern zu führen.
Erstmalig war auch der neue Geschäftsführer der credativ GmbH nach unserer Wieder-Eigenständigkeit vor Ort und hat sich selbst ein Bild verschafft.

Alles in allem war es eine (noch) etwas kleinere, aber nichtsdestotrotz wie immer sehr lehrreiche und familiäre Veranstaltung und wir freuen uns bereits auf die Nächste und bedanken uns bei den Veranstaltern und auch dem gesamten Team vor Ort und im Hintergrund.

Mit der Veröffentlichung der Beta-Version von PostgreSQL 18 wurde eine spannende neue Funktion eingeführt: native Unterstützung für OAuth2. Da ich im Netz weder eine Anleitung noch einen Blogbeitrag gefunden habe, der zeigt, wie man diese Funktion nutzt, habe ich aus Neugier beschlossen, es selbst auszuprobieren.

Ich habe ein neues Projektverzeichnis angelegt und mit dem Experimentieren begonnen.

Umgebung und Zielsetzung

Ziel war es, PostgreSQL 18 Beta mit OAuth2-Authentifizierung einzurichten, für den Identity Provider habe ich Keycloak gewählt.

Ich habe mich dazu entschieden Docker Compose zu verwenden, um beide Dienste schnell und unkompliziert hoch fahren zu können.

Docker-Compose-Konfiguration

Hier ist das docker-compose.yml, das ich verwendet habe:

services:
  mock-oauth2:
    image: quay.io/keycloak/keycloak:24.0
    command: start --https-port=8080 --https-key-store-file=/etc/x509/https/localhost.p12 --https-key-store-password=changeit --import-realm --hostname-strict=false --hostname-url=https://mock-oauth2:8080
    ports:
      - "8080:8080"
    environment:
      KEYCLOAK_ADMIN: admin
      KEYCLOAK_ADMIN_PASSWORD: admin
    volumes:
      - ./certs:/etc/x509/https
      - ./keycloak-realm.json:/opt/keycloak/data/import/realm.json
    networks:
      - pgnet

  postgres18:
    build: .
    ports:
      - "5432:5432"
    environment:
      POSTGRES_PASSWORD: postgres
    volumes:
      - ./postgres/postgresql.conf:/etc/postgresql/postgresql.conf
      - ./postgres/pg_hba.conf:/etc/postgresql/pg_hba.conf
    command: ["-c", "config_file=/etc/postgresql/postgresql.conf"]
    networks:
      - pgnet

networks:
  pgnet:
    driver: bridge

PostgreSQL erwartet eine Verbindung zum OAuth2-Issuer über HTTPS. Dieselbe URL muss auch über den Host erreichbar sein, etwa bei der Nutzung des Keycloak-UI. Das bedeutet: Das Zertifikat muss sowohl auf dem Host als auch im Container gültig und vertrauenswürdig sein.

Der verwendete Hostname (z. B. https://mock-oauth2:8080) muss in beiden Umgebungen korrekt auflösbar sein.

Dazu habe ich folgende Zeile in meiner /etc/hosts-Datei auf dem Host ergänzt:

127.0.0.1 mock-oauth2

So konnten sowohl der PostgreSQL-Container als auch mein Host den Keycloak-Service unter https://mock-oauth2:8080 erreichen.

TLS-Zertifikat für Keycloak

Keycloak benötigt zwingend einen HTTPS-Endpunkt, damit die OAuth2-Issuer-URL von PostgreSQL akzeptiert wird.

Dafür habe ich ein selbstsigniertes Zertifikat erstellt und es in ein .p12-Keystore-Paket umgewandelt, das Keycloak verwenden kann.

Das .p12-Zertifikat wird über folgenden Mount in den Container eingebunden:

volumes:
  - ./certs:/etc/x509/https

Im certs-Verzeichnis liegt die Datei localhost.p12, welche ich aus meinem selbstsignierten Schlüssel und Zertifikat folgendermaßen erstellt habe:

openssl req -x509 -nodes -days 365 \
  -newkey rsa:2048 \
  -keyout server.key \
  -out server.crt \
  -subj "/CN=mock-oauth2" \
  -addext "subjectAltName = DNS:mock-oauth2,DNS:localhost,IP:127.0.0.1"

Keycloak-Realm-Konfiguration

Ich habe eine minimalistische Realm-Datei für Keycloak erstellt. Sie enthält einen Client namens postgres und einen Benutzer mit entsprechenden Anmeldedaten.

Inhalt von keycloak-realm.json:

{
  "realm": "pg",
  "enabled": true,
  "clients": [
    {
      "clientId": "postgres",
      "enabled": true,
      "publicClient": false,
      "redirectUris": ["*"],
      "protocol": "openid-connect",
      "secret": "postgres",
      "directAccessGrantsEnabled": true,
      "standardFlowEnabled": true
    }
  ],
  "users": [
    {
      "username": "postgres",
      "enabled": true,
      "credentials": [
        {
          "type": "password",
          "value": "postgres"
        }
      ]
    }
  ]
}

Nach dem Import des Realms war Keycloak bereit und der Standard-Scope im UI sichtbar.

Installation von libpq-oauth und oauth_validator

Die offizielle PostgreSQL-Image musste ich erweitern, um zusätzliche Abhängigkeiten wie die Erweiterung libpq-oauth sowie den Validator oauth_validator zu installieren.

PostgreSQL 18 unterstützt OAuth2-Authentifizierung experimentell. Allerdings liefert PostgreSQL keine eigene Validator-Bibliothek mit. In der offiziellen Dokumentation heißt es:

Die PostgreSQL-Distribution enthält keine Bibliotheken zur Validierung von OAuth2-Token. Benutzer müssen eine eigene Lösung bereitstellen oder selbst kompilieren.
PostgreSQL
Docs – oauth_validator_libraries

Für Tests habe ich folgende Open-Source-Implementierung verwendet:

github.com/TantorLabs/oauth_validator

Diese minimalistische C-Bibliothek kann kompiliert und als oauth_validator_library in PostgreSQL verwendet werden.

Verwendetes Dockerfile

FROM postgres:18beta1

USER root

RUN apt-get update \
    && apt-get install -y libpq-oauth build-essential libkrb5-dev \
       libsasl2-dev libcurl4-openssl-dev postgresql-server-dev-18 git \
    && git clone https://github.com/TantorLabs/oauth_validator.git /tmp/oauth_validator \
    && cd /tmp/oauth_validator \
    && make && make install \
    && rm -rf /tmp/oauth_validator \
    && apt-get remove -y build-essential git \
    && apt-get autoremove -y && rm -rf /var/lib/apt/lists/*

Dieses Image habe ich dann für den `postgres18`-Service in meinem Docker-Compose-Setup verwendet.

PostgreSQL mit der Keycloak-CA vertrauen lassen

PostgreSQL muss dem von Keycloak präsentierten Zertifikat vertrauen, sonst wird die Verbindung zum OAuth2-Issuer abgelehnt.

Dazu habe ich die Datei `mock-oauth.crt` in den PostgreSQL-Container kopiert und im typischen CA-Pfad abgelegt:

/usr/local/share/ca-certificates/

Dann habe ich innerhalb des Containers folgendes Kommando ausgeführt:

update-ca-certificates

Danach wurde das Zertifikat akzeptiert und PostgreSQL konnte die Verbindung zum HTTPS-Issuer erfolgreich überprüfen.

PostgreSQL-Konfiguration

In pg_hba.conf habe ich folgende Zeile ergänzt:

host all all all oauth scope="profile" issuer="https://mock-oauth2:8080/realms/pg" map="oauthmap"

In pg_ident.conf habe ich die vom Token bereitgestellte Identität dem PostgreSQL-Benutzer zugeordnet:

oauthmap "postgresID" "postgres"

Diese Zuordnung muss ggf. angepasst werden – abhängig davon, wie dein Keycloak-Client konfiguriert ist und welches Feld (z. B.
preferred_username oder sub) im Token übergeben wird.

Verbindungstest mit OAuth2

Zum Testen der Verbindung habe ich folgenden `psql`-Befehl verwendet:

psql "host=localhost \
      port=5432 \
      dbname=postgres \
      user=postgres \
      oauth_issuer=https://mock-oauth2:8080/realms/pg \
      oauth_client_id=postgres \
      oauth_client_secret=changeme \
      oauth_scope=profile"

Nach dem Aufruf erscheint eine Device-Code-Meldung wie:

Besuchen Sie `https://mock-oauth2:8080/realms/pg/device` und geben Sie den Code FBAD-XXYZ ein.

Nach Anmeldung mit den Benutzeranmeldedaten stellt `psql` erfolgreich eine Verbindung zu PostgreSQL über OAuth2 her.

Erkenntnisse und Tipps

• PostgreSQL 18 verlangt HTTPS für die OAuth2-Issuer-URL – selbst im lokalen Setup.
• pg_hba.conf ist empfindlich gegenüber Formatierungsfehlern. Ich musste die Konfiguration mehrfach neu laden (SELECT pg_reload_conf();) und die Logs genau analysieren.
• Um einer lokalen Zertifizierungsstelle zu vertrauen, reicht es, das .crt-Zertifikat in den Container zu kopieren und mit update-ca-certificates zu registrieren.
• Keycloak eignet sich gut für Tests mit OAuth2, aber man muss evtl. mit Scopes, Claims und Secrets experimentieren, bis alles mit PostgreSQL zusammenpasst.

Fazit

Das war ein spannendes, praxisnahes Experiment mit einer vielversprechenden neuen Funktion in PostgreSQL. OAuth2-Integration bringt PostgreSQL näher an moderne Identity-Management-Lösungen heran und vereinfacht den Betrieb in Umgebungen mit zentralisierter Authentifizierung.

Am Donnerstag, 26. Juni, und Freitag, 27. Juni 2025, hatten mein Kollege Patrick Lauer und ich die großartige Gelegenheit, am Swiss PGDay 2025 teilzunehmen, der an der OST Ostschweizer Fachhochschule in Rapperswil stattfand. Diese zweitägige PostgreSQL-Konferenz bot zwei parallele Vortragsreihen in englischer und deutscher Sprache und brachte vor allem Anwender und Experten aus der ganzen Schweiz zusammen. Unser Unternehmen credativ gehörte zu den Unterstützern der diesjährigen Konferenz.

Während der Veranstaltung hielt Patrick einen spannenden Vortrag mit dem Titel „Postgres mit vielen Daten: Bis MAXINT und darüber hinaus“, der auf früheren Diskussionen über den Einsatz von Postgres in großem Maßstab aufbaute. Er hob die praktischen Probleme hervor, die bei der Verarbeitung extrem großer Datensätze in PostgreSQL auftreten – beispielsweise, wie selbst ein einfacher SELECT COUNT(*) schmerzhaft langsam werden kann und wie Backups und Wiederherstellungen bei sehr großen Datensätzen Tage dauern können. Außerdem stellte er Strategien für ein effektives Leistungsmanagement in diesen Größenordnungen vor.

Ich präsentierte eine deutlich überarbeitete Version meines Vortrags „Aufbau eines Data Lakehouse mit PostgreSQL: Eintauchen in Formate, Tools, Techniken und Strategien”. Er behandelte moderne Datenformate und Frameworks wie Apache Iceberg und ging auf die wichtigsten Herausforderungen in Lakehouse-Architekturen ein – von Governance, Datenschutz und Compliance bis hin zu Datenqualitätsprüfungen und Anwendungsfällen für KI/ML. Der Vortrag hob die Fähigkeit von PostgreSQL hervor, eine zentrale Rolle in der heutigen Data-Lakehouse- und KI-Landschaft zu spielen. Zum Abschluss der Konferenz hielt ich einen kurzen Blitzvortrag, in dem ich unser neues Open-Source-Migrationstool „credativ-pg-migrator” vorstellte.

 
(c) Fotos von Gülçin Yıldırım Jelinek

Das Konferenzprogramm war vollgepackt mit vielen hochwertigen und aufschlussreichen Vorträgen. Besonders hervorheben möchten wir:

* Bruce Momjian – „Wie Open Source und Demokratie Postgres vorantreiben”: In seiner Keynote skizzierte Bruce Momjian, wie das Open-Source-Entwicklungsmodell und die demokratische Governance von PostgreSQL zum Erfolg der Datenbank beigetragen haben. Er erläuterte die Unterschiede zwischen Open-Source- und proprietären Modellen, gab einen Überblick über die Geschichte der Governance von PostgreSQL und zeigte auf, wie demokratische, offene Prozesse zu robuster Software und einer vielversprechenden Zukunft für Postgres führen.

* Gülçin Yıldırım Jelinek – „Anatomie von Sperren auf Tabellenebene in PostgreSQL”: In dieser Sitzung wurden die Grundlagen der Sperrmechanismen auf Tabellenebene in PostgreSQL behandelt. Es wurde erklärt, wie verschiedene Sperrmodi während Schemaänderungen erworben und in eine Warteschlange gestellt werden, damit die Teilnehmer verstehen, wie sie Sperrkonflikte verwalten, Ausfallzeiten minimieren und Deadlocks bei DDL-Operationen mit hoher Parallelität vermeiden können.

* Aarno Aukia – „Betrieb von PostgreSQL in großem Maßstab: Erkenntnisse aus Hunderten von Instanzen in regulierten privaten Clouds“: Der Referent berichtete über seine Erfahrungen mit dem Betrieb umfangreicher Postgres-Umgebungen in stark regulierten Branchen. Er diskutierte Architekturmuster, Automatisierungsstrategien und „Day-2-Operations“-Praktiken, die VSHN einsetzt, um strenge Anforderungen an Verfügbarkeit, Compliance und Audits zu erfüllen, darunter sichere Mandantenfähigkeit, deklarative Bereitstellungen, Backups, Überwachung und Lebenszyklusmanagement in unternehmenskritischen Cloud-nativen Setups.

* Bertrand Hartwig-Peillon – „pgAssistant”: Der Autor stellte pgAssistant vor, ein Open-Source-Tool, das Entwicklern dabei hilft, PostgreSQL-Schemas und -Abfragen vor der Produktionsbereitstellung zu optimieren. Er demonstrierte, wie pgAssistant deterministische Analysen mit einem KI-gesteuerten Ansatz kombiniert, um Schema-Inkonsistenzen zu erkennen und Optimierungen vorzuschlagen, wodurch Best Practices und Leistungsoptimierungen innerhalb von Entwicklungsworkflows effektiv automatisiert werden.

* Gianni Ciolli – „The Why and What of WAL”: Gianni Ciolli gab in seiner gewohnt prägnanten italienischen Art einen Überblick über die Geschichte und die Funktionsweise des Write-Ahead Log (WAL) von PostgreSQL. Er erläuterte die zentrale Rolle von WAL in PostgreSQL für Absturzsicherheit, Backups und Replikation und zeigte Beispiele für WAL-fähige Funktionen wie schnelle Absturzbehebung, effiziente Hot-Backups, physische Replikation und logische Dekodierung.

* Daniel Krefl – „Hacking pgvector for performance”: Der Referent stellte eine verbesserte Version der pgvector-Erweiterung für die Verarbeitung großer Datenmengen vor, die durch die Verwaltung des Vektorindex außerhalb des PostgreSQL-Speichers und die Auslagerung von Berechnungen, einschließlich GPU-Integration, optimiert wurde. Er beschrieb detailliert den Prozess der Auslagerung der Kernlogik von pgvector zur Verbesserung der Geschwindigkeit und demonstrierte bemerkenswerte Leistungssteigerungen im Rahmen des EU-AERO-Projekts. Er sprach auch über die verteilten PostgreSQL-Versionen XC, XL und TBase, die leider noch auf der alten Version 10 basieren, und darüber, wie er Änderungen aus diesen Projekten in die Version 16 portiert hat.

* Luigi Nardi – „Eine Benchmark-Studie zu den Auswirkungen der Optimierung von PostgreSQL-Serverparametern”: Luigi Nardi präsentierte umfassende Benchmark-Ergebnisse zur Optimierung von PostgreSQL-Konfigurationsparametern. Er hob hervor, dass viele Benutzer die Standardeinstellungen verwenden, und zeigte, wie durch eine richtige Optimierung für verschiedene Workloads (OLTP, OLAP usw.) erhebliche Leistungsverbesserungen erzielt werden können. Dabei lieferte er umsetzbare Erkenntnisse, die auf bestimmte Umgebungen zugeschnitten sind.

* Renzo Dani – „Von Oracle zu PostgreSQL: Eine SCHWIERIGE Reise und ein Open-Source-Erwachen”: Der Autor berichtete über seine Erfahrungen bei der Migration einer komplexen Unternehmensanwendung von Oracle zu PostgreSQL und ging dabei auf bedeutende Herausforderungen wie implizite Typumwandlungen, Unterschiede bei der Funktionsüberladung, Probleme mit JDBC-Treibern und SQL-Validierungsprobleme ein. Er hob auch die Vorteile hervor, darunter schnellere CI-Pipelines, flexiblere Bereitstellungen und Innovationsmöglichkeiten durch das Open-Source-Produkt Postgres, und gab praktische Ratschläge zu Migrationstools, Teststrategien und zum Management von Kompromissen.

(c) Foto: Swiss PostgreSQL User Group

Am Ende des ersten Tages genossen alle Teilnehmer ein Networking-Dinner. Wir möchten uns beide ganz herzlich bei den Organisatoren des Swiss PGDay (Swiss PostgreSQL User Group) für eine großartige Veranstaltung bedanken. Der Swiss PGDay 2025 war eine unvergessliche und wertvolle Erfahrung, die großartige Lern- und Networking-Möglichkeiten bot. Wir sind auch credativ sehr dankbar, dass sie uns die Teilnahme ermöglicht haben, und freuen uns schon auf die nächsten Ausgaben dieser hervorragenden Konferenz.

Viele Unternehmen denken heutzutage darüber nach, ihre Datenbanken von Legacy- oder proprietären Systemen auf PostgreSQL zu migrieren. Das primäre Ziel ist es, die Kosten zu senken, die Fähigkeiten zu verbessern und die langfristige Nachhaltigkeit zu gewährleisten. Doch allein die Vorstellung, auf PostgreSQL zu migrieren, kann schon überwältigend sein. Sehr oft ist das Wissen über die Altanwendungen begrenzt oder sogar verloren gegangen. In einigen Fällen nimmt die Unterstützung durch den Hersteller ab, und der Expertenpool und die Unterstützung durch die Community schrumpfen. Außerdem laufen alte Datenbanken oft auf veralteter Hardware und alten Betriebssystemen, was weitere Risiken und Einschränkungen mit sich bringt. (mehr …)

Wir freuen uns sehr, Gastgeber des 5. PostgreSQL User Group NRW Meetups zu sein – und das in unseren neuen Geschäftsräumen direkt am Borussia-Park in Mönchengladbach! (mehr …)

Die PGConf.DE 2025, die 9. jährliche PostgreSQL-Konferenz in Deutschland, fand vom 8. bis 9. Mai 2025 im Marriott Hotel in der Nähe des Potsdamer Platzes in Berlin statt. Die Veranstaltung brachte zwei Tage lang PostgreSQL-Enthusiasten, Entwickler, DBAs und Industriesponsoren zu faszinierenden Vorträgen in vier parallelen Tracks zusammen. Mit 347 Teilnehmern war es die bisher größte Veranstaltung. Die gesamte Konferenz war sehr gut organisiert, und daher gebührt allen Organisatoren – insbesondere Andreas Scherbaum, dem Hauptorganisator – ein besonderer Dank für ihre Bemühungen und ihre harte Arbeit.

Unser Unternehmen, die credativ GmbH, ist wieder unabhängig und nahm als Gold-Sponsor teil. Der CTO von credativ, Alexander Wirt, der Head of Sales & Marketing, Peter Dreuw, und der Teamleiter des Datenbank-Teams, Dr. Tobias Kauder, standen den Teilnehmern am Stand von credativ zur Verfügung. Vielen Dank an unseren Teamkollegen Sascha Spettmann für die Lieferung aller Utensilien und Werbetafeln zur Konferenz und zurück.

Insgesamt hielten wir vier Vorträge auf der Konferenz. Michael Banck, technischer Leiter unseres Datenbank-Teams, präsentierte den deutschsprachigen Vortrag „PostgreSQL Performance Tuning“. Er gab einen tiefen und umfassenden Überblick über die wichtigsten Performance-Tuning-Parameter in PostgreSQL und erklärte, wie sie das Verhalten der Datenbank beeinflussen. Sein Vortrag zog ein großes Publikum an und wurde sehr gut aufgenommen.

Ich hatte die absolut einzigartige Gelegenheit, drei verschiedene Vorträge im englischen Track zu halten. In meinem regulären Vortrag „PostgreSQL Connections Memory Usage: How Much, Why and When“ präsentierte ich die Ergebnisse meiner Forschung und Tests zum Speicherverbrauch von PostgreSQL-Verbindungen. Nachdem ich die wichtigsten Aspekte des Linux-Speichermanagements und die Messungen des Speicherverbrauchs, die von Standardbefehlen gemeldet werden, erläutert hatte, beschrieb ich detailliert den Speicherverbrauch von PostgreSQL-Verbindungen während der Abfrageausführung basierend auf den Zahlen, die in smaps-Dateien gemeldet wurden. Ich plane hierzu detaillierte Blogbeiträge über meine Erkenntnisse zu veröffentlichen. Mein anderer Vortrag, „Building a Data Lakehouse with PostgreSQL“, wurde ursprünglich als Reservevortrag für den Fall einer kurzfristigen Absage ausgewählt. Der Vortrag „Creating a Board Game Chatbot with Postgres, AI, and RAG“ von Matt Cornillon musste ersetzt werden. Der Sprecher konnte nicht teilnehmen, da sein Flug sehr kurzfristig und unerwartet annulliert wurde.

Im Sponsor-Track präsentierten der CTO von credativ, Alexander Wirt, und ich einen Vortrag mit dem Titel „Your Data Deserves the Best: Migration to PostgreSQL“. Darin stellten wir unser neues Migrationstool „credativ-pg-migrator“ vor. Es ist in der Lage, Datenmodelle (Tabellen, Daten, Indizes, Constraints und Views) von Informix, IBM DB2 LUW, MS SQL Server, Sybase ASE, SQL Anywhere und MySQL/MariaDB zu migrieren. Im Falle von Informix kann es auch Stored Procedures, Funktionen und Trigger in PostgreSQL PL/pgSQL konvertieren. Weitere Details zu diesem Tool werden wir in einem separaten Blogbeitrag mitteilen.

Da es immer drei oder vier parallele Vorträge gab, musste ich sorgfältig auswählen, an welchen Sessions ich teilnehmen wollte. Besonders gefallen hat mir der Vortrag „András in Windowsland – a DBA’s (mis)adventures“ von András Váczi von Cybertec. Der Sprecher gab viele nützliche Tipps für den Zugriff auf und die Fehlerbehebung von PostgreSQL unter Windows. Mir gefiel auch der deutsche Vortrag „Modern VACUUM“ von Christoph Berg von Cybertec. Er gab wertvolle Einblicke in die Geschichte und Implementierungsdetails des VACUUM-Befehls und der Autovacuum-Hintergrundprozesse. Ein weiterer sehr interessanter Vortrag war die deutsche Präsentation „Modernes SSL ideal einsetzen“ von Peter Eisentraut von EDB. Der Vortrag behandelte die Auswahl geeigneter Protokollversionen und kryptografischer Cipher Suites, die Verwaltung von Schlüsseln und Zertifikaten sowie die Konfiguration von Client-/Server-Einstellungen zur Erfüllung zeitgemäßer Sicherheitsstandards. Der Vortrag „Comparing the Oracle and PostgreSQL transaction systems“ von Laurenz Albe von Cybertec erhielt viel wohlverdiente Aufmerksamkeit. Zu den Kernthemen gehörten der Undo/Redo-Mechanismus von Oracle im Vergleich zum MVCC-Ansatz von PostgreSQL, Unterschiede bei den Standard-Isolationsstufen und Anomalien sowie die Art und Weise, wie jede Datenbank Constraints und transaktionale DDL implementiert. Nicht zuletzt möchte ich den Vortrag „What is an SLRU anyway?“ erwähnen, der von einem wichtigen PostgreSQL-Contributor, Álvaro Herrera, gehalten wurde. Er erklärte, dass SLRUs im Wesentlichen zirkuläre Logs mit einem In-Memory-Cache sind, die zur Nachverfolgung von Informationen wie committeden Transaktionen oder Snapshot-Daten verwendet werden, und hob die Bedeutung der Innovationen in PostgreSQL 17 hervor, die SLRU-Cache-Größen konfigurierbar machten. Leider wurden die Vorträge nicht aufgezeichnet, aber die Folien für die Mehrheit der Vorträge sind bereits auf der Konferenz-Website verfügbar.

Die gesamte Veranstaltung war sehr informativ und bot hervorragende Möglichkeiten zum Networking. Wir freuen uns schon auf die nächste PGConf.DE. In der Zwischenzeit bleiben Sie auf dem Laufenden über alle Neuigkeiten der credativ und folgen Sie uns auf den sozialen Medien – LinkedIn und Mastodon.

Wenn Sie mehr über unsere Angebote zu PostgreSQL-Services wissen möchten, klicken Sie bitte hier!

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

Zwei Ansätze zur Duplikatsvermeidung

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

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

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

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

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

Testbeispiel

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

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

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

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

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

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

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

Beobachtete Ergebnisse

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

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

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

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

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

Zusammenfassung

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

Veranstaltungsort

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

Talks

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

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

Patroni Deployment Patterns, Michael Banck, PGConf.EU 24

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

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

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

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

Ausblick

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Einführung

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

Benchmark

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

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

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

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

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

Analyse

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

Fazit

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