"hot standby" 태그로 연결된 2개 게시물개의 게시물이 있습니다.

PostgreSQL 19에는 standby가 지정한 WAL 위치까지 replay할 때까지 기다리는 WAIT FOR LSN 명령이 들어가요. primary에 쓴 직후 비동기 standby에서 읽으면 방금 쓴 값이 보이지 않을 수 있어요. 이 명령은 그 문제를 읽는 쪽에서만 해결하게 해 줘요. 이 글에서는 공식 문서와 commit을 바탕으로 문법과 제약을 확인했어요. primary 읽기, remote_apply, WAIT FOR LSN도 19beta4 primary 1대, standby 1대 구성에서 비교해 봤어요.

소재는 ClickHouse 블로그의 Read your writes: WAIT FOR in PostgreSQL 19(Gülçin Yıldırım Jelínek(귈친 이을드름 옐리넥), 2026-08-25)입니다. stale read 문제에서 시작해 문법을 설명하고, top-level 명령이어야 하는 이유로 이어지는 구성을 이 글에 활용했습니다. 원문에는 측정값이 없어 지연과 stale read 비율은 직접 측정했습니다. 원문 발행 뒤 9월에 추가된 잠금 제한과 LSN 선택 문제는 commit과 측정 결과를 바탕으로 따로 정리했습니다.

비동기 standby의 stale read​

streaming replication은 기본적으로 비동기입니다. primary는 커밋 WAL을 로컬에 flush하면 클라이언트에 성공을 반환합니다. standby가 그 WAL을 받아 replay하는 작업은 이후에 진행됩니다. 쓰기는 primary로 보내고 읽기는 standby로 보내는 구성에서는 사용자가 방금 저장한 값을 다음 화면에서 보지 못하는 일이 생깁니다. 공식 문서의 Read-Your-Writes Consistency 절도 이 구간을 "a short window where changes on the primary may not yet be visible on the standby"로 설명합니다.

PostgreSQL 18까지 사용하던 해법은 크게 두 가지였습니다. 하나는 방금 쓴 사용자의 읽기를 primary로 보내는 것입니다. 다른 하나는 synchronous_commit = remote_apply를 설정해 커밋이 synchronous standby의 replay까지 기다리게 하는 것입니다. synchronous_commit 문서는 이 설정을 "commits will wait until replies from the current synchronous standby(s) indicate they have received the commit record of the transaction and applied it"라고 설명합니다. 이어서 "This will cause much larger commit delays than previous settings since it waits for WAL replay"라고 적습니다. 첫 번째 방법은 읽기 부하를 standby로 분산한다는 목적을 일부 포기해야 합니다. 두 번째 방법은 쓰는 쪽에서 지연을 감수해야 합니다.

WAIT FOR LSN은 세 번째 선택지입니다. 쓰기는 비동기로 유지하고, 일관성이 필요한 읽기만 standby에서 해당 WAL 위치까지 기다립니다.

WAIT 문법​

공식 문서(19)의 synopsis는 다음과 같습니다.

WAIT FOR LSN 'lsn'
    [ WITH ( option [, ...] ) ]

-- option:
--     MODE 'mode'
--     TIMEOUT 'timeout'
--     NO_THROW
-- mode:
--     standby_replay | standby_write | standby_flush | primary_flush

명령 이름은 개발 도중 한 번 정리됐습니다. 원래 문서 제목과 오류 메시지는 "WAIT FOR"였지만, commit 1a562a266(2026-09-14)에서 명령 이름을 WAIT로 통일했습니다. command tag가 이미 WAIT였고, FOR는 INSERT INTO의 INTO처럼 문장을 자연스러운 영어로 만드는 noise word라는 이유입니다. 문법은 그대로 WAIT FOR LSN이고, 문서 URL은 sql-wait-for.html에서 sql-wait.html로 바뀌었습니다. 이 글에서도 명령은 WAIT, 문법을 나타낼 때는 WAIT FOR LSN으로 씁니다.

MODE는 대기할 대상을 정합니다. 기본값은 standby_replay입니다.

read-your-writes에 사용하는 모드는 standby_replay입니다. write와 flush는 WAL이 standby에 도착했다는 뜻일 뿐이며, 쿼리에 보인다는 보장은 없습니다. 이 두 모드는 commit cba67b5b8부터 walreceiver 위치와 replay 위치 중 큰 값을 기준으로 사용합니다. base backup이나 archive에서 이미 replay한 WAL을 walreceiver가 다시 받을 때까지 기다리는 일을 방지하기 위한 수정입니다.

