updated_at으로 증분 동기화했더니 배치가 바꾼 행만 빠진 이유

문제 발생

상품 테이블이 바뀌면 검색 엔진에 반영하는 동기화 작업이 1분마다 돌았습니다. 지난번에 확인한 시각 이후로 updated_at이 바뀐 행만 가져오는, 흔한 증분 동기화였습니다.

SELECT * FROM products WHERE updated_at > :last_checkpoint;
-- 다 처리하면 이번 조회 시각을 다음 checkpoint로 저장

값을 바꾸는 쪽은 updated_at = now()로 시각을 남깁니다. 그런데 매일 새벽 가격 일괄 변경 배치가 돈 날이면, 검색 결과에 옛날 가격이 남아 있다는 문의가 들어왔습니다. 배치가 바꾼 행은 DB에서 분명히 새 가격이었고 updated_at도 찍혀 있었습니다. 그런데 동기화 로그를 보면 그 행들을 한 번도 가져간 적이 없었습니다. 손으로 고친 상품은 잘 반영되는데, 배치가 바꾼 것만 통째로 빠졌습니다.

원인 분석

PostgreSQL의 now()는 지금 시각이 아니라 트랜잭션이 시작된 시각입니다. PostgreSQL 문서의 현재 날짜/시간 함수 설명에 따르면 CURRENT_TIMESTAMP 같은 함수는 현재 트랜잭션의 시작 시각을 돌려주기 때문에 트랜잭션 동안 값이 바뀌지 않습니다. 문서는 이걸 의도된 기능이라고 설명합니다. 한 트랜잭션 안의 여러 변경이 같은 타임스탬프를 갖게 해서 "현재" 시각을 일관되게 쓰도록 하려는 거라고요. 같은 문서에서 now()는 transaction_timestamp()와 같은 함수라고 적혀 있습니다. 트랜잭션 안에서 2초를 쉬고 다시 찍어 보면 바로 보입니다.

              now_1             |           clock_1
 2026-10-30 23:10:19.872362+09 | 2026-10-30 23:10:19.872803+09
 (pg_sleep 2초)
              now_2             |           clock_2
 2026-10-30 23:10:19.872362+09 | 2026-10-30 23:10:21.875575+09

now()는 2초가 지나도 그대로이고, clock_timestamp()만 움직였습니다.

트랜잭션이 길면 updated_at이 실제 반영 시점보다 한참 과거로 찍힙니다. 가격 배치는 수만 건을 한 트랜잭션으로 바꾸느라 몇 분씩 걸렸고, 그동안 찍힌 updated_at은 전부 배치가 시작된 시각이었습니다. 그 사이에 동기화가 돌면 이렇게 됩니다.

1. 배치가 트랜잭션을 시작하고 행을 고칩니다. updated_at은 시작 시각으로 찍힙니다.
2. 동기화가 돕니다. 배치는 아직 커밋 전이라 동기화 쪽에서는 바뀐 행이 보이지 않습니다. 동기화는 조회 시각을 다음 checkpoint로 저장합니다.
3. 배치가 커밋합니다. 이제 행이 보이지만, updated_at은 1번의 시각이라 2번의 checkpoint보다 과거입니다.
4. 다음 동기화부터는 updated_at > checkpoint 조건에 영원히 걸리지 않습니다.

PostgreSQL 16에서 세션 두 개로 재현해 보니 정확히 이렇게 됐습니다. 배치는 23:10:36에 시작해 23:10:40에 커밋했고, 그 사이 23:10:38에 돈 동기화는 0건을 가져가며 checkpoint를 23:10:38로 저장했습니다. 커밋 뒤 다시 조회하니 updated_at > checkpoint인 행은 0건이었고, 실제로 바뀐 3건의 updated_at은 전부 23:10:36이었습니다.

짧은 트랜잭션이면 같은 일이 덜 보일 뿐, 원리는 같습니다. 손으로 고친 상품은 트랜잭션이 밀리초 단위라서 시작 시각과 커밋 시각 사이에 동기화가 끼어들 틈이 거의 없었습니다. 배치만 빠진 이유가 그겁니다.

