[SQL] ID에 의해 수백만 개의 행을 삭제하는 가장 좋은 방법은

ID에 의해 수백만 개의 행을 삭제하는 가장 좋은 방법은

내 PG 데이터베이스에서 2 백만 행에 대해 삭제해야합니다. 제가 삭제해야한다는 ID 목록을 가지고있다. 그러나, 나는이 일을 시도 할 수있는 방법이 일을하고있다.

나는이 여전히 297,268 행이 삭제 실행되고, 100 사일 나중에 테이블에 그들을 가하고와의 일괄 적으로 그 일을 시도했다. (I는 ID를 테이블에서 100 ID의를 선택 IN 목록, IDS 테이블 내가 선택한 (100)에서 삭제하는 것이 어디에서 삭제했다).

나는 시도했다 :

DELETE FROM tbl WHERE id IN (select * from ids)

그것도 영원히 복용. 내가 할 때까지 그것의 진행 상황을 볼 수 없기 때문에 하드, 시간을 측정,하지만 쿼리는 여전히 이일 후 실행되었다.

그냥 종류 나 특정 ID가 삭제 알고, 그리고 ID의 수백만이있을 때 테이블에서 삭제하는 가장 효과적인 방법을 찾고의.

해결법

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

   1.그것은 모든 따라 ...

   그것은 모든 따라 ...

   SET temp_buffers = '1000MB'; -- or whatever you can spare temporarily
   
   CREATE TEMP TABLE tmp AS
   SELECT t.*
   FROM   tbl t
   LEFT   JOIN del_list d USING (id)
   WHERE  d.id IS NULL;      -- copy surviving rows into temporary table
   
   TRUNCATE tbl;             -- empty table - truncate is very fast for big tables
   
   INSERT INTO tbl
   SELECT * FROM tmp;        -- insert back surviving rows.
   

   당신이없는이 방법은 뷰, 외래 키 또는 다른 따라 개체를 다시합니다. 수동으로 설정 temp_buffers에 대해 읽어보십시오. 이 방법은 대부분 적어도 메모리에 테이블 맞는만큼 빠르고, 또는. 서버가이 작업의 중간에 충돌하는 경우 데이터가 손상 될 수 있음을 유의하십시오. 당신은 안전 할 트랜잭션으로 모든 포장 할 수 있습니다.

   실행은 나중에 분석 할 수 있습니다. 당신이 잘라 내기 경로를 이동하지 않았거나 최소 크기로 가져하려는 경우 VACUUM FULL 분석하는 경우 또는 진공 분석. 큰 테이블에 대한 대안 CLUSTER / pg_repack을 고려 :

   작은 테이블의 경우, 간단한 대신 TRUNCATE의 삭제는 종종 빠릅니다 :

   DELETE FROM tbl t
   USING  del_list d
   WHERE  t.id = d.id;
   

   설명서의 TRUNCATE에 대한 참고 사항 섹션을 참조하십시오. 특히 (페드로는 자신의 의견에서 지적) :

   과:

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

   2.우리의 PostgreSQL 업데이트 / 삭제 성능은 Oracle과 같은 강력한으로하지 알고있다. 언제 우리는 수백만 또는 수백만 개의 행의 10 개의를 삭제해야합니다, 정말 어렵다 및 시간이 오래 걸립니다.

   우리의 PostgreSQL 업데이트 / 삭제 성능은 Oracle과 같은 강력한으로하지 알고있다. 언제 우리는 수백만 또는 수백만 개의 행의 10 개의를 삭제해야합니다, 정말 어렵다 및 시간이 오래 걸립니다.

   그러나 우리는 여전히 생산 DBS에서이 작업을 수행 할 수 있습니다. 다음은 내 생각이다 :

   (; 플래그가 성공적으로 삭제 Y는 기록을 의미와 Y는 null이 될 수 있습니다 ID가 삭제하려는 ID를 참조) ID 및 플래그 - 첫째, 우리는 2 열이있는 로그 테이블을 만들어야합니다.

   나중에, 우리는 함수를 만듭니다. 우리는 삭제 작업마다 10,000 행을한다. 내 블로그에 대한 자세한 내용을 볼 수 있습니다. 그것은 중국에서 비록, 당신은 여전히 ​​당신이 거기 SQL 코드에서 원하는 정보를 얻을 수 있습니다.

   더 빨리 실행으로 확인 두 테이블의 id 열은 인덱스입니다.

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

   3.그런 다음 이름 바꾸기 테이블 (당신이 그것을 할 수있는 충분한 자원을 가지고 제공)를 교환, 새 테이블에 삭제하려는 ID를 제외한 모든에게 테이블에 데이터를 복사하려고 할 수 있습니다.

   그런 다음 이름 바꾸기 테이블 (당신이 그것을 할 수있는 충분한 자원을 가지고 제공)를 교환, 새 테이블에 삭제하려는 ID를 제외한 모든에게 테이블에 데이터를 복사하려고 할 수 있습니다.

   이것은 전문가의 조언이 아닙니다.

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

   4.두 가지 가능한 답 :

   두 가지 가능한 답 :

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

   5.먼저 메이크업 확실히 당신은 ID 필드에 인덱스를 가지고, 모두 당신이에서 삭제할 테이블과 테이블에 당신은 삭제 ID에 대해 사용하고 있습니다.

   먼저 메이크업 확실히 당신은 ID 필드에 인덱스를 가지고, 모두 당신이에서 삭제할 테이블과 테이블에 당신은 삭제 ID에 대해 사용하고 있습니다.

   한 번에 100이 너무 작은 것 같다. 1000 만보십시오.

   삭제 ID 테이블에서 삭제 아무것도 할 필요가 없습니다. 일괄 번호에 새 열을 추가 배치 2 등에 배치 1, 1000 1000를 입력하고 확인 삭제 쿼리가 배치 번호를 포함합니다.

6. ==============================

   6.이 작업을 수행하는 가장 쉬운 방법은 모든 제약 조건을 삭제 한 후 삭제를 할 것입니다.

   이 작업을 수행하는 가장 쉬운 방법은 모든 제약 조건을 삭제 한 후 삭제를 할 것입니다.

7. ==============================

   7.만약 테이블에 당신 some_other_table에서 참조에서 왔어요 삭제 (그리고 당신도 일시적으로 외래 키를 삭제하지 않음), 당신이 some_other_table에서 참조하는 컬럼에 인덱스를 가지고 있는지 확인!

   만약 테이블에 당신 some_other_table에서 참조에서 왔어요 삭제 (그리고 당신도 일시적으로 외래 키를 삭제하지 않음), 당신이 some_other_table에서 참조하는 컬럼에 인덱스를 가지고 있는지 확인!

   나는 비슷한 문제 및 삭제가 실제로 some_other_table에 seq_scans을하고 있던 것으로 밝혀 auto_explain.log_nested_statements = 사실과 함께 사용 auto_explain했다 :

       Query Text: SELECT 1 FROM ONLY "public"."some_other_table" x WHERE $1 OPERATOR(pg_catalog.=) "id" FOR KEY SHARE OF x    
       LockRows  (cost=[...])  
         ->  Seq Scan on some_other_table x  (cost=[...])  
               Filter: ($1 = id)
   

   분명히 다른 테이블에서 참조하는 행을 고정하려고 (존재하지한다, 또는 삭제가 실패합니다). 나는 참조 테이블에 인덱스를 생성 한 후, 삭제는 빠른 진도의 주문이었다.

from https://stackoverflow.com/questions/8290900/best-way-to-delete-millions-of-rows-by-id by cc-by-sa and MIT license