PostgreSQL 미드레벨 기술면접
새 면접Q. 대용량 로그 테이블에서 특정 기간의 데이터를 조회하는 쿼리가 있습니다. WHERE 절에 created_at BETWEEN '2024-01-01' AND '2024-01-31'과 user_id = 12345 두 조건이 있을 때, 단일 컬럼 인덱스 2개를 생성하는 것과 복합 인덱스(Composite Index) 1개를 생성하는 것의 차이를 설명하고, 컬럼 순서가 성능에 미치는 영향을 설명해주세요.
PostgreSQL은 복합 인덱스에서 왼쪽 컬럼부터 순차적으로 사용하므로, 컬럼 순서가 매우 중요합니다. 카디널리티가 높은 컬럼(user_id)을 앞에 배치하면 초기 필터링이 효율적이지만, 범위 검색 컬럼(created_at)을 뒤에 두면 인덱스 스캔 범위가 넓어질 수 있습니다. 일반적으로 등호 조건을 앞에, 범위 조건을 뒤에 배치하는 것이 유리합니다. 단일 인덱스 2개를 사용하면 Bitmap Index Scan으로 결합되지만, 복합 인덱스보다 비효율적입니다. 쿼리 패턴 분석을 통해 가장 자주 사용되는 조건 조합을 기준으로 복합 인덱스를 설계해야 합니다.
- • 복합 인덱스는 컬럼 순서에 따라 성능이 크게 달라지며, 왼쪽 컬럼부터 순차적으로 사용됨
- • 등호 조건(=)을 앞에, 범위 조건(BETWEEN, >, <)을 뒤에 배치하는 것이 일반적으로 효율적
- • 카디널리티(고유값 비율)가 높은 컬럼을 앞에 배치하면 초기 필터링 효율이 높아짐
- • 단일 인덱스 여러 개보다 적절한 복합 인덱스 하나가 성능상 유리함
특정 쿼리에서 복합 인덱스를 생성했는데도 Seq Scan이 선택되는 경우, 어떤 원인들을 의심하고 어떻게 해결하시겠습니까?
Q. 시계열 데이터를 저장하는 시스템에서 timestamp, timestamptz(timestamp with time zone), bigint(Unix epoch) 중 어떤 타입을 선택하시겠습니까? 각 타입의 저장 크기, 인덱스 성능, 시간대 처리, 범위 쿼리 성능을 비교하고, 글로벌 서비스에서 권장되는 선택과 그 이유를 설명해주세요.
timestamptz는 8바이트로 timestamp와 동일한 저장 크기를 가지지만, UTC로 내부 저장하고 세션 시간대에 맞춰 자동 변환합니다. bigint(Unix epoch)도 8바이트이며 범위 쿼리에서 약간 빠를 수 있지만, 가독성과 날짜 함수 활용이 제한됩니다. 글로벌 서비스에서는 timestamptz가 권장되는데, 시간대 변환을 데이터베이스가 자동 처리하고, 날짜 연산 함수를 활용할 수 있으며, 인덱스 성능도 우수하기 때문입니다. timestamp(시간대 없음)는 시간대 정보 손실로 인한 버그 위험이 있어 피해야 합니다. BRIN 인덱스를 활용하면 시계열 데이터의 범위 쿼리를 더 효율적으로 처리할 수 있습니다.
- • timestamptz는 UTC로 저장하고 세션 시간대에 맞춰 자동 변환하여 글로벌 서비스에 적합
- • bigint는 범위 쿼리가 약간 빠를 수 있지만 가독성과 날짜 함수 활용이 제한적
- • timestamp(without timezone)는 시간대 정보 손실로 인한 버그 위험이 있어 피해야 함
- • 시계열 데이터는 BRIN 인덱스를 활용하면 저장 공간을 절약하면서 범위 쿼리 성능을 확보 가능
시계열 데이터가 월 10억 건 이상 적재되는 상황에서, TimescaleDB 같은 확장 없이 네이티브 PostgreSQL만으로 파티셔닝과 인덱스 전략을 어떻게 구성하시겠습니까?
Q. PostgreSQL의 MVCC(Multi-Version Concurrency Control) 아키텍처를 설명하고, 이로 인해 발생하는 bloat 문제와 transaction ID wraparound 위험에 대해 설명해주세요. 또한 장시간 실행되는 트랜잭션이나 idle in transaction 상태가 시스템에 미치는 영향과 모니터링 방법을 제시해주세요.
PostgreSQL의 MVCC는 각 행의 여러 버전을 유지하여 읽기와 쓰기가 서로 블로킹하지 않도록 합니다. 각 행은 xmin(생성 트랜잭션 ID)과 xmax(삭제 트랜잭션 ID)를 가지며, 트랜잭션은 자신의 스냅샷에 맞는 버전만 봅니다. 하지만 오래된 행 버전이 누적되면 bloat가 발생하고, transaction ID가 20억을 넘으면 wraparound로 인한 데이터 손실 위험이 있습니다. 장시간 실행되는 트랜잭션은 오래된 행 버전을 유지시켜 VACUUM을 방해하고, pg_stat_activity를 통해 state='idle in transaction'이나 오래된 xact_start를 모니터링해야 합니다. statement_timeout과 idle_in_transaction_session_timeout 설정으로 예방할 수 있습니다.
- • MVCC는 각 행의 여러 버전을 유지하여 읽기-쓰기 동시성을 제공하지만, 오래된 버전이 bloat를 유발
- • transaction ID wraparound는 20억 개 트랜잭션 후 발생하며, 자동 VACUUM freeze로 예방
- • 장시간 실행 트랜잭션은 오래된 행 버전 정리를 방해하여 bloat와 성능 저하 유발
- • pg_stat_activity로 idle in transaction 상태와 오래된 트랜잭션을 모니터링하고 timeout 설정 필요
Serializable 격리 수준에서 발생하는 Serialization Failure를 애플리케이션 레벨에서 어떻게 처리해야 하며, 재시도 로직 구현 시 주의할 점은 무엇입니까?
Q. PostgreSQL의 shared_buffers, work_mem, maintenance_work_mem, effective_cache_size 파라미터의 역할을 설명하고, 32GB RAM을 가진 전용 데이터베이스 서버에서 각 파라미터를 어떻게 튜닝하시겠습니까? 또한 잘못된 설정이 성능에 미치는 영향을 설명해주세요.
shared_buffers는 PostgreSQL이 사용하는 공유 메모리 캐시로, 일반적으로 RAM의 25%(8GB)로 설정합니다. work_mem은 정렬과 해시 조인 같은 쿼리 작업에 사용되는 세션별 메모리로, 4-16MB 정도가 적절하며 너무 크면 메모리 부족을 유발합니다. maintenance_work_mem은 VACUUM, CREATE INDEX 등에 사용되며 1-2GB로 설정합니다. effective_cache_size는 OS 페이지 캐시를 포함한 총 사용 가능 메모리로, RAM의 50-75%(16-24GB)로 설정하여 쿼리 플래너가 인덱스 스캔 비용을 정확히 계산하도록 합니다. shared_buffers가 너무 크면 체크포인트 시간이 길어지고, work_mem이 너무 작으면 디스크 기반 정렬이 발생하여 성능이 저하됩니다.
- • shared_buffers는 RAM의 25% 정도로 설정하며, 너무 크면 체크포인트 성능 저하 유발
- • work_mem은 세션별로 할당되므로 동시 연결 수를 고려하여 보수적으로 설정(4-16MB)
- • maintenance_work_mem은 유지보수 작업에만 사용되므로 크게 설정 가능(1-2GB)
- • effective_cache_size는 쿼리 플래너의 비용 계산에만 사용되며 실제 메모리 할당은 안 함
random_page_cost와 seq_page_cost 파라미터가 쿼리 플래너의 실행 계획 선택에 어떤 영향을 미치며, SSD 환경에서는 어떻게 조정해야 합니까?
Q. 외래 키 제약조건(Foreign Key Constraint)을 설정할 때 ON DELETE CASCADE, ON DELETE SET NULL, ON DELETE RESTRICT의 차이를 설명하고, 각각의 사용 사례를 제시해주세요. 또한 외래 키가 성능에 미치는 영향과, 대용량 데이터 환경에서 외래 키 사용을 피하는 경우의 대안을 설명해주세요.
ON DELETE CASCADE는 부모 행 삭제 시 자식 행도 자동 삭제되며, 사용자-게시물 관계처럼 종속적인 데이터에 적합합니다. ON DELETE SET NULL은 자식 행의 외래 키를 NULL로 설정하며, 선택적 참조 관계에 사용됩니다. ON DELETE RESTRICT는 자식 행이 있으면 부모 행 삭제를 거부하여 데이터 무결성을 보장합니다. 외래 키는 INSERT/UPDATE/DELETE 시 참조 무결성 검증으로 인한 오버헤드가 있고, 자식 테이블에 자동으로 인덱스를 생성하지 않아 수동 생성이 필요합니다. 대용량 환경에서는 애플리케이션 레벨에서 무결성을 관리하거나, 배치 작업으로 주기적 검증을 수행하는 방식을 고려할 수 있습니다.
- • CASCADE는 종속 데이터 자동 삭제, SET NULL은 선택적 참조, RESTRICT는 삭제 방지로 각기 다른 비즈니스 요구사항에 대응
- • 외래 키는 모든 변경 작업에서 참조 무결성 검증 비용이 발생
- • 외래 키가 참조하는 컬럼에는 반드시 인덱스를 생성해야 조인 성능 확보
- • 대용량 환경에서는 애플리케이션 레벨 검증이나 배치 검증으로 대체 가능
DEFERRABLE 제약조건의 개념과 사용 사례를 설명하고, 순환 참조가 있는 테이블 간 데이터 삽입 시 어떻게 활용할 수 있는지 설명해주세요.
아직 댓글이 없습니다. 첫 번째 댓글을 남겨보세요!