Quartz 4

Postgresql internals

99 min read

1. PostgreSQL 구조 및 아키텍처 핵심

1. 데이터 조직 (Data Organization)

  • 인스턴스(Instance)와 클러스터(Cluster):
    • 실행 중인 PostgreSQL 프로그램을 인스턴스라고 하며, 인스턴스가 관리하는 데이터의 집합을 데이터베이스 클러스터라고 합니다.
    • 클러스터 초기화 시(initdb) 생성되는 3가지 기본 데이터베이스:
      • postgres: 사용자용 기본 DB.
      • template0: 수정 불가, 시스템 복구 및 불변 템플릿용.
      • template1: 사용자가 생성하는 새 DB의 기본 템플릿.
  • 시스템 카탈로그 (System Catalog): 테이블, 인덱스 등 클러스터 객체의 메타데이터를 저장하는 테이블 집합입니다. 기본 키로 oid(객체 식별자)를 사용합니다.
  • 스키마 (Schema): 데이터베이스 객체를 담는 논리적 네임스페이스입니다.
    • public (기본), pg_catalog (시스템 카탈로그), pg_temp (임시 테이블용) 등이 있습니다.
  • 테이블스페이스 (Tablespace): 데이터가 저장되는 물리적 디렉터리 위치를 정의합니다.
    • pg_default: 기본 데이터 저장소 (base/ 디렉터리).
    • pg_global: 클러스터 공용 객체 저장소 (global/ 디렉터리).

2. 파일과 포크 (Files and Forks)

  • 릴레이션(Relation): 테이블, 인덱스 등을 통칭하며, 디스크상에 여러 파일(Fork)로 나뉘어 저장됩니다.
  • 파일 세그먼트: 파일 크기가 1GB를 넘으면 자동으로 분할됩니다.
  • 주요 Fork 종류:
    • Main Fork: 실제 데이터 저장. 파일명은 relfilenode 번호 (예: 12345).
    • Initialization Fork (_init): UNLOGGED 테이블용. 복구 시 재설정에 사용.
    • Free Space Map (_fsm): 페이지 내 빈 공간을 추적하여 삽입 위치를 빠르게 찾음.
    • Visibility Map (_vm): 청소(Vacuum)나 동결(Freezing)이 필요한지, 인덱스 전용 스캔이 가능한지 판단하는 비트맵.

3. 페이지와 TOAST

  • 페이지 (Page): 모든 파일은 8KB 크기의 블록(페이지)으로 나뉩니다. I/O의 최소 단위입니다.
  • TOAST (The Oversized Attributes Storage Technique):
    • 페이지 크기(8KB)보다 큰 행을 저장하기 위한 기술입니다. 데이터를 압축하거나 별도의 TOAST 테이블로 잘라내어 저장합니다.
    • 저장 전략 (Strategies):
      • plain: 압축/분할 없음 (정수형 등).
      • extended: 압축 및 별도 저장 모두 허용 (기본값).
      • external: 압축하지 않고 별도 저장 (긴 문자열 등).
      • main: 압축을 우선 시도하고, 그래도 안 되면 별도 저장.

4. 프로세스와 메모리 (Processes and Memory)

  • 프로세스 모델: PostgreSQL은 스레드가 아닌 프로세스 기반 모델을 사용합니다.
  • 주요 프로세스:
    • postmaster: 서버 시작 시 실행되는 감독 프로세스. 다른 프로세스를 생성(fork)하고 관리함.
    • startup: 장애 복구 수행.
    • checkpointer: 체크포인트 실행 (더티 페이지를 디스크로 플러시).
    • bgwriter: 백그라운드에서 주기적으로 더티 페이지를 디스크에 기록.
    • autovacuum: 죽은 튜플 정리 및 통계 수집.
    • wal writer: WAL(Write-Ahead Log)을 디스크에 기록.
  • 공유 메모리 (Shared Memory): 모든 프로세스가 접근 가능한 메모리 영역으로, Buffer Cache(데이터 페이지 캐싱)와 WAL Buffer 등이 위치합니다.

5. 클라이언트 연결

  • 클라이언트가 접속할 때마다 postmaster는 새로운 Backend 프로세스를 생성합니다.
  • 연결이 많아지면 메모리 사용량과 프로세스 관리 비용이 증가하므로, PgBouncer 같은 Connection Pooler 사용이 권장됩니다.

📝 챕터 3: 페이지와 튜플 (Pages and Tuples)

1. 페이지 구조 (Page Structure)

PostgreSQL은 8KB 크기의 페이지(블록) 단위로 I/O를 수행합니다. 페이지 내부 구조는 양끝에서 안쪽으로 채워지는 형태입니다.

  • 페이지 헤더 (Header): 고정 크기(24 bytes). 체크섬, 플래그, 여유 공간 정보(lower, upper 포인터) 포함.
  • 아이템 포인터 (Item Pointers/Line Pointers): 실제 튜플을 가리키는 배열. 헤더 바로 뒤에 위치. (offset, length) 정보를 가짐.
  • 빈 공간 (Free Space): 아이템 포인터와 튜플 사이에 위치. 튜플이 추가되면 줄어듦.
  • 튜플 (Tuples/Rows): 실제 데이터. 페이지 끝에서부터 거꾸로 채워짐.
  • 특별 공간 (Special Space): 인덱스(B-tree 등)에서만 사용되며 테이블(Heap) 페이지에서는 비어 있음.

💡 핵심 암기: 간접 주소 지정(Indirect Addressing) 방식을 사용합니다. 튜플 ID(TID)는 (페이지번호, 포인터번호)로 구성됩니다. 튜플이 페이지 내에서 이동해도(예: 조각 모음), 포인터 번호는 유지되고 포인터의 offset 값만 바뀌므로 인덱스를 수정할 필요가 없습니다.

2. 튜플 구조 (Tuple Structure)

튜플은 **헤더(Header)**와 **데이터(Data)**로 구성됩니다. 헤더에는 MVCC를 위한 핵심 정보가 담깁니다.

  • xmin: 튜플을 **생성(Insert)**한 트랜잭션 ID.
  • xmax: 튜플을 **삭제(Delete)하거나 잠금(Lock)**을 건 트랜잭션 ID. (현재 살아있는 튜플은 0).
  • ctid: 튜플의 최신 버전을 가리키는 포인터. (Update 시 체인 연결에 사용).
  • infomask (힌트 비트): 트랜잭션 상태(Committed/Aborted)를 기록하여 매번 CLOG를 조회하는 비용을 줄임.
  • 데이터 정렬(Alignment): CPU 아키텍처에 맞춰 데이터 사이에 패딩(Padding)이 추가될 수 있음. 컬럼 순서를 잘 조정하면 저장 공간 절약 가능.

3. 튜플 조작과 MVCC 동작 (Operations)

모든 변경은 새로운 버전 생성 혹은 xmax 마킹으로 이루어집니다 (Overwrite 하지 않음).

  • INSERT: xmin = 현재 TXID, xmax = 0.
  • DELETE: 해당 튜플의 xmax를 현재 TXID로 설정.
  • UPDATE: DELETE + INSERT로 동작.
    1. 기존 튜플(Old Version): xmax를 현재 TXID로 설정 (삭제 표시).
    2. 새 튜플(New Version): xmin을 현재 TXID로 설정, xmax = 0.
    3. 기존 튜플의 ctid가 새 튜플을 가리키도록 설정.
  • COMMIT/ABORT: 데이터 페이지를 즉시 수정하지 않고 **CLOG(Commit Log)**에 트랜잭션 상태를 기록함. 이후 해당 페이지를 조회하는 첫 번째 프로세스가 CLOG를 확인하고 튜플 헤더에 **힌트 비트(Hint Bits)**를 설정함.

4. 기타 핵심 요소 (TOAST & Indexes & Virtual XID)

  • TOAST (The Oversized Attributes Storage Technique):
    • 페이지 크기(8KB)보다 큰 데이터를 저장하기 위한 기술.
    • 압축하거나, 잘라서 별도 테이블(TOAST table)에 저장함. 4가지 전략(plain, extended, external, main) 존재.
  • 인덱스 (Indexes):
    • 인덱스 튜플에는 xmin, xmax 같은 가시성 정보가 없음.
    • 인덱스는 힙(Heap) 테이블을 가리키는 TID만 제공하며, 가시성 확인은 힙 테이블에 접근해서 수행함.
  • 가상 트랜잭션 ID (Virtual XID):
    • 읽기 전용 트랜잭션은 실제 XID를 소모하지 않고 가상 ID만 받음.
    • 데이터를 수정하는 순간 실제 영구적인 XID를 할당받음 (XID 고갈 방지).

🎓 시험 대비 Q&A 요약:

  • Q: 튜플을 수정(Update)하면 물리적으로 어떻게 되는가?
    • A: 기존 튜플은 xmax가 마킹되어 “죽은” 상태가 되고, 새로운 튜플이 Insert 되며, ctid로 연결된다.
  • Q: 인덱스만 읽어서 쿼리 결과를 줄 수 있는가?
    • A: 기본적으로는 불가능하다(인덱스엔 가시성 정보가 없으므로). 단, Visibility Map이 있다면 가능하다(Index-Only Scan).
  • Q: 힌트 비트(Hint Bits)의 역할은?
    • A: 트랜잭션 상태(Commit/Abort)를 튜플 헤더에 캐싱하여, 비싼 CLOG 조회를 줄인다. SELECT만 해도 디스크 쓰기(힌트 비트 설정)가 발생할 수 있는 이유이다.

📝 챕터 4: 스냅샷 (Snapshots) 암기 노트

1. 스냅샷의 정의와 격리 수준 (Isolation Levels)

  • 개념: 특정 시점에 커밋된 데이터만 볼 수 있는 일관된 뷰(Consistent View)를 제공하는 논리적 구조입니다.
  • 생성 시점 (격리 수준에 따라 다름):
    • Read Committed: 각 **SQL 문장(Statement)**이 시작될 때마다 새로운 스냅샷을 생성합니다.
    • Repeatable Read / Serializable: 트랜잭션의 첫 번째 문장이 시작될 때 스냅샷을 생성하고 트랜잭션이 끝날 때까지 유지합니다.

2. 스냅샷의 내부 구조 (Snapshot Structure)

스냅샷은 데이터를 복사하는 것이 아니라, **“누가 활동 중인지”**를 기록한 숫자들의 집합입니다. 형식: xmin : xmax : xip_list,

  • xmin (Lower Boundary): 스냅샷 생성 시점에 활동 중인(Active) 가장 오래된 트랜잭션 ID.
    • xid < xmin: 이미 완료된 트랜잭션 (무조건 보임).
  • xmax (Upper Boundary): 스냅샷 생성 시점에 할당되지 않은 다음 트랜잭션 ID.
    • xid >= xmax: 미래에 시작될 트랜잭션 (무조건 안 보임).
  • xip_list (Active XID List): xmin과 xmax 사이에서 아직 활동 중인 트랜잭션 ID들의 리스트.
    • 리스트에 포함됨: 아직 안 끝남 (안 보임).
    • 리스트에 없음: 이미 완료됨 (보임).

3. 가시성 규칙 (Visibility Rules)

튜플 헤더의 xmin(생성자)과 xmax(삭제자)를 스냅샷과 비교하여 판단합니다.

  1. xmin 확인 (생성자가 보이는가?):
    • xmin < snapshot.xmin: 보임 (과거 완료).
    • xmin >= snapshot.xmax: 안 보임 (미래).
    • snapshot.xmin <= xmin < snapshot.xmax: xip_list에 있으면 안 보임(진행 중), 없으면 보임(완료).
  2. xmax 확인 (삭제자가 보이는가?):
    • 삭제자가 안 보이면(아직 활동 중이거나 미래): 튜플은 보임 (삭제 안 된 것으로 간주).
    • 삭제자가 보이면(완료된 삭제): 튜플은 안 보임 (삭제됨).
  3. 자신의 변경사항: 트랜잭션은 자신이 변경한 내용은 아직 커밋되지 않았어도 볼 수 있습니다. 단, 커서(Cursor)의 경우 커서를 연 시점 이후의 변경은 보지 않기 위해 cmin/cmax (명령어 ID)를 사용합니다.,

