PostgreSQL 주니어 코딩 면접
새 면접Q. PostgreSQL에서 윈도우 함수(Window Function)의 개념을 설명하고, ROW_NUMBER(), RANK(), DENSE_RANK()의 차이점을 예시와 함께 설명해주세요. 각 함수는 동점자를 어떻게 처리하나요?
윈도우 함수는 결과 집합의 행들에 대해 집계나 순위를 계산하되 GROUP BY처럼 행을 축소하지 않고 각 행을 유지하는 함수입니다. ROW_NUMBER()는 동점자와 관계없이 순차적으로 1, 2, 3을 부여하고, RANK()는 동점자에게 같은 순위를 부여한 후 다음 순위를 건너뛰며(1, 2, 2, 4), DENSE_RANK()는 동점자에게 같은 순위를 부여하되 다음 순위를 건너뛰지 않습니다(1, 2, 2, 3). 예를 들어 점수가 95, 90, 90, 85인 경우 ROW_NUMBER()는 1,2,3,4, RANK()는 1,2,2,4, DENSE_RANK()는 1,2,2,3을 반환합니다.
- • 윈도우 함수는 행을 축소하지 않고 각 행에 대해 계산을 수행
- • ROW_NUMBER()는 동점자 무시하고 연속된 번호 부여
- • RANK()는 동점자에게 같은 순위 부여 후 순위 건너뛰기
- • DENSE_RANK()는 동점자에게 같은 순위 부여하되 순위 건너뛰지 않음
PARTITION BY와 ORDER BY를 윈도우 함수에서 사용할 때의 역할과 차이점은 무엇인가요?
Q. PostgreSQL에서 TIMESTAMP, TIMESTAMPTZ(TIMESTAMP WITH TIME ZONE), DATE 타입의 차이점을 설명하고, 글로벌 서비스를 개발할 때 어떤 타입을 선택해야 하나요?
DATE는 날짜만 저장하고(2024-01-01), TIMESTAMP는 날짜와 시간을 저장하되 타임존 정보를 저장하지 않으며(2024-01-01 14:30:00), TIMESTAMPTZ는 날짜와 시간을 타임존 정보와 함께 저장합니다. TIMESTAMPTZ는 입력 시 타임존을 UTC로 변환하여 저장하고 조회 시 세션의 타임존으로 변환하여 반환합니다. 글로벌 서비스에서는 사용자가 여러 타임존에 분산되어 있으므로 TIMESTAMPTZ를 사용해야 정확한 시간 정보를 관리할 수 있습니다.
- • DATE는 날짜만, TIMESTAMP는 날짜+시간(타임존 없음)
- • TIMESTAMPTZ는 타임존 정보를 포함하여 UTC로 저장
- • 글로벌 서비스에서는 TIMESTAMPTZ 사용 권장
- • TIMESTAMPTZ는 조회 시 세션 타임존으로 자동 변환
NOW()와 CURRENT_TIMESTAMP의 차이점은 무엇이며, 두 함수 모두 TIMESTAMPTZ를 반환하나요?
Q. PostgreSQL에서 복합 인덱스(Composite Index)를 생성할 때 컬럼 순서가 중요한 이유를 설명하고, WHERE 절에서 name='John' AND age=30 조건으로 자주 조회하는 경우 어떤 순서로 인덱스를 생성해야 효율적인지 설명해주세요.
복합 인덱스는 B-tree 구조로 첫 번째 컬럼을 기준으로 정렬하고, 같은 값 내에서 두 번째 컬럼을 정렬하는 방식입니다. 따라서 첫 번째 컬럼만으로도 인덱스를 활용할 수 있지만, 두 번째 컬럼만으로는 인덱스를 효율적으로 사용할 수 없습니다. WHERE 절에서 name과 age를 모두 사용하는 경우, 카디널리티(고유값의 수)가 높은 컬럼을 먼저 배치하는 것이 일반적으로 효율적이므로, name의 카디널리티가 age보다 높다면 CREATE INDEX idx_name_age ON users(name, age)로 생성해야 합니다. 또한 name만으로 조회하는 쿼리도 이 인덱스를 활용할 수 있습니다.
- • 복합 인덱스는 첫 번째 컬럼 기준으로 정렬 후 두 번째 컬럼 정렬
- • 첫 번째 컬럼만으로도 인덱스 활용 가능하지만 두 번째 컬럼만으로는 비효율적
- • 카디널리티가 높은 컬럼을 앞에 배치하는 것이 일반적으로 효율적
- • 쿼리 패턴을 고려하여 자주 단독으로 사용되는 컬럼을 앞에 배치
WHERE name='John' 조건만 사용하는 쿼리와 WHERE age=30 조건만 사용하는 쿼리가 있을 때, (name, age) 인덱스는 각각 어떻게 활용되나요?
Q. PostgreSQL에서 COUNT(*), COUNT(column_name), COUNT(DISTINCT column_name)의 차이점을 설명하고, NULL 값이 포함된 컬럼에 대해 각각 어떻게 동작하나요?
COUNT(*)는 NULL을 포함한 모든 행의 개수를 반환하고, COUNT(column_name)은 해당 컬럼에서 NULL이 아닌 값의 개수만 반환합니다. COUNT(DISTINCT column_name)은 해당 컬럼에서 NULL을 제외한 고유한 값의 개수를 반환합니다. 예를 들어 5개 행에서 특정 컬럼에 값이 1, 2, 2, NULL, 3인 경우, COUNT(*)는 5, COUNT(column_name)은 4, COUNT(DISTINCT column_name)은 3을 반환합니다. 따라서 전체 행 수를 세려면 COUNT(*)를, 유효한 값의 개수를 세려면 COUNT(column_name)을 사용해야 합니다.
- • COUNT(*)는 NULL 포함 모든 행 개수 반환
- • COUNT(column_name)은 NULL 제외한 값의 개수 반환
- • COUNT(DISTINCT column_name)은 NULL 제외한 고유 값의 개수 반환
- • NULL 처리 방식의 차이를 이해하고 용도에 맞게 선택
SUM(column_name)과 AVG(column_name) 함수도 NULL 값을 어떻게 처리하나요?
Q. PostgreSQL에서 INSERT ... ON CONFLICT 구문(UPSERT)의 동작 원리를 설명하고, 이를 사용하여 중복 키 발생 시 업데이트를 수행하는 방법을 설명해주세요. 일반적인 INSERT와 UPDATE를 따로 실행하는 것과 비교했을 때 어떤 장점이 있나요?
INSERT ... ON CONFLICT 구문은 INSERT 시도 중 제약조건 위반(주로 PRIMARY KEY나 UNIQUE 제약)이 발생하면 DO NOTHING으로 무시하거나 DO UPDATE SET으로 업데이트를 수행할 수 있는 기능입니다. 예를 들어 INSERT INTO users (id, name) VALUES (1, 'John') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name은 id가 중복되면 name을 업데이트합니다. 별도로 SELECT로 존재 여부를 확인 후 INSERT나 UPDATE를 실행하는 방식과 비교하면, 단일 쿼리로 처리되어 네트워크 왕복이 줄고, 동시성 환경에서 경쟁 조건(race condition)을 방지하며, 트랜잭션 오버헤드가 감소하는 장점이 있습니다.
- • INSERT 시도 중 제약조건 위반 시 DO NOTHING 또는 DO UPDATE 실행
- • EXCLUDED 키워드로 삽입하려던 값을 참조 가능
- • 단일 쿼리로 처리되어 네트워크 왕복 감소
- • 동시성 환경에서 경쟁 조건 방지 및 원자적 연산 보장
ON CONFLICT 절에서 특정 컬럼을 지정하는 것과 ON CONSTRAINT를 사용하는 것의 차이점은 무엇인가요?
아직 댓글이 없습니다. 첫 번째 댓글을 남겨보세요!