PostgreSQL UPDATE 성능 최적화 - 대량 데이터 수정 시 주의사항

UPDATE는 단순히 데이터를 수정하는 것처럼 보이지만, 대량의 데이터를 처리할 때는 생각보다 많은 문제가 발생한다. 실무에서 100만 건 이상의 데이터를 UPDATE 할 때 겪었던 문제들과 해결 방법을 정리한다.

UPDATE가 느린 이유

PostgreSQL에서 UPDATE는 내부적으로 DELETE + INSERT와 비슷하게 동작한다. 기존 행을 삭제 표시하고 새로운 행을 추가하는 방식이다. 이를 MVCC(Multi-Version Concurrency Control)라고 한다.

그래서 UPDATE를 많이 하면

• 테이블 크기가 커진다 (Dead Tuple 증가)
• 인덱스도 새로 추가된다
• VACUUM이 필요해진다

-- 테이블의 Dead Tuple 확인
SELECT 
    relname,
    n_live_tup,
    n_dead_tup,
    round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 2) as dead_ratio
FROM pg_stat_user_tables
WHERE relname = 'link';

dead_ratio가 10% 이상이면 VACUUM을 고려해야 한다.

대량 UPDATE 시 문제점

100만 건을 한번에 UPDATE 하면 어떻게 될까

-- 이렇게 하면 안됨
UPDATE user_log
SET status = 'archived'
WHERE created_at < '2024-01-01';

문제점

• 트랜잭션이 너무 길어짐
• 락이 오래 유지됨
• 다른 작업이 대기 상태에 빠짐
• 메모리 부족 가능성
• 롤백 시 더 오래 걸림

해결방법 1: 배치 처리

한번에 1000~10000건씩 나눠서 처리한다.

-- 배치로 나눠서 처리
DO $$
DECLARE
    batch_size INT := 5000;
    affected INT;
BEGIN
    LOOP
        UPDATE user_log
        SET status = 'archived'
        WHERE id IN (
            SELECT id FROM user_log
            WHERE created_at < '2024-01-01'
            AND status != 'archived'
            LIMIT batch_size
        );
        
        GET DIAGNOSTICS affected = ROW_COUNT;
        
        RAISE NOTICE '처리된 행: %', affected;
        
        COMMIT;
        
        EXIT WHEN affected = 0;
        
        -- 잠시 대기 (다른 트랜잭션에게 양보)
        PERFORM pg_sleep(0.1);
    END LOOP;
END $$;

배치 사이즈는 테이블 상황에 따라 조절한다. 보통 5000~10000 사이가 적당하다.

해결방법 2: WHERE 조건에 인덱스 활용

UPDATE의 WHERE 조건에 인덱스가 없으면 풀스캔이 발생한다.

-- 인덱스 확인
SELECT indexname, indexdef 
FROM pg_indexes 
WHERE tablename = 'user_log';

-- 필요하면 인덱스 추가
CREATE INDEX CONCURRENTLY idx_user_log_created_at 
ON user_log(created_at) 
WHERE status != 'archived';

CONCURRENTLY 옵션을 사용하면 테이블 락 없이 인덱스를 생성할 수 있다. 운영 중인 서비스에서는 필수다.

해결방법 3: 실행계획 확인

UPDATE 전에 항상 실행계획을 확인한다.

EXPLAIN (ANALYZE, BUFFERS)
UPDATE user_log
SET status = 'archived'
WHERE created_at < '2024-01-01'
AND status != 'archived';

Seq Scan이 나오면 인덱스 추가를 고려한다. Buffers 항목에서 shared hit이 낮고 read가 높으면 디스크 I/O가 많다는 의미다.

해결방법 4: 트리거 비활성화

테이블에 트리거가 있으면 UPDATE마다 실행된다. 대량 UPDATE 시에는 임시로 비활성화하는 것도 방법이다.

-- 트리거 비활성화
ALTER TABLE user_log DISABLE TRIGGER ALL;

-- UPDATE 실행
UPDATE user_log SET status = 'archived' WHERE ...;

-- 트리거 다시 활성화
ALTER TABLE user_log ENABLE TRIGGER ALL;

단, 트리거가 하는 작업을 별도로 처리해야 할 수도 있다.

해결방법 5: 새 테이블로 교체

정말 대량의 데이터를 수정해야 한다면 UPDATE 대신 새 테이블을 만드는 게 빠를 수 있다.

-- 1. 새 테이블 생성
CREATE TABLE user_log_new AS
SELECT 
    id,
    user_id,
    CASE 
        WHEN created_at < '2024-01-01' THEN 'archived'
        ELSE status
    END as status,
    created_at
FROM user_log;

-- 2. 인덱스, 제약조건 추가
ALTER TABLE user_log_new ADD PRIMARY KEY (id);
CREATE INDEX idx_user_log_new_user_id ON user_log_new(user_id);

-- 3. 테이블 교체
BEGIN;
ALTER TABLE user_log RENAME TO user_log_old;
ALTER TABLE user_log_new RENAME TO user_log;
COMMIT;

-- 4. 확인 후 기존 테이블 삭제
DROP TABLE user_log_old;

이 방법은 테이블 전체의 50% 이상을 수정해야 할 때 고려한다.

UPDATE 후 VACUUM

대량 UPDATE 후에는 VACUUM을 실행해서 Dead Tuple을 정리한다.

-- 일반 VACUUM (테이블 락 없음)
VACUUM user_log;

-- 통계정보도 갱신
VACUUM ANALYZE user_log;

-- 공간까지 반환 (테이블 락 있음, 운영 중 주의)
VACUUM FULL user_log;

autovacuum이 설정되어 있어도 대량 UPDATE 후에는 수동으로 실행하는 게 좋다.

성능 비교 테스트

실제로 100만 건 테이블에서 테스트한 결과다.

방법 소요시간 비고

한번에 UPDATE	45초	락 유지, 다른 작업 불가
배치 5000건	62초	총 시간은 늘지만 락 분산
배치 10000건	55초	적당한 균형
새 테이블 교체	28초	가장 빠름, 다운타임 필요

상황에 따라 적절한 방법을 선택하면 된다.

정리

• UPDATE는 내부적으로 DELETE + INSERT와 비슷하게 동작
• 대량 UPDATE는 배치로 나눠서 처리
• WHERE 조건에 인덱스 필수
• 실행계획 꼭 확인
• UPDATE 후 VACUUM 실행
• 50% 이상 수정 시 새 테이블 교체 고려

다음 글에서는 UPDATE와 함께 자주 쓰이는 RETURNING 절에 대해 정리한다.