정따뜻의 블로그
← 블로그로 돌아가기
0001년 1월 1일 dev

PostgreSQL은 왜 UPDATE가 무거울까 - VACUUM과 Write Amplification

같은 MVCC인데 왜 다를까

PostgreSQL이랑 MySQL(InnoDB)은 둘 다 MVCC(Multi-Version Concurrency Control)를 쓴다. MVCC는 간단히 말하면 “row를 수정할 때 기존 버전을 바로 덮어쓰지 않고, 새 버전을 만들어서 같이 보관하는 것"이다. 이래야 읽기 트랜잭션이 쓰기 트랜잭션한테 블로킹당하지 않는다.

그런데 이 “옛날 버전을 어떻게 보관하느냐"에서 두 DB의 설계 철학이 완전히 갈린다.

MySQL(InnoDB): Undo Log 방식

MySQL은 테이블에는 항상 최신 버전만 유지한다.

테이블 (항상 최신 데이터만)
┌──────────────────────────┐
│ id=1, name='Bob', age=30 │  ← 최신 버전
└──────────────────────────┘

Undo Log (별도 공간)
┌──────────────────────────┐
│ id=1: age 25→30 (diff)   │  ← 변경된 컬럼만 기록
└──────────────────────────┘

UPDATE가 발생하면:

  1. 변경된 컬럼의 이전 값을 undo log에 기록한다 (전체 row가 아니라 diff만)
  2. 테이블의 row를 in-place로 덮어쓴다
  3. 나중에 purge 스레드가 undo log를 자동 정리한다

핵심은 테이블 자체는 항상 깔끔하다는 것이다. 옛날 버전은 별도 공간(undo tablespace)에 따로 모아둔다.

PostgreSQL: Tuple Versioning 방식

PostgreSQL은 테이블 안에 옛날 버전과 새 버전이 같이 산다.

테이블 (모든 버전이 같이 존재)
┌──────────────────────────────────────────┐
│ id=1, name='Bob', age=25  (xmax=100)     │  ← dead tuple (옛날 버전)
│ id=1, name='Bob', age=30  (xmin=100)     │  ← live tuple (최신 버전)
└──────────────────────────────────────────┘

UPDATE가 발생하면:

  1. 기존 row의 xmax에 현재 트랜잭션 ID를 기록한다 (논리적 삭제)
  2. 전체 row를 통째로 복사해서 새 버전을 같은 테이블에 삽입한다
  3. 변경되지 않은 컬럼도 전부 포함된 완전한 사본이다

이게 무슨 뜻이냐면, UPDATE 한 번이 사실상 DELETE + INSERT라는 거다.

구체적으로 뭐가 문제인가

음식점 주문 시스템을 예시로 들어보자.

-- 주문 상태를 바꾸는 아주 흔한 쿼리
UPDATE orders SET status = 'delivered' WHERE id = 42;

이 한 줄의 쿼리가 실제로 만드는 I/O가 얼마나 다른지 비교해보면:

MySQL

1. undo log에 diff 기록 (status: 'shipped' → 'delivered')
2. 테이블의 기존 row를 in-place 수정
3. status 컬럼에 인덱스가 있으면, 그 인덱스만 업데이트
   (다른 인덱스는 건드리지 않음)

PostgreSQL

1. 기존 row의 xmax를 현재 트랜잭션 ID로 마킹 (논리적 삭제)
2. row 전체를 복사해서 새 tuple 생성 (status만 바뀌었어도 전체 복사)
3. 모든 인덱스를 업데이트 (변경되지 않은 컬럼의 인덱스도 전부!)
   - orders_pkey (id)
   - idx_orders_user_id (user_id)
   - idx_orders_status (status)
   - idx_orders_created_at (created_at)
   → 인덱스가 4개면 4개 전부 새 tuple을 가리키도록 업데이트

이게 바로 **Write Amplification(쓰기 증폭)**이다.

