이번 시간에는 PostgreSQL에서 옛 행 버전이 쌓이는 모습, VACUUM이 하는 일과 하지 않는 일, VACUUM을 막는 트랜잭션을 찾는 방법을 직접 확인하며 배워가는 시간입니다.
예를 들어서 잔액 컬럼만 계속 UPDATE하는 테이블이 있다고 해볼게요. 행 수는 그대로인데, 며칠 지나 보니 테이블 파일이 처음보다 몇 배로 커져 있어요.
PostgreSQL은 UPDATE할 때 행을 그 자리에서 고치지 않고 새 버전을 하나 더 씁니다. 옛 버전은 테이블 파일 안에 그대로 남고, 나중에 VACUUM이 치워요. 그런데 VACUUM이 치우지 못하는 경우가 있습니다.
실습 준비
이 글의 명령은 모두 실습 코드의 postgresql-vacuum-mvcc/ 디렉터리에서 실행합니다.
compose.yaml: PostgreSQL 17 컨테이너setup.sql: 계좌 테이블accounts와 10만 행, 확장 두 개 생성
Docker로 PostgreSQL 17을 띄워요. 서버 옵션은 네 개를 바꿨습니다. 아래가 compose.yaml이에요.
log_autovacuum_min_duration=0: autovacuum(옛 행 버전이 쌓인 테이블에 VACUUM을 자동으로 돌리는 백그라운드 작업)이 돌 때마다 서버 로그에 결과를 남깁니다. 기본값은 10분이라 10분 넘게 걸린 경우만 남아요.autovacuum_naptime=10s: autovacuum이 테이블을 검사하는 간격입니다. 기본값은 1분인데, 실습 시간을 줄이려고 10초로 낮췄어요.max_prepared_transactions=10: 준비된 트랜잭션을 쓰려면 필요합니다. 기본값 0이면 이 기능이 꺼져 있어요.wal_level=logical: 논리 복제 슬롯을 만들려면 필요합니다.
네 옵션은 이 글의 실습에서는 쓰지 않아요.
name: pgvac
services:
db:
image: postgres:17
command: >
postgres
-c log_autovacuum_min_duration=0
-c autovacuum_naptime=10s
-c max_prepared_transactions=10
-c wal_level=logical
environment:
POSTGRES_PASSWORD: postgres
POSTGRES_DB: lab
ports:
- "7100:5432"
mem_limit: 1g
setup.sql은 계좌 테이블 accounts를 autovacuum_enabled = off로 만들고 10만 행을 넣어요. memo에는 100자짜리 문자열을 넣어 행 하나의 크기를 키웠고, balance에는 인덱스를 만들었습니다.
pageinspect와 pgstattuple은 테이블 파일 내부를 들여다보는 확장이에요. 공식 이미지에 들어 있어서 CREATE EXTENSION만 하면 됩니다.
autovacuum_enabled = off는 이 테이블에만 autovacuum을 끄는 설정이에요. 실습 도중에 autovacuum이 끼어들면 숫자가 흔들리기 때문입니다.
자주 쓰는 명령은 셸 함수로 만들어 둘게요.
sql: 컨테이너 안의 psql로 SQL을 실행합니다.size: 테이블 파일 크기와, 통계에 잡힌 살아 있는 행 수(n_live_tup)와 dead tuple 수(n_dead_tup)를 봅니다. 통계는 조금 늦게 반영될 수 있어서pg_stat_force_next_flush()로 먼저 반영해요.vac:VACUUM (VERBOSE)를 실행하고 출력에서 필요한 줄만 남깁니다.
아래 블록은 파일로 만들지 말고 실습 디렉터리에서 연 터미널에 그대로 붙여 넣어 주세요. 이후 명령은 모두 이 터미널에서 실행하고, 터미널을 새로 열면 다시 붙여 넣으시면 됩니다.
docker compose up -d
sql() { docker compose exec -T db psql -U postgres -d lab "$@"; }
size() {
sql -c "SELECT pg_stat_force_next_flush()" > /dev/null
sql -c "SELECT pg_relation_size('accounts') AS bytes, pg_size_pretty(pg_relation_size('accounts')) AS size, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'accounts'"
}
vac() { sql -c "VACUUM (VERBOSE, PROCESS_TOAST false) ${1:-accounts}" 2>&1 | grep -E "^(pages|tuples|removable|index scan)"; }
until docker compose exec -T db pg_isready -h localhost -U postgres > /dev/null 2>&1; do sleep 1; done
sql < setup.sql
sql -c "VACUUM ANALYZE accounts"
size
bytes | size | n_live_tup | n_dead_tup
----------+-------+------------+------------
14131200 | 13 MB | 100000 | 0
(1 row)
적재 직후에 VACUUM ANALYZE를 한 번 돌려 출발점을 맞췄어요. 10만 행이 13MB입니다.
열린 트랜잭션이 있는 모습을 보려면 세션이 따로 필요해요. 터미널을 따로 열어 psql로 접속하고, 이를 세션 A라고 부를게요.
docker compose exec db psql -U postgres -d lab
실습 1: 행 버전은 테이블 안에 쌓입니다
PostgreSQL은 MVCC(Multi-Version Concurrency Control) 방식으로 동시성을 처리해요. 한 행의 여러 버전을 동시에 두고, 트랜잭션마다 자기가 봐야 할 버전을 골라 읽게 하는 방식입니다.
버전을 구분하는 데는 모든 행에 붙는 숨은 컬럼을 씁니다.
xmin: 이 버전을 만든 트랜잭션의 ID입니다. PostgreSQL은 트랜잭션마다 XID라는 번호를 붙이고, 이 번호는 계속 커져요.xmax: 이 버전을 지우거나 새 버전으로 바꾼 트랜잭션의 ID입니다. 아직 아무도 건드리지 않았으면 0이에요.ctid: 이 버전이 테이블 파일의 어디에 있는지 나타냅니다.(페이지 번호, 페이지 안의 순번)형태예요. 페이지는 PostgreSQL이 파일을 읽고 쓰는 8KB 단위의 블록입니다.
세션 A에서 1번 행을 UPDATE하기 전과 후를 비교해 볼게요. UPDATE 직후에는 pageinspect의 heap_page_items로 0번 페이지에 실제로 무엇이 남아 있는지도 봅니다.
SELECT xmin, xmax, ctid, id, balance FROM accounts WHERE id = 1;
UPDATE accounts SET balance = balance + 1 WHERE id = 1;
SELECT lp, lp_flags, t_xmin, t_xmax, t_ctid FROM heap_page_items(get_raw_page('accounts', 0)) WHERE lp <= 2;
SELECT xmin, xmax, ctid, id, balance FROM accounts WHERE id = 1;
xmin | xmax | ctid | id | balance
------+------+-------+----+---------
752 | 0 | (0,1) | 1 | 10000
(1 row)
UPDATE 1
lp | lp_flags | t_xmin | t_xmax | t_ctid
----+----------+--------+--------+----------
1 | 1 | 752 | 757 | (1724,9)
2 | 1 | 752 | 0 | (0,2)
(2 rows)
xmin | xmax | ctid | id | balance
------+------+----------+----+---------
757 | 0 | (1724,9) | 1 | 10001
(1 row)
UPDATE 전의 1번 행은 0번 페이지의 1번 자리((0,1))에 있었어요. UPDATE 뒤에 SELECT로 보이는 1번 행은 (1724,9), 즉 1724번 페이지에 새로 생긴 버전이고, xmin은 UPDATE한 트랜잭션의 ID인 757입니다.
0번 페이지에는 옛 버전이 그대로 남아 있어요. t_xmax에 757이 채워져 "757번 트랜잭션이 이 버전을 바꿨다"고 표시돼 있고, t_ctid는 새 버전의 위치 (1724,9)를 가리킵니다.
옛 버전을 볼 트랜잭션이 하나도 남지 않으면 이 옛 버전은 더 이상 쓸모가 없어요. 이런 행을 dead tuple이라고 합니다.
실습 2: UPDATE를 반복하면 파일이 커지고, VACUUM으로는 줄지 않습니다
10만 행 전체의 balance를 고치는 UPDATE를 다섯 번 반복하면서 크기를 볼게요.
for i in 1 2 3 4 5; do
echo "== round $i"
sql -c "UPDATE accounts SET balance = balance + 1"
size
done
출력의 처음과 끝만 옮깁니다(발췌).
== round 1
UPDATE 100000
bytes | size | n_live_tup | n_dead_tup
----------+-------+------------+------------
28254208 | 27 MB | 100000 | 100001
(1 row)
== round 5
UPDATE 100000
bytes | size | n_live_tup | n_dead_tup
----------+-------+------------+------------
84729856 | 81 MB | 100000 | 500001
(1 row)
살아 있는 행은 계속 10만 개인데, 한 번 돌 때마다 dead tuple이 10만 개씩 늘고 파일은 13MB에서 81MB까지 커졌어요. n_dead_tup의 끝자리 1은 실습 1에서 1번 행을 한 번 고친 몫입니다.
이제 VACUUM을 돌려 볼게요. 먼저 pgstattuple로 파일 안이 어떻게 쓰이고 있는지 봅니다.
tuple_count: 살아 있는 행 수dead_tuple_count,dead_tuple_percent: 내용이 남아 있는 dead tuple 수와 파일에서 차지하는 비율free_percent: 비어 있어서 새 행을 넣을 수 있는 공간의 비율
sql -c "SELECT tuple_count, dead_tuple_count, dead_tuple_percent, free_percent FROM pgstattuple('accounts')"
vac
size
sql -c "SELECT tuple_count, dead_tuple_count, dead_tuple_percent, free_percent FROM pgstattuple('accounts')"
tuple_count | dead_tuple_count | dead_tuple_percent | free_percent
-------------+------------------+--------------------+--------------
100000 | 100036 | 15.7 | 64.72
(1 row)
pages: 0 removed, 10343 remain, 10343 scanned (100.00% of total)
tuples: 100036 removed, 100000 remain, 0 are dead but not yet removable
removable cutoff: 780, which was 0 XIDs old when operation ended
index scan needed: 8620 pages from table (83.34% of total) had 500001 dead item identifiers removed
bytes | size | n_live_tup | n_dead_tup
----------+-------+------------+------------
84729856 | 81 MB | 100000 | 0
(1 row)
tuple_count | dead_tuple_count | dead_tuple_percent | free_percent
-------------+------------------+--------------------+--------------
100000 | 0 | 0 | 83.09
(1 row)
VACUUM VERBOSE 출력에서 볼 줄은 세 개예요.
tuples: 100036 removed: 이번 VACUUM이 내용을 지운 dead tuple 수입니다.n_dead_tup은 50만 개였는데 10만 개 남짓만 지운 것은, 나머지 40만 개는 UPDATE 도중 pruning(페이지를 읽을 때 dead tuple 내용을 그 자리에서 정리하는 일)으로 이미 내용이 지워졌기 때문이에요.index scan needed: ... 500001 dead item identifiers removed: pruning이 남겨 둔 항목까지 합쳐 50만 개가 넘는 자리를 정리하고, 그 자리를 가리키던 인덱스 항목도 지웠다는 뜻입니다.0 are dead but not yet removable: 지워야 하지만 아직 못 지운 dead tuple 수입니다. 지금은 0이에요.
dead tuple은 모두 정리됐고 빈 공간은 83%가 됐어요. 그런데 파일 크기는 84729856바이트 그대로입니다. 왜 그럴까요?
공식 문서도 일반 VACUUM은 dead tuple 자리를 나중에 다시 쓸 수 있게 표시할 뿐이고, 공간을 운영체제에 돌려주지 않는다고 설명해요. 대신 빈자리는 다음 쓰기에 쓰입니다. 같은 전체 UPDATE를 한 번 더 해 볼게요.
sql -c "UPDATE accounts SET balance = balance + 1"
size
UPDATE 100000
bytes | size | n_live_tup | n_dead_tup
----------+-------+------------+------------
84729856 | 81 MB | 100000 | 100000
(1 row)
VACUUM 없이 UPDATE할 때는 한 번에 14MB씩 늘었는데, 이번에는 크기가 그대로예요. 새 버전 10만 개가 VACUUM이 비워 둔 자리에 들어갔기 때문입니다.
실습 3: 열린 트랜잭션 하나가 VACUUM을 막습니다
VACUUM이 어떤 dead tuple이든 지울 수 있는 것은 아니에요. 아직 그 버전을 봐야 하는 트랜잭션이 있으면 남겨 둬야 합니다.
앞에서 생긴 dead tuple을 정리해 두고, 세션 A에서 REPEATABLE READ 트랜잭션을 열어 한 번 읽은 뒤 그대로 둘게요.
REPEATABLE READ에서는 트랜잭션 안의 첫 쿼리가 실행될 때 스냅숏이 만들어지고, 트랜잭션이 끝날 때까지 같은 스냅숏으로 읽어요. 스냅숏은 "어느 트랜잭션까지의 변경을 볼지" 정한 기준이라고 보시면 됩니다.
sql -c "VACUUM accounts"
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM accounts;
count
--------
100000
(1 row)
터미널에서 전체 UPDATE를 한 번 하고 VACUUM을 돌려 볼게요.
sql -c "UPDATE accounts SET balance = balance + 1"
vac
UPDATE 100000
pages: 0 removed, 10343 remain, 3453 scanned (33.38% of total)
tuples: 0 removed, 310997 remain, 100000 are dead but not yet removable
removable cutoff: 781, which was 1 XIDs old when operation ended
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
dead tuple 10만 개가 모두 dead but not yet removable로 남았고, 지운 것은 0개예요.
remain의 310997은 실제 행 수가 아니에요. VACUUM이 페이지의 33%만 훑었기 때문에 훑은 부분을 바탕으로 전체를 추정한 값입니다.
removable cutoff: 781은 VACUUM이 지울 수 있는 기준선입니다. 781번 트랜잭션 전에 지워진 버전만 지울 수 있고, 그 뒤에 지워진 버전은 남겨 둬요.
기준선을 정한 트랜잭션은 pg_stat_activity에서 찾아요. backend_xmin은 그 세션의 스냅숏이 필요로 하는 가장 오래된 트랜잭션 ID입니다.
sql -x -c "SELECT pid, state, backend_xid, backend_xmin, xact_start, query FROM pg_stat_activity WHERE backend_xmin IS NOT NULL AND pid <> pg_backend_pid()"
-[ RECORD 1 ]+-------------------------------
pid | 420
state | idle in transaction
backend_xid |
backend_xmin | 781
xact_start | 2026-10-01 05:13:14.93377+00
query | SELECT count(*) FROM accounts;
세션 A의 backend_xmin이 781로, VACUUM의 removable cutoff와 같죠. 직접 따라 하시면 트랜잭션 ID 숫자는 이 글과 다를 수 있으니, 두 값이 같은지를 봐 주세요.
state가 idle in transaction인 것은 트랜잭션을 연 채 아무 쿼리도 실행하지 않고 있다는 뜻이에요. query에는 마지막으로 실행한 쿼리가 남습니다.
세션 A를 끝내고 다시 VACUUM해 볼게요.
COMMIT;
vac
pages: 0 removed, 10343 remain, 3453 scanned (33.38% of total)
tuples: 100000 removed, 240556 remain, 0 are dead but not yet removable
removable cutoff: 782, which was 0 XIDs old when operation ended
index scan needed: 1729 pages from table (16.72% of total) had 100000 dead item identifiers removed
이번에는 10만 개를 모두 지웠어요.
트랜잭션이 SELECT 한 번만 하고 아무것도 바꾸지 않았어도, 열려 있는 동안에는 VACUUM이 그 뒤에 생긴 dead tuple을 지우지 못합니다.
정리
범위: PostgreSQL 17(postgres:17 이미지), 10만 행 accounts, autovacuum은 테이블 단위로 끔.
- UPDATE는 행을 그 자리에서 고치지 않고 새 버전을 썼습니다. 1번 행의 옛 버전은 0번 페이지에
t_xmax757이 채워진 채 남았고, 새 버전은 1724번 페이지에 생겼습니다. - 10만 행 전체 UPDATE 다섯 번에 살아 있는 행은 그대로인데 파일이 13MB에서 81MB가 됐고, VACUUM으로 dead tuple을 모두 지운 뒤에도 84729856바이트 그대로였습니다. VACUUM으로 파일이 줄 거라고 기대하지 말고 빈자리가 다음 쓰기에 다시 쓰이는지 봐 주세요.
- SELECT 한 번만 한 REPEATABLE READ 트랜잭션이 dead tuple 10만 개를 VACUUM이 못 지우게 막았고, 그 세션의
backend_xmin이 VACUUM의 기준선과 같았습니다. 이 글에 싣지 않은 추가 측정에서는 autovacuum도 같은 기준선을 따라, 열린 트랜잭션이 있는 동안 세 번 돌고도 하나도 지우지 못했습니다. 테이블이 계속 커지면pg_stat_activity에서idle in transaction세션을 먼저 찾고, 긴 트랜잭션을 정리한 뒤에는 수동 VACUUM을 한 번 돌려 주세요.