TIMEOUT은 밀리초 단위 숫자 또는 '100ms', '5s' 같은 단위 문자열을 받습니다. 지정하지 않거나 0이면 무기한 기다립니다. 문서에 따르면 0.5ms 이하 값은 반올림하면 0이 되므로 무기한 대기합니다.

반환값은 status 컬럼 하나이며, 값은 success, timeout, not in recovery 중 하나입니다. NO_THROW가 없으면 timeout과 not in recovery는 오류가 됩니다. 19beta4 standby에서 확인한 두 경우의 출력은 다음과 같습니다.

lab=# WAIT FOR LSN '0/062759E0' WITH (TIMEOUT '100ms');
ERROR:  timed out while waiting for target LSN 0/062759E0 to be replayed; current standby_replay LSN 0/052759E0

lab=# WAIT FOR LSN '0/062759E0' WITH (TIMEOUT '100ms', NO_THROW);
 status
---------
 timeout

문서는 NO_THROW를 사용하는 경우도 설명합니다. 애플리케이션에서 timeout을 처리할 예정이면 NO_THROW로 상태 문자열을 받습니다. 다시 기다리거나 다른 서버로 읽기를 보내는 경우가 그렇습니다. 목표 LSN에 도달하지 못한 상태에서 진행하면 안 되는 흐름이라면 옵션을 빼고 오류로 중단합니다. NO_THROW로 받은 상태는 명시적 트랜잭션을 abort 상태로 만들지 않는다는 차이도 있습니다.

기다릴 LSN 고르기​

애플리케이션은 쓰기 커밋 직후 primary에서 LSN을 받아 보관했다가 standby에 전달합니다. 문서는 이 LSN을 클라이언트나 connection pooler가 보관하라고 안내합니다.

어떤 LSN을 받을지가 중요합니다. commit a203d2019(2026-09-17)에서 문서에 추가된 조건은 "the target LSN is at or after the end of the relevant write transaction's COMMIT record on the primary"입니다. standby가 비교하는 위치는 마지막으로 replay한 레코드의 끝입니다. 그래서 COMMIT 레코드 내부를 가리키는 LSN은 COMMIT이 아직 적용되지 않았는데도 조건을 만족할 수 있다는 설명입니다. 따라서 트랜잭션 안에서 RETURNING pg_current_wal_insert_lsn()처럼 커밋 전에 받은 값은 이 조건을 만족하지 않습니다. autocommit에서는 쓰기 문장이 끝난 뒤 별도 문장으로 LSN을 조회해야 합니다.

문서 예시는 이 LSN을 pg_current_wal_insert_lsn()으로 받습니다. synchronous_commit이 off여도 커밋 레코드 뒤를 가리키도록 insert 위치를 사용한다고 적혀 있습니다. 하지만 이 함수에는 페이지 경계 문제가 있습니다. 마지막 레코드가 8kB WAL 페이지 끝에서 정확히 끝나면 다음 insert 위치는 다음 페이지 헤더(24바이트) 뒤를 가리킵니다. standby의 replay 위치는 페이지 경계에서 멈춰 있으므로, 다른 WAL 레코드가 하나 더 생길 때까지 이 LSN에 도달하지 않습니다. 이 현상은 2026-09-22 pgsql-hackers의 049_wait_for_lsn 테스트 스레드에 보고됐습니다. 내부 함수 GetXLogInsertEndRecPtr()을 SQL 함수로 노출하자는 패치가 제안된 상태이며, REL_19_BETA4에는 반영되지 않았습니다.

아래 측정에서 이 현상을 확인했습니다. 뒤의 "단점과 제약" 절에 결과를 정리했습니다.

측정 환경​

• macOS Docker Desktop VM (2 CPU, 약 12GB, Linux 6.8.0), 다른 작업과 공유하는 VM
• postgres:19beta4 이미지 (PostgreSQL 19beta4, aarch64), docker-compose로 primary 1대, physical streaming standby 1대, 측정용 client 1대 구성
• 컨테이너마다 CPU 1개, primary와 standby는 메모리 768MB
• client는 Python 3.13과 psycopg 3.2.6 사용, 같은 Docker 네트워크에서 두 서버에 각각 연결
• 행 하나(id = 1)를 UPDATE ... RETURNING v로 증가시키고 바로 읽어, 읽은 값이 방금 쓴 값보다 작으면 stale로 셉니다

