プロジェクト（または会社）を立ち上げ、PostgreSQLを使用することに決めた─ともすると、そんな段階にいるでしょうか。大変な部分（データベースの選択作業）は終わり、楽しいステージの幕開けです。

今回の記事では、コネクションプールからセキュリティの基本、拡張やインデックスに至るまで、PostgreSQLを長期にわたって設定するための大事な（あまり知られていない）ヒントをご紹介します。

PGBouncerを使用したPostgreSQLコネクションプーリング

デフォルトでは、PostgreSQLはクライアント接続ごとにOSのメインプロセスとは別のプロセスをフォークします。少ない量であれば、このプロセスの生成と破棄にかかる時間や、「再利用されない」という性質は問題になりません。max_connectionsの数を手動で設定することもできますが、規模が大きくなると、問題が発生する可能性が高くなります。そこでコネクションプーリングを使用して、プロセスを基本的に 「キャッシュ」し、 クライアントがデータベースに接続したり切断したりする際に再利用することができます。

アプリケーションロジックにコネクションプーリングを組み込むこともできますが、多くの場合はサードパーティのツールを使用します。PostgreSQLでは、PGBouncerが代表格です。これはオープンソースの軽量コネクションプーラーで、データベースサーバーまたはアプリケーションサーバーのいずれかにインストールすることができます。プーリングは3つのレベルから選択できます。

  • セッションプーリング：「クライアント接続が無期限」というモデルに忠実で、クライアントが接続している間ずっと接続をオープンにしておきます。
  • トランザクションプーリング：接続が1トランザクションの間持続し、その後はプールに戻されます。
  • ステートメントプーリング：接続が1つのクエリに対してのみ使用されます。

ほとんどの場合、セッションプーリングが選択されます。最も保守的で、接続を切断するリスクが少ない設定です。しかし、アプリケーションはそれぞれ異なるので、要件に適したモードを見つけることが重要です。

コネクションプーリングのパフォーマンスへの影響

誰もが知りたい問題がこちら─実際にこの機能には利用する価値があるのかどうか。Perconaは一連のベンチマークを使い、PGBouncerがパフォーマンスに与える影響を調べています。同時接続クライアント数が少ない場合（<60）、PGBouncerはプーリングのオーバーヘッドにより、1秒あたりのトランザクション数（TPS）にかなりの悪影響を及ぼします。しかし、100以上にスケールアップする頃には、パフォーマンス上のメリットが見られるようになります。

PGBouncer
PGBouncer

以上を考えると、ユーザー数が少ない段階ですぐにコネクションプーラーを利用する必要はないでしょう。一方で、もう少し処理数が増えた時の（低/中程度の）トラフィックに対しては、PGBouncerをうまく活用することができるはずです。

PostgreSQLのセキュリティ

プロジェクトに取り組む最初の数週間は、通常1人か2人の開発者が空のデータベースで作業をすることになるでしょう。しかし、アプリを世に送り出す頃には、セキュリティの管理が必要になります。データベースについて言えば、セキュリティ強化の方法は豊富に存在します。

ホストまたはユーザーレベルでのアクセス制限

アクセスから始めましょう。PostgreSQLでは2つの方法でアクセスを制限できます。

  1. ホストレベル：アクセス権を持つIPアドレスとドメインを定義
  2. ユーザーレベル：データベースユーザーとその権限を定義

PGDATAディレクトリのpg_hba.confファイルで、誰がどのデータベースに接続できるかを定義します。このファイルにクライアントのエントリがなければ、そのクライアントはデータベースにアクセスできません。アプリケーションサーバーが他の場所で稼動していると仮定して、そのサーバーにデータベースへのアクセスを許可する方法を以下に示します。

# このコンピュータからのTCP/IP経由の接続をすべて信頼
host all 127.0.0.1 255.255.255.255 trust

単に「このマシンからの接続を信頼する」というだけでなく、データベースサーバーとクライアントを認証するには、パスワードからID、証明書まで、さまざまな方法があります。また、RDS（またはKinsta）の利便性に頼らない方法を選び、データベースと同じサーバー上でバックエンドを実行している場合には、TCP/IPの代わりにUnixソケットを介して接続することができます。

認証と権限

