Jaki jest najszybszy sposób na masowe wstawianie do Postgres?


242

Muszę programowo wstawić 10 milionów rekordów do bazy danych Postgres. Obecnie wykonuję 1000 instrukcji wstawiania w jednym „zapytaniu”.

Czy istnieje lepszy sposób, aby to zrobić, jakąś masową instrukcję wstawiania, o której nie wiem?

Odpowiedzi:


211

PostgreSQL zawiera przewodnik na temat tego, jak najlepiej zapełnić bazę danych na początku, i sugeruje użycie polecenia KOPIUJ do masowego ładowania wierszy. Przewodnik zawiera kilka innych dobrych wskazówek, jak przyspieszyć proces, takich jak usuwanie indeksów i kluczy obcych przed załadowaniem danych (i dodaniem ich później).


33
Napisałem nieco więcej szczegółów, aby rozwinąć je również na stackoverflow.com/questions/12206600/… .
Craig Ringer

24
@CraigRinger Wow, „trochę więcej szczegółów” to najlepsze niedomówienie, jakie widziałem przez cały tydzień;)
culix 7'14

Wypróbuj pakiet instalacyjny NpgsqlBulkCopy
Elyor

1
-Ponieważ indeksy są również używane do fizycznego układu rekordów db. Nie jestem pewien, czy usunięcie indeksów z dowolnej bazy danych jest dobrym pomysłem.
Farjad

