PostgreSQL 미드레벨 시스템 설계 기술면접
새 면접Q. 월 1억 건 이상의 주문 데이터를 처리하는 전자상거래 시스템을 설계한다고 가정합니다. 주문 테이블(orders)과 주문 상세 테이블(order_items)의 관계를 설계할 때, 성능을 고려하여 어떤 파티셔닝 전략을 사용하고 인덱스는 어떻게 구성하시겠습니까?
주문 테이블은 created_at 기준으로 월별 Range Partitioning을 적용하여 과거 데이터 조회 성능을 최적화합니다. 주문 상세 테이블도 order_id를 통해 동일한 파티션 키를 사용하도록 설계합니다. 주문 테이블에는 user_id와 created_at의 복합 인덱스, order_status의 부분 인덱스를 생성하고, 주문 상세 테이블에는 order_id의 외래키 인덱스와 product_id 인덱스를 구성합니다. 파티션 프루닝을 활용하여 최근 데이터 조회 시 필요한 파티션만 스캔하도록 하며, 오래된 파티션은 별도 테이블스페이스로 이동하거나 압축합니다.
- • Range Partitioning을 사용한 시계열 데이터 분할
- • 파티션 키 정렬을 통한 조인 성능 최적화
- • 복합 인덱스와 부분 인덱스를 활용한 쿼리 성능 향상
- • 파티션 프루닝을 통한 스캔 범위 최소화
파티셔닝 후 통계 정보가 부정확해질 수 있는데, 이를 어떻게 관리하시겠습니까? 그리고 파티션 경계에 걸친 범위 쿼리의 성능 이슈는 어떻게 해결하시겠습니까?
Q. 실시간 분석 대시보드를 위해 여러 테이블을 조인하는 복잡한 집계 쿼리가 있습니다. 이 쿼리는 5초 이상 소요되며, 동시에 여러 사용자가 요청할 때 데이터베이스 부하가 급증합니다. Materialized View를 활용한 최적화 전략과 함께, 데이터 신선도와 성능 사이의 트레이드오프를 어떻게 관리하시겠습니까?
Materialized View를 생성하여 복잡한 조인과 집계를 미리 계산해두고, CONCURRENTLY 옵션을 사용한 증분 리프레시 전략을 적용합니다. 실시간성이 중요한 데이터는 pg_cron이나 애플리케이션 레벨에서 5분 단위로 REFRESH MATERIALIZED VIEW CONCURRENTLY를 실행하고, 덜 중요한 데이터는 야간 배치로 처리합니다. 추가로 Materialized View에 적절한 인덱스를 생성하고, Redis 같은 캐싱 레이어를 앞단에 두어 동일 쿼리 반복 요청을 처리합니다. 데이터 신선도가 중요한 경우 Incremental Materialized View 패턴이나 Trigger 기반 업데이트를 고려하며, 비용이 높다면 애플리케이션 레벨에서 캐시 무효화 전략을 함께 사용합니다.
- • CONCURRENTLY 옵션을 활용한 무중단 리프레시
- • 데이터 중요도에 따른 차등 리프레시 주기 설정
- • Materialized View 위에 인덱스 구성
- • 캐싱 레이어와의 조합을 통한 읽기 성능 극대화
- • Trigger 기반 업데이트와 배치 리프레시의 트레이드오프 이해
Materialized View의 리프레시 중에 Lock이 발생하여 읽기 쿼리가 블로킹될 수 있는데, 이를 완전히 회피하는 방법은 무엇입니까?
Q. 결제 시스템 API를 설계할 때, 사용자 포인트 차감과 주문 생성이 동시에 이루어져야 합니다. PostgreSQL의 트랜잭션 격리 수준 중 어떤 것을 선택하고, 동시성 제어를 위해 어떤 Lock 전략을 사용하시겠습니까? 또한 데드락 가능성을 어떻게 최소화하시겠습니까?
READ COMMITTED 격리 수준을 기본으로 사용하되, 포인트 차감 시 SELECT FOR UPDATE를 사용하여 명시적 Row Lock을 획득합니다. 포인트 잔액 확인과 차감을 원자적으로 처리하기 위해 UPDATE 문에서 WHERE 조건으로 잔액 검증을 포함시킵니다. 데드락 방지를 위해 항상 동일한 순서로 리소스를 잠그도록 규칙을 정하고, Lock 획득 타임아웃을 설정합니다. 높은 동시성이 필요한 경우 Optimistic Locking이나 Advisory Lock을 고려하며, 트랜잭션은 가능한 짧게 유지하고 외부 API 호출은 트랜잭션 밖에서 처리합니다.
- • SELECT FOR UPDATE를 통한 명시적 Row Lock
- • UPDATE 문에서 조건부 업데이트로 Race Condition 방지
- • 리소스 잠금 순서 표준화로 데드락 예방
- • 트랜잭션 범위 최소화 및 타임아웃 설정
SELECT FOR UPDATE와 SELECT FOR UPDATE NOWAIT, SELECT FOR UPDATE SKIP LOCKED의 차이점과 각각 어떤 상황에서 사용해야 하는지 설명해주세요.
Q. 멀티테넌트 SaaS 애플리케이션에서 각 테넌트의 데이터를 논리적으로 분리해야 합니다. PostgreSQL의 Row Level Security(RLS)를 활용한 설계 방법과, 이를 적용할 때 성능상 고려해야 할 사항들을 설명해주세요.
각 테이블에 tenant_id 컬럼을 추가하고 ALTER TABLE ENABLE ROW LEVEL SECURITY를 활성화한 후, CREATE POLICY로 현재 세션의 테넌트 ID와 일치하는 행만 접근 가능하도록 정책을 설정합니다. 애플리케이션에서는 연결 시 SET app.current_tenant_id = 'xxx' 같은 세션 변수를 설정하고, 정책에서 current_setting 함수로 이를 참조합니다. 성능 최적화를 위해 tenant_id를 모든 인덱스의 선행 컬럼으로 포함시켜 인덱스 스캔 효율을 높이고, 파티셔닝과 결합하여 테넌트별로 물리적 분리를 구현할 수도 있습니다. RLS 정책은 쿼리 플랜에 추가 필터로 작용하므로 복잡한 정책은 성능 저하를 일으킬 수 있어 단순하게 유지해야 합니다.
- • Row Level Security 정책 설정과 세션 변수 활용
- • tenant_id를 인덱스 선행 컬럼으로 구성
- • 파티셔닝과의 결합으로 물리적 분리 구현
- • RLS 정책의 복잡도가 쿼리 성능에 미치는 영향 이해
RLS를 사용할 때 슈퍼유저나 테이블 소유자는 기본적으로 정책을 우회할 수 있는데, 이를 제어하려면 어떻게 해야 합니까?
Q. 상품 재고 관리 시스템에서 Redis를 캐시로 사용하고 PostgreSQL을 주 저장소로 사용합니다. 재고 차감 API가 초당 수천 건 호출될 때, Cache Aside 패턴과 Write Through 패턴 중 어떤 것을 선택하고, 캐시와 DB 간 데이터 불일치를 어떻게 방지하시겠습니까?
재고 차감은 정확성이 중요하므로 Write Through 패턴을 사용하여 DB 업데이트 성공 후 캐시를 업데이트하며, 트랜잭션 커밋 후에만 캐시를 갱신합니다. 재고 조회는 Cache Aside 패턴으로 캐시 미스 시 DB에서 조회 후 캐시에 저장합니다. 동시성 제어를 위해 Redis의 WATCH/MULTI/EXEC를 사용한 Optimistic Lock이나 Lua Script로 원자적 연산을 구현하고, DB에서는 SELECT FOR UPDATE로 정합성을 보장합니다. 캐시 불일치 방지를 위해 TTL을 짧게 설정하고, PostgreSQL의 LISTEN/NOTIFY나 Debezium 같은 CDC 도구로 변경 이벤트를 감지하여 캐시를 무효화하는 전략을 병행합니다. 재고 같은 민감한 데이터는 항상 DB를 Source of Truth로 두고 캐시는 성능 보조 수단으로만 사용합니다.
- • Write Through 패턴으로 쓰기 정합성 보장
- • Redis Lua Script 또는 WATCH/MULTI/EXEC로 원자적 연산
- • PostgreSQL LISTEN/NOTIFY 또는 CDC를 통한 캐시 무효화
- • TTL 설정과 DB를 최종 진실의 원천으로 유지
- • 읽기와 쓰기에 다른 캐싱 전략 적용
재고가 0이 되었을 때 과도한 캐시 미스로 인한 DB 부하(Cache Stampede)를 방지하려면 어떤 전략을 사용하시겠습니까?
아직 댓글이 없습니다. 첫 번째 댓글을 남겨보세요!