지연 조건은 두 가지입니다. 하나는 지연을 주입하지 않은 상태입니다. 다른 하나는 primary에서 standby로 가는 replication 경로에만 tc netem delay 20ms를 적용한 상태입니다. client와 서버 사이 경로에는 지연이 없습니다. 20ms 조건에서 pg_stat_replication의 replay_lag는 약 20.6ms였습니다.

비동기 시나리오는 synchronous_standby_names를 비우고 세션에서 synchronous_commit = local로 실행했습니다. remote_apply 시나리오에서만 synchronous_standby_names = 'standby1'로 바꾸고 sync_state = sync를 확인한 뒤 실행했습니다.

세 가지 해법 비교​

지연을 주입하지 않은 상태에서 1,000회씩 반복한 결과입니다. 시간은 쓰기부터 읽기까지 한 번 왕복하는 데 걸린 전체 시간이고 단위는 ms입니다.

같은 VM 안에서 지연을 전혀 주지 않았는데도 A는 1,000회 중 999회가 stale이었습니다. 앞선 500회 측정에서도 489회가 stale이었습니다. 커밋 응답 직후 standby에 보낸 SELECT가 0.1ms 안팎에 끝나므로, 그 안에 WAL 전송과 replay가 완료되지 않은 것으로 보입니다. 복제 지연이 거의 없는 환경에서도 "바로 읽기"에서는 stale이 발생하는 것이 일반적이라고 보는 편이 맞습니다.

D의 WAIT 단계 자체는 p50 0.13ms였습니다. 다만 D의 최댓값은 2,004ms였습니다. 1,000회 중 1회가 2초 timeout에 걸렸기 때문입니다. 해당 회차의 목표 LSN은 0/03132018로 페이지 시작에서 24바이트 뒤였습니다. standby replay 위치와 primary의 write, flush 위치는 모두 0/03132000이었습니다. 앞 절에서 설명한 페이지 경계 현상입니다.

E는 커밋 전에 받은 LSN이므로 문서의 조건을 만족하지 않습니다. 이번 측정에서는 stale이 한 번도 나오지 않았습니다. 제 추정으로는 UPDATE 레코드와 COMMIT 레코드가 짧은 간격으로 함께 전송되고 replay되어 그사이에 읽기가 실행되지 못한 것 같습니다. 다만 이 부분은 확인하지 못했습니다. 측정에서 나오지 않았다고 해서 안전한 방법이 되는 것은 아닙니다.

replication 경로에 20ms를 적용한 상태에서 300회씩 반복한 결과입니다. F는 insert 위치 대신 pg_current_wal_lsn()(write 위치)을 받은 경우입니다.

C와 D의 전체 시간은 거의 같습니다. 둘 다 결국 standby가 replay할 때까지 기다리기 때문입니다. 차이는 대기하는 주체에 있습니다. C는 커밋에 22.9ms가 걸렸습니다. D와 F는 커밋이 0.5ms 안에 끝난 뒤 읽는 쪽에서 22ms를 기다렸습니다.

이 차이가 처리량에 어떻게 나타나는지 같은 20ms 조건에서 pgbench(-N, 클라이언트 4개, 15초)로 확인했습니다.

remote_apply는 세션이나 트랜잭션 단위로도 지정됩니다. 일관성이 필요한 쓰기에만 켜면 이 비용이 줄어듭니다. 그래도 이를 켠 트랜잭션은 replication 경로의 왕복 시간만큼 커밋이 늦어지고, 그동안 행 잠금도 유지됩니다.

장점​

WAIT의 가장 큰 장점은 비용을 읽는 쪽으로 옮긴다는 점입니다. 쓰기 경로는 비동기 상태를 유지하므로 쓰기 처리량과 커밋 지연이 standby 상태에 영향을 받지 않습니다. standby가 느려지거나 멈춰도 해당 standby에서 기다리는 읽기만 영향을 받습니다. TIMEOUT과 NO_THROW를 사용하면 해당 읽기를 primary로 돌릴지도 애플리케이션이 판단합니다.

