백엔드 데이터 인프라 26편. UPDATE 깊이 — HOT UPDATE·fillfactor 튜닝·FROM JOIN·CASE 일괄·낙관적 락 풀어쓴 학습 노트.
이 글은 백엔드 데이터 인프라 시리즈 70편 중 26편이에요. 13편 UPDATE 에서 "기본 5가지 패턴" 을 다뤘으니, 이번 26편은 그 위에 — PG 내부 최적화 + 운영 깊이.
UPDATE 내부 — 다시 한번
24편 DML 개요 (DML = 데이터 조작 언어) 에서 본 — UPDATE는 "수정" 이 아니라 "새 행 추가 + 옛 행 표시". 두 가지 결과가 따라요. 옛 행이 누적되며 테이블 bloat (옛 행이 쌓여 공간 낭비) 가 생기고, 인덱스도 새 위치 가리키도록 갱신 돼요 — 인덱스가 많을수록 비용이 커져요.
이게 PG UPDATE 의 비용 구조. HOT UPDATE 가 이 비용을 줄여요.
HOT UPDATE — Heap Only Tuple
HOT = "같은 페이지 안에 새 버전 박고 인덱스 갱신 안 함". PG 8.3+ 자동 최적화.
조건: - 새 버전이 같은 데이터 페이지에 들어감 (공간 있음) - 변경된 컬럼이 인덱스에 안 박혀 있음
HOT 안 일어나면 = 일반 UPDATE = 인덱스 N개 모두 갱신. 큰 비용.
HOT 확인
SELECT
schemaname, relname,
n_tup_upd, -- 전체 UPDATE
n_tup_hot_upd -- 그 중 HOT UPDATE
FROM pg_stat_user_tables
WHERE relname = 'users';
n_tup_hot_upd / n_tup_upd = HOT 비율. 운영 표준 = 90%+. 낮으면 fillfactor 튜닝.
fillfactor — HOT 만들기
기본 = 100. "데이터 페이지를 가득 채움". HOT UPDATE 공간이 없음.
CREATE TABLE users (...) WITH (fillfactor = 70);
-- 또는 사후
ALTER TABLE users SET (fillfactor = 70);
VACUUM FULL users; -- 적용
fillfactor = 70 = 페이지의 30% 를 "UPDATE 여유 공간" 으로. 자주 UPDATE 되는 테이블에 표준.
룰: - 거의 INSERT 만 = 100 (기본) - 가끔 UPDATE = 90 - 자주 UPDATE = 70~80
FROM JOIN — UPDATE 의 PG 강점
13편 에서 짧게 본 패턴.
UPDATE users u
SET order_count = stats.cnt,
total_spent = stats.total
FROM (SELECT user_id, COUNT(*) AS cnt, SUM(amount) AS total
FROM orders WHERE status = 'PAID'
GROUP BY user_id) stats
WHERE u.id = stats.user_id;
한 SQL로 — 집계 결과를 모든 사용자에게 한 번에 반영. "매 사용자별 서브쿼리" 보다 압도적으로 빠름.
CTE + UPDATE
WITH stats AS (
SELECT user_id, COUNT(*) AS cnt, SUM(amount) AS total
FROM orders WHERE status = 'PAID'
GROUP BY user_id
)
UPDATE users u
SET order_count = stats.cnt, total_spent = stats.total
FROM stats
WHERE u.id = stats.user_id;
가독성·재사용 모두 우수. CTE (공통 테이블 표현식) 로 집계를 분리하면 한눈에 읽혀요.
CASE 로 한 SQL 일괄
-- 100명 사용자에 각자 다른 status — 단건 100번?
-- 아니 — 한 SQL CASE 로
UPDATE users
SET status = CASE id
WHEN 1 THEN 'GOLD'
WHEN 2 THEN 'SILVER'
WHEN 3 THEN 'BRONZE'
-- ...
END
WHERE id IN (1, 2, 3, ...);
각자 다른 값 박는 일괄 UPDATE 패턴.
낙관적 락 — 버전 컬럼
동시 수정 위험을 "버전 컬럼" 으로 해결.
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
name TEXT,
price INTEGER,
version INTEGER NOT NULL DEFAULT 0
);
-- UPDATE 시 버전 확인
UPDATE products
SET price = 2000, version = version + 1
WHERE id = 100 AND version = 5;
-- 결과 행 0 = 다른 트랜잭션이 변경 → 재시도
JPA @Version
@Entity
public class Product {
@Id @GeneratedValue
private Long id;
@Version
private Integer version;
private Integer price;
}
JPA (자바 영속성 API) 가 자동으로 — 위 패턴 적용. 충돌 시 OptimisticLockException.
낙관적 락 적합 = 충돌 드문 시스템. 비관적 (FOR UPDATE) 보다 성능 우수.
비관적 락 — FOR UPDATE
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 10000 WHERE id = 1;
COMMIT;
FOR UPDATE = 다른 트랜잭션의 동시 UPDATE 차단. 확실하지만 성능 비용.
SKIP LOCKED·NOWAIT
SELECT * FROM queue WHERE status = 'PENDING' LIMIT 10 FOR UPDATE SKIP LOCKED;
큐 처리에 매우 자주. 17편 트랜잭션 참고.
대량 UPDATE 안전 패턴
-- 한 트랜잭션 1억 행 UPDATE = 위험
-- chunk + autocommit 루프
DO $$
DECLARE
updated INTEGER;
BEGIN
LOOP
UPDATE logs SET archived = TRUE
WHERE id IN (
SELECT id FROM logs
WHERE archived IS NULL
AND created_at < '2025-01-01'
LIMIT 10000
FOR UPDATE SKIP LOCKED
);
GET DIAGNOSTICS updated = ROW_COUNT;
EXIT WHEN updated = 0;
COMMIT;
PERFORM pg_sleep(0.1); -- 다른 작업에 양보
END LOOP;
END $$;
SKIP LOCKED = 다른 트랜잭션 락된 행 건너뜀, pg_sleep = 시스템 부담 완화.
UPDATE 와 인덱스
-- price 컬럼에 인덱스
UPDATE products SET price = 2000 WHERE id = 1;
-- → price 인덱스 갱신 (HOT 불가)
-- price 인덱스 없는 컬럼만
UPDATE products SET description = 'New' WHERE id = 1;
-- → HOT 가능 (인덱스 갱신 X)
자주 변하는 컬럼 = 인덱스 박지 말기. HOT 가능성 보존.
RETURNING 활용
-- UPDATE 후 즉시 결과 받기 (이력 보존)
WITH updated AS (
UPDATE products SET price = price * 1.1
WHERE category = 'electronics'
RETURNING id, name, price
)
INSERT INTO price_log (product_id, name, new_price, changed_at)
SELECT id, name, price, NOW() FROM updated;
UPDATE + 로그 한 SQL로. CTE + DML 패턴.
트리거 영향
UPDATE 트리거가 — 자동으로 updated_at 갱신·감사 로그 작성·캐시 무효화. 대량 UPDATE 시 비용 폭발.
ALTER TABLE users DISABLE TRIGGER updated_at_trigger;
-- 대량 UPDATE
UPDATE users SET status = 'INACTIVE' WHERE last_login < '2024-01-01';
ALTER TABLE users ENABLE TRIGGER updated_at_trigger;
-- updated_at 수동 갱신
UPDATE users SET updated_at = NOW() WHERE status = 'INACTIVE' AND updated_at < '2024-01-01';
함정 5가지
(1) HOT 비율 모니터링 안 함
pg_stat_user_tables 의 HOT 비율 확인. 낮으면 fillfactor·인덱스 검토.
(2) fillfactor 기본 100
자주 UPDATE 되는 테이블 = 70~80 명시.
(3) 낙관적 락 충돌 안 처리
JPA OptimisticLockException 무시 = 데이터 손실. 재시도 또는 사용자 알림.
(4) 모든 컬럼에 인덱스
인덱스 = SELECT 빠름 + UPDATE·INSERT 느림 + HOT 불가. 필요한 컬럼만.
(5) UPDATE ... FROM ... 의 비결정성
UPDATE users u SET name = o.name FROM orders o WHERE u.id = o.user_id;
-- 한 user 에 여러 orders 있으면 — 어느 order.name 박힐지 비결정
명시적 GROUP BY 또는 DISTINCT ON.
(1) fillfactor 70~80 (UPDATE 잦음). (2) HOT 비율 90%+ 유지. (3) 대량 = chunk + SKIP LOCKED + autocommit. (4) 동시성 = 낙관적 락(@Version) 우선, 필요 시 FOR UPDATE.
한 줄 정리 — UPDATE 내부 = 옛 표시 + 새 추가. HOT 최적화로 인덱스 갱신 회피. fillfactor 70~80, HOT 비율 모니터. FROM JOIN·CTE 로 일괄. 낙관적 락 (@Version) 또는 비관적 (FOR UPDATE). 대량 = chunk + autocommit 루프.
시험 직전 한 번 더 — UPDATE 깊이 입문자가 매번 헷갈리는 것
- UPDATE 내부 = 옛 행 표시 + 새 행 추가
- HOT UPDATE = 같은 페이지 안 + 인덱스 갱신 X
- HOT 조건 = 페이지 여유 + 변경 컬럼 비인덱스
- HOT 비율 =
n_tup_hot_upd / n_tup_upd(90%+ 목표) - fillfactor = 페이지 채우는 비율
- fillfactor 70 = UPDATE 여유 30%
- 거의 INSERT 만 = 100
- 자주 UPDATE = 70~80
- FROM JOIN = 다른 테이블 값으로 일괄 (PG 강점)
- CTE + UPDATE = 가독성·재사용
- CASE 일괄 = 각자 다른 값 한 SQL
- 낙관적 락 = 버전 컬럼 + WHERE version = ?
- JPA @Version = 자동 낙관적 락
- OptimisticLockException = 충돌 → 재시도
- 비관적 락 = FOR UPDATE (확실, 비용)
- SKIP LOCKED = 큐 처리
- NOWAIT = 즉시 에러
- 대량 UPDATE = chunk + autocommit 루프
- SKIP LOCKED 안 다른 워커 영향 X
pg_sleep(N)= 시스템 부담 양보- 자주 변하는 컬럼 = 인덱스 박지 말기
- UPDATE 트리거 = updated_at 자동
- 대량 = 트리거 일시 비활성화 + 수동 갱신
- RETURNING + CTE = 이력 보존 한 SQL
- UPDATE FROM 비결정성 = GROUP BY 또는 DISTINCT ON
시리즈 다른 편 (앞뒤 글 모음)
이전 글:
- 21편 — DDL 개요 데이터 정의 언어 전체 그림
- 22편 — CREATE TABLE 깊이 PARTITION·UNLOGGED·TEMPORARY
- 23편 — 제약 깊이 CHECK DEFERRABLE EXCLUDE
- 24편 — DML 개요 4가지 동사 + MVCC 통합
- 25편 — INSERT 깊이 Bulk·COPY·UPSERT 전략
다음 글: