이력을 보관해야 하는 이유

덮어쓰기의 문제

이력을 보존해야 하는 세 가지 이유

간단한 감사 테이블

CREATE TABLE products_audit (
  audit_id   SERIAL PRIMARY KEY,
  product_id INT            NOT NULL,
  operation  VARCHAR(6)     NOT NULL,  -- INSERT / UPDATE / DELETE
  old_price  NUMERIC(10,2),
  new_price  NUMERIC(10,2),
  changed_by TEXT           NOT NULL,
  changed_at TIMESTAMPTZ    NOT NULL DEFAULT NOW()
);

감사 테이블 채우기

INSERT INTO products_audit (product_id, operation, old_price, new_price, changed_by)
VALUES (42, 'UPDATE', 9.99, 12.49, 'alice');

SELECT * FROM products_audit ORDER BY changed_at DESC LIMIT 5;

감사 추적 기록 읽기

SELECT
  changed_at,
  changed_by,
  operation,
  old_price,
  new_price
FROM products_audit
WHERE product_id = 42
ORDER BY changed_at DESC;

유효 날짜: 유효 시간 이력

CREATE TABLE employee_history (
  id          SERIAL PRIMARY KEY,
  employee_id INT            NOT NULL,
  department  TEXT           NOT NULL,
  salary      NUMERIC(10,2)  NOT NULL,
  valid_from  DATE           NOT NULL,
  valid_to    DATE           -- NULL means current record
);

-- Current record for employee 7
INSERT INTO employee_history (employee_id, department, salary, valid_from)
VALUES (7, 'Engineering', 85000, '2023-01-01');

서서히 변하는 레코드 갱신

-- Step 1: close the current record
UPDATE employee_history
SET valid_to = '2024-06-01'
WHERE employee_id = 7 AND valid_to IS NULL;

-- Step 2: insert the new record
INSERT INTO employee_history (employee_id, department, salary, valid_from)
VALUES (7, 'Product', 90000, '2024-06-01');

-- Verify history
SELECT department, salary, valid_from, valid_to
FROM employee_history
WHERE employee_id = 7
ORDER BY valid_from;

특정 시점 조회

-- What department and salary did employee 7 have on 2023-09-15?
SELECT department, salary, valid_from, valid_to
FROM employee_history
WHERE employee_id = 7
  AND valid_from <= '2023-09-15'
  AND (valid_to > '2023-09-15' OR valid_to IS NULL);

시스템 버전 관리 시간 테이블

-- SQL Server / MariaDB style (illustrative)
CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  status      VARCHAR(20),
  total       NUMERIC(10,2),
  SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START,
  SysEndTime   DATETIME2 GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
) WITH (SYSTEM_VERSIONING = ON);

-- Query historical state
SELECT * FROM orders FOR SYSTEM_TIME AS OF '2024-01-15 12:00:00'
WHERE order_id = 100;

실행 취소에 이력 활용

-- Undo all price changes made by the bad batch job at a specific time
UPDATE products p
SET price = a.old_price
FROM products_audit a
WHERE p.id         = a.product_id
  AND a.operation  = 'UPDATE'
  AND a.changed_by = 'batch_job'
  AND a.changed_at BETWEEN '2024-03-10 02:00:00' AND '2024-03-10 02:05:00';

-- Confirm affected rows
SELECT COUNT(*) AS rows_restored FROM products_audit
WHERE changed_by = 'batch_job'
  AND changed_at BETWEEN '2024-03-10 02:00:00' AND '2024-03-10 02:05:00';

시간에 따른 분석

SELECT
  DATE_TRUNC('month', changed_at) AS month,
  ROUND(AVG(new_price), 2)         AS avg_price
FROM products_audit
WHERE product_id = 42
  AND operation IN ('INSERT', 'UPDATE')
GROUP BY 1
ORDER BY 1;

이해도 확인

복습: 이력을 보존하는 이유