[SQL] PostgreSQL의에서 대량 / 배치 갱신 / upsert

PostgreSQL의에서 대량 / 배치 갱신 / upsert

나는 시도 캐쉬 모델 장고-ORM의 된 강화를 작성하고 트랜잭션이 끝날 때까지 저장 모델을 연기하고있다. 모든 거의 그러나 나는 SQL 구문에서 예상치 못한 어려움을 가로 질러왔다 이루어집니다.

난 안 많이 DBA의 해요,하지만 내가 이해에서, 데이터베이스는 정말 많은 작은 쿼리에 효율적으로 작동하지 않습니다. 몇몇 더 큰 쿼리는 훨씬 더 있습니다. 예를 들어이 큰 일괄 삽입을 사용하는 것이 좋습니다 대신 100 원 라이너 (한 번에 100 개의 행을 말한다).

지금, 나는 무엇을 볼 수있는에서, SQL 정말 테이블에 배치 갱신을 수행 할 수있는 문을 제공하지 않습니다. 용어가 혼란 것 같다, 나는 그가 무엇을 의미하는지 설명 할 것이다. I는 임의의 데이터의 배열 표의 단일 행을 나타내는 각 항목을 갖는다. 나는 배열의 해당 항목에서 테이블의 특정 행, 각 사용하여 데이터를 업데이트하고 싶습니다. 아이디어는 일괄 삽입 매우 유사하다.

