4.3 HOT 업데이트와 index-only scan

PostgreSQL의 MVCC는 UPDATE마다 새 tuple을 만든다(3.2 참고). 이대로면 모든 인덱스가 새 tuple을 가리키도록 갱신돼야 해 비용이 큽니다. HOT(Heap-Only Tuple) 업데이트가 이 문제를 해결합니다. 또 같은 메커니즘이 index-only scan의 효율도 결정합니다. 본 절에서는 둘을 같이 봅니다.

HOT 업데이트 조건

UPDATE가 HOT으로 처리되려면 두 가지 조건을 모두 만족해야 합니다.

1. 변경된 컬럼이 어떤 인덱스에도 포함되지 않을 것
2. 같은 페이지 안에 새 tuple이 들어갈 빈 공간이 있을 것

만족하면:

• 인덱스는 갱신하지 않음
• 새 tuple은 같은 페이지에 추가되고, 이전 tuple의 t_ctid가 새 tuple을 가리킴
• 인덱스 entry는 그대로 두고, 룩업 시 t_ctid 체인을 타고 따라옴

    flowchart LR
  IDX["index entry<br/>id=42 → (5, 1)"]
  P5_1["page 5 slot 1<br/>(old tuple)<br/>name='Bob', age=30<br/>xmax=101, t_ctid=(5,2)"]
  P5_2["page 5 slot 2<br/>(new tuple, HOT)<br/>name='Bob', age=31"]

  IDX --> P5_1
  P5_1 -- "t_ctid" --> P5_2

  classDef idx fill:#dbeafe,stroke:#1d4ed8,color:#1e3a8a
  classDef old fill:#f3f4f6,stroke:#4b5563,color:#1f2937
  classDef new fill:#d1fae5,stroke:#047857,color:#064e3b
  class IDX idx
  class P5_1 old
  class P5_2 new
  

조건 1을 어기는 예: name에 인덱스가 있는데 name을 갱신. → 인덱스도 새 위치를 가리키도록 추가 entry가 들어가야 합니다.

FILLFACTOR — HOT의 연료

fillfactor는 새 페이지를 만들 때 얼마나 채워 둘지를 제어합니다. 100이 가득, 작을수록 빈 공간이 남습니다.

ALTER TABLE accounts SET (fillfactor = 80);
VACUUM FULL accounts;   -- 또는 pg_repack — 새 fillfactor가 적용된 페이지로 재구성

빈 공간이 있어야 새 tuple이 같은 페이지에 들어가 HOT이 성공합니다. 100이면 작은 변경도 새 페이지로 옮겨가 비-HOT이 됩니다.

HOT chain pruning

같은 페이지에 HOT 체인이 길게 쌓이면 SELECT가 그만큼 따라가야 합니다. PostgreSQL은 페이지를 읽을 때 HOT pruning을 통해 죽은 체인 중간을 정리합니다.

• 인덱스에서 도달할 수 있는 entry만 살아남는다
• pruning은 SELECT, INSERT 모두가 트리거할 수 있다 (read-only도 일부 정리)
• HOT 체인 전체가 dead면 페이지에서 통째 제거되고 공간 회수

이래서 HOT update가 활발한 테이블은 VACUUM 없이도 어느 정도 공간이 유지됩니다.

HOT 비율 확인

pg_stat_user_tables에 누적 카운터가 있습니다.

SELECT relname,
       n_tup_upd,
       n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct
  FROM pg_stat_user_tables
 ORDER BY n_tup_upd DESC
 LIMIT 10;

hot_pct가 90% 이상이면 매우 좋은 상태. 50% 아래면 BLOAT이 빨리 자라고 있을 가능성이 있습니다.

Index-only scan

인덱스만 봐도 SELECT의 모든 컬럼을 답할 수 있으면 heap 페이지를 안 봐도 됩니다. 이걸 index-only scan이라고 합니다.

조건:

