변경 이력 모델
UPDATE로 사라지는 게시글 상태를 재현하고 현재 테이블과 스냅샷 이력의 책임을 분리합니다.
이 장은 과거 상태 복원·감사·소급 정정이 실제 요구일 때 읽는 선택 심화입니다.
8장의 트랜잭션·잠금과 12장의 최종 모델을 이해하면 시작할 수 있습니다.
현재 테이블은 최신값, 스냅샷은 한 변경 뒤의 전체 상태, 감사 이벤트는 누가 왜 바꿨는지, 유효 시간은 현실에서 효력이 있던 때, 기록 시간은 DB가 그 사실을 알고 있던 때를 답합니다.
게시글 제목을 정정하는 UPDATE는 짧습니다.
그러나 실행 직전의 제목, 조회수, 상태를 묻는 순간 현재 테이블만으로는 답할 수 없습니다.
마지막 값이 맞다는 사실과 과거가 필요 없다는 결론은 서로 다른 판단입니다.
회원 게시판의 화면은 최신 게시글을 자주 읽습니다.
이 조회를 위해 모든 버전을 한 테이블에 섞으면 FK와 인덱스 조건이 복잡해집니다.
반대로 현재 행만 유지하면 신고 처리 분쟁, 운영 정정, 통계 재계산에서 당시 상태를 확인할 수 없습니다.
이 장에서는 현재를 안정된 참조 대상으로 두고 변경 후 전체 스냅샷을 별도 수명으로 보관합니다.
실습 대상은 ch12에서 완성한 posts입니다.
posts.id는 변하지 않고 제목, view_count, status가 정정될 수 있습니다.
스냅샷은 일부 변화량이 아니라 그 시점의 게시글을 읽는 데 필요한 업무 열 전체를 복사합니다.
예시 시각은 UTC로 고정합니다.
첫 스냅샷은 2026-07-15 00:00, 정정은 정오에 발행합니다.
독자는 두 시각 사이를 조회해 어느 행이 선택되는지 직접 확인하게 됩니다.
UPDATE로 사라지는 과거 상태
updated_at은 마지막 변경 시각만 알려 줍니다.
누가 무엇을 덮었는지 알 수 없고, 두 열이 같은 요청에서 함께 바뀌었는지도 나타내지 못합니다.
변화량 로그 역시 기록 누락과 타입 변환에 취약합니다.
특히 문자열 old_value는 NULL, 숫자, 시간의 의미를 복원 코드에 떠넘깁니다.
DROP TABLE IF EXISTS post_delta_bad;
DROP TABLE IF EXISTS post_current_bad;
CREATE TABLE post_current_bad (
post_id BIGINT UNSIGNED NOT NULL,
title VARCHAR(160) NOT NULL,
view_count BIGINT UNSIGNED NOT NULL,
status VARCHAR(16) NOT NULL,
updated_at DATETIME(6) NOT NULL,
PRIMARY KEY (post_id)
) ENGINE=InnoDB;
CREATE TABLE post_delta_bad (
delta_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
post_id BIGINT UNSIGNED NOT NULL,
column_name VARCHAR(40) NOT NULL,
old_value VARCHAR(255) NULL,
changed_at DATETIME(6) NOT NULL,
PRIMARY KEY (delta_id)
) ENGINE=InnoDB;
INSERT INTO post_current_bad VALUES
(7001, 'B+Tree 초안', 60, 'DRAFT', '2026-07-15 09:00:00');
INSERT INTO post_delta_bad
(post_id, column_name, old_value, changed_at)
VALUES (7001, 'title', 'B+Tree 초안', '2026-07-15 10:00:00');
UPDATE post_current_bad
SET title = 'B+Tree와 covering index',
view_count = 90,
updated_at = '2026-07-15 10:00:00'
WHERE post_id = 7001;
UPDATE post_current_bad
SET view_count = 75,
status = 'PUBLISHED',
updated_at = '2026-07-15 11:00:00'
WHERE post_id = 7001;
SELECT * FROM post_current_bad;
SELECT column_name, old_value FROM post_delta_bad ORDER BY delta_id;마지막 상태는 읽히지만 10시 30분의 조회수와 상태는 결정할 수 없습니다.
오류 실행 결과current: title=B+Tree와 covering index, views=75, status=PUBLISHED
captured deltas: title=B+Tree 초안
views at 10:30: unknown
status at 10:30: unknown
published rows reconstructable at 10:30: 0오류 원인은 이력 행의 집계 단위가 “한 번의 변경 뒤 완전한 게시글”이 아니라 “우연히 기록한 열 하나”이기 때문입니다.
현재에서 사라진 값을 다른 저장소가 전부 소유하지 않으면 과거 질의는 추측이 됩니다.
가장 단순한 추적 컬럼부터 이력 모델까지 비교하기
모든 변경에 완전한 스냅샷이 필요한 것은 아닙니다.
먼저 어떤 질문에 답해야 하는지 정하고 가장 단순한 방법부터 검토합니다.
생성·수정 시각과 작업자, 변경 사유, 유입 시스템을 현재 행에 남기면 마지막 변경의 책임은 빠르게 찾을 수 있습니다.
DROP TABLE IF EXISTS post_tracking_demo;
CREATE TABLE post_tracking_demo (
post_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
title VARCHAR(160) NOT NULL,
view_count BIGINT UNSIGNED NOT NULL,
created_at DATETIME(6) NOT NULL,
created_by BIGINT UNSIGNED NOT NULL,
updated_at DATETIME(6) NOT NULL,
updated_by BIGINT UNSIGNED NOT NULL,
update_reason VARCHAR(240) NOT NULL,
source_system VARCHAR(40) NOT NULL
) ENGINE = InnoDB;
INSERT INTO post_tracking_demo VALUES (
7001, 'B+Tree 초안', 60,
'2026-07-15 09:00:00', 1,
'2026-07-15 09:00:00', 1,
'최초 등록', 'WEB'
);
UPDATE post_tracking_demo
SET title = 'B+Tree와 covering index',
view_count = 90,
updated_at = '2026-07-15 10:00:00',
updated_by = 2,
update_reason = '게시글 조회수 정정',
source_system = 'ADMIN'
WHERE post_id = 7001;
SELECT title, view_count, updated_at,
updated_by, update_reason, source_system
FROM post_tracking_demo;현재 제목: B+Tree와 covering index
마지막 수정자: member 2
마지막 수정 사유: 게시글 조회수 정정
수정 전 view_count: 알 수 없음
10시 30분 전체 상태: 알 수 없음추적 컬럼은 “누가 마지막으로 바꿨는가?”에는 적합하지만 “그 전에 무엇이었는가?”에는 답하지 못합니다.
여러 방식의 차이는 다음과 같습니다.
| 방식 | 잘 답하는 질문 | 장점 | 한계 |
|---|---|---|---|
| 현재 행의 추적 컬럼 | 마지막 수정 시각·작업자·사유 | 구현과 조회가 단순 | 이전 상태를 복원하지 못함 |
previous_value 컬럼 | 바로 직전 값 하나 | 작은 설정값에 간단 | 변경이 누적되면 더 오래된 값이 사라지고 열이 늘어남 |
| 한 테이블에 모든 버전 | 특정 시점의 상태 | 한 구조에서 이력 조회 | 현재 행 조건과 FK·UNIQUE·인덱스가 복잡해짐 |
| 별도 전체 행 스냅샷 | 현재 조회와 과거 복원을 모두 지원 | 복원 쿼리가 단순하고 타입 유지 | 저장 중복과 두 테이블의 원자 갱신 필요 |
| 컬럼 단위 변경 로그 | 무엇이 바뀌었는지 찾기 | 변경량이 작고 감사 검색이 쉬움 | 전체 상태 복원이 어렵고 값 타입 표현이 복잡 |
| 공통 감사 로그 | 여러 도메인의 활동을 한곳에서 검색 | 작업자·요청 흐름을 통합 | 도메인별 시점 복원과 제약이 약함 |
실무에서는 한 가지만 고집하기보다 역할을 나눕니다.
현재 테이블에는 created_at, updated_at 같은 기본 추적 컬럼을 두고, 과거 상태 복원이 필요한 핵심 데이터에는 전체 스냅샷을 둡니다.
누가 왜 바꿨는지를 입증해야 하면 15-3의 추가 전용 감사 이벤트를 함께 기록합니다.
애플리케이션 로그는 장애 분석에 유용하지만 보존 기간과 트랜잭션 단위가 다르므로 데이터 이력을 자동으로 대신하지 않습니다.
처음부터 전체 스냅샷을 선택할 때도 모든 테이블을 복제하지 않습니다.
권한·게시글 상태·노출 정책처럼 과거 시점의 결정을 재현해야 하는 데이터와, 쉽게 다시 만들 수 있는 캐시·일시 계산값을 구분합니다.
이 판단을 마친 뒤 아래 현재/이력 모델을 적용합니다.
스냅샷에는 중복이 생깁니다.
그 중복은 변경 가능성이 낮은 읽기 스냅샷을 의도적으로 보존한 결과입니다.
대신 valid_from, valid_to, version_no를 두어 어느 구간에 어떤 복사본이 유효한지 검증할 수 있어야 합니다.
스냅샷이 책임질 열을 화면이 아니라 복원 질문에서 고르기
제목만 감사 대상이라고 생각해 제목만 복사하면 통계 정정 때 조회수가 다시 빠집니다.
author_id와 request_key 같은 관계도 당시 분류를 보존해야 한다면 함께 담습니다.
거대한 본문이나 첨부 자료처럼 별도 수명의 객체는 ID와 그때 표시할 표시명 스냅샷을 구분합니다.
현재 모델과 이력 모델
posts은 “지금 참조할 게시글”을 한 건 제공합니다.
따라서 태그 연결과 통계 원본의 FK는 안정된 현재 PK를 향합니다.
post_revision은 “특정 시각에 보였던 게시글”을 version_no와 반개구간으로 제공합니다.
반개구간 [valid_from, valid_to)를 쓰면 경계 시각이 두 버전에 동시에 포함되지 않습니다.
열린 최신 구간만 valid_to가 NULL입니다.
생성된 current_slot은 열린 행에 1, 닫힌 행에 NULL을 반환하므로 UNIQUE(post_id, current_slot)가 현재 스냅샷 중복을 원자적으로 차단합니다.
변경 프로시저가 지켜야 할 세 문장
첫째, 기존 열린 스냅샷을 잠근 뒤에만 version_no를 증가시킵니다.
둘째, 정정 시각은 열린 구간 시작보다 커야 합니다.
셋째, 현재 UPDATE와 이전 구간 종료, 신규 스냅샷 INSERT가 하나의 커밋이어야 합니다.
어느 문장이라도 오류가 발생하면 처리기가 전체 트랜잭션을 되돌립니다.
MySQL 8.4 메모: 생성 열의 NULL은 UNIQUE에서 서로 다른 값으로 취급됩니다. 이 성질을 사용해 닫힌 여러 버전은 허용하고 열린 버전 하나만 제한합니다.
스냅샷 버전 기록
아래 DDL은 스냅샷의 복합 PK, 현재 유일성, 유효 구간 검사를 정의합니다.
최초 스냅샷을 만든 다음 프로시저가 잠금부터 커밋까지 수행합니다.
SQL 중간에 애플리케이션 콜백이 끼지 않습니다.
DROP PROCEDURE IF EXISTS revise_post_revision;
DROP TABLE IF EXISTS post_revision;
CREATE TABLE post_revision (
post_id BIGINT UNSIGNED NOT NULL,
version_no INT UNSIGNED NOT NULL,
author_id BIGINT UNSIGNED NOT NULL,
request_key VARCHAR(64) NOT NULL,
title VARCHAR(160) NOT NULL,
view_count BIGINT UNSIGNED NOT NULL,
status VARCHAR(16) NOT NULL,
created_at DATETIME(6) NOT NULL,
published_at DATETIME(6) NULL,
valid_from DATETIME(6) NOT NULL,
valid_to DATETIME(6) NULL,
change_reason VARCHAR(240) NOT NULL,
current_slot TINYINT GENERATED ALWAYS AS
(IF(valid_to IS NULL, 1, NULL)) STORED,
PRIMARY KEY (post_id, version_no),
CONSTRAINT uq_post_revision_current UNIQUE (post_id, current_slot),
CONSTRAINT fk_post_revision_current FOREIGN KEY (post_id)
REFERENCES posts (id) ON DELETE RESTRICT,
CONSTRAINT chk_post_revision_window
CHECK (valid_to IS NULL OR valid_to > valid_from),
CONSTRAINT chk_post_revision_views
CHECK (view_count <= 1000000000),
INDEX ix_post_revision_asof (post_id, valid_from, valid_to)
) ENGINE=InnoDB;
SET @post_id = (SELECT id FROM posts ORDER BY id LIMIT 1);
SET @author_id = (SELECT author_id FROM posts WHERE id = @post_id);
INSERT INTO post_revision
(post_id, version_no, author_id, request_key, title,
view_count, status, created_at, published_at,
valid_from, valid_to, change_reason)
SELECT id, 1, author_id, request_key, title,
view_count, status, created_at, published_at,
'2026-07-15 00:00:00.000000', NULL, 'post revision 시작'
FROM posts
WHERE id = @post_id;
DELIMITER //
CREATE PROCEDURE revise_post_revision (
IN p_post_id BIGINT UNSIGNED,
IN p_effective_at DATETIME(6),
IN p_title VARCHAR(160),
IN p_view_count BIGINT UNSIGNED,
IN p_reason VARCHAR(240)
)
BEGIN
DECLARE v_version INT UNSIGNED;
DECLARE v_opened_at DATETIME(6);
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;
START TRANSACTION;
SELECT version_no + 1, valid_from
INTO v_version, v_opened_at
FROM post_revision
WHERE post_id = p_post_id AND valid_to IS NULL
FOR UPDATE;
IF v_opened_at IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'open snapshot not found';
END IF;
IF p_effective_at <= v_opened_at OR p_view_count > 1000000000 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'invalid snapshot boundary';
END IF;
UPDATE post_revision
SET valid_to = p_effective_at
WHERE post_id = p_post_id AND valid_to IS NULL;
UPDATE posts
SET title = p_title, view_count = p_view_count
WHERE id = p_post_id;
INSERT INTO post_revision
(post_id, version_no, author_id, request_key, title,
view_count, status, created_at, published_at,
valid_from, valid_to, change_reason)
SELECT id, v_version, author_id, request_key, title,
view_count, status, created_at, published_at,
p_effective_at, NULL, p_reason
FROM posts
WHERE id = p_post_id;
COMMIT;
END//
DELIMITER ;
CALL revise_post_revision(
@post_id, '2026-07-15 12:00:00.000000',
'snapshot 이력 실습', 100, '영수증과 대조해 제목과 조회수를 정정'
);현재 행과 열린 스냅샷이 같고 첫 스냅샷은 정확히 정오에 닫힙니다.
개선 실행 결과versions=2
open snapshots=1
closed snapshots=1
current/open differences=0
overlapping interval pairs=0FK는 현재 게시글을 지우지 못하게 하고, 스냅샷 삭제도 ON DELETE RESTRICT로 막습니다.
이력 보존 기간이 끝났다면 업무 승인과 별도 보관 절차를 거쳐야지 부모 삭제의 부수 효과로 사라져서는 안 됩니다.
version_no는 사용자가 보는 수정 횟수입니다.
유효 시각의 정렬 키는 valid_from입니다.
둘을 하나로 취급하면 과거 시각 수정이나 데이터 이관에서 문제가 생깁니다.
이 문서에서는 순방향 발행만 허용하며, 소급 정정은 ch15-4의 두 시간 축 모델로 넘깁니다.
정상 결과를 네 개의 독립 쿼리로 확인하기
열린 행 수, 현재와 열린 스냅샷의 값 차이, 구간 겹침, 특정 시각 선택을 따로 검사합니다.
하나의 복잡한 보고서로 합치면 어느 규칙이 깨졌는지 알기 어렵습니다.
각 쿼리는 위반 행만 돌려주도록 작성합니다.
특정 시점 복원과 구간 검증
조회 시각은 반개구간 조건으로 선택합니다.
겹침 검사는 자기 조인으로 두 버전을 비교합니다.
현재 일치 검사는 NULL-안전한 동등성을 사용해 published_at의 NULL도 정확히 비교합니다.
SELECT version_no, title, view_count, status
FROM post_revision
WHERE post_id = @post_id
AND '2026-07-15 10:30:00.000000' >= valid_from
AND '2026-07-15 10:30:00.000000' < COALESCE(valid_to, '9999-12-31 23:59:59.999999');
SELECT post_id
FROM post_revision
WHERE valid_to IS NULL
GROUP BY post_id
HAVING COUNT(*) <> 1;
SELECT a.post_id, a.version_no, b.version_no
FROM post_revision AS a
JOIN post_revision AS b
ON b.post_id = a.post_id AND b.version_no > a.version_no
WHERE a.valid_from < COALESCE(b.valid_to, '9999-12-31 23:59:59.999999')
AND b.valid_from < COALESCE(a.valid_to, '9999-12-31 23:59:59.999999');
SELECT s.id AS post_id
FROM posts AS s
JOIN post_revision AS h
ON h.post_id = s.id AND h.valid_to IS NULL
WHERE NOT (s.title <=> h.title)
OR NOT (s.view_count <=> h.view_count)
OR NOT (s.status <=> h.status)
OR NOT (s.published_at <=> h.published_at);첫 SELECT는 정오 이전 버전만 한 건 반환합니다.
이어지는 세 감사 쿼리는 각각 0행이어야 합니다.
정정 프로시저의 UPDATE와 스냅샷 INSERT 사이에 오류를 주입하면 처리기가 롤백하여 열린 스냅샷이 그대로 남습니다.
EXPLAIN에서 시점 조회가 ix_post_revision_asof의 post_id와 valid_from 범위를 사용하는지도 확인합니다.
이력이 커질수록 단일 게시글 시간선 조회와 기간 보관이 주 워크로드가 됩니다.
전체 스냅샷의 중복을 허용해 얻는 것
복원 쿼리는 변화량 누적 계산이 필요 없고 한 행만 읽습니다.
스키마가 바뀔 때는 스냅샷 열을 확장한 뒤 새 쓰기 주체를 배포하고 과거 행을 백필합니다.
이 비용은 이력을 보존하겠다는 명시적 선택의 대가입니다.
현재와 이력을 나누지 않을 조건
상태가 절대 수정되지 않는 추가 전용 이벤트라면 별도 현재 테이블이 필요하지 않을 수 있습니다.
반대로 현재 행을 참조하는 FK가 많고 최신 조회 비율이 높다면 분리가 단순합니다.
판단 근거는 유행하는 패턴이 아니라 읽기 집계 단위와 참조 수명입니다.
운영 중 불일치가 보이면 먼저 할 일
쓰기 주체를 잠시 한 경로로 제한하고 현재/열린 차이 목록을 스냅샷으로 저장합니다.
현재를 무조건 이력에 덮어쓰면 누가 맞는지 증거가 사라집니다.
change_reason과 배포 로그를 대조한 뒤 승인된 방향으로 한 건씩 복구합니다.
현재·이력 분리 기준
| 판단 축 | 확인할 질문 |
|---|---|
| 참조 | 다른 테이블이 현재 post_id를 안정적으로 참조해야 하는가? |
| 복원 | 운영자가 과거 한 시점의 완전한 행을 자주 재현하는가? |
| 쓰기 | 동시 정정이 있어 열린 스냅샷 한 건을 DB가 보장해야 하는가? |
| 보존 | 이력 삭제 기간과 현재 삭제 수명이 서로 다른가? |
네 질문 중 복원과 참조가 모두 필요하면 두 테이블 분리는 강한 후보입니다.
과거가 필요 없다고 확정할 수 있고 외부 원장이 이미 완전한 스냅샷을 가진다면 중복 보관을 피할 수 있습니다.
선택 기록에는 보존 기간과 복구 책임자까지 적습니다.
연습 문제
정오에 만든 버전 2를 오후 1시에 다시 정정하세요.
그 뒤 12시 30분 조회는 버전 2를, 13시 30분 조회는 버전 3을 돌려줘야 합니다.
같은 effective_at으로 두 번째 발행을 시도하면 거절되어야 합니다.
해설과 예시 답안
기존 프로시저가 열린 valid_from보다 큰 시각만 허용하므로 그대로 재사용할 수 있습니다.
발행 뒤 세 구간의 경계와 현재/열린 일치 검사를 함께 확인합니다.
CALL revise_post_revision(
@post_id, '2026-07-15 13:00:00.000000',
'snapshot 복원 검증', 110, '두 번째 정정으로 시점 경계를 검사'
);
SELECT version_no, title
FROM post_revision
WHERE post_id = @post_id
AND '2026-07-15 12:30:00' >= valid_from
AND '2026-07-15 12:30:00' < COALESCE(valid_to, '9999-12-31');
SELECT version_no, title
FROM post_revision
WHERE post_id = @post_id
AND '2026-07-15 13:30:00' >= valid_from
AND '2026-07-15 13:30:00' < COALESCE(valid_to, '9999-12-31');
CALL revise_post_revision(
@post_id, '2026-07-15 13:00:00.000000',
'거절 대상', 10, '동일 경계 중복 발행을 확인'
);두 시점 SELECT는 각각 버전 2와 3 한 건을 반환합니다.
마지막 호출은 잘못된 스냅샷 범위로 롤백되고 버전 수는 3에서 늘지 않습니다.
핵심 정리
- 현재 행의 마지막 값만으로는 과거의 완전한 게시글을 재현할 수 없습니다.
- 전체 스냅샷은 유효 구간과 함께 저장할 때 의도된 중복이 됩니다.
- 열린 버전 유일성은 생성된
current_slot과 UNIQUE로 경쟁 조건까지 막습니다. - 현재 변경과 이력 발행은 동일 트랜잭션에서 성공하거나 함께 취소됩니다.
다음 문서에서는 업무상 유효 기간 자체가 겹치는 예약 노출 정책을 모델링합니다.