PostgreSQL 시니어 CS 기초 면접
새 면접Q. PostgreSQL의 WAL(Write-Ahead Logging) 메커니즘의 동작 원리를 설명하고, checkpoint와 WAL segment의 관계를 설명해주세요. 또한 checkpoint_timeout, max_wal_size 파라미터가 성능과 복구 시간에 미치는 영향을 분석하고, 대용량 배치 작업 시 WAL 관련 최적화 전략을 제시해주세요.
WAL은 데이터 변경 전에 로그를 먼저 디스크에 기록하여 장애 시 복구를 보장하는 메커니즘입니다. Checkpoint는 메모리의 더티 페이지를 디스크에 동기화하는 시점으로, 이후의 WAL만 복구 시 재생하면 됩니다. checkpoint_timeout이 짧으면 I/O 부하가 증가하지만 복구 시간이 단축되고, max_wal_size가 크면 checkpoint 빈도가 줄어 성능은 향상되지만 디스크 공간과 복구 시간이 증가합니다. 대용량 배치 작업 시에는 wal_level을 minimal로 설정하거나, UNLOGGED 테이블 사용, 또는 작업 전후로 checkpoint_timeout을 조정하여 WAL 오버헤드를 최소화할 수 있습니다.
- • WAL은 데이터 변경 전 로그를 먼저 기록하여 ACID 보장 및 장애 복구를 지원
- • Checkpoint는 더티 페이지를 디스크에 동기화하며 복구 시작점을 결정
- • checkpoint_timeout과 max_wal_size는 성능과 복구 시간의 트레이드오프 관계
- • 배치 작업 시 wal_level 조정, UNLOGGED 테이블, 또는 파라미터 튜닝으로 최적화
full_page_writes 파라미터의 역할과 이를 비활성화했을 때의 위험성, 그리고 ZFS나 Copy-on-Write 파일시스템에서는 왜 이를 비활성화할 수 있는지 설명해주세요.
Q. PostgreSQL의 Advisory Lock과 일반적인 Row-level Lock의 차이점을 설명하고, 분산 환경에서 크론 작업이나 배치 작업의 중복 실행을 방지하기 위해 Advisory Lock을 사용하는 구체적인 방법과 주의사항을 제시해주세요. 또한 pg_advisory_lock과 pg_try_advisory_lock의 차이와 적절한 사용 시나리오를 설명해주세요.
Advisory Lock은 애플리케이션 레벨에서 정의한 임의의 정수 키에 대한 잠금으로, 특정 테이블이나 행과 무관하게 논리적 리소스를 보호할 수 있습니다. Row-level Lock은 실제 데이터 행에 대한 잠금이며 트랜잭션과 자동으로 연동됩니다. 크론 작업 중복 방지를 위해서는 작업 시작 시 pg_try_advisory_lock으로 특정 키를 획득 시도하고, 실패하면 이미 실행 중이므로 종료하는 방식으로 구현합니다. pg_advisory_lock은 잠금 획득까지 대기하고, pg_try_advisory_lock은 즉시 성공/실패를 반환하므로, 중복 실행 방지에는 후자가 적합합니다. 주의사항으로는 세션 레벨 잠금은 명시적으로 해제하거나 연결 종료 시까지 유지되므로 반드시 unlock을 호출해야 합니다.
- • Advisory Lock은 애플리케이션 정의 키에 대한 논리적 잠금으로 데이터와 독립적
- • pg_try_advisory_lock은 즉시 반환되어 중복 실행 방지에 적합
- • 세션 레벨 잠금은 명시적 해제 필요, 트랜잭션 레벨 잠금은 자동 해제
- • 분산 환경에서 동일 DB를 공유하는 경우 효과적인 동시성 제어 메커니즘
Advisory Lock이 데이터베이스 재시작이나 연결 풀 환경에서 예상치 못한 동작을 할 수 있는 시나리오와, 이를 방지하기 위한 베스트 프랙티스를 설명해주세요.
Q. PostgreSQL의 클라이언트-서버 통신 프로토콜에서 사용되는 메시지 기반 프로토콜의 특징을 설명하고, Extended Query Protocol과 Simple Query Protocol의 차이점을 비교해주세요. 또한 Prepared Statement를 사용할 때의 성능상 이점과 Plan Cache 관련 주의사항을 설명해주세요.
PostgreSQL은 TCP/IP 기반의 메시지 지향 프로토콜을 사용하며, 각 메시지는 타입 코드와 길이를 포함합니다. Simple Query Protocol은 쿼리 문자열을 한 번에 전송하고 실행하는 단순한 방식이고, Extended Query Protocol은 Parse-Bind-Execute 단계로 분리되어 파라미터 바인딩과 Prepared Statement를 지원합니다. Prepared Statement는 쿼리 파싱과 플랜 생성을 재사용하여 CPU 오버헤드를 줄이고, 파라미터 타입이 명확하여 SQL 인젝션 방지에도 효과적입니다. 다만 PostgreSQL은 5번 실행 후 Generic Plan으로 전환하는데, 파라미터 분포가 불균등한 경우 Custom Plan보다 비효율적일 수 있으므로 plan_cache_mode 설정이나 재prepare 전략이 필요합니다.
- • Extended Query Protocol은 Parse-Bind-Execute로 분리되어 파라미터 바인딩 지원
- • Prepared Statement는 파싱 재사용으로 성능 향상 및 SQL 인젝션 방지
- • 5회 실행 후 Generic Plan으로 전환되며 파라미터 분포에 따라 성능 차이 발생
- • plan_cache_mode로 플랜 캐싱 전략 제어 가능
Connection Pooler 환경에서 Prepared Statement의 동작 방식과 제약사항, 그리고 PgBouncer의 Transaction Pooling 모드에서 Prepared Statement를 사용할 때 발생할 수 있는 문제를 설명해주세요.
Q. PostgreSQL의 Shared Buffers, OS Page Cache, 그리고 애플리케이션 메모리의 관계를 설명하고, shared_buffers 크기를 결정할 때 고려해야 할 요소들을 제시해주세요. 또한 Linux의 vm.swappiness와 transparent_hugepage 설정이 PostgreSQL 성능에 미치는 영향과 권장 설정값을 설명해주세요.
Shared Buffers는 PostgreSQL 프로세스들이 공유하는 메모리 영역으로 데이터 페이지를 캐싱하고, OS Page Cache는 파일 시스템 레벨에서 디스크 I/O를 캐싱합니다. PostgreSQL은 이중 캐싱 구조로 Shared Buffers를 먼저 확인하고 없으면 OS 캐시를 활용합니다. shared_buffers는 전체 메모리의 25% 정도가 일반적이며, 너무 크면 체크포인트 시 I/O 버스트가 발생하고 관리 오버헤드가 증가합니다. vm.swappiness는 0 또는 1로 설정하여 스왑을 최소화해야 하며, transparent_hugepage는 latency spike를 유발하므로 'never'로 비활성화하는 것이 권장됩니다. work_mem, maintenance_work_mem 등 세션별 메모리도 고려하여 OOM을 방지해야 합니다.
- • Shared Buffers와 OS Page Cache의 이중 캐싱 구조로 메모리 활용
- • shared_buffers는 전체 메모리의 25% 정도, 너무 크면 checkpoint I/O 증가
- • vm.swappiness=0-1로 스왑 최소화, transparent_hugepage=never로 설정
- • 세션별 메모리(work_mem 등)를 고려한 전체 메모리 설계 필요
huge_pages 설정을 활성화했을 때의 성능 이점과 메모리 단편화 감소 효과, 그리고 컨테이너 환경에서 huge_pages를 사용할 때의 제약사항을 설명해주세요.
Q. PostgreSQL의 쿼리 플래너가 조인 순서를 결정하는 알고리즘과 비용 계산 모델을 설명하고, Nested Loop Join, Hash Join, Merge Join의 내부 동작 원리와 각각이 선택되는 조건을 비교해주세요. 또한 join_collapse_limit과 from_collapse_limit 파라미터가 복잡한 다중 조인 쿼리의 플랜에 미치는 영향을 설명해주세요.
PostgreSQL 플래너는 동적 프로그래밍 기반으로 가능한 조인 순서를 탐색하며, 각 경로의 비용(CPU 비용 + I/O 비용)을 추정하여 최소 비용 플랜을 선택합니다. Nested Loop Join은 외부 테이블의 각 행마다 내부 테이블을 탐색하며 작은 데이터셋과 인덱스가 있을 때 효율적입니다. Hash Join은 작은 테이블로 해시 테이블을 생성하고 큰 테이블을 스캔하며 등가 조인에 적합하고 메모리 의존적입니다. Merge Join은 양쪽이 정렬된 상태에서 순차 병합하며 정렬된 인덱스가 있거나 정렬 비용이 낮을 때 선택됩니다. join_collapse_limit은 GEQO로 전환되는 조인 개수 임계값으로, 기본값 8을 초과하면 휴리스틱 알고리즘을 사용하여 플랜 탐색 시간을 단축하지만 최적 플랜을 놓칠 수 있습니다.
- • 플래너는 동적 프로그래밍으로 조인 순서 탐색 및 비용 기반 최적화 수행
- • Nested Loop는 소규모+인덱스, Hash Join은 등가조인+메모리, Merge Join은 정렬된 데이터에 적합
- • join_collapse_limit 초과 시 GEQO 휴리스틱 알고리즘으로 전환
- • 통계 정확도(ANALYZE)와 cost 파라미터가 플랜 품질에 결정적 영향
GEQO(Genetic Query Optimizer)의 동작 원리와 유전 알고리즘이 쿼리 최적화에 적용되는 방식, 그리고 geqo_threshold와 geqo_effort 파라미터를 조정하여 복잡한 쿼리의 플랜 품질과 최적화 시간을 균형있게 조절하는 전략을 설명해주세요.
아직 댓글이 없습니다. 첫 번째 댓글을 남겨보세요!