Postgres에 대량 삽입하는 가장 빠른 방법은 무엇입니까?

Postgres에 대량 삽입하는 가장 빠른 방법은 무엇입니까?

수천만 개의 기록을 포스트그레스 데이터베이스에 프로그래밍 방식으로 삽입해야 해현재 한 번의 쿼리에서 수천 개의 삽입문을 실행하고 있습니다.

제가 모르는 벌크 삽입문 같은 더 좋은 방법이 있을까요?

PostgreSQL에는 처음에 데이터베이스를 가장 잘 채우는 방법에 대한 가이드가 있으며 행을 대량으로 로드하기 위해 COPY 명령을 사용할 것을 권장합니다.이 가이드에서는 데이터를 로드하기 전에 인덱스 및 외부 키를 제거하고 나중에 다시 추가하는 등 프로세스 속도를 높이는 방법에 대한 몇 가지 유용한 팁을 제공합니다.

Postgres가 지원하는 다중 행 값 구문인 COPY를 사용하는 대신 사용할 수 있습니다.매뉴얼에서 다음 항목을 참조하십시오.

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

위의 코드는 2개의 행을 삽입하지만, 준비된 스테이트먼트 토큰의 최대 수에 도달할 때까지 임의로 확장할 수 있습니다(999달러일 수도 있지만, 그것에 대해서는 100% 확신할 수 없습니다.경우에 따라서는 카피를 사용할 수 없는 경우가 있기 때문에, 이것은 이러한 상황에 적합한 대체 수단입니다.

작업 속도를 높이는 한 가지 방법은 트랜잭션 내에서 여러 개의 삽입 또는 복사를 명시적으로 수행하는 것입니다(예: 1000).Postgres의 기본 동작은 각 문 뒤에 커밋하는 것이기 때문에 커밋을 배치함으로써 오버헤드를 피할 수 있습니다.Daniel의 답변 가이드에 나와 있는 바와 같이 자동 커밋을 비활성화해야 할 수 있습니다.또, 하단의 코멘트에서는, wal_buffer 의 사이즈를 16 MB 로 늘리는 것도 도움이 된다고 하는 점에 주의해 주세요.

UNNEST 배열을 포함하는 함수를 다중 행 VALUES 구문과 함께 사용할 수 있습니다.이 방법은 사용하는 것보다 느린 것 같습니다.COPY와 비단뱀(pycopg python)을 다룰 이 됩니다.list에 cursor.execute pg가 ARRAY

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

없이VALUES「 」 、 「 」 、 「 」 。

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
);

일괄 업데이트와 동일한 구문:

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;

(이 Wiki는 답변을 편집하고 강화할 수 있습니다!)

외부 파일은 최적의 전형적인 벌크 데이터입니다.

"대량 데이터"라는 용어는 "많은 데이터"와 관련이 있으므로 SQL로 변환할 필요 없이 원래 원시 데이터를 사용하는 것이 당연합니다."대량 삽입"의 일반적인 원시 데이터 파일은 CSV 및 JSON 형식입니다.

일부 변환이 포함된 대량 삽입

ETL 도포 및 섭취 프로세스에서 데이터를 삽입하기 전에 변경해야 합니다.임시 테이블은 디스크 공간을 많이 사용하며, 더 빠른 방법은 아닙니다.포스트그레SQL Foreign Data Wrapper(FDW; 외부 데이터 래퍼)가 최선의 선택입니다.

CSV의 예예를 들어tablename (x, y, z)SQL CSV를 사용합니다.

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

의 SQL을 할 수 .COPY(원래 데이터와 같이) 로딩하다tmp_tablename를 에 tablename 디스크는 섭취하는 . 단, 디스크 소비를 피하기 위해서는 다음 방법으로 직접 섭취하는 것이 가장 좋습니다.

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

FDW용 스태틱 FDW용 데이터베이스를 준비해야 합니다.대신 스태틱하게tmp_tablename_fdw이를 생성하는 함수를 사용할 수 있습니다.

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');

JSON의 예파일 두 개로 구성된 세트입니다.myRawData1.json ★★★★★★★★★★★★★★★★★」Ranger_Policies2.json「 」 、 「 」:

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
;

여기서 jsonb_read_files() 함수는 마스크로 정의된 폴더의 모든 파일을 읽습니다.

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;

gzip 스트리밍 부족

파일 수집(주로 빅 데이터)의 가장 빈번한 방법은 원본 파일을 gzip 형식으로 보존하고 스트리밍 알고리즘으로 전송하는 것입니다.이 알고리즘은 UNIX 파이프에서 디스크를 사용하지 않고 빠르게 실행할 수 있는 모든 방법입니다.

 gunzip remote_or_local_file.csv.gz | convert_to_sql | psql 

따라서 이상적인 (미래에는) 포맷을 위한 서버 옵션.csv.gz.

@CharlieClark 코멘트 뒤에 주의: (2022년) 현재 할 일이 없습니다.대안은 STDIN입니다.

  gunzip -c file.csv.gz | pgloader --type csv ... - pgsql:///target?foo

하시면 됩니다.COPY table TO ... WITH BINARY이는 "텍스트 및 CSV 형식보다 다소 빠른 속도"입니다.삽입할 행이 수백만 개이고 이진 데이터가 익숙한 경우에만 이 작업을 수행하십시오.

다음은 바이너리 입력과 함께 psycopg2를 사용하는 Python의 레시피 예시입니다.

대부분 데이터베이스의 다른 활동에 따라 달라집니다.이와 같은 작업을 수행하면 다른 세션의 데이터베이스 전체가 사실상 동결됩니다.또 다른 고려사항은 데이터 모델 및 제약 조건, 트리거 등의 존재입니다.

은 항상 타깃과 유사한 입니다.create table tmp AS select * from target where 1=0임시직그런 다음 중복, 타겟에 이미 존재하는 키 등 체크할 수 있는 것을 확인합니다.

...do insert into target select * from tmp또는 이와 유사합니다.

실패하거나 시간이 너무 오래 걸리는 경우 중단하고 다른 방법(인덱스/제약 해제 등)을 고려합니다.

방금 이 문제가 발생했습니다.Postgres로의 벌크 Import에는 csvsql(릴리스)을 권장합니다.대량 삽입을 수행하려면createdb그런 다음 사용csvsql데이터베이스에 접속하여 CSV 폴더 전체에 대해 개별 테이블을 만듭니다.

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

네이티브 libpq 메서드로 매우 빠른 Postgresq 데이터 로더를 구현했습니다.패키지 https://www.nuget.org/packages/NpgsqlBulkCopy/를 사용해 보세요.

내가 이미 늦었을지도 몰라.그런데 자바 라이브러리가 있는데pgbulkinsertBytefish에 의해.저와 제 팀은 15초 만에 백만 장의 음반을 대량으로 삽입할 수 있었습니다.물론 미니오에 있는 파일에서 100만개 이상의 레코드를 읽고, 100만개 이상의 레코드의 맨 위에 있는 몇 가지 처리를 수행하고, 중복될 경우 레코드를 필터링하여 마지막으로 100만개의 레코드를 Postgres 데이터베이스에 삽입하는 작업도 수행했습니다.그리고 이 모든 과정은 15초 안에 완료되었다.DB 조작에 걸린 시간은 정확히 기억나지 않지만, 5초 미만이었던 것 같습니다.자세한 것은, https://www.bytefish.de/blog/pgbulkinsert_bulkprocessor.html 를 참조해 주세요.

다른 사람들이 지적했듯이 Postgres에 데이터를 Import할 때 Postgres가 당신을 위해 설계한 검사로 인해 작업이 느려집니다.또한 데이터를 사용하기 위해 어떤 식으로든 데이터를 조작해야 하는 경우가 많습니다.Postgres 프로세스 외부에서 수행할 수 있는 모든 작업은 COPY 프로토콜을 사용하여 가져올 수 있음을 의미합니다.

저는 pgloader를 사용하여 정기적으로 httparchive.org 프로젝트에서 데이터를 Import하고 있습니다.소스 파일이 MySQL에 의해 생성되기 때문에 다음과 같은 MySQL의 이상한 점을 처리할 수 있어야 합니다.\N빈 값 및 부호화 문제와 함께 사용됩니다.파일도 너무 커서 적어도 내 머신에서는 FDW를 사용하면 메모리가 부족합니다.pgloader를 사용하면 메인 데이터베이스로 들어가기 전에 원하는 필드를 선택하고 관련 데이터 유형으로 캐스트하며 추가 작업을 수행할 수 있는 파이프라인을 쉽게 만들 수 있으므로 인덱스 업데이트 등이 최소화됩니다.

다음 쿼리는 10000개의 행이 있는 열을 가진 테이블을 만들 수 있습니다.* 보통 쿼리 성능을 테스트하기 위해 이러한 테이블을 만듭니다.generate_series()를 체크할 수 있습니다.

CREATE TABLE test AS SELECT generate_series(1, 10000);

postgres=# SELECT count(*) FROM test;
 count
-------
 10000
(1 row)

postgres=# SELECT * FROM test;
 generate_series
-----------------
               1
               2
               3
               4
               5
               6
-- More --

또한 테이블이 이미 있는 경우 아래 쿼리를 실행하여 10000 행을 삽입합니다.

INSERT INTO test (generate_series) SELECT generate_series(1, 10000);

언급URL : https://stackoverflow.com/questions/758945/whats-the-fastest-way-to-do-a-bulk-insert-into-postgres