[PostgresSQL] 대용량 row를 Delete할때 안전하고 효율적으로 할 수 있는 방법이 궁금합니다.

Question

안녕하세요. PostgreSQL 관련 질문드립니다.PostgreSQL에서 Delete문을 통해 오래된 records들을 지우고자 합니다. 약 5000만건의 row가 쌓여있으며 4800만건의 records를 삭제하고 싶습니다. (실서비스와 연동되어 있다보니 그냥 delete문을 치기에는 부담이 있습니다.)보통 대용량의 delete시 나눠서 batch로 진행한다는 것으로 알고 있습니다. 허나 제 케이스에서는 where절에 들어가는 시간절에 index가 없다보니 delete시 sequential scan을 하는 것을 확인했고 이 경우 어차피 모든 row를 탐색하기 때문에 나눠서 진행하는 것이 의미가 있는지 모르겠습니다. (아래는 일부 시간만 delete했을 때 execution plan입니다)Delete on example (cost&#61;0.00..758103.80 rows&#61;1616 width&#61;6) (actual time&#61;4666.500..4666.501 rows&#61;0 loops&#61;1) -> Seq Scan on example (cost&#61;0.00..758103.80 rows&#61;1616 width&#61;6) (actual time&#61;12.732..4373.117 rows&#61;1529 loops&#61;1) &#34; Filter: ((&#34;&#34;timestamp&#34;&#34; >&#61; '2021-10-27 03:00:00'::timestamp without time zone) AND (&#34;&#34;timestamp&#34;&#34; < '2021-10-27 04:00:00'::timestamp without time zone))&#34; Rows Removed by Filter: 15929099 Planning Time: 0.033 ms Execution Time: 4666.515 ms정리해보면, sequential scan으로 진행되는 4800만건의 delete문을 Batch로 진행할지, 나눠서 진행할지, 나눠서 진행한다면 얼만큼 나눠서 진행하면 좋을지 경험자분께 여쭙고 싶습니다. 또한 삭제를 진행할때 DB 의 어떤 지표들을 모니터링 하면 좋을지도 궁금합니다!

xml개발자 · Accepted Answer

데이터가 엄청 많네요...delete 쿼리문에 limit 제한을 걸어서 50~100만건씩 삭제하시면 될 것 같습니다.구글에 검색해보니, postgreSQL은 WHERE 절에 IN 으로 서브쿼리 감싸서 가능하다고 하네요.아래 참조자료 보시면 될 것 같아요.https://gardentree.tistory.com/143

무명시민 · Answer

DELETE 로는 답이 없을 듯 합니다. 5천만건 중에 200만건만 남기고 4천8백만건을 지우는것 보다는200만건을 임시 테이블로 만들고 이 임시 테이블과 5천만건의 본 테이블과 바꿔치기 하시는게 그나마 빠를 것 같네요더불어서 날짜로 필터링을 하는 것 보면 날짜로 파티션를 고려해 파티션 테이블로 변경도 고려해 보세요그래야 삭제 할 때도 파티션별로 삭제를 해야 관리도 용의 할것 같습니다.

카스마당 · Answer

보통은 대용량 삭제 시 CTAS 문으로 하지 않나요.?PgSQL도 비슷할 겁니다.https://jack-of-all-trades.tistory.com/333

[PostgresSQL] 대용량 row를 Delete할때 안전하고 효율적으로 할 수 있는 방법이 궁금합니다.

3개의 답변

#tunning 목록