PostgreSQL 미드레벨 시스템 설계 면접
새 면접Q. 소셜 미디어 플랫폼에서 사용자 간 팔로우 관계를 저장하는 테이블을 설계해야 합니다. 1억 명 이상의 사용자가 있고, 각 사용자는 평균 500명을 팔로우합니다. 팔로우/언팔로우 기능과 함께 '내가 팔로우하는 사람들의 최근 게시물' 조회가 빈번하게 발생합니다. 테이블 스키마와 인덱스 전략을 어떻게 설계하시겠습니까?
follows 테이블은 (follower_id, following_id, created_at)으로 구성하고, 복합 인덱스를 활용합니다. follower_id를 기준으로 한 조회가 많으므로 (follower_id, created_at DESC) 복합 인덱스를 생성하고, 양방향 조회를 위해 (following_id, follower_id) 인덱스도 추가합니다. 데이터 규모를 고려하여 follower_id 기준 해시 파티셔닝을 적용하고, posts 테이블과 조인 시 성능을 위해 partial index나 covering index를 활용합니다. 팔로우 수가 많은 인플루언서의 경우 별도 캐싱 전략을 적용하여 DB 부하를 분산시킵니다.
- • 복합 인덱스 전략: (follower_id, created_at), (following_id, follower_id)
- • 해시 파티셔닝으로 대용량 데이터 분산 처리
- • 조회 패턴에 따른 covering index 활용
- • 인플루언서 등 핫 데이터에 대한 별도 캐싱 전략
팔로워 수가 1000만 명이 넘는 셀럽의 게시물 조회 시 발생할 수 있는 성능 문제를 어떻게 해결하시겠습니까?
Q. 글로벌 서비스를 위해 PostgreSQL 데이터베이스를 여러 리전에 분산 배치해야 합니다. 사용자 프로필 정보는 쓰기 작업이 적고 읽기 작업이 많으며, 지역별로 접근 지연시간을 최소화해야 합니다. Read Replica를 활용한 아키텍처 설계 시 고려해야 할 사항과, 복제 지연(replication lag)으로 인한 데이터 정합성 문제를 API 레벨에서 어떻게 처리하시겠습니까?
Master-Replica 구조로 각 리전에 Read Replica를 배치하고, 읽기 트래픽은 가장 가까운 Replica로 라우팅합니다. 쓰기는 Master로만 전송하며, Synchronous Replication과 Asynchronous Replication을 혼합 사용합니다. API 레벨에서는 쓰기 후 즉시 읽기가 필요한 경우 Master로 라우팅하거나, 클라이언트에 write timestamp를 반환하고 Replica가 해당 시점까지 복제 완료되었는지 확인하는 Read-Your-Writes 일관성을 보장합니다. 중요 트랜잭션은 session replication mode를 조정하여 강한 일관성을 보장하고, 일반 조회는 eventual consistency를 허용합니다.
- • 리전별 Read Replica 배치 및 지리적 라우팅
- • Synchronous/Asynchronous Replication 혼합 전략
- • Read-Your-Writes 일관성을 위한 timestamp 기반 검증
- • API 레벨에서 일관성 수준을 요청 특성에 따라 차등 적용
Master 장애 발생 시 Replica를 Master로 승격시키는 Failover 전략과, 이 과정에서 발생할 수 있는 데이터 손실을 최소화하는 방법은 무엇입니까?
Q. 의료 정보 시스템에서 환자 데이터를 다루는 API를 설계합니다. 의사는 자신이 담당하는 환자의 정보만 조회 가능하고, 간호사는 읽기만 가능하며, 관리자는 모든 접근 권한을 갖습니다. PostgreSQL의 Role 기반 접근 제어(RBAC)와 Row Level Security를 활용하여 이를 구현하는 방법과, 애플리케이션 레벨에서 연동하는 전략을 설명해주세요.
PostgreSQL에서 doctor, nurse, admin 역할을 생성하고 각각에 적절한 권한을 부여합니다. patients 테이블에 RLS 정책을 적용하여, doctor 역할은 doctor_id가 자신과 일치하는 행만 조회 가능하도록 하고, nurse는 SELECT만 허용하며, admin은 BYPASSRLS 권한을 부여합니다. 애플리케이션에서는 사용자 인증 후 해당하는 DB role로 SET ROLE을 실행하거나, connection pooling 시 역할별 커넥션 풀을 분리합니다. 감사 로그를 위해 pgaudit 확장을 활용하여 모든 접근을 기록하고, 민감 데이터는 pgcrypto를 이용해 암호화합니다.
- • PostgreSQL Role 생성 및 권한 차등 부여
- • Row Level Security 정책으로 행 단위 접근 제어
- • 애플리케이션에서 SET ROLE 또는 역할별 커넥션 풀 분리
- • pgaudit를 통한 감사 로그 및 pgcrypto로 데이터 암호화
Connection Pooling 환경에서 SET ROLE을 사용할 때 발생할 수 있는 성능 문제와 보안 이슈는 무엇이며, 어떻게 해결하시겠습니까?
Q. 예약 시스템에서 같은 좌석에 대한 동시 예약 요청이 발생할 수 있습니다. 100개의 좌석이 있고, 초당 수백 건의 예약 시도가 발생하는 상황에서 overbooking을 방지하면서도 높은 처리량을 유지해야 합니다. Optimistic Locking과 Pessimistic Locking의 장단점을 비교하고, 이 시나리오에 적합한 동시성 제어 전략과 PostgreSQL의 어떤 기능을 활용할지 설명해주세요.
이 시나리오에서는 충돌 가능성이 높으므로 Pessimistic Locking이 더 적합합니다. SELECT FOR UPDATE SKIP LOCKED를 활용하여 경합 없는 좌석을 빠르게 선택하고, 이미 잠긴 좌석은 건너뛰어 처리량을 높입니다. seats 테이블에 available_count 컬럼을 두고 UPDATE로 원자적으로 차감하며, CHECK 제약조건으로 음수 방지합니다. 트랜잭션 격리 수준은 READ COMMITTED를 사용하되, 예약 확정 시에만 SERIALIZABLE로 상향하여 phantom read를 방지합니다. 대안으로 PostgreSQL의 Advisory Lock을 좌석 ID 기반으로 사용하여 애플리케이션 레벨에서 세밀한 제어도 가능합니다.
- • SELECT FOR UPDATE SKIP LOCKED로 경합 최소화
- • available_count 원자적 차감 및 CHECK 제약조건
- • 트랜잭션 격리 수준을 상황별로 차등 적용
- • Advisory Lock을 활용한 애플리케이션 레벨 동시성 제어
SKIP LOCKED를 사용할 때 공정성(fairness) 문제가 발생할 수 있습니다. 특정 사용자가 계속 좌석을 할당받지 못하는 상황을 어떻게 방지하시겠습니까?
Q. 운영 중인 서비스의 users 테이블(1억 건)에 새로운 NOT NULL 컬럼을 추가해야 합니다. 서비스 다운타임 없이 안전하게 스키마 변경을 수행하는 전략과, PostgreSQL의 어떤 기능을 활용하여 테이블 락을 최소화할 수 있는지 설명해주세요.
먼저 새 컬럼을 NULL 허용으로 추가하고 기본값을 설정합니다. PostgreSQL 11 이상에서는 ALTER TABLE ADD COLUMN with DEFAULT가 즉시 완료되어 테이블 재작성이 발생하지 않습니다. 그 후 백그라운드 배치 작업으로 기존 데이터를 작은 단위로 나누어 UPDATE하며, CONCURRENTLY 옵션으로 인덱스를 생성합니다. 모든 데이터 업데이트 완료 후 NOT NULL 제약조건을 추가하되, CHECK 제약조건을 먼저 NOT VALID로 추가하고 VALIDATE CONSTRAINT로 검증하여 짧은 락만 획득합니다. 배포는 Blue-Green 방식으로 진행하여 애플리케이션 코드가 새 컬럼을 점진적으로 사용하도록 합니다.
- • 컬럼 추가 시 DEFAULT 활용으로 테이블 재작성 방지
- • 배치로 데이터 점진적 업데이트 및 CONCURRENTLY 인덱스 생성
- • CHECK 제약조건을 NOT VALID로 추가 후 VALIDATE로 검증
- • Blue-Green 배포로 애플리케이션 코드 점진적 전환
대용량 테이블의 컬럼 타입을 변경해야 하는 경우(예: INTEGER to BIGINT) 다운타임 없이 수행하는 방법은 무엇입니까?
아직 댓글이 없습니다. 첫 번째 댓글을 남겨보세요!