PostgreSQL과 InnoDB는 행을 어디에 저장할까¶
같은 SQL과 B-Tree를 사용해도 기본 키와 보조 인덱스의 비용이 달라지는 이유를 물리 저장 위치에서 찾습니다.
애플리케이션에서는 PostgreSQL과 MySQL 모두 표에 행을 넣고 SELECT로 찾는다. 그러나 디스크에서 행을 놓는 위치와 인덱스가 행을 가리키는 방법은 다르다. 이 차이는 기본 키의 크기, 보조 인덱스 조회 횟수, 업데이트와 오래된 버전 정리 비용으로 이어진다.
먼저 페이지(Page)를 알아야 한다. 데이터베이스는 디스크에서 행 하나의 몇 바이트만 가져오기보다 고정 크기 블록인 페이지를 메모리로 옮긴다. 원하는 행과 함께 어떤 데이터가 같은 페이지에 놓이는지가 저장 장치 접근 횟수와 캐시 효율을 결정한다.
PostgreSQL은 행과 인덱스를 분리한다¶
PostgreSQL의 일반 표는 힙(Heap)에 행을 저장한다. 여기서 힙은 정렬 자료구조라는 뜻이 아니라, 기본 키 순서와 무관하게 사용 가능한 공간에 행을 두는 저장 영역이다. 기본 키도 다른 인덱스처럼 힙의 행 위치를 가리킨다.
PostgreSQL 인덱스의 리프 항목에는 보통 TID(Tuple Identifier, 튜플 위치 식별자)가 들어 있다. TID는 힙 페이지 번호와 그 페이지 안의 항목 위치를 합친 값이다. 이메일 인덱스로 사용자를 조회하면 먼저 인덱스에서 TID를 찾고, 그 TID로 힙 페이지를 방문해 실제 행과 현재 트랜잭션에 보이는 버전인지 확인한다.
이 분리 덕분에 기본 키가 행의 물리 배치를 강제하지 않는다. 반대로 인덱스에서 후보를 찾은 뒤 힙 페이지를 다시 방문할 수 있다. 여러 인덱스가 같은 행의 서로 다른 물리 위치를 가리키므로 업데이트가 새 행 버전을 만들 때 인덱스 유지와 가시성 처리가 중요해진다.
InnoDB는 기본 키 트리 안에 행을 둔다¶
InnoDB는 MySQL 안에서 저장과 트랜잭션을 맡는 기본 저장 엔진이다. InnoDB 표는 기본 키로 정렬된 클러스터형 인덱스(Clustered Index)이고, 기본 키 B-Tree의 리프 페이지에 행 전체가 들어 있다.
이메일 같은 다른 열에 만든 보조 인덱스에는 보조 키와 기본 키가 함께 들어간다. 이메일로 사용자를 찾으면 보조 인덱스에서 기본 키를 얻고, 필요한 열이 보조 인덱스에 모두 없다면 기본 키 트리를 다시 탐색해 행 전체를 가져온다.
그래서 InnoDB의 기본 키는 논리적인 식별자이면서 저장 위치의 기준이다. 기본 키가 길면 모든 보조 인덱스 항목도 길어진다. 무작위 값이 넓게 흩어지는 키는 여러 페이지에 삽입을 분산시키고 페이지 분할과 캐시 압박을 늘릴 수 있다.
flowchart TB
subgraph P["PostgreSQL: 행과 인덱스를 분리"]
PI["이메일 보조 인덱스"] -->|"힙 페이지와 항목 위치"| PH["힙 페이지"]
PH --> PV["현재 트랜잭션에 보이는 튜플 버전"]
end
subgraph I["InnoDB: 기본 키 트리에 행 저장"]
IS["이메일 보조 인덱스"] -->|"기본 키"| IP["기본 키 B-Tree"]
IP --> IR["리프 페이지의 전체 행"]
end한 논리적 행이 여러 버전으로 보이는 이유¶
조회와 업데이트가 동시에 일어날 때 매번 서로를 잠그면 안전하지만 처리량이 크게 떨어진다. PostgreSQL과 InnoDB는 모두 MVCC(Multi-Version Concurrency Control, 다중 버전 동시성 제어)를 사용해 필요한 동안 이전 행 버전을 보존한다. 같은 행을 조회해도 트랜잭션이 시작된 시점과 격리 수준에 따라 보이는 버전이 달라질 수 있다.
PostgreSQL은 업데이트할 때 기존 튜플을 제자리에서 덮어쓰기보다 힙에 새 튜플 버전을 만든다. 이전 버전은 즉시 지우지 않고, 어떤 트랜잭션도 필요로 하지 않게 된 뒤 VACUUM이 재사용 가능한 공간으로 정리한다. 오래 열린 트랜잭션이 있으면 과거 버전을 지울 수 없어 표와 인덱스가 불어날 수 있다.
InnoDB는 현재 레코드에서 이전 버전을 찾을 수 있도록 실행 취소 로그인 언두 로그(Undo Log)에 변경 전 정보를 연결한다. 조회 뷰가 현재 레코드를 볼 수 없다면 이 연결을 따라 과거 버전을 재구성한다. 더는 필요 없는 언두 기록과 삭제 표시된 레코드는 퍼지 작업이 정리한다.
두 제품 모두 MVCC를 사용하지만 과거 버전이 놓이는 곳과 정리 작업이 다르다. PostgreSQL에서는 튜플 버전과 VACUUM 상태를, InnoDB에서는 언두 이력과 퍼지 지연을 관찰해야 한다.
버퍼는 단순한 캐시보다 많은 일을 한다¶
PostgreSQL의 공유 버퍼와 InnoDB의 버퍼 풀은 디스크 페이지를 메모리에 보관한다. 필요한 페이지가 이미 메모리에 있으면 저장 장치 접근을 피할 수 있다. 하지만 적중률 하나만 높다고 빠른 것은 아니다. 질의 하나가 너무 많은 페이지를 요구하면 모두 메모리에 있어도 CPU와 메모리 대역폭을 많이 사용한다.
업데이트된 페이지는 메모리에서 먼저 바뀌고 나중에 디스크로 내려갈 수 있다. 이 페이지를 더티 페이지(Dirty Page)라고 한다. 성공한 커밋을 잃지 않기 위해 PostgreSQL은 WAL(Write-Ahead Log, 선행 기록 로그), InnoDB는 리두 로그를 데이터 페이지보다 먼저 안전하게 기록한다.
이 설계는 커밋할 때마다 큰 데이터 페이지를 원하는 디스크 위치에 곧바로 쓰는 대신 작은 로그를 먼저 순차적으로 남길 수 있게 한다. 장애 뒤에는 로그를 재생해 커밋된 변경을 복원한다. 따라서 데이터 파일 기록 시점과 커밋 시점은 같지 않을 수 있다.
조회 한 번과 업데이트 한 번의 비용을 따라가자¶
PostgreSQL의 보조 인덱스 조회는 실행 계획 → 보조 B-Tree → TID → 힙 페이지 → 튜플 가시성 확인으로 흐른다. 인덱스에 필요한 열이 모두 있고 힙 페이지의 가시성 상태가 충분히 확인된 경우에는 힙 방문을 줄이는 인덱스 전용 조회가 가능하다.
InnoDB의 보조 인덱스 조회는 실행 계획 → 보조 B-Tree → 기본 키 → 클러스터형 B-Tree → 조회 뷰 확인으로 흐른다. 보조 인덱스에 필요한 열이 모두 있으면 기본 키 트리 재탐색을 피할 수 있다. 두 제품 모두 커버링 인덱스가 유용하지만 피하게 되는 방문이 무엇인지는 다르다.
업데이트에서도 차이가 이어진다. PostgreSQL은 새 힙 튜플 버전을 만들며, 조건이 맞으면 같은 페이지 안에서 이전 버전과 연결해 인덱스 업데이트를 줄이는 HOT(Heap-Only Tuple) 최적화를 사용할 수 있다. InnoDB는 변경 전 정보를 언두에 남기고 클러스터형 레코드를 바꾸며, 인덱스 열이 바뀌면 관련 보조 인덱스도 업데이트한다.
설계할 때 달라지는 판단¶
InnoDB에서는 기본 키가 모든 보조 인덱스에 포함되므로 폭이 짧고 삽입 분포가 예측 가능한 키가 저장 비용에 유리하다. PostgreSQL에서는 기본 키가 행의 물리 순서를 정하지 않으므로 다양한 키를 선택하기 쉽지만, 업데이트 비율과 VACUUM이 오래된 버전을 따라잡는지 확인해야 한다.
두 제품 가운데 보편적으로 더 빠른 저장 구조는 없다. 대표 질의가 기본 키 범위 조회인지, 보조 인덱스 뒤에 원본 행을 얼마나 자주 방문하는지, 업데이트가 인덱스 열을 얼마나 자주 바꾸는지에 따라 비용이 달라진다.
운영에서는 질의당 페이지 수, 버퍼 적중과 저장 장치 지연, 더티 페이지 증가, PostgreSQL의 불필요 튜플과 VACUUM 진행, InnoDB의 언두 이력과 퍼지 진행을 함께 본다. “인덱스를 사용했다”는 사실만으로 실제 행까지 가는 전체 경로가 짧았다고 결론 내리면 안 된다.