데이터베이스 내부 동작: ACID 구현, 격리 수준, 인덱스 구조와 쿼리 최적화

작성 0xmote · 2026년 8월 30일 · 15분 읽기 · 수정 2026년 10월 9일 고급 튜토리얼

이 글의 핵심

관계형 데이터베이스의 동작 원리를 엔진 관점에서 정리합니다. ACID가 로그·버퍼·잠금으로 어떻게 구현되는지, 격리 수준과 이상 현상, B-tree와 해시 인덱스의 역할, 옵티마이저 기반 쿼리 튜닝의 흐름, 그리고 실서비스에서 쓰는 연결 풀·복제·관측 패턴을 다룹니다.

들어가며

관계형 데이터베이스(RDBMS)는 SQL 처리기만이 아니라 스토리지, 버퍼 풀, 선행 기록 로그(WAL), 동시성 제어가 결합된 시스템입니다. “쿼리를 보내면 결과가 나온다”는 수준을 넘어 운영, 튜닝, 장애 분석을 하려면 엔진이 ACID와 격리를 어떻게 보장하는지, 인덱스가 실행 계획에 어떻게 연결되는지를 이해해야 합니다.

이 글은 특정 제품의 매뉴얼이 아니라 PostgreSQL과 MySQL(InnoDB) 등에서 공통으로 등장하는 개념을 중심으로 정리합니다. 실제 설정값과 세부 동작은 버전과 환경에 따라 다르므로, 원리를 잡은 뒤 공식 문서와 EXPLAIN 결과로 확인하는 것이 좋습니다.

ACID 속성의 구현

ACID는 프로토콜이 아니라 트랜잭션이 만족해야 할 성질을 네 가지로 나눈 것입니다. 엔진은 보통 로그, 버퍼 관리, 잠금 또는 MVCC를 조합해 이를 달성합니다.

원자성(Atomicity)

트랜잭션의 변경은 모두 반영되거나 하나도 반영되지 않아야 합니다. 데이터 페이지를 디스크에 바로 덮어쓰기만 하면 장애 시 중간 상태가 남을 수 있으므로, 대부분의 엔진은 선행 기록 로그(write-ahead logging, WAL)를 둡니다. 변경은 먼저 순차적인 로그에 기록되고, 데이터 파일은 체크포인트와 백그라운드 플러시를 통해 나중에 따라갑니다. 핵심 규칙은 “데이터 페이지가 디스크에 쓰이기 전에 그 변경을 설명하는 로그가 먼저 디스크에 있어야 한다”는 것입니다.

장애 후 재시작하면 엔진은 로그를 읽어 커밋된 변경 중 데이터 파일에 아직 반영되지 않은 것을 다시 적용(redo)하고, 커밋되지 않은 트랜잭션이 남긴 변경을 무효화합니다. InnoDB는 별도의 undo 로그로 미완료 변경을 되돌리고, PostgreSQL은 행의 옛 버전을 그대로 두는 MVCC 구조라서 커밋 로그(clog)에 그 트랜잭션을 중단으로 표시하는 것만으로 미완료 변경이 보이지 않게 됩니다. 체크포인트는 “이 시점까지의 변경은 데이터 파일에 반영되었다”는 경계를 만들어, 재시작 시 다시 적용할 로그 범위를 줄입니다.

여러 데이터베이스에 걸친 갱신을 원자적으로 하려면 2단계 커밋(2PC) 같은 분산 커밋 프로토콜이 필요하지만, 코디네이터 장애 시 참여자가 결과를 모르는 채로 잠금을 쥐고 기다리는 상태가 생길 수 있고 운영 복잡도가 큽니다. 그래서 마이크로서비스에서는 가능하면 하나의 데이터베이스 트랜잭션 안에서 끝내거나, 사가 패턴, 아웃박스 패턴, 멱등한 재시도로 경계를 나누는 설계가 흔합니다.

애플리케이션 쪽에서 가장 흔한 실수는 트랜잭션 경계를 의도와 다르게 잡는 것입니다. ORM의 자동 커밋 모드에서 여러 쿼리를 실행하면 각 쿼리가 따로 커밋되므로, 중간에 실패하면 일부만 반영된 상태가 남습니다. 함께 성공하거나 실패해야 하는 작업은 명시적인 트랜잭션으로 묶고, 예외 경로에서도 롤백되는지 확인해야 합니다.

일관성(Consistency)

