PostgreSQL でのメモリのチューニング

postgresql.conf での設定

PostgreSQL データベースへの大量のデータの読み込みを促進するため、postgresql.conf ファイルで shared_buffers と max_locks_per_transaction の値を大きくしなければならないことがあります。

shared_buffers

shared_buffers パラメータは、共有メモリ バッファに使用されるメモリの量を指定します。PostgreSQL ドキュメントでは、たいていの場合、パフォーマンス上の理由から、128KB または 16KB に max_connections の設定値をかけた値より大きい値を設定する必要があると説明されています。運用環境のインストールでは、shared_buffers を数十 MB に設定することが推奨されます。

ほとんどの場合、大量のデータを読み込むときは、shared_buffers をデフォルト値の 32MB よりも大きく設定する必要があります。postgresql.conf ファイルでこのパラメータを変更した場合は、データベース クラスタを再起動する必要があります。

max_locks_per_transaction

max_locks_per_transaction 値は、同時にロック可能なデータベース オブジェクトの数を指定します。ほとんどの場合はデフォルトの 64 で十分ですが、大量（たとえば数千単位）のデータセットを一度に読み込む場合は、トランザクションの同時オブジェクト ロック数が 64 を超えることもあります。

同時ロックとデータセット数は 1 対 1 の関係ではありません。たとえば、3000 のデータセットを読み込む場合、max_locks_per_transaction を 3000 に増やす必要はありません。一括読み込みの前に、まず値を 100 に設定してみてください。

max_locks_per_transaction パラメータ値を変更した場合は、サーバを再起動する必要があります。

どちらのパラメータの値を大きくした場合も、UNIX オペレーティング システムにおいて、より多くの共有メモリがデータベースで必要になることがあります。OS の共有メモリ設定を大きくする方法については、PostgreSQL のドキュメントの『カーネル リソースの管理』をご参照ください。

SQL 空間クエリのパフォーマンスの改善

ビジネス テーブルの ST_Geometry 空間列を返す SQL クエリを実行する場合は、システム環境変数を ST_GEOMETRY_OUTPUT_FORMAT に設定して、拡張 WKT（Well-Known Text）形式ではなく ST_Geometry タイプの出力にすると、クエリのパフォーマンスが向上します。

デフォルトでは、ST_GEOMETRY_OUTPUT_FORMAT はTYPE に設定されているため、バイナリの 16 進数表現が返されます。これは、使用可能なジオデータベースのバックアップを作成するために必要な設定です。この変数を ST_GEOMETRY に変更するのは、SQL クエリのパフォーマンスの向上が望ましい場合だけにしてください。多数の空間 SQL クエリを実行するためなどにこの変数を設定した場合は、クエリが完了したら必ずこの設定を削除し、PostgreSQL データベース クラスタを再起動してください。

この変数は、PostgreSQL を実行しているコンピュータ上で設定する必要があります。

Linux OS の場合は、SQL クエリを発行するシェルについてこの変数を設定します。bash シェルの場合の構文は次のとおりです。

ST_GEOMETRY_OUTPUT_FORMAT=ST_GEOMETRY

csh シェルの場合の構文は次のとおりです。

setenv ST_GEOMETRY_OUTPUT_FORMAT ST_GEOMETRY

Windows の場合、システムのプロパティでシステム環境変数を設定します。

変数名: ST_GEOMETRY_OUTPUT_FORMAT

変数値: ST_GEOMETRY

変数を設定した場合は、PostgreSQL データベース クラスタを再起動する必要があります。