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

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

---

내 PG 데이터베이스에서 약 2 백만 개의 행을 삭제해야합니다. 삭제해야하는 ID 목록이 있습니다. 그러나 이렇게하려면 며칠이 걸립니다.

나는 그것들을 테이블에 넣고 100 개의 일괄 처리를 시도했다. 4 일 후, 이것은 297268 개의 행만 삭제 된 상태로 여전히 실행되고있다. (ID 테이블에서 100 개의 ID를 선택하고 해당 목록의 위치를 ​​삭제하고 내가 선택한 100 개의 ID 테이블에서 삭제해야했습니다).

나는 시도했다 :

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

그것도 영원합니다. 완료 될 때까지 진행 상황을 볼 수 없기 때문에 시간을 측정하기 어렵지만 쿼리는 2 일 후에도 계속 실행되었습니다.

삭제할 특정 ID를 알고 있고 수백만 개의 ID가있을 때 테이블에서 삭제하는 가장 효과적인 방법을 찾고 있습니다.

---

그것은 모두 달려 있습니다 ...

• 모든 인덱스 삭제 (삭제에 필요한 ID의 인덱스 제외)
  나중에 다시 생성 (= 인덱스에 대한 증분 업데이트보다 훨씬 빠름)

• 일시적으로 안전하게 삭제 / 비활성화 할 수있는 트리거가 있는지 확인

• 외래 키가 테이블을 참조합니까? 삭제할 수 있습니까? 일시적으로 삭제 하시겠습니까?

• autovacuum 설정에 따라 작업 전에 실행 하는 것이 도움이 될 수 있습니다VACUUM ANALYZE .

• 관련 테이블에 대한 동시 쓰기 액세스 가 없다고 가정 하거나 테이블을 독점적으로 잠 가야하거나이 경로가 전혀 적합하지 않을 수 있습니다.

• 설정에 따라 데이터베이스 채우기 매뉴얼의 관련 장에 나열된 일부 항목 도 유용 할 수 있습니다.

• 테이블의 많은 부분을 삭제하고 나머지는 RAM에 맞는 경우 가장 빠르고 쉬운 방법은 다음과 같습니다.

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설명서 의 설정을 읽으십시오 . 이 방법은 테이블이 메모리에 맞거나 적어도 대부분의 메모리에 들어가는 한 빠릅니다. 이 작업 중에 서버가 충돌하면 데이터가 손실 될 수 있습니다. 더 안전하게 만들기 위해 모든 것을 트랜잭션으로 래핑 할 수 있습니다.

ANALYZE나중에 실행하십시오 . 또는 VACUUM ANALYZE자르기 경로를 사용하지 않았거나 VACUUM FULL ANALYZE최소 크기로 가져 오려는 경우. 큰 테이블의 경우 대안을 고려하십시오 CLUSTER/ pg_repack:

• Postgres 타임 스탬프 쿼리 범위 최적화

작은 테이블의 경우 DELETE대신 간단한 것이 TRUNCATE더 빠릅니다.

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

설명서 의 참고 섹션을 TRUNCATE읽으십시오 . 특히 ( Pedro가 그의 의견에서 지적했듯이 ) :

TRUNCATE같은 명령에서 모든 테이블이 잘리지 않는 한 다른 테이블의 외래 키 참조가있는 테이블에서는 사용할 수 없습니다. [...]

과:

TRUNCATEON DELETE테이블에 대해 존재할 수 있는 트리거를 실행 하지 않습니다 .

---

PostgreSQL의 업데이트 / 삭제 성능이 Oracle만큼 강력하지 않다는 것을 알고 있습니다. 수백만 또는 수천만 행을 삭제해야 할 때 정말 어렵고 시간이 오래 걸립니다.

그러나 프로덕션 DB에서는 여전히이 작업을 수행 할 수 있습니다. 다음은 내 생각입니다.

먼저, 두 개의 열이있는 로그 테이블을 생성해야합니다. id& flag( id는 삭제할 ID를 나타냅니다. 또는 , 레코드가 성공적으로 삭제되었음을 나타내는) 일 flag수 있습니다 .YnullY

나중에 함수를 만듭니다. 10,000 행마다 삭제 작업을 수행합니다. 내 블로그 에서 자세한 내용을 볼 수 있습니다 . 중국어로되어 있지만 SQL 코드에서 원하는 정보를 얻을 수 있습니다.

id더 빠르게 실행되므로 두 테이블 의 열이 인덱스 인지 확인하십시오 .

---

삭제하려는 ID를 제외한 테이블의 모든 데이터를 새 테이블에 복사 한 다음 이름을 바꾼 다음 테이블을 교체 할 수 있습니다 (할 수있는 충분한 리소스가있는 경우).

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

---

두 가지 가능한 답변 :

1. Your table may have lots of constraint or triggers attached to it when you try to delete a record. It will incur much processor cycles and checking from other tables.

2. You may need to put this statement inside a transaction.

---

First make sure you have an index on the ID fields, both in the table you want to delete from and the table you are using for deletion IDs.

100 at a time seems too small. Try 1000 or 10000.

There's no need to delete anything from the deletion ID table. Add a new column for a Batch number and fill it with 1000 for batch 1, 1000 for batch 2, etc. and make sure the deletion query includes the batch number.

---

The easiest way to do this would be to drop all your constraints and then do the delete.

---

If the table you're deleting from is referenced by some_other_table (and you don't want to drop the foreign keys even temporarily), make sure you have an index on the referencing column in some_other_table!

I had a similar problem and used auto_explain with auto_explain.log_nested_statements = true, which revealed that the delete was actually doing seq_scans on some_other_table:

    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)

Apparently it's trying to lock the referencing rows in the other table (which shouldn't exist, or the delete will fail). After I created indexes on the referencing tables, the delete was orders of magnitude faster.

참고URL : https://stackoverflow.com/questions/8290900/best-way-to-delete-millions-of-rows-by-id