데이터베이스 채우기

데이터베이스 채우기 (14.4. Populating a Database)

데이터베이스를 처음 만들고 나서 대량의 데이터를 넣어야 할 때가 있어요. 그냥 INSERT를 수백만 번 돌리면 오래 걸리기 마련이죠. 이 페이지에서는 대량 데이터 적재를 최대한 효율적으로 만드는 방법을 몇 가지 소개해 드릴게요. 천천히 따라 해 보면 적재 시간을 크게 줄일 수 있을 거예요.

출처: PostgreSQL 공식 문서 — populate

자동 커밋 끄기 (Disable Autocommit)

INSERT를 여러 번 사용할 때는 autocommit을 끄고 마지막에 한 번만 커밋하는 게 좋아요. 순수 SQL에서는 시작에 BEGIN, 끝에 COMMIT을 쓰면 돼요. 일부 클라이언트 라이브러리는 사용자 몰래 이 작업을 해주기도 하는데, 그런 경우라도 원하는 시점에 라이브러리가 커밋을 하도록 확인해야 해요.

각 삽입을 개별적으로 커밋하게 두면, 행 하나가 추가될 때마다 PostgreSQL이 많은 일을 해야 해요. 모든 삽입을 한 트랜잭션으로 묶으면 추가 이득도 있어요. 어느 한 행의 삽입이 실패하면 그때까지 삽입된 모든 행이 롤백되니까, 부분적으로만 로드된 데이터에 갇히지 않아요.

COPY 사용하기 (Use COPY)

여러 번의 INSERT 대신 COPY로 한 명령에서 모든 행을 로드하세요. COPY는 많은 수의 행을 로드하도록 최적화되어 있어요. INSERT보다 유연성은 떨어지지만 대량 로드에서는 오버헤드가 훨씬 적어요. COPY는 단일 명령이라 이 방법으로 테이블을 채울 때 autocommit을 끌 필요도 없어요.

COPY를 쓸 수 없다면, PREPARE로 준비된 INSERT 문을 만든 다음 EXECUTE를 필요한 만큼 반복하는 것도 도움이 돼요. 이러면 INSERT를 반복해서 파싱·플래닝하는 오버헤드가 줄어들어요. 인터페이스마다 제공 방식이 다르니 "prepared statements"를 인터페이스 문서에서 찾아보세요.

참고로 COPY로 많은 행을 로드하는 것은, PREPARE를 쓰고 여러 삽입을 한 트랜잭션으로 배치하더라도 거의 항상 INSERT보다 빨라요.

COPY는 더욱이 이전에 실행한 CREATE TABLE이나 TRUNCATE와 같은 트랜잭션 안에서 사용할 때 가장 빨라요. 그 경우 WAL을 쓸 필요가 없거든요. 오류가 나면 새로 로드된 데이터가 들어 있는 파일이 어차피 제거되니까요. 다만 이 고려 사항은 wal_level이 minimal일 때만 적용돼요. 그 외에는 모든 명령이 WAL을 써야 하기 때문이죠.

인덱스 제거하기 (Remove Indexes)

새로 만든 테이블을 로드한다면 가장 빠른 방법은 이래요.

테이블을 만든다.
COPY로 테이블 데이터를 벌크 로드한다.
그다음 필요한 인덱스를 만든다.

기존 데이터 위에 인덱스를 만드는 게, 각 행이 로드될 때마다 점진적으로 인덱스를 갱신하는 것보다 빨라요.

기존 테이블에 대량의 데이터를 추가한다면, 인덱스를 드롭 → 데이터 로드 → 인덱스 재생성하는 게 이득일 수 있어요. 물론 인덱스가 없는 동안 다른 사용자의 데이터베이스 성능은 나빠질 수 있어요. 특히 unique 인덱스는 드롭하기 전에 다시 한번 생각해야 해요. 인덱스가 없으면 unique 제약이 주는 오류 검사가 그동안 사라지기 때문이죠.

외래 키 제약 제거하기 (Remove Foreign Key Constraints)

