Bardzo powolne usuwanie w PostgreSQL, obejście?

Mam bazę danych PostgreSQL 9.2, która ma główny schemat z około 70 tabelami i zmienną liczbą identycznie ustrukturyzowanych schematów dla każdego klienta po 30 tabel. Schematy klienta mają klucze obce odnoszące się do głównego schematu, a nie na odwrót.

Właśnie zacząłem wypełniać bazę danych prawdziwymi danymi zaczerpniętymi z poprzedniej wersji. Baza danych osiągnęła około 1,5 GB (oczekuje się, że w ciągu tygodni wzrośnie do kilku 10 GB), kiedy musiałem wykonać zbiorcze usunięcie w bardzo centralnej tabeli w głównym schemacie. Wszystkie zainteresowane klucze obce są oznaczone NA USUŃ KASKADĘ.

Nie było zaskoczeniem, że zajmie to dużo czasu, ale po 12 godzinach stało się jasne, że lepiej zacząć od początku, porzucając DB i ponownie uruchamiając migrację. Ale co, jeśli będę musiał powtórzyć tę operację później, gdy DB będzie aktywne i znacznie większe? Czy istnieją alternatywne, szybsze metody?

Czy byłoby znacznie szybciej, gdybym napisał skrypt, który będzie przeglądał zależne tabele, zaczynając od tabeli najdalej od tabeli centralnej, usuwając zależne wiersze tabela po tabeli?

Ważnym szczegółem jest to, że niektóre tabele zawierają wyzwalacze.

Miałem podobny problem. Jak się okazuje, te ON DELETE CASCADEwyzwalacze spowalniały trochę rzeczy, ponieważ te kaskadowe usuwanie było strasznie powolne.

Rozwiązałem problem, tworząc indeksy w polach kluczy obcych w tabelach odwołań, i zacząłem od poświęcenia kilku godzin na usunięcie do kilku sekund.

Masz kilka opcji. Najlepszą opcją jest uruchomienie usuwania wsadowego, aby wyzwalacze nie zostały trafione. Przed wyzwalaniem wyłącz wyzwalacze, a następnie włącz je ponownie. Oszczędza to bardzo dużo czasu. Na przykład:

ALTER TABLE tablename DISABLE TRIGGER ALL; 
DELETE ...; 
ALTER TABLE tablename ENABLE TRIGGER ALL;

Ważnym kluczem tutaj jest to, że chcesz zminimalizować głębokość podkwerend. W takim przypadku możesz skonfigurować tabele tymczasowe do przechowywania odpowiednich informacji, aby uniknąć głębokich podzapytań podczas usuwania.

Najprostszym sposobem rozwiązania tego problemu jest do kwerendy szczegółowy harmonogram z PostgreSQL: EXPLAIN. W tym celu musisz znaleźć co najmniej jedno zapytanie, które zostanie zakończone, ale zajmie to więcej niż oczekiwano. Powiedzmy, że ta linia wyglądałaby

delete from mydata where id='897b4dde-6a0d-4159-91e6-88e84519e6b6';

Zamiast naprawdę uruchamiać to polecenie, możesz to zrobić

begin;
explain (analyze,buffers,timing) delete from mydata where id='897b4dde-6a0d-4159-91e6-88e84519e6b6';
rollback;

Cofanie w końcu pozwala na uruchomienie tego bez modyfikowania bazy danych, ale nadal otrzymujesz szczegółowy harmonogram tego, co zajęło ile. Po uruchomieniu może się okazać, że niektóre wyzwalacze powodują duże opóźnienia:

...
Trigger for constraint XYZ123: time=12311.292 calls=1
...

timeJest w ms (milisekundy), więc sprawdzenie tej contraint trwało około 12,3 sekundy. Musisz dodać nowy INDEXnad wymaganymi kolumnami, aby ten wyzwalacz mógł być skutecznie obliczony. W przypadku odwołań do klucza obcego kolumna odwołująca się do innej tabeli musi zostać zindeksowana (tzn. Kolumna źródłowa, a nie kolumna docelowa). PostgreSQL nie tworzy dla ciebie takich indeksów i DELETEjest to jedyne typowe zapytanie, w którym naprawdę potrzebujesz tego indeksu. W rezultacie możesz gromadzić lata danych, dopóki nie trafisz na przypadek, w którym DELETEjest zbyt wolny z powodu braku indeksu.

Po poprawieniu wydajności tego ograniczenia (lub innej rzeczy, która zajęła zbyt dużo czasu), powtórz polecenie w begin/ rollbackblock, aby porównać nowy czas wykonania z poprzednim. Kontynuuj, aż będziesz zadowolony z czasu odpowiedzi na usunięcie pojedynczej linii (mam jedno zapytanie, aby przejść od 25,6 sekundy do 15 ms, po prostu dodając różne indeksy). Następnie możesz przejść do pełnego usunięcia bez żadnych włamań.

(Zauważ, że EXPLAINpotrzebne jest zapytanie, które można pomyślnie ukończyć. Kiedyś miałem problem z tym, że PostgreSQL zbyt długo zastanawiał się, czy jedno usunięcie naruszy ograniczenie klucza obcego i w takim przypadku EXPLAINnie można go użyć, ponieważ nie wyemituje czasu niepowodzenia zapytania. W takim przypadku nie znam łatwego sposobu na debugowanie problemów z wydajnością).

Wyłączenie wyzwalaczy może stanowić zagrożenie dla integralności bazy danych i nie może być zalecane; jednak jeśli masz pewność, że Twoja operacja jest odporna na ograniczenia, możesz wyłączyć wyzwalacze, wykonując następujące czynności:SET session_replication_role = replica;

Uruchom DELETEtutaj.

Aby przywrócić wyzwalacze, uruchom: SET session_replication_role = DEFAULT;

Źródło tutaj.

Jeśli masz wyzwalacze ON DELETE CASCADE, mają nadzieję, że istnieją tam z jakiegoś powodu i dlatego nie powinny być wyłączone. Kolejną sztuczką (wciąż dodającą swoje indeksy), która działa dla mnie, jest utworzenie funkcji usuwania, która ręcznie usuwa dane, zaczynając od tabel na końcu kaskady, i działa w kierunku tabeli głównej. (Jest to to samo, co musiałbyś, gdybyś miał wyzwalacz przy usunięciu ograniczenia)

CREATE TABLE tablea (
    tablea_uid integer
);

CREATE TABLE tableb (
    tableb_uid integer,
    tablea_rid integer REFERENCES tablea(tablea_uid)
);

CREATE TABLE tablec (
    tablec_uid integer,
    tableb_rid integer REFERENCES tableb(tableb_uid)
);

W takim przypadku usuń dane z tablec, a następnie tableb, a następnie tablea

CREATE OR REPLACE FUNCTION delete_in_order()
 RETURNS void AS $$

    DELETE FROM tablec;
    DELETE FROM tableb;
    DELETE FROM tablea;

$$ LANGUAGE SQL;