Sperren in Postgres: 7 Tipps zum Arbeiten mit Sperren

Hallo nochmal! Am kommenden Dienstag startet ein neuer Stream zum Kurs „Relational DBMS“ , sodass wir weiterhin nützliches Material zu diesem Thema veröffentlichen. Lass uns gehen.



Letzte Woche habe ich über den Wettbewerbszugriff in Postgres geschrieben , welche Teams sich gegenseitig blockieren und wie Sie blockierte Teams diagnostizieren können. Natürlich müssen Sie nach der Diagnose möglicherweise behandelt werden. Mit Postgres können Sie sich in den Fuß schießen, aber Postgres bietet Ihnen auch Möglichkeiten, die Spitze nicht zu treffen. Im Folgenden finden Sie einige wichtige Tipps, wie Sie dies tun können und wie Sie dies nicht tun können. Dies hat sich als nützlich erwiesen, wenn Sie mit Benutzern zusammenarbeiten, wenn Sie von ihrer einzelnen Postgres-Datenbank zu Citus wechseln oder wenn Sie neue Analyseanwendungen in Echtzeit erstellen.

1. Fügen Sie niemals eine Spalte mit einem Standardwert hinzu

Goldene PostgreSQL-Regel: Geben Sie beim Hinzufügen einer Spalte zu einer Tabelle in einer Produktionsumgebung niemals einen Standardwert an .

Das Hinzufügen einer Spalte erfordert eine sehr aggressive Tabellensperre, die sowohl das Lesen als auch das Schreiben blockiert. Wenn Sie eine Spalte mit einem Standardwert hinzufügen, überschreibt PostgreSQL die gesamte Tabelle, um den Standardwert für jede Zeile einzugeben. Dies kann in großen Tabellen mehrere Stunden dauern. Gleichzeitig werden alle Anforderungen blockiert, sodass Ihre Datenbank nicht verfügbar ist.

Mach das nicht:

--     ,       (?) ALTER TABLE items ADD COLUMN last_update timestamptz DEFAULT now(); 

Mach es besser so:

 -- select, update, insert  delete ,      () ALTER TABLE items ADD COLUMN last_update timestamptz; -- select  insert ,  update  delete ,    UPDATE items SET last_update = now(); 

Oder, noch besser, vermeiden Sie das Aktualisieren und delete Sperren für längere Zeit, indem Sie in kleinen Stapeln aktualisieren, zum Beispiel:

 do { numRowsUpdated = executeUpdate( "UPDATE items SET last_update = ? " + "WHERE ctid IN (SELECT ctid FROM items WHERE last_update IS NULL LIMIT 5000)", now); } while (numRowsUpdate > 0); 

Auf diese Weise können Sie eine neue Spalte hinzufügen und füllen, ohne Ihre Benutzer zu beeinträchtigen.

2. Achten Sie auf Sperrwarteschlangen und verwenden Sie Zeitüberschreitungen

Jede Sperre in PostgreSQL hat eine Priorität. Wenn Transaktion B versucht, eine Sperre zu ergreifen, die bereits von Transaktion A mit einer widersprüchlichen Sperrstufe gehalten wird, wartet Transaktion B in der Sperrwarteschlange. Jetzt passiert etwas Interessantes: Wenn eine andere Transaktion C eintrifft, muss sie nicht nur den Konflikt mit A, sondern auch mit Transaktion B und jeder anderen Transaktion in der Sperrwarteschlange überprüfen.

Dies bedeutet, dass Ihr DDL-Befehl, selbst wenn er sehr schnell ausgeführt werden kann, lange Zeit in der Warteschlange stehen kann und darauf wartet, dass die Anforderungen abgeschlossen werden. Die danach ausgeführten Anforderungen werden dahinter blockiert .

Wenn Sie möglicherweise auf lange SELECT Abfragen für eine Tabelle stoßen, gehen Sie nicht wie folgt vor:

 ALTER TABLE items ADD COLUMN last_update timestamptz; 

