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.

• Tomas Vondra präsentierte „The Past and the Future of the Postgres Community“, wobei er erklärte, wie die Arbeit an PostgreSQL-Änderungen und -Korrekturen in Commitfests organisiert ist und zukünftige Entwicklungsideen innerhalb der Community diskutierte.
• Laurenz Albes Vortrag, „Sicherheitsattacken auf PostgreSQL“, beleuchtete mehrere potenzielle Angriffsvektoren in PostgreSQL und erregte mit überraschenden Beispielen große Aufmerksamkeit.
• Chris Engelberts Präsentation, „PostgreSQL on Kubernetes: Dos and Don’ts“, behandelte die Hauptprobleme im Zusammenhang mit dem Betrieb von PostgreSQL auf Kubernetes und diskutierte Lösungen, einschließlich der Vor- und Nachteile bestehender PostgreSQL Kubernetes Operatoren.
• Maurizio De Giorgi und Ismael Posada Trobo diskutierten „Solving PostgreSQL Connection Scalability Issues: Insights from CERN’s GitLab Service“ und erläuterten die Herausforderungen und Lösungen für die Skalierbarkeit in CERNs riesiger Datenbankumgebung.
• Dirk Krautschicks Vortrag, „Warum sich PostgreSQL-Fans auch für Kafka und Debezium interessieren sollten?“, zeigte Beispiele für die Verwendung von Debezium-Konnektoren und Kafka mit PostgreSQL für verschiedene Anwendungsfälle, einschließlich Datenmigrationen.
• Patrick Stählin diskutierte „Wie wir einen Datenkorruptions-Bug mit der Hilfe der Community gefunden und gefixt haben“, wobei er Probleme mit Free-Space-Map-Dateien nach der Migration auf PostgreSQL 16 ansprach.
• Marion Baumgartners Präsentation, „Geodaten-Management mit PostGIS“, lieferte interessante Details zur Verarbeitung von Geodaten in PostgreSQL mithilfe der PostGIS-Erweiterung.
• Prof. Stefan Keller, einer der Hauptorganisatoren und Professor für Data Engineering an der OST Universität Rapperswil, präsentierte „PostgreSQL: A Reliable and Extensible Multi-Model SQL Database“, wobei er die Multi-Modell-Struktur von PostgreSQL inmitten des schwindenden Interesses an NoSQL-Lösungen diskutierte.
• Luigi Nardi von DBTune präsentierte „Lessons Learned from Autotuning PostgreSQL“ und beschrieb ein KI-basiertes Performance-Tuning-Tool, das von seinem Unternehmen entwickelt wurde.
• Kanhaiya Lal und Belma Canik gingen auf „Beyond Keywords: AI-powered Text Search with pgvector for PostgreSQL“ ein und untersuchten die Verwendung der pgvector-Erweiterung zur Verbesserung der Volltextsuchfunktionen in PostgreSQL.
• Gabriele Bartolini, der Entwickler des PostgreSQL Kubernetes Operators „CloudNativePG“, diskutierte in seinem Vortrag „Unleashing the Power of PostgreSQL in Kubernetes“ die Geschichte und Fähigkeiten dieses Operators.

Die Deutsche Postgres Konferenz fand dieses Jahr zum 8. Mal statt, dieses Mal am 12. April in München. Es war die bisher größte PGConf.DE mit 270 Registrierungen, 16 mehr als letztes Jahr. Auf der Konferenz waren wir mit sieben Kollegen vertreten, sodass wir uns über die drei parallelen Tracks plus einen Sponsor-Track aufteilen konnten. Einer der drei Tracks hatte deutschsprachige Vorträge, wo auch Michael Banck in seinem Vortrag die drei führenden Cloud Provider (Amazon RDS, Google Cloud SQL, Microsoft Azure Database) miteinander verglich zudem es auch positives Feedback gab.

Michael Banck bei seinem Vortrag – PostgreSQL-As-A-Service: Vergleich von Cloud Providern

Simon Riggs, eine wichtige Person in der PostgreSQL-Community verstarb leider zwei Wochen vor der Konferenz. In den Vorträgen gab es große Anteilnahme an seinem Verlust und Gülçin Jelinek gedachte seiner gleich zu Beginn der Konferenz. Das letzte mal trafen wir ihn Ende 2023 in Prag auf der PGConf.EU, wo er die Keynote hielt.

Am Tag vor der Konferenz nahm mein Kollege Michael Banck an dem zweiten Patroni Contributor Meeting teil. Das erste Meeting war letztes Jahr vor der letzten Deutschen Postgres Konferenz in Essen. Zu dem Treffen kamen die beiden Patroni-Maintainer Alexander Kukushkin und Polina Bungina, sowie Verteter von EnterpriseDB, Cybertec und Data Egret. Es wurden einige organisatorische Fragen (z.B. über das Logo) geklärt, die Roadmap besprochen sowie einige vorgeschlagene Features für Multi-Site Patroni diskutiert. Nach dem Patroni Meeting war am Abend das Organiser- und Speaker-Dinner im Gasthaus Eder in der Nähe des Konferenz-Hotels.

Der Feature Freeze für PostgreSQL 17 war nur wenige Tage vor der Konferenz am 08. April. Daher wurden die damit verbundenen Verbesserungen in den Vorträgen berücksichtigt oder befassten sich vollständig mit einem neuen Feature. Dazu zählen unter anderem native inkrementelle Backups und Migration von logical replication Slots bei Major Upgrades. Die Veröffentlichung von PostgreSQL 17 ist für September geplant. Bis dahin könnten einzelne Features noch entfernt werden, falls Probleme auftreten, die nicht rechtzeitig gelöst werden können.

