MySQL vs PostgreSQL 2편: UPDATE의 나비효과

MySQL vs PostgreSQL 1편에서는 MySQL의 Index-Organized Table(IOT)과 PostgreSQL의 Heap Table 구조를 비교하며, 데이터를 저장하고 읽는(Read) 방식의 근본적인 차이를 알아보았습니다.

 

PostgreSQL은 무거운 데이터를 억지로 정렬하지 않고 힙(Heap) 공간에 마구잡이로 던져 넣은 뒤, 인덱스에서 물리적 주소(TID)로 다이렉트로 꽂아버리는 방식을 택했습니다. 읽기(Read) 관점에서는 트리를 두 번 타야 하는 MySQL보다 구조적으로 훨씬 명쾌하고 빨라 보였죠.

 

하지만 데이터베이스의 진짜 숙명은 조회가 아니라 끊임없는 '변경(UPDATE)'에 있습니다. 오늘은 데이터가 변경되는 순간, 평화롭던 PostgreSQL의 힙 구조에 어떤 거대한 나비효과가 발생하는지, 그리고 글로벌 테크 기업 우버(Uber)는 왜 이 문제 때문에 MySQL로 마이그레이션했는지 파헤쳐 보겠습니다.

---

수정(UPDATE) 차이: 덮어쓰기 vs 덧붙이기

MySQL, PostgreSQL 두 DB는 데이터를 업데이트할 때 과거 버전을 다루는 방식(MVCC)에서 완전히 다른 길을 선택했습니다.

MySQL (InnoDB)

제자리 덮어쓰기 (In-place Update)

Clustered Index Leaf Node

값: 'A' → 값: 'B'

(PK 위치 변함 없음. 기존 자리에 덮어씀)

과거 데이터 밀어내기

Undo Log (언두 로그)

보관된 과거 데이터: 'A'

PostgreSQL

덧붙이기 (Append-Only)

8KB Heap Page (힙 영역)

과거 튜플 (TID: 100)

값: 'A'

(삭제하지 않고 그대로 방치)

 

[NEW] 신규 튜플 (새로운 TID: 105)

값: 'B'

(빈 공간에 새로운 주소로 생성)

 

• MySQL (InnoDB): 제자리 덮어쓰기
  MySQL은 데이터를 업데이트할 때 기본적으로 클러스터링 인덱스 리프 노드의 로컬 데이터만 새로운 값으로 덮어씁니다. 덮어쓰기 전의 '과거 데이터'는 언두 로그(Undo Log)라는 별도의 공간에 보존하며, 논리적인 PK(프라이머리 키) 값은 동일하게 유지됩니다.
• PostgreSQL: 덧붙이기 (Append-Only)
  PostgreSQL의 데이터 적재 방식은 본질적으로 추가 전용(Append-only) 메커니즘에 가깝습니다. 레코드를 업데이트하면 기존 값을 치환하지 않고, 힙의 새로운 빈 공간에 새로운 버전의 튜플을 생성하며 완전히 새로운 물리적 주소(TID)를 발급합니다.

---

쓰기 증폭(Write Amplification)과 우버(Uber) 사태

PostgreSQL의 직관적인 '덧붙이기' 방식은 쓰기 작업에서 치명적인 연쇄 작용을 일으킵니다. 인덱스와 전혀 상관없는 컬럼 하나를 변경했을 때의 상황을 가정해 봅시다.

• MySQL: 데이터는 제자리에 덮어써지고 논리적 주소(PK)는 불변하므로, 테이블에 달린 어떠한 세컨더리 인덱스 B+Tree도 수정할 필요가 없습니다.
• PostgreSQL: 최신 튜플을 힙 공간에 새로 만들면서 물리적 위치(TID)가 바뀌었으므로, 테이블에 걸려있던 모든 세컨더리 인덱스의 TID 포인터들을 전부 새 주소로 갱신해야 합니다.

이처럼 단 하나의 데이터를 바꿨을 뿐인데 수많은 인덱스가 출렁이며 디스크 I/O가 폭발하는 현상을 '쓰기 증폭(Write Amplification)'이라고 부릅니다. 실제로 글로벌 플랫폼 우버(Uber)는 위치 정보가 초당 수천 번씩 업데이트되는 환경에서 이 쓰기 증폭과 복제(Replication) 대역폭 마비 문제를 견디지 못하고 PostgreSQL에서 MySQL로 인프라를 대대적으로 이전했습니다. 워낙 유명하고 지금까지도 토론이 이어지는 사건이죠.

---

HOT 업데이트와 인덱스 설계의 딜레마

