PostgreSQL® 13 Archives

Am 24.09.2020 veröffentlicht das PostgreSQL^®-Projekt die 13. Version der freien Datenbank PostgreSQL^®. Auch in diesem Jahr gibt es zahlreiche neue Features und Verbesserungen.

Einige dieser Neuerungen haben wir in vorherigen Blogartikel bereits vorgestellt. Hier noch einmal zwei wichtige Features im Überlick:

Parallel VACUUM

Mit PostgreSQL^® 13 lernt das VACUUM–Kommando eine neue Fähigkeit: Das parallele Aufräumen von Indexen. VACUUM in einer PostgreSQL^®-Datenbank ist im Wesentlichen für mehrere wichtige Aufgaben verantwortlich:

Garbage Collection, also freigeben von gelöschten Tupel für die Wiederverwendung
Verhindern von Bloat in einer Datenbank und damit von ausuferndem Plattenplatzbedarfs
Verhindern des Überlaufs des Transaktionszählers (FREEZE)
Diverses Aufräumen von Transaktionsinformationen, usw.

Ausführliche Informationen zum Thema Parallel VACUUM erhalten Sie in unserem Blogartikel: PostgreSQL^® 13 Bits: Parallel VACUUM

Deduplication in B-tree Indexe

Mit der kommenden Version 13 von PostgreSQL^® werden B-tree Indexe in der Lage sein, mehrfach identisch vorkommende Spaltenwerte physikalisch kompakter abzuspeichern. Bereits mit Version 12 wurde eine Optimierung eingeführt, die B-tree Indexe ein effizienteres Speichern von wiederholt vorkommenden Spaltenwerten gestattet. Hierzu werden diese Spalten in derselben Reihenfolge auch im Index abgelegt, wie diese auch physikalisch im sogenannten Heap, also der physischen Repräsentation der Tabelle, vorkommen. Dies vereinfacht die Verwaltung dieser Indexeinträge und kann auch bereits mit dieser Maßnahme das Wachstum eines Indexes positiv beeinflussen.

In Version 13 werden nun effektiv wiederholt vorkommende Spaltenwerte zusammengefasst im B-tree Index gespeichert, Deduplication genannt. Eine sogenannte Posting List verwaltet dabei, als alternative Repräsentation eines Index Tupel die physischen Positionen an welcher Stelle in der Tabelle der jeweilige Spaltenwert zu finden ist. Diese Repräsentation ist deutlich kompakter als die Spaltenwerte jeweils für sich zu speichern. Ferner ermöglicht es auch das schnellere Auffinden von entsprechenden Spaltenwerten und VACUUM kann solche Indexe auch effizienter warten.

Auch zu diesem Thema haben wir einen ausführlichen Blogartikel verfasst: PostgreSQL^® 13 Bits: Deduplication in B-tree Indexe

Fragen?

Bei Fragen rund um den Einsatz von PostgreSQL^® 13 stehen wir Ihnen natürlich gerne zur Verfügung. Sprechen Sie uns an! Unser PostgreSQL^® Competence Center ist bei Bedarf 24 Stunden am Tag, an 365 Tagen im Jahr für Sie verfügbar.

Wir bedanken uns bei allen Entwicklern des PostgreSQL^®-Projekts und natürlich ganz besonders bei unseren eigenen Mitarbeitern, die ebenfalls an den Arbeiten für PostgreSQL^® 13 beteiligt waren.

POSTGRES XIII

Während des Beta-Zeitraums von PostgreSQL^® 13 hat credativ auch in diesem Jahr wieder spannendes Merchandise gestaltet. Angelehnt an die Raumfahrtmission Apollo 13 wurde ein Design entwickelt, das den Fortschritt und die hohe Reichweite von PostgreSQL^® darstellt.

Gerne möchten wir von Ihnen wissen, warum Sie PostgreSQL^® einsetzen. Hierzu können Sie uns auf Twitter einen Retweet mit Kommentar auf unseren Beitrag schreiben.

Als Dankeschön verschenken wir an zehn zufällig ausgewählte Teilnehmer jeweils ein POSTGRES XIII Merchandise-Set mit T-Shirt, Patches und Stickern.

