gihyo.jp & Let's Postgres 連動企画 今こそ!PostgreSQL

第5回PostgreSQL でのデータベース構築の際に必要となる物理設計のポイント

第5回では、PostgreSQL でのデータベース構築の際に必要となる物理設計のポイントとして、データ容量の計算方法とインデックスの張り方を解説していきます。

データベース・サイジング

サイジングとは、サービスの開始前に、想定される負荷や格納されるデータ量を見積り、十分な性能や規模のサーバおよびストレージを用意することです。今回は、サイジングの要素のうち、ストレージサイズの計算方法を紹介します。

データファイルの構成

PostgreSQLはデータベース・クラスタと呼ばれるディレクトリの下に、複数のディレクトリやファイルを作成します。容量の多くを占めるのはアプリケーションが使うテーブルやインデックスになるでしょうが、それ以外にも管理領域やログのためのディスク領域が必要になります。

表1 データファイルの構成と容量
要素容量説明
テーブルインデックス(用途依存⁠データを保持できる容量の他、更新がある場合にはマルチバージョン管理を行うための領域を加味します。詳細な計算方法は後述します。
管理情報10MB程度システムカタログ等の管理情報が含まれます。
トランザクションログ(pg_xlog)100MB~数GB停電などの異常停止でもコミット結果を保証するためのログです。更新処理が多い場合には増やすとスループットが向上します。checkpoint_segments 変数で設定します。ログアーカイブが失敗するとファイルサイズが増加するので注意しましょう。
アーカイブログ(archive_command)(用途依存)アーカイブリカバリのためにトランザクションログをアーカイブする場合に必要です。サイズはバックアップ保存期間中に更新される領域と同じ程度です。参照主体では容量は小さくて済みますが、更新が多くバックアップ保存期間が長い場合は、データベースサイズ以上のサイズになることもあります。
コミットログ(pg_clog)50MB~512MBトランザクションがコミットしたかロールバックしたかを管理するログです。autovacuum_freeze_max_age 変数で設定します。
サーバログ(pg_log)(用途依存)サーバログはエラーや警告が発生した際にメッセージが記録されます。デフォルトの設定では古いログは削除されません。繰り返し発生するエラーに気づかずに大量のメッセージが出力されないよう注意しましょう。

テーブルサイズの計算

テーブルサイズを計算する際には、ページと管理ヘッダを加味するとより正確な値が求められます。まず、行ごとに28byteの管理ヘッダが付与され、その後ページに収まるよう行データが配置されます。ページには24byteのヘッダ領域と、fillfactor (10-100%) で指定する空き領域があることを加味します。

インデックスサイズの計算

よく使われるbtreeインデックスについて説明します。インデックスもテーブルと同様ですが、管理ヘッダは12byteです。また、fillfactorのデフォルト値は90%です。これ以外にも、木構造であるbtreeの上層ページが必要ですが、影響は小さいので省略します。

計算例

pgbenchベンチマークのaccountsテーブルに対して、この計算方法を適用してみます。pgbench_accountsテーブルは以下のように定義されています。

   Table "public.pgbench_accounts"
  Column  |     Type      | Modifiers
----------+---------------+-----------
 aid      | integer       | not null
 bid      | integer       |
 abalance | integer       |
 filler   | character(84) | (文字列は長さ管理の 1byte を加える)
Indexes:
    "pgbench_accounts_pkey" PRIMARY KEY, btree (aid)

“行データ長⁠⁠キー長⁠は8byte単位で切り上げて計算します。この場合、行データ長は104byte、キー長は8byteになります。100,000行の場合に、上記の計算式を当てはめると表2のようになり、おおむね正しい値が得られることがわかります。更新を行う場合には、計算した推定値にさらに安全率を20%程度加えると良いでしょう。

表2 テーブルとインデックスサイズの推定値と実測値
 名前推定値実測値
テーブルpgbench_accounts13120 KB13120 KB
インデックス pgbench_accounts_pkey2184 KB2208 KB

インデックスの張り方

インデックスは検索処理の高速化に非常に有効ですが、インデックスが多すぎるとディスクやメモリを消費し、更新処理も遅くなります。検索パターンに応じて使い分け、適切な列に対してインデックスを定義するコツを紹介します。

インデックスの使い分け

PostgreSQLでは、btreeインデックスの他にも、gistやginインデックスをサポートしています。用途の一覧を表3に示します。一般的な用途では btree インデックスが最適だと思いますが、たとえば、gistは地理情報や、開始時間~終了時間の重なりを調べるような用途で役立ちます。また、全文検索や配列内の要素検索など、1行から複数のキーが抽出される場合には gin を使います。

表3 インデックスの使い分け
 スカラー値多次元/範囲
1キー/行btreegist
多キー/行gin-

複数列インデックスを使う判断

PostgreSQLは非常に柔軟なインデックスの使い方をします。かなり多くのクエリパターンで複数列インデックスを使うことができますし、逆に必ずしも複数列インデックスが必要無い場合もあります。

複数列インデックスでは、キーの順番によらずインデックスを使うことができます。以下の例では、全てインデックスが利用可能です。ただし、最初のキーから順に条件を指定したほうが処理は高速です。その差はcostの値にも表れています。

=# CREATE INDEX idx_abc ON tbl (a, b, c);
=# EXPLAIN SELECT * FROM tbl WHERE a = 1 AND b = 2;
  => Index Scan on idx_abc (cost=8.27)
=# EXPLAIN SELECT * FROM tbl WHERE a = 1 AND c = 3;
  => Index Scan on idx_abc (cost=8.36)
=# EXPLAIN SELECT * FROM tbl WHERE b = 2 AND c = 3;
  => Bitmap Index Scan on idx_abc (cost=14.91)

また、必ずしも複数列インデックスを定義しなくても、各列ごとのインデックスを同時に使うこともできます。それぞれのインデックスの結果をマージしてビットマップ・スキャン (Bitmap Scan) による処理を行います。以下の例でも、idx_a と idx_b の共通部分 (BitmapAnd) をスキャンしていることがわかります。

=# CREATE INDEX idx_a ON tbl (a);
=# CREATE INDEX idx_b ON tbl (b);
=# EXPLAIN SELECT * FROM tbl WHERE a = 1 AND b = 2;
  => Bitmap Heap Scan (cost=12.92)
       ->  BitmapAnd
             ->  Bitmap Index Scan on idx_a
             ->  Bitmap Index Scan on idx_b

インデックスを多くの検索パターンで使うことができるので、インデックスの数を減らしても検索性能が満足できるかもしれません。インデックス数が多いと更新性能に悪影響があるため、特に更新処理が多い場合には有効なチューニングになります。

なるべくキーの更新を避ける

インデックスのキーは、なるべく変更しないようにしましょう。PostgreSQL には "HOT" と呼ばれる更新処理の最適化機能があるのですが、インデックスのキーに含まれる列が更新されると、この最適化が行われなくなります。更新性能が低下するため注意しましょう。

また、キーでない列はインデックスに含めてはいけません。他のDBMS製品では⁠covering index⁠と呼ばれる最適化手法をサポートしているものがありますが、PostgreSQL ではサポートしていません (v8.4 現在)。インデックスには必要なキー値のみを含むようにしましょう。

今回は、PostgreSQL の物理設計に関するポイントを解説しました。テーブルやインデックスの定義はデータベースの性能に大きな影響を与えます。スキーマ設計を行う際の参考にしてください。

※)
検索処理にて取得するフィールドが全てインデックスに含まれている場合、インデックススキャンの際にはテーブルにはアクセスせずに処理を高速化する機能です。

おすすめ記事

記事・ニュース一覧