· 개발

PostgreSQL UPDATE가 새 인덱스 항목을 만들지 않는 조건: HOT 업데이트와 fillfactor

PostgreSQL의 HOT(heap-only tuple) 업데이트가 일어나는 두 조건, 인덱스가 참조하는 열과 같은 페이지의 빈 공간을 공식 문서로 정리했습니다. fillfactor 설정과 pg_stat_user_tables로 비율을 보는 법도 다룹니다.

개발데이터베이스PostgreSQL

PostgreSQL에서 UPDATE는 행을 제자리에서 고치지 않습니다. 다중 버전 동시성 제어(MVCC) 때문에 새 행 버전을 테이블에 추가하고, 그 행을 가리키는 인덱스마다 새 항목이 필요할 수도 있습니다. 숫자 하나를 바꾸는 갱신이 인덱스 쓰기까지 늘리는 셈인데, 일정한 조건을 갖추면 PostgreSQL은 인덱스에 이 새 항목을 넣지 않고 넘어갑니다(BRIN 같은 요약 인덱스는 그래도 갱신될 수 있는데, 이 예외는 뒤에서 다룹니다). 이 최적화가 HOT(heap-only tuple)입니다. 이 글은 2026년 10월 12일 기준 최신 정식 버전인 PostgreSQL 18(마이너 18.6) 문서를 기준으로 했습니다.

HOT가 되는 두 조건

문서는 HOT가 가능한 경우를 두 가지로 적습니다.

  1. 갱신이 테이블의 인덱스가 참조하는 열을 하나도 바꾸지 않는다. 단, 요약(summarizing) 인덱스는 따지지 않으며, 코어 배포판에서 요약 인덱스 방식은 BRIN뿐이다.
  2. 원래 행이 있는 페이지에 갱신된 행이 들어갈 빈 공간이 충분하다.

예를 들어 이런 테이블이 있다고 해 보겠습니다.

CREATE TABLE sessions (
    id           bigint PRIMARY KEY,
    user_id      bigint NOT NULL,
    last_seen_at timestamptz NOT NULL,
    hits         integer NOT NULL DEFAULT 0
);
CREATE INDEX sessions_user_id_idx ON sessions (user_id);

UPDATE sessions
SET hits = hits + 1, last_seen_at = now()
WHERE id = 42;

인덱스는 기본 키의 id와 user_id에만 있고, 이 UPDATE는 hits와 last_seen_at만 바꿉니다. 첫 번째 조건을 만족하므로 같은 페이지에 자리가 있으면 HOT 업데이트가 됩니다.

여기에 CREATE INDEX ON sessions (last_seen_at);를 추가하면 같은 UPDATE가 인덱스가 참조하는 열을 바꾸게 되어 HOT 대상에서 빠집니다. 조회를 빠르게 하려고 만든 인덱스 하나가 자주 바뀌는 열에 걸리면 모든 갱신의 비용이 달라질 수 있다는 뜻입니다.

문서의 표현은 “인덱스 키”가 아니라 “인덱스가 참조하는 열”이라는 점도 눈여겨볼 만합니다. 표현식 인덱스의 식 안에 쓰인 열이나 부분 인덱스의 WHERE 조건에 쓰인 열도 그 인덱스가 참조하는 열입니다. 이를테면 CREATE INDEX ON sessions (user_id) WHERE hits > 100;은 키가 user_id지만 조건에서 hits를 참조합니다. 업무 로직상 “검색 키가 아닌 카운터”라고 생각한 열이라도, 인덱스 정의 어딘가에 들어가 있으면 그 열의 갱신은 첫 번째 조건을 만족하지 못합니다.

반대로 last_seen_at에 BRIN 인덱스를 만드는 경우는 요약 인덱스이므로 첫 번째 조건을 깨지 않습니다. 다만 문서는 HOT 업데이트에서도 요약 인덱스는 갱신이 필요할 수 있다고 적고 있습니다.

HOT가 아끼는 것

두 조건을 만족하면 문서가 말하는 이득은 두 가지입니다.

첫째, 갱신된 행을 나타내려고 인덱스에 새 항목을 넣을 필요가 없습니다. 위의 예라면 UPDATE 한 번마다 기본 키 인덱스와 sessions_user_id_idx에 쓰던 항목이 생기지 않습니다.

둘째, 한 행이 여러 번 갱신되면 가장 오래된 버전과 가장 새로운 버전을 뺀 중간 버전들을 주기적인 VACUUM을 기다리지 않고 평소 작업 중에 완전히 지울 수 있습니다. 문서는 여기에 SELECT도 포함된다고 명시합니다. 읽기 쿼리가 페이지를 정리하는 일이 생길 수 있다는 뜻입니다.

이게 가능한 이유는 페이지 구조에 있습니다. 테이블과 인덱스는 보통 8kB인 고정 크기 페이지의 배열로 저장되고, 각 페이지에는 실제 행을 가리키는 4바이트짜리 항목 식별자(item identifier) 배열이 있습니다. PostgreSQL이 만드는 행 포인터(CTID)는 페이지 번호와 이 항목 식별자의 번호로 이루어지고, 항목 식별자는 해제될 때까지 옮겨지지 않으므로 오래 참조해도 됩니다. HOT에서 인덱스는 항상 원래 행 버전의 항목 식별자를 가리킵니다. 정리할 때는 그 행 버전의 데이터를 지우고 항목 식별자를 아직 어떤 동시 트랜잭션에게 보일 수 있는 가장 오래된 버전으로 가는 리다이렉트로 바꿉니다. 아무에게도 보이지 않는 중간 버전은 완전히 지워지고 그 항목 식별자는 재사용됩니다.

같은 페이지라는 조건이 붙는 이유도 여기서 짐작할 수 있습니다. 인덱스가 원래 항목 식별자만 알고 있으므로 새 버전이 같은 페이지 안에 있어야 인덱스를 고치지 않고 따라갈 수 있습니다.

fillfactor로 페이지에 자리를 남기기

두 번째 조건은 테이블의 fillfactor로 확률을 높일 수 있습니다. CREATE TABLE 문서에 따르면 테이블의 fillfactor는 10에서 100 사이의 백분율이고 기본값은 100, 즉 페이지를 꽉 채우는 것입니다. 더 작게 지정하면 INSERT가 페이지를 그 비율까지만 채우고, 남은 공간은 그 페이지에 있는 행을 갱신할 때를 위해 남겨 둡니다. 그러면 UPDATE가 갱신된 행을 원래 행과 같은 페이지에 둘 기회가 생기고, 문서는 이것이 다른 페이지에 두는 것보다 효율적이며 HOT 업데이트 가능성을 높인다고 설명합니다.

새 테이블에서는 WITH 절로 지정합니다. 아래 70은 문서 예제의 값일 뿐 권장값이 아닙니다.

CREATE TABLE sessions (
    id           bigint PRIMARY KEY,
    user_id      bigint NOT NULL,
    last_seen_at timestamptz NOT NULL,
    hits         integer NOT NULL DEFAULT 0
) WITH (fillfactor = 70);

기존 테이블은 ALTER TABLE ... SET으로 바꿉니다.

ALTER TABLE sessions SET (fillfactor = 70);

ALTER TABLE 문서에 따르면 이 명령은 테이블 내용을 바로 바꾸지 않으며, 매개변수에 따라 원하는 효과를 보려면 테이블을 다시 써야 할 수 있습니다. 문서가 드는 방법은 VACUUM FULL, CLUSTER, 또는 테이블을 다시 쓰게 하는 ALTER TABLE 형식입니다. 이들은 테이블 전체를 다시 쓰므로 운영 중인 테이블에 실행하기 전에 각 명령의 문서에서 잠금 동작을 확인하는 편이 좋습니다. fillfactor 변경 자체는 SHARE UPDATE EXCLUSIVE 잠금을 잡습니다.

몇이 적당한지는 문서가 정해 주지 않습니다. 문서가 말하는 것은 갱신되지 않는 테이블에는 꽉 채우는 것이 가장 좋고 갱신이 많은 테이블에는 더 작은 값이 적절하다는 방향뿐입니다. 정의상 페이지를 덜 채우면 같은 행 수에 더 많은 페이지가 필요합니다. 그리고 fillfactor를 낮추지 않아도 HOT는 일어납니다. 새 행이 자연스럽게 새 페이지로 옮겨 가면서 기존 페이지에 빈 공간이 생기기 때문입니다.