Teilnahmeschluss ist der 24.09.2020 um 16 Uhr.

Viel Glück und Erfolg wünscht das Team der credativ GmbH!

Teilnahmebedingungen

Die Teilnahme am Beitrag von credativ GmbH, nachfolgend Betreiber oder Veranstalter genannt, ist kostenlos und richtet sich ausschließlich nach diesen Teilnahmebedingungen.

Ablauf

Die Dauer des Ablaufes erstreckt sich vom 23.09.2020, 12:30 Uhr bis zum 24.09.2020, 16 Uhr. Innerhalb dieses Zeitraums erhalten Nutzer über die Plattform Twitter die Möglichkeit, teilzunehmen.

Teilnahme

Um teilzunehmen ist ein Retweet inklusive Kommentar über die Begründung zur Nutzung von PostgreSQL^® des entsprechenden Twitter-Beitrages von @credativde notwendig. Die Teilnahme ist nur innerhalb des Teilnahmezeitraums möglich. Nach Teilnahmeschluss eingehende Einsendungen werden bei der Auswahl nicht berücksichtigt.

Pro Teilnehmer nimmt nur eine übermittelte Anmeldung teil. Es ist untersagt, mehrere Twitter-Accounts zur Erhöhung der Chancen zu verwenden.

Die Teilnahme ist kostenlos.

Teilnahmeberechtigte

Teilnahmeberechtigt sind natürliche Personen, die das 14. Lebensjahr vollendet haben. Die Teilnahme ist nicht auf Kunden des Veranstalters beschränkt und nicht vom Erwerb einer Ware oder Dienstleistung abhängig.

Sollte ein Teilnehmer in seiner Geschäftsfähigkeit eingeschränkt sein, bedarf es der Einwilligung seines gesetzlichen Vertreters.

Nicht teilnahmeberechtigt sind alle an der Konzeption und Umsetzung beteiligte Personen und Mitarbeiter des Betreibers sowie ihre Familienmitglieder. Zudem behält sich der Betreiber vor, nach eigenem Ermessen Personen von der Teilnahme auszuschließen, wenn berechtigte Gründe vorliegen, beispielsweise

(a) bei Manipulationen im Zusammenhang mit der Durchführung, (b) bei Verstößen gegen diese Teilnahmebedingungen, (c) bei unlauterem Handeln oder (d) bei falschen oder irreführenden Angaben im Zusammenhang mit der Teilnahme.

Benachrichtigung und Übermittlung des Merchandise-Sets

Zehn von credativ zufällig ausgewählte Teilnehmer erhalten jeweils ein POSTGRES XIII Merchandise-Set mit T-Shirt, Patches und Stickern. Es kommen ausschließlich diejenigen Teilnehmer in die Auswahl, die einen Retweet mit Kommentar über Ihre Begründung zur Nutzung von PostgreSQL^® korrekt durchgeführt haben.

Die zufällig ausgewählten Teilnehmer werden zeitnah über eine gesonderte Privatnachricht bei Twitter informiert, sowie in einem öffentlichen Beitrag markiert.

Die Aushändigung der Merchandise-Sets erfolgt ausschließlich an die ausgewählten Teilnehmer oder an die gesetzlichen Vertreter der minderjährigen Teilnehmer. Ein Umtausch, eine Selbstabholung sowie eine Barauszahlung sind nicht möglich.

Für den Versand des Merchandise-Sets anfallende Kosten übernimmt der Betreiber. Mit der Inanspruchnahme verbundene Zusatzkosten gehen zu Lasten der ausgewählten Teilnehmer. Für eine etwaige Versteuerung der Merchandise-Sets sind die ausgewählten Teilnehmer selbst verantwortlich.

Meldet sich der ausgewählte Teilnehmer nach zweifacher Aufforderung innerhalb einer Frist von 3 Wochen nicht, kann das Merchandise-Set auf einen anderen Teilnehmer übertragen werden.

Beendigung

Der Veranstalter behält sich ausdrücklich vor, den Beitrag ohne vorherige Ankündigung und ohne Mitteilung von Gründen zu beenden. Dies gilt insbesondere für jegliche Gründe, die einen planmäßigen Ablauf des des Beitrages stören oder verhindern würden.