데이터베이스 문맥의 일관성에는 두 층이 있습니다. 하나는 제약 조건, 외래 키, 트리거로 스키마 수준에서 유지되는 불변식이고, 다른 하나는 “계좌 잔액의 합은 변하지 않는다” 같은 비즈니스 규칙입니다. DBMS는 선언된 제약을 어기는 커밋을 거부해 유효한 상태에서 유효한 상태로만 이동하도록 돕지만, 모든 도메인 규칙을 제약으로 표현할 수는 없으므로 나머지는 애플리케이션이 트랜잭션과 올바른 격리 수준으로 지켜야 합니다.

격리성(Isolation)

동시에 실행되는 트랜잭션이 서로의 중간 결과를 어디까지 볼 수 있는지는 격리 수준과, 그것을 구현하는 MVCC와 잠금으로 결정됩니다. 다음 절에서 자세히 다룹니다.

지속성(Durability)

커밋이 성공했다고 응답했다면 장애 후에도 그 결과가 남아야 합니다. 이를 위해 엔진은 커밋 응답 전에 해당 WAL을 안정 저장소에 동기화(fsync 등)합니다. 여러 트랜잭션의 커밋을 모아 한 번에 동기화하는 그룹 커밋은 처리량을 크게 높이고, PostgreSQL의 synchronous_commit = off나 InnoDB의 innodb_flush_log_at_trx_commit = 2처럼 동기화를 미루는 설정은 성능을 얻는 대신 장애 시 마지막 순간의 커밋을 잃을 수 있습니다. 복제는 단일 노드 디스크의 지속성과는 별개의 문제로, 비동기 복제라면 페일오버 시 복제되지 않은 커밋이 사라질 수 있습니다.

트랜잭션 격리 수준

SQL 표준은 네 가지 격리 수준을 정의합니다. 제품마다 세부 동작과 기본값이 다르므로, 표준의 이름과 사용하는 데이터베이스의 실제 동작을 함께 확인해야 합니다.

네 가지 격리 수준과 이상 현상

격리 수준	더티 리드	비반복 읽기	팬텀 리드
READ UNCOMMITTED	허용될 수 있음	가능	가능
READ COMMITTED	방지	가능	가능
REPEATABLE READ	방지	방지	표준상 가능 (구현에 따라 방지)
SERIALIZABLE	방지	방지	방지

더티 리드(dirty read)는 아직 커밋되지 않은 다른 트랜잭션의 변경을 읽는 것입니다. 비반복 읽기(non-repeatable read)는 같은 행을 두 번 읽었을 때 그사이 다른 트랜잭션이 커밋한 변경 때문에 값이 달라지는 것입니다. 팬텀 읽기(phantom read)는 같은 범위 조건으로 두 번 조회했을 때 다른 트랜잭션이 행을 추가하거나 삭제해서 결과 집합이 달라지는 것입니다. PostgreSQL은 READ UNCOMMITTED를 READ COMMITTED와 똑같이 동작시키므로 더티 리드가 일어나지 않습니다.

표준 표에 없는 이상 현상도 있습니다. 읽기 스큐(read skew)는 한 트랜잭션이 서로 관련된 두 행을 서로 다른 시점의 상태로 읽어서, 이체 중인 두 계좌의 잔액 합이 맞지 않게 보이는 것 같은 현상입니다. READ COMMITTED에서는 문장마다 새 스냅샷을 쓰므로 일어날 수 있고, 트랜잭션 전체에 하나의 스냅샷을 쓰는 REPEATABLE READ에서는 막힙니다. 쓰기 스큐(write skew)는 두 트랜잭션이 같은 조건을 읽고 각자 다른 행을 수정해 전체 불변식을 깨는 현상입니다. 예를 들어 “당직 의사가 최소 한 명은 있어야 한다”는 규칙에서 두 의사가 동시에 “다른 사람이 당직이니 나는 빠져도 된다”고 판단하고 각자 자기 행만 고치면 둘 다 빠지게 됩니다. 같은 행을 두고 충돌하지 않기 때문에 스냅샷 기반 REPEATABLE READ에서도 막히지 않으며, SERIALIZABLE이나 관련 행에 대한 명시적 잠금이 필요합니다.

MVCC와 스냅샷

