PostgreSQL EXPLAIN ANALYZE UPDATE는 실제로 행을 바꾼다

PostgreSQL에서 UPDATE의 실행 계획을 보려고 EXPLAIN ANALYZE를 실행했는데 행의 값이 달라졌다면, 분석 대상 쿼리가 실제로 실행된 것이다. 일반 EXPLAIN은 계획을 만들고, ANALYZE 옵션을 켜면 그 쿼리를 실행해 측정한다. 자동 커밋 상태였다면 뒤늦게 ROLLBACK을 입력해도 이미 끝난 변경을 취소할 수 없다.

계획만 필요하면 ANALYZE를 빼고, 실제 실행 결과가 필요하면 먼저 운영과 분리된 실습 DB를 준비한다. PostgreSQL 18의 EXPLAIN 문서↗ (새 탭에서 열림)는 ANALYZE의 기본값이 꺼짐이며, 켰을 때 쿼리의 부수 효과도 발생한다고 설명한다. EXPLAIN (ANALYZE FALSE)는 계획만 보는 쪽이고, EXPLAIN (ANALYZE TRUE)는 실제 실행하는 쪽이다.

같은 UPDATE 앞에 ANALYZE만 붙여 비교했다

다음은 운영 사고 기록이 아니라 전용 컨테이너의 PostgreSQL 18.6에서 합성 행 하나로 실행한 재현이다. 네트워크와 호스트 포트를 열지 않았고, 실험 뒤 컨테이너를 제거했다. 값이 바뀌는지 확인하는 실험이며 성능 비교는 아니다.

따라 하려면 폐기할 수 있는 PostgreSQL DB에 psql로 새로 접속하고, 아래 SQL을 끝까지 같은 연결에서 실행한다. 이 비교는 psql의 AUTOCOMMIT이 기본값인 on일 때를 전제로 한다. \echo :AUTOCOMMIT으로 확인할 수 있다. 임시 테이블도 실제 운영 연결이 아닌 실습 DB에 만든다.

첫 입력은 값 10을 가진 행을 만들고, 계획만 본 뒤에도 그 값이 유지되는지 확인한다.

CREATE TEMP TABLE explain_write_demo (id integer PRIMARY KEY, qty integer NOT NULL);
INSERT INTO explain_write_demo VALUES (1, 10);

EXPLAIN UPDATE explain_write_demo
SET qty = qty + 1 WHERE id = 1 RETURNING id, qty;
SELECT id, qty FROM explain_write_demo;

계획에는 Update on explain_write_demo와 인덱스 스캔이 표시됐지만, 마지막 SELECT의 결과는 (1, 10)이었다. 계획에 Update 노드가 있다는 사실만으로 실행됐다고 판단할 수는 없다.

이어서 같은 UPDATE에 ANALYZE를 붙여 값과 출력을 비교한다.

EXPLAIN ANALYZE UPDATE explain_write_demo
SET qty = qty + 1 WHERE id = 1 RETURNING id, qty;
SELECT id, qty FROM explain_write_demo;

이번에는 계획에 actual time과 Execution Time이 추가됐고, SELECT 결과는 (1, 11)이었다. 앞의 EXPLAIN과 달리 실제 UPDATE가 수행됐다.

두 UPDATE 모두 RETURNING id, qty를 붙였지만 EXPLAIN의 출력에는 (1, 11) 같은 반환 데이터가 없었다. 반환 행이 화면에 없다고 UPDATE가 실행되지 않은 것은 아니다. 공식 실행 계획 안내↗ (새 탭에서 열림)는 쿼리의 결과 데이터 대신 계획 정보를 출력한다고 설명한다. 여기서는 뒤따르는 SELECT로 저장된 값을 따로 확인했다.

이때 ROLLBACK;을 실행하자 WARNING: there is no transaction in progress가 나왔고, 다시 같은 SELECT를 해도 값은 11이었다. 트랜잭션 문서↗ (새 탭에서 열림)에서 설명하듯, 명시적인 BEGIN 없이 각 문장을 실행하면 성공한 문장은 개별적으로 커밋된다. 이 실험에서도 ANALYZE가 붙은 문장이 끝날 때 변경이 확정됐다. ROLLBACK 문서↗ (새 탭에서 열림)는 트랜잭션 블록 밖에서 실행한 롤백에는 경고 외의 효과가 없다고 명시한다. 사용하는 GUI나 드라이버가 트랜잭션을 자동으로 시작하는지는 별도로 확인해야 한다.