Datenschutz

Für die Teilnahme ist ein Twitter-Account notwendig.

Der Veranstalter weist darauf hin, dass sämtliche personenbezogenen Daten des Teilnehmers weder an Dritte weitergegeben noch diesen zur Nutzung überlassen werden.

Der Teilnehmer erklärt sich mit der Nennung seines Twitter-Profiles auf der Plattform Twitter einverstanden.

Sobald die personenbezogenen Daten des Teilnehmers nicht mehr benötigt werden, werden diese umgehend gelöscht.

Der Teilnehmer kann seine erklärte Einwilligung jederzeit widerrufen. Der Widerruf ist schriftlich an die im Impressumsbereich angegebenen Kontaktdaten des Betreibers zu richten. Nach Widerruf der Einwilligung werden die erhobenen und gespeicherten personenbezogenen Daten des Teilnehmers umgehend gelöscht.

Twitter Disclaimer

Diese Aktion steht in keiner Verbindung zu Twitter und wird in keiner Weise von Twitter gesponsert, unterstützt oder organisiert.

Anwendbares Recht

Fragen oder Beanstandungen sind an den Betreiber zu richten. Kontaktmöglichkeiten finden sich im Impressumsbereich des Betreibers.

Der Beitrag des Betreibers unterliegt ausschließlich dem Recht der Bundesrepublik Deutschland. Der Rechtsweg ist ausgeschlossen.

Salvatorische Klausel

Sollte eine Bestimmung dieser Teilnahmebedingungen ganz oder teilweise unwirksam sein oder werden, so wird dadurch die Gültigkeit dieser Teilnahmebedingungen im Übrigen nicht berührt. Statt der unwirksamen Bestimmung gilt diejenige gesetzlich zulässige Regelung, die dem in der unwirksamen Bestimmung zum Ausdruck gekommenen Sinn und Zweck wirtschaftlich am nächsten kommt. Entsprechendes gilt für den Fall des Vorliegens einer Regelungslücke in diesen Teilnahmebedingungen.

Dieser Artikel wurde ursprünglich von Philip Haas geschrieben.

Hintergrund

Mit PostgreSQL^® 13 lernt das VACUUM–Kommando eine neue Fähigkeit: Das parallele Aufräumen von Indexen. VACUUM in einer PostgreSQL^®-Datenbank ist im wesentlichen für mehrere wichtige Aufgaben verantwortlich:

Garbage Collection, also freigeben von gelöschten Tupel für die Wiederverwendung
Verhindern von Bloat in einer Datenbank und damit von ausuferndem Plattenplatzbedarfs
Verhindern des Überlaufs des Transaktionszählers (FREEZE)
Diverses Aufräumen von Transaktionsinformationen, usw.

Neben dem manuellen VACUUM Kommando gibt es noch den sogenannten Autovacuum Datenbankprozess, der sich automatisch im Hintergrund um diese Aufgaben kümmert. Im Groben entspricht die Arbeitsweise von Autovacuum auch dem Vorgehen bei Ausführung eines manuellem VACUUM-Kommandos. Diese gliedert sich im Wesentlichen in mehrere Phasen:

Scannen der Tabellenblöcke und Ermitteln von darin enthaltenen, toten Tupeln und, falls notwendig, FREEZE
Die toten Tupel werden im Speicher gesammelt (maintenance_work_mem ist hier die Obergrenze)
Löschen der Tupelreferenzen aus entsprechenden Indexen (Vacuum Index Phase)
Für jeden gescannten Tabellenblock, Löschen der darin enthaltenen toten Tupel
Für jeden gescannten Tabellenblock, Reorganisieren der lebenden Tupel
Aktualisieren der Visibility Map und Freespace Map
Ist maintenance_work_mem voll, bevor das Ende einer Tabelle erreicht wurde, zurück zu 2.)
Aufräumen (Cleanup Phase), u.a. Aktualisierung der Index-Statistiken für VACUUM
Löschen von leeren Tabellenblöcken am Ende der Tabelle (falls vorhanden)
Aktualisieren der Laufzeitstatistiken
Aufräumen von Transaktionsinformationen

