PostgreSQL ビュー管理 | ビューの作成、更新、活用

はじめに

この実験（Lab）では、PostgreSQL のビュー管理について探求します。主な目標は、単純なビューやマテリアライズドビューなど、さまざまな種類のビューを理解し、実装することです。

まず、employeesテーブルに基づいて単純なビューを定義し、特定の列を選択するビューの作成方法を説明します。次に、ビューを介してデータをクエリ（query）し、場合によっては変更する方法を学びます。最後に、この実験（Lab）では、マテリアライズドビューの作成とデータの投入、およびこれらのビューを手動で更新して最新の状態に保つ方法について説明します。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL postgresql(("PostgreSQL")) -.-> postgresql/PostgreSQLGroup(["PostgreSQL"]) postgresql/PostgreSQLGroup -.-> postgresql/rows_add("Insert Multiple Rows") postgresql/PostgreSQLGroup -.-> postgresql/row_edit("Update Single Row") postgresql/PostgreSQLGroup -.-> postgresql/row_drop("Delete One Row") postgresql/PostgreSQLGroup -.-> postgresql/view_init("Create Basic View") postgresql/PostgreSQLGroup -.-> postgresql/view_drop("Drop Existing View") postgresql/PostgreSQLGroup -.-> postgresql/func_call("Call Stored Function") subgraph Lab Skills postgresql/rows_add -.-> lab-550966{{"PostgreSQL ビュー管理"}} postgresql/row_edit -.-> lab-550966{{"PostgreSQL ビュー管理"}} postgresql/row_drop -.-> lab-550966{{"PostgreSQL ビュー管理"}} postgresql/view_init -.-> lab-550966{{"PostgreSQL ビュー管理"}} postgresql/view_drop -.-> lab-550966{{"PostgreSQL ビュー管理"}} postgresql/func_call -.-> lab-550966{{"PostgreSQL ビュー管理"}} end

単純なビューの定義

このステップでは、PostgreSQL で単純なビューを定義する方法を学びます。ビューは、SQL ステートメントの結果セット（result-set）に基づく仮想テーブルです。これらは、複雑なクエリ（query）を簡素化し、抽象化を提供し、データアクセスを制御するのに役立ちます。

ビューの理解

ビューは基本的に保存されたクエリです。ビューをクエリすると、PostgreSQL は基になるクエリを実行し、実際（real）のテーブルであるかのように結果セットを返します。ビュー自体はデータを保存しません。ベーステーブルに保存されているデータに別の方法でアクセスできます。

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ビューの定義

データを含むテーブルができたので、単純なビューを作成しましょう。このビューには、各従業員のファーストネーム（first name）、ラストネーム（last name）、および部署（department）のみが表示されます。次の 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';

このクエリは、営業（Sales）部の従業員のみを返します。

ビューを介したデータの変更

特定の条件下では、ビューを介してデータを変更できます。ビューは、PostgreSQL が更新するベーステーブルと列を判断するのに十分単純である必要があります。一般に、ビューが次の基準を満たしている場合、ビューは変更可能です。

1 つのテーブルからのみ選択します。
集計関数（aggregate functions）（例：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ビューを介して従業員の給与（salary）を更新してみましょう。

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 の給与が更新されていることがわかります。

ビューを介したデータの挿入

ビューにベーステーブルの NULL を許容しない（non-nullable）すべての列が含まれている場合、ビューを介してデータを挿入することもできます。employee_detailsビューにはemployeesテーブルのすべての列が含まれているため、新しい従業員を挿入できます。

INSERT INTO employee_details (first_name, last_name, department, salary)
VALUES ('David', 'Lee', 'IT', 90000.00);

employee_idはシリアル（serial）列であり、自動的に生成されるため、指定しないことに注意してください。

挿入を確認します。

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';

重要な考慮事項

すべてのビューが変更可能であるとは限りません。結合（join）、集計（aggregation）、またはその他の複雑な操作を含む複雑なビューは、通常、読み取り専用です。
ビューを介してデータを変更すると、パフォーマンスに影響を与える可能性があります。PostgreSQL は、ビュー操作を基になるベーステーブルの操作に変換する必要があります。
ビューを介してデータを変更する場合は、変更がベーステーブルに直接影響するため、注意してください。

マテリアライズドビューの作成と投入

このステップでは、PostgreSQL でマテリアライズドビュー（materialized view）を作成し、投入する方法を学びます。通常（regular）のビューとは異なり、マテリアライズドビューはクエリの結果セット（result set）を物理テーブルとして保存します。これにより、特に複雑なクエリやリモートソース（remote sources）からデータにアクセスするクエリの場合、クエリのパフォーマンスを大幅に向上させることができます。ただし、マテリアライズドビューのデータは、基になるデータが変更されても自動的に更新されません。手動で更新するか、定期的に更新されるようにスケジュールする必要があります。

マテリアライズドビューの作成

マテリアライズドビューを作成するには、CREATE MATERIALIZED VIEWステートメントを使用します。各部署の平均給与（average salary）を示す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ステートメントを使用します。主なオプションは 2 つあります。

REFRESH MATERIALIZED VIEW view_name: これは、マテリアライズドビューを定義するクエリを再実行して、マテリアライズドビューを更新します。マテリアライズドビューに対してACCESS EXCLUSIVEロックを取得し、同時アクセスを防止します。
REFRESH MATERIALIZED VIEW CONCURRENTLY view_name: これは、同時クエリをブロックせずにマテリアライズドビューを更新します。ただし、マテリアライズドビューに少なくとも 1 つのインデックス（index）が必要です。

まず、標準のREFRESH MATERIALIZED VIEWコマンドを使用して、employee_salariesマテリアライズドビューを更新してみましょう。

REFRESH MATERIALIZED VIEW employee_salaries;

ここで、employee_salariesマテリアライズドビューを再度クエリします。

SELECT * FROM employee_salaries;

IT 部門の平均給与が、新しい従業員を反映するように更新されたことがわかります。

同時更新

マテリアライズドビューを同時に更新するには、まずインデックスを作成する必要があります。department列にインデックスを作成しましょう。

CREATE 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 シェルを終了することを忘れないでください。

まとめ

この実験（Lab）では、PostgreSQL で単純なビュー（view）を定義する方法を学びました。まず、従業員 ID、名、姓、部署、給与の列を含むサンプルデータ（sample data）を持つemployeesテーブルを作成しました。次に、employeesテーブルから名、姓、部署のみを選択するemployee_infoという名前のビューを定義し、ビューがクエリを簡素化し、基になるデータ（underlying data）に特定の視点を提供する方法を示しました。

また、ビューを介してデータをクエリおよび変更する方法、およびマテリアライズドビュー（materialized view）を作成および更新する方法も学びました。マテリアライズドビューは、クエリの結果をテーブルとして保存し、複雑なクエリのパフォーマンスを向上させます。中断を最小限に抑えるための同時更新（concurrent refreshing）など、マテリアライズドビューを更新するためのさまざまな方法を検討しました。