PostgreSQL의 락·인덱스·실행계획과 트랜잭션
같은 행을 바꾸려는 작업의 접근 순서를 조절합니다.
활용 동시 수정과 락 대기 이해
두 세션이 같은 행을 수정하려 합니다. 먼저 락을 얻은 트랜잭션이 끝날 때까지 충돌하는 수정은 기다립니다.
PostgreSQL 18의 행 락과 Read Committed 예제입니다. UPDATE와 SELECT FOR UPDATE가 같은 행에서 충돌하면 트랜잭션 종료를 기다립니다. 보통의 SELECT는 MVCC 스냅샷을 읽으므로 같은 행 락 때문에 기다리지는 않지만 DDL의 테이블 락 등은 별개입니다. 락은 정합성을 돕는 대신 대기와 경합을 만듭니다.
서로 가진 락을 기다리면 둘 다 멈출 수 있습니다.
활용 락 순서 설계와 트랜잭션 재시도
두 트랜잭션이 행 A와 B를 서로 다른 순서로 잠그려 합니다. 아직 대기 순환은 없습니다.
예제는 행 락 두 개를 반대 순서로 얻어 대기 순환을 만듭니다. PostgreSQL은 데드락을 감지하면 한 트랜잭션을 중단해 해소하지만, 어느 트랜잭션이 선택될지는 고정하지 않습니다. 중단된 업무는 트랜잭션 전체를 재시도하고 자원 획득 순서를 통일해 순환을 예방합니다.
검색 키를 따로 정리해 읽을 데이터를 줄입니다.
활용 조회 조건에 맞는 인덱스 설계
인덱스가 없으면 테이블의 행을 확인하며 조건에 맞는 데이터를 찾습니다. 그림은 여덟 행의 교육용 예제입니다.
SELECT * FROM items WHERE id = 14;그림은 같은 조건을 순차 스캔과 B-tree 인덱스 접근으로 비교하는 모형입니다. 인덱스가 있어도 작은 테이블이나 대부분의 행을 읽는 조건에서는 순차 스캔이 유리할 수 있습니다. 인덱스는 저장 공간과 삽입·수정·삭제 시 유지 비용을 추가하며 모든 인덱스가 B-tree인 것은 아닙니다.
쿼리의 예상과 실제 실행 결과를 비교합니다.
활용 느린 쿼리의 원인 찾기
EXPLAIN은 플래너가 예상한 접근 경로와 비용을 보여 줍니다. 이 단계에서는 쿼리 결과를 실제로 계산하지 않습니다.
EXPLAIN SELECT * FROM items WHERE id = 14;PostgreSQL 18의 설명용 계획이며 숫자는 실측 벤치마크가 아닙니다. cost는 시간 단위가 아닌 추정 비용입니다. ANALYZE 옵션은 문장을 실제 실행합니다. actual rows와 time은 반복 실행의 평균이며 loops를 함께 읽고 상·하위 노드 시간을 단순 합산하지 않습니다. 버퍼와 실행계획은 데이터·캐시·통계에 따라 바뀝니다.
여러 변경을 함께 확정하거나 함께 취소합니다.
활용 이체·주문처럼 묶여야 하는 작업
이체의 출금과 입금을 한 트랜잭션으로 묶습니다. 시작 시 두 계좌의 커밋 값은 각각 100입니다.
BEGIN;PostgreSQL 18의 Read Committed 예제로 계좌 간 이체를 보여 줍니다. BEGIN 이후 중간 변경은 다른 세션의 일반 조회에 공개하지 않고 COMMIT에서 확정합니다. 오류·제약 위반 시 ROLLBACK으로 트랜잭션을 종료합니다. 격리 수준과 충돌 상황에 따라 대기·실패·재시도가 필요합니다.
트랜잭션의 보장을 네 가지 원칙으로 나눕니다.
활용 데이터 보호 범위와 한계 이해
ACID는 하나의 성질이 아니라 네 보장을 묶은 이름입니다. 같은 이체 예제에서 각각의 의미를 구분합니다.
원자성은 전부 반영 또는 취소, 일관성은 선언한 제약과 업무 불변식, 격리성은 동시 실행의 관찰 규칙, 지속성은 커밋 결과의 보존입니다. DB가 모든 업무 규칙을 자동으로 알아내지는 않으며 격리 수준마다 보장이 다릅니다. 지속성 예제는 PostgreSQL의 정상적인 영구 테이블과 synchronous_commit=on을 가정합니다.