앞선 세 편은 이미 생긴 죽은 튜플을 어떻게 치울 것인가에 대한 이야기였다. 이번 편은 반대쪽이다. 애초에 덜 만드는 방법.

UPDATE가 인덱스까지 건드리는 문제

PostgreSQL에서 UPDATE는 새 버전을 쓰는 일이라고 1편에서 정리했다. 그런데 여기 딸려오는 비용이 하나 더 있다.

새 버전이 생기면 그 위치를 가리키도록 인덱스도 갱신해야 한다. 인덱스가 다섯 개인 테이블에서 한 행을 UPDATE하면, 힙에 새 버전 하나가 생기고 인덱스 다섯 곳에 엔트리가 추가된다.

그래서 갱신이 잦은 테이블은 힙보다 인덱스가 먼저 부푸는 경우가 많다. 그리고 인덱스 팽창은 조회 성능에 직접 영향을 준다.

HOT update

PostgreSQL에는 이 비용을 건너뛰는 경로가 있다. HOT(Heap-Only Tuple) update다.

조건은 두 개고, 둘 다 만족해야 한다.

  1. 인덱스가 걸린 컬럼을 하나도 수정하지 않는다
  2. 새 버전이 같은 페이지에 들어갈 자리가 있다

둘 다 만족하면 새 버전은 같은 페이지에 저장되고, 예전 버전에서 새 버전으로 이어지는 연결만 페이지 안에 만들어진다. 인덱스는 건드리지 않는다. 인덱스는 여전히 예전 위치를 가리키고 있고, 조회는 그 위치에서 페이지 내 연결을 따라가 최신 버전을 찾는다.

이렇게 되면 세 가지가 좋아진다.

  • 인덱스 엔트리가 안 늘어난다 → 인덱스가 안 부푼다
  • 기록해야 할 변경이 줄어든다 → WAL이 덜 생긴다
  • 그 페이지를 읽을 때 기회가 되면 VACUUM을 기다리지 않고도 페이지 안에서 필요 없어진 버전을 정리할 수 있다

세 번째가 특히 중요하다. HOT으로 처리된 갱신은 상당 부분 페이지 단위로 자체 정리되기 때문에, autovacuum에 걸리는 부하 자체가 줄어든다.

지금 비율이 얼마인지부터 본다

튜닝은 측정부터다.

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,
       n_dead_tup
FROM pg_stat_user_tables
WHERE n_tup_upd > 0
ORDER BY n_tup_upd DESC
LIMIT 20;

hot_pct가 갱신이 잦은 테이블에서 낮게 나온다면 개선 여지가 있다는 뜻이다. 반대로 이미 높다면 굳이 손댈 필요 없다.

PostgreSQL 16부터는 n_tup_newpage_upd가 추가되어, 같은 페이지에 자리가 없어 다른 페이지로 넘어간 갱신을 따로 셀 수 있다. 이 값이 크면 원인이 공간 부족이라는 게 명확해진다.

두 조건 중 무엇이 막고 있는가

HOT이 안 되고 있다면 두 조건 중 어느 쪽이 문제인지 갈라야 한다. 대응이 완전히 다르다.

조건 1이 막는 경우 — 인덱스 걸린 컬럼을 갱신하고 있다

가장 흔하고, 가장 놓치기 쉽다.

전형적인 예가 자주 바뀌는 컬럼에 인덱스가 걸린 경우다. 마지막 수정 시각, 상태 값, 조회수 같은 것들이다. 이런 컬럼은 갱신될 때마다 값이 바뀌므로, 인덱스가 하나라도 걸려 있으면 그 테이블의 UPDATE는 영원히 HOT이 될 수 없다.

그래서 인덱스 하나를 추가하는 비용은 “쓰기가 조금 느려진다” 정도가 아니다. 그 컬럼을 갱신하는 모든 UPDATE에서 HOT 경로를 통째로 없애는 셈일 수 있다.

어떤 컬럼에 인덱스가 걸려 있는지는 이렇게 본다.

SELECT i.relname AS index_name,
       a.attname AS column_name,
       idx.indisunique,
       s.idx_scan          -- 실제로 쓰이고 있는지
FROM pg_index idx
JOIN pg_class t   ON t.oid = idx.indrelid
JOIN pg_class i   ON i.oid = idx.indexrelid
JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(idx.indkey)
LEFT JOIN pg_stat_user_indexes s ON s.indexrelid = i.oid
WHERE t.relname = '대상테이블'
ORDER BY a.attname;

idx_scan이 0에 가까운 인덱스가 자주 바뀌는 컬럼에 걸려 있다면 제거 후보다. 쓰지도 않으면서 HOT만 막고 있는 셈이다.

인덱스가 실제로 필요하다면 다른 방법을 찾아야 한다. 자주 바뀌는 컬럼을 별도 테이블로 분리하는 식이다. 다만 이건 스키마 변경이라 비용이 크므로, 갱신 빈도가 정말 높을 때만 고려할 만하다.

조건 2가 막는 경우 — 페이지에 자리가 없다

fillfactor는 여기서 쓴다.

fillfactor는 페이지를 몇 %까지 채울지 정하는 값이다. 테이블의 기본값은 100이다. 즉 페이지를 꽉 채워 쓴다. 저장 효율은 최선이지만, 나중에 같은 페이지에 새 버전을 넣을 자리가 없다.

