PostgreSQL 미드레벨 코딩 문제
새 면접Q. PostgreSQL의 Window Function에서 ROWS BETWEEN과 RANGE BETWEEN의 차이점을 설명하고, 각각 UNBOUNDED PRECEDING, CURRENT ROW, N PRECEDING 같은 프레임 절을 사용할 때의 동작 원리를 설명해주세요. 또한 이동 평균(Moving Average)을 계산할 때 어떤 프레임 절을 사용하는 것이 적절한지 설명해주세요.
ROWS BETWEEN은 물리적인 행의 개수를 기준으로 프레임을 정의하고, RANGE BETWEEN은 논리적인 값의 범위를 기준으로 프레임을 정의합니다. ROWS BETWEEN 3 PRECEDING AND CURRENT ROW는 현재 행을 포함한 최근 4개 행을 의미하며, RANGE BETWEEN은 ORDER BY 컬럼의 값이 같은 행들을 하나의 그룹으로 처리합니다. 이동 평균 계산 시에는 일반적으로 ROWS BETWEEN을 사용하여 정확한 행 개수를 기준으로 평균을 계산하며, UNBOUNDED PRECEDING은 파티션의 첫 행부터, CURRENT ROW는 현재 행까지를 의미합니다. RANGE BETWEEN은 날짜나 숫자 범위 기반 집계가 필요할 때 유용합니다.
- • ROWS BETWEEN은 물리적 행 개수, RANGE BETWEEN은 논리적 값 범위 기준
- • UNBOUNDED PRECEDING은 파티션 시작, CURRENT ROW는 현재 행, N PRECEDING은 N개 이전 행
- • 이동 평균은 일반적으로 ROWS BETWEEN으로 구현하며 정확한 행 개수 제어
- • RANGE BETWEEN은 ORDER BY 값이 같은 행들을 동일 그룹으로 처리
LAG, LEAD 함수와 Window Function의 프레임 절을 조합하여 사용할 때 성능상 차이가 있나요? 대용량 데이터에서 Window Function 최적화 전략은 무엇인가요?
Q. PostgreSQL에서 PL/pgSQL로 작성한 Stored Procedure와 Function의 차이점을 설명하고, 각각의 트랜잭션 제어 방식을 비교해주세요. 특히 Procedure에서만 가능한 COMMIT과 ROLLBACK 사용 시나리오와, Function에서 OUT 파라미터를 사용하는 경우와 RETURNS TABLE을 사용하는 경우의 차이점을 설명해주세요.
Stored Procedure는 CALL 문으로 호출하며 값을 반환하지 않고, Function은 SELECT 문이나 표현식에서 호출 가능하며 반드시 값을 반환합니다. Procedure는 내부에서 COMMIT과 ROLLBACK을 명시적으로 사용할 수 있어 배치 작업에서 부분 커밋이 가능하지만, Function은 호출한 트랜잭션 컨텍스트 내에서만 동작하여 독립적인 트랜잭션 제어가 불가능합니다. Function의 OUT 파라미터는 단일 레코드를 반환할 때 사용하고, RETURNS TABLE은 결과 집합을 반환할 때 사용하며 SELECT 문의 FROM 절에서 직접 사용 가능합니다. Procedure는 대용량 ETL 작업에서 중간 커밋을 통해 메모리를 절약하고 롤백 범위를 제한하는 데 유용합니다.
- • Procedure는 CALL로 호출하고 트랜잭션 제어 가능, Function은 SELECT에서 호출하고 값 반환 필수
- • Procedure는 내부 COMMIT/ROLLBACK 가능하여 배치 작업에 적합
- • Function은 호출 트랜잭션 컨텍스트 내에서만 동작하며 독립 트랜잭션 불가
- • OUT 파라미터는 단일 레코드, RETURNS TABLE은 결과 집합 반환
VOLATILE, STABLE, IMMUTABLE 속성이 Function의 성능과 쿼리 최적화에 미치는 영향은 무엇이며, 잘못 설정했을 때 발생할 수 있는 문제는 무엇인가요?
Q. PostgreSQL의 Array 타입과 JSONB 타입을 사용하여 다중 값을 저장하는 방식을 비교하고, 각각의 인덱싱 방법과 쿼리 성능 특성을 설명해주세요. 특히 Array의 ANY, ALL 연산자와 JSONB의 @>, ? 연산자를 사용한 검색 시 GIN 인덱스의 활용 방식과, 정규화된 1:N 관계 테이블과 비교한 장단점을 설명해주세요.
Array 타입은 동일한 타입의 순서가 있는 값들을 저장하며, JSONB는 키-값 구조의 계층적 데이터를 저장합니다. Array는 GIN 인덱스를 생성하여 ANY(array_column) = value 형태의 검색을 최적화할 수 있고, JSONB는 GIN 인덱스로 @> 연산자(포함 검색)와 ? 연산자(키 존재 검색)를 지원합니다. Array는 단순 태그나 카테고리 저장에 적합하고 쿼리가 직관적이지만, 복잡한 구조는 표현하기 어렵습니다. JSONB는 유연한 스키마가 필요할 때 유용하지만 타입 안정성이 낮고, 정규화된 테이블에 비해 외래 키 제약이나 조인 최적화가 제한적입니다. 정규화된 1:N 테이블은 데이터 무결성과 복잡한 조인이 필요할 때 우선 고려해야 합니다.
- • Array는 동일 타입 순서 값, JSONB는 계층적 키-값 구조 저장
- • 둘 다 GIN 인덱스로 검색 최적화 가능하며 각각 특화된 연산자 지원
- • Array는 단순 다중 값에 적합, JSONB는 유연한 스키마에 적합
- • 정규화 테이블은 무결성과 복잡한 조인이 필요할 때 우선
JSONB 컬럼에 저장된 중첩된 배열 데이터를 효율적으로 검색하고 집계하는 방법은 무엇이며, jsonb_array_elements 같은 함수를 사용할 때 성능 고려사항은 무엇인가요?
Q. 조직도나 카테고리 계층 구조를 표현하는 테이블에서 WITH RECURSIVE를 사용한 계층 쿼리를 작성할 때, 무한 루프를 방지하는 방법과 깊이 제한을 두는 전략을 설명해주세요. 또한 상향 탐색(자식에서 부모로)과 하향 탐색(부모에서 자식으로)의 구현 차이와, 대용량 계층 데이터에서 Recursive CTE의 성능 한계와 대안을 제시해주세요.
WITH RECURSIVE는 초기 쿼리(base case)와 재귀 쿼리(recursive case)로 구성되며, 무한 루프 방지를 위해 ARRAY를 사용해 방문한 노드를 추적하거나 최대 깊이를 제한하는 조건을 추가합니다. 상향 탐색은 자식 노드에서 시작해 parent_id를 따라가며, 하향 탐색은 루트에서 시작해 id = parent_id 조건으로 자식을 찾습니다. 깊이 제한은 재귀 부분에 depth 컬럼을 추가하고 WHERE depth < max_depth 조건으로 구현합니다. 대용량 계층에서는 Recursive CTE가 성능 문제를 일으킬 수 있으므로, Materialized Path(경로를 문자열로 저장), Nested Set(좌우 값으로 범위 표현), 또는 ltree 확장을 사용하는 것이 효율적입니다.
- • WITH RECURSIVE는 base case와 recursive case로 구성
- • 무한 루프 방지는 방문 노드 배열 추적 또는 깊이 제한으로 구현
- • 상향은 parent_id 추적, 하향은 자식 노드 탐색 방식
- • 대용량에서는 Materialized Path, Nested Set, ltree 확장 고려
ltree 확장을 사용한 계층 구조와 Recursive CTE를 비교했을 때, 각각의 INSERT/UPDATE/DELETE 성능과 쿼리 성능 트레이드오프는 무엇인가요?
Q. 복잡한 서브쿼리를 포함한 SELECT 문을 최적화할 때, Correlated Subquery를 JOIN이나 Window Function으로 리팩토링하는 전략을 설명해주세요. 특히 EXISTS, IN, NOT EXISTS, NOT IN 서브쿼리의 성능 차이와, NULL 값이 포함된 경우 NOT IN의 예상치 못한 동작을 설명하고, 이를 안전하게 처리하는 방법을 제시해주세요.
Correlated Subquery는 외부 쿼리의 각 행마다 서브쿼리가 실행되어 성능이 저하되므로, LEFT JOIN과 NULL 체크 또는 Window Function으로 변환하는 것이 효율적입니다. EXISTS는 첫 번째 매칭 행을 찾으면 즉시 중단하여 IN보다 빠르고, NOT EXISTS는 안티 조인으로 최적화됩니다. NOT IN은 서브쿼리 결과에 NULL이 있으면 항상 FALSE를 반환하는 의도치 않은 동작을 하므로, NOT EXISTS나 LEFT JOIN + IS NULL 패턴으로 대체해야 합니다. IN은 작은 결과 집합에 적합하고, EXISTS는 대용량 데이터나 조기 종료가 가능한 경우에 유리하며, JOIN으로 변환하면 쿼리 플래너가 더 다양한 최적화 전략을 선택할 수 있습니다.
- • Correlated Subquery는 행마다 실행되어 느리므로 JOIN이나 Window Function으로 변환
- • EXISTS는 조기 종료로 IN보다 빠르고, NOT EXISTS는 안티 조인 최적화
- • NOT IN은 NULL 포함 시 예상치 못한 결과, NOT EXISTS나 LEFT JOIN + IS NULL 사용
- • JOIN 변환 시 쿼리 플래너가 더 다양한 최적화 전략 선택 가능
LATERAL JOIN의 개념과 일반 JOIN과의 차이점을 설명하고, Correlated Subquery를 LATERAL JOIN으로 변환하면 성능이 개선되는 이유는 무엇인가요?
아직 댓글이 없습니다. 첫 번째 댓글을 남겨보세요!