PostgreSQL의 데이터 관리 (MVCC와 Dead Tuple)
- MVCC(Multi-Version Concurrency Control): PostgreSQL이 데이터를 수정/삭제하는 메커니즘.
- Dead Tuple의 발생 원인:
UPDATE와DELETE이후 남겨진 파편들. - HOT(Heap-Only Tuple) 업데이트: 인덱스 업데이트 없이 같은 페이지 내에서 처리되는 효율적인 UPDATE 메커니즘.
- Table Bloat(비대화): 물리적 크기가 비정상적으로 늘어나는 이유와 서비스에 미치는 영향.
1.1 MVCC(Multi-Version Concurrency Control)
MVCC의 핵심 원리
PostgreSQL은 데이터를 직접 수정하지 않고, 새로운 버전을 만듭니다.
-- Session A
BEGIN;
UPDATE accounts SET balance = 900 WHERE id = 1;
-- 기존 행을 수정하는 게 아니라, 새 행을 추가
-- Session B (동시에 실행)
SELECT balance FROM accounts WHERE id = 1;
-- Session A의 작업과 상관없이 즉시 실행됨
-- 기존 버전(1000)을 읽음핵심: 여러 버전이 동시에 존재하고, 각 트랜잭션은 자신의 시점에 맞는 버전을 봅니다.
Tuple의 시스템 컬럼
PostgreSQL의 모든 행에는 사용자가 정의하지 않은 숨겨진 컬럼들이 있습니다.
CREATE TABLE users (id int, name text);
INSERT INTO users VALUES (1, 'Alice');
-- 시스템 컬럼 확인
SELECT xmin, xmax, ctid, id, name FROM users; xmin | xmax | ctid | id | name
------+------+-------+----+-------
100 | 0 | (0,1) | 1 | Alicestruct HeapTupleHeaderData
{
union
{
HeapTupleFields t_heap;
DatumTupleFields t_datum;
} t_choice;
ItemPointerData t_ctid; /* current TID of this or newer tuple (or a
* speculative insertion token) */
/* Fields below here must match MinimalTupleData! */
uint16 t_infomask2; /* number of attributes + various flags */
uint16 t_infomask; /* various flag bits, see below */
uint8 t_hoff; /* sizeof header incl. bitmap, padding */
/* ^ - 23 bytes - ^ */
bits8 t_bits[FLEXIBLE_ARRAY_MEMBER]; /* bitmap of NULLs */
/* MORE DATA FOLLOWS AT END OF STRUCT */
};
xmin (트랜잭션 생성 번호)
- 이 행을 만든 트랜잭션의 ID
xmax (트랜잭션 삭제 번호)
- 이 행을 삭제하거나 수정한 트랜잭션의 ID
- 0이면 아직 유효한 행
ctid (물리적 위치)
- (페이지 번호, 행 번호)
- 디스크에서 이 행이 저장된 위치
- UPDATE 시 새 위치로 이동
트랜잭션 ID (XID)
PostgreSQL은 각 트랜잭션에 순차적으로 번호를 부여합니다.
SELECT txid_current();
-- 결과: 1000
BEGIN;
SELECT txid_current();
-- 결과: 1001
INSERT INTO test VALUES (1);
-- 이 행의 xmin은 1001이 됨
COMMIT;
SELECT txid_current();
-- 결과: 1002 (새 트랜잭션)XID는 시간의 흐름을 나타냅니다:
- XID=100보다 XID=200이 나중
- 작은 번호 = 과거, 큰 번호 = 미래
MVCC 동작 과정
1단계: INSERT
-- 트랜잭션 100
BEGIN;
INSERT INTO users VALUES (1, 'Alice');
COMMIT;페이지 내부 상태:
Page 0:
Slot 1: xmin=100, xmax=0, ctid=(0,1) → (1, 'Alice')- xmin=100: 트랜잭션 100이 생성
- xmax=0: 아직 삭제되지 않음
- ctid=(0,1): 0번 페이지, 1번 슬롯
2단계: UPDATE
-- 트랜잭션 101
BEGIN;
UPDATE users SET name = 'Bob' WHERE id = 1;
COMMIT;페이지 내부 상태:
Page 0:
Slot 1: xmin=100, xmax=101, ctid=(0,2) → (1, 'Alice') [이전 버전]
Slot 2: xmin=101, xmax=0, ctid=(0,2) → (1, 'Bob') [새 버전]무슨 일이 일어났나?
-
Slot 1 (Alice):
- xmax가 0에서 101로 변경됨
- "트랜잭션 101이 나를 수정했다"
- ctid가 (0,2)를 가리킴 ("새 버전은 Slot 2에 있다")
-
Slot 2 (Bob):
- 새로운 행이 생성됨
- xmin=101 ("트랜잭션 101이 나를 만들었다")
- xmax=0 ("아직 유효하다")
중요: Alice와 Bob이 동시에 페이지에 존재합니다!
3단계: DELETE
-- 트랜잭션 102
BEGIN;
DELETE FROM users WHERE id = 1;
COMMIT;페이지 내부 상태:
Page 0:
Slot 1: xmin=100, xmax=101, ctid=(0,2) → (1, 'Alice') [이전 버전]
Slot 2: xmin=101, xmax=102, ctid=(0,2) → (1, 'Bob') [삭제됨]- Slot 2의 xmax가 102로 설정됨
- 물리적으로는 여전히 페이지에 존재
- 하지만 "트랜잭션 102가 삭제했다"고 표시됨
Snapshot
Snapshot이란?
트랜잭션이 시작할 때 찍은 데이터베이스의 "시점"입니다.
-- 초기 상태
INSERT INTO products VALUES (1, 'Laptop', 1000);
-- 트랜잭션 A 시작
BEGIN; -- XID=100, Snapshot 생성
SELECT price FROM products WHERE id = 1;
-- 결과: 1000이 시점에 Snapshot이 생성됩니다:
Snapshot {
내가 볼 수 있는 트랜잭션: XID=99 이하
진행 중인 트랜잭션: 없음
}-- 다른 세션에서 UPDATE (트랜잭션 B)
UPDATE products SET price = 900 WHERE id = 1;
COMMIT; -- XID=101 완료
-- 트랜잭션 A 계속
SELECT price FROM products WHERE id = 1;
-- 결과: 여전히 1000!트랜잭션 A의 Snapshot은 "XID=99 이하만 보기"로 고정되어 있기 때문에 결과는 여전히 1000입니다.
COMMIT;
-- 새 트랜잭션 C 시작
BEGIN; -- XID=102, 새 Snapshot 생성
SELECT price FROM products WHERE id = 1;
-- 결과: 900새 Snapshot은 "XID=101 이하 보기"이므로 900을 봅니다.
가시성 판단 규칙
"어떤 행을 볼 수 있는가?"
PostgreSQL은 각 행마다 다음을 확인합니다:
규칙 1: xmin 체크
이 행을 만든 트랜잭션(xmin)이 내 Snapshot에서 보이는가?예시:
행: xmin=105
내 Snapshot: XID=100까지 보기
105 > 100 → 안 보임
→ 이 행은 내 미래에 만들어진 것규칙 2: xmax 체크
이 행을 삭제한 트랜잭션(xmax)이 있는가?
있다면, 그 트랜잭션이 내 Snapshot에서 보이는가?예시 1:
행: xmin=95, xmax=0
내 Snapshot: XID=100까지 보기
xmin(95) < 100 → 생성은 보임
xmax=0 → 삭제 안 됨
→ 이 행을 볼 수 있음예시 2:
행: xmin=95, xmax=98
내 Snapshot: XID=100까지 보기
xmin(95) < 100 → 생성은 보임
xmax(98) < 100 → 삭제도 보임
→ 이 행은 삭제됨, 안 보임예시 3:
행: xmin=95, xmax=105
내 Snapshot: XID=100까지 보기
xmin(95) < 100 → 생성은 보임
xmax(105) > 100 → 삭제는 안 보임
→ 이 행을 볼 수 있음 (내 시점에선 아직 안 지워짐)격리 수준 (Isolation Level)
READ COMMITTED (기본값)
각 쿼리마다 새로운 Snapshot 생성
BEGIN; -- 트랜잭션 시작, Snapshot은 아직 안 만들어짐
SELECT balance FROM accounts WHERE id = 1;
-- 이 시점에 Snapshot 생성: XID=100까지 보기
-- 결과: 1000
-- 다른 세션에서
UPDATE accounts SET balance = 900 WHERE id = 1;
COMMIT; -- XID=101
-- 같은 트랜잭션 내에서
SELECT balance FROM accounts WHERE id = 1;
-- 새 Snapshot 생성: XID=101까지 보기
-- 결과: 900 ← 변경사항이 보임!
COMMIT;REPEATABLE READ
트랜잭션 시작 시 한 번만 Snapshot 생성
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 이 시점에 Snapshot 생성: XID=100까지 보기
SELECT balance FROM accounts WHERE id = 1;
-- 결과: 1000
-- 다른 세션에서
UPDATE accounts SET balance = 900 WHERE id = 1;
COMMIT; -- XID=101
-- 같은 트랜잭션 내에서
SELECT balance FROM accounts WHERE id = 1;
-- 여전히 동일한 Snapshot 사용: XID=100까지 보기
-- 결과: 1000 ← 변경사항이 안 보임!
COMMIT;언제 사용?
- READ COMMITTED: 일반적인 OLTP, 최신 데이터 필요
- REPEATABLE READ: 보고서 생성, 일관된 스냅샷 필요
실제 확인하기
버전 확인
CREATE TABLE demo (id int, value text);
INSERT INTO demo VALUES (1, 'v1');
SELECT ctid, xmin, xmax, * FROM demo; ctid | xmin | xmax | id | value
-------+------+------+----+-------
(0,1) | 1001 | 0 | 1 | v1UPDATE demo SET value = 'v2';
SELECT ctid, xmin, xmax, * FROM demo; ctid | xmin | xmax | id | value
-------+------+------+----+-------
(0,2) | 1002 | 0 | 1 | v2ctid가 (0,1)에서 (0,2)로 변경됨 → 새로운 물리적 위치!
페이지 내부 보기
CREATE EXTENSION pageinspect;
SELECT lp, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('demo', 0)); lp | t_xmin | t_xmax | t_ctid
----+--------+--------+--------
1 | 1001 | 1002 | (0,2) ← v1 (dead)
2 | 1002 | 0 | (0,2) ← v2 (live)해석:
- lp=1: 이전 버전, xmax=1002로 삭제 표시, ctid=(0,2)로 새 버전 가리킴
- lp=2: 현재 버전, xmax=0으로 유효함
-- 한 번 더 UPDATE
UPDATE demo SET value = 'v3';
SELECT lp, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('demo', 0)); lp | t_xmin | t_xmax | t_ctid
----+--------+--------+--------
1 | 1001 | 1002 | (0,2) ← v1 (dead)
2 | 1002 | 1003 | (0,3) ← v2 (dead)
3 | 1003 | 0 | (0,3) ← v3 (live)버전 체인: lp1 → lp2 → lp3
1.2 Dead Tuple의 발생 원인
UPDATE의 내부 동작
PostgreSQL에서 UPDATE는 실제로:
1. DELETE (기존 tuple의 t_xmax 설정)
2. INSERT (새 tuple 생성)
src/backend/executor/nodeModifyTable.c의 ExecUpdate() 함수를 보면:
static TupleTableSlot *
ExecUpdate(ModifyTableState *mtstate, ...)
{
// ... 생략 ...
// 1. 기존 tuple을 찾아서 삭제 마킹
result = table_tuple_update(resultRelationInfo->ri_RelationDesc,
tupleid,
slot,
...);
// 2. 인덱스들도 업데이트 (후술)
if (resultRelInfo->ri_NumIndices > 0)
ExecInsertIndexTuples(...);
// ... 생략 ...
}결과: UPDATE 1회 = Dead tuple 1개 생성
DELETE의 동작
DELETE FROM users WHERE id = 1;내부적으로:
- 해당 tuple의
t_xmax를 현재 XID로 설정 - 실제 물리적 삭제는 하지 않음
- Tuple은 페이지에 그대로 남아있음 (dead tuple)
Dead Tuple이 남는 이유
왜 즉시 삭제하지 않을까요?
- MVCC 때문: 이전 스냅샷을 가진 트랜잭션들이 여전히 읽을 수 있어야 함
- 동시성: DELETE 시점에 다른 트랜잭션들을 블록하지 않음
실제 확인해보기
CREATE TABLE test (id int, data text);
INSERT INTO test SELECT generate_series(1, 10000), 'data';
-- Dead tuple 확인
SELECT n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'test';
-- n_live_tup: 10000, n_dead_tup: 0
UPDATE test SET data = 'updated';
SELECT n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'test';
-- n_live_tup: 10000, n_dead_tup: 1000010,000개의 dead tuple이 생성되게됩니다.
1.3 HOT(Heap-Only Tuple) 업데이트
HOT의 등장 배경
UPDATE마다 새 tuple을 만들면:
- 인덱스도 모두 업데이트해야 함 (비용 증가)
- Dead tuple 증가
PostgreSQL 8.3부터 HOT가 도입되었습니다.
HOT의 조건
HOT UPDATE가 가능하려면:
1. 인덱스 컬럼이 변경되지 않음
2. 새 tuple이 같은 페이지 내에 저장 가능 (충분한 free space)
CREATE TABLE hot_test (
id int PRIMARY KEY,
indexed_col int,
data text
);
CREATE INDEX idx_indexed ON hot_test(indexed_col);
INSERT INTO hot_test VALUES (1, 100, 'data');
-- HOT UPDATE 가능 (indexed_col 변경 없음)
UPDATE hot_test SET data = 'new data' WHERE id = 1;
-- HOT UPDATE 불가능 (indexed_col 변경됨)
UPDATE hot_test SET indexed_col = 200 WHERE id = 1;결과:
- 기존 tuple:
HEAP_HOT_UPDATED플래그 설정,t_ctid로 새 tuple 가리킴 - 새 tuple:
HEAP_ONLY_TUPLE플래그 설정 - 인덱스 항목은 여전히 기존 tuple을 가리킴
HOT Chain 추적
인덱스 → 기존 tuple → (t_ctid) → 새 tuple
인덱스: [100] → Page 1, Offset 3
↓
Page 1, Offset 3: [HOT_UPDATED, t_ctid=(1,7)]
↓
Page 1, Offset 7: [HEAP_ONLY, actual data]인덱스 스캔 시 heap_hot_search_buffer()가 HOT chain을 따라가서 최신 버전을 찾습니다.
HOT의 VACUUM 효과
HOT UPDATE된 dead tuple은:
- 인덱스 정리 불필요 (인덱스 항목이 없으므로)
- Heap만 정리하면 됨 → VACUUM이 훨씬 빠름
n_tup_hot_upd통계로 확인 가능
SELECT n_tup_hot_upd, n_tup_upd
FROM pg_stat_user_tables
WHERE relname = 'hot_test';1.4 Table Bloat(비대화)
Bloat의 정의
Bloat: 실제 유효한 데이터에 비해 테이블이 차지하는 물리적 공간이 과도하게 큰 현상
실제 데이터: 100MB
테이블 파일 크기: 500MB
→ Bloat: 400MB (80%)Bloat 발생 메커니즘
- UPDATE/DELETE로 Dead Tuple 누적
-- 초기: 10,000 rows, 100MB
CREATE TABLE bloat_demo AS
SELECT generate_series(1, 10000) id, repeat('x', 10000) data;
-- 전체 UPDATE 10회
DO $$
BEGIN
FOR i IN 1..10 LOOP
UPDATE bloat_demo SET data = repeat('y', 10000);
END LOOP;
END $$;내부적으로:
- 10,000 rows × 10회 = 100,000개의 dead tuple 생성
- 각 tuple은 여전히 페이지에 존재
- VACUUM이 공간을 OS에 반환하지 않음
표준 VACUUM은:
- Dead tuple을 "재사용 가능"으로 표시 (FSM에 등록)
- 파일 크기는 줄이지 않음
- 다음 INSERT가 이 공간을 재사용
Bloat의 영향
1) 쿼리 성능 저하
SELECT * FROM bloat_demo WHERE id = 5000;- Sequential Scan: 1.1GB 전체를 읽어야 함 (원래는 100MB)
- Index Scan: 인덱스는 정상이지만, heap fetch 시 더 많은 페이지 접근
2) 캐시 효율 저하
- shared_buffers에 유효하지 않은 데이터가 많이 적재됨
- 실제 필요한 데이터를 밀어냄
3) 디스크 I/O 증가
- Checkpoint 시 더 많은 페이지를 써야 함
- Backup 크기 증가
ref
아직 배고프다