PostgreSQL な日々

ビュー

データベースには、ビューと呼ばれるものが存在します。普通、テーブルからデータを表示するときには、select 文を使いますが、これをビューとして登録しておくことが出来ます。以前利用した meibo テーブルを元に、年齢が 23 才のデータだけ選択する select 文があったとします。

=> insert into meibo values ('Jangon Source', 23, 'Public');
INSERT 16607 1
=> insert into meibo values ('Splint Coocer', 23, 'Onittor');
INSERT 16608 1
=> select * from meibo;
      name       | age |      address
-----------------+-----+--------------------
 Atsuzaki Hizuya |  23 | Tokyo dokoka 1-2-3
 Ryouma Sakamoto |  31 | Kouchi Tosa
 Jangon Source   |  23 | Public
 Splint Coocer   |  23 | Onittor
(4 rows)

=> select * from meibo where age = 23;
      name       | age |      address
-----------------+-----+--------------------
 Atsuzaki Hizuya |  23 | Tokyo dokoka 1-2-3
 Jangon Source   |  23 | Public
 Splint Coocer   |  23 | Onittor
(3 rows)

そしてこの select 文を age23 という名前のビューにします。

=> create view age23 as select * from meibo where age = 23;
CREATE

ビューは、テーブルと同じような操作が出来ます。

=> select * from age23;
      name       | age |      address
-----------------+-----+--------------------
 Atsuzaki Hizuya |  23 | Tokyo dokoka 1-2-3
 Jangon Source   |  23 | Public
 Splint Coocer   |  23 | Onittor
(3 rows)

=> select * from age23 where name like '%o%';
      name       | age |      address
-----------------+-----+--------------------
 Jangon Source   |  23 | Public
 Splint Coocer   |  23 | Onittor
(2 rows)

このように、ある一定の条件を付けた SQL 文を、ビューとして登録しておき、そのビューを select することにより、毎回条件を指定しなくてもすむようになります。私の経験ですが、今回の例のような、where 句を付けたビューより、副問い合わせや、外部結合（後で説明します）などを使った select 文をビューとして登録するケースが多いようです。

インデックス

さて、PostgreSQL もデータベースですから、たくさんのデータを登録する場合があると思います。データベースの例に漏れず、こういった場合インデックスを張る必要が出てきます。たとえば、where 句で code = 1 と言った検索をするとき、code のインデックスを作っておけば、インデックスを元に検索をします。もしインデックスがない場合は、そのテーブルの該当する全てのカラムを探す必要が出てきます。これは数百件というデータ量ならば対して気になりませんが、数万件という単位になると、かなり違ってきます。したがって、検索に使われているカラムには、インデックスを作るのが基本です（例外については後述します）。では、実際にインデックスを作ってみましょう。

=> create index profile_code on profile (code);

この例では、profile というテーブルの code というカラムにインデックスを作っています。profile_code と言うのは、インデックス名です。実際には何でもかまいません。こういう風に、インデックスを作っておくと、where 句での検索の際に、インデックスを使って検索をしてくれます。

インデックスには、ユニークインデックスと呼ばれるものがあります。これは、インデックスに格納する値が重複しないのを保証します。次のようにインデックスを作った場合、ユニークインデックスになります。

=> create unique index profile_code on profile (code);

これの利点は、データを挿入（insert）するときに、code に同じ値が入っていた場合、エラーとなって登録できなくなります。同じデータを登録したくないときは、このユニークインデックスを作ると便利です。また、速度面でも、ただのインデックスより、全然速く検索を行うことができます。

実行速度

データ量が多くなってくると、SQL の実行速度が問題となってくる場合があります。これの解決方法として、インデックスを作ることを説明しました。ただし、注意しなければいけないのは、必ずしもインデックスを作ると速くなるわけではないということです。良く言われることですが、値の分布が小さいときにはインデックスを作るべきではありません。例えば、性別のフィールドがあったとします。そのカラムには、「男」と「女」という２種類の値しか入っていなかったとします。こういったっ場合は、間違いなくインデックスを作ると遅くなってしまいます。逆に、ユニークインデックスのように値の重複しないカラムに対してはもっとも効果が高いのです。

インデックスを有効に使うには、定期的に vacuumdb コマンドを実行する必要があります。これは、Oracle などではなじみの深い統計情報を生成するアナライザです（それ以外に、一度利用されて、今は空いている保存領域を再利用できるようにもします）。特にオプションを指定しないと、テーブルごとにデータ件数など簡単な統計情報を生成しますが、-zオプションを指定すると、各列ごとにデータ分布の統計情報を収集するようになります。もちろん、-z を付けて実行したほうがいいと思うのですが、データ件数が多い場合は vacuumdb コマンドの実行にとても時間がかかってしまいます。vacuumdb コマンドを使うかどうか、-z オプションを付けるかどうか、というのは、実行時のパフォーマンスとデータベースの運用方法の問題ですので、よく検討してください。