remote_apply는 standby가 응답하지 않으면 커밋이 멈춥니다. 확인을 위해 synchronous_standby_names = 'standby1' 상태에서 standby 컨테이너를 docker pause로 멈췄습니다. 그리고 statement_timeout = '3s'를 설정한 세션에서 remote_apply UPDATE를 실행했습니다. 3초가 지나도 끝나지 않았습니다. 68초 시점에 pg_stat_activity의 wait_event는 SyncRep였습니다. pg_cancel_backend()로 취소하자 다음 경고와 함께 반환됐습니다.

WARNING:  canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.

autocommit 문장에서는 statement_timeout이 이 대기에 적용되지 않았습니다. finish_xact_command()가 CommitTransactionCommand() 전에 disable_statement_timeout()을 호출하기 때문으로 보입니다(src/backend/tcop/postgres.c, REL_19_BETA4). 코드를 읽고 추정한 내용이며 다른 경로까지 확인하지는 않았습니다.

remote_apply가 보장하는 대상은 "current synchronous standby(s)"뿐이라는 점도 다릅니다. 읽기 standby가 여러 대이고 그중 일부만 synchronous라면 나머지 standby에서는 여전히 stale read가 발생합니다. WAIT는 읽기를 받은 standby에서 직접 기다리므로 standby 수와 관계없이 같은 방식으로 동작합니다.

단점과 제약​

애플리케이션의 LSN 전달​

WAIT는 SQL 한 줄이지만, 그 전후 과정은 애플리케이션이 처리해야 합니다. 쓰기 뒤에 LSN을 조회하고 세션이나 요청 컨텍스트에 보관한 다음 읽기 standby에 넘겨야 합니다. 읽기 경로와 쓰기 경로가 서로 다른 서비스로 분리되어 있다면 LSN을 전달할 통로도 만들어야 합니다. primary 읽기나 remote_apply는 애플리케이션 코드 변경이 거의 없다는 점에서 여전히 단순합니다.

페이지 경계의 insert LSN​

pg_current_wal_insert_lsn()의 페이지 경계 현상을 별도로 재현했습니다. primary에 다른 부하가 없는 상태에서 pg_logical_emit_message()로 WAL을 채워 마지막 레코드가 8kB 경계에서 끝나도록 만들었습니다. 그런 다음 두 LSN으로 각각 WAIT를 실행했습니다.

write 위치를 기준으로 기다리면 즉시 끝났습니다. insert 위치를 기준으로 기다리면 전혀 관계없는 WAL 레코드가 생길 때까지 6초에서 19초를 기다렸습니다. 쓰기가 계속 발생하는 서버라면 다음 레코드가 금방 생성되어 이 현상이 드러나지 않을 것입니다. 그러나 쓰기가 드문 서버에서는 TIMEOUT이 짧으면 timeout이 발생하고, TIMEOUT이 없으면 그만큼 읽기가 늦어집니다. 앞의 1,000회 측정에서 발생한 2초 timeout 1건도 같은 원인이었습니다.

pg_current_wal_lsn()은 페이지 헤더를 건너뛰지 않습니다. 하지만 synchronous_commit = off이면 커밋이 반환된 시점에 write 위치가 아직 COMMIT 레코드 끝에 도달하지 않았을 가능성이 있습니다. 따라서 어떤 함수를 사용할지는 synchronous_commit 설정에 따라 달라집니다. on이나 local처럼 커밋이 로컬 flush를 기다리는 설정에서는 pg_current_wal_lsn()이 조건을 만족합니다. off를 사용하는 트랜잭션에서는 insert 위치를 사용하되 TIMEOUT을 반드시 지정하는 편이 안전합니다. 이 판단은 문서와 제 측정을 종합한 것이며, 공식 문서가 직접 권고하는 내용은 아닙니다.

snapshot 보유 시 실행 불가​

WAIT가 함수가 아닌 명령인 이유가 여기에 있습니다. 최초 구현 commit 447aae13b는 다음과 같이 설명합니다.

WAIT FOR needs to wait without any snapshot held. Otherwise, the snapshot could prevent the replay of WAL records, implying a kind of self-deadlock.