BEGIN은 실행 전에, 같은 연결에서 시작한다

테이블 변경을 남기지 않고 실측하려면 실행 전에 트랜잭션을 열어야 한다. 다음 입력은 앞에서 값이 11이 된 실습 테이블에 이어서 실행한다. 트랜잭션 안과 롤백 뒤의 값을 비교하는 것이 목적이다.

BEGIN;
EXPLAIN ANALYZE UPDATE explain_write_demo
SET qty = qty + 1 WHERE id = 1 RETURNING id, qty;
SELECT id, qty FROM explain_write_demo;
ROLLBACK;
SELECT id, qty FROM explain_write_demo;

첫 SELECT는 (1, 12), 롤백 뒤의 SELECT는 (1, 11)이었다. 롤백은 이번 트랜잭션의 변경을 취소했다. 앞서 자동 커밋된 10→11까지 취소한 것은 아니다. BEGIN·분석 쿼리·ROLLBACK을 서로 다른 연결에서 실행하면 이 묶음이 성립하지 않는다.

RETURNING 출력의 차이도 같은 테이블에서 대조할 수 있다. 아래는 EXPLAIN 없이 UPDATE 자체가 반환하는 데이터를 확인하고 그 변경은 취소하는 입력이다.

BEGIN;
UPDATE explain_write_demo
SET qty = qty + 1 WHERE id = 1 RETURNING id, qty;
ROLLBACK;
SELECT id, qty FROM explain_write_demo;

일반 UPDATE는 (1, 12) 한 행과 UPDATE 1을 출력했고, 롤백 뒤 SELECT는 (1, 11)이었다. UPDATE 문서↗ (새 탭에서 열림)의 RETURNING은 실제 갱신한 행을 바탕으로 값을 반환한다. 앞의 EXPLAIN ANALYZE도 값을 바꿨지만, 클라이언트에 내보낸 것은 그 행 데이터가 아니라 실행 계획이었다.

롤백 범위를 확인한 뒤 실측 대상을 고른다

이번에 직접 확인한 것은 트리거·시퀀스·사용자 정의 함수가 없는 임시 테이블의 정수 열 변경이다. 이 조건에서는 BEGIN 안의 변경이 롤백됐다. 이 결과를 “어떤 쿼리든 마지막에 ROLLBACK만 쓰면 안전하다”로 확대할 수는 없다.

시퀀스 공식 문서↗ (새 탭에서 열림)는 nextval이 소비한 번호를 트랜잭션 취소 때 다시 돌려놓지 않으며, setval의 상태 변경도 롤백되지 않는다고 설명한다. 이는 문서로 확인한 예외이고 이번 재현에서는 실행하지 않았다. 트리거가 실행하는 함수, 그 함수의 외부 호출과 외부 시스템에 남는 효과도 이번 실험의 검증 범위에 없다. 해당 경로를 확인하지 않은 채 롤백이 모두 없애 준다고 가정해서는 안 된다.

실행 시간이나 실제 처리 행 수가 필요하지 않다면 일반 EXPLAIN으로 필요한 계획을 먼저 확인한다. 실측이 필요하면 별도 DB에 필요한 스키마·인덱스와 합성 데이터 또는 허용된 복제 데이터를 준비하고, 트리거·함수의 외부 연결도 운영에서 분리한다. 그 환경에서 BEGIN과 ROLLBACK으로 테이블 변경의 범위를 묶는다. 데이터 양·분포·통계가 다르면 계획과 시간도 달라질 수 있으므로, 이 한 행 실험의 계획이나 시간을 운영 성능으로 옮겨 해석하지 않는다. EXPLAIN ANALYZE 자체의 측정 비용도 있다.

예제와 다르게 값이 그대로라면 먼저 대상 행이 조건에 맞는지, 쿼리가 오류 없이 끝났는지, 같은 연결과 테이블을 조회했는지 확인한다. 롤백 뒤 값이 10이 아니라 11인 것은 위 실행 순서에서는 정상이다.