다중 버전 동시성 제어(MVCC)는 행의 여러 버전을 유지하고, 각 트랜잭션이 자기 스냅샷 시점에 커밋되어 있던 버전을 읽도록 합니다. 그래서 읽기는 쓰기를 기다리지 않고 쓰기도 읽기를 기다리지 않습니다. 하지만 두 트랜잭션이 같은 행을 갱신하려 하면 행 잠금으로 순서가 정해지고, 잠금을 서로 다른 순서로 잡으면 데드락이 생깁니다. 옛 버전은 어떤 트랜잭션도 더 이상 볼 수 없게 된 뒤에 정리되므로(PostgreSQL의 VACUUM, InnoDB의 purge), 오래 열린 트랜잭션은 이 정리를 막아 테이블과 undo 영역을 부풀립니다.

제품별 기본값

PostgreSQL의 기본 격리 수준은 READ COMMITTED입니다. REPEATABLE READ는 트랜잭션 시작 시점의 스냅샷을 쓰는 스냅샷 격리로 구현되어 팬텀도 보이지 않지만, 쓰기 스큐는 막지 못합니다. SERIALIZABLE은 SSI(Serializable Snapshot Isolation)로 구현되어 직렬화 이상이 생길 수 있는 트랜잭션을 직렬화 실패 오류로 중단시키므로, 애플리케이션이 재시도해야 합니다.

MySQL InnoDB의 기본은 REPEATABLE READ입니다. 일반 SELECT는 트랜잭션 첫 읽기 시점의 스냅샷을 읽고, SELECT ... FOR UPDATE나 UPDATE 같은 잠금 읽기는 최신 커밋 버전을 읽으면서 넥스트 키 락과 갭 락으로 범위에 새 행이 들어오지 못하게 막습니다. 같은 트랜잭션 안에서도 일반 읽기와 잠금 읽기가 서로 다른 결과를 볼 수 있다는 점이 InnoDB REPEATABLE READ의 특징입니다.

격리 수준을 올리기 전에 실제로 어떤 이상이 문제인지 먼저 정의해야 합니다. 보고서나 정산처럼 여러 테이블을 같은 시점 기준으로 읽어야 한다면 하나의 스냅샷으로 읽는 트랜잭션이 필요하고, 재고 차감처럼 같은 행을 두고 경쟁하는 갱신이라면 SELECT ... FOR UPDATE, 버전 컬럼을 이용한 낙관적 잠금, 또는 UPDATE ... SET stock = stock - 1 WHERE id = ? AND stock > 0처럼 조건을 갱신문 안에 넣는 방식이 더 직접적인 해결책입니다.

-- PostgreSQL: 현재 트랜잭션에만 적용 (BEGIN 직후, 첫 쿼리 전에 실행)
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- ...
COMMIT;

-- PostgreSQL: 이 세션의 이후 트랜잭션 기본값 변경
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- MySQL: 이 세션의 이후 트랜잭션에 적용
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

서비스 전체의 기본값을 바꾸기보다, 문제가 되는 배치나 리포트 쿼리만 별도의 격리 수준으로 실행하거나 읽기 전용 복제본으로 분리하는 편이 안전합니다.

인덱싱 전략: B-tree와 Hash

B-tree가 널리 쓰이는 이유

대부분의 RDBMS는 기본 인덱스로 B+tree 계열을 사용합니다. 노드 하나를 디스크 페이지 하나에 맞추고 노드마다 수백 개의 키를 담으므로, 수억 개의 행도 트리 높이 3~4 정도로 찾을 수 있습니다. 키가 정렬된 상태로 저장되므로 등치 검색뿐 아니라 범위 조건(BETWEEN, >, <), ORDER BY, 복합 인덱스의 앞쪽 컬럼만 쓰는 검색에도 인덱스를 쓸 수 있고, 리프 노드가 서로 연결되어 있어 범위를 순서대로 읽기 좋습니다.

InnoDB는 테이블 자체가 기본 키 순서의 B+tree(클러스터 인덱스)이고, 보조 인덱스의 리프에는 행 위치 대신 기본 키 값이 들어 있습니다. 그래서 보조 인덱스로 찾은 뒤 기본 키로 한 번 더 트리를 탐색해야 하고, 기본 키가 길면 모든 보조 인덱스가 함께 커집니다. PostgreSQL은 테이블(힙)과 인덱스가 분리되어 있어 인덱스 항목이 힙의 행 위치를 직접 가리킵니다.

Hash 인덱스

해시 인덱스는 키를 해시 버킷에 매핑해 등치 비교를 평균 상수 시간에 처리합니다. 대신 범위 검색, 정렬, 앞부분 일치 검색에는 쓸 수 없습니다. PostgreSQL의 해시 인덱스는 버전 10부터 WAL에 기록되어 장애 복구와 복제에서 안전하게 쓸 수 있게 되었지만, 같은 등치 검색도 B-tree가 충분히 빠른 경우가 많아 쓰임은 제한적입니다. MySQL은 MEMORY 엔진 테이블이 해시 인덱스를 지원하고, InnoDB는 자주 조회되는 B-tree 페이지에 대해 내부적으로 적응형 해시 인덱스를 만들어 쓰기도 합니다(설정으로 끌 수 있습니다).

