第5回　PostgreSQLの基礎を見直す

みなさんこんにちは。技術系Q&Aサイトteratail開発チームの出川幾夫です。

PostgreSQLはMySQLと並んで広く使われているオープンソースのRDBMSです。商用のRDMBSと比較しても遜色ない高機能なRDMBSですが、その機能を特に意識せず運用してしまっている現場も多いのではないでしょうか。

今回はPostgreSQLの、普段の運用では無視しがちな、データベースをサービスに使っている人間として知っておくべき基礎的な部分について、概説していこうと思います。

PostgreSQLのデータ型・関数

PostgreSQLではさまざまなデータ型が利用可能です。文字列型はchar、varchar、textなどがあり、char(n)やvarchar(n)のnはバイト数ではなく文字数で指定します。

integer型やfloat型などの数値型にはMySQLにあるようなUNSIGNEDはないので注意が必要です。IDなどの連番を表現するにはserial型が便利です。またtimestamp型、ネットワークアドレス型、幾何データ型など特定の用途に便利な型も揃っています。

PostgreSQLのデータ型で特徴的なのが配列型です。その名の通り1つのカラムに複数個のデータを格納することができ、RDBMSらしからぬ非正規化を実現する型となっています。タグ付けのような簡単な多対多の関係であれば、中間テーブルを設けずJOINの回数が一度減るため、パフォーマンスが要求される場面などに利用価値があります。

配列型は、プログラミング言語に慣れ親しんだ人であれば直感的にわかりやすいSQLで扱うことができます。teratailでは、ユーザは複数のタグを「Myタグ」として自分に紐付けることができますが、これと同様のデータ構造を表すためにtag_idsカラムをintegerの1次元配列として定義すると以下のようになります。

CREATE TABLE users (
    name varchar(32),
    tag_ids integer[]
);

PostgreSQLの配列のインデックスは1始まりです。配列からデータを取り出す際の配列リテラルは{1,2,3}のように表現でき、配列のサイズは可変です。またGINインデックスを用いることで配列の各要素にインデックスを貼ることもできます。

またJSONを保存するJSON型というのもPostgreSQL9.2以降で提供されています。これはJSON形式でデータを保存するのに非常に便利です。text型のカラムに格納する場合と比べて、挿入の際にはJSONの構文チェックがされエラーを検出できるのが利点です。またarray_to_json()やjson_populate_record()などJSONを扱うための組み込み関数も豊富で、JSONの書き出しや読み込みに非常に有用です。JSON内の特定のフィールドの値を取り出すこともできます。

INSERT INTO settings (id, data) VALUES (1, '{ "notifications": {"mail": true, "mobile": true}}');

PostgreSQL9.4ではJSONを最適化されたバイナリで保持するJSONB型が追加され、JSONを扱う際のパフォーマンスが大幅に向上しています。

複合型というプログラミング言語で言う構造体のような役割を果たす型も存在します。テーブル作成時には同名の複合型が作られます。

これらの非正規化を実現する型はクエリを短縮しパフォーマンスをあげることができますが、信頼性が必要とされるデータを入れる用途には適さないので濫用は禁物です。

Window関数も便利です。rank()やrow_number()など集約関数に似た動きする関数が用意されています。

SELECT username, rank() OVER (ORDER BY point DESC) AS rank FROM users;

他にもMySQLなど他のRDBMSとは細かな違いがあります。他のDBの癖をそのまま使ってしまわないように基本的な部分は設計時に予め確認しておきましょう。

基礎的な設定項目

PostgreSQLの基本的な設定はpostgresql.confで行い、「⁠設定パラメータ名 = 値」の形で記述します。データベースのパフォーマンスにかかわるパラメータが多く設定されているため、このファイルの内容は非常に重要な部分です。設定変更後にPostgreSQLの再起動が必要なものはコメントに“⁠change requires restart⁠”と書かれていますので注意しましょう。

実際に反映されている設定パラメータの値はSHOW ALLで確認ができます。

=# SHOW ALL;

shared_buffersは、テーブルやインデックスのデータの共有メモリバッファのために利用するメモリ量を指定します。デフォルトは128MBとなっていますが、1GB以上のRAMを搭載しているサーバの場合はシステムメモリの25％程度が推奨されています。多くの場合デフォルトの128MBは推奨値と比べて小さいはずなので、データベースサーバ運用のはじめの段階で適切な値に設定すべき項目です。

log_min_duration_statementはいわゆるスロークエリをログに残すための設定です。ミリ秒単位で記述し、これ以上の実行時間のかかった全てのSQL文を記録します。デフォルト値は-1でログが保存されないようになっていますので、PostgreSQLをインストールした時点で、まずはじめに数値を指定して設定しておくべきです。パフォーマンスチューニングの際に非常に良い指標になります。

他にも設定パラメータは多く存在しますので、一度全て確認して、その後サービスに必要な変更があれば適宜変更しましょう。自動VACUUMの有無や実行時間帯や閾値などのパラメータはテーブルごとに個別に設定することもできます。