인덱스와 마찬가지로 외래 키 제약도 행 단위보다 "벌크로" 검사하는 게 더 효율적이에요. 그래서 외래 키 제약을 드롭하고 데이터를 로드한 다음 제약을 다시 만드는 게 유용할 수 있어요. 역시 데이터 로드 속도와 제약이 없는 동안의 오류 검사 손실 사이에 트레이드오프가 있어요.

게다가, 기존 외래 키 제약이 있는 테이블에 데이터를 로드할 때는 각 새 행이 서버의 보류 중 트리거 이벤트 목록에 항목을 필요로 해요(행의 외래 키 제약을 검사하는 게 트리거의 발화이기 때문이죠). 수백만 행을 로드하면 트리거 이벤트 큐가 사용 가능한 메모리를 넘쳐서 참을 수 없는 스와핑이나 명령의 완전한 실패로 이어질 수 있어요. 그래서 대량 데이터를 로드할 때는 외래 키를 드롭했다가 다시 적용하는 것이 바람직할 뿐 아니라 때로는 필수예요. 제약을 임시로 제거하는 게 받아들여질 수 없다면, 로드 작업을 더 작은 트랜잭션으로 나누는 방법밖에 없을 수도 있어요.

maintenance_work_mem 늘리기

대량 데이터를 로드할 때 maintenance_work_mem 설정 변수를 일시적으로 늘리면 성능이 개선될 수 있어요. CREATE INDEX 명령과 ALTER TABLE ADD FOREIGN KEY 명령을 빨라지게 도와줘요. COPY 자체에는 별 도움이 안 되니, 위 기법 중 하나 이상을 쓸 때만 유용한 조언이에요.

max_wal_size 늘리기

max_wal_size 설정 변수를 일시적으로 늘리는 것도 대량 데이터 로드를 빠르게 만들 수 있어요. PostgreSQL에 많은 데이터를 로드하면 체크포인트가 보통의 주기(checkpoint_timeout 설정 변수가 지정)보다 더 자주 발생하게 돼요. 체크포인트가 발생할 때마다 모든 더티 페이지를 디스크로 플러시해야 하거든요. 벌크 데이터 로드 동안 max_wal_size를 일시적으로 늘리면 필요한 체크포인트의 개수를 줄일 수 있어요.

WAL 아카이빙과 스트리밍 복제 끄기

WAL 아카이빙이나 스트리밍 복제를 사용하는 설치에 대량 데이터를 로드할 때는, 많은 양의 증분 WAL 데이터를 처리하는 것보다 로드 완료 후 새 베이스 백업을 만드는 게 더 빠를 수 있어요. 로드 중 증분 WAL 로깅을 막으려면 아카이빙과 스트리밍 복제를 꺼야 해요. wal_level을 minimal로, archive_mode를 off로, max_wal_senders를 0으로 설정하면 되죠. 단, 이 설정들을 바꾸려면 서버 재시작이 필요하고, 그전에 만든 베이스 백업은 아카이브 복구와 스탠바이 서버에 사용할 수 없게 되어 데이터 손실로 이어질 수 있어요.

아카이버나 WAL sender가 WAL 데이터를 처리하는 시간을 아끼는 것 외에도, 이렇게 하면 특정 명령이 실제로 더 빨라지기도 해요. wal_level이 minimal이고 현재 서브트랜잭션(또는 최상위 트랜잭션)이 변경하는 테이블이나 인덱스를 만들었거나 잘랐다면 WAL을 전혀 쓰지 않아도 되거든요. (WAL을 쓰는 것보다 끝에 fsync를 하는 편이 크래시 안전성을 더 저렴하게 보장할 수 있어요.)

나중에 ANALYZE 실행하기

