本番パターンの概要

実バックエンドプロジェクトにおいて、PostgreSQLのパフォーマンスが問題になるのは、単一の遅いクエリが原因であることは稀です。パフォーマンスは、アプリケーションの接続方法、クエリの方法、そしてスキーマが時間とともにどのように変化するかの組み合わせによって決まります。このレッスンでは、エンドツーエンドのパフォーマンスに直接影響する4つの本番パターンを取り上げます。PgBouncerによるコネクションプーリング、大規模な結果セット向けのページネーション戦略、sqitchやFlywayなどのツールを使ったスキーママイグレーションのワークフロー、そしてクエリからデータベース設定までを対象とする体系的なパフォーマンスチューニングのアプローチです。

目標は、「自分のマシンでは動く」という状態から、実負荷下でも予測可能な動作へと移行することです。

PgBouncerによるコネクションプーリング

PostgreSQLへのすべての接続はバックエンドプロセスをフォークするため、メモリとCPUを消費します。数百の同時リクエストを処理するバックエンドは、すぐに接続上限を使い果たします。PgBouncerはアプリケーションとPostgreSQLの間に位置し、多くのアプリケーション接続をより小さな実データベース接続プールに多重化します。

最も一般的な本番モードはトランザクションプーリングで、トランザクションの間だけクライアントに接続が割り当てられます。

# /etc/pgbouncer/pgbouncer.ini
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

アプリケーション側では、データベースのホストを5432番ポートではなく6432番ポートに指定します。トランザクションプーリングには重要な注意点があります。prepared statement、`SET`コマンド、`LISTEN/NOTIFY`のようなセッションレベルの機能は、トランザクションごとに背後の接続が変わる可能性があるため、安定して動作しません。サーバサイドprepared statementは、セッションプーリングモードでのみ使用してください。

ページネーション戦略

`LIMIT ... OFFSET ...`によるoffsetページネーションは便利ですが、PostgreSQLがスキップされた行をすべてスキャンして破棄するため、大規模テーブルではパフォーマンスが低下します。数百万行のテーブルでは、ページ1000はページ1より明らかに遅くなります。

Keysetページネーション（カーソルページネーションとも呼ばれます）は、インデックスされた列から最後に確認した値を使用することで、offsetを完全に回避します。

-- Bad: offset pagination
SELECT id, created_at, title
FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;

-- Good: keyset pagination
SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

2番目のクエリは`(created_at, id)`の複合インデックスを使用し、正しい位置に一気に到達できます。また、ページ読み込みの間に新しい行が挿入されても安定した結果を返すため、フィードや無限スクロールには不可欠です。

スキーママイグレーション

本番環境では、スキーマ変更は再現可能で、レビュー可能で、かつ reversible（巻き戻し可能）でなければなりません。Flyway、Liquibase、sqitchなどのツールは、マイグレーションをバージョン管理されたSQLファイルとして順番に保存し、適用済みのマイグレーションを追跡テーブルに記録します。

典型的なマイグレーションファイルは次のような形式です。

-- V002__add_user_email_index.sql
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

最も重要な3つのルールがあります。第一に、既存テーブルに対しては必ず`CREATE INDEX CONCURRENTLY`を使用し、インデックス作成中に書き込みをロックしないようにします。第二に、破壊的な変更（カラムの削除など）を新機能と同じマイグレーションに混ぜず、ロールバック可能な状態を保ちます。第三に、本番データのコピーに対してマイグレーションをテストしてください。PostgreSQLプランナーの統計情報は、小規模な開発データベースでは現れない問題を引き起こすことがあるからです。

エンドツーエンドのパフォーマンスチューニング

チューニングはレイヤードアプローチに従います。まず、`pg_stat_statements`またはアプリケーションレベルのロギングで遅いクエリを特定します。次に、`EXPLAIN (ANALYZE, BUFFERS)`を実行し、実際の実行計画、行数、バッファヒットとディスク読み取りを確認します。第三に、根本原因を修正します。多くの場合、インデックスの欠落、不適切な結合順序、または必要以上のデータを取得するクエリが原因です。

クエリ層がクリーンになった後でのみ、データベースのパラメータに触れてください。一般的な改善策としては、`shared_buffers`をRAMの約25%に増やす、`effective_cache_size`を約75%に設定する、大きなソートやハッシュがディスクにスピルしている場合はクエリごとに`work_mem`を調整する、といったものがあります。ただし、設定変更は、すべてのリクエストでテーブル全体をスキャンするクエリを救うことはできない、という点を忘れずにいてください。

-- Finding the top 10 slowest queries by total execution time
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

まとめ

本番PostgreSQLのパフォーマンスは単一の調整項目ではなく、システム全体の特性です。PgBouncerでコネクションプーリングを行い、データベースを枯渇させずに同時接続を処理し、大規模テーブルではoffsetページネーションをkeysetページネーションに置き換え、バージョン管理されたマイグレーションツールでスキーマ変更を管理し、サーバー設定に触れる前にクエリファーストのチューニングワークフローに従ってください。これらを組み合わせることで、予測可能なレイテンシと、負荷増加時における明確な改善パスが得られます。