4. 트랜잭션 호라이즌 (Transaction Horizon)

  • 개념: 데이터베이스 전체에서 가장 오래된 스냅샷의 xmin. 이 시점 이전의 데이터는 모든 트랜잭션에게 “과거”이므로 가시성이 동일합니다.
  • Vacuum과의 관계: Vacuum은 호라이즌보다 오래된(Dead) 튜플만 청소할 수 있습니다.
  • 문제점: **긴 트랜잭션(Long Transaction)**이나 Repeatable Read 트랜잭션이 종료되지 않고 버티면 호라이즌이 멈춰 서게 되어, Vacuum이 죽은 튜플을 정리하지 못하고 테이블이 비대해집니다(Bloat).,

5. 기타 핵심 기능

  • 스냅샷 내보내기 (Export): pg_export_snapshot()으로 현재 스냅샷 상태를 ID로 내보내고, 다른 세션에서 SET TRANSACTION SNAPSHOT 'id'로 가져올 수 있습니다. pg_dump가 병렬 백업을 수행할 때 모든 워커가 동일한 시점을 보기 위해 사용합니다. (RR/Serializable만 가능),
  • 시스템 카탈로그: 테이블 구조 변경(DDL) 등을 즉시 반영하기 위해, 시스템 카탈로그 조회는 항상 최신 스냅샷을 사용합니다(트랜잭션 격리 수준 무시).

💡 시험 대비 핵심 요약 (Q&A):

  • Q: 스냅샷 100:105:100,102,104의 의미는?
    • A: 100번 미만은 모두 완료됨. 105번 이상은 아직 시작 안 함. 100~104 중 100, 102, 104는 진행 중(안 보임), 101, 103은 완료됨(보임).
  • Q: 왜 긴 트랜잭션이 Vacuum을 방해하는가?
    • A: 트랜잭션이 스냅샷을 잡고 있으면 그 스냅샷의 xmin(호라이즌)이 갱신되지 않음. 그보다 최신 데이터는 “누군가에게는 아직 필요할 수 있다”고 판단되어 지울 수 없음.
  • Q: 과거 특정 시점(Time Travel)의 데이터를 스냅샷으로 볼 수 있는가?
    • A: 불가능함. PostgreSQL은 트랜잭션의 시작 시점만 알고 종료 시점은 기록하지 않으므로, 과거 특정 시점에 누가 활동 중이었는지(xip_list) 재구성할 수 없음.,

📝 챕터 5: 페이지 가지치기 및 HOT 업데이트 (Page Pruning & HOT Updates)

1. 페이지 가지치기 (Page Pruning)

  • 개념: SELECT나 UPDATE가 힙 페이지를 읽을 때, 해당 페이지 내의 죽은 튜플(Dead Tuples)을 청소하는 경량 작업. (Vacuum과 다름).
  • 발동 조건:
    1. 이전 INSERT가 공간 부족으로 실패했을 때.
    2. 페이지 채움 비율이 fillfactor(기본 100%)를 초과했을 때.
  • 동작:
    • **데이터베이스 호라이즌(Database Horizon)**보다 오래된(모든 트랜잭션에 보이지 않는) 튜플을 제거.
    • 인덱스 처리: 인덱스는 건드리지 않음. 따라서 힙 튜플은 지워져도 아이템 포인터(Line Pointer)는 남겨두고 상태를 **dead**로 변경함 (인덱스가 여전히 가리키고 있기 때문).
    • 메타데이터: **Free Space Map(FSM)**이나 **Visibility Map(VM)**을 업데이트하지 않음 (공간은 UPDATE용으로만 재사용됨).

2. HOT 업데이트 (Heap-Only Tuple Updates)

  • 목적: 인덱스가 없는 컬럼만 수정할 때, 인덱스 업데이트 오버헤드와 공간 낭비를 줄이기 위함.
  • 조건 (필수):
    1. 업데이트되는 컬럼이 어떤 인덱스에도 포함되지 않아야 함.
    2. 새로운 튜플 버전이 **동일한 페이지(Same Page)**에 저장되어야 함.
  • 구조:
    • 인덱스는 체인의 첫 번째 튜플(Root)만 가리킴.
    • 튜플 헤더의 ctid를 통해 다음 버전으로 연결되는 체인을 형성.
    • 플래그:
      • 부모 튜플: Heap Hot Updated (체인을 따라가라).
      • 자식 튜플: Heap Only Tuple (인덱스가 나를 직접 가리키지 않는다).

3. HOT 체인 가지치기 (Pruning for HOT)

  • Redirect (재지정): HOT 체인 중간에 죽은 튜플들이 정리되면, 인덱스가 가리키던 루트 포인터의 상태를 **redirect**로 변경하여 살아있는 최신 튜플을 직접 가리키게 함.
  • Unused (재사용): 중간 단계의 아이템 포인터들은 인덱스 참조가 없으므로 즉시 unused 상태가 되어, 새 튜플이 해당 ID를 즉시 재사용할 수 있음.

4. HOT 체인의 분절 (Chain Splits)

  • 상황: 업데이트 시 현재 페이지에 공간이 부족하여 새 튜플이 다른 페이지로 이동해야 할 때.
  • 결과: HOT 업데이트가 불가능함.
    • 새 페이지의 튜플을 가리키는 새로운 인덱스 엔트리가 생성됨.
    • 이전 튜플은 Heap Hot Updated 플래그가 설정되지 않음 (체인이 끊김).

5. 인덱스 가지치기 (Index Pruning)

  • 개념: B-tree 인덱스 페이지가 꽉 차서 분할(Split)되기 직전에 수행되는 정리 작업.
  • 제거 대상:
    1. 인덱스 스캔 중 발견되어 **dead**로 표시된 아이템.
    2. HOT 체인 최적화 등을 통해 힙 튜플이 정리되어 더 이상 필요 없어진 인덱스 엔트리 (힙 테이블을 확인하여 가시성 체크 후 제거).

챕터 6: Vacuum & Autovacuum 암기 노트

1. Vacuum의 목적과 특징

  • 목적: MVCC로 인해 생성된 **죽은 튜플(Dead Tuples)**이 차지하는 공간을 회수하여 재사용 가능하게 만듦 (OS로 반환하는 것이 아님).
  • 동시성: VACUUM(옵션 없는 기본)은 테이블에 Lock을 걸지 않음 (SELECT/UPDATE/INSERT 가능). 단, DDL 등과는 충돌.
  • 보조 역할:
    • Visibility Map (VM) 업데이트: 페이지가 모두 최신(all-visible)인지 표시하여 Index-Only Scan 지원.
    • FSM (Free Space Map) 업데이트: 확보된 공간을 기록하여 INSERT 시 재사용 유도.
  • 제약 사항: 데이터베이스 호라이즌(Database Horizon), 즉 가장 오래된 활성 트랜잭션보다 이전의 튜플만 지울 수 있음. 긴 트랜잭션은 Vacuum을 방해함.

2. Vacuum의 4단계 실행 과정 (Phases)

  1. Heap Scan (힙 스캔): 테이블을 스캔하며 죽은 튜플을 식별.
    • 죽은 튜플의 위치(TID)를 maintenance_work_mem 크기의 메모리 배열에 수집.
    • VM에 이미 처리됨으로 표시된 페이지는 스킵.
  2. Index Vacuuming (인덱스 청소): 수집된 TID를 참조하여 모든 인덱스에서 해당 엔트리를 제거.
    • 메모리가 부족하면 1~2 단계를 여러 번 반복함.
    • 병렬 처리 가능 (min_parallel_index_scan_size 이상일 때).
  3. Heap Vacuuming (힙 청소): 다시 힙으로 돌아와 실제 튜플을 제거(Reclaim)하고 FSM/VM 업데이트.
  4. Heap Truncation (힙 자르기): 파일 끝부분에 빈 페이지들이 연속되면, 테이블을 잠그고(Exclusive Lock) 파일 크기를 줄여 OS로 공간 반환.

3. Autovacuum (자동 진공)

  • 구조: Launcher 프로세스가 Worker 프로세스를 생성하여 실행.
  • 트리거 공식 (언제 실행되는가?):
    • Vacuum: 죽은 튜플 수 > threshold + (scale_factor 전체 행 수)
      • 기본값: threshold=50, scale_factor=0.2 (20%).
    • Analyze: 변경된 행 수 > threshold + (scale_factor 전체 행 수)
      • 기본값: threshold=50, scale_factor=0.1 (10%).
    • Insert-only: (PG 13+) 변경 없이 INSERT만 발생해도 Freezing을 위해 실행됨 (vacuum_insert_threshold).
  • 메모리: autovacuum_work_mem을 사용 (설정 안 하면 maintenance_work_mem 사용).

4. 부하 제한 (Throttling)

Vacuum이 시스템 I/O를 독점하지 않도록 비용(Cost) 기반으로 일시 중지함.

  • 비용 계산:
    • page_hit (버퍼 캐시 히트): 비용 1
    • page_miss (디스크 읽기): 비용 10 (기본값 2)
    • page_dirty (페이지 수정): 비용 20
  • 동작: 누적 비용이 vacuum_cost_limit(기본 200)에 도달하면 vacuum_cost_delay(기본 2ms)만큼 잠듦(Sleep).

5. 모니터링 및 튜닝 포인트

  • pg_stat_progress_vacuum: 현재 실행 중인 Vacuum의 단계(Phase)와 진행률 확인.
  • pg_stat_all_tables: n_dead_tup(죽은 튜플 수), last_autovacuum 확인.
  • 튜닝 팁: 대용량 테이블은 autovacuum_vacuum_scale_factor를 낮춰(예: 0.05) 더 자주 청소하게 하거나, autovacuum_work_mem을 늘려 인덱스 스캔 반복을 줄여야 함.

📝 챕터 7: 동결 (Freezing)

1. 문제점: 트랜잭션 ID 회귀 (Transaction ID Wraparound)

  • 제약: PostgreSQL의 트랜잭션 ID(XID)는 32비트 정수(약 40억 개). 시스템이 오래 운영되면 ID가 고갈되어 다시 0부터 시작함.
  • 위험: XID 비교는 원형(Circular) 논리를 사용. 현재 XID보다 “미래”에 있는 ID는 보이지 않음.
    • 회귀가 발생하면, 과거의 아주 오래된 데이터가 갑자기 “미래의 데이터”로 인식되어 보이지 않게 되는(Invisible) 데이터 손실 발생.
  • 해결책: 오래된 튜플을 **동결(Freeze)**하여, XID 비교 로직을 건너뛰고 “무조건 과거(항상 보임)“로 취급하게 만듦.

2. 동결의 구현 (Implementation)

  • 과거 방식: xmin을 FrozenTransactionId (값 2)로 덮어썼음 (비효율적, 추적 불가).
  • 현재 방식: xmin 값을 유지하되, **튜플 헤더의 힌트 비트(infomask)**를 조작하여 동결됨을 표시.
    • Frozen XID: xmin이 동결된 것으로 표시됨.
    • XID가 보존되므로 디버깅이나 포렌식에 유리함.

3. 동결 프로세스와 가시성 맵 (Visibility Map, VM)

  • 일반 Vacuum: 죽은 튜플(Dead Tuple)만 정리.

  • 동결 Vacuum: 살아있는 튜플 중 오래된 것을 동결 처리.

  • VM의 역할: VM은 두 가지 비트를 가짐.

    1. all_visible: 모든 트랜잭션에 보임 (Index-Only Scan 용).

    2. all_frozen (PG 9.6+): 페이지 내 모든 튜플이 이미 동결됨.

    • 최적화: all_frozen 비트가 켜진 페이지는 동결 작업을 위해 스캔할 필요가 없음.