1. SELECT가 요청한 모든 컬럼이 인덱스에 포함돼 있을 것
2. Visibility Map(VM)에서 해당 heap 페이지가 all-visible이라고 표시돼 있을 것

조건 2가 필요한 이유: 인덱스 entry는 dead tuple도 가리킬 수 있어, 인덱스만 보면 그 tuple이 visible한지 모릅니다. VM의 all-visible 비트가 켜져 있으면 “이 페이지의 모든 tuple은 모든 트랜잭션에서 보임"이 보장되므로 heap을 안 봐도 안전합니다.

VM 갱신 = VACUUM 의존

all-visible 비트는 VACUUM이 켠다. 그러므로 index-only scan의 효율은 VACUUM 빈도에 좌우됩니다.

• INSERT만 잔뜩 일어난 테이블: VACUUM이 돌기 전까지 VM이 안 켜져 있어 index-only scan이 효과 없음
• 그래서 PG 13+의 autovacuum 트리거에 INSERT 카운터도 들어감 — autovacuum_vacuum_insert_threshold, autovacuum_vacuum_insert_scale_factor

Covering index

PG 11+는 INCLUDE 절로 인덱스에 추가 컬럼을 넣을 수 있습니다. 인덱스 키는 아니지만 페이지에 같이 저장돼 index-only scan을 가능하게 합니다.

CREATE INDEX idx_orders_user_status
  ON orders(user_id, status)
  INCLUDE (created_at, amount);

-- 이제 다음 쿼리가 index-only scan으로 답해질 수 있음
SELECT created_at, amount
  FROM orders
 WHERE user_id = 42 AND status = 'paid';

INCLUDE로 들어간 컬럼은 정렬·비교에는 안 쓰이지만 답할 때는 쓰입니다. 너무 큰 컬럼을 넣으면 인덱스가 비대해져 손해 — 절충점은 5~10 컬럼 이내.

index-only scan 확인

EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id, status
  FROM orders
 WHERE user_id = 42;

-- Index Only Scan using idx_orders_user_status on orders
--   Heap Fetches: 0     <- 0이면 완전한 index-only

Heap Fetches가 0이면 VM이 잘 갱신돼 있어 진짜 index-only로 동작합니다. 0이 아니면 VM 미흡으로 heap을 일부 봐야 했다는 뜻 — VACUUM 빈도 늘리기 검토합니다.

BRIN, Bloom 등 다른 인덱스 타입과의 차이

운영의 의미: 대부분 OLTP의 index-only scan은 B-tree에 한정됩니다.

HOT과 index-only scan의 관계

둘 다 좋은 인덱스 설계가 핵심:

• HOT을 살리려면 자주 갱신되는 컬럼에 인덱스를 만들지 말 것
• index-only scan을 살리려면 자주 읽는 컬럼 묶음을 covering index로 만들 것

운영 체크리스트

-- HOT 비율이 낮은 상위 테이블
SELECT relname, n_tup_upd, n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct
  FROM pg_stat_user_tables
 WHERE n_tup_upd > 10000
 ORDER BY hot_pct NULLS LAST;

-- index-only scan이 잘 작동하지 않는 인덱스 — EXPLAIN ANALYZE의 Heap Fetches 추적

정리

• HOT update = 인덱스 없는 컬럼만 변경하고 같은 페이지에 새 tuple이 들어갈 때
• fillfactor를 70~85로 낮춰 빈 공간 확보가 HOT의 연료
• HOT chain pruning이 자동으로 dead chain 정리
• index-only scan = 모든 요청 컬럼이 인덱스에 + VM의 all-visible
• INCLUDE 절로 인덱스에 부가 컬럼 추가 (covering index)
• 진단: n_tup_hot_upd 비율, EXPLAIN ANALYZE의 Heap Fetches
• 두 기능 모두 좋은 인덱스 설계 + 충분한 VACUUM이 전제

다음 절(4.4)에서는 commit 시 fsync 정책을 좌우하는 **synchronous_commit**을 봅니다.