postgresql.confをデフォルトのままあまり変更せずに運用している場合は、一度各パラメータを確認してみるとよいでしょう。ただパフォーマンスの向上を求めて闇雲にパラメータを変更するのではなく、きちんと事象分析などをしてから必要に応じて適切な変更を行うようにしましょう。

参考：PostgreSQLでDBバックアップのパフォーマンスチューニングをする方法: https://teratail.com/questions/3785
参考：Postgresで複雑なSQLの実行計画の制御: https://teratail.com/questions/846

VACUUM

PostgreSQLの特徴のひとつに追記型アーキテクチャであることが挙げられます。PostgreSQLではDELETEやUPDATEを実行した際には既存の行は内部的には削除されず、後に新しいデータが追加される形で保存されていきます。DELETEやUPDATEされる前の行は不要な行としてそのまま残り続けるため、長く運用していると性能低下を起こすことがあります。

この不要領域を回収して再利用可能な状態にするのがVACUUMという機能です。テーブルロックを行わないのでサービスが動いていても実行でき、更新頻度が高いテーブルでは定期的にVACUUMを実行する必要があります。

VACUUMは内部的には不要領域を含むページが記録されているVM（Visibility Map）を参照して、ページを走査しながら不要領域を削除します。

VACUUMは以下のようにテーブル名を指定してそのテーブルに対して実行します。指定しない場合はデータベース内の全テーブルに対してVACUUMを行います。

=# VACUUM some_table;

PostgreSQL 8.3以降は自動VACUUM機能がデフォルトで有効化されているため、特に何もしなくても不要行の割合が閾値を超えたら自動的にVACUUMが実行されるようになっています。公式でも自動VACUUMは推奨されていますので、基本的に有効（autovacuum=on）のままで問題ないでしょう。

閾値は設定パラーメーターであるautovacuum_vacuum_thresholdとautovacuum_vacuum_scale_factorを使って以下の式のように表されます。

閾値 = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * 行数

最後のVACUUMのあとに発生した不要行の数が上のVACUUM閾値を超えるとVACUUMが実行されます。テーブルの更新頻度やデータの件数によってこちらをチューニングしていく必要があります。

データベースごとのVACUUMやANALYZEの実行履歴は、pg_stat_user_tablesテーブルから参照できます。

=# SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze, vacuum_count,autovacuum_count, analyze_count,autoanalyze_count FROM pg_stat_user_tables;

VACUUMはPostgreSQLの基本アーキテクチャに関わる部分で非常に重要な機能です。開発環境でVACUUM VERBOSE some_tableをして実行の流れを見てみるとどのような流れでVACUUMが行われていくのかがわかりますので、ぜひ一度確認してみてください。

参考：PostgreSQLのvacuum analyzeについて: https://teratail.com/questions/13605

全文検索モジュールpg_bigm

PostgreSQLではさまざまなモジュールが利用できます。その中でも最も導入が簡単で効果の大きいものがpg_bigmというモジュールです。

pg_bigmモジュールはGINインデックスを利用した全文検索を実現するモジュールで、ブログ記事のような文書内の部分一致検索（LIKE '%word%'）の速度を大幅に向上させることができます。pg_trgmというPostgreSQLの公式モジュールには、日本語の検索にはPostgreSQLの再ビルドが必要で、かつ2文字以下の検索はインデックスが利用されないという欠点がありました。これに対してpg_bigmはこの2つの欠点を解消したものとなっています。また=%という演算子を利用することで類似度検索にも対応します。

インストール方法は非常に簡単です。pg_bigmの公式ページの指示通りにビルドしてインストールした後に、postgresql.confに以下の2行を追記します。PostgreSQL 9.2以降であれば下のcustom_variable_classesの行は不要です。

shared_preload_libraries = 'pg_bigm'
# custom_variable_classes = 'pg_bigm' # PostgreSQL 9.1以前

そして拡張を有効化します。

=# CREATE EXTENSION pg_bigm;

これで、GINインデックスを張ったカラムに対しての全文検索でpg_bigmが適用されるようになります。

=# CREATE INDEX post_body_gin_index ON posts USING gin(body gin_bigm_ops);

pg_bigmを無効化する場合はDROP EXTENSIONで無効化します。

=# DROP EXTENSION pg_bigm CASCADE;

pg_bigmを使った全文検索では、私が試してみたところ、2万件ほどの文章データでシーケンシャルスキャンのときと比較して、10倍以上高速化したことがあります。

全文検索にはGroongaやElasticSearchのような高機能な全文検索エンジンも効果的ですが、まずPostgreSQLの中でpg_bigmのようなモジュールを導入して簡便に高速化するのも非常におすすめです。

参考：PostgreSQLで、全文検索を行う為のGINとGiSTインデックスの使い分けについて: https://teratail.com/questions/1178

最後に

今回は、PostgreSQLの運用で忘れがちなもっとも基礎的で理解しておくべき部分を説明していきました。teratailではPostgreSQLに関する質問や回答も多く投稿されていますので、疑問点ができたらどんどん質問してみるとよいと思います。

teratail: https://teratail.com