vacuumdb コマンドは、実際には vacuum という SQL 文を実行しています。実行環境によって、vacuumdb コマンドか vacuum SQL 文の好きなほうを使うことが出来ます。PostgreSQL 7.2 から、vacuum 文と analyze 文が分離されています。vacuum analyze 文と analyze 文は等価のようです。また、以前は vacuum 文の実行中は、テーブルロックが発生しましたが、7.2 からはロックされません。そのかわり、テーブルサイズも小さくならなくなったので、以前と同じようにテーブルサイズを縮めたい場合は、vacuum full 文（vacuumdb -f コマンド）を使う必要があります（この場合、テーブルロックが発生します）。

この vacuumdb によって得た統計情報は、検索の際に利用されます。統計情報は、データを追加・更新・削除していくうちに古くなってしまうので（vacuum 以外では統計情報が更新されないため、実際のデータと統計情報が違ってくる）、日に一度や、週に一度など、定期的に実行するようにしましょう。cron になど登録しておくと良いかもしれません（cron については、man 5 crontab などとして、マニュアルを読んでください）。

> crontab -l
00 04 * * * /usr/local/pgsql/bin/vacuumdb -t テーブル名 データベース名

vacuumdb は、-t オプションを指定して、テーブル名をつけなければ、全テーブルに対して実行を行います。

後の方の、SQL チューニングでもう少し詳しく説明しています。

システムカタログ

PostgreSQL は当然のことながら、多数のシステムカタログを持ちます。他の RDBMS などでは、システムディクショナリと言っているかも知れません。システムカタログとは、データベースの内部的な情報を格納している特殊なテーブル（ビューの場合もある）で、データベースに必ず存在するものです。データベースの状態が変わると、自動的にこの中に格納されている値も変更されます。したがって、このシステムカタログを見ることにより、データベースの内部情報を参照することが出来るのです。

PostgreSQL では、システムカタログは 「pg_」で始まり、次のようなものがあります。

これ以外にもシステムカタログはありますが、pg_class を見れば、どんなものがあるかわかります。テーブルのオーナや、関数の引数のデータ型などを調べることができます（理解できればだけど・・・）。ドキュメントを探しても、主要なシステムカタログに対する説明しかないようで、ちょっとわからない部分もあります。時々必要になってくるので覚えておいて損はないでしょう。pg_proc や pg_type なんかは、いろいろなものがあって、使ってみたくなりますね。でも、ドキュメントが見つからないのですけど・・・。

他のユーザとデータの共有

殆んどのデータベースは、セキュリティの概念を持っています。もちろん、PostgreSQL も例外ではなく、アクセスの制限、拒否、許可等を行うことができます。ただし、ここで注意しなければならないのは、デフォルトの設定では、PostgreSQL 自身が特にユーザ管理を行っていない点です。「createuser コマンドでユーザを作ったぞ」と、言われる方もいるかも知れません。ただし、それは UNIX 上の「このユーザに利用許可を与える」と言った程度のものです。PostgreSQL 自身がパスワードを管理し、ログイン制御を行っているわけではありません。ログイン制御は、OS に任せ、OS にログインしたユーザで、PostgreSQL に登録されたユーザであれば、利用できます。こういった意味では、PostgreSQL がユーザ管理を行っているとは言えないでしょう。では、それ以外のセキュリティとは何を言っているのでしょう。

PostgreSQL の場合、全てのクラス（テーブル）にアクセス権限があります。これは、SQL 文の grant 文と、revoke 文によって管理することができます。クラスは、基本的に、作った人が持ち主になり、そして、そのクラスの管理者になります。作った人は、そのクラスの最高権限を持っていて、そのクラスにデータを登録したり、削除したり変更したりできます。ところが、他のユーザは、そのクラスに登録されているデータ自体を見ることができません。もちろん、変更や削除を行うことはできません。では、どうしたら、他のユーザにデータを公開したら良いのでしょう。この時、先ほど名前を出した、grant 文を使います。grant 文の構文は、簡単に書くと次のようになります。

=> grant 操作 on クラス to ユーザ;

概要を説明すると、「クラス」に対する「操作」を「ユーザ」に許可する。と、いう意味になります。「操作」の部分には、「all, select, insert, update, delete, rule, references, trigger」のいずれか、あるいは、カンマ「,」で区切って複数指定できます。これは、まあわかる通り、実行を許可する SQL 文です。all は全て許可します。「クラス」には、もちろんクラスを指定します。これは、カンマで区切って複数指定できます。最後の「ユーザ」は「public」か、ユーザ名、または「group グループ名（後で説明します）」を指定できます。public は、全ユーザが対象になります。ユーザ名は、もちろん、データベースに登録してあるユーザ名です。システムカタログの pg_user には、登録されているユーザの一覧が格納されています（psql などで、select * from pg_user; とすると、見られます。）。