---
title: "PostgreSQL Materialized View: 쿼리 결과 캐싱이 합리적인 경우"
published: 2026-02-03T22:47:08.000Z
canonical: https://jeff.news/article/364
---
# PostgreSQL Materialized View: 쿼리 결과 캐싱이 합리적인 경우

28초 걸리던 대시보드 쿼리를 Materialized View로 180ms로 줄이는 과정을 다룸. View/MV/Summary Table 비교, WITH NO DATA로 안전한 생성, 인덱스 설계, Full vs Concurrent refresh 전략, freshness contract 설정, cron/pg_cron/K8s CronJob 스케줄링, advisory lock으로 중복 refresh 방지, mv_refresh_log 테이블을 활용한 observability까지 운영 레벨의 전체 가이드임.

## 문제 상황: 대시보드 쿼리가 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+ 에 달함

> [!IMPORTANT]
> Materialized View 적용 후 쿼리 시간이 **28초 → 180ms**로 단축됨. 조인/집계 없이 인덱스 스캔만으로 결과를 가져오는 구조로 바뀐 것임.

## 생성 방법: 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` 각각 별도 인덱스 생성

```sql
-- 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` 테이블을 만들어서 추적해야 함

```sql
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도 결국 쿼리임. 리소스를 소비하므로 그에 맞게 계획해야 함

## 핵심 포인트

- 10M+ 행 조인 쿼리가 28초에서 180ms로 단축됨 (refresh 비용 4.2초)
- WITH NO DATA로 구조만 먼저 만들고 인덱스 설정 후 populate하는 안전한 롤아웃 방법
- CONCURRENTLY 키워드로 읽기 블로킹 없이 refresh 가능 (유니크 인덱스 필수)
- pg_matviews에 last_refresh 컬럼이 없으므로 mv_refresh_log 테이블로 직접 추적해야 함
- advisory lock 패턴으로 refresh 겹침 방지

## 인사이트

반복되는 비싼 쿼리를 매번 실행하는 건 설계 선택이지 불가피한 제약이 아님. Materialized View는 '읽기 시점의 계산'을 '스케줄된 계산'으로 바꾸는 명시적 트레이드오프이고, freshness contract과 observability를 갖추면 운영 가능한 캐싱 레이어가 됨.