snapshot을 가진 세션이 replay를 기다리는 동안 replay가 해당 snapshot 때문에 멈추면 서로 기다리는 상태가 됩니다. 함수와 프로시저는 snapshot을 가진 상태에서 실행되므로 이 기능을 포함할 수 없다는 것이 commit의 설명입니다. 이후 commit a8b61c23c(2026-04-13)는 함수, 프로시저, DO 블록 안에서의 실행을 명시적으로 막았습니다.

이 제약에 관한 문서 표현은 원문 발행 뒤 바뀌었습니다. 원문은 "READ COMMITTED보다 높은 격리 수준의 트랜잭션에서는 실행할 수 없다"는 취지로 설명합니다. 그러나 commit 6fe990f34(2026-09-18)는 이것이 격리 수준 전체에 대한 금지가 아니라고 정정했습니다. 코드가 검사하는 조건은 "현재 트랜잭션이 snapshot을 갖고 있는가"입니다. REPEATABLE READ 트랜잭션은 첫 쿼리 전까지 snapshot이 없으므로 그 전에는 WAIT를 실행해도 됩니다. 반대로 READ COMMITTED여도 cursor가 열려 있으면 실행할 수 없습니다. 19beta4 standby에서 확인한 결과는 다음과 같습니다.

ERROR:  WAIT cannot be executed while the current transaction holds a snapshot
DETAIL:  This transaction runs at an isolation level higher than READ COMMITTED, so it holds a snapshot from its first query until it ends.

REPEATABLE READ에서 WAIT를 먼저 실행하고 첫 SELECT에서 snapshot을 얻으면 해당 트랜잭션 전체가 목표 LSN 이후의 데이터를 봅니다. 여러 쿼리에서 같은 시점의 데이터를 읽어야 하는 화면이라면 이 순서가 적합합니다.

잠금 보유 시 대기 거부​

snapshot이 없어도 잠금이 남아 있으면 같은 순환이 발생합니다. commit f7c092917(2026-09-12)의 설명은 다음과 같습니다. standby의 startup process가 replay 중에 이 세션이 가진 잠금을 필요로 하는 경우가 있습니다. 이때 startup은 세션의 잠금 해제를 기다리고 세션은 startup의 replay를 기다립니다. WAIT의 대기는 잠금 대기로 등록되지 않아 deadlock detector가 이 순환을 감지하지 못합니다. standby conflict 지연이 무제한이면 이 상태가 무기한 지속될 수 있습니다. 이 commit 이후에는 목표 LSN에 아직 도달하지 않았고 세션이 heavyweight lock을 하나라도 가지고 있으면 WAIT를 거부합니다. 19beta4 발표문의 "Several fixes for the new WAIT FOR command, including a deadlock and clearer reporting of isolation-level errors"에서 deadlock 부분은 날짜로 보아 이 수정을 가리키는 것으로 보입니다.

READ COMMITTED에서도 트랜잭션 안에서 앞선 SELECT가 획득한 AccessShareLock은 트랜잭션이 끝날 때까지 남습니다. 따라서 다음 순서는 거부됩니다.

lab=# BEGIN;
lab=*# SELECT v FROM t WHERE id = 1;
lab=*# WAIT FOR LSN '0/062759E0' WITH (TIMEOUT '100ms', NO_THROW);
ERROR:  cannot wait for a standby LSN while holding locks
DETAIL:  This session holds a lock on relation 16389 of database 16384, which could make recovery wait for this session while this session waits for recovery.
HINT:  Release the locks, or execute WAIT before acquiring them.

NO_THROW를 지정해도 이 오류는 그대로 발생합니다. 문서는 NO_THROW가 timeout과 not in recovery의 보고 방식만 바꾼다고 명시합니다. 세션 단위 advisory lock(pg_advisory_lock(42))을 획득한 상태에서도 같은 오류가 발생했습니다. 목표 LSN에 이미 도달한 경우에는 잠금이 있어도 즉시 success가 반환됐습니다. 문서에서 권하는 순서는 트랜잭션 밖에서, 또는 트랜잭션의 첫 문장으로 WAIT를 실행한 뒤 읽기 쿼리를 실행하는 것입니다.

timeline과 recovery conflict​

WAIT는 LSN 숫자만 비교합니다. 문서는 cascading standby의 upstream이 promote되어 timeline이 바뀐 경우에도 숫자만 넘으면 success를 반환한다고 명시합니다. timeline 확인이 필요하면 애플리케이션에서 직접 확인하라고 안내합니다. standby 자신이 기다리는 도중 promote되면 not in recovery가 반환되거나 오류가 발생합니다. 이 두 경우는 이번에 재현하지 않았습니다.