Ale polecam, nic w pamięci !!! A jeśli twój rozmiar partii może być niewielka, bardzo źle działało, to jego klasa :( Próbuję npgsql klasy CopyIn, ponieważ jest to jak mapowanie sformatowane w CSV w zapytaniach PG. Czy możesz wypróbować Big Table?
Elyor

93

Istnieje alternatywa dla korzystania z COPY, która jest składnią wielu wartości, którą obsługuje Postgres. Z dokumentacji :

INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

Powyższy kod wstawia dwa wiersze, ale możesz go dowolnie rozszerzyć, dopóki nie osiągniesz maksymalnej liczby przygotowanych tokenów instrukcji (może to być 999 USD, ale nie jestem w 100% pewien). Czasami nie można korzystać z funkcji KOPIUJ i jest to godny zamiennik w takich sytuacjach.


12
Czy wiesz, jak wydajność tej metody wypada w porównaniu z KOPIOWANIEM?
Grant Humphries,

Jeśli napotkasz problem z uprawnieniami, przed wypróbowaniem tego użyj KOPIUJ ... OD STDINA
Andrew Scott Evans

Jeśli korzystasz z zabezpieczeń na poziomie wierszy, jest to najlepsze, co możesz zrobić. „Funkcja COPY FROM nie jest obsługiwana w przypadku tabel z zabezpieczeniami na poziomie wiersza” od wersji 12.
Eloff,

KOPIOWANIE jest znacznie szybsze niż rozszerzony WSTAW
hipertracker

24

Jednym ze sposobów przyspieszenia jest jawne wykonanie wielu wstawień lub kopii w ramach transakcji (powiedzmy 1000). Domyślnym zachowaniem Postgresa jest zatwierdzanie po każdej instrukcji, więc przez grupowanie zatwierdzeń można uniknąć pewnego narzutu. Jak mówi przewodnik w odpowiedzi Daniela, może być konieczne wyłączenie automatycznego zatwierdzania, aby to zadziałało. Zwróć również uwagę na komentarz na dole, który sugeruje zwiększenie wielkości buforów wal_buffers do 16 MB.


1
Warto wspomnieć, że limit liczby wstawek / kopii, które można dodać do tej samej transakcji, jest prawdopodobnie znacznie wyższy niż cokolwiek, co spróbujesz. Możesz dodać miliony wierszy w ramach tej samej transakcji i nie napotkać problemów.
Sumeet Jain

@SumeetJain Tak, zwracam uwagę na szybkość „sweet spot” pod względem liczby kopii / przekładek na transakcję.
Dana the Sane

Czy to zablokuje tabelę podczas trwania transakcji?
Lambda Fairy

15

UNNESTfunkcja z tablicami może być używana wraz ze składnią multirow VALUES. Myślę, że ta metoda jest wolniejsza niż używanie, COPYale przydaje mi się w pracy z psycopg i python (python listprzekazany na cursor.executepg ARRAY):

INSERT INTO tablename (fieldname1, fieldname2, fieldname3)
VALUES (
    UNNEST(ARRAY[1, 2, 3]), 
    UNNEST(ARRAY[100, 200, 300]), 
    UNNEST(ARRAY['a', 'b', 'c'])
);

bez VALUESużycia podselekcji z dodatkową kontrolą istnienia:

INSERT INTO tablename (fieldname1, fieldname2, fieldname3)
SELECT * FROM (
    SELECT UNNEST(ARRAY[1, 2, 3]), 
           UNNEST(ARRAY[100, 200, 300]), 
           UNNEST(ARRAY['a', 'b', 'c'])
) AS temptable
WHERE NOT EXISTS (
    SELECT 1 FROM tablename tt
    WHERE tt.fieldname1=temptable.fieldname1
);

ta sama składnia dla aktualizacji zbiorczych:

UPDATE tablename
SET fieldname1=temptable.data
FROM (
    SELECT UNNEST(ARRAY[1,2]) AS id,
           UNNEST(ARRAY['a', 'b']) AS data
) AS temptable
WHERE tablename.id=temptable.id;


9

Zależy to głównie od (innej) aktywności w bazie danych. Takie operacje skutecznie zamrażają całą bazę danych na inne sesje. Innym zagadnieniem jest model danych i obecność ograniczeń, wyzwalaczy itp.

Moje pierwsze podejście to zawsze: utwórz tabelę (temp) o strukturze podobnej do tabeli docelowej (utwórz tabelę tmp AS wybierz * z celu, gdzie 1 = 0) i zacznij od odczytu pliku do tabeli temp. Następnie sprawdzam, co można sprawdzić: duplikaty, klucze, które już istnieją w celu itp.

Następnie wykonuję polecenie „do insert into target select * from tmp” lub podobne.

Jeśli to się nie powiedzie lub zajmie to zbyt długo, przerywam to i rozważam inne metody (tymczasowe usuwanie indeksów / ograniczeń itp.)



6

Właśnie napotkałem ten problem i polecam csvsql ( wersje ) do masowego importu do Postgres. Aby wykonać wstawianie zbiorcze, którego po prostu createdbużyjesz csvsql, a następnie użyjesz , aby połączyć się z bazą danych i utworzyć indywidualne tabele dla całego folderu CSV.

$ createdb test 
$ csvsql --db postgresql:///test --insert examples/*.csv

1
W przypadku csvsql, aby również wyczyścić źródłowy plik csv z ewentualnych błędów formatowania, najlepiej postępować zgodnie z tymi instrukcjami , więcej dokumentacji tutaj
sal

0

Plik zewnętrzny to najlepsze i typowe dane zbiorcze

Termin „dane zbiorcze” odnosi się do „dużej ilości danych”, więc naturalne jest używanie oryginalnych danych surowych , bez potrzeby przekształcania ich w SQL. Typowe pliki surowych danych dla „wstawiania zbiorczego” to CSV i JSON formaty .

Wkładka luzem z pewną transformacją

W aplikacjach ETL i procesach przetwarzania musimy zmienić dane przed ich wstawieniem. Tabela tymczasowa zajmuje (dużo) miejsca na dysku i nie jest to najszybszy sposób na zrobienie tego. PostgreSQL wrapper obcych dane (FDW) jest najlepszym wyborem.

Przykład CSV . Załóżmy, że tablename (x, y, z)plik SQL lub CSV jest podobny

fieldname1,fieldname2,fieldname3
etc,etc,etc
... million lines ...

Możesz użyć klasycznego kodu SQL, COPYaby załadować ( tak jak oryginalne dane) tmp_tablename, wstawić przefiltrowane dane do tablename... Ale, aby uniknąć zużycia dysku, najlepiej jest je pobrać bezpośrednio przez

INSERT INTO tablename (x, y, z)
  SELECT f1(fieldname1), f2(fieldname2), f3(fieldname3) -- the transforms 
  FROM tmp_tablename_fdw
  -- WHERE condictions
;

Musisz przygotować bazę danych dla FDW, a zamiast statycznego tmp_tablename_fdwmożesz użyć funkcji, która ją generuje :

CREATE EXTENSION file_fdw;
CREATE SERVER import FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE tmp_tablename_fdw(
  ...
) SERVER import OPTIONS ( filename '/tmp/pg_io/file.csv', format 'csv');

Przykład JSON . Zestaw dwóch plików myRawData1.jsoni Ranger_Policies2.jsonmoże być spożywana przez:

INSERT INTO tablename (fname, metadata, content)
 SELECT fname, meta, j  -- do any data transformation here
 FROM jsonb_read_files('myRawData%.json')
 -- WHERE any_condiction_here
;

gdzie funkcja jsonb_read_files () odczytuje wszystkie pliki folderu zdefiniowane przez maskę:

CREATE or replace FUNCTION jsonb_read_files(
  p_flike text, p_fpath text DEFAULT '/tmp/pg_io/'
) RETURNS TABLE (fid int,  fname text, fmeta jsonb, j jsonb) AS $f$
  WITH t AS (
     SELECT (row_number() OVER ())::int id, 
           f as fname,
           p_fpath ||'/'|| f as f
     FROM pg_ls_dir(p_fpath) t(f)
     WHERE    f like p_flike
  ) SELECT id,  fname,
         to_jsonb( pg_stat_file(f) ) || jsonb_build_object('fpath',p_fpath),
         pg_read_file(f)::jsonb
    FROM t
$f$  LANGUAGE SQL IMMUTABLE;

Brak przesyłania strumieniowego gzip

Najczęstszą metodą „pobierania plików” (głównie w Big Data) jest zachowanie oryginalnego pliku w formacie gzip i przesłanie go za pomocą algorytmu przesyłania strumieniowego , wszystko, co może działać szybko i bez zużycia dysku w potokach unix:

 gunzip remote_or_local_file.csv.gz | convert_to_sql | psql 

Tak więc idealna (przyszłość) jest opcją formatu dla serwera.csv.gz .

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.