Mach das besser:

 SET lock_timeout TO '2s' ALTER TABLE items ADD COLUMN last_update timestamptz; 

Wenn lock_timeout gesetzt ist lock_timeout DDL-Befehl nicht ausgeführt, wenn er auf eine Sperre wartet, und blockiert daher Anforderungen für mehr als 2 Sekunden. Der Nachteil ist, dass Ihre ALTER TABLE möglicherweise nicht ausgeführt wird, Sie es jedoch später erneut versuchen können. Sie können pg_stat_activity abfragen, um festzustellen , ob Sie lange Abfragen haben, bevor Sie den DDL-Befehl ausführen .

3. Verwenden Sie die nicht blockierende Indexerstellung

Eine weitere goldene Regel von PostgreSQL: Verwenden Sie immer die nicht blockierende Indexerstellung.
Das Erstellen eines Index für ein großes Dataset kann Stunden oder sogar Tage dauern, und der reguläre Befehl CREATE INDEX sperrt alle Datensätze für die Dauer des Befehls. Trotz der Tatsache, dass SELECTs nicht blockiert werden, ist es immer noch ziemlich schlecht, und es gibt einen besseren Weg: CREATE INDEX CONCURRENTLY .

Mach das nicht:

 --    CREATE INDEX items_value_idx ON items USING GIN (value jsonb_path_ops); 

Tun Sie stattdessen Folgendes:

 --    DDL CREATE INDEX CONCURRENTLY items_value_idx ON items USING GIN (value jsonb_path_ops); 

Die nicht blockierende Indexerstellung hat einen Nachteil. Wenn etwas schief geht, wird es nicht zurückgesetzt und hinterlässt einen unvollständigen ("ungültigen") Index. Wenn dies passiert, mach dir keine Sorgen, lauf einfach

 DROP INDEX CONCURRENTLY items_value_idx 

und versuchen Sie es erneut zu erstellen.

4. Verwenden Sie aggressive Schlösser so spät wie möglich

Wenn Sie einen Befehl ausführen müssen, der aggressive Tabellensperren empfängt, versuchen Sie dies so spät wie möglich in der Transaktion, damit die Abfragen so lange wie möglich fortgesetzt werden können.

Zum Beispiel, wenn Sie den Inhalt der Tabelle vollständig ersetzen möchten. Mach das nicht:

 BEGIN; --     : TRUNCATE items; -  : \COPY items FROM 'newdata.csv' WITH CSV COMMIT; 

Laden Sie stattdessen die Daten in eine neue Tabelle und ersetzen Sie die alte:

 BEGIN; CREATE TABLE items_new (LIKE items INCLUDING ALL); --  : \COPY items_new FROM 'newdata.csv' WITH CSV --     : DROP TABLE items; ALTER TABLE items_new RENAME TO items; COMMIT; 

Es gibt ein Problem: Wir haben Datensätze nicht von Anfang an blockiert, und die alte Elementtabelle könnte sich zum Zeitpunkt des Zurücksetzens geändert haben. Um dies zu verhindern, können wir die Tabelle explizit zum Schreiben sperren, aber nicht zum Lesen:

 BEGIN; LOCK items IN EXCLUSIVE MODE; ... 

Manchmal ist es besser, die Blockierung selbst in die Hand zu nehmen.

5. Hinzufügen eines Primärschlüssels mit minimaler Blockierung

Das Hinzufügen eines Primärschlüssels zu Ihren Tabellen ist häufig eine gute Idee. Wenn Sie beispielsweise die logische Replikation verwenden oder eine Datenbank mit Citus Warp migrieren möchten.

Mit Postgres ist es sehr einfach, einen Primärschlüssel mit ALTER TABLE zu erstellen. ALTER TABLE wird jedoch ein Index für den Primärschlüssel erstellt. Dies kann viel Zeit in Anspruch nehmen, wenn die Tabelle groß ist. Alle Anforderungen werden blockiert.

 ALTER TABLE items ADD PRIMARY KEY (id); --      