값을 낮추면 각 페이지에 갱신용 여유를 남긴다.

ALTER TABLE busy_table SET (fillfactor = 90);

대가는 명확하다. 같은 데이터가 더 많은 페이지에 나뉘어 저장되므로 테이블이 커지고, 순차 스캔이 읽어야 할 양과 버퍼 사용량이 늘어난다. 그래서 전 테이블에 일괄 적용할 값이 아니라, 갱신이 잦은 소수 테이블에만 주는 게 맞다.

값은 보통 이 정도로 잡는다.

테이블 성격fillfactor
삽입 위주, 갱신 거의 없음100 (기본값 유지)
갱신이 섞임90
갱신이 매우 잦음80~85

너무 낮추면 스캔 비용이 먼저 나빠지므로 무작정 낮추는 건 좋지 않다. 바꾼 뒤에 hot_pct가 실제로 올라갔는지 확인하는 게 중요하다.

놓치기 쉬운 점

fillfactor는 기존 페이지에 소급 적용되지 않는다.

이미 꽉 채워 저장된 페이지는 그대로 남는다. 새로 기록되는 페이지부터 적용된다. 그래서 값을 바꾸고 나서 아무 변화가 없다고 느끼기 쉽다.

즉시 반영하려면 테이블을 다시 써야 한다.

-- 서비스를 멈출 수 있다면
VACUUM FULL busy_table;

-- 멈출 수 없다면 pg_repack 등으로 온라인 재작성

두 방법의 대가는 1편에 정리해뒀다. 급하지 않다면 그냥 두고 시간이 지나며 자연스럽게 반영되게 하는 것도 방법이다.

실제 접근 순서

정리하면 이 순서로 본다.

  1. hot_pct를 잰다. 갱신이 잦은데 낮은 테이블을 찾는다.
  2. 인덱스 걸린 컬럼이 갱신되는지 확인한다. 여기가 원인이면 fillfactor를 아무리 낮춰도 소용없다.
  3. 안 쓰는 인덱스부터 정리한다. idx_scan이 0인 인덱스는 비용만 내고 있다.
  4. 그래도 낮으면 fillfactor를 낮춘다. 90부터 시작해서 효과를 보고 조정한다.
  5. 기존 페이지 반영이 필요한지 판단한다. 대부분은 급하지 않다.

2번을 3번보다 먼저 하는 게 핵심이다. 순서를 바꾸면 아무 효과 없는 fillfactor 조정에 시간을 쓰게 된다.

시리즈를 마치며

네 편에 걸쳐 정리한 걸 한 줄씩 줄이면 이렇게 된다.

  • 1편 — VACUUM은 공간을 재사용 가능하게 만들 뿐 반환하지 않는다
  • 2편 — autovacuum의 기본 발동 조건은 큰 테이블에 맞지 않는다
  • 3편 — 오래된 트랜잭션이나 복제 슬롯이 붙잡고 있으면 설정은 무의미하다
  • 4편 — 인덱스와 페이지 여유를 조정하면 죽은 튜플 자체가 줄어든다

여기에 트랜잭션 ID가 순환하는 구조와 freeze를 다룬 별도 글까지 더하면 VACUUM 주변은 대체로 훑은 셈이다.

운영하면서 느낀 건, 이 영역의 문제는 대개 어느 날 갑자기 나빠지지 않는다는 것이다. 몇 주에 걸쳐 조금씩 진행되다가 임계점에서 한 번에 드러난다. 그래서 튜닝 기법보다 몇 개의 값을 상시로 보고 있는 것이 실질적으로 더 도움이 됐다. 죽은 튜플 비율, 마지막 autovacuum 시각, 가장 오래된 트랜잭션, 그리고 복제 슬롯. 이 넷이면 대부분 미리 잡힌다.

자주 묻는 질문

Q. HOT update는 어떤 조건에서 발생하나요?

두 조건이 동시에 만족해야 합니다. 첫째로 인덱스가 걸린 컬럼을 하나도 수정하지 않아야 하고, 둘째로 새 버전이 기존 튜플과 같은 페이지에 들어갈 자리가 있어야 합니다. 둘 다 만족하면 새 버전은 같은 페이지에 저장되고 인덱스 엔트리를 새로 만들지 않으므로, 인덱스 팽창과 WAL 발생량이 함께 줄어듭니다.

Q. fillfactor를 낮추면 어떤 효과가 있나요?

각 페이지에 갱신용 여유 공간을 남겨두므로 새 버전이 같은 페이지에 들어갈 확률이 올라가 HOT update 비율이 높아집니다. 대신 같은 데이터가 더 많은 페이지에 나뉘어 저장되므로 테이블 크기가 커지고 순차 스캔과 버퍼 사용량이 늘어납니다. 갱신이 잦은 테이블에만 선별적으로 적용하는 것이 일반적입니다.

Q. fillfactor를 바꾸면 기존 데이터에도 적용되나요?

적용되지 않습니다. fillfactor는 새로 기록되는 페이지에만 영향을 주므로, 이미 꽉 찬 상태로 저장된 기존 페이지는 그대로 남습니다. 즉시 반영하려면 VACUUM FULL이나 pg_repack처럼 테이블을 다시 쓰는 작업이 필요합니다.