이 실습에서는 PostgreSQL 뷰 관리를 탐색합니다. 주요 목표는 단순 뷰와 물리적 뷰를 포함한 다양한 유형의 뷰를 이해하고 구현하는 것입니다.
employees 테이블을 기반으로 단순 뷰를 정의하여 특정 열을 선택하는 뷰를 만드는 방법을 보여줍니다. 그런 다음 뷰를 통해 데이터를 쿼리하고 잠재적으로 수정하는 방법을 배웁니다. 마지막으로, 이 실습에서는 물리적 뷰를 생성하고 채우는 방법과 이러한 뷰를 최신 상태로 유지하기 위한 수동 새로 고침에 대해 다룹니다.
이 단계에서는 PostgreSQL 에서 단순 뷰를 정의하는 방법을 배웁니다. 뷰는 SQL 문의 결과 집합을 기반으로 하는 가상 테이블입니다. 복잡한 쿼리를 단순화하고 추상화를 제공하며 데이터 접근을 제어하는 데 유용합니다.
뷰 이해
뷰는 본질적으로 저장된 쿼리입니다. 뷰를 쿼리할 때 PostgreSQL 은 기본 쿼리를 실행하고 실제 테이블인 것처럼 결과 집합을 반환합니다. 뷰는 데이터 자체를 저장하지 않고 기본 테이블에 저장된 데이터에 액세스하는 다른 방법을 제공합니다.
employees 테이블 생성
먼저 작업할 employees 테이블을 생성해 보겠습니다. 터미널을 열고 postgres 사용자로 PostgreSQL 데이터베이스에 연결합니다.
sudo -u postgres psql이제 employees 테이블을 생성합니다.
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10, 2)
);다음으로 employees 테이블에 샘플 데이터를 삽입합니다.
INSERT INTO employees (first_name, last_name, department, salary) VALUES
('John', 'Doe', 'Sales', 60000.00),
('Jane', 'Smith', 'Marketing', 75000.00),
('Robert', 'Jones', 'Sales', 62000.00),
('Emily', 'Brown', 'IT', 80000.00),
('Michael', 'Davis', 'Marketing', 70000.00);다음 쿼리를 실행하여 데이터를 확인할 수 있습니다.
SELECT * FROM employees;출력에서 삽입된 데이터를 볼 수 있어야 합니다.
employee_info 뷰 정의
이제 데이터가 있는 테이블이 있으므로 단순 뷰를 생성해 보겠습니다. 이 뷰는 각 직원의 이름, 성 및 부서만 표시합니다. 다음 SQL 문을 사용하여 employee_info라는 뷰를 정의할 수 있습니다.
CREATE VIEW employee_info AS
SELECT first_name, last_name, department
FROM employees;이 문은 employees 테이블에서 first_name, last_name, department 열을 선택하는 employee_info라는 뷰를 생성합니다.
뷰 쿼리
뷰를 쿼리하려면 일반 테이블과 마찬가지로 SELECT 문을 사용할 수 있습니다.
SELECT * FROM employee_info;이 쿼리는 뷰에 정의된 대로 모든 직원의 이름, 성 및 부서를 반환합니다.
뷰 설명
psql에서 \d 명령어를 사용하여 뷰를 설명할 수 있습니다.
\d employee_info이렇게 하면 뷰 정의와 포함된 열을 볼 수 있습니다.
이 단계에서는 PostgreSQL 에서 뷰를 통해 데이터를 쿼리하고 수정하는 방법을 배웁니다. 뷰는 주로 데이터를 쿼리하는 데 사용되지만, 경우에 따라 기본 테이블의 기본 데이터를 수정하는 데도 사용될 수 있습니다.
뷰를 통한 데이터 쿼리
이전 단계에서 보여준 것처럼 뷰를 통해 데이터를 쿼리는 간단합니다. SELECT 문을 사용하여 일반 테이블인 것처럼 뷰에서 데이터를 검색할 수 있습니다.
예를 들어, employee_info 뷰의 모든 데이터를 검색하려면 다음과 같이 합니다.
SELECT * FROM employee_info;WHERE 절과 다른 SQL 구문을 사용하여 데이터를 필터링하고 정렬할 수도 있습니다.
SELECT * FROM employee_info WHERE department = 'Sales';이 쿼리는 영업 부서의 직원만 반환합니다.
뷰를 통한 데이터 수정
뷰를 통해 데이터를 수정하는 것은 특정 조건 하에서 가능합니다. 뷰는 PostgreSQL 이 업데이트할 기본 테이블과 열을 결정할 수 있을 만큼 충분히 단순해야 합니다. 일반적으로 뷰가 수정 가능하려면 다음 기준을 충족해야 합니다.
SUM, AVG, COUNT) 를 포함하지 않습니다.GROUP BY, HAVING 또는 DISTINCT 절을 포함하지 않습니다.업데이트를 더 쉽게 하기 위해 employee_id를 포함하는 다른 뷰를 만들어 보겠습니다.
CREATE VIEW employee_details AS
SELECT employee_id, first_name, last_name, department, salary
FROM employees;이제 employee_details 뷰를 통해 직원의 급여를 업데이트해 보겠습니다.
UPDATE employee_details
SET salary = 65000.00
WHERE employee_id = 1;이 문은 employee_id가 1 인 직원의 급여를 65000.00 으로 업데이트합니다.
employees 테이블을 직접 쿼리하여 업데이트를 확인할 수 있습니다.
SELECT * FROM employees WHERE employee_id = 1;employee_id가 1 인 직원의 급여가 업데이트되었음을 확인할 수 있습니다.
뷰를 통한 데이터 삽입
뷰를 통해 데이터를 삽입할 수도 있습니다. 뷰에는 기본 테이블의 모든 필수 열이 포함되어 있어야 합니다. employee_details 뷰에는 employees 테이블의 모든 열이 포함되어 있으므로 새 직원을 삽입할 수 있습니다.
INSERT INTO employee_details (first_name, last_name, department, salary)
VALUES ('David', 'Lee', 'IT', 90000.00);employee_id를 지정하지 않은 이유는 시리얼 열이기 때문에 자동으로 생성되기 때문입니다.
삽입을 확인합니다.
SELECT * FROM employees WHERE first_name = 'David' AND last_name = 'Lee';뷰를 통한 데이터 삭제
마찬가지로 수정 가능한 뷰를 통해 데이터를 삭제할 수 있습니다.
DELETE FROM employee_details WHERE first_name = 'David' AND last_name = 'Lee';삭제를 확인합니다.
SELECT * FROM employees WHERE first_name = 'David' AND last_name = 'Lee';중요 고려 사항
이 단계에서는 PostgreSQL 에서 물리적 뷰를 생성하고 채우는 방법을 배웁니다. 일반 뷰와 달리 물리적 뷰는 쿼리의 결과 집합을 실제 테이블로 저장합니다. 이는 특히 복잡한 쿼리 또는 원격 소스의 데이터에 액세스하는 쿼리의 경우 쿼리 성능을 크게 향상시킬 수 있습니다. 그러나 물리적 뷰의 데이터는 기본 데이터가 변경되면 자동으로 업데이트되지 않습니다. 수동으로 새로 고쳐야 하거나 주기적으로 새로 고쳐지도록 예약해야 합니다.
물리적 뷰 생성
물리적 뷰를 생성하려면 CREATE MATERIALIZED VIEW 문을 사용합니다. 각 부서의 평균 급여를 보여주는 employee_salaries라는 물리적 뷰를 생성해 보겠습니다.
CREATE MATERIALIZED VIEW employee_salaries AS
SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;이 문은 employees 테이블의 데이터를 기반으로 각 부서의 평균 급여를 계산하는 employee_salaries라는 물리적 뷰를 생성합니다.
물리적 뷰 쿼리
물리적 뷰는 일반 테이블과 마찬가지로 쿼리할 수 있습니다.
SELECT * FROM employee_salaries;이렇게 하면 employees 테이블에 있던 데이터를 기준으로 각 부서의 부서와 평균 급여가 반환됩니다. (물리적 뷰가 생성되었을 때의 데이터).
물리적 뷰 채우기
물리적 뷰를 생성하면 초기 데이터로 자동으로 채워집니다. 그러나 employees 테이블의 기본 데이터가 변경되면 employee_salaries 물리적 뷰의 데이터는 자동으로 업데이트되지 않습니다.
employees 테이블에 새 직원을 삽입해 보겠습니다.
INSERT INTO employees (first_name, last_name, department, salary) VALUES
('Alice', 'Johnson', 'IT', 85000.00);이제 employee_salaries 물리적 뷰를 다시 쿼리하면:
SELECT * FROM employee_salaries;새 직원을 반영하여 IT 부서의 평균 급여가 변경되지 않은 것을 알 수 있습니다. 이는 물리적 뷰가 새로 고쳐지지 않았기 때문입니다.
물리적 뷰 설명
psql에서 \d 명령어를 사용하여 물리적 뷰를 설명할 수 있습니다.
\d employee_salaries이렇게 하면 물리적 뷰 정의와 포함된 열을 볼 수 있습니다.
이 단계에서는 PostgreSQL 에서 구체화된 뷰를 수동으로 새로고침하는 방법을 배웁니다. 이전 단계에서 언급했듯이, 구체화된 뷰는 기본 데이터가 변경되어도 자동으로 업데이트되지 않습니다. 최신 데이터를 반영하려면 명시적으로 새로고침해야 합니다.
구체화된 뷰 새로고침
구체화된 뷰를 새로고침하려면 REFRESH MATERIALIZED VIEW 문을 사용합니다. 두 가지 주요 옵션이 있습니다.
REFRESH MATERIALIZED VIEW view_name: 이 명령은 구체화된 뷰를 정의하는 쿼리를 다시 실행하여 새로고침합니다. 구체화된 뷰에 ACCESS EXCLUSIVE 잠금을 획득하여 동시 접근을 방지합니다.
REFRESH MATERIALIZED VIEW CONCURRENTLY view_name: 이 명령은 동시 쿼리를 차단하지 않고 구체화된 뷰를 새로고침합니다. 하지만 이를 위해서는 구체화된 뷰에 하나 이상의 인덱스가 있어야 합니다.
먼저 표준 REFRESH MATERIALIZED VIEW 명령을 사용하여 employee_salaries 구체화된 뷰를 새로고침해 보겠습니다.
REFRESH MATERIALIZED VIEW employee_salaries;이제 employee_salaries 구체화된 뷰를 다시 쿼리합니다.
SELECT * FROM employee_salaries;IT 부서의 평균 급여가 이제 새 직원을 반영하도록 업데이트된 것을 볼 수 있습니다.
동시 새로고침
구체화된 뷰를 동시적으로 새로고침하려면 먼저 해당 뷰에 UNIQUE 인덱스를 생성해야 합니다. 이는 동시 새로고침의 요구 사항으로, PostgreSQL 은 전체 뷰를 잠그지 않고 새로고침을 수행하기 위해 행을 고유하게 식별할 방법이 필요하기 때문입니다. employee_salaries 뷰의 department 열은 뷰가 부서별로 그룹화되므로 고유하므로 해당 열에 고유 인덱스를 생성할 수 있습니다.
department 열에 고유 인덱스를 생성합니다.
CREATE UNIQUE INDEX idx_employee_salaries_department ON employee_salaries (department);이제 구체화된 뷰를 동시적으로 새로고침할 수 있습니다.
REFRESH MATERIALIZED VIEW CONCURRENTLY employee_salaries;데이터가 여전히 최신 상태인지 확인하기 위해 employee_salaries 구체화된 뷰를 다시 쿼리합니다.
SELECT * FROM employee_salaries;올바른 새로고침 방법 선택
REFRESH MATERIALIZED VIEW를 사용합니다.REFRESH MATERIALIZED VIEW CONCURRENTLY를 사용합니다. 먼저 구체화된 뷰에 인덱스를 생성하는 것을 잊지 마십시오.중요 고려 사항
cron과 같은 도구를 사용하여 정기적인 새로고침을 예약하여 구체화된 뷰의 데이터를 최신 상태로 유지하는 것을 고려하십시오.\q를 입력하고 Enter 키를 눌러 psql 셸을 종료하십시오.
이 실습에서는 PostgreSQL 에서 간단한 뷰를 정의하는 방법을 배웠습니다. employees 테이블을 생성하고 직원 ID, 이름, 성, 부서 및 급여와 같은 열을 포함한 샘플 데이터로 시작했습니다. 그런 다음 employees 테이블에서 이름, 성 및 부서만 선택하는 employee_info라는 뷰를 정의하여 뷰가 쿼리를 단순화하고 기본 데이터에 대한 특정 관점을 제공하는 방법을 보여주었습니다.
뷰를 통해 데이터를 쿼리하고 수정하는 방법과 물리적 뷰를 생성하고 새로 고치는 방법도 배웠습니다. 물리적 뷰는 쿼리 결과를 테이블로 저장하여 복잡한 쿼리의 성능을 향상시킵니다. 중단을 최소화하기 위한 동시 새로 고침을 포함한 물리적 뷰를 새로 고치는 다양한 방법을 탐색했습니다.
