Najlepszy sposób na wypełnienie nowej kolumny w dużej tabeli?

W Postgres mamy tabelę 2,2 GB z 7 801 611 wierszami. Dodajemy do niej kolumnę uuid / guid i zastanawiam się, jaki jest najlepszy sposób na wypełnienie tej kolumny (ponieważ chcemy dodać NOT NULLdo niej ograniczenie).

Jeśli dobrze rozumiem Postgres, aktualizacja jest technicznie usunięciem i wstawieniem, więc zasadniczo przebudowuje całą tabelę 2,2 GB. Mamy też działającego niewolnika, więc nie chcemy, aby pozostawało w tyle.

Czy jest coś lepszego niż pisanie skryptu, który powoli wypełnia go z czasem?

To bardzo zależy od szczegółów twoich wymagań.

Jeśli masz wystarczającą ilość wolnego miejsca (co najmniej 110% pg_size_pretty((pg_total_relation_size(tbl))) na dysku i możesz sobie pozwolić na blokadę udostępniania przez pewien czas i blokadę wyłączności na bardzo krótki czas , to utwórz nową tabelę zawierającą uuidkolumnę za pomocą CREATE TABLE AS. Czemu?

• Co powoduje spowolnienie dużego INSERT i wybuch dysku?

Poniższy kod wykorzystuje funkcję z dodatkowego uuid-ossmodułu .

• Zablokuj tabelę przed jednoczesnymi zmianami w SHAREtrybie (nadal pozwalając na jednoczesne odczyty). Próby zapisu do tabeli będą czekać i ostatecznie zakończą się niepowodzeniem. Patrz poniżej.

• Skopiuj całą tabelę podczas wypełniania nowej kolumny w locie - możliwe, że porządnie porządkuje rzędy, będąc przy niej.
  Jeśli zamierzasz zmienić kolejność wierszy, pamiętaj, aby ustawić work_memmożliwie najwyższą wartość (tylko na sesję, a nie globalnie).

• Następnie dodaj ograniczenia, klucze obce, indeksy, wyzwalacze itp. Do nowej tabeli. Podczas aktualizacji dużych części tabeli tworzenie indeksów od zera jest znacznie szybsze niż iteracyjne dodawanie wierszy.

• Gdy nowy stół będzie gotowy, upuść stary i zmień jego nazwę, aby zastąpić go nowym. Tylko ten ostatni krok zyskuje wyłączną blokadę na starym stole do końca transakcji - która powinna być teraz bardzo krótka.
  Wymaga to również usunięcia dowolnego obiektu w zależności od typu tabeli (widoki, funkcje wykorzystujące typ tabeli w podpisie, ...), a następnie odtworzenia ich później.

• Zrób to wszystko w jednej transakcji, aby uniknąć niekompletnych stanów.

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;

To powinno być najszybsze. Każda inna metoda aktualizacji musi również przepisać cały stół, tylko w droższy sposób. Wybrałbyś tę trasę tylko wtedy, gdy nie masz wystarczającej ilości wolnego miejsca na dysku lub nie możesz sobie pozwolić na zablokowanie całej tabeli lub wygenerowanie błędów dla równoczesnych prób zapisu.

Co dzieje się z jednoczesnymi zapisami?

Inna transakcja (w innych sesjach), która próbuje INSERT/ UPDATE/ DELETEw tej samej tabeli po tym, jak transakcja przejęła SHAREblokadę, będzie czekać na zwolnienie blokady lub przekroczenie limitu czasu, zależnie od tego, co nastąpi wcześniej. I tak się nie powiedzie , ponieważ tabela, do której próbowali pisać, została z nich usunięta.

Nowa tabela ma nowy identyfikator OID tabeli, ale współbieżna transakcja już rozwiązała nazwę tabeli na identyfikator OID poprzedniej tabeli . Kiedy blokada jest w końcu zwolniona, próbują sami zablokować stół przed napisaniem do niego i stwierdzają, że zniknął. Postgres odpowie:

ERROR: could not open relation with OID 123456

Gdzie 123456jest OID starej tabeli. Musisz złapać ten wyjątek i ponowić zapytania w kodzie aplikacji, aby go uniknąć.

Jeśli nie możesz sobie na to pozwolić, musisz zachować swój oryginalny stół.

Dwie alternatywy utrzymujące istniejący stół

1. Zaktualizuj na miejscu (możliwe, że aktualizację uruchamiasz jednocześnie na małych segmentach) przed dodaniem NOT NULLograniczenia. Dodanie nowej kolumny z wartościami NULL i bez NOT NULLograniczeń jest tanie.
   Od wersji Postgres 9.2 możesz również utworzyć CHECKograniczenie za pomocąNOT VALID :

   Ograniczenie będzie nadal egzekwowane względem kolejnych wstawek lub aktualizacji

   Umożliwia to aktualizację wierszy peu à peu - w wielu osobnych transakcjach . Pozwala to uniknąć zbyt długiego blokowania wierszy, a także umożliwia ponowne użycie martwych wierszy. (Będziesz musiał uruchomić VACUUMręcznie, jeśli nie ma wystarczającej ilości czasu pomiędzy uruchomieniem autovacuum.) Na koniec dodaj NOT NULLograniczenie i usuń NOT VALID CHECKograniczenie:

   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;

   Powiązana odpowiedź omawiająca NOT VALIDbardziej szczegółowo:

   • Wyłącz wszystkie ograniczenia i kontrole tabeli podczas przywracania zrzutu

2. Przygotuj nowy stan w tabeli tymczasowej , TRUNCATEoryginalnej i uzupełnij z tabeli tymczasowej. Wszystko w jednej transakcji . Nadal musisz SHAREzablokować się przed przygotowaniem nowego stołu, aby zapobiec utracie równoczesnych zapisów.

   Szczegóły w powiązanej odpowiedzi dotyczącej SO:

   • Najlepszy sposób na usunięcie milionów wierszy według identyfikatora
   • Dodać nową kolumnę bez blokady tabeli?

Nie mam „najlepszej” odpowiedzi, ale mam „najmniej złą” odpowiedź, która może pozwolić ci zrobić wszystko dość szybko.

Moja tabela miała 2MM wiersze, a wydajność aktualizacji była chugująca, gdy próbowałem dodać dodatkową kolumnę znacznika czasu, która domyślnie była ustawiona na pierwszą.

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

Po zawieszeniu przez 40 minut spróbowałem tego na małej partii, aby dowiedzieć się, ile to może potrwać - prognoza wynosiła około 8 godzin.

Akceptowana odpowiedź jest zdecydowanie lepsza - ale ta tabela jest bardzo używana w mojej bazie danych. Jest tam kilkadziesiąt tabel, które FKEY na nim; Chciałem uniknąć przełączania KLUCZY ZAGRANICZNYCH na tak wielu stołach. A potem są poglądy.

Trochę przeszukiwania dokumentów, studiów przypadków i StackOverflow, a ja miałem „A-Ha!” za chwilę. Odpływ nie dotyczył podstawowej aktualizacji, ale wszystkich operacji INDEX. Moja tabela miała 12 indeksów - kilka dla unikalnych ograniczeń, kilka dla przyspieszenia planowania zapytań i kilka dla wyszukiwania pełnotekstowego.

Każdy wiersz, który został zaktualizowany, nie tylko działał na DELETE / INSERT, ale także narzut związany ze zmianą każdego indeksu i sprawdzaniem ograniczeń.

Moim rozwiązaniem było usunięcie każdego indeksu i ograniczenia, zaktualizowanie tabeli, a następnie dodanie wszystkich indeksów / ograniczeń z powrotem.

Napisanie transakcji SQL, która wykonała następujące czynności, zajęło około 3 minut:

• ZACZYNAĆ;
• porzucone indeksy / constaints
• aktualizacja tabeli
• ponownie dodaj indeksy / ograniczenia
• POPEŁNIĆ;

Uruchomienie skryptu zajęło 7 minut.

Przyjęta odpowiedź jest zdecydowanie lepsza i bardziej właściwa ... i praktycznie eliminuje potrzebę przestojów. W moim przypadku użycie tego rozwiązania wymagałoby znacznie więcej pracy „programisty”, a my mieliśmy 30-minutowy okres planowego przestoju, w którym można go zrealizować. Nasze rozwiązanie rozwiązało to w 10.