PostgreSQLのスキーマについて

Question

###質問
現在OSS-DB Silverの勉強をしているのですが、
スキーマ検索パスについて質問させてください。

公式サイトの例題解説にて、スキーマ検索パスの問題を解きました。
その中でfooというユーザで下記のSQLを打った時の状況です。

SET search_path TO “$user”,bar,public;
SELECT * from sample;
※この前にいくつかのスキーマ名を指定したsampleにデータを入れています。

このとき、スキーマ名を省略した場合はSETしたsearch_pathの優先度が高いものが選ばれて出力されるとありました。
では、search_pathを複数設定することは何の意味があるのでしょうか？

手元に実機がない為検証できていないのですが、
search_pathが通っていないスキーマにはアクセスできないという認識でいいのでしょうか？

初歩的な質問かもしれませんが、宜しくお願いします。

Answer 1

一例として、
共通テーブルやマスタテーブルをpublicとして、個々のテーブルを各スキーマに分けて同じテーブル定義でデータの分離に使ったりしました、（個々は店舗単位だったり）。
ログインユーザに引っ掛けてスキーマを定義して、プログラムはログイン（CONNECT)で可変にすれば、それぞれが独自のテーブルを持ち共通テーブルも使える様にしておりました。
かつてのDBがお高い頃で、一つのDBにテスト用のスキーマなど作成して、本番スキーマとデータの分離をさせたりもしてました。
結構スキーマの管理が大変だったと思います。なので私は今は余り使っていません。

Comments

[sityu]

ありがとうございます。
例えば、ユーザの操作履歴を残す際にユーザIDなどでスキーマをわけると
操作履歴の検索速度は格段に変わると思っていいのでしょうか？

[A.Ichi]

RDBは、データ量が大きくパフォーマンスに影響しますので分けると速度は速くなります。履歴系（Postgres）ならパーティションでも出来ます。パーティションは、100個以下がよろしいかと。

[sityu]

わかりやすく理解できました。
ありがとうございました！

Answer 2

SELECT文などでアクセスするテーブルは、本来どのオーナーのどのテーブル・ビューなのかを示すためにSELECT * FROM public.table_aみたいな書き方になりますが、search_path を設定することによってpublic.の部分を省略して書けるようになる、というのがひとつ目のメリット。（ちょっとでもSQL文が短くなって読みやすくなります。）

publicの他、他のオーナーも同じ名前のテーブルやビューを持っている場合でも、search_path を設定することによって自分にとってのテーブルはどこから探すかを明示できるようになる、というのがふたつ目のメリット。（私にとってtable_aはpublic配下にあってもbar配下にあっても自分のところのを使うぞという宣言。）

でも、（アクセス可能な権限が与えられていれば）オーナー名付きでfooさんがSELECT * FROM bar.table_aも実行できるので、SQL文の可読性を上げることの意味が強い気がします。

参考：18.11. クライアント接続デフォルト
http://www.postgresql.jp/document/9.5/html/runtime-config-client.html#runtime-config-client-statement

Comments

[sityu]

回答ありがとうございます。
search_pathを複数設定する意味はどこにあるのでしょうか？
仮に　foo,bar,publicと設定した場合に、fooスキーマが無ければbarスキーマを見るという意味合いでしょうか？