PostgreSQL パフォーマンスとセキュリティに関するベストプラクティス トップ10
高速なクエリ、安全なアクセス、優れたメンテナンス、復元可能なバックアップのための実践的なPostgreSQLベストプラクティス。
PostgreSQLのパフォーマンスとセキュリティのためのトップ10ベストプラクティス
PostgreSQLのベストプラクティスは、データベースが実際の本番トラフィックを処理し始めると重要になります。適切に設定された環境は、クエリの予測可能性を維持し、データを保護し、何かが壊れたときの復旧経路を提供します。
これらの10のチェック項目を、新しいPostgreSQLサーバーや、動作が遅くなり始めた既存システムの実用的なレビューリストとして使用してください。
1. インデックスを最適化し、EXPLAIN ANALYZEを理解する
インデックスはデータ取得を高速化するために重要ですが、適切でない、または過剰なインデックスは書き込み操作のパフォーマンスを低下させる可能性があります。さまざまなインデックスタイプ（B-tree、GIN、GiST、BRINなど）をいつ、どのように使用するかを理解することが最も重要です。
常にEXPLAIN ANALYZEを使用して、PostgreSQLがクエリをどのように実行するかを理解してください。各ステップの実行時間を含むクエリプランの詳細情報を提供し、ボトルネックやインデックス最適化の機会を特定するのに役立ちます。
実践例: EXPLAIN ANALYZEの使用
EXPLAIN ANALYZE
SELECT customer_name, order_date
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date > '2023-01-01'
ORDER BY order_date DESC;
出力を分析すると、o.order_dateやc.customer_id（まだ主キーでない場合）のインデックスが有益かどうかが明らかになります。
ヒント
pg_stat_statements（有効な場合）を使用して低速クエリを定期的に確認し、それらにEXPLAIN ANALYZEを適用してください。
2. クエリを最適化し、スキーマを効果的に設計する
インデックス作成以外にも、効率的なクエリ作成と慎重なスキーマ設計はパフォーマンスに大きな影響を与えます。本番コードではSELECT *を避け、必要な列のみを選択してください。適切なWHERE句を使用してデータを早期にフィルタリングし、結合タイプを理解してください。データベーススキーマを正規化してデータの冗長性を減らしますが、実用的であることを心がけてください。特定の読み取りが多いシナリオでは、非正規化が有益な場合もあります。
クエリのベストプラクティス
- サブクエリよりも結合を優先する: 多くの場合、データを結合するにはサブクエリよりも
JOIN操作の方が効率的です。 ORDER BYとともにLIMITを使用する: ページネーションや上位Nレコードの取得には、ORDER BYをLIMITとともに使用し、適切なインデックスがあることを確認してください。- 正しいデータ型を選択する: より小さく、より正確なデータ型（例：範囲が許せば
BIGINTの代わりにSMALLINT）を使用すると、ストレージを削減し、パフォーマンスを向上させることができます。
3. 最適なメンテナンスのためにAutovacuumを設定する
PostgreSQLはマルチバージョン同時実行制御（MVCC）モデルを使用しており、UPDATEおよびDELETE操作は古いデータバージョンをすぐに削除しません。これらの「デッドタプル」は時間の経過とともに蓄積され、テーブルの肥大化とパフォーマンスの低下を引き起こします。VACUUMとANALYZEは、それぞれデッドタプルのクリーンアップと統計情報の更新に不可欠です。
AUTOVACUUMは、これらのタスクを自動化するためのPostgreSQLの組み込みプロセスです。postgresql.confでのautovacuumパラメータの適切な設定が重要です。
主要なautovacuumパラメータ
autovacuum = on（デフォルト）autovacuum_vacuum_scale_factor（デフォルト: 0.2、つまりテーブルサイズの20%）autovacuum_vacuum_threshold（デフォルト: 50）autovacuum_analyze_scale_factor（デフォルト: 0.1）autovacuum_analyze_threshold（デフォルト: 50）
非常にビジーなテーブルでは、これらの値を調整し、より低いしきい値やスケールファクターを設定する必要があるかもしれません。
コマンド例
autovacuumアクティビティを表示するには:
SELECT * FROM pg_stat_activity WHERE backend_type = 'autovacuum worker';
4. コネクションプーリングを実装する
新しいデータベース接続の確立は、CPUとメモリの面でコストのかかる操作です。短命な接続が多いアプリケーションや同時ユーザー数が多いアプリケーションでは、このオーバーヘッドがパフォーマンスに大きな影響を与える可能性があります。PgBouncerやPgpool-IIなどのコネクションプーラーは、アプリケーションとPostgreSQLの間に配置され、オープンな接続のプールを維持し、必要に応じてそれらを再利用します。
これにより、接続確立のオーバーヘッドが削減され、同時接続がより効率的に管理され、ロードバランシング機能も提供できます。
コネクションプーリングを使用する理由
- 接続のセットアップ/ティアダウンのオーバーヘッドを削減します。
- データベースへの総接続数を制限し、リソースの枯渇を防ぎます。
- アプリケーションのスケーラビリティを向上させます。
5. postgresql.confパラメータを慎重にチューニングする
postgresql.confファイルには、PostgreSQLの動作、リソース使用量、パフォーマンスを制御する多数のパラメータが含まれています。一般的なデフォルト値は控えめであることが多いため、サーバーのハードウェアとワークロードに基づいてこれらをチューニングすることが重要です。
考慮すべき重要なパラメータ
shared_buffers: PostgreSQLがデータページのキャッシュに使用するメモリ量。多くの専用サーバーは、総RAMの約25%から始め、テスト後に調整します。work_mem: ディスクに書き込む前にソートやハッシュ操作で使用されるメモリ。ディスクソートを避けるために十分な高さに設定しますが、セッションごとの設定であるため注意が必要です。maintenance_work_mem:VACUUM、CREATE INDEX、ALTER TABLE ADD FOREIGN KEYのためのメモリ。work_memよりもはるかに高く設定できます。wal_buffers: ディスクにフラッシュする前のWAL（Write-Ahead Log）データのためのメモリ。小さいですが重要です。effective_cache_size: PostgreSQLとOSによるディスクキャッシュに利用可能なメモリの量をクエリプランナーに通知します。多くのデプロイメントではRAMの大部分に設定し、実際のクエリで計画を検証します。max_connections: 許可される最大同時接続数。
警告
postgresql.confの変更には、多くの場合、データベースの再起動またはリロード（pg_ctl reload）が必要です。不適切なチューニングはパフォーマンスを低下させたり、安定性の問題を引き起こしたりする可能性があります。
6. ハードウェアを監視し、適切なサイズに調整する
完璧なデータベースチューニングがあっても、不十分なハードウェアはボトルネックになります。サーバーのCPU、RAM、ディスクI/O（IOPS、スループット）、ネットワーク使用量を定期的に監視してください。pg_stat_statements、pg_stat_activity、およびOSレベルの監視ツール（例：vmstat、iostat、top）は貴重な洞察を提供します。
主要な監視領域
- CPU使用率: CPU使用率が高い場合は、非効率なクエリまたは処理能力の不足を示している可能性があります。
- メモリ使用量: 過剰なスワッピングがないか確認します。これはRAM不足を示しています。
- ディスクI/O: ディスクアクセスが遅いと、データベースのパフォーマンスが大幅に制限される可能性があります。より高速なストレージ（SSD/NVMe）やRAID構成を検討してください。
- ネットワークレイテンシ: アプリケーションとデータベース間のレイテンシが高いと、リクエストが遅くなる可能性があります。
ハードウェアの適切なサイズ調整には、現在および予測されるワークロードを処理するための十分なリソース（CPU、RAM、高速ストレージ）の割り当てが含まれます。クラウドプロバイダーはスケーリングを容易にしますが、リソースの効率的な使用は常に重要です。
7. 強力な認証を実装し、pg_hba.confを制限する
セキュリティは強力な認証から始まります。常に強力なパスワードポリシーを適用し、安全な認証方法を使用してください。PostgreSQLは、pg_hba.conf（ホストベース認証）で定義されたさまざまな方法をサポートしています。本番環境では、パスワード認証にmd5やpasswordよりもscram-sha-256を優先してください。これはより安全です。
pg_hba.confで、信頼できるホストまたはネットワークのみにアクセスを制限してください。host all all 0.0.0.0/0 scram-sha-256は、絶対に必要な場合を除き、強力なファイアウォールルールと組み合わせて使用しないでください。
pg_hba.confの例
# TYPE DATABASE USER ADDRESS METHOD
local all all peer
host all all 127.0.0.1/32 scram-sha-256
host all my_app_user 192.168.1.0/24 scram-sha-256
ヒント
pg_hba.confファイルを定期的に監査し、必要なアクセスのみが許可されていることを確認してください。
8. 最小権限の原則（RBAC）に従う
最小権限の原則は、ユーザーとプロセスがタスクを実行するために必要な最小限の権限のみを持つべきであると定めています。PostgreSQLでは、これはロールベースのアクセス制御（RBAC）を通じて実現されます。
- 特定のロールを作成する: アプリケーションアクセスに
postgresスーパーユーザーを使用しないでください。 - 最小限の権限を付与する:
GRANTおよびREVOKEコマンドを使用して、データベース、スキーマ、テーブル、シーケンス、関数に対する権限を正確に割り当ててください。 PUBLIC権限を確認する: PostgreSQLは、データベースへのCONNECTや、古いデフォルト設定ではpublicスキーマへのUSAGEなど、いくつかのデフォルト権限をPUBLICに付与します。アプリケーションで不要な場合は、幅広いアクセスを取り消してください。
例: 読み取り専用ユーザーの作成
CREATE ROLE app_readonly_user WITH LOGIN PASSWORD 'strongpassword';
GRANT CONNECT ON DATABASE mydatabase TO app_readonly_user;
GRANT USAGE ON SCHEMA public TO app_readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_readonly_user;
9. ファイアウォールとSSL/TLSでネットワークアクセスを保護する
データベースサーバーは、決してパブリックインターネットに直接公開しないでください。強力なファイアウォールルールを実装して、PostgreSQLのデフォルトポート（5432）への着信接続を、信頼できるアプリケーションサーバーまたは特定のIPアドレスのみに制限してください。
さらに、SSL/TLSを使用してアプリケーションとPostgreSQL間のすべての通信を暗号化してください。これにより、盗聴や中間者攻撃を防ぐことができます。postgresql.confでssl = onを設定し、クライアントがSSLを使用するように設定されていることを確認してください（sslmode=requireまたはverify-full）。
postgresql.confのSSL設定
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
# ssl_ca_file = 'root.crt' # クライアント証明書が必要な場合
注意
postgresql.confのlisten_addressesが特定のIPまたは*（外部でファイアウォールされている場合のみ）に設定されていることを確認してください。
10. 堅牢なバックアップとリカバリ戦略を実装する
データ損失は壊滅的です。堅牢なバックアップとリカバリ戦略は譲れません。バックアップを取るだけでなく、リカバリプロセスを定期的にテストして、バックアップが有効であり、目標復旧時間（RTO）内に正常に復元できることを確認してください。
バックアップ方法
pg_dump/pg_dumpall: 論理バックアップ（SQLスクリプト）。小規模なデータベースやスキーマのみのバックアップに適しています。使いやすいですが、大規模なデータベースでは低速になる可能性があります。pg_basebackup: データディレクトリの完全なコピーを作成するための物理ベースバックアップ。ポイントインタイムリカバリ（PITR）に不可欠です。- WALアーカイブ:
pg_basebackupと組み合わせて、継続的アーカイブ（Write-Ahead Logセグメントの転送）によりPITRが可能になり、データベースを任意の時点に復元できます。
バックアップはオフサイトに保存し、暗号化してください。自動化されたバックアップソリューションを検討し、その成功/失敗を監視してください。
例: pg_dump
pg_dump -Fc -f mydatabase_$(date +%Y%m%d).bak mydatabase
例: pg_basebackup
pg_basebackup -h localhost -p 5432 -U backup_user -D /var/lib/postgresql/backups/base_backup_$(date +%Y%m%d) -F tar -z -v
まとめ
低速なクエリ、バックアップ、アクセス制御から始めてください。これらの3つの領域は、最も痛みを伴う障害を早期に捉えます。その後、自身のワークロードの測定値に基づいて、メモリ、autovacuum、コネクションプーリング、ハードウェアをチューニングしてください。