4. 동결 제어 파라미터 (계층 구조 암기 필수)

PostgreSQL은 XID 나이(Age)에 따라 동결의 강도를 단계적으로 높입니다.

(1) vacuum_freeze_min_age (기본: 5천만)

  • 조건: 튜플의 나이가 이 값보다 적으면 동결하지 않고 그냥 둠.
  • 목적: 방금 생성된 “Hot”한 데이터가 곧 업데이트될 수도 있는데 미리 얼리는 낭비를 방지 (Early Freezing 방지).

(2) vacuum_freeze_table_age (기본: 1억 5천만)

  • 동작: 테이블의 가장 오래된 XID(relfrozenxid) 나이가 이 값을 넘으면 Aggressive Vacuum 실행.
  • 특징: VM의 all_visible이 켜진 페이지도 무시하고(Skip 불가), all_frozen이 아닌 모든 페이지를 스캔하여 동결 수행.

(3) autovacuum_freeze_max_age (기본: 2억)

  • 동작: 이 한계에 도달하면 Autovacuum이 꺼져 있어도 강제로 실행됨.
  • 목적: XID 회귀(Wraparound) 방지를 위한 안전장치. 이 시점에 불필요한 오래된 CLOG(Commit Log) 파일도 삭제됨.

(4) vacuum_failsafe_age (기본: 16억, PG 14+)

  • 동작: 회귀 임계치에 위험할 정도로 근접하면 발동.
  • 특징: 성능 제약(cost_delay)을 무시하고, 인덱스 청소를 생략한 채 힙(Heap) 동결에만 집중하여 속도를 최우선으로 함.

5. 수동 동결 및 최적화

  • VACUUM FREEZE: vacuum_freeze_min_age를 0으로 간주하고 즉시 모든 튜플을 동결.
  • COPY ... WITH FREEZE: 데이터를 입력(Load)할 때 즉시 동결 상태로 저장.
    • 조건: 동일 트랜잭션 내에서 테이블을 생성했거나 TRUNCATE 한 경우에만 가능 (배타적 잠금 필요).
    • 장점: 이후에 별도의 동결 Vacuum이 필요 없어 대량 데이터 적재 시 성능 이득.

📝 챕터 8: 테이블과 인덱스 재구성 (Rebuilding)

1. 재구성이 필요한 이유 (Why Rebuild?)

  • Routine Vacuum의 한계: 일반 Vacuum은 페이지 내부의 빈 공간(Free Space)만 확보할 뿐, 파일 자체의 크기를 줄이는 것은 **파일 끝(Tail)**에 빈 페이지가 몰려 있을 때만 가능합니다.
  • 비대화(Bloat)의 문제점:
    • 전체 테이블 스캔 속도 저하.
    • 버퍼 캐시 효율 저하 (데이터 밀도 감소).
    • B-tree 인덱스 레벨 증가 (검색 속도 저하).
  • 해결책: 테이블을 완전히 새로 써서 밀도를 높이고 파일 크기를 줄여야 함.

2. 완전한 재구성: VACUUM FULL

  • 동작 방식: 테이블과 모든 인덱스를 처음부터 다시 작성(Rewrite)합니다. 데이터를 fillfactor에 맞춰 최대한 밀집시킵니다.
  • 특징:
    • 새 파일 생성: 작업 중에는 기존 파일과 새 파일이 공존하므로 여분의 디스크 공간이 필요합니다.
    • 배타적 잠금(Exclusive Lock): 작업 중 테이블에 대한 읽기/쓰기가 모두 차단됩니다. 운영 중 사용 시 주의가 필요합니다.

3. 기타 재구성 명령 (Alternatives)

  • CLUSTER: VACUUM FULL과 유사하게 테이블을 재구성하지만, 지정한 인덱스 순서대로 데이터를 정렬합니다.
    • 장점: 범위 검색(Range Scan) 효율이 좋아짐.
    • 단점: 이후 업데이트되는 데이터는 정렬 순서를 따르지 않으므로 주기적으로 다시 실행해야 함.
  • REINDEX: 테이블은 그대로 두고 인덱스만 새로 생성합니다.
  • TRUNCATE: 모든 행을 삭제하고 새로운 빈 파일을 만듭니다. DELETE보다 빠르며 물리적으로 파일을 교체하는 방식입니다.

4. 무중단 재구성 (Minimal Downtime)

VACUUM FULL의 배타적 잠금을 피하기 위해 외부 도구를 사용합니다.

  • pg_repack: 트리거(Trigger)를 사용하여 변경 사항을 추적하고, 섀도우 테이블(Shadow Table)을 만들어 데이터를 복사한 뒤 교체합니다. 잠금은 시작과 끝에만 짧게 발생합니다.
  • pgcompacttable: 가짜 업데이트(Fake update)를 수행하여 행을 이동시키고 끝부분을 잘라내는 방식을 사용합니다.

5. 예방 및 주의사항 (Precautions)

  • 장기 트랜잭션 (Long Transactions): 데이터베이스 호라이즌(Horizon)을 붙잡고 있어 Vacuum을 방해하여 비대화를 유발합니다.
    • 해결: 읽기 전용 쿼리는 복제본(Replica)으로 분산하거나, idle_in_transaction_session_timeout, old_snapshot_threshold 설정으로 제한합니다.
  • 대량 업데이트 (Mass Updates): 테이블의 모든 행을 한 번에 업데이트하면 테이블 크기가 순간적으로 2배가 됩니다 (구 버전 + 신 버전 공존).
    • 해결 (Batch Update): 한 번에 모든 행을 수정하지 말고, 배치(Batch) 단위로 나누어 처리합니다. Vacuum이 중간중간 공간을 회수할 수 있게 합니다.
    • 핵심 구문: SELECT ... LIMIT ... FOR UPDATE SKIP LOCKED를 사용하여 이미 잠긴 행은 건너뛰고 처리합니다.

9장 버퍼 캐시 (Buffer Cache)

1. 개념 및 아키텍처

  • 목적: 디스크 I/O 성능 차이 (ms vs ns)를 극복하기 위해 관계 페이지(Relation Pages)를 공유 메모리에 캐싱
  • 이중 캐싱(Double Caching): PostgreSQL은 Direct I/O 대신 운영 체제의 버퍼를 거치는 방식을 사용하여, 데이터가 OS 캐시와 PostgreSQL 버퍼 캐시에 중복 존재할 수 있다.
  • 구조: 공유 메모리 내에 위치하며 모든 프로세스가 접근 가능함
    • 구성: 버퍼 배열로 이루어지며, 각 버퍼는 8KB 페이지와 버퍼 헤더를 포함함
    • 버퍼 헤더 정보: 물리적 위치(파일 ID, 포크, 블록 번호), 더티(Dirty 비트), 사용 횟수(Usage Count), 고정 횟수(Pin Count)

2. 캐시 히트(Hit)와 해시 테이블

  • 해시 테이블: 특정 페이지가 캐시에 있는지 빠르게 찾기 위해 사용함
    • 해시 키: (파일 ID, 포크 번호, 블록 번호)의 조합
  • 버퍼 고정 (Pinning): 페이지를 사용 중일 때 핀(Pin)을 꽂아 다른 프로세스가 해당 버퍼를 교체(Evicton) 하지 못하도록 보호함

3. 캐시 미스 (Miss)와 교체 알고리즘(Eviction)

  • 프리 리스트 (Free List): 서버 시작 직후의 빈 버퍼들을 관리하는 리스트
  • 시계 정리 알고리즘 (Clock Sweep): 빈 버퍼가 없을 때 기존 페이지를 내보내는 방식
    1. 시계 바늘 (Clock hand): 버퍼를 순회하며 사용 횟수(Usage Count)를 1씩 감소시킴
    2. 선택 기준: 사용 횟수가 0이고 핀이 꽃혀 있지 않은 첫 번째 버퍼를 교체 대상으로 선정함
    3. 더티 페이지 처리: 교체 대상이 수정된(Dirty) 페이지라면 디스크에 먼저 기록한 후 교체함

4. 대략 작업 최적화(Buffer Rings)

  • 목적: 대규모 순차 스캔이나 대량 쓰기 작업이 유용한 기존 캐시 데이터를 밀어내는 것을 방지함
  • 동작: 전체 캐시 대신 작은 크기의 버퍼 링 내에서만 교체를 수행함
    • Bulk Reads: 대량 순차 스캔 시 사용 (256KB)
    • Bulk Writes: COPY 등 대량 쓰기 시 사용 (16MB)
    • Vaccuming: Vaccum 작업 시 사용 (256KB)

5. 설정 및 관리

  • shared_buffers: 전체 버퍼 캐시 크기 설정 (보통 RAM의 1/4 권장)
  • pg_prewarm: 서버 재시작 후 캐시를 미리 채우기 위한 확장 모듈
  • 로컬 캐시(Local Cache): 임시 테이블 전용 캐시로, 개별 프로세스의 메모리에 위치하며 temp_buffers로 제어함

10장 Write-Ahead Log (WAL)

1. WAL의 목적과 원칙

  • 목적: 데이터 무결성(Durability) 보장 및 성능 향상 (랜덤 쓰기 대신 순차 쓰기 활용)
  • WAL 프로토콜 (Rule): 데이터 페이지를 수정하기 전, 해당 변경 사항을 담은 로그(WAL record)를 먼저 디스크에 기록해야 함
  • 로깅 대상: 버퍼 캐시 내 페이지 수정, 트랜잭션 커밋/롤백, 파일 작업
  • 로깅 제외: UNLOGGED 테이블, 임시 테이블 (Temporary tables)

2.구조 (Logical & Physical)

  • LSN (Log Sequence Number): 64-bit 정수, WAL 스트림 내의 바이트 오프셋 위치를 나타내는 고유 주소
  • WAL 레코드 (WAL record): 헤더 (XID, Resource Manager Id, 체크섬, 길이 등)와 데이터로 구성
  • 물리적 파일: pg_wal 디렉터리에 저장, 기본 16MB 크기의 세그먼트 파일.
    • 파일명: 타임라인 ID(8자리) + LSN 상위/하위 비트 조합
  • WAL 버퍼: 공유 메모리(wal_buffers) 내의 링 버퍼 (Ring Buffer) 구조 사용.

3 체크포인트 (Checkpoint)

  • 정의: 메모리의 모든 더티 페이지(Dirty Pages)를 디스크로 플러시(Flush)하고 동기화(Sync)하는 시점. 복구(Recovery)의 시작점을 이동시킴
  • 프로세스: checkpointer가 수행
    1. Start: WAL에 체크포인트 시작 기록
    2. Flush: 더티 페이지들을 디스크에 기록(I/O 부하 분산을 위해 checkpoint_completion_target 기간 동안 천천히 수행)
    3. Sync: 파일 시스템 동기화(fsync)
    4. Completion: pg_control 파일에 최신 체크포인트 위치(REDO point) 갱신
  • 트리거 조건: checkpoint_timeout(시간) 초과 또는 max_wal_size(용량) 초과 시 발생.

4. 복구 (Recovery)

  • 발동 조건: 서버 시작 시 pg_control 파일의 상태가 in production인 경우 (비정상 종료)
  • 과정: 마지막 체크포인트의 REDO 지점부터 WAL을 순차적으로 다시 실행(Replay)
  • 데이터 손상 방지 (Non-Atomic Writes): 페이지 쓰기가 블록 단위가 아니어서 발생할 수 있는 찢어진 페이지(Torn Page) 문제를 해결하기 위해, 체크포인트 후 첫 수정 시 전체 페이지 이미지 (Full Page Image, FPI)를 WAL에 기록함. 복구 시 FPI로 덮어쓰고 이후 로그를 적용.