standby의 recovery conflict 때문에 WAIT 세션이 종료될 수도 있습니다. 문서는 tablespace drop replay처럼 모든 backend를 종료하는 경우를 예로 들며, 재시도나 대체 경로를 준비하라고 안내합니다.

점검 쿼리​

WAIT를 도입하기 전후에 확인할 쿼리입니다. 앞의 두 개는 이번 환경에서 실행해 확인했습니다. 세 번째의 wait event 이름은 소스의 wait_event_names.txt(REL_19_BETA4)를 기준으로 적었으며, 실제 대기 중인 세션에서 조회해 보지는 않았습니다.

primary에서 standby별 지연을 확인합니다. 20ms 조건에서 replay_lag는 약 20.6ms였고, WAIT 대기 시간 p50은 22.0ms로 서로 비슷했습니다.

SELECT application_name, sync_state,
       write_lag, flush_lag, replay_lag,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_behind_bytes
FROM pg_stat_replication;

standby에서 현재 replay 위치를 확인합니다. 문서에 따르면 standby_replay 모드가 성공하면 pg_last_wal_replay_lsn()이 목표 LSN 이상이 됩니다.

SELECT pg_last_wal_receive_lsn(),
       pg_last_wal_replay_lsn(),
       now() - pg_last_xact_replay_timestamp() AS since_last_replayed_xact;

standby에서 WAIT로 대기하는 세션을 찾습니다. 소스에 따르면 wait event는 WaitForWalReplay, WaitForWalWrite, WaitForWalFlush입니다.

SELECT pid, application_name, wait_event, now() - query_start AS waiting, query
FROM pg_stat_activity
WHERE wait_event IN ('WaitForWalReplay', 'WaitForWalWrite', 'WaitForWalFlush');

버전별 차이​

WAIT는 PostgreSQL 19에서 처음 추가된 명령입니다. remote_apply는 9.6부터 있었습니다. 18 이하에서 standby read-your-writes를 보장하려면 primary 읽기, remote_apply, 또는 애플리케이션에서 standby의 pg_last_wal_replay_lsn()을 반복 조회하는 방법을 사용해야 했습니다. 반복 조회 방식은 이번에 측정하지 않았습니다. 2020년 PostgreSQL 13 개발 주기에도 트랜잭션 시작 시 LSN을 기다리는 기능이 commit(0f5ca02f5)됐지만, 다음 날 revert(1aac32df8)된 적이 있습니다.

19 개발 주기의 주요 commit은 다음과 같습니다. 모두 REL_19_BETA4 태그(2026-09-21 stamp)에 포함돼 있습니다.

베타 기간에 문서 표현과 제약이 여러 번 바뀌었습니다. 페이지 경계 문제처럼 논의 중인 사안도 있습니다. 따라서 도입 시점에는 GA 버전의 문서와 릴리스 노트를 다시 확인하는 것이 좋습니다.

측정 결과 remote_apply와 WAIT의 전체 지연은 거의 같았어요. 차이는 그 지연을 쓰기와 읽기 중 어디에 두느냐였어요. 기다릴 LSN을 어떤 함수로 받을지는 GA 전까지 계속 지켜볼 생각이에요.

참고​

• WAIT 공식 문서 (19)
• Read-Your-Writes Consistency (devel)
• synchronous_commit (devel)
• PostgreSQL 19 릴리스 노트
• PostgreSQL 19 Beta 4 Released
• Re: test: avoid redundant standby catchup in 049_wait_for_lsn (pgsql-hackers, 2026-09-22)
• Read your writes: WAIT FOR in PostgreSQL 19 (ClickHouse 블로그)
• dbalog: 12.1 스트리밍 복제, 12.3 Hot standby와 query conflict, synchronous_commit

SAVEPOINT는 안전장치처럼 생겼어요. 트랜잭션 중간에 표시를 남기고, 일부만 되돌릴 수 있게 해 주는 기능이니까요. 그런데 이 안전장치를 한 트랜잭션에서 64번 넘게 쓰는 순간, 그 트랜잭션이 아니라 클러스터 전체가 절벽에서 떨어집니다.