Arbeitsteilung

Mit der neuen Kommandooption PARALLEL kann das VACUUM Kommando nun die Vacuum Index und Cleanup Phase mit mehreren Arbeitsprozessen (Workern) parallel durchführen, z.B.:

VACUUM (ANALYZE, PARALLEL 4) my_table;

Dies führt ein VACUUM mit bis zu vier parallelen Workern mit zusätzlicher Aktualisierung der Optimizer Statistiken durch. Das bisherige Verfahren bleibt für die Tabelle selbst unverändert, mit PARALLEL verarbeitet VACUUM nun mit jeweils einem dedizierten Worker einen spezifischen Index. Zu beachten ist, dass der Leader, also die Session die das VACUUM-Kommando ausführt, ebenfalls entsprechende Arbeit verrichtet. Wird „0“ als Anzahl der Worker spezifiziert, wird die parallele Verarbeitung der Indexe deaktiviert und der bisherige Arbeitsablauf verwendet. Sind weniger als vier Indexe auf der Tabelle vorhanden, so wird die Anzahl der Worker entsprechend angepasst. Auch kann es passieren, dass aufgrund nebenläufiger DDL Kommandos (anderes paralleles VACUUM oder parallelisiertes CREATE INDEX) weniger Worker ausgewählt werden, da die Anzahl der maximal verfügbaren Worker Prozesse entweder durch max_parallel_maintenance_workers oder max_worker_processes begrenzt wird.

Parallel VACUUM als Standardoption

PARALLEL ist in der aktuellen Beta2 von PostgreSQL^® 13 als Standardoption aktiviert. Wird die Option weggelassen, richtet sich die Zahl der Worker nach der Anzahl an Indexe pro Tabelle. Ob ein Index überhaupt für die parallele Verarbeitung vorgesehen wird, entscheidet auch der Konfigurationsparameter min_parallel_index_scan_size. Die Standardeinstellung ist 512kB.

Der Administrator sollte bei der Konfiguration von PostgreSQL^® darauf achten, entsprechenden Spielraum bei der Parametrisierung von max_worker_processes einzuplanen, da mit dem seit PostgreSQL^® 11 verfügbaren parallelisierten CREATE INDEX in PostgreSQL^® 13 nun ein neues parallelisierbares DDL-Kommando hinzukommt.

Verbesserung bei der Geschwindigkeit

Das parallele Verarbeiten der Indexe sollte VACUUM gerade bei größeren Mengen an gelöschten Tupel bei entsprechender Anzahl an Indexen deutlich beschleunigen. Um diesen theoretischen Vorteil in der Praxis zu zeigen, verwenden wir an dieser Stelle ein Sample Dataset der IMDb Datenbank. Die Tabelle cast_info hat folgendes Schema:

                                Table "public.cast_info"
     Column     │  Type   │ Collation │ Nullable │                Default                
────────────────┼─────────┼───────────┼──────────┼───────────────────────────────────────
 id             │ integer │           │ not null │ nextval('cast_info_id_seq'::regclass)
 person_id      │ integer │           │ not null │ 
 movie_id       │ integer │           │ not null │ 
 person_role_id │ integer │           │          │ 
 note           │ text    │           │          │ 
 nr_order       │ integer │           │          │ 
 role_id        │ integer │           │ not null │ 
Indexes:
    "cast_info_pkey" PRIMARY KEY, btree (id)
    "cast_info_idx_cid" btree (person_role_id)
    "cast_info_idx_mid" btree (movie_id)
    "cast_info_idx_pid" btree (person_id)
    "test_idx" btree (note)
Foreign-key constraints:
    "movie_id_exists" FOREIGN KEY (movie_id) REFERENCES title(id)
    "person_id_exists" FOREIGN KEY (person_id) REFERENCES name(id)
    "person_role_id_exists" FOREIGN KEY (person_role_id) REFERENCES char_name(id)
    "role_id_exists" FOREIGN KEY (role_id) REFERENCES role_type(id)

Die Tabelle ist ca. 2759 MByte groß und verfügt über insgesamt fünf Indexe, deren Größen sich wie folgt manifestieren:

