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

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

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

データファイルの構成

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

テーブルサイズの計算

テーブルサイズを計算する際には、ページと管理ヘッダを加味するとより正確な値が求められます。まず、行ごとに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%程度加えると良いでしょう。

インデックスの張り方

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

インデックスの使い分け

PostgreSQLでは、btreeインデックスの他にも、gistやginインデックスをサポートしています。用途の一覧を表3に示します。一般的な用途では btree インデックスが最適だと思いますが、たとえば、gistは地理情報や、開始時間～終了時間の重なりを調べるような用途で役立ちます。また、全文検索や配列内の要素検索など、1行から複数のキーが抽出される場合には 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 の物理設計に関するポイントを解説しました。テーブルやインデックスの定義はデータベースの性能に大きな影響を与えます。スキーマ設計を行う際の参考にしてください。

※）

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