PlanetScale이 8월 11일에 공개한 분석이 이 경로를 측정으로 보여줬습니다. 동일한 INSERT/SELECT/DELETE 워크로드가 평소 약 7,200 TPS(지연 약 2ms)로 돌다가, 어느 한 트랜잭션이 subtransaction 64개를 넘긴 순간 약 160 TPS(지연 약 90ms)로 떨어졌습니다. 45배 하락입니다. 문제의 트랜잭션이 끝나자 처리량은 원래대로 돌아왔습니다.

이 글은 해당 분석을 따라가면서, 왜 남의 트랜잭션 하나가 내 쿼리를 느리게 만드는지 경로를 짚고, 마지막에 원문에는 없는 우리 클러스터 점검용 진단 쿼리를 붙입니다.

subtransaction은 어디서 생기나​

subtransaction은 최상위 트랜잭션 안에 중첩된 트랜잭션입니다. 각 subtransaction은 자기만의 transaction ID(subxid)를 받습니다. 만드는 경로는 두 가지입니다.

명시적 경로는 SAVEPOINT입니다.

BEGIN;
SAVEPOINT s1;   -- subtransaction 1
INSERT INTO orders ...;
SAVEPOINT s2;   -- subtransaction 2
UPDATE stock ...;
COMMIT;

암묵적 경로가 더 위험합니다. PL/pgSQL에서 EXCEPTION 절이 있는 블록은 진입할 때마다 subtransaction을 하나 만듭니다. "혹시 실패하면 이 블록만 되돌린다"를 구현하는 방법이 내부적으로 SAVEPOINT와 같기 때문입니다.

-- 이 루프는 반복마다 subtransaction을 하나씩 만든다
FOR i IN 1..70 LOOP
  BEGIN
    INSERT INTO audit_log VALUES (...);
  EXCEPTION WHEN unique_violation THEN
    NULL;  -- 무시하고 계속
  END;
END LOOP;
-- 70개 생성, 64개 한계 초과

ORM도 조용히 만듭니다. Django의 transaction.atomic()을 중첩하면 안쪽은 SAVEPOINT가 되고, JPA/Hibernate에서 REQUIRES_NEW가 아닌 중첩 트랜잭션 전파, Rails의 중첩 transaction 블록(requires_new: true)도 같습니다. 애플리케이션 코드에는 SAVEPOINT라는 글자가 한 번도 안 나오는데 DB에는 수십 개씩 쌓이는 상황이 흔합니다.

64개 한계와 pg_subtrans​

각 backend의 PGPROC 구조체에는 subtransaction ID를 담는 캐시가 있고, 크기가 PGPROC_MAX_CACHED_SUBXIDS, 즉 64개입니다. 이 안에 다 들어가는 동안에는 다른 세션이 MVCC visibility를 판단할 때 공유 메모리만 보면 됩니다. 빠릅니다.

65개째부터 이 캐시는 overflow 상태로 표시됩니다. 이제 다른 세션들은 어떤 subxid가 어느 최상위 트랜잭션에 속하는지 공유 메모리에서 알 수 없고, 디스크 기반 구조인 pg_subtrans를 조회해야 합니다. pg_subtrans는 SLRU(simple least-recently-used) 캐시를 통해 접근하는데, 이 조회는 lightweight lock을 잡고 필요하면 디스크 I/O까지 발생시킵니다.

핵심은 비용을 치르는 쪽이 subtransaction을 만든 트랜잭션이 아니라는 점입니다. overflow가 켜져 있는 동안 다른 모든 세션이 자기 snapshot으로 튜플 가시성을 판단할 때마다(XidInMVCCSnapshot) pg_subtrans SLRU lock을 두고 경합합니다. PlanetScale 측정에서 병목으로 지목된 지점이 정확히 이 lock입니다.

overflow 상태는 문제의 트랜잭션이 끝날 때까지 유지됩니다. 그리고 얼마나 오래 가는지는 그 트랜잭션이 아니라, 클러스터에서 가장 오래 돌고 있는 트랜잭션의 수명에 좌우됩니다. long-running transaction이 있으면 그만큼 고통이 길어집니다.

hot standby까지 번지는 이유​

이 문제의 두 번째 얼굴이 더 고약합니다. overflow가 발생한 시점에 WAL로 기록되는 RUNNING_XACTS 레코드에 "subxid overflowed" 표시가 함께 남습니다. 활성 subtransaction 목록을 다 담지 못했다는 뜻입니다.