クライアント自身の認証の次には、権限の問題に対処する必要があります。SQLでは権限体系が定義されており、PostgreSQLの各オブジェクト（テーブル、行など）には、ユーザーに割り当てることができる、そのオブジェクトに関連する様々な権限があります─SELECTやUPDATEだけでなく、TRUNCATE、REFERENCES、TRIGGERなどです。ユーザーに権限を与えるにはGRANTコマンドを利用できます。

ベストプラクティスは権限を最小限に抑えることです。クライアントのために作成したデータベースユーザーが、「必要がある」ものすべてにアクセスできるように設定しましょう。

行レベルのセキュリティ

最後に取り上げるのは 行レベルのセキュリティ（RLS）です。ここでは（ユーザーではなく）テーブルの観点から、アクセスや更新などが可能な行を制限します。デフォルトでは、テーブルでRLSが有効になっていないため、ユーザーはアクセスポリシーに従った操作を行うことができます。テーブルのRLSを有効にするには、まず次のようにします。

ALTER TABLE [table_name] ENABLE ROW LEVEL SECURITY

そしてポリシーを追加します。例えば、lightsaber_internalsテーブルへの読み取りアクセスを、既にjediユーザーグループに定義されている信頼できる個人に制限し、「ライトセーバーの所有者」だけがその詳細を見ることができるようにしたいとします。その方法は以下の通りです。

ALTER TABLE lightsaber_internals ENABLE ROW LEVEL SECURITY
CREATE POLICY jedi_only ON lightsaber_internals TO jedi
    USING (jedi = lightsaber_jedi);

このようなRLSポリシーは、テーブルよりも詳細なレベルでのセキュリティが必要な場合（PII─個人と特定できる情報─を含む状況など）で有効です。

スケーリングの問題を事前に考える

私がこれまで働いてきたスタートアップ企業では、データベースに関して、ある程度の手動スケーリングが行われてきました。ある日の朝のことです。DatadogでPostgreSQLサーバーが完全に容量不足であるとの警告が出現。調査し、インシデントページを更新し、結局ディスク容量をアップグレードして、また同じことが起こるまで待つことになります（次はRAMの問題かもしれませんが）。このような事態に先手を打つことが重要です。いくつかの方法をご紹介します。

1. データベース監視のセットアップ

私が働いていた会社のほとんどは、データベースのモニタリングにDatadogを使っていました。マネージドデータベースサービスを利用しているのであれば、Datadogのネイティブサービスを利用することで、少しは何とかなるはずです。Datadogのブログには、読み書きスループット、シーケンシャルスキャン、ディスクに書き込まれたデータなど、監視すべき主な指標を網羅した優れた記事があります。

2. 垂直スケーリングのガイドラインをまとめる

あなたのチームがページングされた時-そしてそれは起こるだろう-あなたが一番避けたいことは、問題を解決するために全員が手を挙げなければならないことだ。スペースやコンピュート不足に陥ったとき、何がスコープ内なのか、チームのために基本的なプランをまとめておくことをおすすめします。

3. autovacuumのバキュームとチューニング

PostgreSQLでデータをDELETE 、またはUPDATE （これは機能的には削除と挿入に相当します）しても、実際にそのデータはすぐに削除されません（😱）。代わりに、xmaxヘッダに削除のトランザクションIDを格納することで、削除されたものとして「記録」されます。これは、PostgreSQLのMVCCをより簡単にするためです。しかし、最終的に「本当に削除」しなければ、そのような行がディスク領域を浪費し、問題が発生してしまいます。

削除する最も簡単な方法はVACUUMコマンドを使うことです。不要な行が蓄積されるたびに手動でバキュームを実行することも、x分ごとに実行するように設定することもできますが、その行の蓄積数に基づいて自動でバキュームを実行するように設定するのがさらに効果的です。自動バキュームのチューニングは、この記事の範囲外になりますので、2ndQuadrantのこちらの記事をおすすめします。

4. リードレプリカのセットアップ

これはシンプルな施策です。トラフィックの増加が予想される場合（ローンチが近いなど）、読み取り専用のレプリカを複数（または最低でも一つ）作成することができます。

複数のレプリカを作成すれば、そのうちの一つが何らかの理由でダウンした場合の可用性を改善できます。ほとんどのDBaaSサービスで、レプリカを簡単に追加できます。ただ、コストには注意してください。読み込み専用であるにもかかわらず、メインDBインスタンスと同じレベルの価格であることがよくあります。

