6 lipca, 2020
Articles
Brak komentarzy

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

Mamy tabelę 2,2 GB w Postgresie z 7 801 611 wierszami. Dodajemy do niej kolumnę uuid / guid i zastanawiam się, jaki jest najlepszy sposób zapełnienia tej kolumny (ponieważ chcemy dodać do niej ograniczenie NOT NULL).

Jeśli dobrze rozumiem Postgres, aktualizacja jest technicznie usuwaniem i wstawianiem, więc jest to zasadniczo przebudowa całej tabeli 2,2 GB. Mamy również działającego slavea, więc nie chcemy, aby pozostawał w tyle.

Czy jest coś lepszego niż napisanie skryptu, który powoli zapełnia go w czasie?

Komentarze

  • Czy uruchomiłeś już ALTER TABLE .. ADD COLUMN ..., czy też ta część również wymaga odpowiedzi?
  • Nie uruchomiłeś wszelkie modyfikacje tabeli jeszcze na etapie planowania. Zrobiłem to już wcześniej, dodając kolumnę, wypełniając ją, a następnie dodając ograniczenie lub indeks. Jednak ta tabela jest znacznie większa i martwię się o obciążenie, blokowanie, replikację itp. …

Odpowiedź

W dużej mierze zależy to od szczegółów konfiguracji i wymagań.

Zwróć uwagę, że od Postgres 11 dodaje się tylko kolumnę z volatile DEFAULT nadal wyzwala przepisywanie tabeli . Niestety tak jest w Twoim przypadku.