인덱스를 설계할 때 볼 것

컬럼 값의 종류가 적어 한 값에 해당하는 행이 많다면, 그 컬럼만의 인덱스는 효과가 작습니다. 테이블의 상당 부분을 읽어야 한다면 옵티마이저는 인덱스를 거쳐 행을 하나씩 찾는 것보다 테이블 전체를 순차로 읽는 쪽을 고르기 때문입니다.

복합 인덱스는 컬럼 순서가 중요합니다. 등치 조건 컬럼을 앞에, 범위 조건 컬럼을 뒤에 두는 것이 일반적인 원칙이고, 정렬이나 그룹화에 쓰는 컬럼도 함께 고려해야 합니다. (a, b) 인덱스는 a만으로 검색할 때 쓰이지만 b만으로 검색할 때는 대개 쓰이지 않습니다.

쿼리에 필요한 모든 컬럼이 인덱스에 들어 있으면(커버링 인덱스) 테이블을 읽지 않고 인덱스만으로 답할 수 있어 랜덤 I/O가 줄어듭니다. 반대로 인덱스는 삽입, 갱신, 삭제 때마다 함께 수정해야 하므로, 많이 만들수록 쓰기가 느려지고 저장 공간과 버퍼 풀을 차지합니다. 쓰이지 않는 인덱스는 PostgreSQL의 pg_stat_user_indexes나 MySQL의 sys.schema_unused_indexes로 찾아 정리합니다.

쿼리 최적화의 기본 흐름

옵티마이저와 통계

옵티마이저는 테이블과 인덱스의 통계(행 수, 값 분포, NULL 비율 등)를 바탕으로 비용 모델을 계산해 실행 계획을 고릅니다. 통계가 오래되었거나 데이터 분포를 잘 반영하지 못하면 예상 행 수가 실제와 크게 달라지고, 그 결과 불필요한 전체 스캔이나 나쁜 조인 순서가 선택됩니다. PostgreSQL의 autovacuum은 통계도 자동으로 갱신하지만, 대량 적재 직후처럼 데이터가 갑자기 바뀌었다면 ANALYZE(MySQL은 ANALYZE TABLE)를 직접 실행하는 것이 좋습니다.

실행 계획 읽기

EXPLAIN은 옵티마이저가 고른 계획과 예상 비용을, EXPLAIN ANALYZE(PostgreSQL, MySQL 8.0.18 이상)는 실제로 쿼리를 실행해 단계별 실제 행 수와 시간을 보여 줍니다. 비용 숫자 자체보다 예상 행 수와 실제 행 수가 크게 어긋나는 단계를 찾는 것이 핵심입니다. 그 단계가 잘못된 계획 선택의 출발점인 경우가 많습니다. EXPLAIN ANALYZE는 쿼리를 실제로 실행하므로, UPDATE나 DELETE를 분석할 때는 트랜잭션 안에서 실행하고 롤백해야 합니다.

인덱스만 읽고 답하는 계획은 PostgreSQL에서 Index Only Scan, MySQL에서는 Extra 열의 Using index로 표시됩니다. PostgreSQL은 인덱스에 행의 가시성 정보가 없으므로, 해당 페이지가 가시성 맵(visibility map)에 “모든 행이 보임”으로 표시되어 있을 때만 힙 접근을 건너뜁니다. 최근에 많이 갱신된 테이블이라면 Index Only Scan이라도 Heap Fetches가 많을 수 있으므로 EXPLAIN ANALYZE 출력에서 확인합니다. InnoDB의 보조 인덱스는 이런 제약 없이 커버링이 되지만, 앞서 말했듯 보조 인덱스에는 기본 키가 들어 있으므로 기본 키 컬럼도 커버링 대상에 포함됩니다.

애플리케이션 패턴