Glücklicherweise können Sie die ganze harte Arbeit zuerst mit CREATE UNIQUE INDEX CONCURRENTLY und dann den eindeutigen Index als Primärschlüssel verwenden, was eine schnelle Operation ist.

 CREATE UNIQUE INDEX CONCURRENTLY items_pk ON items (id); --   ,     ALTER TABLE items ADD CONSTRAINT items_pk PRIMARY KEY USING INDEX items_pk; --  ,   

Die Aufteilung der Erstellung des Primärschlüssels in zwei Stufen hat für den Benutzer praktisch keine Auswirkungen.

6. Verwenden Sie niemals VACUUM FULL

Benutzererfahrung Postgres kann manchmal nur ein wenig genial sein. Obwohl VACUUM FULL klingt, wie Sie es tun möchten, um den "Staub" Ihrer Datenbank zu VACUUM FULL , wäre ein geeigneterer Befehl:

 PLEASE FREEZE MY DATABASE FOR HOURS; 

VACUUM FULL überschreibt die gesamte Tabelle auf die Festplatte, was Stunden oder Tage dauern kann, und blockiert gleichzeitig alle Anforderungen. Es gibt zwar mehrere gültige Anwendungsfälle für VACUUM FULL , z. B. eine Tabelle, die früher groß war, jetzt aber klein ist und immer noch viel Platz VACUUM FULL , aber dies ist wahrscheinlich nicht Ihre Option.
Obwohl Sie sich bemühen sollten, Optionen für die automatische Bereinigung zu konfigurieren und Indizes zu verwenden, um Abfragen zu beschleunigen, können Sie manchmal VACUUM , jedoch NICHT VACUUM FULL .

7. Vermeiden Sie Deadlocks, indem Sie Befehle anordnen

Wenn Sie PostgreSQL seit einiger Zeit verwenden, haben Sie höchstwahrscheinlich Fehler gesehen wie:

 ERROR: deadlock detected DETAIL: Process 13661 waits for ShareLock on transaction 45942; blocked by process 13483. Process 13483 waits for ShareLock on transaction 45937; blocked by process 13661. 

Dies geschieht, wenn gleichzeitige Transaktionen dieselben Sperren in einer anderen Reihenfolge ausführen. Beispielsweise führt eine Transaktion die folgenden Befehle aus.

 BEGIN; UPDATE items SET counter = counter + 1 WHERE key = 'hello'; --    hello UPDATE items SET counter = counter + 1 WHERE key = 'world'; --    world END; 

Gleichzeitig kann eine andere Transaktion dieselben Befehle ausgeben, jedoch in einer anderen Reihenfolge.

 BEGIN UPDATE items SET counter = counter + 1 WHERE key = 'world'; --    world UPDATE items SET counter = counter + 1 WHERE key = 'hello'; --    hello END; 

Wenn diese Transaktionsblöcke gleichzeitig ausgeführt werden, ist es wahrscheinlich, dass sie nicht mehr aufeinander warten und niemals enden. Postgres erkennt diese Situation in etwa einer Sekunde und storniert eine der Transaktionen, um die andere abzuschließen. In diesem Fall sollten Sie sich Ihre Anwendung ansehen, um herauszufinden, ob Sie sicherstellen können, dass Ihre Transaktionen immer in derselben Reihenfolge ausgeführt werden. Wenn beide Transaktionen zuerst hello , dann world ändern, sperrt die erste Transaktion die zweite auf hello bevor andere Sperren erfasst werden können.
Teilen Sie Ihre Tipps!

Wir hoffen, dass Sie diese Empfehlungen hilfreich finden. Wenn Sie weitere Tipps haben, können Sie @citusdata oder unsere aktive Citus-Benutzergemeinschaft auf Slack twittern.

Wir erinnern Sie daran, dass es in wenigen Stunden einen Tag der offenen Tür geben wird, an dem wir ausführlich über das Programm für den kommenden Kurs sprechen werden.