ds-forge — interview trainer

Understand it step by step (한국어로 이해 → 영어로 말하기)

아무도 DELETE를 많이 안 했는데 테이블·인덱스가 계속 커지고, 인덱스가 멀쩡한데도 쿼리가 점점 느려진다. 원인은 Postgres의 MVCC다 — UPDATE/DELETE는 행을 그 자리에서 안 고치고 옛 버전을 죽은 튜플(dead tuple)로 남긴다. 죽은 튜플이 안 치워지면 디스크가 부풀고(bloat), VACUUM이 따라잡지 못하면 트랜잭션 ID 소진(wraparound)까지 간다. 증상 → 원인(MVCC) → 눈으로 확인 → autovacuum 튜닝(지루한 정답) → 스케일 처방(파티셔닝/HOT) → bloat 회수(VACUUM FULL vs pg_repack) 순서로 올라간다.

1
증상: 큰 DELETE를 한 적도 없는데 offers 테이블이 한 달 새 40GB → 95GB로 커졌고, 같은 인덱스를 쓰는 쿼리가 12ms → 60ms로 느려졌다. EXPLAIN 플랜은 그대로 Index Scan인데 Buffers의 read 숫자만 3배가 됐다. '행 수는 그대로인데 디스크와 I/O만 늘었다'가 bloat의 전형적인 지문이다.
🔧 도구:pg_total_relation_size()pg_stat_statements (느려진 쿼리 추세)EXPLAIN (ANALYZE, BUFFERS)
```
-- live rows roughly flat, but the table keeps growing
SELECT pg_size_pretty(pg_total_relation_size('offers'));  -- 40GB -> 95GB
SELECT reltuples::bigint FROM pg_class WHERE relname = 'offers'; -- ~20M, flat

-- same plan, 3x the buffers read
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM offers WHERE buyer_id = 42 ORDER BY created_at DESC LIMIT 20;
-- Index Scan ... Buffers: shared read=312 -> read=940
```
🗣 영어로 말해
“The table more than doubled on disk and reads got slower, even though the row count barely changed.”
checking microphone…
2
원인 — MVCC. Postgres는 UPDATE/DELETE를 제자리에서 하지 않는다. UPDATE는 옛 행을 죽은 버전으로 표시하고 새 버전을 추가하며, DELETE는 죽은 것으로만 표시한다. 동시에 도는 다른 트랜잭션이 자기 스냅샷에서 옛 버전을 봐야 하기 때문이다. 그래서 update가 많은 테이블은 살아있는 행 1개당 죽은 튜플 여러 개가 쌓인다. 이 죽은 공간이 인덱스에도 그대로 박혀, 인덱스가 가리키는 페이지에 살아있는 행이 듬성듬성해지면 같은 행 20개를 읽는 데 더 많은 페이지를 만져야 한다 — 이게 느려짐의 정체다.
⚖️ Trade-off: MVCC는 읽기가 쓰기를 막지 않게 해주는 대신, 죽은 튜플을 누군가 주기적으로 치워줘야 한다는 청구서를 남긴다. 그 청소부가 VACUUM이다.
```
-- one logical update = old version dead + new version added
UPDATE offers SET status = 'COUNTERED' WHERE id = 42;
-- physical: tuple(id=42, status=PENDING)   -> marked dead
--           tuple(id=42, status=COUNTERED) -> new live version
-- the index now has entries pointing at both until VACUUM cleans up
```
🗣 영어로 말해
“MVCC never updates in place — every UPDATE leaves the old row version as a dead tuple for concurrent snapshots to still see.”
checking microphone…
3
눈으로 확인: 추측하지 말고 카탈로그를 본다. pg_stat_user_tables에서 n_dead_tup(죽은 튜플 수)과 n_live_tup, 그리고 last_autovacuum 시각을 본다. 죽은 비율이 높은데 last_autovacuum이 오래됐거나 비어 있으면 autovacuum이 못 따라잡는 중이다. autovacuum이 도는데도 안 줄면, 보통 범인은 '오래된 트랜잭션'이다 — 열린 채 방치된 트랜잭션이나 안 쓰는 replication slot이 xmin horizon을 잡아두면, 그보다 나중에 죽은 튜플은 아직 누가 볼 수도 있어서 VACUUM이 회수를 못 한다. pg_stat_activity에서 가장 오래된 xact_start를 같이 확인한다.
🔧 도구:pg_stat_user_tables (n_dead_tup, last_autovacuum)pg_stat_activity (오래된 xact_start)pg_replication_slots (방치된 slot)pgstattuple 확장 (정확한 bloat %)
```
SELECT relname, n_live_tup, n_dead_tup,
       round(n_dead_tup::numeric / nullif(n_live_tup,0), 2) AS dead_ratio,
       last_autovacuum, autovacuum_count
FROM pg_stat_user_tables WHERE relname = 'offers';
-- n_dead_tup=18,400,000  dead_ratio=0.92  last_autovacuum: 6 days ago

-- the usual culprit: an old transaction pins the xmin horizon
SELECT pid, state, now() - xact_start AS age, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL ORDER BY xact_start LIMIT 3;
-- also check: SELECT slot_name, active FROM pg_replication_slots;
```
🗣 영어로 말해
“I don't guess — I read n_dead_tup and last_autovacuum from the catalog, then check for an old transaction holding the xmin horizon.”
checking microphone…
4
지루한 정답 — autovacuum을 끄지 말고, 못 따라잡는 테이블에 맞춰 튜닝한다. 기본 autovacuum_vacuum_scale_factor=0.2는 '죽은 튜플이 살아있는 행의 20%가 되면 청소'라는 뜻이라, 2,000만 행 테이블은 400만 개가 쌓여야 겨우 발동한다 — 너무 늦다. 큰/핫한 테이블만 테이블 단위로 임계치를 낮추고(scale_factor를 0.02로), 워커가 더 자주·세게 일하도록 vacuum_cost_limit을 올린다. 함께 일하는 건 ANALYZE다 — 죽은 튜플 청소뿐 아니라 플래너 통계도 갱신돼 플랜이 안 틀어진다.
⚖️ Trade-off: 더 자주·세게 vacuum하면 I/O와 CPU를 더 쓴다. 하지만 그 비용은 꾸준하고 예측 가능한 반면, 방치하면 한 번에 거대한 bloat와 긴 락을 동반한 회수 작업으로 청구서가 몰아서 온다.
✅ Fix: 큰 테이블에 글로벌 0.2 기본값을 그대로 두지 않는다. 핫 테이블만 골라 scale_factor를 0.02로 낮춰 자주 돌게 하고, cost_limit을 올려 한 번에 더 많이 치우게 한다. autovacuum을 끄는 건 거의 항상 오답이다 — 죽은 튜플은 사라지지 않고 wraparound 위험까지 키운다.
🔧 도구:ALTER TABLE ... SET (autovacuum_*)vacuum_cost_limit / autovacuum_vacuum_cost_limitVACUUM (ANALYZE, VERBOSE)
```
-- table-level: vacuum at 2% dead, not the 20% default
ALTER TABLE offers SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold    = 5000,
  autovacuum_analyze_scale_factor = 0.02
);

-- cluster-level: let workers do more I/O per round before sleeping
-- vacuum_cost_limit = 5000          (default 2000 since PG12; was 200 pre-PG12)
-- autovacuum_vacuum_cost_limit = -1 (inherits vacuum_cost_limit; raise to override)
-- autovacuum_max_workers = 5        (default 3)

-- manual run only to confirm, with timing + verbosity
VACUUM (ANALYZE, VERBOSE) offers;
```
🗣 영어로 말해
“The boring fix is to keep autovacuum on and lower the threshold per hot table, not to run it by hand.”
checking microphone…
5
wraparound 한 가지는 따로 알아야 한다. Postgres의 트랜잭션 ID는 32비트라 한 바퀴 돌면(약 21억) 옛 데이터가 미래로 보여 사라질 위험이 있다. VACUUM은 죽은 튜플 청소 외에 '이 행들은 충분히 오래돼 모두에게 보인다'고 표시(freeze)해서 이 시계를 되돌리는 역할도 한다. autovacuum이 막혀 freeze가 밀리면 'database is not accepting commands to avoid wraparound' 경고 후 결국 쓰기가 멈춘다. age(relfrozenxid)로 남은 여유를 감시하고, anti-wraparound autovacuum은 절대 끄거나 막지 않는다.
⚖️ Trade-off: freeze는 추가 I/O를 쓰지만 선택이 아니다 — wraparound는 데이터 손상/쓰기 정지로 이어지는 정합성 문제라 비용을 따질 영역이 아니다.
🔧 도구:age(relfrozenxid)autovacuum_freeze_max_ageVACUUM (FREEZE)
```
-- how close any table is to the wraparound limit (~2.1B)
SELECT relname, age(relfrozenxid) AS xid_age
FROM pg_class WHERE relkind = 'r'
ORDER BY xid_age DESC LIMIT 5;
-- if xid_age approaches autovacuum_freeze_max_age (default 200M),
-- an anti-wraparound autovacuum kicks in and must not be cancelled

-- emergency, single-user mode if it ever fully blocks writes:
-- VACUUM (FREEZE) <table>;
```
🗣 영어로 말해
“VACUUM also freezes old rows to stop transaction-ID wraparound, so I monitor relfrozenxid age and never block the anti-wraparound run.”
checking microphone…
6
스케일 처방 — 애초에 죽은 튜플을 덜 만들거나, 회수를 공짜로 만든다. ① HOT update: UPDATE하는 컬럼에 인덱스가 없고 같은 페이지에 여유가 있으면 Postgres가 인덱스를 안 건드리는 Heap-Only Tuple 갱신을 한다 — 기본 fillfactor 100은 페이지를 꽉 채워 여유가 없으니, fillfactor를 90으로 낮춰 페이지에 여유를 두면 HOT 비율이 올라 인덱스 bloat가 크게 준다. ② 시간순으로 쌓이고 오래되면 지우는 이벤트성 테이블은 RANGE 파티셔닝 — 보존 정책이 거대한 DELETE(=죽은 튜플 폭증) 대신 DROP TABLE 한 방이 되어 VACUUM 부담 자체가 사라진다. ③ append-only로 설계하면 UPDATE가 없어 죽은 튜플이 거의 안 생긴다.
⚖️ Trade-off: fillfactor를 낮추면 페이지에 일부러 빈 공간을 둬 테이블이 조금 커진다. 파티셔닝은 모든 쿼리에 파티션 키가 필요하고 운영 복잡도가 는다. 둘 다 '쓰기 패턴이 그 모양일 때'만 이득이라 측정 후 적용한다.
✅ Fix: 회수 비용을 사후에 치르지 말고 설계로 없앤다: 갱신 컬럼에 인덱스를 줄이고 fillfactor로 HOT update를 유도하고, 시계열 테이블은 파티셔닝해 DROP으로 정리한다. 가능하면 UPDATE 대신 append-only로 가서 죽은 튜플 자체를 안 만든다.
🔧 도구:fillfactor (HOT update 유도)n_tup_hot_upd 모니터PARTITION BY RANGE + DROP
```
-- 1) leave room in each page so updates stay HOT (no index churn)
--    default fillfactor is 100 (pages packed full -> no HOT)
ALTER TABLE offers SET (fillfactor = 90);
-- HOT update count visible here (compare n_tup_hot_upd vs n_tup_upd):
SELECT relname, n_tup_upd, n_tup_hot_upd
FROM pg_stat_user_tables WHERE relname = 'offers';

-- 2) retention as a metadata op, not a giant DELETE
DROP TABLE offer_events_2025_01;   -- no dead tuples, no VACUUM debt
--   vs  DELETE FROM offer_events WHERE created_at < '2025-02-01';
--       -> tens of millions of dead tuples to VACUUM afterward
```
🗣 영어로 말해
“At scale I make fewer dead tuples to begin with — HOT updates via fillfactor, or partition-and-drop so retention never creates a giant DELETE.”
checking microphone…
7
이미 쌓인 bloat 회수 — VACUUM FULL vs pg_repack. 일반 VACUUM은 죽은 공간을 '재사용 가능'으로만 표시하지 OS에 디스크를 돌려주진 않는다(테이블 끝의 완전히 빈 페이지만 예외적으로 반환). 부푼 95GB를 실제로 40GB로 줄이려면 테이블을 다시 써야 한다. VACUUM FULL은 테이블을 통째로 새로 쓰며 가장 깔끔하지만 작업 내내 ACCESS EXCLUSIVE 락을 잡아 읽기·쓰기가 전부 멈춘다 — 운영 중인 큰 테이블엔 사실상 금지다. pg_repack은 같은 회수를 온라인으로 한다: 새 테이블에 복사하고 그동안의 변경을 트리거로 따라잡은 뒤, 맨 마지막 교체 순간에만 아주 짧은 배타 락을 잡는다. 대가는 작업 중 약 2배의 디스크 여유, 확장 설치, PK/유니크 키 요구, 그리고 트리거 오버헤드다.
⚖️ Trade-off: VACUUM FULL is simplest and built-in but locks the table for the entire rewrite; pg_repack avoids the long lock but needs the extension, roughly double the disk during the run, a PK/unique key, and adds trigger overhead while copying. VACUUM FULL은 내장이라 간단하지만 작업 내내 테이블을 잠가 운영 중엔 못 쓴다. pg_repack은 긴 락을 피하는 대신 확장 설치, 작업 중 약 2배 디스크, PK/유니크 키 요구, 복사 동안의 트리거 오버헤드를 지불한다. '예방은 autovacuum, 회수는 pg_repack'이 운영의 기본값이다.
✅ Fix: Use plain VACUUM/autovacuum to prevent bloat; use pg_repack (not VACUUM FULL) to reclaim it online on a production table; reserve VACUUM FULL for maintenance windows or small tables. CLUSTER also rewrites and sorts but, like VACUUM FULL, takes an exclusive lock. 평소엔 autovacuum으로 bloat를 예방하고, 이미 부푼 운영 테이블은 pg_repack으로 무중단 회수한다. VACUUM FULL은 점검 시간대나 작은 테이블에만 쓴다. CLUSTER도 재작성+정렬을 주지만 VACUUM FULL과 마찬가지로 배타 락을 잡는다.
🔧 도구:VACUUM (공간 재사용만)VACUUM FULL / CLUSTER (재작성, 배타 락)pg_repack (온라인 회수)pgstattuple (회수 전후 검증)
```
-- plain VACUUM: reuses space, does NOT shrink the file on disk
--   (only trailing all-empty pages are returned to the OS)
VACUUM offers;

-- VACUUM FULL: rewrites + shrinks, but ACCESS EXCLUSIVE lock the whole time
VACUUM FULL offers;          -- reads AND writes blocked until done

-- pg_repack: same compaction, online, only a brief lock at swap
--   needs ~2x disk during the run + a PK/unique key + the extension
pg_repack -d mydb -t offers --jobs 4
-- copies into a shadow table, trigger captures changes,
-- then a short ACCESS EXCLUSIVE lock to swap relfilenodes
```
🗣 영어로 말해
“Plain VACUUM only marks space reusable; to give disk back I rewrite the table — VACUUM FULL takes a full exclusive lock, so on a live table I use pg_repack to do it online.”
checking microphone…

7단계 영어를 다 말하면 → 이 메커니즘 전체를 영어로 설명할 수 있게 된다.