（予想される）最大のテーブルにインデックスを追加する

データベースのインデックスは、スキャンを高速化する補助的なデータ構造を作成することで、読み取りクエリ高速化に貢献します。多くのユースケースで、テーブルにインデックスを追加することは基本的に問題ありません。PostgreSQLでは、インデックスを作成するにはCREATE INDEXコマンドを使います。テーブルへのクエリ実行時に、インデックスが存在するかどうかを確認し、存在する場合にはそれが使用されます（これはEXPLAINで確認できます）。

PostgreSQLで最も一般的なインデックスの種類はB木インデックスであり、CREATE INDEX使用時にデフォルトでこれが選択されます。基本的には、インデックスを作成したいカラムをソートし、ソートした行へのポインタを格納します。こうすることで、実際のテーブルがソートされているカラムだけでなく、どのようなカラムに対してもバイナリ検索をかけることができます。このツリーがどのように実装されているかについては、PostgreSQLのドキュメントをご覧ください。

便利ではありますが、インデックスは楽しいものばかりではありません。スペースを取るものであり、作成する数や種類に注意しなければ、データベースのパフォーマンスを減じる可能性もあります。PostgreSQLのドキュメントに書かれている以下の文章がそれを示しています。

「インデックスは主にデータベースの性能を引き上げるのに使用されますが、使い方を誤ると低速化を招く可能性があります」（原文の日本語訳）

インデックスを作成すると、PostgreSQLはインデックスとインデックスのレコードへのポインタを持つルックアップテーブルを作成します。このテーブルが多すぎると、ディスクスペースを消費し、INSERTクエリに時間がかかり、クエリの実行方法を選択する前にクエリエンジンに多くの選択肢を検討させることになります。

おまけ─PostgreSQL拡張の追加

PostgreSQLの特徴の1つは、サードパーティによる拡張のネイティブサポートです。SQLとCから拡張機能を作成することができ、2、3のステートメントからソフトウェアライブラリ全体のような大規模なものまで可能です。公開されている/オープンソースの拡張機能を使うのも有用で、ソフトウェアパッケージを使うようなものです。自ら開発することなく、すぐさま機能を実装できます。人気の高いPostgreSQL拡張をいくつかご紹介します。

Timescale

Timescaleは時系列データを扱うのに便利なPostgreSQL拡張です。クエリを（かなり）高速化し、時系列データを非常に効率的に保存することができます。時系列データでビジネスを行うのであれば、Timescaleのクラウドホスティングオプションは検討するに値します。

PostGIS

PostGISは地理データ（ライン、ポリゴン、ロケーションなど）の保存、インデックス作成、クエリといった機能をPostgreSQLに追加します。クラウドプロバイダを利用している場合、ほとんどの場合PostGISがプリインストールされています。自分でインストールする必要がある場合は、こちらでその手順をご確認ください。

pg_stat_staements

pg_stat_statementsは、データベース上で実行されたクエリに関する統計情報を含むビューをPostgreSQLデータベースに作成するものです。クエリの実行時間（平均値、中央値、平均値など）、誰がクエリを実行したか、ブロックキャッシュヒット数、書き込まれたブロック数など、多くの統計情報を見ることができます（計44の列あり）。インストールは、.confファイルに追加しサーバーを再起動するだけで完了します。

pg_audit

pg_auditは、詳細な監査を受ける可能性のある企業（政府機関、金融機関など）におすすめです。log_statement=allを設定することで、PostgreSQLのデータベースに対する全てのstatementのログを記録することができます（必要な情報を簡単に検索して見つけることができるわけではありませんが）。pg_auditはPostgreSQLの内部ログ関数を使用したもので、監査に必要となるログを用意するという目的に有用です。インストール方法はこちらを参照してください。

まとめ

PostgreSQLはあらゆるビジネスを支える素晴らしい（そして非常に人気のある）選択肢です。Kinstaはこれをサポートしています。その他のヒントやあなたのご経験から思うことがあれば、コメント欄でお聞かせください。

Justin Gage

Justin is a technical writer and author of the popular Technically newsletter. He did his B.S. in Data Science before a stint in full-stack engineering and now focuses on making complex technical concepts accessible to everyone.