[PostgreSQL 3/12] MVCC와 HOT: 한 행의 여러 버전은 어떻게 연결될까?

About this offer

이 글에서 다룰 주제 가시성: 동시에 접근하는 두 세션은 어떤 버전을 보는가? 저장 구조: 행 버전과 페이지의 주소는 어떻게 연결되는가? 업데이트 최적화: HOT·fillfactor·pruning은 어떤 비용을 줄이는가? 주요 단어 · MVCC · Snapshot · xmin/xmax · Heap Page · ctid · HOT · fillfactor · Pruning 테이블의 행 하나를 수정했는데 내부에는 같은 논리 행의 여러 버전이 남을 수 있다. 이는 불필요한 복사만을 뜻하지 않는다. 다른 트랜잭션이 자신의 스냅샷에 맞는 데이터를 읽기 위해 필요한 구조다. 이번에는 보이는 행과 물리적으로 저장된 버전을 구분한 뒤, HOT가 인덱스 유지 비용을 줄이는 방법까지 따라간다. MVCC 는 여러 행 버전과 스냅샷으로 가시성을 판단하는 방식이다. HOT 는 같은 페이지의 새 버전에 일반 인덱스 엔트리를 추가하는 일을 줄이는 UPDATE 최적화다. 자료와 예제 기준 — PostgreSQL 18을 중심으로 개인 학습 노트를 재구성했다. SQL·실행 계획·설정값은 설명 및 재현용 예제이며 이 글을 위해 운영 DB에서 새로 측정한 결과는 아니다. DDL/DML 예제는 독립적인 테스트 환경에서 사용한다. 1. 스냅샷과 다른 DB의 버전 관리 비교 트랜잭션과 MVCC 트랜잭션은 여러 변경을 함께 커밋하거나 롤백하는 경계이다. MVCC는 행 버전과 스냅샷을 이용해 동시 접근에서 어떤 변경을 볼 수 있는지 결정한다. 스냅샷은 데이터베이스 전체 복사본이 아니다. 트랜잭션 ID 범위와 진행 중 트랜잭션 등의 정보를 이용하는 가시성 판단 기준이다. 일반 SELECT와 UPDATE 사이의 행 잠금 경합은 줄지만, 같은 행의 동시 쓰기와 명시적 잠금은 여전히 대기하거나 충돌할 수 있다. PostgreSQL과 InnoDB의 가장 큰 차이 항목 PostgreSQL MySQL InnoDB 기본 행 저장 Heap 클러스터드 인덱스 리프 UPDATE 새 튜플 버전 생성 현재 레코드 수정 + Undo 기록 이전 버전 Heap의 이전 튜플 Undo로 이전 상태 재구성 가시성 메타데이터 xmin·xmax 등 DB_TRX_ID·DB_ROLL_PTR 등 정리 VACUUM·페이지 pruning Purge 긴 스냅샷의 영향 과거 튜플 회수 지연 필요한 Undo 회수 지연 이는 대표적인 저장 원리를 단순화한 비교다. 기본 키 변경 등 모든 수정이 InnoDB에서 동일한 물리적 in-place 갱신으로 끝나는 것은 아니다. PostgreSQL 예시 트랜잭션 100이 pending 을 넣고 200이 completed 로 바꾼다고 가정한다. 튜플 status xmin xmax 이전 버전 pending 100 200 새 버전 completed 200 0 xmin: 이 행 버전을 만든 트랜잭션. xmax: 삭제·교체 또는 잠금 관련 정보를 표현하는 필드. 0이 아니라고 무조건 삭제된 행은 아니다. ctid: 튜플의 물리적 위치. UPDATE 등으로 바뀔 수 있으므로 업무 식별자로 쓰지 않는다. 가시성은 숫자 크기만으로 판단하지 않고 커밋 상태·스냅샷·자기 트랜잭션 변경 등을 고려한다. InnoDB 예시 현재 행의 DB_TRX_ID로 가시성을 판단하고 필요하면 DB_ROLL_PTR가 가리키는 Undo 정보를 적용해 이전 행을 재구성한다. Undo는 반드시 과거 행 전체의 복사본인 것은 아니다. InnoDB 보조 인덱스는 클러스터드 레코드와 다르게 관리된다. 인덱스 키 변경 시 이전 항목에 삭제 표시를 하고 새 항목을 넣는다. MVCC 확인 때문에 클러스터드 레코드와 Undo 접근이 추가로 필요할 수 있다. 기본 격리 수준도 다르다 격리 수준 PostgreSQL InnoDB 기본값 Read Committed Repeatable Read Read Committed 일반 조회 문장마다 새 스냅샷 consistent read마다 새 스냅샷 Repeatable Read 첫 비제어 문장 시점의 스냅샷 유지 첫 consistent read 시점의 Read View 유지 두 DB 모두 자기 트랜잭션의 변경은 볼 수 있다. InnoDB의 WITH CONSISTENT SNAPSHOT 같은 명시적 시작 방식은 별도 조건이 있다. 반복 조회 예시 순서 A B 1 BEGIN 2 SELECT → pending 3 UPDATE → completed, COMMIT 4 같은 SELECT 기본 설정에서 A의 두 번째 일반 SELECT는 PostgreSQL에서는 completed, InnoDB에서는 pending을 볼 수 있다. PostgreSQL도 RR로 바꾸면 이 예제에서는 pending을 유지한다. 이는 저장 구조 자체보다 격리 수준의 차이이다. RR의 쓰기 동작까지 같지는 않다. PostgreSQL에서는 스냅샷 이후 다른 트랜잭션이 수정·커밋한 행을 갱신하려다 serialization failure가 발생할 수 있다. InnoDB의 잠금 조회·UPDATE는 일반 consistent read와 다른 방식으로 최신 상태를 대상으로 동작할 수 있고, 조건에 따라 next-key lock을 사용한다. 2. 같은 행, 서로 다른 버전 학습 자료의 개념도 — 행 버전과 스냅샷. 세부 조건은 본문 설명을 함께 읽는다. 예시는 성공한 UPDATE와 서로 다른 스냅샷을 단순화한 것이다. 행 버전 progress xmin xmax 예시 가시성 V1 10 100 200 업데이트 전 스냅샷에서 보일 수 있음 V2 50 200 0 트랜잭션 200의 커밋을 보는 스냅샷에서 보일 수 있음 트랜잭션 200이 성공적으로 UPDATE한 단순 예시다. 실제 가시성은 커밋 여부, 스냅샷, 명령 순서 등을 함께 판단한다. xmax != 0 은 무조건 삭제되었다는 뜻이 아니다. 행 잠금에도 사용될 수 있다. xmin / xmax — xmin은 생성 트랜잭션, xmax는 삭제·대체 또는 잠금 관련 정보다. 숫자만으로 가시성을 판단하지 않는다. 격리 수준 — READ COMMITTED는 문장마다 스냅샷을 사용한다. REPEATABLE READ는 트랜잭션 스냅샷을 유지한다. 읽기와 쓰기 — 일반 SELECT와 UPDATE의 충돌을 줄인다. 같은 행을 수정하는 트랜잭션끼리는 기다릴 수 있다. 기억할 문장: 오래된 버전 ≠ 즉시 제거 가능한 버전. 가시성 판단이 먼저다. 3. 8KB 페이지 안의 주소록 Heap 슬롯(line pointer)과 실제 tuple 데이터는 서로 다른 공간이다. 페이지 구성 역할 Page Header 일반적으로 24바이트. LSN·빈 공간 경계 등 ItemIdData / line pointer 슬롯당 4바이트. 위치·길이·상태 Free Space 슬롯과 tuple을 배치할 여유 공간 Tuple 데이터 행 헤더와 컬럼 값 인덱스의 TID (10,1) 은 페이지 10의 슬롯 1을 뜻한다. 해당 슬롯이 실제 tuple의 바이트 위치를 가리킨다. ctid 는 현재 행 버전의 물리 위치를 나타내는 시스템 컬럼이다. t_ctid 는 tuple 헤더에 있는 자기 자신 또는 후속 버전의 위치 정보로 구분한다. 슬롯 — 위치·길이·상태를 담는다. 인덱스는 페이지 번호와 슬롯 번호로 heap에 접근한다. 헤더 — tuple에는 xmin·xmax·t_ctid·infomask 등이 있다. HOT와 HHU는 tuple 헤더 플래그다. 주소 재사용 — ctid는 UPDATE나 재작성으로 바뀌고 슬롯도 재사용된다. 업무상 식별에는 PK를 사용한다. 기억할 문장: 슬롯은 주소록, tuple은 데이터. 둘의 수명은 같지 않을 수 있다. 4. fillfactor는 UPDATE의 여유 공간 INSERT 때 얼마나 채울지 정한다. 페이지 점유율의 영구적인 상한이 아니다. 설정 INSERT 시 목표 UPDATE에 미치는 영향 fillfactor=100 페이지를 최대한 채움 같은 페이지 여유가 부족할 수 있음 fillfactor=80 대략 20%를 여유로 남김 새 버전을 같은 페이지에 배치할 기회 증가 HOT는 새 버전이 같은 페이지에 들어가고 일반 인덱스가 참조하는 컬럼의 값이 변경되지 않는 조건에서 가능하다. BRIN 같은 요약 인덱스는 예외가 있다. 표현식 인덱스의 입력 컬럼, 부분 인덱스 조건, INCLUDE 컬럼도 검토해야 한다. UPDATE가 여유 공간을 사용하므로 실제 점유율이 fillfactor를 넘을 수 있다. HOT의 두 조건 — 페이지 공간뿐 아니라 인덱스 참조 컬럼의 변경 여부도 맞아야 한다. BRIN 등은 별도 예외가 있다. 공간과 성능의 교환 — 낮은 fillfactor는 UPDATE에 유리할 수 있지만, 초기 페이지 수와 읽기·캐시 비용이 늘어난다. 기존 데이터 — ALTER TABLE ... SET (fillfactor=80)만으로 기존 페이지가 즉시 재배치되거나 bloat가 사라지지 않는다. 기억할 문장: 여유 공간 확보 + 인덱스 조건 충족 → HOT 가능성 증가. 5. 인덱스 하나로 버전을 따라간다 학습 자료의 개념도 — HOT chain과 tuple 플래그. 세부 조건은 본문 설명을 함께 읽는다. 모두 커밋되었고 pruning 전인 HOT chain의 단순화된 예시 같은 heap 페이지에서 두 번 HOT 업데이트가 성공했고 아직 pruning 전인 예시다. 플래그 비트 값 의미 HEAP_HOT_UPDATED / HHU 0x4000 해당 버전에서 HOT 업데이트가 일어남 HEAP_ONLY_TUPLE / HOT 0x8000 별도 일반 인덱스 엔트리 없이 생성된 heap-only 버전 V1은 원래 인덱스 진입점이다. V2는 HOT로 생성되었고 다시 HOT 업데이트되었으므로 두 플래그가 모두 1이다. V3의 t_ctid는 이 예시에서 자기 자신을 가리킨다. 플래그만으로 커밋 여부나 가시성을 확정하지 않는다. Heap-Only Tuple — heap에는 새 버전을 만들지만 일반 인덱스의 새 엔트리를 생략한다. WAL·이전 버전은 여전히 생긴다. 가시성 — 인덱스에서 진입한 뒤 HOT chain에서 스냅샷에 보이는 버전을 찾는다. 무조건 최신 버전은 아니다. 중간 버전 — V2는 HOT로 생성되고 다시 HOT 업데이트되었으므로 HHU와 HOT가 모두 1일 수 있다. 기억할 문장: HHU는 후속 연결, HOT는 heap-only 버전이라는 표시다. 6. 데이터는 지워도 진입점은 남긴다 학습 자료의 개념도 — 페이지 슬롯의 네 상태. 세부 조건은 본문 설명을 함께 읽는다. V1·V2는 더 이상 필요 없고 V3는 살아 있다고 가정한다. 공간 Pruning 전 Pruning 후 인덱스 엔트리 슬롯 1 참조 그대로 유지 슬롯 1 NORMAL → V1 REDIRECT → 슬롯 3 슬롯 2 NORMAL → V2 UNUSED 슬롯 3 NORMAL → V3 그대로 유지 V1·V2 데이터 공간 점유 회수 V1·V2를 어떤 트랜잭션도 더 이상 필요로 하지 않는다고 가정한다. V3로 가는 경로가 필요한 동안 VACUUM도 인덱스 진입점과 redirect를 없애지 않는다. chain 전체가 회수 가능해지면 인덱스 참조와 root 슬롯까지 정리할 수 있다. 한 페이지 안에서 — pruning은 페이지 내부에서 불필요한 버전과 HOT 연결을 정리한다. 인덱스를 직접 청소하지 않는다. REDIRECT — 옛 tuple의 t_ctid가 아니다. V1 데이터는 없어지고 슬롯 자체가 필요한 후속 슬롯을 가리킨다. VACUUM과의 관계 — VACUUM도 pruning을 수행한다. 둘은 경쟁하는 대안이 아니라 범위가 다른 유지보수 작업이다. 기억할 문장: 인덱스를 수정하지 않고 페이지 안에서 재사용 가능한 공간을 만든다. 7. NORMAL은 “살아 있음”이 아니다 슬롯의 물리 상태와 tuple의 MVCC 가시성을 구분하자. 상태 담고 있는 의미 재사용 NORMAL 실제 tuple 데이터 위치·길이 아직 불가 REDIRECT 같은 페이지의 다른 슬롯 번호 불가 DEAD 유효 버전은 없지만 참조 정리가 필요한 슬롯 아직 불가 UNUSED 사용하지 않는 슬롯 가능 인덱스가 있는 일반 heap에서의 대표 경로다. 실제 구현은 인덱스 유무와 최적화에 따라 중간 단계가 합쳐질 수 있다. 슬롯 상태는 HHU/HOT tuple 헤더 플래그와 별개다. NORMAL — tuple 데이터가 있다는 뜻이다. 오래된 버전이나 삭제된 버전도 아직 NORMAL일 수 있다. DEAD — DELETE 직후 자동으로 바뀌지 않는다. 회수 가능해진 뒤 pruning 등의 단계에서 나타난다. UNUSED — 페이지 안에서 슬롯을 다시 쓸 수 있다는 뜻이다. 파일 공간을 OS에 돌려줬다는 뜻은 아니다. 기억할 문장: 슬롯 상태: 어디를 가리키나? / MVCC: 누구에게 보이나? 8. 두 세션으로 가시성 관찰하기 격리된 환경에서 해 보는 실습 1 MVCC·Rollback을 두 세션으로 관찰 아래는 교육용 DB에서 실행한다. ORM Flush가 전송한 UPDATE를 SQL로 직접 관찰하는 실습이다. CREATE TABLE study_orders ( id bigint PRIMARY KEY, status text NOT NULL ); INSERT INTO study_orders VALUES (42, 'PENDING'); 세션 A: BEGIN; UPDATE study_orders SET status = 'PAID' WHERE id = 42; SELECT id, status, xmin, xmax FROM study_orders WHERE id = 42; -- 자기 변경인 PAID를 확인. 아직 COMMIT하지 않는다. 세션 B: SELECT id, status, xmin, xmax FROM study_orders WHERE id = 42; -- 일반 SELECT는 미커밋 PAID가 아닌 PENDING을 조회한다. 세션 A: ROLLBACK; 세션 B: SELECT id, status, xmin, xmax FROM study_orders WHERE id = 42; -- PENDING. 시스템 컬럼 값만 보고 커밋·삭제를 단정하지 않는다. 2 오래된 스냅샷 확인 세션 B에서 먼저 실행: BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT status FROM study_orders WHERE id = 42; -- 이 SELECT로 스냅샷 확보 세션 A: BEGIN; UPDATE study_orders SET status = 'PAID' WHERE id = 42; COMMIT; 세션 B: SELECT status FROM study_orders WHERE id = 42; -- 기존 스냅샷: PENDING COMMIT; SELECT status FROM study_orders WHERE id = 42; -- 새 스냅샷: PAID 일반 SELECT는 모든 물리 버전을 나열하지 않는다. Tuple header의 원시 상태나 WAL 자체를 살피려면 별도의 진단 도구와 권한이 필요하다. 자료 기준과 참고 문서 개인 PostgreSQL 학습 노트를 바탕으로 정리했다. 첨부 그림은 제공된 학습 자료를 사용했으며, 버전이나 설정에 따른 조건은 본문에 덧붙였다. 격리 수준 페이지 구조 HOT 시스템 컬럼 MySQL InnoDB MVCC 이어서 읽기 · ← 이전 편 · 다음 편 → · 전체 시리즈 목차