Written with AI assistance from the source video. The video is the authority.
7:45Better Stack
Log in to leave a comment
No posts yet
레거시 시스템에서 부모-자식 관계나 다중 경로를 탐색할 때 쓰는 재귀 CTE는 데이터가 깊어지면 무너진다. 인덱스 팬아웃이 흔들리는 순간 엔진은 각 단계마다 임시 테이블을 만들고 셀프 조인을 반복한다. 결과 집합이 메모리를 채우고 디스크로 쏟아지는 스필오버가 일어난다. I/O는 막히고 CPU는 고갈된다. PostgreSQL 14에 들어온 네이티브 CYCLE 구문이 배열 검색 비용을 줄여주지만, 탐색 깊이가 깊어질수록 반복되는 보조 인덱스 룩업과 대용량 튜플 메모리 복사 비용은 그대로 남는다.
조회 속도를 높이고 CPU를 아끼려면 SQL/PGQ 기반의 네이티브 그래프 구문으로 갈아타야 한다. 첫째, 기존 관계형 테이블의 물리 구조를 그대로 둔 채 CREATE PROPERTY GRAPH 구문을 쓴다. 단일 엔티티 테이블은 VERTEX TABLES로, 다대다 교차 테이블은 EDGE TABLES로 명시한다. 둘째, 키로 지정한 컬럼이 그래프 속성에 자동으로 들어가지 않으므로 PROPERTIES 절에 필드를 적어 GRAPH_TABLE 필터링 권한을 챙긴다. 셋째, 탐색 깊이가 3단계를 넘고 평균 분기율이 10개 이상이거나 튜플 수가 1000만 건을 넘는 쿼리는 GRAPH_TABLE MATCH 패턴으로 바꾼다. 복잡한 재귀 코드가 단 한 줄의 패턴으로 줄어들고 메모리 버퍼 사용량이 눈에 띄게 줄어든다.
분산 환경에서 멀티스레드가 동시에 데이터를 밀어 넣을 때 발생하는 중복 삽입 에러는 애플리케이션 락으로 잡을 수 없다. 네트워크 RTT 오버헤드가 생기고 트랜잭션 커밋 직전의 빈틈에서 레이스 컨디션이 터진다. INSERT ON CONFLICT와 MERGE 구문으로 행 레벨 락을 내재화해야 한다. ON CONFLICT 구문은 스펙큘레이티브 삽입 기법으로 고유 인덱스 페이지에 락을 걸고 삽입을 시도한다. 충돌이 나면 DO UPDATE나 DO NOTHING으로 바로 분기해 데드락 세션 빈도를 떨어뜨린다.
원자적 구문을 쓸 때 트랜잭션 격리 수준을 바꾸면 파급 효과가 크다. READ COMMITTED에서는 후행 트랜잭션이 선행 트랜잭션의 커밋을 기다렸다가 최신 튜플을 다시 읽어 안전하게 처리한다. 하지만 REPEATABLE READ나 SERIALIZABLE로 높이면 선행 트랜잭션과 충돌이 났을 때 대기 없이 바로 직렬화 실패 에러를 뱉고 롤백한다. 롤백 비용을 줄이고 커넥션 풀 고갈을 막으려면 40001과 40P01 에러 코드에만 재시도 루프를 걸어야 한다. 기본 대기 시간에 무작위 지터값을 더해 시도 시점을 흩어놓고, 최대 재시도 횟수를 3회에서 5회 사이로 묶어 상위 비즈니스 레이어로 던진다.
블록 파편화와 죽은 튜플이 쌓여 인덱스 스캔 효율이 떨어지는 시점을 잡는 것이 스토리지 관리의 시작이다. MVCC 아키텍처 특성상 UPDATE나 DELETE로 죽은 튜플은 OS로 바로 반환되지 않고 파편화로 남는다. pg_stat_user_tables 뷰의 n_dead_tup 지표와 pgstattuple 익스텐션을 같이 볼 때 dead_tuple_ratio가 20퍼센트를 넘거나 free_space 비율이 30퍼센트를 차지하면 디스크 공간을 회수해야 한다. 방치하면 I/O 블록 읽기가 늘어나고 버퍼 풀 효율이 망가진다.
서비스를 멈추지 않고 백그라운드에서 스토리지를 회수하려면 pg_repack 도구를 쓴다. 첫째, 대상 원본 테이블의 섀도우 테이블을 만들고 로깅 트리거를 달아 변경 사항을 추적한다. 둘째, 유효한 튜플을 섀도우 테이블로 대량 복사하고 인덱스를 비동기 병렬로 재생성한 뒤 시스템 카탈로그를 바꾼다. 셋째, 터미널에서 백그라운드 프로세스 명령을 직접 실행한다.
pg_repack \
--dbname=production_db \
--table=public.orders \
--jobs=4 \
--wait-timeout=10 \
--no-superuser-check
잠금 경합을 막으려면 세션 레벨에서 SET lock_timeout = '3s';를 걸어 3초 안에 락을 못 잡으면 큐에 머물지 않고 바로 에러를 내게 만든다. 여기에 --wait-timeout=10 옵션을 붙여 후속 쿼리가 연쇄적으로 막히는 상황을 끊어낸다.
통계 정보 수집 주기가 어긋나 쿼리 플랜이 갑자기 바뀌면 프로덕션 장애로 이어진다. 대용량 작업이 몰리는 곳에서는 통계와 실제 데이터 분포가 어긋나 플랜 플립이 일어난다. 옵티마이저가 인덱스 스캔 대신 네스티드 루프 조인을 고르면 CPU 스파이크가 튀고 I/O 병목이 생긴다. shared_preload_libraries에 auto_explain 모듈을 넣고 log_min_duration을 500밀리초로 잡아 느린 쿼리의 실제 실행 플랜을 서버 로그에 남겨야 진단이 가능하다.
특정 쿼리의 실행 경로를 강제로 고정하려면 힌트 모듈을 쓴다. 로그에서 문제 쿼리를 잡은 뒤 pg_hint_plan 익스텐션을 올려 SQL 문장 앞에 주석을 달거나 hint_plan.hints 카탈로그 테이블에 쿼리를 등록한다. 소스 코드를 배포하기 힘든 상황이라면 카탈로그 단에 정적 힌트 문자열을 직접 주입해 재배포 없이 즉시 플랜을 고정할 수 있다. 통계 재수집이나 인덱스 재생성으로 시간이 걸리는 비상 상황에서 쿼리 응답 속도를 지키는 가장 빠른 방법이다.