HOT 비율을 확인하는 법

문서는 HOT와 HOT가 아닌 갱신을 pg_stat_all_tables 뷰로 볼 수 있다고 안내합니다. 사용자 테이블만 보려면 같은 열을 가진 pg_stat_user_tables를 씁니다. 관련 열은 세 개입니다.

  • n_tup_upd: 갱신된 행의 총수. 아래 두 열에 세어진 갱신과 나머지 HOT가 아닌 갱신을 모두 포함합니다.
  • n_tup_hot_upd: HOT로 갱신된 행 수. 인덱스에 다음 버전이 필요 없었던 갱신입니다.
  • n_tup_newpage_upd: 다음 버전이 새 힙 페이지로 간 갱신 수. 이 갱신은 항상 HOT가 아닙니다.
SELECT relname,
       n_tup_upd,
       n_tup_hot_upd,
       n_tup_newpage_upd,
       n_tup_upd - n_tup_hot_upd - n_tup_newpage_upd AS other_non_hot,
       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 > 0
ORDER BY n_tup_upd DESC
LIMIT 20;

other_non_hot은 문서의 정의에서 계산한 값으로, 새 페이지로 가지 않았는데 HOT도 아니었던 갱신입니다. 앞의 두 조건에 비춰 보면 이 값이 크면 인덱스가 참조하는 열을 바꾸는 갱신이 많다고 추정할 수 있고, n_tup_newpage_upd가 크면 페이지 공간이 부족했다고 볼 단서가 됩니다. 다만 새 페이지로 간 갱신 중에도 인덱스 열을 바꾼 것이 섞일 수 있으므로 원인을 단정하려면 실제 UPDATE 문과 인덱스 정의를 같이 봐야 합니다.

이 숫자를 읽을 때 알아 둘 점이 있습니다.

  • 누적 통계입니다. 마지막 초기화 이후의 합계이므로 지금의 경향을 보려면 시간을 두고 두 번 조회해 차이를 비교하는 편이 낫습니다.
  • 정상 종료하면 통계가 보존되지만, 즉시 종료나 서버 크래시, 베이스 백업이나 시점 복구(PITR)에서 시작하면 모든 카운터가 초기화됩니다.
  • 실시간이 아닙니다. 각 서버 프로세스는 유휴 상태가 되기 직전에, 최대 1초에 한 번(빌드 기본값) 통계를 공유 메모리로 내보내므로 진행 중인 트랜잭션은 아직 반영되지 않습니다. 기본 설정에서는 한 트랜잭션 안에서 조회한 값이 트랜잭션이 끝날 때까지 캐시됩니다.
  • 테이블과 인덱스 접근 통계는 track_counts가 켜져 있어야 수집됩니다.

HOT 비율이 몇 퍼센트여야 한다는 기준은 문서에 없습니다. 갱신마다 인덱스 열을 바꾸는 것이 업무상 당연한 테이블이라면 비율이 낮은 것이 정상입니다. 숫자는 목표가 아니라 “이 테이블의 갱신이 왜 인덱스를 건드리는가”를 묻기 위한 출발점으로 쓰는 편이 맞습니다.

결과는 바뀌지 않는다

HOT는 갱신 비용을 줄이는 저장소 수준의 최적화이지 정확성에 관한 설정이 아닙니다. 갱신이 HOT이든 아니든 MVCC가 새 행 버전을 만든다는 점은 같고, 각 문장이 어떤 행 버전을 보는지도 격리 수준이 정합니다. 그 부분은 READ COMMITTED와 스냅샷에서 다뤘습니다. 그래서 fillfactor를 바꾸거나 인덱스를 지울지 정할 때 따질 것은 쿼리 결과가 아니라 저장 공간, 쓰기 비용, 그리고 그 인덱스가 실제로 돕는 읽기입니다.

참고한 문서

링크와 내용은 2026년 10월 12일에 확인했습니다.

블로그로