PostgreSQL Materialized View: 쿼리 결과 캐싱이 합리적인 경우

문제 상황: 대시보드 쿼리가 28초씩 걸림

• 대시보드 쿼리가 30초 타임아웃에 걸리고, BI 도구는 스피너만 돌아가고, 유저는 페이지를 새로고침하는 상황임
• 인덱스도 다 걸었고, shared_buffers도 튜닝했고, 쿼리도 세 번 다시 썼는데 해결이 안 됨
• 문제는 SQL이 나쁜 게 아니라, PostgreSQL이 매번 수백만 행을 aggregate/join/scan하도록 시키고 있는 구조 자체임
• 같은 비싼 계산을 하루에 수십 번 반복 실행하는 건 설계상의 선택이지, 자랑거리가 아님

View vs Materialized View vs Summary Table

• View: 읽을 때마다 매번 계산됨. 항상 최신이지만 매 읽기마다 전체 계산 비용을 지불함
• Materialized View: refresh 시점에 계산하고 결과를 디스크에 저장함. 읽기는 빠르고 예측 가능하며, staleness는 refresh 스케줄로 명시적으로 관리됨
• Summary Table: 업데이트 파이프라인을 직접 관리해야 함. ETL, 앱 코드, 트리거 등으로 insert/update 로직을 직접 작성함
• Materialized View는 물리적 relation이라 일반 테이블처럼 인덱스를 걸 수 있고, 플래너도 테이블처럼 취급함

구체적 예시: 이커머스 주문 매출 리포트

• 조인 대상 테이블: orders 10M행 + order_items 40M행 + products 500K행 + customers 2M행
• 제품 카테고리별, 고객 지역별, 주간별로 매출을 집계하는 쿼리임
• 실행 시간 28초, 하루 40회 이상 실행됨 (대시보드 로드, 내보내기, API 호출)
• 버퍼 스캔량이 80GB+ 에 달함

생성 방법: Step-by-Step

• CREATE MATERIALIZED VIEW ... WITH NO DATA로 구조만 먼저 만드는 게 핵심임. 데이터를 바로 채우지 않으므로 인덱스를 먼저 걸고 유지보수 시간에 populate할 수 있음
• REFRESH MATERIALIZED VIEW mv_order_revenue_summary;로 데이터를 채움. 첫 refresh 비용은 4.2초임
• 인덱스는 원본 테이블의 읽기 패턴이 아니라 MV를 조회하는 패턴에 맞춰 설계해야 함
• 필터 컬럼용: CREATE INDEX idx_mv_revenue_tenant_week ON mv_order_revenue_summary(tenant_id, week);
• 그루핑 차원용: category, region 각각 별도 인덱스 생성

-- MV 생성 (데이터 없이)
CREATE MATERIALIZED VIEW mv_order_revenue_summary AS
SELECT o.tenant_id, p.category, c.region,
       DATE_TRUNC('week', o.order_date) AS week,
       COUNT(DISTINCT o.order_id) AS order_count,
       SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY o.tenant_id, p.category, c.region, DATE_TRUNC('week', o.order_date)
WITH NO DATA;

Refresh 전략: Full vs Concurrent

• Full Refresh: REFRESH MATERIALIZED VIEW로 전체를 다시 씀. 실행 중 읽기가 블로킹됨. 피크 시간 외에 스케줄링해야 함
• Concurrent Refresh: REFRESH MATERIALIZED VIEW CONCURRENTLY로 백그라운드에서 새 스냅샷을 만들고 원자적으로 교체함. 읽기가 계속 가능함
• Concurrent refresh를 쓰려면 유니크 인덱스가 필수임: CREATE UNIQUE INDEX idx_mv_revenue_unique ON mv_order_revenue_summary(tenant_id, category, region, week);
• Concurrent가 refresh 오버헤드는 더 크지만, 유저 대면 쿼리를 서비스하는 MV라면 4초짜리 락보다 나음

Freshness Contract: "충분히 신선한" 기준 정하기

• 5분마다: 준실시간 대시보드용
• 매시간: 내부 리포팅용
• 매일: 경영진 요약용
• 운영 SLO 예시: 최대 refresh 런타임 10초, 허용 staleness 1시간
• Staleness를 유저에게 보여줘야 함. 데이터가 마지막으로 언제 갱신됐는지 표시하는 것이 중요함

스케줄링: 하나의 스케줄러만 사용할 것

• OS cron: 0 * * * * postgres psql -d production -c "REFRESH MATERIALIZED VIEW CONCURRENTLY ...;"
• pg_cron: SELECT cron.schedule('refresh-revenue-mv', '0 * * * *', $$REFRESH MATERIALIZED VIEW CONCURRENTLY ...$$);
• K8s CronJob: postgres:16 이미지로 psql 명령 실행하는 CronJob 설정
• 여러 소스에서 refresh를 트리거하면 안 됨. 하나의 메커니즘을 정해서 일관되게 사용할 것
• RDS/Azure/Cloud SQL 등 매니지드 환경에서 pg_cron이 안 되면 Cloud Scheduler, EventBridge, Azure Automation 등을 활용하면 됨

중복 Refresh 방지: Advisory Lock 패턴

• refresh가 6분 걸리는데 5분마다 스케줄링하면 refresh 작업이 겹침
• pg_try_advisory_lock(12345)로 락을 먼저 획득하고, 성공한 경우에만 refresh를 실행하는 패턴을 사용함
• 락 획득 실패 시 RAISE NOTICE 'Refresh already running, skipping'으로 건너뜀
• 이 패턴으로 지연 시 발생하는 refresh storm을 방지할 수 있음

Observability: Refresh 비용과 Staleness 추적

• 핵심 수치 정리: 기존 쿼리 28초 → MV 쿼리 180ms, refresh 비용 4.2초, 매시간 refresh
• 시간당 4.2초 작업으로 하루 40+ 쿼리에서 각 28초를 절약하는 셈이니 수학적으로 충분히 타당함
• pg_matviews에는 last_refresh 컬럼이 없음. 직접 mv_refresh_log 테이블을 만들어서 추적해야 함

CREATE TABLE mv_refresh_log (
    mv_name TEXT PRIMARY KEY,
    last_refresh_at TIMESTAMPTZ,
    refresh_duration_ms INTEGER
);

• refresh 시 clock_timestamp()로 시작/종료 시간을 잡고 ON CONFLICT DO UPDATE로 기록을 갱신하는 패턴을 사용함
• refresh가 시스템 자원에 미치는 영향도 모니터링해야 함: CPU/IO 스파이크, temp 파일 사용량(대규모 sort/hash가 디스크로 spill될 수 있음), replica lag 등
• Materialized View refresh도 결국 쿼리임. 리소스를 소비하므로 그에 맞게 계획해야 함