Verletzungen von Unique Constraints während des Einfügens verursachen Bloat in PostgreSQL

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.