SELECT indexrelid::regclass, pg_size_pretty(pg_relation_size(indexrelid)) 
FROM pg_index WHERE indrelid = 'cast_info'::regclass;
    indexrelid     │ pg_size_pretty 
───────────────────┼────────────────
 cast_info_pkey    │ 1108 MB
 cast_info_idx_cid │ 421 MB
 cast_info_idx_mid │ 396 MB
 cast_info_idx_pid │ 452 MB
 test_idx          │ 388 MB
(5 rows)

Damit Autovacuum den Test nicht stört, wird er für die Tabelle deaktiviert:

ALTER TABLE cast_info SET(autovacuum_enabled = 'false');

Folgender SQL aktualisiert in der Tabelle ca. 1.05 Mio Tupel in dem es rein fiktiv die Rollenzuordnung von bestimmten Datensätzen ändert. Die Ausgabe ist ein wenig gekürzt, um das Beispiel besser zu veranschaulichen:

UPDATE cast_info SET role_id = 12 WHERE role_id = 6;
UPDATE 1055730

Anschließend wird der Parameter max_parallel_maintenance_workers auf 4 gesetzt, um maximal vier parallele Worker für VACUUM zu ermöglichen.

SET max_parallel_maintenance_workers TO 4;
VACUUM cast_info ;
Time: 6872,329 ms (00:06,872)

Wiederholt man den UPDATE mit umgekehrter Bedingung und anschließendem nicht-parallelisiertem VACUUM ergibt sich folgendes Bild:

UPDATE cast_info SET role_id = 6 WHERE role_id = 12;
UPDATE 1055730

VACUUM (PARALLEL 0) cast_info ;
Time: 45435,458 ms (00:45,435)

Die parallele Verarbeitung von Indexen in diesem Testfall zeigt einen mehr als deutlichen Vorteil zugunsten des parallelisierten VACUUM. Die folgende Grafik veranschaulicht die Laufzeiten noch einmal:

Fazit

Mit PostgreSQL^® 13 lernt VACUUM Indexe parallel zu verarbeiten. Dies sorgt für einen deutlichen Geschwindigkeitsvorteil bei der Wartung von Tabellen. Ein Wermutstropfen aktuell ist, dass der automatische Hintergrundprozess Autovacuum noch nicht von diesem Feature profitieren kann, nur manuelles VACUUM unterstützt aktuell parallele Verarbeitung. Administratoren, die beispielsweise jedoch große Batchverarbeitungen mit Tabellen mit vielen Indexen direkt im Anschluß mit manuellem VACUUM optimieren, können von parallel VACUUM direkt profitieren.

Bei Fragen rund um den Einsatz von PostgreSQL^® 13 parallel VACUUM stehen wir Ihnen natürlich gerne zur Verfügung. Sprechen Sie uns an! Unser PostgreSQL^® Competence Center ist bei Bedarf 24 Stunden am Tag, an 365 Tagen im Jahr für Sie verfügbar.

In schreibenden Workloads, die wenige oder gar keine mehrfach identischen Spaltenwerte enthalten, können unter Umständen kleinere Auswirkungen auf die Performance beobachtet werden. Die Deduplication Funktionalität kann beim Erzeugen eines Indexes mit dem Indexattribut deduplication_items abgeschaltet werden. In der aktuellen Beta 2 von PostgreSQL^® 13 ist Deduplication standardmässig aktiviert, was sich jedoch bis zum finalen Release noch ändern kann. Die folgende Syntax schaltet das Feature explizit beim Anlegen eines Index aus:

CREATE INDEX ON table (spalte) WITH(deduplicate_items=off);

Üblicherweise jedoch speichern Anwendungen in der Regel Spaltenwerte pro Spalte häufiger mehrfach ab. Ausnahme sind UNIQUE Constraints oder Primary Keys, aber selbst bei diesen kann Deduplication Vorteile bieten. Kommen mehrere Versionen einer Zeile mit dem spezifischen eindeutigen Spaltenwert vor, kann Deduplication unnötigen Bloat im Index ebenfalls verhindern.

