Der beste Weg, um eine neue Spalte in eine große Tabelle zu füllen?

Es hängt sehr stark von Details Ihres Setups und Ihrer Anforderungen ab.

Beachten Sie, dass seit Postgres 11 nur eine -Spalte mit einer flüchtigen DEFAULT löst immer noch eine Tabellenumschreibung aus . Leider ist dies Ihr Fall.

Wenn Sie haben ausreichend freien Speicherplatz auf der Festplatte – mindestens 110% von pg_size_pretty((pg_total_relation_size(tbl)) – und kann sich eine Freigabesperre für einige Zeit und eine exklusive Sperre für eine sehr kurze Zeit, dann erstellen Sie eine neue Tabelle einschließlich der Spalte uuid Verwenden von CREATE TABLE AS . Warum?

• Was führt dazu, dass großes INSERT langsamer wird und die Festplattennutzung explodiert?

Der folgende Code verwendet eine -Funktion aus dem zusätzlichen uuid-oss -Modul .

• Sperren Sie die Tabelle gegen gleichzeitige Änderungen im SHARE -Modus (wobei weiterhin gleichzeitige Lesevorgänge zulässig sind). Versuche, in die Tabelle zu schreiben, warten und schlagen schließlich fehl. Siehe unten.

• Kopieren Sie die gesamte Tabelle, während Sie die neue Spalte im laufenden Betrieb füllen – und ordnen Sie möglicherweise Zeilen günstig an, wenn Sie gerade dabei sind.
  If Wenn Sie Zeilen neu anordnen möchten, stellen Sie sicher, dass work_mem hoch genug ist, um die Sortierung im RAM oder so hoch durchzuführen wie Sie es sich leisten können (nur für Ihre Sitzung, nicht global).

• Fügen Sie dann Einschränkungen, Fremdschlüssel, Indizes, Trigger usw. zu den neuen hinzu Tabelle. Wenn Sie große Teile einer Tabelle aktualisieren, ist es viel schneller, Indizes von Grund auf neu zu erstellen, als Zeilen iterativ hinzuzufügen. Zugehörige Hinweise im Handbuch.

• Wenn die neue Tabelle fertig ist, löschen Sie die alte und benennen Sie die neue um um es zu einem Ersatz zu machen. Nur dieser letzte Schritt erhält eine exklusive Sperre für die alte Tabelle für den Rest der Transaktion – die jetzt sehr kurz sein sollte.
  Außerdem müssen Sie jedes Objekt abhängig vom Tabellentyp löschen (Ansichten, Funktionen, die den Tabellentyp verwenden in der Signatur, …) und erstellen Sie sie anschließend neu.

• Führen Sie alles in einer Transaktion aus, um unvollständige Zustände zu vermeiden.

BEGIN; LOCK TABLE tbl IN SHARE MODE; SET LOCAL work_mem = "???? MB"; -- just for this transaction CREATE TABLE tbl_new AS SELECT uuid_generate_v1() AS tbl_uuid, <list of all columns in order> FROM tbl ORDER BY ??; -- optionally order rows favorably while being at it. ALTER TABLE tbl_new ALTER COLUMN tbl_uuid SET NOT NULL , ALTER COLUMN tbl_uuid SET DEFAULT uuid_generate_v1() , ADD CONSTRAINT tbl_uuid_uni UNIQUE(tbl_uuid); -- more constraints, indices, triggers? DROP TABLE tbl; ALTER TABLE tbl_new RENAME tbl; -- recreate views etc. if any COMMIT; 

Dies sollte am schnellsten sein. Bei jeder anderen Aktualisierungsmethode muss auch die gesamte Tabelle neu geschrieben werden, nur auf teurere Weise. Sie würden diesen Weg nur gehen, wenn Sie nicht genügend freien Speicherplatz auf der Festplatte haben oder es sich nicht leisten können, die gesamte Tabelle zu sperren oder Fehler bei gleichzeitigen Schreibversuchen zu generieren.

Was passiert mit gleichzeitigen Schreibvorgängen?

Andere Transaktion (in anderen Sitzungen), die versucht, INSERT / UPDATE / DELETE Warten Sie in derselben Tabelle, nachdem Ihre Transaktion die Sperre SHARE ausgeführt hat, bis die Sperre aufgehoben wird oder eine Zeitüberschreitung eintritt, je nachdem, was zuerst eintritt. Sie werden fail so oder so, da die Tabelle, in die sie schreiben wollten, unter ihnen gelöscht wurde.

Die neue Tabelle hat eine neue Tabellen-OID, aber gleichzeitige Transaktionen haben den Tabellennamen bereits in die OID der vorherigen Tabelle aufgelöst. Wenn die Sperre endgültig aufgehoben wird, versuchen sie, die Tabelle selbst zu sperren, bevor sie darauf schreiben und feststellen, dass sie “ s weg.Postgres antwortet:

ERROR: could not open relation with OID 123456

Wobei 123456 die OID der alten Tabelle ist. Sie müssen diese Ausnahme abfangen und Abfragen in Ihrem App-Code wiederholen, um dies zu vermeiden.

Wenn Sie sich das nicht leisten können, müssen Sie Ihre ursprüngliche Tabelle behalten. P. >

ALTER TABLE tbl ADD CONSTRAINT tbl_no_null CHECK (tbl_uuid IS NOT NULL) NOT VALID; -- update rows in multiple batches in separate transactions -- possibly run VACUUM between transactions ALTER TABLE tbl ALTER COLUMN tbl_uuid SET NOT NULL; ALTER TABLE tbl ALTER DROP CONSTRAINT tbl_no_null; 

Ich habe keine“ beste „Antwort, aber Ich habe eine „am wenigsten schlechte“ Antwort, mit der Sie die Dinge relativ schnell erledigen können.

Meine Tabelle hatte 2 MM Zeilen und die Aktualisierungsleistung war fehlerhaft, als ich versuchte, eine sekundäre Zeitstempelspalte hinzuzufügen, die standardmäßig die erste war .

ALTER TABLE mytable ADD new_timestamp TIMESTAMP ; UPDATE mytable SET new_timestamp = old_timestamp ; ALTER TABLE mytable ALTER new_timestamp SET NOT NULL ; 

Nachdem es 40 Minuten lang hängen geblieben war, habe ich es an einer kleinen Charge versucht, um eine Vorstellung davon zu bekommen, wie lange dies dauern könnte – die Prognose war da 8 Stunden.

Die akzeptierte Antwort ist definitiv besser – aber diese Tabelle wird in meiner Datenbank häufig verwendet. Es gibt ein paar Dutzend Tabellen, auf denen FKEY basiert. Ich wollte vermeiden, dass FOREIGN KEYS auf so vielen Tabellen umgeschaltet wird Und dann gibt es Ansichten.

Ein bisschen nach Dokumenten, Fallstudien und StackOverflow suchen, und ich hatte den „A-Ha!“ – Moment. Der Abfluss war nicht auf dem Kern-UPDATE, sondern auf allen INDEX-Operationen. Meine Tabelle enthielt 12 Indizes – einige für eindeutige Einschränkungen, einige für die Beschleunigung des Abfrageplaners und einige für die Volltextsuche.

Jede Zeile, die AKTUALISIERT wurde, arbeitete nicht nur an einem DELETE / INSERT, sondern auch an dem Aufwand, jeden Index zu ändern und Einschränkungen zu überprüfen.

Meine Lösung bestand darin, jeden Index und zu löschen Einschränkung, aktualisieren Sie die Tabelle und fügen Sie dann alle Indizes / Einschränkungen wieder hinzu.

Das Schreiben einer SQL-Transaktion mit den folgenden Aktionen dauerte ca. 3 Minuten:

• BEGIN;
• Indizes / Konstanten gelöscht
• Tabelle aktualisieren
• Indizes / Einschränkungen erneut hinzufügen
• COMMIT;

Die Ausführung des Skripts dauerte 7 Minuten.

Die akzeptierte Antwort ist definitiv besser und korrekter … und macht Ausfallzeiten praktisch überflüssig. In meinem Fall hätte es jedoch erheblich mehr gedauert. “ Entwickler „arbeiten daran, diese Lösung zu verwenden, und wir hatten ein 30-minütiges Fenster mit geplanten Ausfallzeiten, in denen sie ausgeführt werden konnten. Unsere Lösung hat sie in 10 behoben.

Kommentare

• Ganz zu schweigen davon, dass gleichzeitige Schreibvorgänge möglich sind. Gleichzeitige Lesevorgänge können ohne Index langsam sein s obwohl 🙂 Wäre interessant, es mit den anderen Optionen zu vergleichen …