PostgreSQL 미드레벨 기술면접
새 면접Q. PostgreSQL에서 Savepoint의 개념과 사용 목적을 설명하고, 대용량 배치 작업에서 Savepoint를 활용하여 부분 롤백을 구현하는 전략을 제시해주세요. 또한 Savepoint 사용 시 성능상 주의해야 할 점은 무엇인가요?
Savepoint는 트랜잭션 내에서 중간 체크포인트를 설정하여 전체 트랜잭션을 롤백하지 않고 특정 지점까지만 롤백할 수 있는 기능입니다. 대용량 배치 작업에서는 1000건마다 Savepoint를 설정하여, 오류 발생 시 마지막 Savepoint 이후의 작업만 재시도할 수 있습니다. Savepoint를 과도하게 사용하면 트랜잭션 메타데이터가 증가하여 메모리 사용량이 늘어나고, 긴 트랜잭션으로 인해 MVCC bloat가 발생할 수 있으므로 적절한 단위로 커밋하는 것이 중요합니다.
- • 트랜잭션 내 부분 롤백 기능
- • 배치 작업에서 오류 복구 전략으로 활용
- • 과도한 사용 시 메모리 증가 및 bloat 발생 가능
- • 적절한 배치 크기로 커밋 분할 필요
Savepoint와 Nested Transaction의 차이점은 무엇이며, PostgreSQL은 진정한 의미의 Nested Transaction을 지원하나요?
Q. PostgreSQL의 Partial Index(부분 인덱스)와 Expression Index(표현식 인덱스)의 개념을 설명하고, 각각의 실무 활용 사례를 제시해주세요. 또한 이러한 특수 인덱스를 사용할 때 쿼리 플래너가 인덱스를 활용하지 못하는 경우와 해결 방법을 설명해주세요.
Partial Index는 WHERE 조건을 만족하는 일부 행만 인덱싱하여 인덱스 크기를 줄이고 성능을 향상시킵니다. 예를 들어 status='active'인 주문만 자주 조회한다면 CREATE INDEX ON orders(created_at) WHERE status='active'로 생성할 수 있습니다. Expression Index는 함수나 표현식의 결과를 인덱싱하며, LOWER(email)로 대소문자 무시 검색이나 (price * quantity)로 계산된 값 검색에 활용됩니다. 쿼리의 WHERE 절이 인덱스 정의와 정확히 일치하지 않으면 플래너가 인덱스를 사용하지 못하므로, 쿼리 패턴을 분석하여 인덱스를 설계하고 EXPLAIN으로 검증해야 합니다.
- • Partial Index는 조건부 인덱싱으로 크기 절약
- • Expression Index는 함수/표현식 결과 인덱싱
- • 쿼리 조건이 인덱스 정의와 일치해야 활용 가능
- • LOWER(), COALESCE() 등 함수 기반 검색에 유용
Expression Index를 사용할 때 Immutable, Stable, Volatile 함수의 차이가 인덱스 생성에 어떤 영향을 미치나요?
Q. PostgreSQL의 Advisory Lock의 개념과 일반 테이블 Lock과의 차이점을 설명하고, 분산 작업 큐 시스템에서 중복 작업 처리를 방지하기 위해 Advisory Lock을 어떻게 활용할 수 있는지 설명해주세요. 또한 Advisory Lock 사용 시 주의사항은 무엇인가요?
Advisory Lock은 애플리케이션 레벨에서 정의한 정수 ID를 기반으로 동작하는 사용자 정의 잠금 메커니즘으로, 테이블이나 행이 아닌 논리적 리소스를 잠급니다. 일반 Lock과 달리 PostgreSQL이 자동으로 관리하지 않으며 애플리케이션이 명시적으로 획득하고 해제해야 합니다. 작업 큐에서 pg_try_advisory_lock(job_id)를 사용하여 특정 작업 ID에 대한 잠금을 시도하고, 성공한 워커만 작업을 처리하도록 구현할 수 있습니다. 세션이 종료되면 자동 해제되지만, 명시적으로 pg_advisory_unlock()을 호출하지 않으면 세션이 살아있는 동안 잠금이 유지되므로 반드시 해제 로직을 구현해야 합니다.
- • 애플리케이션 정의 논리적 리소스 잠금
- • 테이블 Lock 없이 동시성 제어 가능
- • 분산 작업 중복 처리 방지에 활용
- • 명시적 해제 필요, 세션 종료 시 자동 해제
Advisory Lock의 Session Level Lock과 Transaction Level Lock의 차이점과 각각의 사용 사례를 설명해주세요.
Q. PostgreSQL의 SERIAL, IDENTITY, UUID 타입을 Primary Key로 사용할 때 각각의 장단점을 비교하고, 마이크로서비스 환경에서 여러 서비스가 독립적으로 데이터를 생성할 때 어떤 타입을 선택하시겠습니까? 또한 UUID를 사용할 때 성능 저하를 최소화하는 방법을 설명해주세요.
SERIAL/IDENTITY는 순차적인 정수로 인덱스 성능이 우수하지만 분산 환경에서 충돌 가능성이 있고 데이터 규모를 유추할 수 있습니다. UUID는 전역적으로 고유하여 분산 시스템에 적합하고 보안성이 높지만, 16바이트로 크기가 크고 무작위 값으로 인해 인덱스 단편화가 발생합니다. 마이크로서비스 환경에서는 UUID가 적합하며, UUID v1(시간 기반) 또는 ULID를 사용하면 순차성이 있어 인덱스 성능을 개선할 수 있습니다. 또한 UUID를 bytea 타입으로 저장하거나 적절한 fillfactor 설정으로 페이지 분할을 줄일 수 있습니다.
- • SERIAL은 순차적이지만 분산 환경 부적합
- • UUID는 전역 고유성 보장, 분산 시스템 적합
- • UUID 무작위성으로 인한 인덱스 단편화
- • UUID v1, ULID 등 시간 기반 UUID로 성능 개선
대용량 테이블에서 UUID Primary Key를 사용할 때 Clustered Index와 Non-Clustered Index의 관점에서 성능 영향을 설명해주세요.
Q. PostgreSQL의 CTE(Common Table Expression)를 사용할 때 WITH 절과 WITH RECURSIVE 절의 차이를 설명하고, CTE Materialization과 Inlining의 개념을 설명해주세요. 또한 PostgreSQL 12 이후 버전에서 CTE 최적화가 어떻게 변경되었으며, 성능을 고려한 CTE 사용 전략을 제시해주세요.
WITH 절은 일반적인 서브쿼리를 명명하여 재사용하고, WITH RECURSIVE는 계층 구조나 그래프 탐색 같은 재귀 쿼리를 구현합니다. CTE Materialization은 CTE 결과를 임시 테이블처럼 한 번만 실행하여 저장하고, Inlining은 CTE를 메인 쿼리에 통합하여 최적화합니다. PostgreSQL 12 이전에는 CTE가 항상 Materialized 되어 최적화 장벽이 되었지만, 12 이후에는 기본적으로 Inline되며 MATERIALIZED/NOT MATERIALIZED 힌트로 제어할 수 있습니다. 중복 실행 방지가 필요하면 WITH ... AS MATERIALIZED를, 조인 최적화가 필요하면 Inline을 활용하거나 서브쿼리로 대체하는 것이 좋습니다.
- • WITH RECURSIVE는 재귀 쿼리 구현
- • Materialization은 한 번 실행 후 저장
- • PostgreSQL 12 이후 기본 Inline으로 변경
- • MATERIALIZED 힌트로 명시적 제어 가능
조직도 테이블에서 특정 직원의 모든 하위 직원을 조회하는 재귀 쿼리를 설계할 때, 성능을 위해 고려해야 할 인덱스 전략과 재귀 깊이 제한 방법을 설명해주세요.
아직 댓글이 없습니다. 첫 번째 댓글을 남겨보세요!