5. 백그라운드 라이터(Bg Writer)

  • 목적: 일반 백엔드 프로세스가 버퍼 할당을 위해 직접 디스크 쓰기(Victim buffer write)를 하지 않도록 미리 더티 페이지를 디스크에 기록하여 깨끗한 버퍼를 확보함
  • 체크포인터와 차이점: BgWriter는 페이지를 쓰기만 하고 동기화(fsync)는 하지 않음. 체크포인터는 전체를 동기화하고 복구 시점을 갱신함

6. 설정 및 모니터링(튜닝 포인트)

  • min_wal_size / max_wal_size: WAL 파일 보관 용량. max_wal_size는 소프트 리미트로, 초과 시 체크포인트가 강제 실행 됌
  • checkpoint_completion_target (default 0.9): 체크포인트 I/O를 전체 주기의 90% 동안 분산시켜 I/O 스파이크 방지
  • full_page_writes: 기본 on. 끄면 성능은 오르지만 크래시 시 데이터 복구 불가 위험
  • pg_stat_bgwriter 뷰:
    • buffers_backend: 백엔드가 직접 쓴 횟수 (이 값이 높은 경우 튜닝 필요)
    • buffers_clean: BgWriter가 쓴 횟수.
    • buffers_checkpoint: 체크포인터가 쓴 횟수

11. WAL Modes (성능과 신뢰성)

1. 성능 vs 내구성: 커밋 모드 (Comit Modesxs)

  • 트랜잭션의 커밋 속도와 데이터 내구성 간의 트레이드 오프를 제어합니다.
  • 동기 커밋 (Synchronous Commit - 기본 값)
    • 설정: synchronous_commit = on
    • 동작: 트랜잭션 커밋 시 WAL 레코드가 디스크에 플러시(fsync) 될 때까지 클라이언트가 대기합니다.
    • 특징: 시스템 크래시가 발생해도 커밋된 데이터는 100% 보존된다.
  • 비동기 커밋 (Asynchronous Commit):
    • 설정: synchronous_commit = off.
    • 동작: 커밋 즉시 성공을 반환, 실제 디스크 쓰기는 백그라운드에서 지연 처리
    • WalWriter 프로세스: walwriter가 주기적으로(wal_writer_delay, default 200ms) 깨어나거나 데이터가 쌓이면 (wal_writer_flush_after) WAL을 디스크로 쓴다.
    • 리스크: 크래시 시 최근 최대 3 x wal_writer_delay 만큼의 데이터가 유실될 수 있다.
    • 장점: 짧은 쓰기 트랜잭션이 많은 환경에서 대기 시간이 획기적으로 줄어든다.

2. 데이터 무결성: 찢어진 페이지, FPW

  • 하드웨어/OS의 쓰기 단위와 DB 페이지 크기 차이로 인한 데이터 손상 방지
  • 문제점 (Non-Atomic Writes / Torn Pages):
    • PostgreSQL 페이지(8KB) > OS/ 디스크의 원자적 쓰기 단위(4KB or 512B)
    • 페이지를 쓰는 도중 전원이 나가면, 페이지의 일부만 기록되는 찢어진 페이지(Torn Page)가 발생, 이는 일반 WAL 레코드(델타 값)로 복구할 수 없다.
  • 해결책: 전체 페이지 이미지 (Full Page Writes, FPW)
    • 동작: 체크포인트 이후 해당 페이지가 처음 수정될 때, 변경 사항뿐만 아니라 페이지 전체 이미지를 WAL에 기록
    • 복구 매커니즘: 복구 시 FPI로 페이지를 통째로 덮어씌운 후, 이후의 WAL 레코드를 적용
    • 비용: WAL 사용량이 급증, 체크포인트 주기를 짧게 가져가면 FPW가 자주 발생하여 쓰기 부하가 발생한다.
    • 최적화: wal_compression = on 설정을 통해 FPW 데이터를 압축하여 WAL 크기를 줄일 수 있다.

3. WAL 레벨 (WAL Levels)

  • WAL에 기록되는 정보의 양을 제어하며, 기능 지원 범위를 결정한다. (wal_level parameter)
    1. Minimal (최소):
    • 목적: 크래시 복구(Crash Recovery)만 보장.
    • 특징: 대량 작업 (COPY, CREATE TABLE AS, CREATE INDEX 등)시, 해당 트랜잭션 내에서 생성/삭제된 테이블에 대한 WAL 기록을 생략하고 디스크 동기화만 수행하여 속도를 높인다.
    • 제약: [아카이빙(PITR)][https://www.postgresql.org/docs/current/continuous-archiving.html] 이나 복제(Replication) 사용 불가
    1. Replica (기본 값):
    • 목적: 크래시 복구 + 아카이빙(PITR) + 물리적 복제(Streaming Replication)
    • 특징: 모든 변경 사항에 WAL에 기록한다. pg_basebackup을 사용하려면 필수이다.
    1. Logical (논리적)
    • 목적: Replica 기능 + 논리적 복제(Logical Replication)
    • 특징: 논리적 디코딩 플러그인이 데이터를 추출할 수 있도록 추가 정보를 기록한다.

12. 관계형 락 (Relation-Level Locks)

1. 락의 기본 개념과 특징

  • 목적: 공유 리소스(테이블, 인덱스 등)에 대한 동시 접근을 제어하여 데이터 일관성을 보장한다.
  • Heavyweight Locks (무거운 락 or 중량적 잠금)
    • 저장 위치: 서버의 공유 메모리에 저장된다. (디스크, 페이지 내부는 아님)
    • 제한: 시스템 전체 락 개수는 max_locks_per_transaction * max_connections로 제한된다. 해당 풀이 가득 차면 더 이상 락을 걸 수 없다.
    • 대기: 락을 획득하지 못하면 프로세스는 Sleep 상태로 대기 큐에 들어간다 ( CPU 소모 X )

2. 트랜잭션 ID 락 (Locks On Transcation IDs)

  • 원칙: 모든 트랜잭션은 자신의 트랜잭션 ID(XID)에 대해 배타적 락(Exclusive Lock)을 보유한다.
  • 용도: 다른 트랜잭션이 트랜잭션의 완료(Commit/Abort)를 기다려야 할 때, 해당 XID에 락을 요청하고 대기한다.
  • 예시: UPDATE 중인 행을 다른 트랜잭션이 수정하려고 하면, 후행 트랜잭션은 선행 트랜잭션의 XID 락이 해재될 때까지 대기한다.

3. 관계형 락 모드 8 단계 (Relation-Level Lock Modes)

  • 가장 약한 것 부터 강한 순서대로 암기 필수, 충돌 규칙 이해할 것
락 모드주요 명령어특징 및 충돌
1. Access ShareSELECT가장 약한 락. Access Exclusive만 충돌 (테이블 읽기 허용)
2. Row ShareSELECT FOR UPDATE/SHARE행을 선택했으나 아직 수정 전 상태
3. Row ExclusiveINSERT, UPDATE, DELETE데이터 변경 시 사용. 일반적인 DML
4. Share Update ExclusiveVACUUM(기본), CREATE INDEX CONCURRENTLY스키마 변경 없음. 동시 작업 가능 VACCUM 끼리 충돌
5. ShareCREATE INDEX데이터 변경(Row Exclusive)과 충돌. 읽기만 허용.
6. Share Row ExclusiveCREATE TRIGGER, FK 체크잘 사용하지 않음
7. ExclusiveREFRESH MAT VIEW CONCURRENTLY잘 사용하지 않음
8. Access ExclusiveDROP, TRUNCATE, VACCUM FULL, LOCK TABLE모든 모두와 충돌. SELECT 조차 불가능하게 하는 유일한 락.
  • 참고
    1. SELECT는 Access Exclusive가 아닌 이상 절대 차단되지 않는다.
    2. UPDATE/DELETE는 Row Exclusive를 얻는다.
    3. CREATE INDEX는 Share를 얻어 쓰기(Row Exclusive)를 막지만 읽기는 허용한다.
    4. VACCUM FULL은 Access Exclusive를 얻어 읽기/쓰기를 모두 막는다.

4. 대기 큐와 교착 상태 (Wait Queue & DeadLocks)

  • 공정성 (Fairness): 각 대기 큐는 기본적으로 먼저 온 요청을 먼저 처리하는 방식. 뒤늦게 온 락 요청이 호환되더라도, 앞에 호환되지 않는 대기자가 있다면 새치기하지 않고 큐에 선다.
  • 교착 상태 (Deadlock): 두 트랜잭션이 서로 상대방이 가진 리소스를 기다리는 순환 의존 상태.
  • 감지 매커니즘:
    • 프로세스가 락을 얻지 못하고 대기하기 시작하면 타이머가 돈다.
    • deadlock_timeout 시간이 지나면 시스템이 교착 상태 감지 (Deadlock Check) 로직을 실행한다.
    • 교착 상태가 확인되면, PostgreSQL은 둘 중 하나를 강제 종료(Abort) 하여 해결함
      • 보통 감지를 유발한 쪽을 선택함

13. 행 단위 락 (Row-Level Locks)

1. 락의 설계 원칙

  • 저장 위치: 행 단위 락은 메모리(Lock Manager)가 아닌 디스크의 튜플 헤더(xmax 필드)에 저장된다.
  • 이유: 메모리 공간 (max_locks_per_transaction)의 제약 없이 수십억 개의 행을 동시에 잠그기 위해서
  • 매커니즘: 트랜잭션이 행을 변경(UPDATE/DELETE)하면, 해당 튜플의 xmax에 자신의 트랜잭션 ID(XID)를 기록한다. 다른 트랜잭션은 이 xmax를 보고 아직 실행 중인 트랜잭션이 해당 행을 점유 임을 인지하고 대기한다.

2. 락 모드 4단계 및 호환성 (Modes)

락 모드 (Mode)주요 명령어특징 및 충돌
1. UpdateDELETE, UPDATE(PK/Unique 변경)가장 가력한 락, 모든 모드와 충돌
2. No Key UpdateUPDATE, SELECT FOR UPDATEKey Share와는 호환됨. (가장 흔한 Update 락).
3. ShareSELECT FOR SHARE데이터 변경(No Key Update 이상)과 충돌
4. Key ShareSELECT FOR KEY SHARE(FK 체크)가장 약한 락. Update와만 충돌 (참조 무결성 확인용).
  • 정리
    1. INSERT는 행을 잠그지 않음
    2. FK (외래키) 체크는 가장 약한 Key Share 락 모드를 사용하여, 부모 테이블의 일반 UPDATE(No Key Update) 를 막지 않음.

3. 멀티트랜잭션 (Multitransactions)

  • 문제: 튜플 헤더의 xmax 필드는 하나의 숫자만 저장할 수 있는데, 여러 트랜잭션이 동시에 공유 락 (Shared Lock)을 걸어야 한다면?
  • 해결: 여러 XID를 그룹화하여 MultiXactId라는 별도의 ID를 발급받아 xmax를 저장하고, 튜플 헤더에 xmax_is_multi 비트를 켠다.
  • 저장소: 멤버 정보는 pg_multixact 디렉토리에 저장된다 (트랜잭션 ID와 마찬가지로 Wraparound 관리가 필요함)

4. 대기 큐 구현 (Wait Queue Implementation)

  • 행 락은 메모리에 없으므로 대기 큐도 없기에 Heavyweight Lock을 보조로 사용한다.
  • 예시: 트랜잭션 B가 트랜잭션 A가 잠근 행을 수정하려 할 때 발생하는 4단계 프로세스
    1. Tuple Lock 획득: B는 해당 튜플에 대해 Heavyweight Lock(type=tuple)을 배타적으로 획득함(새치기 방지)
    2. XID 락 대기: B는 xmax에 기록된 A의 XID에 대해 락을 요청하고 A가 끝날 때까지 잠든다
    3. xmax 갱신: A가 끝나면 B가 깨어나고, 튜플의 xmax를 자신의 XID로 덮어쓴다.
    4. Tuple Lock 해제

5 교착 상태 (Deadlocks) 및 옵션

  • 감지: deadlock_timeout (기본 1초) 동안 락을 얻지 못하면 시스템이 교착 상태 검사를 수행한다.
  • 해결: 사이클이 발견되면 관련 트랜잭션 중 하나를 강제 종료(Abort) 한다.
  • No Wait 옵션:
    • NOWAIT: 락 획득 실패 시 즉시 에러 발생
    • SKIP LOCKED: 잠긴 행은 건너뛰고 처리 (배치 작업이나 큐 구현시 유용)

14. 기타 잠금 (Miscellaneous Locks)

1. 객체 외 락 (Non-Object Locks)

  • 정의: 관계(Relation)가 아닌 시스템 카탈로그상의 객체를 보호하기 위한 Heavyweight Lock.
  • 대상: 테이블스페이스, 스키마, 역할(Role), 구독(Subscription), ENUM 타입 등.
  • 식별: pg_locks에서 locktype = 'object'로 표시됌. 식별자는 database OID, classid(카탈로그 테이블 OID), objid(객체 OID)로 구성됌
  • 예시: CREATE TABLE 실행 시 해당 테이블이 속할 스키마에 대해 AccessShareLock을 획득하여 트랜잭션 중 스키마가 삭제되는 것을 방지함

2. 관계 확장 락 (Relation Extension Locks)

  • 목적: 테이블이나 인덱스 파일의 크기를 늘려 새로운 페이지를 추가할 때, 동시 접근을 제어하기 위함
  • 특징:
    • locktype = 'extend'.
    • 트랜잭션 종료까지 유지되지 않고, 확장 작업이 끝나면 즉시 해제됌
    • 따라서 교착 상태 (Deadlock)을 유발하지 않음 (Wait-for graph에 포함 안 됌)
  • 최적화: 힙(Heap) 파일은 한 번에 여러 페이지(최대 512개)씩 확장하여 락 경합을 줄임. 반면 B-tree는 한 번에 한 페이지씩 확장함

3. 페이지 락 (Page Locks)

  • 주의: 버퍼 캐시의 페이지 핀(pin)이나 경량 락(LWLock) 과는 다름. GIN 인덱스에서만 사용하는 특수한 Heavyweight Lock.
  • 용도: GIN 인덱스의 fastupdate 기능 사용 시, Pending List의 항목들을 메인 인덱스 구조로 이동시키는 작업을 보호하기 위해 메타페이지(MetaPage)를 잠금
  • 특징: locktype = 'page'. 확장 락과 마찬가지로 작업 완료 즉시 해제되므로 교착 상태를 유발하지 않음

4. 자문 락 (Advisory Locks)

  • 개념: 데이터베이스 객체와 무관하게, 애플리케이션이 정의한 추상적 자원을 잠그는 기능. DB 락의 의미를 모르며 획득/해제 규칙만 강제함
  • 식별: 주로 임의의 64비트 정수나 두 개의 32비트 정수를 키로 사용
  • 유효 범위 (Scope)
    1. 세션 레벨 (기본값): 명시적으로 해제하거나 세션이 끝날 때까지 유지됌. 트랜잭션이 끝나도 유지
    2. 트랜잭션 레벨 (_xact 접미사): 트랜잭션이 커밋/롤백되면 자동 해제됌
  • 함수: pg_advisory_lock(대기), pg_advisory_lock_try(대기 안함), pg_advisory_unlock등

5. 서술 락(Predicated Locks) - SSI 구현 핵심

  • 목적: Serializable 격리 수준에서 데이터 뮤결성을 보장하기 위함 (Phantom Read 방지 및 직렬화 이상 현상 감지)
  • 오해: 이름과 달리 아무것도 차단하지 않음. 대신 트랜잭션 간의 데이터 의존성을 추적
  • 작동 원리
    • 데이터를 읽을 때 SIReadLock 모드로 락 정보를 기록
    • 커밋 시점에 위험한 의존성 사이클(RW-dependency)이 감지되면 트랜잭션을 중단 시킴 (Serialization Failure).
  • 락 에스컬레이션 (Escalation): 메모리 절약을 위해 락의 단위를 키움
    1. Tupel: 초기 단계
    2. Page: 페이지 내 튜플 락 수가 max_pred_locks_per_page 를 초과할 때.
    3. Relation: 관계 내 페이지 락 수가 한계치를 넘을 때.
  • 인덱스 지원: B-tree, Hash, Gist, GIN 등 대부분 지원. 인덱스 스캔 시 읽은 범위만 잠금. 지원하지 않는 인덱스나 순차 스캔(Seq Scan) 시에는 테이블 전체에 서술 락이 걸림

15. 메모리 구조 락 (Locks on Memory Structure)

1. 개요 및 분류 (Hierarchy)

  • 공유 메모리 내의 데이터 구조 (Hash table, Buffer header 등)을 동시 접근으로부터 보호하기 위해 사용된다. Heavyweight Lock 보다 훨씬 가볍고 빠름

스핀락 (Spinlocks) - 가장 가벼움

  • 목적: 특정 메모리 셀이나 아주 작은 구조체 업데이트 보호 (수십 CPU 사이클 이내)
  • 구현: 하드웨어의 원자적 명령(Atomic Instructions, TAS, CAS) 사용
  • 동작: 락 획득 실패 시, 루프를 돌며 재시도(Busy-wait, Spinning)하다가 일정 시간 후 잠듦
  • 특징:
    • Exclusive 모드만 지원
    • Deadlock 감지 없음
    • 모니터링 불가능 (Instrumentation 없음)

경량 락 (Lightweight Locks, LWLocks) - 중간 단계

  • 목적: 데이터 구조(버퍼 해시 테이블, WAL 버퍼) 처리 동안 보호.
  • 동작: 락 획득 실패 시 대기 상태로 전환. 대기 큐가 공정하지 않음(No Fair Queue) - 대기 중인 프로세스 중 임의의 하나가 획득 (경합 심할 때는 불리함)
  • 특징:
    • Shared / Exclusive 모드 지원
    • Deadlock 감지 없음 (개발자가 코드 레벨에서 보장해야 함)
    • 모니터링 가능 (pg_stat_activity 등)

2. 주요 사용 사례

버퍼 캐시 (Buffer Cache)

  1. BufferMapping (LWLock): 해시 테이블(페이지 번호 ←> 버퍼 ID 매핑) 보호. 경합을 줄이기 위해 128개의 파티션(Tranche)으로 쪼개져 있음
  2. 버퍼 헤더 스핀 락: 버퍼 헤더의 상태 플래그나 사용 횟수 변경 시 사용
  3. BufferContent (LWLock): 실페 페이지 내용을 읽거나 쓸 때 사용. (핀(Pin) 만으로는 부족할 때)
  4. BufferIO (LWLock): 디스크 I/O 중임을 표시하여 다른 프로세스가 대기하게 만듦.

WAL 버퍼(WAL Buffers)

  1. WALBufMapping (LWLock): WAL 페이지 매핑 보호 (1개만 존재, WAL은 순차적이라 경합 적음)
  2. WALWrite (LWLock) : WAL을 디스크로 쓰는 작업을 직렬화(한 번에 한 프로세스만 쓰기)
  3. WALInsert (LWLock): 8개의 파티션(Tranche). 여러 프로세스가 동시에 WAL 레코드를 버퍼에 채워 넣을 때 사용.
    • 참고: 삽입 위치 예약은 스핀락을 사용함

3. 모니터링 (Monitoring Waits)

  • log_lock_waits: Heavyweight Lock만 로깅하므로 메모리 구조 락들은 기록되지 않음 (단, 데드락 체크 타임아웃 관련)
  • pg_stat_activity 뷰
    • wait_event_type: LWLock, BufferPin(버퍼 핀 대기), Activity (백그라운드 프로세스 동작) 등
    • wait_event: 구체적인 락 이름 (WALWrite, BufferContent)
  • 주의: 스핀락 대기는 여기서 보이지 않으며, 모니터링되지 않는 대기는 Unaccounted로 남음 (CPU 사용 중인 것으로 보이는 상태)

4. 샘플링(Sampling)

  • pg_stat_activity는 현재 시점의 스냅샷만 보여주므로, 짧게 지나가는 메모리 락 대기는 포착하기 어려움.
  • pg_wait_sampling 확장: 대기 이벤트를 주기적으로 샘플링하여 프로파일링(History) 정보를 제공. 짧은 대기 이벤트 분석에 필수적.

16. 쿼리 실행 단계 (Query Execution Stages)

1. 단순 쿼리 프로토콜 (Simple Query Protocol)의 4단계

  • 클라이언트가 텍스트 쿼리를 보내면 서버는 아래 4단계를 거쳐 전체 결과를 반환한다.

파싱 (Parsing)

  • 목적: 쿼리 테스트의 문법적 오류 검사 및 구문 분석
  • 과정:
    • Lexer & Parser: 텍스트를 토큰(Lexeme)으로 분리하고 문법 규칙(Gramme)에 맞는지 검사. (Flex/Bison 사용)
    • 결과: 파스 트리 (Parse Tree, Raw Parse Tree) 생성
  • 특징: 테이블 존재 여부나 권한은 확인하지 않음 (문법만 확인)

변환 (Transformation / Analysis)

  • 목적: 쿼리의 의미(Semantics)를 분석하고 재작성.
  • 과정
    • Semantic Analysis: 시스템 카탈로그를 조회하여 테이블 컬럼 존재 여부, 타입 확인, 권한 검사.
    • Rewriting: 뷰(View)를 기본 쿼리(Base Query)로 치환하거나, 룰(Rule System) 적용
  • 결과: 쿼리 트리 (Query Tree) 생성

계획 (Planning / Optimization)

  • 목적: 가장 효율적인 실행 경로(Plan) 찾기.
  • 비용 기반 최적화 (Cost-based): 통계 정보를 바탕으로 I/O와 CPU 비용을 계산하여 가장 저렴한 경로 선택.
  • 주요 작업:
    • 조인 순서 결정: join_collapse_limit(default 8) 이하일 때 조인 순서를 최적화 (Flattening). 그 이상이면 GEQ0(유전 알고리즘) 사용 가능.
    • 노드 선택: 스캔 방법 (Seq Scan, Index Scan) 및 조인 방법(Hash, Merge, Nested Loop) 결정.
  • 결과: 플랜 트리 (Plan Tree) 생성.

실행 (Executioon)

  • 목적: 실제 데이터를 가져오고 연산 수행.
  • 구조: 포털(Portal) 이라는 객체에 쿼리 상태 저장.
  • 파이프라인 방식(Assembly Line): 루트 노드가 자식 노드에게 데이터를 요청(Pull) 하면, 자식 노드가 데이터를 처리하여 올려주는 방식
  • 메모리: 정렬/해시 작업 시 work_mem 사용. 부족할 경우 임시 파일(disk) 사용.

2. 확장 쿼리 프로토콜 (Extended Query Protocol)

  • 반복되는 쿼리의 효율성과 보안을 위해 단계를 분리하여 실행하는 방식 (Prepared Statement)
  • 구조: Prepare → Bind → Execute (파싱/변환 → 파라미터 바인딩 → 계획/실행)
  • 장점:
    • 파싱 비용 절감: 쿼리 텍스트 분석 및 변환을 한 번만 수행하고 백엔드 메모리에 저장 (글로벌 캐시는 아님)
    • 보안: 파라미터 바인딩을 통해 SQL Injection 방지
  • 계획 (Plan)의 종류:
    1. 커스텀 플랜 (Custom Plan): 바인딩된 구체적인 파라미터 값을 보고 최적화한 계획
      • 예시: 특정 값의 선택도가 낮으면 인덱스 스캔
    2. 제네릭 플랜 (Generic Plan): 파라미터 값과 무관하게 재사용 가능한 일반적인 계획
      • $1 등 변수로 처리
  • 플랜 선택 로직:
    • 처음 5번은 무조건 커스텀 플랜 사용
    • 6번째부터는 제네릭 플랜 비용 < 커스텀 플랜 평균 비용일 경우 제니릭 플랜으로 고정하여 재사용.
    • 제어: plan_cache_mode 파라미터로 강제 설정 가능

3. 커서(Cursors)와 결과 반환

  • 단순 프로토콜: 결과 행(Row)이 아무리 많아도 한 번에 클라이언트로 전송.
  • 확장 프로토콜/커서: 결과를 배치(Batch) 단위로 나눠서 가져올 수 있음(FETCH)
  • 최적화: 커서 사용 시 플래너는 전체 결과가 아닌 상위 일부(cursor_tuple_fraction)를 빨리 반환하는 데 최적화된 계획(Startup cost)을 선택함

17. 통계 (Statistics)

1. 기본 통계 (Relation-Level Statistics) - pg_class

  • 플래너가 가장 먼저 확인하는 테이블 전체 단위의 통계 정보
  • reltuples: 테이블의 추정 행(Row) 수. 스캔 비용 계산의 기초
    • 특이값 -1: 테이블 생성 후 한번도. ANALYZE 되지 않음을 의미 (실제 0건과 구별)
  • relpages: 테이블의 크기 (페이지 수). I/O 비용 계산에 사용
  • relallvisible: 가시성 맵(VM)에 태깅된 페이지 수. Index-Only Scan 비용 산정 시 중요

2. 컬럼 수준 통계 (Column-Level Statistics) - pg_stats 뷰

  • pg_statistic 시스템 카탈로그를 보기 좋게 만든 뷰
  • null_frac: NULL값의 비율 (WHERE col IS NULL 예측 시 사용)
  • avg_width: 컬럼 값의 평균 바이트 크기. 메모리 사용량(work_mem) 추정에 사용 (Sort, Hash)
  • n_distinct: 고유 값 (Distinct Value) 의 개수
    • 양수 (+): 실제 고유 값의 개수 (예: 3이면 고유 값이 3개)
    • 음수 (-): 전체 행 대비 비율 (Scaling) (예: -0.3은 전체 행의 30%가 고유값)
    • 용도: 균등 분포 가정 시 선택도(Selectivity) = 1/n_distinct.
  • correlation (상관관계): 물리적 저장 순서와 논리적 값 순서의 연관성 (-1 ~ 1)
    • 1 또는 -1: 완벽하게 정렬 됌 (Index Scan 비용 낮음, 랜덤 액세스 적음)
    • 0: 무작위 분포 (Index Scan 비용 높음)

3. 데이터 분포 통계 (MCV & Histogram)

  • 데이터가 균등하게 분포하지 않을 때 (Non-uniform) 정확도를 높이기 위한 장치입니다.
  • MCV (Most Common Values): 가장 빈번하게 등장하는 값(most_common_vals)과 그 빈도 (most_common_freqs)를 저장
    • 용도: 등호 조건(=)의 선택도를 매우 정확하게 계산.
  • Historgram (히스토그램): MCV를 제외한 나머지 값들을 버킷(Bucket)으로 나누어 경계값 (histogram_bounds)만 저장
    • 특징: 각 버킷에 포함된 행의 비율은 동일함.
    • 용도: 범위 검색(<, >, BETWEEN 선택도 추정

4. 표현식 통계 (Expression Statistics)

  • 문제점: WHERE func(col) = const와 같이 가공된 컬럼은 통계가 없어 플래너가 기본 선택도(0.5%)를 적용해 추정이 매우 부정확함
  • 해결책 1 (Extended Statistics): CREATE STATISTICS 명령어로 표현식에 대한 통계를 수동 생성
  • 해결책 2 (Expression Index): 표현식의 인덱스를 생성하면, 해당 표현식에 대한 통계가 자동으로 수집됨

5. 다변수 통계 (Multivariate Statistics)

  • 여러 컬럼 간의 연관 관계 (상호 의존성)를 파악하여 선택도 과소평가(Underestimation)를 방지한다. (CREATE STATISTICS로 생성)
  • 기능정 종속성 (Functional Dependencies): 시(City)가 결정되면 우편번호(Zip)가 결정되는 관계 등을 파악
  • 다변수 N-Distinct: Group By a, b 처럼 여러 컬럼을 그룹핑할 때의 카디널리티 추정
  • 다변수 MCV: 여러 컬럼 조합의 빈도수를 저장 (상관관계가 높은 컬럼들의 복합 조건 필터링 시 유용)

6. 수집 및 설정 (Sampling & Config)

  • 샘플링: 테이블 전체를 읽지 않고 샘플링하여 통계를 만듦.
    • 샘플 크기 = 300 * default_statistics_target (행)
  • default_statistics_target: 기본값 100. MCV 목록 크기, 히스토그램 버킷 수 등을 결정.
    • 튜닝: 특정 컬럼의 분포가 복잡하면 ALTER TABLE ... ALTER COLUMN ... SET STATISTICS로 이 값을 늘려 정확도를 높일 수 있음.

18. 테이블 접근 방법 (Table Access Methods)

1. 플러그형 스토리지 엔진 (Pluggable Storage Engines)

  • PostgreSQL 12부터 테이블 데이터의 저장 및 접근 방식을 교체할 수 있는 인터페이스 도입
  • 기본 엔진: heap (표준 스토리지 엔진)
  • 구조:
    • 공유 컴포넌트 (Core): 트랜잭션 관리(MVCC), 버퍼 매니저, WAL, 쿼리 최적화기 등은 모든 엔진이 공유함
    • 엔진별 정의: 튜플 구조, 스캔 방식, INSERT/UPDATE/DELETE 구현, Vaccum 방식 등
  • 새로운 엔진 예시
    • zheep: Undo 로그를 사용하여 비화(Bloat)를 방지하고 제자리 업데이트(in-place update)를 수행.
    • zedstore: 컬럼 지향(Columnar) 스토리지로 OLAP 쿼리에 최적화

2. 순차 스캔 (Sequential Scans)

  • 테이블의 메인 포크 파일을 처음부터 끝까지 읽는 가장 기본적인 스캔 방식
    • 작동 방식: 페이지를 차례로 읽고 각 튜플의 가시성(Visibility)를 확인하여 필터링한다.
    • 버퍼 링 (Buffer Ring): 대용량 테이블 스캔 시 버퍼 캐시 전체를 밀어내지 않도록 작은 크기의 버퍼 링을 사용합니다. 여러 프로세스가 스캔 시 이 링을 공유(Synchronized Scan)하여 I/O를 절약합니다.
  • 비용 산정 (Cost Estimation):
    • I/O 비용: relpages * seq_page_cost (순차 읽기는 랜덤 읽기 보다 빠름)
    • CPU 비용: reltuples * cpu_table_cost
    • 특징: 선택도(Selectivity)가 낮아 대부분의 행을 읽어야 할 때 인덱스 스캔보다 효율적입니다.

3. 병렬 쿼리 (Parallel Plans)

  • 쿼리를 여러 프로세스가 나누어 처리하여 속도를 높이는 기법이다.
  • 구조: Leader 프로세스가 Worker 프로세스들을 생성하고, Gather 노드에서 결과를 취합합니다.
  • 병렬 순차 스캔 (Parallel Seq Scan):
    • 작동: 여러 워커가 테이블의 블록들을 나누어 읽습니다. 동기화를 통해 같은 블록을 중복해서 읽지 않습니다.
    • 주의: OS 레벨의 프리페칭(Prefetching)이 작동하지 않으므로, PostgreSQL이 자체적으로 블록 단위 프리페칭을 수행합니다.
  • 비용 산정 차이:
    • CPU 비용: 전체 튜플 수를 프로세스 수(Leader 포함)로 나누어 계산합니다 (분산 처리 효과)
    • I/O 비용: 전체 페이지 수는 나누지 않습니다 (디스크 부하는 동일).
    • 오버헤드: 프로세스 시작 비용(parallel_setup_cost)과 튜플 전송 비용(parallel_tuple_cost)이 추가됩니다.

4. 병렬 처리 제한 및 설정 (Limitations & Config)

  • 프로세스 수 제어 계층:
    1. max_worker_processes: 전체 시스템의 백그라운드 워커 총량.
    2. max_parallel_workers: 병렬 쿼리용 워커 풀
    3. max_parallel_workers_per_gather: 단일 쿼리당 최대 워커 수.
  • 자동 할당 로직: 테이블 크기가 min_parallel_table_scan_size(기본 8MB)보다 작으면 병렬 처리를 안 함. 그 이상이면 로그 스케일(3배 커질 때마다 워커 1명 추가)로 워커 수를 늘림
  • 병렬 처리 불가 (Non-Parallelizable):
    • 데이터 수정 (UPDATE, DELETE)
    • 커서 (Cursor) 사용 쿼리
    • PARALLEL UNSAFE 함수 포함 시
  • 병렬 제한 (Parallel Restricted - Leader만 실행 가능):
    • CTE 스캔 (Materialized)
    • 임시 테이블 (Temporary Table) 스캔
    • PARALLEL RESTRICTED 함수 포함 시.

19. 인덱스 액세스 방식 (Index Access Methods)

1. 인덱스 엔징 아키텍처 (Architecture)

  • PostgreSQL은 특정 인덱스의 구현 세부 사항을 코어에서 분리하여 확장성을 제공합니다.
  • 인덱스 엔진 (Indexing Engine): 코어 시스템, 모든 액세스 방식에 공통적인 작업을 수행함.
    • TID(Tuple ID)를 받아 힙(Heap) 튜플 읽기
    • MVCC 가시성 확인 (인덱스는 가시성 정보가 없음
    • 조건 재확인 (Recheck) 수행
  • 액세스 방식 (Acess Method, AM): 인덱스 알고리즘 구현체 (B-tree, Hash, Gist 등)
    • 역할: 인덱스 구조 관리, 페이지 레이아웃, 락(Lock)관리, WAL 로깅, 검색/삽입 알고리즘
    • 내장 AM: btree, hash, gist, gin, spgist, brin

2. 연산자 클래스와 패밀리 (Operator & Classes & Famlies)

  • AM는 트리 구조를 알지만 데이터의 의미(비교 방법 등)는 모름, 이를 연결해 주는 것이 연산자 클래스이다.
    • 전략 (Strategies): SQL 연산자와 AM의 논리를 매핑 (B-tree에서 전략 1은 < 연산자)
    • 지원 함수 (Support Functions): AM이 내부적으로 사용하는 C 함수 (두 값을 비교하여 -1, 0, 1반환)
  • 연산자 패밀리 (Operator Family): 여러 데이터 타입(예: int2, int4, int8) 간의 비교를 지원하기 위해 연산자 클래스들을 그룹화한 것.
  • 실전 예시 (text_pattern_ops):
    • 기본 text_ops: 로케일(Collation)을 따름. LIKE 검색 시 인덱스 사용이 불가할 수 있음.
    • text_pattern_ops: 바이트 단위 비교. 로케일과 무관하게 LIKE 'abc%' 검색 최적화 가능

3. 인덱스 속성 3단계 (Properties)

  • 인덱스가 어떤 기능을 지원하는지 시스템(플래너)에 알리는 인터페이스. (중요)

인덱스 방식 속성 (AM Level)

  • can_order: 정렬된 데이터 반환 가능한지 (B-tree만 가능)
  • can_unique: 유니크 제약 조건 지원하는지 (B-tree만 가능)
  • can_multi_col: 다중 컬럼 인덱스를 지원하는지
  • can_exclude: 제외(Excluision) 제약 조건을 지원하는지 (GIST, SP-GIST, Hash 등)
  • can_include: INCLUDE절 (Non-Key 컬럼 포함)을 지원하는지

인덱스 레벨 속성 (Index Level)

  • clusterable: CLUSTER 명령어로 테이블 정렬이 가능한지
  • index_scan: 결과를 하나씩 반환 가능한지 (GIN은 불가능, Bitmap만 가능)
  • bitmap_scan: 비트맵 스캔을 지원하는지 (대부분은 가능)
  • backward_scan: 역방향 스캔을 지원하는지 (B-Tree는 가능)

컬럼 레벨 속성 (Column Level)

  • asc, desc, nulls_first/last: 정렬 순서 및 NULL 위치 (B-tree 관련)
  • returnable(중요): Index-Only Scan 가능 여부. 인덱스가 원본 데이터를 저장하고 있어 힙 접근 없이 반환 가능한지 (Hash는 False)
  • distance_orderable: 거리 기반 정렬(k-NH, ←> 연산자) 지원이 가능한지 (GIST SP-GIST)

20. 인덱스 스캔 (Index Scans)

1. 일반 인덱스 스캔 (Regular Index Scans)

  • 가장 기본적인 방식. 인덱스에서 TID를 찾아 힙(Heap) 테이블을 조회함
  • 작동 방식: Index Scan노드, 인덱스 액세스 방식(AM)이 TID를 하나씩 반환하면, 인덱싱 엔진이 해당 힙 튜플을 가져와 가시(Visibility)를 확인하고 반환함
  • 비용 산정 (Cost):
    • 구성: 인덱스 페이지 읽기 비용 + 힙 페이지 읽기 비용 + CPU 비용
    • 상관관계 (Correlation)의 중요성: 물리적 저장 순서와 인덱스 논리 순서의 연관성 (1이나 -1에 가까울수록 좋음)
      • 높은 상관관계: 인덱스 순서대로 힙 페이지를 읽을 때 순차 접근(Sequential access)에 가까워 성능이 우수함
      • 낮은 상관관계: 힙 페이지를 무작위로 오가며 읽어야 함 (Random I/O). 비용이 급격히 증가함
    • 캐싱: effective_cache_size가 클수록 디스크 I/O 비용을 낮게 평가하여 인덱스 스캔을 선택할 확률이 높아짐

2. 인덱스 온리 스캔 (Index-Only Scans)

  • 힙 테이블 접근을 생략하고 인덱스만 읽어 데이터를 반환하는 최적화 방식.
  • 작동 방식: Index Only Scan노드. 인덱스가 쿼리에 필요한 모든 컬럼을 포함하고 있어야 함 (Covering Index)
  • 필수 조건 (Visibility Map): 인덱스에는 튜플의 가시성 정보가 없음. 따라서 Visibility Map(VM)을 확인하여 해당 페이지가 All-Visible인 경우에만 힙 접근을 생략함
    • Heap Fetches: VM에 All-Visible로 표시되지 않은 페이지는 힙을 직접 확인해야 하며, 실행 계획에 Heap Fetches 수로 표시됌
  • INCLUDE 절: 유니크 인덱스의 키(Key)가 아닌 컬럼들을 INCLUDE로 추가하여 커버링 인덱스로 활용 가능 (유니크 제약 조건에 영향을 주지 않음)

3. 비트맵 스캔 (Bitmap Scans)

  • 낮은 상관관계(Low Correlation)로 인한 랜덤 I/O 문제를 해결하고, 여러 인덱스를 결합하기 위한 방식.
  • 작동 방식 (2단계):
    1. Bitmap Index Scan: 인덱스를 읽어 조건에 맞는 TID들의 비트맵을 생성 (테이블 접근 안 함)
    2. Bitmap Heap Scan: 생성된 비트맵을 바탕으로 힙 페이지를 물리적 순서(Offset)순서대로 읽음. 랜덤 I/O를 순차 I/O로 변환
  • 인덱스 결합: BitmapAnd, BitmapOr노드를 사용해 여러 인덱스의 비트맵을 비트 연산하여 결합 가능
  • 비트맵 정확도 (Lossy Bitmap):
    • 비트맵 크기가 work_mem을 초과하면 Lossy 모드로 전환.
    • Exact: 튜플 단위 비트맵
    • Lossy: 페이지 단위 비트맵. 페이지를 읽은 후 조건 재확인(Recheck)이 필요함

4. 병렬 스캔 (Parallel Index Scans)

  • 모든 스캔 방식(Index Scan, Index-Only Scan, Bitmap Scan)은 병렬 처리를 지원함
  • 작동 방식: 리더와 워커 프로세스들이 인덱스 페이지(B-tree의 경우)나 힙 페이지를 나누어 스캔.
  • Parallel Bitmap: 비트맵 생성은 리더가 수행(직렬), 힙 스캔은 병렬로 수행

21. 중첩 로프 조인 (Nested Loop Joins)

1. 기본 개념 및 알고리즘

  • 가장 기본적이고 유연하 조인 방식
  • 작동 원리: 두 개의 반복문(Loop)를 사용
    • 외부 집합 (Outer Set/Driving Table): 먼저 스캔하는 테이블
    • 내부 집합 (Inner Set): 외부 집합의 각 행(Row)마다 한 번씩 스캔되는 테이블
  • 효율성 결정 요인:
    1. 외부 집합의 크기: 작을수록 유리함 (반복 횟수 감소)
    2. 내부 집합 접근 효율: 인덱스 스캔이 가능한 경우 매우 빠름 (전체 스캔시 O(N * M )으로 매우 느림)

2. 주요 실행 방식 3가지 (Execution Variations)

  • 플래너가 상황에 따라 선택하는 3가지 변형 노드를 구분해야 한다.

카테시안 곱 (Cartesian Product) - CROSS JOIN

  • 상황: 조인 조건이 없을 때 모든 가능한 조합 생성
  • 구조: Nested Loop → (Seq Scan + Materialize)
  • Materialize 노드: 내부 집합을 매번 디스크에서 읽지 않고 메모리(또는 임시 파일)에 저장해두고 반복 사용한다.

파라미터화된 조인 (Parameterized Joins) - 일반적인 인덱스 조인

  • 상황: 외부 테이블의 값을 파라미터로 사용하여 내부 테이블을 검색할 때.
  • 구조: Nested Loop → Seq Scan + Index Scan)
  • 특징: 외부 행의 값(키)를 내부 테이블의 검색 조건(Index Cond)으로 전달한다. 내부 집합이 대용량이어도 인덱스를 타므로 효율적임

메모이제이션 (Memoization) - v.14 신기능

  • 상황: 파라미터화된 조인에서, 외부 테이블에 중복된 키 값이 많을 때.
  • 구조: Nested Loop → (Seq Scan + Memoize + Index Scan)
  • Meomize 노드: 파라미터 값(키)과 그에 따른 내부 스캔 결과를 해시 테이블에 캐싱한다.
    • 이미 조회한 키가 들어오면 인덱스 스캔을 생략하고 캐시된 결과를 반환한다.
    • work_mem 내에서 LRU 방식으로 관리된다.

3. 조인 유형별 지원 여부 (Join Types Support)

  • 지원하는 경우
    • INNER JOIN: 기본 방식
    • Left oUter Join: Nested Loop Left Join노드. 내부 집합에서 매칭되는 행이 없으면 NULL로 채워서 반환.
    • Semi Join(EXISTS): 첫 번째 매칭되는 행을 찾으면 즉시 중단하고 반환(효율적)
    • Anti Join(Not EXISTS): 첫 번째 매칭되는 행을 찾으면 즉시 중단하고 해당 외부 행을 버림 (효율적)
  • 지원하지 않는 경우
    • Right Outer Join / Full Outer Join: 중첩 루프 알고리즘 특성상(외부 집합 기준 순회), 내부 집합의 매칭되지 않은 남은 행을 추적하여 반환하기 어렵기 때문 (이 경우 Hash Join이나 Merge Join이 사용)

4. 비동등 조인 (Non-Equi-joins) 유일한 장점

  • Hash, Merge Join은 오직 등호 조건만 처리 가능함
  • Nested Loop는 부등호(>, <) 범위, 지리적 거리 등 임의의 모든 조건으로 조인이 가능함. 인덱스가 없으면 느리지만 유일한 대안인 경우가 많음

5. 병렬 처리

방식: 외부 집합은 병렬 워커들이 나누어서 스캔할 수 있지만 내부 집합은 각 워커가 독자적으로(순차적으로)스캔/인덱스 탐색을 수행한다.

22. 해싱 (Hashing)

1. 해시 조인 기본 매커니즘

  • 해시 조인은 = 에서만 사용할 수 있으며, 대용량 데이터 처리에 효율적임
  • 알고리즘 2단계:
    1. 빌드 단계 (Build Phase): 두 테이블 중 더 작은 집합 (Inner Set)을 읽어 해시 테이블을 메모리에 생성 (Hash 노드)
    2. 프로브 단계 (Probe Phase): 다른 테이블(Outer Set)을 스캔하며 해시 키를 계산하고, 해시 테이블에서 매칭되는 행을 찾는다. (Hash Join 노드)
  • 메모리 제한: 해시 테이블 크기는 work_mem * hash_mem_multiplier로 제한된다.

2. 실행 모드 (Execution Modes)

  • 메모리 가용량에 따라 실행 방식이 달라짐

원 패스 (One-Pass) - 인메모리

  • 조건: 해시 테이블 전체가 할당된 메모리에 들어갈 때
  • 동작: Inner Set을 모두 메모리 해시 테이블에 올린 후, Outer Set을 스캔하며 즉시 조인, 가장 빠름

투 패스 (Two-Pass) - 디스크 사용

  • 조건: 해시 테이블이 메모리보다 클 때
  • 배치 (Batche): 데이터 세트를 여러 개의 배치로 쪼개어 임시 파일(disk)에 저장한다.
  • 동작:
    1. Inner/Outer Set을 스캔하며 해시 값에 따라 배치를 나눈다. (Batch 0은 메모리 유지 시도)
    2. 나머지 배치는 디스크에 쓴다.
    3. 각 배치별로 해시 테이블을 로드하고 조인을 수행한다. (I/O 비용 발생)

3. 동적 보정 (Dynamic Adjustments)

  • 통계 정보가 부정확하거나 데이터 분포가 고르지 않을 때(Skew) 실행 중에 전력을 수정한다.
  • 배치 분할 (Increasing Batches): 해시 테이블 생성 중 메모리가 부족해지면, 배치 수를 2배로 늘려 데이터를 더 잘게 쪼개고 디스크로 내린다.
  • 스큐 최적화 (Skew Optimization): Outer Set에서 가장 빈번하게 등장하는 값 (MCV)에 해당하는 해시 키들을 별도로 관리하여, 이들이 할상 첫 번째 배치에 머물게 하여 I/O를 줄인다.

4. 병렬 해시 조인 (Parallel Hash Joins)

  • 일반 해시 조인 (구형): 각 워커가 개별적(Private) 해시 테이블을 만든다. (메모 낭비 심함)
  • 병렬 해시 조인 (Parallel Hash Join): 모든 워카가 공유 메모(Shared Memory)에 하나의 거대한 해시 테이블을 협력하여 만든다.
    • 장점: 메모리 효율이 좋고, 원 패스로 처리할 확률이 높아진다.
    • 병렬 투 패스: 메모리가 부족하면 워커들이 협력하여 배치를 디스크에 나누어 쓴다ㅓ.

5. 해시 집계(Aggregation & Distinct)

  • HashAggregate 노드는 GROUP BY, DISTINCT, UNION(중복 제거) 처리에 사용된다.
  • 동작: 해시 테이블을 만들어 그룹 키를 저장한다.
  • 메모리 초과 시: 해시 조인과 유사하게 데이터를 파티션으로 나누어 디스크에 쓴다 (Spill to Disk)
  • 비교: 정렬이 필요 없으면 Sort + GroupAggregate보다 HashAggregate가 일반적으로 빠르다.

23. 정렬과 병합 (Sorting and Merging)

1. 병합 조인 (Merge Joins)

  • 두 개의 정렬된(Sorted) 집합을 스캔하며 결합하는 방식. 인덱스 스캔이나 명시적 정렬(Sort)가 선행되어야 한다.
  • 알고리즘: 두 집합(Inner, Outer) 의 커서를 이동하며 키를 비교함. 키가 같으면 반환하고, 다르면 작은 쪽의 커서를 이동시킨다.
  • 특징:
    • 조건: Equal Join만 지원
    • 비용: 입력이 정렬되어 있다면 매우 효율적(선형 복잡도)이며 메모리를 거의 쓰지 않는다. 정렬되지 않았다면 정렬 비용 (O(N * Log N))이 추가된다.
    • 중복처리: Inner 집합에 중복이 있으면 커서를 되돌려(Restore) Outer 행과 다시 매칭한다.
  • 병합 조인 지원 범위:
    • Outer Join: Inner/Left Outer Join은 정렬 순서를 유지하지만 Full/Right Outer Join은 NULL 값이 중간에 끼어들어 정렬 순서를 꺨 수 있으므로, 실행 계획에 Sort 노드가 추가될 수 있다.
    • 병렬 처리: 병렬 처리는 가능하지만, Outer 집합만 병렬로 나누어 읽고 Inner 집합은 모든 워커가 전체를 스캔해야 한다.

2. 정렬 알고리즘 5가지

  • Sort 노드는 메모리 가용량(work_mem)과 목적(Limit 등)에 따라 알고리즘을 선택한다.

퀵소트 (Quicksort)

  • 조건: 데이터가 work_mem 메모리 내에 들어올 때.
  • 특징: 가장 일반적이고 빠르다.

Top-N 힙소트 (Top-N Heapsort)

  • 조건: Order By ... Limit N 쿼리이면서, 전체 정렬 대신 상위 N개만 필요할 때.
  • 특징: N개의 항목을 유지하는 이진 힙(Heap)구조를 사용하여 메모리를 절약한다.

외부 병합 정렬 (External Merge Sort)

  • 조건: 데이터가 work_mem보다 클 때.
  • 과정:
    1. 데이터를 메모리만큼 잘라서 퀵소트 후 임시 파일에 쓴다.
    2. 정렬된 파일들을 다시 읽어 병합하고 파일이 너무 많으면 여러 패스를 거침
  • 비용: 디스크 I/O 비용이 발생하여 느림

증분 정렬 (Incremental Sort) - V.13 추가기능

  • 상황: 데이터가 이미 앞쪽 키(key)로 정렬되어 있을 때 (인덱스가 (a,b)인데 ORDER BY a,b,c 를 요청)
  • 동작: 이미 정렬된 그룹 (a, b가 같은 행들) 단위로 나누어 뒤쪽 키(c)만 정렬한다. 전체를 다시 정렬하는 것보다 훨씬 빠르다.

병렬 정렬 (Gather Merge)

  • 상황: 병렬 쿼리에서 정렬된 결과를 ㅜ언할 때
  • 노드 Gather 대신 Gather Merge 노드를 사용한다.
  • 동작: 각 워커가 정렬한 데이터를 리더가 받아 바이너리 힙을 이용해 병합하여 정렬 순서를 유지한다.

3. 그룹화 및 중복 제거 (Distinct & Grouping)

  • 해싱뿐만 아니라 정렬을 통해서도 그룹화를 수행할 수 있다.
  • Unique 노드: 정렬된 데이터에서 인접한 중복 값을 제거한다 (DISTINCT)
  • GroupAggregate 노드: 정렬된 데이터를 순서대로 읽으며 집계 함수를 계산한다. (HashAggregate 보다 메모리를 적게 씀)
  • MixedAggregate 노드: GOUPING SET 사용 시, 일부는 정렬로, 일부는 해싱으로 처리하여 효율을 높인다.
특징Nested Loop (중첩 루프)Hash Join (해시 조인)Merge Join (병합 조인)
적합한 데이터작은 데이터셋 (또는 첫 행 반환이 빠를 때)대용량 데이터셋 (메모리 충분 시)대용량 데이터셋 (이미 정렬된 경우)
조인 조건모든 조건 (부등호, 범위 등)**등호(=)**만 가능**등호(=)**만 가능
메모리 사용적음많음 (work_mem에 해시 테이블 생성)적음 (단, 정렬 필요시 많이 사용)
초기 비용낮음 (즉시 반환 시작)높음 (해시 테이블 빌드 대기)높음 (정렬 대기, 인덱스 있으면 낮음)
복잡도O(N×M) (인덱스 시 선형)O(N+M) (선형)O(N+M) (선형) + 정렬비용

정리

  1. Merge Join은 인덱스가 있거나 정렬이 필요할 때 유리하다.
  2. Hash Join은 정렬되지 않은 대용량 데이터를 처리할 때 가장 빠르지만, 해시 테이블 생성 전까지 결과를 반환하지 않는다.
  3. Nested Loop만이 유일하게 비동등 조인을 처리할 수 있다.

24. 해시 인덱스 (Hash Indexes)

1. 기본 개념

  • 정의: 디스크 기반의 해시 테이블 (Hash Tables). 키 값을 해싱하여 저장하고 검색
  • 용도: 동등 비교 검색만 지원
  • 저장 방식: 인덱스 키 값 자체를 저장하지 않고, 해시 코드와 TID만 저장
    • 결과: 키 길이가 길어도 인덱스 크기가 작음 (고정 크기 해시만 저장)
    • 제약: Index-Only Scan 불가능 (원본 키가 없으므로 힙 테이블 재확인이 필수임)
    • 충돌(Collision): 해시 충돌 가능성이 있으므로, 인덱스 검색 후 반드시 힙 튜플을 읽어 값을 재확인 (Recheck)해야 함

2. 페이지 구조 (Page Layout)

  1. 메타페이지 (Metapage, 0번 페이지): 인덱스 제어 정보 저장 (총 튜플 수, 채우기 비율 fillfactor, 버킷 매핑 정보 등)
  2. 버킷 페이지 (Bucket Pages): 실제 해시 코드와 TID데이터를 저장하는 메인 페이지.
  3. 오버플로우 페이지 (Overflow Pages): 특정 버킷이 꽉 찼을 때 체이닝(Chaining)방식으로 연결되는 추가 페이지
  4. 비트뱁 페이지 (Bitmap Pages): 비워진 오버플로우 페이지를 추적하여 재사용하기 위한 관리 페이지

3. 동적 해싱 및 관리 (Dynamic Hashing)

  • 확장 (Expansion): 데이터가 늘어나면 버킷을 하나씩 쪼개서 확장함 (Split)
    • 한 번에 전체를 두 배로 늘리는 것이 아니라, 한 번에 하나의 버킷만 분할하여 부하를 분산함
    • 비트 마스크 (Bit Mask): High mask와 Low mask를 사용하여 해시 코드를 적절한 버킷 주소로 매핑
  • 축소 (Shrinking): 불가능함
    • DELETE로 데이터가 지워져도 할당된 페이지는 OS로 반환되지 않음 (비트맵 페이지를 통해 재사용만 가능)
    • 물리적 크기를 줄이려면 REINDEX 또는 VACCUM FULL이 유일한 방법
  • WAL 로깅: PostgreSQL 10부터 지원 (이전에는 복구/복제가 불가능)

4. 속성

속성지원 여부설명
정렬 (can_order)❌해시 함수는 순서를 섞으므로 정렬 불가.
유니크 (can_unique)❌유니크 제약 조건 생성 불가.
제외 제약 (can_exclude)✅Exclusion Constraint는 지원함 (유일성 보장용으로 활용 가능).
다중 컬럼 (can_multi_col)❌단일 컬럼만 지원.
Index-Only Scan❌원본 데이터가 없어 힙 접근 필수 (returnable=f).
NULL 검색❌NULL은 해시 연산 불가 (search_nulls=f).
클러스터링❌해시 순서로 테이블을 정렬하는 것은 의미 없음.

5. 연산자 클래스 (Operator Class)

  • 기본 연산자는 오직 = Equality 하나뿐임
  • 지원 함수는 해시 코드를 반환하는 함수를 포함함

25. B-Tree

1. 개요 및 구조

  • 정의: 정렬 가능한 데이(Ordinal Types)를 위해 설계된 균형 트리. 모든 리프 노드가 동일한 깊이에 위치하여 검색 성능이 균일함
  • 특징:
    • 높은 분기율 (High Branching Factor): 하나의 노드에 수백 개의 아이템 저장 가능. 트리의 깊이가 매우 낮음
    • 양방향 연결 리스트 (Bidirectional List): 같은 레벨의 노드끼리 양방향으로 연결되어 있어, 정렬된 순서대로 범위 검색 (Range Scan) 및 역방향 검색 (Backward Scan)이 매우 효율적임
  • 페이지 구성:
    • 메타페이지(Metapage): 루트 페이지의 위치와 트리의 레벨 정보를 저장 (0번 페이지)
    • 하이 키 (High Key): 각 페이지 (가장 오른쪽 페이지 제외)는 해당 페이지에 들어갈 수 있는 값의 상한선(High Key)를 저장함

2. 동작 원리 (Operations)

  • 검색: 루트에서 시작하여 Ki <= value <= Ki+1조건을 만족하는 자식 노드로 내려감. 리프 노드에 도달하면 이진 탐색(Binary Search 수행)
    • 범위 검색: 시작 값을 찾은 후 연결 리스트를 따라 이동
  • 삽입 (Insertion): 키 순서에 맞는 리프 노드를 찾아 삽입
    • 분할 (Split): 노드에 공간이 없으면 페이지를 둘로 쪼개고, 부모 노드에 새 포인터를 추가, 루트가 쪼개지면 트리 깊이가 1증가함
    • 병합 불가: 한 번 쪼개진 노드는 다시 병합되지 않음 (Vaccum으로 공간만 회수)

3. 최적화 기능 V.13

  • 중복 제거 (Deduplication): 유니크하지 않은 인덱스에서 동일한 키 값이 반복될 때, 여러 엔트리를 만드는 대신 하나의 키와 TID리스트(Posting List)로 병합하여 저장. 인덱스 크기를 획기적으로 줄임
    • 제약: 데이터 타입이 단순 바이너리 비교를 지원해야 함
  • 접미사 잘라내기 (Suffix Truncation): 내부 노드(Inner Node)는 검색 경로만 결정하면 되므로, 전체 키 대신 구분 가능한 접두사만 저장하여 공간을 절약함

4. 연산자 클래스 (Operator Classes)

전략 (Strategies): 5가지 비교 연산자 필수 (<, <=, =, >=, >). • 지원 함수: 두 값을 비교하여 -1, 0, 1을 반환하는 함수 필수. • 패턴 매칭 (LIKE):     ◦ text_ops (기본값): 로케일(Collation)을 따름. LIKE 'abc%' 검색 시 인덱스 사용 불가할 수 있음.     ◦ text_pattern_ops: 로케일을 무시하고 바이트 단위 비교. LIKE ‘abc%’ 검색 최적화에 필수.

5. 다중 컬럼 인덱스 (Multicolumn Indexes)

• 정렬 순서: 첫 번째 컬럼부터 순서대로 정렬됨. 따라서 **선행 컬럼(Leading column)**이 조건절에 포함되어야 인덱스를 효율적으로 사용 가능.     ◦ 예: 인덱스 (a, b)가 있을 때, WHERE a = 1은 효율적이지만 WHERE b = 1은 비효율적. • 정렬 방향: ASC, DESC, NULLS FIRST/LAST 지정 가능. 다중 컬럼 정렬 시 방향이 혼합된 쿼리(예: a ASC, b DESC)를 처리하려면 인덱스 생성 시에도 동일하게 방향을 지정해야 함.

6. 속성 (Properties)

속성지원 여부설명
Can Order✅유일하게 정렬된 데이터 반환 가능.
Can Unique✅유일하게 유니크 제약 조건(Unique/PK) 지원.
Can Multi-col✅다중 컬럼 지원.
Can Include✅INCLUDE 절(Covering Index) 지원.
Index-Only Scan✅리프 노드에 데이터가 있으므로 가능 (returnable=t).
Backward Scan✅양방향 리스트 덕분에 역방향 스캔 가능.
NULL✅NULL도 정렬되어 저장됨 (search_nulls=t).

Graph View

Backlinks