1. 구체화된 뷰
Materialized View(이하 mv로 표기)는 이름 그대로 구체화된 뷰를 뜻한다.
view가 가상 테이블로서 조회 요청이 들어오면 기본 테이블에서 실시간으로 데이터를 계산하여 데이터를 반환한다면, mv는 쿼리의 결과를 물리적 테이블로 저장해놓고 조회 요청이 들어오면 즉시 결과를 반환한다.
view와는 다르게 물리적으로 저장된 상태이기 때문에 인덱스를 설정할 수 있고, 이로 인해 성능적인 면에서 이득을 볼 수 있다. 하지만 실시간으로 변경되는 데이터가 필요하다면, 새 result set 구성을 위해 refresh를 해야만 한다.
2. 실무에서의 MV 활용
처음 mv의 존재를 알게 된 것은 현재 회사에 입사하고 시스템을 익히기 시작한 무렵이었다. mv에 대해 실습을 하기에 앞서, 실무에서 MV가 필요하여 사용했던 경험에 대해 다뤄보고자 한다.
2.1. Foreign Table의 한계
현재 개발하고 있는 시스템은 PostgreSQL를 메인으로 사용하고 있는데, 필요에 따라 타 DB(Oracle)의 데이터를 사용해야하는 경우가 있었다. 이에 외부 DB에 접근할 수 있는 FDW(Foreign Data Wrapper)를 통해 Foreign Table을 구축해서 사용하고 있었다.
Foreign Table를 사용할 경우 다음과 같은 쿼리를 통해 실시간으로 oracle의 데이터를 조회할 수 있는데, 매번 네트워크를 통해 Oracle에 접근해야 하므로 성능 및 연결 관리에 아쉬운 점이 있었다.
SELECT * FROM oracle_employees WHERE department = 'IT';
따라서, 실시간으로 가져올 필요가 없는 데이터에 한해서 mv를 구성하여 개선할 수 있었다.
2.2. 비싼 JOIN 비용에 대한 대안
현재 내가 현재 속해있는 팀은 지도 관련 기능을 주로 개발하고 있다. 우리는 지도에 다양한 공간 객체를 시각화하기 위해 GeoServer라는 오픈 소스 WMS를 이용하고 있는데, 이를 통해 화면에 표시되는 특정 좌표 영역에 해당하는 공간 데이터를 이미지 형태로 렌더링하여 지도 위에 표출할 수 있다.
WMS(Web Map Service): 지도 이미지를 웹을 통해 제공하는 서비스
여기서 데이터 소스를 웹에서 서비스할 수 있도록 발행한 형태를 Layer라고 부르는데, 이 레이어는 경우에 따라 여러 테이블 간의 조인이 포함된 복합 쿼리를 사용해야만 했다. 당시, 내가 맡았던 기능 또한 2000만에 달하는 레코드를 가진 테이블을 join하고 CTE(임시 테이블)을 통해 작업을 해야하는 굉장히 무거운 쿼리를 사용해야만 했는데, 당연히 시스템에서 이미지를 받기까지 굉장히 오랜 시간이 걸려 골치였다.
이때의 대안으로 미리 필요한 연산을 마친 result set을 mv로 생성하여 레이어 응답 속도를 99% 이상 개선할 수 있었다.
3. MV 실습
실습은 PostgreSQL로 진행하며, 간단하게 user, order 테이블을 생성하고 데이터를 삽입해두었다.
CREATE table "user" (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
age INTEGER NOT NULL
);
CREATE table "order" (
id SERIAL PRIMARY KEY,
user_id INTEGER,
total_amount NUMERIC(10,2) NOT NULL,
status VARCHAR(20) DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
3.1. CREATE MATERIALIZED VIEW
이제 두 테이블을 join한 user_order_summary라는 mv를 생성할 것이다.
CREATE MATERIALIZED VIEW user_order_summary AS
SELECT
u.id AS user_id,
u.name AS user_name,
o.id AS order_id,
o.total_amount AS order_total_amount,
o.updated_at AS order_update_date
FROM "user" u
INNER JOIN "order" o ON u.id = o.user_id
WHERE o.status = 'completed';
CREATE INDEX user_idx ON user_order_summary (user_id);
다음과 같이 데이터가 제대로 저장된 것을 확인할 수 있다.
조회 시에는 다음과 같이 index를 사용하는 것을 확인할 수 있다.
explain analyze
select *
from user_order_summary
where user_id = 77;
3.2. REFRESH MATERIALIZED VIEW
refresh의 경우 다음의 쿼리로 실행할 수 있다.
REFRESH MATERIALIZED VIEW user_order_summary;
refresh 시에는 mv 생성 시 정의된 기본 쿼리가 다시 실행되며, 참조하는 테이블들의 현재 데이터를 기반으로 새로운 result set을 생성한다.
그리고 refresh 작업 중에는 조회를 비롯한 mv에 대한 모든 접근이 잠금으로 인해 불가하다.
그렇다면, refresh 작업 중에는 mv의 접근을 포기해야만 할까??
3.2. REFRESH MATERIALIZED VIEW CONCURRENTLY
다행히도 concurrently 옵션으로 refresh를 하면, refresh 작업을 실행하는 도중에도 mv의 조회가 가능하다.
REFRESH MATERIALIZED VIEW concurrently user_order_summary;
CONCURRENTLY REFRESH의 구체적인 동작 방식에 대해 알아보자.
1. PostgreSQL이 임시로 현재 데이터를 기반으로 구성된 mv를 생성한다.
2. unique 키를 기반으로 삽입할 행, 삭제할 행, 수정할 행을 식별하고 기존 mv와의 차이점을 계산한다.
3. 이를 기반으로 전체 재구성을 피하고 증분 업데이트를 실시한다.
4. refresh 작업 도중 읽기는 허용하되, DDL은 차단한다.
만약, unique key 없이 CONCURRENTLY REFRESH를 시도한다면, 다음과 같이 실패한다.
따라서, concurrently 옵션을 사용하고자 한다면 사전에 고유 인덱스를 생성해둬야 한다.
create unique index order_idx on public.user_order_summary USING btree (order_id);