Insgesamt war die Qualität der Vorträge sehr hoch, sodass man aus jedem Vortrag etwas interessantes mitnehmen konnte. Deshalb freuen wir uns bereits auf die kommende PGConf.EU im Oktober in Athen.

Übersicht

Was ist das Hauptproblem bei der Aufblähung von Systemtabellen?

Beispiel für eine Berichtstabelle in einer Buchhaltungssoftware

CREATE TEMP TABLE pivot_temp_table (
  id serial PRIMARY KEY,
  inserted_at timestamp DEFAULT current_timestamp,
  client_id INTEGER,
  name text NOT NULL,
  address text NOT NULL,
  loyalty_program BOOLEAN DEFAULT false,
  loyalty_program_start TIMESTAMP,
  orders_202301_count_of_orders INTEGER DEFAULT 0,
  orders_202301_total_price NUMERIC DEFAULT 0,
  ...
  orders_202312_count_of_orders INTEGER DEFAULT 0,
  orders_202312_total_price NUMERIC DEFAULT 0);

CREATE INDEX pivot_temp_table_idx1 ON pivot_temp_table (client_id);
CREATE INDEX pivot_temp_table_idx2 ON pivot_temp_table (name);
CREATE INDEX pivot_temp_table_idx3 ON pivot_temp_table (loyalty_program);
CREATE INDEX pivot_temp_table_idx4 ON pivot_temp_table (loyalty_program_start);

Tabelle pg_attribute

SELECT
  c.relname as table_name,
  o.rolname as table_owner,
  c.relkind as table_type,
  a.attname as column_name,
  a.attnum as column_number,
  a.atttypid::regtype as column_data_type,
  pg_get_expr(adbin, adrelid) as sql_command
FROM pg_attrdef ad
JOIN pg_attribute a ON ad.adrelid = a.attrelid AND ad.adnum = a.attnum
JOIN pg_class c ON c.oid = ad.adrelid
JOIN pg_authid o ON o.oid = c.relowner
WHERE c.relname = 'pivot_temp_table'
ORDER BY table_name, column_number;

  table_name | table_owner | table_type | column_name | column_number | column_data_type | sql_command
------------------+-------------+------------+-------------------------------+---------------+-----------------------------+----------------------------------------------
  pivot_temp_table | postgres | r | id | 1 | integer | nextval('pivot_temp_table_id_seq'::regclass)
  pivot_temp_table | postgres | r | inserted_at | 2 | timestamp without time zone | CURRENT_TIMESTAMP
  pivot_temp_table | postgres | r | loyalty_program | 6 | boolean | false
  pivot_temp_table | postgres | r | orders_202301_count_of_orders | 8 | integer | 0
  pivot_temp_table | postgres | r | orders_202301_total_price | 9 | numeric | 0
-->  bis zur Spalte "orders_202312_total_price"

Tabelle pg_class

Zusammenfassung dieses Beispiels

Beispiel für einen Online-Shop

Analyse des Datenverkehrs

WITH tablenames AS (SELECT tablename FROM (VALUES('pg_attribute'),('pg_attrdef'),('pg_class')) as t(tablename))
SELECT
  tablename,
  now() as checked_at,
  pg_relation_size(tablename) as relation_size,
  pg_relation_size(tablename) / (8*1024) as relation_pages,
  a.*,
  s.*
FROM tablenames t
JOIN LATERAL (SELECT * FROM pgstattuple(t.tablename)) s ON true
JOIN LATERAL (SELECT last_autovacuum, last_vacuum, last_autoanalyze, last_analyze, n_live_tup, n_dead_tup
FROM pg_stat_all_tables WHERE relname = t.tablename) a ON true
ORDER BY tablename

  tablename | pg_attribute
  checked_at | 2024-02-18 10:46:34.348105+00
  relation_size | 44949504
  relation_pages | 5487
  last_autovacuum | 2024-02-16 20:07:15.7767+00
  last_vacuum | 2024-02-16 20:55:50.685706+00
  last_autoanalyze | 2024-02-16 20:07:15.798466+00
  last_analyze | 2024-02-17 22:05:43.19133+00
  n_live_tup | 3401
  n_dead_tup | 188221
  table_len | 44949504
  tuple_count | 3401
  tuple_len | 476732
  tuple_percent | 1.06
  dead_tuple_count | 107576
  dead_tuple_len | 15060640
  dead_tuple_percent| 33.51
  free_space | 28038420
  free_percent | 62.38

WITH pages AS (
  SELECT * FROM generate_series(0, (SELECT pg_relation_size('pg_attribute') / 8192) -1) as pagenum),
tuples_per_page AS (
  SELECT pagenum, nullif(sum((t_xmin is not null)::int), 0) as tuples_per_page
  FROM pages JOIN LATERAL (SELECT * FROM heap_page_items(get_raw_page('pg_attribute',pagenum))) a ON true
  GROUP BY pagenum)
SELECT
  count(*) as pages_total,
  min(tuples_per_page) as min_tuples_per_page,
  max(tuples_per_page) as max_tuples_per_page,
  round(avg(tuples_per_page),0) as avg_tuples_per_page,
  mode() within group (order by tuples_per_page) as mode_tuples_per_page
FROM tuples_per_page

  pages_total | 5487
  min_tuples_per_page | 1
  max_tuples_per_page | 55
  avg_tuples_per_page | 23
  mode_tuples_per_page | 28