PostgreSQL 미드레벨 시스템 설계 면접

Range 파티셔닝은 시간 기반 데이터(월별, 일별)에 적합하며 오래된 데이터 삭제가 용이합니다. List 파티셔닝은 지역, 카테고리 등 명확한 그룹핑이 가능한 경우 사용하며, Hash 파티셔닝은 균등 분산이 필요할 때 적합합니다. 파티션 프루닝은 WHERE 절의 파티션 키 조건을 통해 불필요한 파티션 스캔을 제거하여 성능을 크게 향상시킵니다. 파티션 키는 쿼리 패턴과 일치해야 하며, 너무 많은 파티션(수천 개 이상)은 플래닝 오버헤드를 증가시킬 수 있습니다. 파티션 관리 자동화를 위해 pg_partman 같은 확장을 고려할 수 있습니다.

PostgreSQL의 통계 정보는 각 컬럼의 데이터 분포, 가장 빈번한 값(MCV), NULL 비율, 히스토그램 등을 포함하며 쿼리 플래너가 비용 기반으로 최적의 실행 계획을 선택하는 데 사용됩니다. pg_stat_statements는 실행된 쿼리의 통계를 추적하여 느린 쿼리를 식별하고, pg_stat_user_tables는 테이블별 스캔 횟수와 인덱스 사용률을 모니터링합니다. 통계가 부정확하면 플래너가 잘못된 실행 계획을 선택할 수 있으며, 이는 ANALYZE 명령으로 통계를 갱신하거나 default_statistics_target을 증가시켜 해결합니다. 특정 컬럼의 통계 샘플링을 ALTER TABLE SET STATISTICS로 조정할 수 있습니다.

pgcrypto는 애플리케이션 레벨에서 특정 컬럼만 선택적으로 암호화할 수 있어 유연하지만, 암호화된 데이터는 인덱싱이 불가능하고 검색이 어렵습니다. TDE는 파일 시스템 레벨에서 전체 데이터를 암호화하여 투명하게 작동하지만 PostgreSQL 커뮤니티 버전은 기본 지원하지 않습니다. 암호화된 데이터 검색을 위해서는 해시 기반 검색(HMAC)을 사용하거나, 마스킹된 부분 데이터를 별도 컬럼에 저장하거나, 토큰화(Tokenization) 방식을 고려할 수 있습니다. 키 관리는 AWS KMS나 HashiCorp Vault 같은 외부 시스템을 활용하는 것이 안전합니다.

OFFSET/LIMIT 방식은 구현이 간단하고 임의 페이지 접근이 가능하지만, OFFSET이 클수록 모든 이전 행을 스캔해야 하므로 성능이 급격히 저하됩니다. Cursor 기반 페이지네이션은 마지막 레코드의 키 값을 기준으로 WHERE 절을 사용하여 일정한 성능을 유지하며 대용량 데이터에 적합하지만, 임의 페이지 접근이 불가능하고 복잡한 정렬 조건 처리가 어렵습니다. 동적 데이터의 일관성을 위해서는 스냅샷 격리 수준의 트랜잭션을 사용하거나, 타임스탬프 기반 필터링을 추가하거나, 버전 관리를 통해 페이지네이션 세션을 유지할 수 있습니다. 무한 스크롤에는 Cursor 방식이, 전통적인 페이지 네비게이션에는 OFFSET/LIMIT이 적합합니다.

캐시 히트율(shared_buffers)은 99% 이상이 이상적이며 90% 이하면 메모리 부족을 의심해야 합니다. 커넥션 사용률은 max_connections의 80%를 넘으면 경고하고 connection pooling을 검토해야 합니다. 트랜잭션 처리량(TPS)과 쿼리 응답 시간은 베이스라인 대비 급격한 변화를 감지해야 합니다. 복제 지연은 1초 이상이면 주의하고 10초 이상이면 심각한 상태입니다. 디스크 I/O 대기가 높으면 인덱스 부족이나 쿼리 최적화가 필요합니다. pg_stat_database로 전체 DB 통계를, pg_stat_activity로 현재 실행 중인 쿼리를, pg_stat_replication으로 복제 상태를 모니터링하며, 이를 Prometheus와 Grafana로 시각화하는 것이 일반적입니다.