PostgreSQL, MySQL 인덱스 구조 비교
PostgreSQL의 인덱스 구조
- `Heap Table`(실제 저장소) + 별도 인덱스 구조
: 클러스터 인덱스가 없음
: PK 인덱스, 보조 인덱스 모두 Heap으로 향하는 포인터 `TID` 를 가짐
- 인덱스 리프 노드에는 `TID (Heap Tuple Pointer)` 만 저장
: `TID` 크기는 6 바이트로 작고 고정되어 공간 효율이 좋음
- 실제 데이터를 읽으려면 반드시 `Heap Table 페이지`로 이동해야 함
B-Tree Index
├── Key = (writer_id, created_at)
└── Value = TID (Heap row pointer)인덱스만 보고 실제 데이터를 읽을 수 없고,
`TID` 가 가리키는 `Heap 위치` (`Heap Page #, Offset 형태`) 로 이동해야 실제 row를 가져올 수 있다.
> `Heap`은 물리적으로 정렬되어 있지 않기 때문에 `랜덤 I/O`가 발생
> Heap Table 이란? <
인덱스로 정렬되지 않은 저장 구조로
행이 삽입 순서대로 저장되며, 각 행의 위치는 인덱스가 별도로 기억한다. (PK로도 정렬되지 않음)
따라서 항상 `인덱스` → `Heap Fetch` 단계를 거친다.
InnoDB(MySQL)의 인덱스 구조
- `Clustered Table`(실제 저장소) + 별도 인덱스 구조
: PK 순서로 정렬되어 있음
- 인덱스 리프 노드에는 PK 값이 저장
: PK 크기와 비례하여 커지므로 공간 효율이 떨어짐
- PK 인덱스 트리 자체가 실제 데이터 저장소
Secondary Index (보조 인덱스)
├── key: (writer_id, created_at)
└── value: PK (memo_id)
Primary Key (클러스터 인덱스)
├── key: memo_id
└── value: 실제 row 전체 (writer_id, created_at, content, ...)보조 인덱스 리프 페이지에서 찾고,
PK 트리로 이동하여 PK 리프에서 실제 row 전체를 가져올 수 있다.
> 보조 인덱스 리프와 PK 리프는 물리적으로 떨어져 있어, 두 번의 접근 `랜덤 I/O`가 발생
Heap Table vs Clustered Table
| 구분 | Heap Table | Clustered Table |
| 저장 구조 | 무정렬 Heap | PK 순서로 정렬된 트리 |
| PK 역할 | Heap 포인터를 가진 보조 인덱스 | 실제 데이터 저장소 |
| 인덱스 리프 내용 | TID (페이지 + 슬롯) | PK + row 데이터 |
| row 접근 | 인덱스 > Heap Fetch | 인덱스 탐색으로 바로 접근 |
| Full Scan | Heap 전체 순차 접근 | PK 인덱스 순차 접근 |
| 정렬 상태 | 삽입 순서 | PK 기준 정렬 |
| 대표 DBMS | PostgreSQL, Oracle | MySQL(InnoDB) |
PostgreSQL, MySQL 구조가 다른 이유는?
PostgreSQL의 Heap Table
- 테이블은 무정렬 저장소, 인덱스는 데이터 접근의 창구
> 쓰기, 동시성, 일관성 중심의 설계를 택함
장점
: Insert 빠름 (정렬 불필요)
: 인덱스 재구성 자유롭고, 독립적
: 다양한 인덱스 패턴에 유연
: MVCC(다중 버전 동시성 제어) 구현이 단순
단점
: 랜덤 I/O 많음
: dead tuple로 인한 단편화 발생
: PK 정렬 불가
> MVCC 구현이 단순하다고? <
Heap Table 은 update 시 기존 row 를 덮지 않고, Heap 에 새로운 위치에 tuple 버전(v2)를 추가한다.
각 tuple 에는 `xmin (생성 트랜잭션)`, `xmax (삭제 트랜잭션)` 정보가 있어
트랜잭션에서 읽을 때, 스냅샷의 유효한 버전만 읽는다.
따라서 한 트랜잭션이 수정 중이어도 다른 트랜잭션은 수정 전 버전을 안전하게 읽을 수 있다.
InnoDB(MySQL)의 Clustered Table
- PK 순서로 데이터를 저장하면 Range Scan과 순차 접근이 빨라짐
> 읽기, 정렬, 트랜잭션 성능 중심의 설계를 택함
장점
: PK Range Scan 매우 빠름
: 읽기 효율적 (인덱스와 row가 인접)
: PK 기반 FK 조인 효율적
: 커버링 인덱스 자동화 (PK가 항상 row에 포함)
단점
: 중간 삽입 시 페이지 split 발생 (정렬이 깨지므로)
: PK 변경 시 재정렬 필요
: 모든 보조 인덱스가 PK를 포함하여 필요 공간이 증가
: 비 PK 칼럼 기준 접근 시 lookup 필요
> 덮어쓰기가 발생하면 일관성은 어떻게 보장하지? <
Clustered Table 은 row 가 PK 트리에 저장되어 있어 update 시 기존 row 를 덮어쓴다.
따라서 과거 버전은 Undo Log 에 별도로 기록되며,
rollback, consistency read 시 Undo Log 를 참조해야 해서 구조가 복잡하다.
PostgreSQL, MySQL 인덱스 생성 방식의 차이
| 항목 | PostgreSQL | MySQL |
| 방식 | Sequence 객체 기반 | 테이블 내부 카운터 기반 |
| 저장 위치 | 독립 시퀀스 (pg_class) | 테이블 메타데이터 내부 |
| 특징 | 시퀀스가 독립 객체라 병렬에 안전 | 테이블 단위 카운터로 관리 |
| 롤백 시 | 숫자 소비 (건너 뜀) | 값 복구 가능 |
| Insert 효율 | Heap 환경에서 경합 줄임 | Clustered 구조에서 순차 삽입 최적화 |
| 설계 목적 | Heap 기반의 동시성, 안정성 | PK 순차 저장을 통한 I/O 효율 극대화 |