출간 준비 중인 < SQL TUNER for PostgreSQL · 기본원리편 >의 미리보기입니다.
PostgreSQL에 접속하는 SQL 툴은 오토 커밋(Auto Commit)과 매뉴얼 커밋(Manual Commit) 두 가지 모드를 지원한다. 기본값은 오토 커밋이다. 이에 대해서는 ‘9-1-2. 오토 커밋과 명시적 트랜잭션’ 절에서 다루었다.
반면 Oracle은 매뉴얼 커밋이 기본 모드다. 매뉴얼 커밋은 실수로 인한 데이터 손상을 방지하는 안전 장치 역할을 한다. 예를 들어 Oracle에서 실수로 다음과 같은 SQL을 실행했다고 가정해 보자.
-- [SQL-10-3-5-a]: Oracle에서 아래 SQL을 실행한다고 가정
UPDATE startdbpg.tr_ord_vac t1
SET ord_amt = 0
-- WHERE ord_no = 10;
SQL
복사
위 SQL의 원래 의도는 10번 주문의 금액만 0으로 변경하는 것이었지만 WHERE 절을 실수로 주석 처리했다. 따라서 이 SQL이 실행되면 모든 주문 금액이 0이 된다. 다행히 Oracle은 매뉴얼 커밋 모드이므로 실수를 인지하면 롤백을 실행해 원래의 상태로 되돌아 갈 수 있다.
이런 장점 때문에 Oracle을 오랜 시간 사용해온 개발자들은 데이터 안전을 이유로 PostgreSQL에서도 매뉴얼 커밋을 사용해야 한다고 주장한다. 하지만 PostgreSQL에서는 매뉴얼 커밋이 예상치 못한 심각한 문제를 일으킬 수 있다.
아래와 같이 SQL 창을 두 개 열어 왼쪽(A 세션)은 매뉴얼 커밋 모드로 변경하고 오른쪽(B 세션)은 오토 커밋 모드를 유지한다. A 세션에서는 단순 SELECT를, B 세션에서는 UPDATE와 베큠을 실행한다. A 세션과 B 세션을 순서대로 오가면서 SQL을 실행해 보자.
-- [SQL-10-3-5-b] A 세션
-- A 세션은 매뉴얼 커밋으로 변경
-- DBeaver 메뉴로 매뉴얼 커밋 모드로 변경
-- 데이터베이스>트랜잭션모드>Manual Commit
-- 1. 매뉴얼 커밋에서 tr_ord_vac를 조회
SELECT *
FROM startdbpg.tr_ord_vac t1
WHERE t1.ord_no = 10;
-- 7. COMMIT
COMMIT;
SQL
복사
-- [SQL-10-3-5-b] B 세션
-- B 세션은 오토 커밋을 유지
-- 2. 1,000건 UPDATE(데드 튜플 1,000개 생성)
UPDATE startdbpg.tr_ord_vac t1
SET ord_st = t1.ord_st
WHERE t1.ord_no <= 1000;
-- 3. 데드 튜플 확인(n_dead_tup = 1000)
SELECT t1.n_dead_tup ,t1.n_live_tup
FROM pg_stat_user_tables t1
WHERE t1.relid = 'startdbpg.tr_ord_vac'::regclass;
-- 4. 베큠 수행(VERBOSE로 상세 정보 출력)
VACUUM VERBOSE startdbpg.tr_ord_vac;
-- 5. 다시 데드 튜플 확인(n_dead_tup이 그대로 1,000)
SELECT t1.n_dead_tup ,t1.n_live_tup
FROM pg_stat_user_tables t1
WHERE t1.relid = 'startdbpg.tr_ord_vac'::regclass;
-- 6. tr_ord_vac에 락을 잡고 있는 pid 확인
SELECT t1.locktype, t1.pid
FROM pg_locks t1
WHERE t1.relation = 'startdbpg.tr_ord_vac'::regclass;
SQL
복사
-- B 세션의 4번 SQL(VACUUM VERBOSE)의 output
-- B 세션의 4번 SQL(VACUMM VERBOSE)의 output
vacuuming "startdbpg.startdbpg.tr_ord_vac"
…
tuples: 0 removed, 8024515 remain, 1000 are dead but not yet removable
removable cutoff: 3572666, which was 1 XIDs old when operation ended
…
SQL
복사
위 테스트 과정에서 4번 SQL의 출력(output) 결과를 보면 ‘1000 are dead but not yet removable’ 이라고 표시되어 있다. 데드 튜플이 1,000개 있지만 제거할 수 없다고 출력되었다.
매뉴얼 커밋 모드에서는 SELECT 쿼리만 실행해도 트랜잭션이 시작된다. 이 트랜잭션이 열려 있는 동안, PostgreSQL은 해당 트랜잭션에서 볼 수 있는 데이터를 보존해야 한다. 따라서 베큠이 실행되어도 데드 튜플을 제거할 수 없다. A 세션에서 커밋을 실행한 후 다시 베큠을 수행해야 데드 튜플이 제거된다.
실제 운영 환경에서도 이러한 문제가 발생한 사례가 제법 있다. 개발자가 매뉴얼 커밋 모드로 장시간 접속한 상태에서 배치 작업이 반복 실행되었고, 베큠이 데드 튜플을 제거하지 못해 테이블이 비정상적으로 비대해졌다. 그 결과 조회 성능이 수십 배 이상 저하되어 시스템 장애로 이어졌다.
데이터와 시스템 안전을 위해서는 매뉴얼 커밋보다는 다음과 같은 프로세스를 수립하는 것이 필요하다.
•
접속 모드: 무조건 오토 커밋 모드로 접속한다.
•
계정 분리: 읽기 전용 계정과 쓰기 권한 계정을 분리한다.
•
접근 통제: 쓰기 권한 계정은 승인된 작업에만 한시적으로 사용한다.
•
작업 절차: 변경 작업은 사전 승인 → 작업 수행 → 즉시 로그아웃 절차를 따른다.
◦
데이터 변경 전에 BEGIN을 사용해 명시적으로 트랜잭션을 시작
◦
문제없이 작업을 완료했다면 COMMIT으로 명시적 트랜잭션 종료 후 로그아웃
금융권이나 개인정보를 다루는 시스템일수록 이런 엄격한 통제 체계를 운영한다. 불편하고 번거로워 보이지만, 장기적으로는 이것이 데이터를 안전하게 보호하는 방법이다. PostgreSQL에서는 특히 매뉴얼 커밋의 부작용을 고려하면, 이런 체계적인 접근이 더욱 중요하다