예를 들어 : 내 표는 두 개의 열 "ID"와 "some_col"을 가질 수 있습니다. 지금 일괄 업데이트 데이터를 설명하는 배열은 세 항목 (1 ', 제 업데이트'), (2 '번째 업데이트 ") 및 ("제 업데이트 3')로 구성된다. 갱신 전에 테이블의 행을 포함 ( '제 2'()의 첫번째 '1), (3, "제").

이 게시물 건너 온 :

왜 빠른 일괄 삽입 / 업데이트는 무엇입니까? 어떻게 배치 업데이트 작업을?

이는 그러나 정말 마지막 구문을 알아낼 수 없습니다, 내가 원하는 것을 할 것 같다.

나는 또한 업데이트를 필요로 일괄 삽입을 사용하여 다시 삽입 모든 행을 삭제할 수 있습니다, 그러나 나는 그것이 어려운이 실제로 더 잘 수행 할 것으로 믿고 찾을 수 있습니다.

일부 저장 프로 시저가 여기에 수도 그래서 나는, PostgreSQL의 8.4와 함께 작동합니다. 내가 프로젝트 결국 오픈 소스로 계획하지만, 더 이상 휴대용 아이디어 나 방법이 다른 RDBMS에서 대부분의 환영을 같은 일을하는 할 수 있습니다.

질문을 따르 어떻게 문 "upsert"A "삽입 또는 업데이트"배치를 / 어떻게?

시험 결과

내가 수행 한 100 배 10 배 삽입 작업은 이상의 4 가지 테이블 (총 그래서 1000 개 삽입) 확산. 나는 PostgreSQL의 8.4 백엔드와 장고 1.3에서 테스트.

이러한 결과입니다 :

결론 : 하나의 connection.execute에서 가능한 한 많은 작업으로 실행 (). 장고 자체는 상당한 오버 헤드를 도입한다.

면책 조항 : 나는 작업이 가능한 빨리 그 때문에 실행할 수 있도록 삽입, 기본 기본 키 인덱스에서 떨어져있는 인덱스를 소개하지 않았다.

해결법

1. ==============================

   1.나는 배치 트랜잭션 작업을위한 3 개 전략을 사용했습니다 :

   나는 배치 트랜잭션 작업을위한 3 개 전략을 사용했습니다 :

   덧붙여, Hibernate는 또한 콜렉션 페칭에 배치 전략을 지원합니다. 당신이 @BatchSize와 컬렉션을 주석 경우 연결을 가져 오는 경우, Hibernate는 콜렉션을로드 적은 SELECT 문에지도 = 대신에 사용됩니다.

2. ==============================

   2.당신은 Ketema에 의해 세 개의 열의 대량 삽입을 수정할 수 있습니다 :

   당신은 Ketema에 의해 세 개의 열의 대량 삽입을 수정할 수 있습니다 :

   INSERT INTO "table" (col1, col2, col3)
     VALUES (11, 12, 13) , (21, 22, 23) , (31, 32, 33);
   

   그것은된다 :

   INSERT INTO "table" (col1, col2, col3)
     VALUES (unnest(array[11,21,31]), 
             unnest(array[12,22,32]), 
             unnest(array[13,23,33]))
   

   자리에 값을 교체 :

   INSERT INTO "table" (col1, col2, col3)
     VALUES (unnest(?), unnest(?), unnest(?))
   

   이 쿼리에 대한 인수로 배열이나 목록을 통과해야합니다. 이 방법 당신은 문자열 연결을하고 (: SQL 주입과 지옥을 인용하고 모든 hazzles 및 위험)없이 거대한 대량 삽입 할 수 있습니다.

   PostgreSQL은 UPDATE로 확장에서 추가되었습니다. 이 방법을 사용할 수 있습니다 :

   update "table" 
     set value = data_table.new_value
     from 
       (select unnest(?) as key, unnest(?) as new_value) as data_table
     where "table".key = data_table.key;
   

   매뉴얼은 좋은 설명이 누락되어 있지만, PostgreSQL을-관리자 메일 링리스트에 대한 예제가있다. 나는 그것을 정교하게하려고 :

   create table tmp
   (
     id serial not null primary key,
     name text,
     age integer
   );
   
   insert into tmp (name,age) 
   values ('keith', 43),('leslie', 40),('bexley', 19),('casey', 6);
   
   update tmp set age = data_table.age
   from
   (select unnest(array['keith', 'leslie', 'bexley', 'casey']) as name, 
           unnest(array[44, 50, 10, 12]) as age) as data_table
   where tmp.name = data_table.name;
   

   StackExchange은 VALUES 절 대신 하위 쿼리를 사용하여 ... FROM ... 업데이트를 설명하는 다른 게시물도 있습니다. 그들은 쉽게 읽을 수에 의해 수도 있지만, 행 고정 된 수의 제한됩니다.

3. ==============================

   3.대량 삽입은 같은 수행 할 수 있습니다 :

   대량 삽입은 같은 수행 할 수 있습니다 :

   INSERT INTO "table" ( col1, col2, col3)
     VALUES ( 1, 2, 3 ) , ( 3, 4, 5 ) , ( 6, 7, 8 );
   

   3 행을 삽입합니다.

   여러 업데이트는 SQL 표준에 의해 정의하지만, PostgreSQL의 구현되어 있지 않습니다.

   인용문:

   참조 : http://www.postgresql.org/docs/9.0/static/sql-update.html

4. ==============================

   4.이 레코드로 채우기 JSON으로 꽤 빠른 (PostgreSQL을 9.3+)

   이 레코드로 채우기 JSON으로 꽤 빠른 (PostgreSQL을 9.3+)

   big_list_of_tuples = [
       (1, "123.45"),
       ...
       (100000, "678.90"),
   ]
   
   connection.execute("""
       UPDATE mytable
       SET myvalue = Q.myvalue
       FROM (
           SELECT (value->>0)::integer AS id, (value->>1)::decimal AS myvalue 
           FROM json_array_elements(%s)
       ) Q
       WHERE mytable.id = Q.id
       """, 
       [json.dumps(big_list_of_tuples)]
   )
   

5. ==============================

   5.자동 커밋을 끄고 한이 말에 커밋 않습니다. 일반 SQL에서 발행이 수단은 시작에서 시작과 끝에서 COMMIT. 당신은 실제 upsert을하기 위해 함수를 작성해야합니다.

   자동 커밋을 끄고 한이 말에 커밋 않습니다. 일반 SQL에서 발행이 수단은 시작에서 시작과 끝에서 COMMIT. 당신은 실제 upsert을하기 위해 함수를 작성해야합니다.

from https://stackoverflow.com/questions/7019831/bulk-batch-update-upsert-in-postgresql by cc-by-sa and MIT license