테이블 안의 데이터 분포를 크게 바꿨다면 ANALYZE 실행을 강력히 권장해요. 여기에는 테이블에 대량의 데이터를 벌크 로드하는 경우도 포함돼요. ANALYZE(또는 VACUUM ANALYZE)를 실행하면 플래너가 테이블에 대한 최신 통계를 갖게 돼요. 통계가 없거나 낡았다면 플래너가 질의 플래닝 때 잘못된 결정을 내려서, 부정확하거나 없는 통계를 가진 테이블의 성능이 나빠질 수 있어요. autovacuum 데몬이 켜져 있으면 ANALYZE를 자동으로 실행할 수도 있어요. 자세한 내용은 Section 24.1.3과 Section 24.1.6을 참고하세요.

pg_dump에 대한 몇 가지 참고 사항

pg_dump가 생성한 덤프 스크립트는 위 지침 중 몇 가지(전부는 아님)를 자동으로 적용해요. pg_dump 덤프를 최대한 빨리 복원하려면 몇 가지를 수동으로 해줘야 해요. (참고로 이 점들은 덤프를 만들 때가 아니라 복원할 때 적용돼요. psql로 텍스트 덤프를 로드하든 pg_restore로 아카이브 파일을 로드하든 동일하게 적용돼요.)

기본적으로 pg_dump는 COPY를 쓰고, 완전한 스키마+데이터 덤프를 만들 때 인덱스와 외래 키를 만들기 전에 데이터를 로드하도록 조심해요. 그래서 이 경우엔 여러 지침이 자동으로 처리돼요. 남은 작업은:

maintenance_work_mem과 max_wal_size를 적절한(즉 보통보다 큰) 값으로 설정한다.
WAL 아카이빙이나 스트리밍 복제를 쓴다면 복원 중에는 꺼두는 것을 고려한다. 덤프를 로드하기 전에 archive_mode를 off로, wal_level을 minimal로, max_wal_senders를 0으로 설정하면 된다. 그 후 올바른 값으로 되돌리고 새 베이스 백업을 만든다.
pg_dump와 pg_restore의 병렬 덤프/복원 모드를 시험해 보고 최적의 동시 작업 수를 찾는다. -j 옵션으로 병렬 덤프·복원하면 직렬 모드보다 성능이 훨씬 높아진다.
전체 덤프를 하나의 트랜잭션으로 복원할지 고려한다. psql이나 pg_restore에 -1 또는 --single-transaction 옵션을 주면 된다. 이 모드에서는 아주 작은 오류조차 전체 복원을 롤백해서 몇 시간의 작업을 버릴 수 있다. 데이터가 얼마나 서로 얽혀 있는지에 따라 수동 정리보다 나을 수도 있고 아닐 수도 있다. 단일 트랜잭션을 쓰고 WAL 아카이빙을 꺼두면 COPY 명령이 가장 빨리 실행된다.
데이터베이스 서버에 CPU가 여러 개 있다면 pg_restore의 --jobs 옵션 사용을 고려한다. 동시 데이터 로드와 인덱스 생성을 가능하게 한다.
마지막에 ANALYZE를 실행한다.

데이터 전용 덤프(data-only dump)는 여전히 COPY를 쓰지만, 인덱스를 드롭하거나 재생성하지 않고 보통 외래 키도 건드리지 않아요. 그래서 데이터 전용 덤프를 로드할 때 그 기법들을 쓰려면 인덱스와 외래 키를 직접 드롭하고 재생성해야 해요. 데이터를 로드하는 동안 max_wal_size를 늘리는 건 여전히 유용하지만 maintenance_work_mem은 늘릴 필요가 없어요. 그건 나중에 인덱스·외래 키를 직접 재생성할 때 하는 게 좋죠. 끝나면 ANALYZE하는 것도 잊지 마세요. 자세한 내용은 Section 24.1.3과 Section 24.1.6을 참고하세요.

참고

--disable-triggers 옵션으로 외래 키를 비활성화하는 효과를 얻을 수 있어요. 하지만 이 옵션은 외래 키 검증을 연기하는 게 아니라 없애버리므로, 사용하면 잘못된 데이터가 삽입될 수 있다는 점을 알아두세요.

데이터베이스 채우기