Beispiel von Deduplication

Das Ergebnis der kompakteren Indexstruktur lässt sich mit nachfolgendem Beispiel illustrieren. Die Query in diesem Beispiel erzeugt eine Datenmenge aus 10.000.000 numerischen Werten, die zufällig per random() auf 1000 unterschiedliche Werte begrenzt wird. Dadurch ist die jeweilige Größe der mehrfach vorkommenden Spaltenwerte ungleich verteilt.

INSERT INTO unique_test
SELECT floor(generate_series(1, 10000000) % (random() * 1000)::numeric);

Vergleicht man in PostgreSQL^® 11.8, 12.3 und 13beta2 nun die Indexgrößen, ergibt sich folgendes Bild:

B-tree Index Größe mit Deduplication, Vergleich zwischen PostgreSQL<sup><p id=

® 11, 12 und 13″ width=“1024″ height=“576″ /> B-tree Index Größe mit Deduplication, Vergleich zwischen PostgreSQL^® 11, 12 und 13

Man sieht deutlich die Größenunterschiede, die sich im Vergleich von PostgreSQL^® 11 und 12 zu Version 13 ergeben. Deduplication reduziert die Indexgröße hier auf ca. 60 MByte, während bei PostgreSQL^® 11 und 12 ca. 220 MByte belegt werden. Die Grafik zeigt auch, dass die in der Einleitung beschriebene Optimierung in PostgreSQL^® 12 für diesen simulierten Fall nicht funktionieren. Allerdings werden diese im Laufe des Lebenszyklus des Index dann zum Tragen kommen.

Einschränkungen

Migrationen mit pg_upgrade

Ein wichtiger Punkt für die Nutzung von Deduplication in B-tree Indexen in PostgreSQL^® 13 ist bei Migrationen zu beachten. Verwendet man pg_upgrade, um eine bestehende PostgreSQL^® Instanz von beispielsweise PostgreSQL^® 12 auf 13 zu aktualisieren, werden alle physische Objekte in den neuen Cluster übernommen. Da Deduplication in PostgreSQL^® auf jeden Fall eine neue Indexstruktur verwendet, können bestehende Indexe aus dem alten Cluster daher nicht ohne weiteres die Deduplication Funktionalität verwenden und müssen neu angelegt werden. Dies kann beispielsweise mit dem PostgreSQL^® Kommando REINDEX oder dem äquivalenten Kommandozeilentool reindexdb erreicht werden. Dies gilt für Upgrades von allen älteren Major-Releases. Auch für Migrationen auf PostgreSQL^® 12 müssen B-tree Indexe, die von den dort angesprochenen Optimierungen profitieren möchten, neu angelegt werden. In PostgreSQL^® 13 hilft dabei auch die neue Option –jobs von reindexdb diesen Prozess zu beschleunigen, indem Indexe parallel mit der angebenen Anzahl an Prozessen aufgebaut werden können.

Verwendung bestimmer Datentypen

Der numerische Datentyp numeric kann nicht dedupliziert werden, da der Skalierungsfaktor eines jeden Spaltenwertes erhalten bleiben muss. B-tree Indexe auf Spalten dieses Typs werden daher automatisch nicht dedupliziert. Dasselbe gilt im Wesentlichen für den Datentyp jsonb. Da dieser intern numeric verwendet unterliegt jsonb denselben Einschränkungen. Bei dem Datentyp float4 gibt es unterschiedliche Repräsentationen des Wertes 0. Wie bei numeric mit dem entsprechenden scale factor muss bei float4 die jeweils unterschiedliche Repräsentation erhalten bleiben, was die Verwendung von Deduplizierung ausschließt. Spalten vom Typ text die eine Sortierreihenfolge verwenden die nicht-deterministisch ist können ebenfalls nicht dedupliziert werden.

Auch sogenannte Composite, also zusammengesetzte Datentypen, Arrays sowie Range Type verhindern die Nutzung der Funktion, allerdings hat dies keine formalen Gründe, sondern ist aktuell nicht implementiert. B-tree Indexe die mit der INCLUDE Direktive angelegt wurden, können ebenfalls aktuell keine Deduplizierung verwenden.