성능 문제의 상당수는 쿼리 하나가 아니라 애플리케이션이 쿼리를 보내는 방식에서 나옵니다. ORM에서 목록을 가져온 뒤 루프 안에서 연관 객체를 하나씩 조회하면 행 수만큼 쿼리가 나가는 N+1 문제가 생깁니다. 조인이나 IN 조건을 쓰는 일괄 로딩으로 바꾸면 왕복 횟수가 줄어듭니다. 필요 없는 컬럼까지 가져오는 SELECT *는 네트워크 전송과 디코딩 비용을 늘리고 커버링 인덱스를 쓸 수 없게 만듭니다. 큰 트랜잭션은 잠금을 오래 쥐고 MVCC의 옛 버전 정리를 막으므로, 대량 갱신은 적당한 크기로 나눠 커밋합니다.

MySQL 실행 계획 해석은 MySQL EXPLAIN 가이드에서 더 자세히 다룹니다.

프로덕션 데이터베이스 패턴

연결 관리

데이터베이스 연결을 맺는 데는 TCP 연결, 인증, 세션 초기화 비용이 들고, PostgreSQL은 연결마다 서버 프로세스를 하나씩 만듭니다. 그래서 애플리케이션은 연결 풀(HikariCP 같은 드라이버 수준 풀, PgBouncer나 RDS Proxy 같은 외부 풀러)로 연결을 재사용합니다. 풀 크기는 클수록 좋은 것이 아닙니다. 데이터베이스가 동시에 효율적으로 처리할 수 있는 쿼리 수는 CPU 코어와 디스크 성능에 묶여 있으므로, 연결을 너무 많이 열면 문맥 전환과 잠금 경합으로 오히려 처리량이 떨어집니다. 모든 애플리케이션 인스턴스의 풀 크기를 합친 값이 서버의 max_connections를 넘지 않는지도 확인해야 합니다.

읽기 확장과 일관성

읽기 복제본은 읽기 부하를 나눠 주지만, 비동기 복제라면 Primary에 커밋된 변경이 복제본에 보이기까지 지연이 있습니다. 사용자가 방금 저장한 데이터를 바로 보여 줘야 하는 화면에서 복제본을 읽으면 저장한 내용이 사라진 것처럼 보입니다. 쓰기 직후 일정 시간 동안은 그 사용자의 읽기를 Primary로 보내거나, 복제본이 특정 로그 위치까지 따라왔는지 확인한 뒤 읽는 방식으로 자기 쓰기 읽기(read-your-writes)를 보장합니다.

가용성과 백업

자동 페일오버, 전체 백업과 증분 백업(또는 WAL 아카이빙을 이용한 시점 복구), 그리고 복구 목표 시간(RTO)과 복구 시점 목표(RPO)를 문서화해 둡니다. 백업은 실제로 복원해 보기 전까지는 백업이라고 할 수 없으므로 주기적으로 복원 훈련을 합니다. 스키마 마이그레이션은 새 컬럼을 추가하고 양쪽을 모두 쓰다가 옛 컬럼을 제거하는 expand-contract 방식, 이중 쓰기, 배치 백필 등으로 무중단에 가깝게 진행하는 경우가 많습니다. 큰 테이블에 대한 DDL은 제품과 버전에 따라 테이블 전체를 잠글 수 있으므로 사전에 확인해야 합니다.

관측성

느린 쿼리는 슬로우 쿼리 로그, PostgreSQL의 pg_stat_statements, MySQL의 Performance Schema로 찾습니다. 개별 실행 시간보다 총 실행 시간(호출 수 × 평균 시간) 기준으로 정렬하면, 한 번은 빠르지만 아주 자주 호출되는 쿼리처럼 실제 부하의 대부분을 차지하는 쿼리가 드러납니다. 애플리케이션 트레이스와 데이터베이스 메트릭을 함께 보면 지연이 데이터베이스 안에서 생기는지, 연결 풀 대기나 네트워크에서 생기는지 구분할 수 있습니다.

동시성과 비즈니스 규칙

같은 데이터를 두고 경쟁하는 갱신에는 버전 컬럼을 이용한 낙관적 잠금, 유일 제약과 UPSERT, 명시적인 행 잠금 등을 도메인에 맞게 고릅니다. 유일성처럼 데이터베이스 제약으로 표현할 수 있는 규칙은 애플리케이션 코드의 “조회 후 삽입”보다 제약에 맡기는 편이 안전합니다. 조회와 삽입 사이에 다른 트랜잭션이 끼어들 수 있기 때문입니다. Redis 같은 외부 분산 락은 데이터베이스 트랜잭션과 원자적으로 묶이지 않으므로, 락 만료나 네트워크 지연으로 두 프로세스가 동시에 락을 가졌다고 믿는 상황을 데이터베이스 쪽 제약이나 버전 검사로 한 번 더 막아 두는 것이 좋습니다.