みなさんこんにちは。技術系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