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

NTT オープンソースソフトウェアセンタ
板垣貴裕

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

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

データファイルの構成

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

テーブルサイズの計算

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

8KB × ceil(行数 / floor(floor(8KB × fillfactor - 24) / (28 + 行データ長)))

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

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

8KB × ceil(行数 / floor(floor(8KB × fillfactor - 24) / (12 + キー長)))

計算例

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

（2009 年 10 月 27 日公開 / gihyo.jp にて）