Jeśli masz wystarczającą ilość wolnego miejsca na dysku – co najmniej 110% z pg_size_pretty((pg_total_relation_size(tbl)) – i stać go na blokadę udziału przez jakiś czas i wyłączną blokadę na bardzo krótki czas, a następnie utwórz nowa tabela zawierająca kolumnę uuid przy użyciu CREATE TABLE AS . Dlaczego?

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

  • Zablokuj tabelę przed równoczesnymi zmianami w trybie SHARE (nadal zezwalając na jednoczesne odczyty). Próby zapisu do tabeli będą czekać i ostatecznie zakończyć się niepowodzeniem. Zobacz poniżej.

  • Skopiuj całą tabelę, zapełniając nową kolumnę w locie – prawdopodobnie porządkując wiersze, będąc przy niej.
    Jeśli zamierzasz zmienić kolejność wierszy, pamiętaj, aby ustawić work_mem wystarczająco wysoko, aby wykonać sortowanie w pamięci RAM lub tak wysoko na ile możesz sobie pozwolić (tylko dla Twojej sesji, a nie globalnie).

  • Następnie dodaj ograniczenia, klucze obce, indeksy, wyzwalacze itp. do nowego stół. Podczas aktualizowania dużych części tabeli tworzenie indeksów od podstaw jest dużo szybsze niż iteracyjne dodawanie wierszy. Powiązane porady w instrukcji.

  • Gdy nowa tabela będzie gotowa, usuń starą i zmień nazwę nowej aby stał się zamiennikiem typu drop-in. Dopiero ten ostatni krok powoduje uzyskanie blokady na wyłączność starej tabeli na resztę transakcji – która teraz powinna być bardzo krótka.
    Wymaga również usunięcia dowolnego obiektu w zależności od typu tabeli (widoki, funkcje korzystające z typu tabeli w podpisie, …) i odtwórz je 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;

Powinno to nastąpić najszybciej. Każda inna metoda aktualizacji musi również przepisać całą tabelę, tylko w droższy sposób. Możesz iść 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 generowanie błędów przy jednoczesnych próbach zapisu.

Co dzieje się z równoczesnymi zapisami?

Inna transakcja (w innych sesjach) próbująca INSERT / UPDATE / DELETE w tej samej tabeli po przyjęciu przez transakcję blokady SHARE, będzie czekać na zwolnienie blokady lub przekroczenie limitu czasu, w zależności od tego, co nastąpi wcześniej. Będą one niepowodzenie w obu przypadkach, ponieważ tabela, do której próbowali pisać, została usunięta spod nich.

Nowa tabela ma nowy tabeli OID, ale współbieżna transakcja już ustaliła nazwę tabeli na OID poprzedniej tabeli . Kiedy blokada zostanie ostatecznie zwolniona, próbują sami zablokować tabelę przed zapisaniem do niej i stwierdzą, że to jest ” zniknęło.Postgres odpowie:

ERROR: could not open relation with OID 123456

Gdzie 123456 to OID starej tabeli. Musisz złapać ten wyjątek i ponawiać zapytania w kodzie aplikacji, aby tego uniknąć.

Jeśli nie możesz sobie na to pozwolić, musisz zachować oryginalną tabelę.

Utrzymanie istniejącej tabeli, alternatywa 1

Aktualizacja na miejscu (prawdopodobnie uruchamianie aktualizacji na małych segmentach naraz) przed dodaniem ograniczenia NOT NULL . Dodanie nowej kolumny z wartościami NULL i bez ograniczenia NOT NULL jest tanie.
Ponieważ Postgres 9.2 możesz także utworzyć CHECK z NOT VALID :

Ograniczenie będzie nadal być wymuszane przy kolejnych wstawieniach lub aktualizacjach

To pozwala aktualizować wiersze peu à peu – w wielu oddzielnych transakcjach . Pozwala to uniknąć zbyt długiego utrzymywania blokad rzędów, a także umożliwia ponowne wykorzystanie martwych rzędów. (Będziesz musiał uruchomić VACUUM ręcznie, jeśli nie ma wystarczająco dużo czasu na rozpoczęcie automatycznego odkurzania). Na koniec dodaj NOT NULL ograniczenie i usuń ograniczenie NOT VALID CHECK: 

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ź dotycząca NOT VALID bardziej szczegółowo:

Zachowanie istniejącej tabeli, alternatywa 2

Przygotuj nowy stan w tabeli tymczasowej , TRUNCATE oryginał i uzupełnij z tabeli tymczasowej. Wszystko w jednej transakcji . nadal trzeba wykonać SHARE lock przed pr tworzenie nowej tabeli, aby zapobiec utracie równoczesnych zapisów.

Szczegóły w tej powiązanej odpowiedzi na SO:

Komentarze

  • Fantastyczna odpowiedź! Dokładnie informacje, których szukałem. Dwa pytania 1. Czy masz pomysł na łatwy sposób sprawdzenia, jak długo zajęłoby takie działanie? 2. Jeśli zajmie to, powiedzmy, 5 minut, co stanie się z akcjami próbującymi zaktualizować wiersz w tej tabeli w ciągu tych 5 minut?
  • @CollinPeters: 1. Lew ' s czasu zajęłoby skopiowanie dużego stołu – i prawdopodobnie odtworzenie indeksów i ograniczeń (to zależy). Usuwanie i zmiana nazwy jest tania. Aby przetestować, możesz uruchomić przygotowany skrypt SQL bez LOCK do DROP włącznie. Mogłem tylko wypowiadać dzikie i bezużyteczne domysły. Jeśli chodzi o 2., proszę rozważyć dodatek do mojej odpowiedzi.
  • @ErwinBrandstetter Kontynuuj odtwarzanie widoków, więc jeśli mam kilkanaście widoków, które nadal używają starej tabeli (oid) po zmianie nazwy tabeli. Czy jest jakiś sposób na wykonanie głębokiego zamiany zamiast ponownego uruchomienia odświeżania / tworzenia całego widoku?
  • @CodeFarmer: Jeśli zmienisz nazwę tabeli, widoki będą nadal działać z tabelą o zmienionej nazwie. Aby zamiast tego widoki korzystały z tabeli nowa , należy je odtworzyć na podstawie nowej tabeli. (Również po to, aby umożliwić usunięcie starej tabeli.) Nie ma (praktycznego) sposobu na obejście tego.
  • ponieważ postgres 9.2 nie ' nie rzuca could not open relation with OID 123456

Odpowiedź

Nie mam„ najlepszej ”odpowiedzi, ale Mam „najmniej złą” odpowiedź, która może pozwolić ci załatwić sprawę w miarę szybko.

Moja tabela miała 2 miliony wierszy, a wydajność aktualizacji była dławiąca, gdy próbowałem dodać dodatkową kolumnę z datownikiem, która domyślnie była pierwsza . 

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

Po 40 minutach zawieszenia próbowałem to na małej partii, aby zorientować się, jak długo to może potrwać – prognoza była około 8 godzin.

Zaakceptowana odpowiedź jest zdecydowanie lepsza – ale ta tabela jest intensywnie wykorzystywana w mojej bazie danych. Jest kilkadziesiąt tabel, na których została umieszczona FKEY; Chciałem uniknąć przełączania KLAWISZY OBCYCH na tak wielu tabelach . A potem są poglądy.

Trochę przeszukiwania dokumentów, studiów przypadków i StackOverflow i miałem moment „A-Ha!”. nie było w rdzeniu UPDATE, ale we wszystkich operacjach INDEX. Moja tabela zawierała 12 indeksów – kilka dla unikalnych ograniczeń, kilka dla przyspieszenia planowania zapytań i kilka dla wyszukiwania pełnotekstowego.

Każdy ZAKTUALIZOWANY wiersz nie pracował tylko nad DELETE / INSERT, ale także kosztem zmiany każdego indeksu i sprawdzania ograniczeń.

Moim rozwiązaniem było porzucenie każdego indeksu i ograniczenie, zaktualizuj tabelę, a następnie dodaj z powrotem wszystkie indeksy / ograniczenia.

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

  • BEGIN;
  • porzucono indeksy / ograniczenia
  • aktualizuj tabelę
  • ponownie dodaj indeksy / ograniczenia
  • COMMIT;

Uruchomienie skryptu zajęło 7 minut.

Zaakceptowana odpowiedź jest zdecydowanie lepsza i bardziej poprawna … i praktycznie eliminuje potrzebę przestojów. Jednak w moim przypadku zajęłoby to znacznie więcej ” Deweloper „pracował nad użyciem tego rozwiązania i mieliśmy 30 minut zaplanowanego przestoju, w którym można go było wykonać. Nasze rozwiązanie rozwiązało problem w 10.

Komentarze

  • Nie wspominając o tym, że umożliwia jednoczesne zapisy. Równoczesne odczyty mogą być powolne bez indexe s chociaż 🙂 Byłoby interesujące porównanie go z innymi opcjami …

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *