PostgreSQL 미드레벨 기술면접
새 면접Q. 운영 중인 PostgreSQL 데이터베이스에서 특정 테이블의 쿼리 성능이 점진적으로 느려지는 현상이 발생했습니다. VACUUM과 ANALYZE의 역할을 설명하고, Autovacuum이 제대로 작동하지 않을 때 발생할 수 있는 문제와 튜닝 방법을 설명해주세요.
VACUUM은 DELETE/UPDATE로 생성된 dead tuple을 정리하여 디스크 공간을 재사용 가능하게 만들고, ANALYZE는 테이블 통계 정보를 수집하여 쿼리 플래너가 최적의 실행 계획을 세우도록 돕습니다. Autovacuum이 미작동하면 테이블 bloat로 인한 성능 저하, 트랜잭션 ID wraparound 위험, 부정확한 통계로 인한 비효율적 쿼리 플랜이 발생합니다. 튜닝은 autovacuum_vacuum_scale_factor와 autovacuum_vacuum_threshold를 조정하여 더 자주 실행되도록 하거나, 특정 테이블에 대해 pg_stat_user_tables를 모니터링하며 수동 VACUUM을 고려할 수 있습니다.
- • VACUUM은 dead tuple 정리 및 공간 재사용
- • ANALYZE는 통계 정보 수집으로 쿼리 최적화 지원
- • Autovacuum 미작동 시 bloat, wraparound, 성능 저하 발생
- • autovacuum 파라미터 튜닝으로 실행 빈도 조정
VACUUM FULL과 일반 VACUUM의 차이점과 각각을 사용해야 하는 상황을 설명해주세요. 또한 운영 환경에서 VACUUM FULL을 실행할 때 주의해야 할 점은 무엇인가요?
Q. 사용자 검색 기능에서 이름, 이메일, 전화번호 등 여러 컬럼을 조합하여 검색하는 쿼리가 자주 발생합니다. B-tree, GiST, GIN 인덱스의 특징을 비교하고, 부분 문자열 검색(LIKE '%keyword%')과 전문 검색(Full-Text Search)을 지원하기 위한 최적의 인덱스 전략을 설계해주세요.
B-tree는 등호, 범위 검색에 최적화되어 있고, GiST는 기하학적 데이터나 범위 타입에 적합하며, GIN은 배열, JSONB, 전문 검색 등 다중 값 검색에 효율적입니다. 부분 문자열 검색은 pg_trgm 확장의 GIN 인덱스를 사용하여 trigram 기반 유사도 검색을 구현하고, 전문 검색은 tsvector 타입과 GIN 인덱스를 활용하여 언어별 stemming과 ranking을 지원합니다. 복합 검색 조건이 많다면 각 컬럼에 개별 GIN 인덱스를 생성하거나, 검색 패턴에 따라 composite 인덱스와 partial 인덱스를 조합하여 사용하는 것이 효과적입니다.
- • B-tree는 정렬/범위, GiST는 기하/범위, GIN은 다중값 검색에 적합
- • pg_trgm과 GIN 인덱스로 부분 문자열 검색 최적화
- • tsvector와 GIN으로 전문 검색 구현
- • 검색 패턴에 따라 인덱스 타입과 조합 전략 선택
GIN 인덱스의 fastupdate 옵션과 pending list의 동작 원리를 설명하고, 쓰기 성능과 읽기 성능 사이의 트레이드오프를 어떻게 조정하시겠습니까?
Q. 마이크로서비스 아키텍처에서 여러 서비스가 PostgreSQL에 접속할 때 connection 관리 전략을 설계해야 합니다. PostgreSQL의 connection 모델과 한계를 설명하고, PgBouncer 같은 connection pooler를 사용할 때 transaction pooling과 session pooling의 차이점 및 각각의 적합한 사용 사례를 설명해주세요.
PostgreSQL은 각 connection마다 별도의 백엔드 프로세스를 생성하는 process-per-connection 모델로, connection 수가 많아지면 메모리 사용량과 context switching 비용이 증가합니다. Transaction pooling은 트랜잭션 단위로 connection을 재사용하여 높은 동시성을 지원하지만 prepared statements와 session-level 설정을 사용할 수 없고, session pooling은 클라이언트 세션 동안 connection을 유지하여 모든 PostgreSQL 기능을 사용할 수 있지만 pooling 효율이 낮습니다. 대부분의 stateless API는 transaction pooling으로 처리량을 극대화하고, temporary table이나 advisory lock이 필요한 경우 session pooling을 선택합니다.
- • PostgreSQL은 process-per-connection 모델로 connection 수 제한
- • Transaction pooling은 높은 동시성, 일부 기능 제약
- • Session pooling은 모든 기능 지원, 낮은 pooling 효율
- • 워크로드 특성에 따라 pooling 모드 선택
max_connections, shared_buffers, work_mem 파라미터 간의 관계를 설명하고, connection 수를 늘릴 때 함께 조정해야 하는 메모리 관련 설정은 무엇인가요?
Q. 복잡한 JOIN과 서브쿼리를 포함한 쿼리의 성능 문제를 분석해야 합니다. EXPLAIN과 EXPLAIN ANALYZE의 차이점을 설명하고, 실행 계획에서 Seq Scan, Index Scan, Bitmap Index Scan의 차이와 각각이 선택되는 조건을 설명해주세요. 또한 쿼리 플래너가 잘못된 실행 계획을 선택했을 때 개선 방법을 제시해주세요.
EXPLAIN은 쿼리 플래너가 예상한 실행 계획과 비용 추정치를 보여주고, EXPLAIN ANALYZE는 실제로 쿼리를 실행하여 실제 수행 시간과 row 수를 측정합니다. Seq Scan은 테이블 전체를 읽을 때, Index Scan은 소수의 행을 정확히 찾을 때, Bitmap Index Scan은 중간 규모의 결과를 효율적으로 가져올 때 선택됩니다. 잘못된 계획 선택 시 ANALYZE로 통계 갱신, random_page_cost/seq_page_cost 조정, 필요시 특정 쿼리에 힌트 대신 CTE나 서브쿼리 구조 변경, 또는 enable_seqscan 같은 플래너 옵션을 임시로 조정하여 원하는 계획을 유도할 수 있습니다.
- • EXPLAIN은 예상 계획, EXPLAIN ANALYZE는 실제 실행 결과
- • Seq/Index/Bitmap Scan은 결과 크기와 선택도에 따라 선택
- • 통계 갱신과 cost 파라미터 조정으로 플래너 개선
- • 쿼리 구조 변경이나 플래너 옵션으로 실행 계획 유도
Nested Loop, Hash Join, Merge Join의 동작 원리와 각각이 효율적인 시나리오를 설명하고, join_collapse_limit 파라미터가 복잡한 JOIN 쿼리에 미치는 영향을 설명해주세요.
Q. 전자상거래 시스템에서 상품의 속성이 카테고리마다 다르고 자주 변경되어 유연한 스키마가 필요합니다. PostgreSQL의 JSONB 타입을 활용한 설계 전략과, JSONB 데이터를 효율적으로 쿼리하기 위한 인덱싱 방법, 그리고 정규화된 관계형 설계와 비교한 장단점을 설명해주세요.
JSONB는 바이너리 형태로 저장되어 JSON보다 빠른 조회와 인덱싱을 지원하며, 스키마 변경 없이 유연한 데이터 구조를 저장할 수 있습니다. GIN 인덱스를 사용하여 JSONB 내부 키나 값에 대한 검색을 최적화하고, 특정 경로에 대해서는 expression 인덱스를 생성하여 성능을 향상시킬 수 있습니다. 장점은 스키마 유연성과 개발 속도이지만, 단점은 데이터 무결성 검증이 어렵고, 복잡한 조인이나 집계 쿼리 성능이 정규화된 테이블보다 떨어질 수 있습니다. 핵심 검색 필드는 별도 컬럼으로 추출하고 나머지를 JSONB에 저장하는 하이브리드 접근이 효과적입니다.
- • JSONB는 바이너리 저장으로 빠른 조회와 인덱싱 지원
- • GIN 인덱스와 expression 인덱스로 쿼리 최적화
- • 스키마 유연성 vs 무결성 및 쿼리 성능 트레이드오프
- • 하이브리드 접근: 핵심 필드는 컬럼, 나머지는 JSONB
JSONB의 containment 연산자(@>, <@)와 existence 연산자(?, ?&, ?|)의 차이를 설명하고, 각각에 적합한 GIN 인덱스 operator class(jsonb_ops vs jsonb_path_ops)를 선택하는 기준을 설명해주세요.
아직 댓글이 없습니다. 첫 번째 댓글을 남겨보세요!