status 컬럼 하나를 바꿨을 뿐인데, PostgreSQL은 row 전체를 복사하고 모든 인덱스를 업데이트한다. row에 큰 VARCHAR 컬럼이 있다면? 1바이트 바꾸려고 수 KB를 새로 쓰는 꼴이 된다.

MySQL은 바뀐 컬럼만 diff로 기록하고, 바뀐 컬럼에 걸린 인덱스만 업데이트하면 끝이다.

Dead Tuple과 VACUUM

PostgreSQL의 UPDATE가 “DELETE + INSERT"라는 건, 업데이트할 때마다 dead tuple이 쌓인다는 뜻이다.

시간 →

UPDATE 1회 후:  live 1개, dead 1개
UPDATE 5회 후:  live 1개, dead 5개
UPDATE 100회 후: live 1개, dead 100개

테이블 크기: 계속 커짐 📈

이 dead tuple들은 아무도 안 읽지만 디스크 공간을 차지하고, sequential scan 할 때 전부 훑어야 한다. 이걸 “bloat"라고 부른다.

MySQL은? undo log에 diff만 쌓이고, purge 스레드가 알아서 정리한다. 테이블 자체는 깨끗하다.

그래서 PostgreSQL에는 VACUUM이라는 프로세스가 필요하다.

VACUUM이 하는 일:
1. 테이블을 스캔해서 dead tuple을 찾는다
2. dead tuple이 차지하는 공간을 "재사용 가능"으로 표시한다
3. (VACUUM FULL이면) 실제로 디스크 공간을 OS에 반환한다

주의: 일반 VACUUM은 공간을 "재사용 가능"으로만 표시한다.
      디스크에서 실제로 줄어들지는 않는다!
      줄이려면 VACUUM FULL이 필요한데, 이건 테이블 전체를 잠근다.

autovacuum이라는 백그라운드 프로세스가 자동으로 돌긴 하지만, write가 많은 워크로드에서는 dead tuple 생성 속도를 autovacuum이 못 따라가는 상황이 생긴다. 그러면 테이블이 걷잡을 수 없이 부풀어오른다.

HOT Update: PostgreSQL의 자구책

PostgreSQL도 이 문제를 알고 있어서, 8.3 버전부터 HOT(Heap Only Tuple) Update라는 최적화를 도입했다.

HOT이 작동하는 조건:

  1. 인덱스가 걸린 컬럼이 변경되지 않았을 때
  2. 같은 page에 새 tuple을 저장할 공간이 있을 때

이 두 조건이 만족되면:

HOT Update:
- 새 tuple을 같은 page에 만든다
- 인덱스를 업데이트하지 않는다! (핵심)
- 기존 line pointer → 새 tuple로 redirect
- dead tuple도 SELECT 시점에 page 단위로 정리 가능

일반 Update:
- 새 tuple이 다른 page에 갈 수도 있다
- 모든 인덱스를 전부 업데이트해야 한다
- dead tuple은 VACUUM이 와야 정리된다

그래서 PostgreSQL에서 UPDATE가 많은 테이블을 설계할 때는:

  • 자주 바뀌는 컬럼에 인덱스를 거는 걸 신중하게 생각해야 한다
  • fillfactor를 낮춰서 (70% 정도) 같은 page에 여유 공간을 확보해야 한다
-- fillfactor를 70%로 설정해서 HOT update 가능성을 높이기
ALTER TABLE orders SET (fillfactor = 70);

모니터링도 가능하다:

SELECT n_tup_upd, n_tup_hot_upd
FROM pg_stat_user_tables
WHERE relname = 'orders';

-- n_tup_hot_upd / n_tup_upd 비율이 높을수록 좋다

하지만 HOT은 조건이 까다롭다. 인덱스 걸린 컬럼이 하나라도 바뀌면 HOT이 안 된다. 현실적으로 인덱스를 아예 안 거는 건 불가능하니까, HOT만으로 write amplification 문제가 완전히 해결되지는 않는다.

그래서 PostgreSQL은 “쓰기가 비싼” DB인가?

트레이드오프를 정리하면 이렇다:

                     PostgreSQL              MySQL (InnoDB)