해결 방안

1. 세 가지 "현재 시각"을 구분해서 씁니다. 문서대로 now()와 transaction_timestamp()는 트랜잭션 시작 시각, statement_timestamp()는 현재 문장이 시작된 시각, clock_timestamp()는 실제 현재 시각이라 한 SQL 문장 안에서도 바뀝니다. 행마다 실제로 처리된 시각이 필요하면 clock_timestamp()를 씁니다. 같은 트랜잭션에서 넣은 네 행이 DEFAULT now() 열에서는 모두 같은 값 하나였고, DEFAULT clock_timestamp() 열에서는 서로 다른 값 네 개였습니다.

sent_at timestamptz NOT NULL DEFAULT clock_timestamp()

반대로 "이 작업이 한 묶음으로 일어났다"는 걸 남기고 싶을 때는 now()가 맞는 선택입니다. 둘 중 무엇이 옳은지는 그 열이 무엇을 기록하려는지에 달려 있습니다.

2. 그래도 증분 동기화의 근본 문제는 남는다는 걸 압니다. clock_timestamp()로 바꿔도 찍히는 건 행을 고친 순간이지 커밋한 순간이 아닙니다. 커밋 전에 동기화가 지나가면 똑같이 놓칩니다. 틈이 몇 분에서 몇 초로 줄 뿐입니다. 타임스탬프 기반 동기화를 유지한다면 checkpoint를 겹치게 잡습니다. updated_at > :last_checkpoint - interval '10 minutes'처럼 가장 긴 트랜잭션보다 넉넉하게 뒤로 물리고, 같은 행이 여러 번 와도 결과가 같도록 받는 쪽을 upsert로 만듭니다. 이 여유 시간은 배치가 이보다 오래 걸리면 다시 깨지니, 배치 실행 시간도 함께 감시합니다.

3. 긴 배치는 작게 나눠 커밋합니다. 수만 건을 트랜잭션 하나로 묶으면 updated_at과 커밋 시각의 차이가 그만큼 커집니다. 천 건 단위로 끊어 커밋하면 각 묶음의 시작 시각이 실제 반영 시각에 가까워지고, 잠금을 쥐고 있는 시간도 줄어듭니다. 전부 아니면 전무여야 하는 작업이라면 나누는 대신 2번의 겹치는 윈도로 보완합니다.

4. 누락이 절대 안 되는 동기화라면 타임스탬프를 버립니다. 변경을 놓치지 않는 게 핵심이라면 시각 비교는 구조적으로 불리합니다. PostgreSQL 문서가 설명하는 논리 디코딩(logical decoding)은 테이블의 변경을 WAL(write-ahead log)에서 읽어 애플리케이션이 다룰 수 있는 형태로 뽑아내는 방식이라, "언제 찍혔나"가 아니라 "커밋된 변경"을 기준으로 가져옵니다. 규모가 작다면 같은 트랜잭션 안에서 변경 이벤트를 별도 테이블에 쌓아 두고, 처리한 이벤트에 표시하는 방식도 있습니다.

5. MySQL에서 옮겨 왔다면 NOW()의 뜻이 다릅니다. MySQL 문서에 따르면 NOW()는 트랜잭션이 아니라 그 문장이 실행되기 시작한 시각을 돌려주고, SYSDATE()는 실제로 실행되는 시각을 돌려줍니다. MySQL에서 문장마다 NOW()가 달라지는 데 익숙했다면, PostgreSQL에서 한 트랜잭션 안의 now()가 끝까지 같은 값이라는 데서 걸립니다. PostgreSQL 문서에도 다른 데이터베이스 시스템은 이 값을 더 자주 갱신할 수 있다는 주석이 달려 있습니다. 같은 이름의 함수라도 DB를 옮길 때는 시각 함수부터 다시 확인합니다.

공식 문서

• PostgreSQL: Date/Time Functions — Current Date/Time
• PostgreSQL: Logical Decoding Concepts
• MySQL: Date and Time Functions — NOW(), SYSDATE()