VIEW와 MATERIALIZED VIEW 비교 (postgresql)

● 요구사항

방대한 데이터를 테이블에 적재할 경우 쿼리 수행시간이 오래 걸리는 이슈가 발생하였다.

원시 데이터를 가공하여 data mart를 구성할 수도 있겠지만

동일한 스키마내의 데이터를 가공하면 되기 때문에 view를 활용하기로 하였다.

 

● postgreSQL 의 view

1. CREATE VIEW
- CREATE VIEW 명령은 정의된 쿼리를 뷰로 정의한다.
- 뷰는 물리적으로 구체화되지 않습니다.
- 뷰를 호출하면 매번 그 뷰에 지정한 쿼리를 실행한다.
- 뷰의 이름은 동일한 스카마에 있는 뷰, 테이블, 시퀀스, 인덱스 이름과 구별되어야 합니다.

- document URL : https://www.postgresql.org/docs/14/sql-createview.html

CREATE VIEW comedies AS
    SELECT *
    FROM films
    WHERE kind = 'Comedy';

2. CREATE MATERIALIZED VIEW

- CREATE MATERIALIZED VIEW 명령은 정의된 쿼리에 대해 구체화한 뷰를 정의한다.

- 쿼리가 실행될 때 물리적 데이터를 만들어 저장한다.

- REFRESH MATERIALIZED VIEW 명령으로 만들어진 데이터를 갱신한다.

- 구체화한 뷰는 테이블과 비슷하기 때문에, 테이블에서 사용하는 대부분 속성들을 사용한다.(index등)

-  document URL : https://www.postgresql.org/docs/14/sql-creatematerializedview.html
                         https://www.postgresql.org/docs/14/sql-refreshmaterializedview.html

CREATE MATERIALIZED VIEW comedies AS
    SELECT *
    FROM films
    WHERE kind = 'Comedy';

REFRESH MATERIALIZED VIEW comedies;

3. 요약

	일반 VIEW	MATERIALIZED VIEW
동작 방식	실행시 정의된 쿼리를 실행하여 결과를 리턴	정의된 쿼리를 미리 실행하여 물리적으로 데이터를 가지고 있으며 그 데이터 내에서 결과를 리턴
데이터 싱크	실행시 원시 데이터를 조회	REFRESH MATERIALIZED VIEW 를 통해 싱크
장점	데이터 싱크 불 필요	이미 데이터가 생성되어 있기 때문에 조회속도가 빠르다
단점	정의된 쿼리 속도가 느리면 view도 느리다.	데이터 싱크를 주기적으로 맞춰야 한다.

 

4. 성능 비교

stats에 30만건을 입력하여 아래 데이터를 view와 MATERIALIZED VIEW를 생성하여 field1가 1000이상인 데이터를 조회하였다.

SELECT
	target_id,
	date,
	hour,
	field1 AS field1,
	field2 AS field2,
	field3 AS field3,
	SUM(field4) AS field4,
	SUM(field5) AS field5,
	SUM(field6) AS field6,
	SUM(field7) AS field7,
	SUM(field8) AS field8,
	SUM(field9) AS field9,
	SUM(field10) AS field10
FROM
	stats
GROUP BY
	target_id, date, hour
;

- 일반 VIEW 실행계획

                                            QUERY PLAN
-----------------------------------------------------------------------------------------------
GroupAggregate  (cost=0.93..38992.62 rows=15438 width=92)
  Group Key: stats.target_id, stats.date, stats.hour
  Filter: (sum(stats.field1) > 1000)
  ->  Incremental Sort  (cost=0.93..28668.35 rows=299857 width=52)
        Sort Key: stats.target_id, stats.date, stats.hour
        Presorted Key: stats.target_id
        ->  Index Scan using idx_target_id on stats  (cost=0.42..16243.14 rows=299857 width=52)

- MATERIALIZED VIEW 실행계획

                                QUERY PLAN
-----------------------------------------------------------------------
Seq Scan on mater_view_stats  (cost=0.00..7886.14 rows=279952 width=92)
  Filter: (field1 > 1000)

	일반 VIEW	MATERIALIZED VIEW
startup cost	0.93	0.00
total cost	38992.62	7886.14

 

5. 결론
통계 함수나 복잡한 조인으로 시간이 오래 걸리는 쿼리의 경우 MATERIALIZED VIEW로 만들경우 물리적 데이터를 만들어 해당 데이터에서 조회를 하는 것을 확인하였다.

그로인해 속도 향상을 기대해 볼수 있다.