PostgreSQL에서 WITH ... AS MATERIALIZED 물리적 임시저장

WITH ... AS MATERIALIZED 의미

PostgreSQL에서 WITH ... AS MATERIALIZED는 Common Table Expression (CTE, 공통 테이블 표현식)을 강제적으로 물리적으로 저장 (Materialized)하도록 지정하는 옵션이다.

---

기본적인 WITH (CTE) 사용

일반적으로 WITH를 사용하면 쿼리 실행 시 일시적으로 데이터를 저장하여 사용할 수 있다.

WITH CRSINFO AS (
    SELECT id, name, created_at
    FROM courses
    WHERE created_at > NOW() - INTERVAL '1 year'
)
SELECT * FROM CRSINFO;

위 쿼리는 courses 테이블에서 1년 이내에 생성된 데이터를 CRSINFO라는 임시 테이블에 저장하고, 그 데이터를 조회한다.

---

✅ WITH CRSINFO AS MATERIALIZED의 의미

WITH CRSINFO AS MATERIALIZED (
    SELECT id, name, created_at
    FROM courses
    WHERE created_at > NOW() - INTERVAL '1 year'
)
SELECT * FROM CRSINFO;

 MATERIALIZED의 역할:

• WITH CTE 결과를 물리적으로 임시 저장하여 재사용할 때 다시 계산하지 않음 (성능 최적화)
• 일반 WITH는 기본적으로 "INLINE" 처리되며, 서브쿼리처럼 매번 실행됨 → MATERIALIZED는 처음 실행 후 결과를 캐시하여 이후 재사용 시 더 빠름
• 쿼리 실행 계획(Execution Plan)에서 MATERIALIZED CTE는 Temporary Table처럼 동작

---

예제: MATERIALIZED vs NOT MATERIALIZED

1. 일반 WITH (기본값: NOT MATERIALIZED)

WITH CRSINFO AS (
    SELECT id, name, created_at
    FROM courses
    WHERE created_at > NOW() - INTERVAL '1 year'
)
SELECT * FROM CRSINFO;

• WITH 내부 쿼리는 각각의 호출마다 다시 실행됨 (비효율적)

2. WITH ... AS MATERIALIZED

WITH CRSINFO AS MATERIALIZED (
    SELECT id, name, created_at
    FROM courses
    WHERE created_at > NOW() - INTERVAL '1 year'
)
SELECT * FROM CRSINFO;

• CRSINFO가 한 번 실행되어 결과를 캐시함 → 동일한 데이터를 여러 번 사용할 때 성능 향상

---

⚠️ AS MATERIALIZED 주의점

1. MATERIALIZED는 쿼리 결과를 강제적으로 저장하므로 큰 데이터셋에서는 메모리 사용량 증가 가능
2. 성능 이점이 있지만, 데이터가 자주 변경되는 경우 최신 데이터가 반영되지 않을 수도 있음
3. PostgreSQL 12 버전 이상에서만 사용 가능 (이전 버전에서는 MATERIALIZED 옵션 없음)

---

결론

• WITH ... AS MATERIALIZED는 CTE 결과를 임시 저장하여 성능을 최적화하는 기능
• 같은 CTE를 여러 번 사용할 때 쿼리 성능을 개선할 수 있음
• PostgreSQL 12 이상에서만 지원됨

성능 최적화가 필요할 때 유용하지만, 데이터 변경이 빈번한 경우에는 주의해야 합다!