PostgreSQL TRUNCATE로 여러 테이블 초기화할 때 느린 문제

 결론

• TRUNCATE 명령 실행시 여러번 실행보다는 한번에 여러 테이블을 지정해는 것이 더 빠르다.

 

개요

• 테스트 코드 작성 중에 테스트 케이스 하나당 20초 이상 걸리는 것을 발견하였다.
• 케이스 실행 전에 테스트 데이터 재설정 TRUNCATE TABLE에서 평균 18.4초 걸리기 때문이였다.
• 테스트 데이터는 12개의 테이블로 이루어져 있는데 FK설정이 많아서 전체적으로 TRUNCATE에 CASCADE 옵션을 적용했다.
• 의외로 데이터를 INSERT하는 작업은 1.3초 정도였다.
• 테이블 비우는 작업을 개선하는 내용을 다뤄본다.

 

현재 상황

• 테스트 데이터 설정을 위해서는 DBUnit을 이용하였다.
• 처음에는 CLEAN_INSERT오퍼레이션을 이용하였는데
  이것은 TRUNCATE가 아닌 DELETE로 내용을 지우기 때문에
  속도가 느리고 FK도 제대로 지울 수 없을 것이라고 판단하여,
  INSERT로 바꾸고 이 작업 전에 TRUNCATE를 직접 하기로 하였다.
• 어플리케이션은 Spring에서 작업중이라 hikariDataSourc에서 connection을 가져와서
  직접 Statement.execute하였다.
• Statement는 한번만 만들고 재사용하였다.
• DB는 PostgreSQL14이다.

 

개선 시도

Execute이용 : 19.2Sec (개선 전 시간)

• statement.execute 12회

 

CASCADE 없애기 : 실패

• CASCADE는 연관된 데이터를 지워주지만 데이터를 체크하기에 시간이 걸린다고 상정하였다.
• DBUnit CSV방식은 CSV파일의 순서를 정해줄 수 있는데(table-ordering.txt),
  그 역순으로 TRUNCATE를 실행하면 CASCADE가 없어도 지울 수 있지 않을까라는 생각에
  역순으로 CASCADE없이 TRUNCATE를 실행해보았다.
• 결과 : 데이터가 없는 테이블에서도 FK설정이 되어 있으면 CASCADE를 이용하라는 안내와 함께 에러를 뱉어낸다.

 

ExecuteBatch이용 : -7.3Sec

• statement.execute대신 statement.executeBatch를 이용
  statement.addBatch 12회 + statement.executeBatch 1회
• 결과 : 배치라고 하나 개별 명령문을 연속 실행하는 듯 하다.(12개의 TRUNCATE를 한번에 실행)

 

AutoCommit False : -7.7Sec

• connection의 autoCommit만 false로 설정하고 실행하였다.
  statement.execute 12회
• 결과 : connection의 설정 외에 바꾸지 않았는데도 7.7초 더 빨리 완료되었다.

 

AutoCommit False + ExecuteBatch이용 : -8.1Sec

• 결과 : 배치보다는 AutoCommit의 영향이 큰 듯 하다.

 

Truncate명령 1회 실행 : -14.9Sec or -14.6Sec

• TRUNCATE TABLE {table_name} CASCADE x 12의 형태가 아닌
  TRUNCATE TABLE {table_name_1} ,{table_name_2}...  CASCADE로 실행하였다.
  statement.execute 1회
• 결과 : 다른 결과와 달리 AutoCommit이 True인 경우가 더 빨랐다. -14.9초
  어차피 명령이 1회 실행이라 AutoCommit은 큰 의미는 없어보인다.

 

테스트 결과

• excel: 

TRUNCATE.xlsx

0.01MB

 

참고

• PostgreSQL 14 document: https://www.postgresql.org/docs/14/sql-truncate.html