새로 배포한 standby가 이 오염된 RUNNING_XACTS를 읽으면, 일관된 snapshot을 만들 수 없다고 판단하고 hot standby 활성화를 보류합니다. WAL은 계속 재생하는데 읽기 쿼리는 받지 않는 상태, 접속하면 이런 메시지만 돌아옵니다.

FATAL:  the database system is not yet accepting connections
DETAIL:  Recovery snapshot is not yet ready for hot standby.

pg_subtrans를 primary에서 넘겨받으면 되지 않느냐는 생각이 들지만, pg_subtrans 변경은 WAL에 기록되지 않고, standby는 기동 시 활성 페이지를 0으로 초기화하기 때문에 로컬 데이터도 신뢰할 수 없습니다. standby가 이 상태를 벗어나는 조건은 overflow 되지 않은 새 RUNNING_XACTS 레코드가 도착하거나, primary의 shutdown checkpoint를 재생하거나, 누락 가능성이 있는 트랜잭션들이 전부 끝나는 것입니다.

운영 관점에서 시나리오를 조합하면 이렇게 됩니다. 부하가 튀어서 읽기 용량을 늘리려고 replica를 새로 붙였는데, 하필 그 부하를 만들던 워크로드가 subtransaction overflow를 일으키는 중이라면, 새 replica는 WAL만 재생하며 접속을 거부합니다. 용량을 더하려고 만든 노드가 용량이 되어 주지 않는 역설입니다.

지금 확인할 것들​

여기부터는 원문 내용에 우리가 운영에서 바로 실행할 쿼리를 더해 정리합니다.

먼저 지금 이 순간 subtransaction을 쌓고 있는 backend가 있는지 확인합니다. PostgreSQL 16부터 pg_stat_get_backend_subxact()로 backend별 subxact 개수와 overflow 여부를 볼 수 있습니다.

SELECT pg_stat_get_backend_pid(id) AS pid, s.subxact_count, s.subxact_overflowed
FROM pg_stat_get_backend_idset() AS id
JOIN LATERAL pg_stat_get_backend_subxact(id) AS s ON true
WHERE s.subxact_count > 0
ORDER BY s.subxact_count DESC;

subxact_overflowed = true인 행이 하나라도 보이면 그 순간 클러스터 전체가 pg_subtrans 조회 경로로 동작 중이라는 뜻입니다. pg_stat_activity와 조인하면 어떤 애플리케이션, 어떤 쿼리인지까지 특정됩니다.

과거에 겪었는지 의심된다면 SLRU 통계를 봅니다.

SELECT name, blks_hit, blks_read, blks_written, stats_reset
FROM pg_stat_slru
WHERE name = 'subtransaction';

평상시 이 카운터는 거의 움직이지 않습니다. blks_hit가 급증한 구간이 있었다면 overflow가 있었다는 강한 정황입니다. 그래프 도구에 이 두 컬럼을 올려 두면 사후 추적이 쉬워집니다.

가장 오래된 트랜잭션 나이도 함께 감시합니다. overflow의 지속 시간을 결정하는 값입니다.

SELECT max(now() - xact_start) AS oldest_tx
FROM pg_stat_activity
WHERE xact_start IS NOT NULL;

예방책은 소박합니다.

코어 쪽 근본 해법으로는 PGPROC_MAX_CACHED_SUBXIDS를 늘려 재컴파일하는 단기 우회(공유 메모리 비용 증가)와, snapshot을 CSN(Commit Sequence Number) 기반으로 바꾸는 장기 방향이 거론됩니다. CSN 논의는 10년 넘게 이어지고 있지만 아직 본류에 머지되지 않았습니다.

64라는 숫자를 기억해 두면 좋겠어요. SAVEPOINT와 EXCEPTION 블록은 공짜가 아니고, 비용 청구서는 옆 세션으로 날아갑니다. 저는 이번 주에 저희 클러스터에도 위 진단 쿼리 두 개를 모니터링에 추가해 두려고 합니다.

참고 자료​

• The Dangers of Postgres Subtransactions - PlanetScale, 2026-08-11
• PostgreSQL 문서: SAVEPOINT
• PostgreSQL 문서: pg_stat_slru