─────────────────────────────────────────────────────────────
UPDATE 비용         높음 (전체 row 복사       낮음 (in-place 수정
                    + 모든 인덱스 업데이트)    + 변경 인덱스만 업데이트)

READ 비용           낮음 (모든 버전이          약간 높음 (과거 버전은
                    테이블에 있어서            undo log를 따라가며
                    재구성 불필요)             재구성 필요)

옛날 버전 정리      VACUUM 필요               purge 스레드가 자동 처리
                    (튜닝 필요)               (거의 신경 안 써도 됨)

bloat 위치          테이블 자체에 쌓임         undo tablespace에 쌓임
                    (성능 영향 큼)             (테이블은 깨끗)

ROLLBACK 비용       거의 없음 (옛날 버전이     있음 (undo log에서
                    이미 테이블에 있으니까)    다시 복원해야 함)

PostgreSQL의 설계는 읽기에 유리하고, 쓰기에 불리하다. 과거 버전이 테이블에 그대로 있으니 읽기 트랜잭션은 undo log를 따라갈 필요 없이 바로 읽으면 된다. 반면 쓰기는 매번 full copy + 인덱스 전체 업데이트라는 비용을 지불한다.

MySQL은 반대다. 쓰기는 가볍지만, 오래된 버전을 읽으려면 undo log를 타고 올라가면서 row를 재구성해야 한다.

실전에서 뭘 신경써야 하나

PostgreSQL을 쓰면서 write-heavy 워크로드를 다룬다면:

첫째, autovacuum 튜닝은 필수다. 기본값은 보수적으로 잡혀 있어서, 쓰기가 많은 테이블에서는 dead tuple이 쌓이는 속도를 못 따라간다.

-- write가 많은 테이블에 대해 autovacuum을 공격적으로 설정
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,     -- 기본값 0.2 → 0.01
    autovacuum_vacuum_cost_delay = 2,          -- 기본값 2ms (최신 버전)
    autovacuum_vacuum_cost_limit = 1000        -- 기본값 200
);

둘째, 인덱스 설계를 신중하게. 인덱스가 많을수록 write amplification이 심해진다. “혹시 필요할까봐” 거는 인덱스가 PostgreSQL에서는 MySQL보다 훨씬 비싸다.

셋째, fillfactor 조정으로 HOT update 비율을 높인다. UPDATE가 잦은 테이블은 fillfactor를 70-80% 정도로 낮추면 같은 page에서 HOT update가 될 확률이 올라간다.

넷째, pg_stat_user_tables를 모니터링한다. n_dead_tup이 계속 올라가고 있다면 autovacuum이 못 따라가고 있다는 신호다.

정리

PostgreSQL과 MySQL은 같은 MVCC라는 이름을 쓰지만, 구현이 근본적으로 다르다.

MySQL은 “테이블은 깨끗하게, 히스토리는 따로” 전략이다. UPDATE가 가볍고, 정리도 purge 스레드가 알아서 한다. 대신 과거 버전을 읽으려면 undo log를 재구성하는 비용이 있다.

PostgreSQL은 “모든 버전을 테이블에 같이” 전략이다. 읽기는 빠르지만, UPDATE마다 전체 row 복사 + 모든 인덱스 업데이트가 발생한다. 그리고 쌓이는 dead tuple을 치우기 위해 VACUUM이라는 별도의 메커니즘이 필요하다.

어떤 DB가 더 낫다는 게 아니다. PostgreSQL이 “쓰기에 비싸다"는 것은 사실이지만, 그 대가로 읽기 성능과 트랜잭션 격리에서 이점을 얻는다. PostgreSQL의 SERIALIZABLE 격리 수준은 reader를 절대 block하지 않는다 - MySQL의 gap locking과는 다른 접근이다.

결국 핵심은, PostgreSQL을 쓸 거면 VACUUM을 이해하고, write amplification을 의식하고, 인덱스 설계에 더 신경 쓰는 것이다. 도구의 특성을 모르고 쓰면 어떤 DB든 느리다.