PostgreSQL 튜닝 기술(상) - 3장 Index

📚 학습 범위: 『PostgreSQL 튜닝 기술 상』 3장 Index

1. 인덱스 듀플리케이션 (Index Duplication)

• 동일한 인덱스 키 값이 여러 개 존재할 경우, PostgreSQL은 해당 키 값을 인덱스에 한 번만 저장하고, 그 키에 해당하는 여러 TID(Tuple ID) 를 연결해서 관리한다.
• 이는 인덱스 크기를 줄이기 위한 설계이며, 키 값을 매번 저장하는 오라클 방식과 대비된다.
• 유니크 인덱스에서도 MVCC 때문에 일시적으로 같은 키 값을 가진 dead row + live row 가 존재할 수 있으며, 이 경우에도 키는 하나만 저장되고 TID만 여러 개가 유지된다.

  위 개념을 이해하기 위해서는 다 같이 PostgreSQL의 MVCC 구조를 배워보는 시간을 가져보면 좋을듯

2. Oracle과의 인덱스 구조 비교

3. MVCC와 인덱스 설계

• PostgreSQL은 MVCC (Multi-Version Concurrency Control) 을 사용하여, 하나의 행이 여러 버전(TID)을 가질 수 있음.
• VACUUM 이전까지는 같은 키 값을 가진 데드/라이브 로우가 공존하므로 인덱스도 여러 TID를 유지해야 함.
• 이 구조에 따라 PostgreSQL 인덱스는 중복 키 값을 하나로 통합해 저장하고, 다수의 TID를 매핑함.

4. table에 대해 vacuum을 수행하면 index의 dead TID도 같이 정리 될까?

• chatgpt 답변 :
  • VACUUM은 테이블의 데드 튜플 제거뿐 아니라 인덱스 내 데드 TID도 함께 제거한다.
  • VACUUM FULL은 테이블 및 인덱스를 완전히 재작성한다.

5. REINDEX의 필요성

• REINDEX는 인덱스만 별도로 재작성하는 명령어.
• VACUUM FULL이 전체 인덱스를 재생성하긴 하지만, 다음의 경우에는 REINDEX가 더 적합:
  • 인덱스 손상 시 (corruption)
  • 특정 인덱스에만 bloat가 집중된 경우
  • 전체 테이블에 대한 vacuum full 수행이 어려운 경우(테이블에 대한 exclusive lock, 모든 index에 대한 재생성 등과 같은 이유로 인해)

6. 인덱스의 EXACT vs LOSSY 모드?

• EXACT 모드: TID까지 정확히 포함 → 테이블 접근 없이 처리 가능
• LOSSY(LOGICAL) 모드: 페이지 단위 정보만 포함 → Heap Scan 단계에서 조건 재검증 필요 (Recheck)

7. Index Only Scan의 두 가지 조건

1. Index Coverage: 인덱스만으로 필요한 컬럼을 모두 제공할 수 있어야 함
2. Visibility Map (VM):
   • 각 테이블 페이지가 모든 튜플이 visible함을 기록한 비트맵
     • dead tuple 없이 모든 live 튜플만으로 구성되어 있을 때