[ 살펴보기 ] PostgreSQL - View

PostgreSQL에서 view라는 일종의 가상 테이블을 생성하여 생성한 view를 통해 특정 table의 data를 조회할 수 있다. 예를 들어 다음은 user table에서 id와 name column만 반환하는 view를 생성하는 예제다.

CREATE VIEW users_name AS
SELECT id, name FROM users;

위의 statement를 통해 생성한 view는 다음 예제와 같이 조회할 수 있다.

SELECT * FROM users_name;

단순 조회 뿐만 아닌 WHERE와 같은 keyword를 통해 원하는 결과를 filter할 수도 있다. 예제를 통해 볼 수 있듯이 View의 data를 조회하는 방법은 일반 table를 통해 조회하는 방법과 다르지 않다.

SELECT * FROM users_name WHERE id = 27;

View를 생성할 때 다음과 같이 특정 조건에 부합하는 data만 반환하는 view를 생성하여 사용할 수도 있다. 아래는 나이가 20세 이상인 user만 반환하는 view를 생성하는 예제다.

CREATE VIEW users_adult AS
SELECT id, name FROM users WHERE age >= 20;

민감한 데이터를 포함하고 있는 table이라면 이러한 view를 만들어 놓고 table에 직접 query하는 것이 아닌 만들어진 view를 통해서만 데이터를 조회하는 방법이 있다. 예를 들어 user table 중 이름의 두 번째 글자만 *로 표시하고 싶다면 다음과 같이 view를 생성해서 사용할 수 있다.

CREATE VIEW users_name AS
SELECT id, concat(substr(name,1,1),'*',substr(name,3)) AS name FROM users;

위의 statement를 통해 생성된 view를 조회하면 다음과 같이 name column 중 두 번째 글자만 *로 대체되어 조회된다.

View는 데이터의 보안 뿐만 아니라 복잡한 join이 필요한 데이터는 미리 view로 생성하여 사용할 수도 있다. 예를 들어 다음과 같이 여러 join이 필요한 데이터를 미리 view로 생성해 두면 데이터를 조회할 때 해당 view를 통해 쉽게 데이터를 조회할 수 있다.

CREATE VIEW payment_category
AS
SELECT c.category_title AS category, 
sum(p.amount) AS sum_payment 
FROM payment AS p
INNER JOIN rental AS r ON p.rental_id  = r.rental_id 
INNER JOIN category AS c ON c.category_id  = r.category_id
GROUP BY c.category_title

생성한 View를 삭제할 때는 table과 마찬가지로 DROP command를 사용한다.

DROP VIEW users_name;

CREATE VIEW command를 통해 생성된 view는 table과는 달리 데이터를 실제로 저장하지는 않는다. 대신 view를 조회할 때 마다 view를 생성할 때 정의한 sql statement가 실행되어 결과를 반환해준다.

Materialized View

일반 view와는 달리 materialized view는 실제 table과 같이 데이터를 저장한다. 시간이 다소 소요될 수 있는 expensive query의 결과를 materialzed view를 통해 저장해 두었다가 필요할 때 materialzed view를 통해 필요한 data를 빠르게 조회할 수 있다.

Materialized view는 다음과 같이 CREATE MATERIALIZED VIEW statement를 통해 생성한다.

CREATE MATERIALIZED VIEW users_seoul
AS SELECT * FROM users WHERE city = 'seoul';

위의 예제는 users_seoul라는 이름을 가진 materialized view를 생성한다. 그리고 materialized view가 생성될 때 users table에서 거주지가 seoul인 모든 user 데이터가 materialized view에 저장된다.

생성한 materialized view를 조회하는 방법은 일반 view를 조회하는 방법과 동일하다.

SELECT * FROM users_seoul;

주의할 점은 materialed view는 일반 view와 같이 view를 조회할 때 마다 AS statement 이후에 정의한 sql statement를 실행하지 않는다. 즉, materialized view가 반환하는 data는 별도로 refresh하지 않는 한 최신 데이터가 아닌 materialized view가 생성될 때 저장 되었던 데이터를 그대로 반환한다.

예를 들어 다음과 같이 새로운 데이터를 입력하고 materialized view를 조회해도 새로운 데이터는 조회되지 않는다.

INSERT INTO users (name, email, city) 
VALUES ('test name', 'test email', 'seoul')

SELECT * FROM users_seoul; 
/* 새로 추가한 test name user는 조회되지 않는다 */

만약 materialized view에 저장되어 있는 data를 최신 data로 업데이트 하고 싶다면 REFRESH keyword를 통해 업데이트 할 수 있다.

REFRESH MATERIALIZED VIEW users_seoul;

위의 statement를 실행하고 다시 users_seoul materialized view를 조회해 보면 새로 추가한 test name user 정보가 조회 결과에 포함되어 있는 것을 확인할 수 있다.

REFRESH statement를 통해 materialized view 데이터를 업데이트 할 때 업데이트가 완료될 때 까지 materialized view가 데이터를 조회하는 table은 lock이 되므로 주의하자.

만약 materialized view를 생성할 때 다음과 같이 WITH NO DATA option을 사용하면 materialized view가 생성될 때 아무런 데이터를 저장하지 않은 상태로 생성되며 데이터가 materialized view에 추가될 때 까지 해당 materialized view를 조회할 수 없다.

CREATE MATERIALIZED VIEW users_seoul
AS SELECT * FROM users WHERE city = 'seoul' WITH NO DATA;

위에서 생성한 materialized view를 조회하면 오류가 발생한다. 그렇기에 WITH NO DATA option을 통해 생성된 materialized view는 다음과 같이 새로운 데이터를 우선 저장하고 조회를 진행한다.

REFRESH MATERIALIZED VIEW users_seoul;

Materialized view를 삭제할 때는 일반 view와 마찬가지로 DROP statement를 사용한다.

DROP MATERIALIZED VIEW users_seoul;