[PostgreSQL] 머티리얼라이즈드 뷰 무거운 집계를 미리 구워 대시보드에서 즉답한다

설치·접속: PostgreSQL 설치와 접속

부제: 대시보드에서 매번 돌리기엔 무거운 상품별 매출 집계를, 결과를 통째로 저장해 두고 빠르게 읽고 싶을 때

-- 집계 결과를 물리적으로 저장. 조회는 일반 테이블처럼 즉시 응답한다
CREATE MATERIALIZED VIEW product_sales AS
SELECT p.id, p.name,
       count(o.id)                              AS orders,
       coalesce(sum(o.qty), 0)                  AS total_qty,
       round(coalesce(sum(o.qty * p.price),0),2) AS revenue
FROM products p
LEFT JOIN orders o ON o.product_id = p.id
GROUP BY p.id, p.name;

SELECT * FROM product_sales ORDER BY revenue DESC LIMIT 6;

 id |  name  | orders | total_qty | revenue
----+--------+--------+-----------+---------
 17 | item17 |     14 |        56 | 4844.00
 11 | item11 |     17 |        62 | 4746.72
  3 | item3  |     17 |        64 | 4588.80
  5 | item5  |     13 |        39 | 2924.22
 13 | item13 |     11 |        37 | 2617.75
 18 | item18 |     13 |        44 | 2473.68
(6 rows)

일반 뷰(VIEW)는 조회할 때마다 원본 쿼리를 다시 돌리지만, 머티리얼라이즈드 뷰는 집계 결과를 디스크에 실제로 저장해 둔다. 조인·집계가 무거워 매 요청마다 계산하면 느린 대시보드나 리포트에서, 미리 구워 둔 결과를 인덱스까지 걸어 즉시 읽는다. 대신 원본이 바뀌어도 자동 갱신되지 않으므로, REFRESH MATERIALIZED VIEW로 다시 구워야 최신이 된다. "약간 오래된 값이어도 괜찮고, 대신 빨라야 하는" 집계에 딱 맞는다.

이렇게도 쓴다

원본이 바뀐 뒤 결과를 다시 굽는다. (조합: REFRESH)

REFRESH MATERIALIZED VIEW product_sales;

 

갱신 중에도 조회를 막지 않는다(운영 중 새로고침). (조합: CONCURRENTLY + UNIQUE INDEX)

CREATE UNIQUE INDEX ON product_sales(id);   -- 필수 전제조건
REFRESH MATERIALIZED VIEW CONCURRENTLY product_sales;

 

정의만 먼저 만들고 데이터는 나중에 채운다. (조합: WITH NO DATA)

CREATE MATERIALIZED VIEW mv_sales AS SELECT ... WITH NO DATA;
REFRESH MATERIALIZED VIEW mv_sales;   -- 이때 처음 채워짐

 

조회 속도를 위해 뷰 위에 인덱스를 건다(일반 테이블처럼).

CREATE INDEX ON product_sales(revenue DESC);

 

크론으로 주기 갱신을 건다. (조합: 셸 + psql)

# 매시 정각 새로고침
0 * * * * psql -d shop -c "REFRESH MATERIALIZED VIEW CONCURRENTLY product_sales"

 

채워졌는지 여부를 시스템 카탈로그로 확인한다. (조합: pg_matviews)

SELECT matviewname, ispopulated FROM pg_matviews WHERE matviewname='product_sales';