【PostgreSQL】PostgreSQLのスキーマ(Schema)について

こんにちは。プロダクト事業部のしげぞうです!

先日、新人さん向けの社内教育で PostgreSQL を取り扱ったのですが、新人さんに何の気なしに PostgreSQLスキーマ(Schema) について質問を受けました。情けないことにこれまでの業務であまり意識して使ってこなかったこともあって、納得いく説明ができず。。(先輩しっかりしなさい!)

そこで今回は、PostgreSQLスキーマ(Schema) について自分なりに調べてまとめてみました。

スキーマとは

スキーマ(Schema) とは、1つのデータベースの中に複数設定することができる 名前空間 のことを指すそうです。

PostgreSQL では、DBを構成するデータベースオブジェクト(テーブルやビュー、インデックスなど)は、全てスキーマという名前空間の下に配置されます。「じゃあ、どんなスキーマがあるの?」ということですが、PostgreSQL でデータベースを新規で作成(CREATE DATABASE)すると、そのデータベース配下に下記の6つのスキーマがデフォルトで作成されます。

スキーマ 名称 概要
information_schema インフォメーションスキーマ データベースのメタデータを取得するために利用されるビューの集まり。標準SQL(PostgreSQL独自のSQLでない)で定義されており、MySQLやOracleなどの他のDBでも存在している。
pg_catalog システムカタログ PostgreSQLがシステム的に備えているオブジェクトを格納しているスキーマ。pg_catalogの中に存在するオブジェクトは基本的にpg_という名前で始まる。
pg_toast TOAST(The Oversized-Attribute Storage Technique) PostgreSQLで可変長データ型bytea, varchar(n)など)のデータを扱えるようにするTOASTの仕組みを実行するスキーマ。
pg_temp_[バックエンドID] 一時テーブル用のスキーマ 一時テーブル(CREATE TEMP TABLEで作成したテーブル)を格納するスキーマ。セッションごとにスキーマが作成され、バックエンドIDという番号で区切られる。
pg_toast_temp_[バックエンドID] TOAST用の一時スキーマ TOAST処理にて作成された一時テーブルが格納されるスキーマ。
public 標準で使用されるスキーマ テーブルやインデックスなどを新規作成する場合は、基本このスキーマの下に作成される。

スキーマは自由に定義できる

データベース作成時にデフォルトで6つのスキーマが作成されますが、新規で定義することも可能です。
新規で定義する場合、下記のようなクエリを実行します。

-- スキーマの作成
-- "test_schema" というスキーマを作成
CREATE SCHEMA test_schema;

-- ユーザーと紐づくスキーマの作成
-- "schemaA" というスキーマを作成し、スキーマの所有者は "userA"
CREATE SCHEMA schemaA AUTHORIZATION userA;
-- スキーマ名は省略可能。ユーザー名がスキーマ名となる
CREATE SCHEMA userB;

作成したスキーマはもちろん削除も可能です。削除する場合は、事前に削除対象スキーマの所有するオブジェクトをすべて削除しておかなければならないので注意が必要です。

-- スキーマの削除
-- "CASCADE"は、スキーマ削除時にオブジェクトも合わせて削除するオプション
DROP SCHEMA test_schema CASCADE;

どのスキーマを参照するか決定するスキーマ検索パス

スキーマを意識してデータベース上のテーブルをクエリで操作する場合は、テーブル名に対象とするスキーマ名の修飾子を指定します。(SELECTだとSELECT * FROM test_schema.foods;といった感じ)

しかし、実際のところテーブル名の前に修飾子をつけてクエリを実行する場面は少なく、毎回修飾子でスキーマの指定をしていると手間になるかと思います。そこで PostgreSQL ではクエリをどのスキーマに対して実行するかを内部で決めてくれる スキーマ検索パスsearch_path)という仕組みが存在しています。

スキーマ検索パス は下記のコマンドで確認できます。

SHOW search_path;

実際に実行してみると、デフォルトの設定の場合は以下のような結果が返ってきます。

testdb=# SHOW search_path;
   search_path
-----------------
 "$user", public
(1 row)

※ "$user"はデータベースユーザーのスキーマ

スキーマ検索パス は、カンマ区切りでスキーマを指定していきます。 PostgreSQL ではこの スキーマ検索パス に設定したスキーマのリストに従って、どのスキーマを参照するかを判断します。リストに指定したスキーマは 左から順に 優先して参照され、参照したスキーマ内に対象のテーブルが存在する場合はそのスキーマが対象スキーマとなります。 参照しようとしたスキーマが存在しない or スキーマ内に対象のテーブルが存在しない 場合は、リストの次に指定されているスキーマをチェックします。

ちなみに、デフォルトでデータベースオブジェクトがpublicスキーマ内に作成されるのは、このsearch_pathの最後にpublicが指定されているためです。
スキーマ検索パス の設定を変更する場合は、以下のようなコマンドで変更を行います。

-- スキーマ検索パスの変更
SET search_path TO schemaA,schemaB,schemaC,public;

最後に

今回は、これまでの業務で気にしてこなかった スキーマ についてまとめてみました。スキーマの仕組みは、スキーマ検索パスを利用してユーザーごとに参照できるテーブルを切り替えたい運用をしたい場合に有効的なのかなと思いました。

今後も PostgreSQL で気になった機能や仕様などについて、引き続き調べていこうと思います!

参考

コメントを残す

%d人のブロガーが「いいね」をつけました。