PostgreSQL 코어 개발자들도 이 딜레마를 완화하기 위해 HOT (Heap-Only Tuple) 기능을 도입했습니다. 인덱스가 걸려있지 않은 컬럼을 업데이트할 때, 신규 튜플을 기존 튜플과 동일한 8KB 페이지 안에 삽입할 수 있다면 세컨더리 인덱스들을 일절 업데이트하지 않습니다. 기존 튜플의 헤더를 조작해 내부적인 리다이렉션 링크(lp_flags=LP_REDIRECT)를 형성하기 때문입니다.'

🔥 HOT (Heap-Only Tuple) 업데이트 원리

인덱스 수정 없이, 8KB 페이지 내부에서 이정표(Line Pointer)만 꺾어서 최신 데이터를 찾아갑니다.

세컨더리 인덱스

여전히 기존 주소 기억
TID: [Block, LP 1]

→

8KB Heap Page (동일한 페이지)

상단: Line Pointers (이정표)

LP 1 (기존) LP_REDIRECT ⤵

LP 2 (신규) 데이터 가리킴 ⬇

( 빈 공간 / Free Space )

하단: Heap Tuples (실제 데이터)

✨ 최신 데이터 (Tuple 2)

과거 데이터 (Tuple 1)

하지만 실무에서 반드시 명심해야 할 두 가지 치명적인 한계가 있습니다.

• 페이지 내 빈 공간 필수: HOT 업데이트가 동작하려면 8KB 페이지 내부에 새로운 데이터를 수용할 빈 공간이 남아있어야 합니다. 따라서 잦은 업데이트가 예상되는 테이블은 fillfactor 비율을 80~90%로 의도적으로 낮추어 설계해야 합니다.
• 인덱스 컬럼 업데이트 시 HOT 무효화: 인덱스가 걸린 컬럼을 수정하면 인덱스 트리 내의 위치가 바뀌어야 하므로 HOT 마법이 즉시 깨집니다. 결국 힙에 새로운 TID가 발급되고 모든 세컨더리 인덱스가 갱신되는 쓰기증폭이 어김없이 다시 발생합니다.

---

Append 된 데이터를 청소하는 프로세스, VACCUM

PostgreSQL이 Leaf Node 에 실제 데이터는 Append Only 로 관리한다는 사실과 이런 Leaf Node는 실제로는 순서를 모르고, 인덱스 때문에 TID가 따로 관리된다는 사실을 확인했습니다. 그렇다면 한가지 의문이 남습니다. 계속 Append 되기만 하면 더이상 접근할 필요가 없는 데이터는 어떻게 되는거지?

 

MySQL은 언두 로그(Undo Log)라는 별도의 휴지통을 사용하며 알아서 덮어쓰고 비워내지만, PostgreSQL은 실제 데이터가 사는 본진(Heap) 구석구석에 이 과거 데이터들이 남겨져 있으니까 의문이 들 수 밖에 없습니다. 이를 방치하면 테이블의 물리적 크기가 무한정 비대해지겠죠. 그리고 데이터를 스캔할때 디스크에서 퍼올려야하는 Page(8KB)가 굉장히 많아져서 극심한 읽기 성능 저하가 일어날겁니다. 

 

이 쓰레기 다중 버전 튜플들을 주기적으로 스캔하여 빈 공간(Free Space)을 다시 확보하여 FSM(Free Space Map)에 반환하는 백그라운드 프로세스가 바로 VACUUM (Autovacuum)입니다.

 

결국 PostgreSQL의 힙(Heap) 구조는 단건 쓰기와 읽기에 매우 유연하고 빠르지만, 그 이면에는 fillfactor를 깎아 HOT 업데이트를 유도하고, `VACUUM`이 부지런히 돌아가며 쓰레기를 치워줘야만 성능이 유지되는 '섬세한 유지보수'가 반드시 동반되어야 하는 아키텍처인 것입니다.

---

마무리하며

B+Tree 구조에서부터 시작해서, MySQL 과 PostgreSQL의 데이터 저장방식을 알아보고 데이터 업데이트에 따른 차이도 알아봤습니다. 이런 공부를 통해 백엔드 개발을 하면서 단순히 ORM만 다룰때는 보이지 않던 것들이 보이게 되는것 같습니다.

 

이처럼 RDBMS의 스토리지 구조를 완벽히 이해하고 있다면, 서비스 트래픽(읽기/쓰기 비율)에 맞는 최적의 아키텍처를 주도적으로 설계할 수 있게 됩니다. 회사에서 주로 PostgreSQL을 이용하는데, 쓰기 증폭을 고려해서 인덱스를 설계해야겠다는 생각이 듭니다.