Jak skutecznie kopiować miliony wierszy z jednej tabeli do drugiej w Postgresql?

37

Mam dwie tabele bazy danych. Jeden zawiera setki milionów rekordów. Nazwijmy to history. Drugi jest obliczany codziennie i chcę skopiować wszystkie jego rekordy do historyjednego.

To, co zrobiłem, to uruchomić:

INSERT INTO history SELECT * FROM daily

I przez jakiś czas to działało, ale zaczęło być coraz wolniejsze w miarę wzrostu liczby rekordów. Teraz mam około 2 milionów płyt, które muszą być skopiowane z dailyaby historyw jednej operacji, a to trwa zbyt długo, aby kompletne.

Czy istnieje inny, bardziej wydajny sposób kopiowania danych z jednej tabeli do drugiej?

postgresql

— Milovan Zogovic
źródło

10

Jeśli planujesz przechowywać historię przez długie okresy (wiele miesięcy), sugeruję przyjrzenie się opcjom partycjonowania - może to być jedna partycja na każdy dzień lub tydzień i tak dalej. Zależy to również od wzorców dostępu do twojej tabeli historii (czy uruchamiasz zapytania, które uzyskują dostęp do danych między datami? Czy robisz wiele agregacji itp.). Zobacz zmaterializowane widoki do przechowywania agregatów / podsumowań. http://www.postgresql.org/docs/9.3/static/ddl-partitioning.html http://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html

— Jayadevan
źródło

Dziękuję za odpowiedź. Wydaje się, że to jedyna droga. Musiałbym podzielić dane na części według miesięcy, a tym samym znacznie przyspieszyć ponowne indeksowanie (ponieważ regeneracja indeksu była tutaj problemem).

— Milovan Zogovic,

16

Zrzuć tabelę w formacie csv

COPY table TO '/tmp/table.csv' DELIMITER ',';

użyj polecenia KOPIUJ, które jest znacznie wydajniejsze w przypadku dużych ilości danych.

COPY table FROM '/tmp/table.csv' DELIMITER ',';

Sprawdź dokumenty postgres na http://www.postgresql.org/docs/current/static/sql-copy.html, aby uzyskać więcej informacji

— Fabrizio Mazzoni
źródło

1

Wciąż działa bardzo, bardzo wolno ... Być może musi coś zrobić z koniecznością odbudowania tak ogromnego indeksu? historyTabela zawiera 160 milionów wierszy , a my dodajemy kolejne 3 miliony wierszy.

— Milovan Zogovic

2

Jeśli wypełniasz pustą tabelę lub dodajesz więcej wierszy niż już istnieje, zwykle bardziej wydajne jest upuszczanie indeksów nieklastrowanych i tworzenie ich ponownie po zakończeniu przesyłania (chyba że w danym momencie jest aktywne użycie tabel) )

— David Spillett,

BTW, czy jest to jednorazowa operacja, czy jest to coś, co musisz robić regularnie? Jeśli regularnie, sugeruję utworzenie wyzwalacza, abyś nie musiał przechodzić przez tę próbę za każdym razem.

— Fabrizio Mazzoni,

@FabrizioMazzoni - Musi być wykonywany codziennie o określonej godzinie (trochę robienie zdjęć w czasie).

— Milovan Zogovic,

@DavidSpillett - rzeczywiście! Usunięcie indeksów powoduje, że import jest bardzo szybki (patrz moja odpowiedź powyżej), jednak odtwarzanie indeksów zajmuje wiele godzin (ponieważ mam

— 160 mln

14

Problem dotyczył indeksów. historyStół miał 160m indeksowanych wiersze. Uruchomienie jednego z nich COPY FROMlub INSERT INTO .. SELECTzajęło dużo czasu, aby nie wstawiać wierszy, ale aktualizować indeksy. Kiedy wyłączyłem indeksy, zaimportowałem 3M wierszy w 10 sekund. Teraz muszę znaleźć szybszy sposób reindeksacji dużego stołu.

— Milovan Zogovic
źródło

3

Czy potrzebujesz indeksów w tabeli historii?

— Sherlock,

2

Dodaj indeks, używając słowa kluczowego KONKURENCYJNIE

— Akvel

10

Możesz użyć narzędzia psql , mogę być wydajny, ponieważ:

psql -h ${DAILY_HOST_IP} -p ${PG_PORT} ${DB_NAME} ${USER_NAME} -c "copy daily to stdout " | psql -h ${HISTORY_HOST_IP} -p ${PG_PORT} ${DB_NAME} ${USER_NAME}  -c "copy history from stdin"

Możesz także napisać skrypt powłoki.

— franki
źródło

Świetne rozwiązanie bez pośredniego pliku. Również bardzo szybko skopiowałem tabelę 950 milionów wierszy w 1h20 (bez indeksów) między zwykłym dyskiem a sieciowym systemem plików.

— Le Droid

3

To oczywiście nie jest dokładna odpowiedź na twoje pytanie, ale jeśli nie potrzebujesz dostępu do historytabeli, możesz również wygenerować zrzut SQL:

pg_dump -h host -p port -w -U user db > dump.sql

Następnie można użyć narzędzia takiego jak gitobliczyć różnicę i przechowywać ją wydajnie.

git add dump.sql
git commit -m "temp dump"
git gc --aggressive

Jest to przydatne, ponieważ większość części w bazie danych nie zmienia się codziennie. Zamiast przechowywać całą kopię na każdy dzień, można zapisać różnicę między dwoma dniami.

Możesz użyć crontabzadania, dzięki któremu zrzut jest przetwarzany codziennie.

— Willem Van Onsem
źródło