Aurora PostgreSQLの基本設計で押さえておきたいポイント
導入・背景
AWS案件でAurora PostgreSQLの基本設計を担当するなかで、「これは最初に決めておかないと後から困る」と感じたポイントを整理します。
公式ドキュメントには各パラメータの説明は載っていますが、「基本設計の段階で何を決めるべきか」「デフォルト値のままでよいのか」という判断基準は意外とまとまっていません。同じようにAurora PostgreSQLの設計を担当する方の参考になればと思います。
想定読者
- AWSの基本的なサービスを理解しているインフラエンジニア
- Aurora PostgreSQLの基本設計をこれから担当する、または担当中の方
- パラメータ設計やバックアップ戦略の判断基準を知りたい方
※ PostgreSQLやAuroraの基本的な概念は理解している前提で進めます。
この記事でわかること
- クラスターパラメータグループとDBパラメータグループの使い分け
- max_connectionsの算出ロジックと設計時の考え方
- ログ関連パラメータのデフォルト値と設計判断
- autovacuumをはじめとした基本設計で検討すべきパラメータ
- AWS Backup/PITR/スナップショットとpg_dumpの使い分け
1. パラメータグループの設計判断
1.1 クラスターパラメータグループとDBパラメータグループの違い
Aurora PostgreSQLにはパラメータグループが2種類あります。
| 種別 | 適用範囲 | 代表的なパラメータ |
|---|---|---|
| クラスターパラメータグループ | クラスター全体(Writer/Reader共通) | timezone, log_statementなど |
| DBパラメータグループ | 個別のDBインスタンス | max_connections, shared_preload_libraries, work_memなど |
クラスター全体で統一すべき設定はクラスターパラメータグループ、インスタンスごとに異なる値を持ちうる設定はDBパラメータグループで管理します。
両方に存在するパラメータの場合、DBパラメータグループで変更した値がクラスターパラメータグループの設定より優先されます。
この記事で扱うパラメータの管理先は、ざっくり以下の考え方です。
| パラメータ | 設計上の扱い |
|---|---|
| timezone / rds.force_ssl | クラスター全体で統一するため、クラスターパラメータグループで管理 |
| log_statement / log_min_duration_statement | 基本はクラスターパラメータグループで共通管理。必要に応じてDBパラメータグループで個別上書き |
| max_connections / shared_preload_libraries | インスタンス単位で反映されるため、DBパラメータグループで管理 |
| statement_timeout / idle_in_transaction_session_timeout | 共通方針はクラスターパラメータグループ、個別調整はDBパラメータグループで管理 |
| autovacuum関連 | 共通方針はパラメータグループ、必要に応じてテーブル単位で調整 |
【参考】
1.2 カスタムパラメータグループを作るべき理由
Aurora PostgreSQL作成時に割り当てられるデフォルトのパラメータグループは編集できません。パラメータを1つでも変更する可能性があるなら、クラスターパラメータグループ・DBパラメータグループともにカスタムで作成しておくべきです。
また、PITRやスナップショットからの復元時にはデフォルトのパラメータグループが適用される場合があります。カスタムパラメータグループを用意しておけば、復元後に同じ設定を再割り当てしやすくなります。
1.3 静的パラメータと動的パラメータ
パラメータには静的パラメータと動的パラメータがあり、変更時の挙動が異なります。
| 種別 | 変更の反映タイミング | 例 |
|---|---|---|
| 静的パラメータ | DBインスタンスの再起動が必要 | max_connections, shared_preload_libraries |
| 動的パラメータ | 即時反映(再起動不要) | statement_timeout, timezone, log_statement |
基本設計で特に注意すべきは静的パラメータです。たとえばpgAuditを後から有効化する場合、shared_preload_librariesの変更とDBインスタンスの再起動が必要になります。監査ログの要否は基本設計の段階で決めておくべきです。
【参考】
2. max_connectionsの算出ロジック
2.1 計算式とインスタンスタイプの関係
Aurora PostgreSQLのmax_connectionsのデフォルト値は、インスタンスタイプのメモリサイズから以下の計算式で算出されます。
LEAST(DBInstanceClassMemory / 9531392, 5000)
DBInstanceClassMemoryは、OSやRDSの管理プロセス用に予約される分を差し引いたDBプロセスで利用可能なメモリ量です。実際の値はAWS内部で算出されるため、正確な値は対象のパラメータグループや実機で確認します。
公称メモリを使って概算すると以下のようになります。
| インスタンスタイプ | 公称メモリ | 公称メモリベースの概算値 | max_connectionsデフォルト値の目安 |
|---|---|---|---|
| db.r6g.large | 16 GB | 16 × 1024³ ÷ 9531392 ≒ 1802 | 1802 |
| db.r6g.xlarge | 32 GB | 32 × 1024³ ÷ 9531392 ≒ 3604 | 3604 |
| db.r6g.2xlarge | 64 GB | 64 × 1024³ ÷ 9531392 ≒ 7208 | 5000 |
| db.r6g.4xlarge | 128 GB | 128 × 1024³ ÷ 9531392 ≒ 14417 | 5000 |
【参考】
2.2 大きいインスタンスタイプではデフォルト値の5000が適用される
db.r6g.2xlarge(公称メモリ64 GB)あたりから計算結果が5000を超えるため、デフォルト値は5000に固定されます。
一般的な業務システムで5000同時接続を超えることは稀なので、初期構築時点ではデフォルト値のままとし、性能検証のタイミングでアプリ側と調整する方針でも問題ないケースが多いです。
一方、小さいインスタンスタイプ(db.r6g.largeなど)ではデフォルト値が1800程度になるため、コネクションプーリングやアプリケーション側の接続数制御を設計段階で検討しておく必要があります。
3. ログ関連パラメータの設計
Aurora PostgreSQLのログ関連パラメータは、デフォルトでは多くのログが出力されない設定になっています。運用で必要なログは基本設計の段階で決めておくべきです。
| ログの種類 | 関連パラメータ | デフォルト値 | デフォルトでの出力 |
|---|---|---|---|
| DDLログ | log_statement | none | 出力されない |
| 監査ログ | pgaudit.log(pgAudit拡張) | none | 出力されない |
| スロークエリログ | log_min_duration_statement | 未設定(無効) | 出力されない |
3.1 log_statement(DDLログ)
log_statementはどの種類のSQL文をログに出力するかを制御するパラメータです。
| 設定値 | ログ対象 |
|---|---|
| none(デフォルト) | ログ出力なし |
| ddl | CREATE, ALTER, DROPなどのDDL文 |
| mod | DDL文 + INSERT, UPDATE, DELETEなどのDML文 |
| all | すべてのSQL文 |
本番環境では最低でもddlに設定しておくと、「いつ誰がテーブル定義を変更したか」を追跡しやすくなります。
ただし、ddl以上に設定するとCREATE USERやALTER USERなどのSQL文もログに出力されるリスクがあります。CloudWatch Logsへのアクセス制限や、パスワード部分がリダクトされるpgAuditの併用を検討してください。
【参考】
3.2 pgAudit(監査ログ)
pgAuditはPostgreSQLの監査ログ拡張機能です。Aurora PostgreSQLではデフォルトで無効になっています。
有効化には以下が必要です。
- カスタムDBパラメータグループの
shared_preload_librariesにpgauditを追加する - DBインスタンスを再起動する
-
CREATE EXTENSION pgaudit;でpgAudit拡張をインストールする -
pgaudit.logパラメータで監査対象を設定する
| 設定値 | 監査対象 |
|---|---|
| none(デフォルト) | 監査ログ出力なし |
| all | すべて(read, write, function, role, ddl, misc) |
| ddl | DDL文(CREATE, ALTER, DROPなど) |
| function | 関数呼び出し、DOブロック |
| misc | DISCARD、FETCH、CHECKPOINT、VACUUM、SETなど |
| read | SELECT文やCOPY(読み取り元がテーブルの場合) |
| write | INSERT, UPDATE, DELETE, TRUNCATE |
| role | GRANT, REVOKE, CREATE ROLEなど権限関連 |
重要なのは、pgAuditの有効化にはshared_preload_librariesの変更が必要であり、DBの再起動を伴う点です。監査要件がある場合は、最初からpgAuditを組み込んでおくべきです。
【参考】
3.3 log_min_duration_statement(スロークエリログ)
log_min_duration_statementは、指定した時間以上かかったSQL文をログに記録するパラメータです。AWSのパラメータ表ではデフォルト値は未設定で、スロークエリログは実質的に無効です。
# 例: 1000に設定すると、1秒以上かかったクエリがログに出力される
log_min_duration_statement = 1000 # 単位: ミリ秒
このパラメータは動的パラメータなので再起動なしで変更可能ですが、運用開始後に「どのクエリが遅いかわからない」とならないよう、基本設計で取得方針を決めておくことをおすすめします。
| 設定値 | 用途 |
|---|---|
| 1000(1秒) | 一般的な閾値。業務システムで1秒以上かかるクエリは改善対象になることが多い |
| 5000(5秒) | ログ量を抑えたい場合 |
| 0 | すべてのクエリの実行時間を記録する。調査時の一時的な設定向け |
【参考】
4. 基本設計で検討すべきその他のパラメータ
4.1 autovacuum関連
PostgreSQLではUPDATEやDELETEを実行しても古い行データ(不要タプル)はすぐには削除されません。autovacuumはこの不要タプルを回収する仕組みです。
Aurora PostgreSQLではautovacuumはデフォルトで有効で、AWSも無効にしないことを強く推奨しています。ここでは「デフォルト設定のままで問題ないか」という観点で整理します。
autovacuumは以下の条件を満たしたテーブルに対して実行されます。
不要タプル数 >= autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × テーブルの総行数
| パラメータ | デフォルト値 | 説明 |
|---|---|---|
| autovacuum_vacuum_threshold | AWSのパラメータ表では明示値なし | autovacuumが実行される最小の不要タプル数 |
| autovacuum_vacuum_scale_factor | 0.1(10%) | テーブルの総行数に対する不要タプルの割合 |
| autovacuum_max_workers | GREATEST(DBInstanceClassMemory/64371566592,3) |
同時に実行できるautovacuumワーカーの数 |
| autovacuum_naptime | 5秒 | autovacuumの実行間隔 |
注意したいのは大規模テーブルです。autovacuum_vacuum_scale_factorが0.1の場合、1億行のテーブルでは1000万行規模の更新/削除が発生してからautovacuumの対象になる計算です。
大規模テーブルが想定される場合は、テーブル単位でautovacuum_vacuum_scale_factorを小さくする(例: 0.01)か、autovacuum_vacuum_thresholdを調整することを検討します。ただし基本設計時点では行数が確定していないことも多いため、「大規模テーブルは個別にチューニングする」という方針を決めておくのが現実的です。
Aurora PostgreSQLにはrds.adaptive_autovacuumという独自パラメータもあり、デフォルトで有効です。トランザクションIDのラップアラウンドが近づくと、autovacuum関連のパラメータを自動的に積極的な値に調整します。
【参考】
4.2 statement_timeout / idle_in_transaction_session_timeout
どちらも長時間実行や放置されたトランザクションを制御するためのパラメータです。
| パラメータ | デフォルト値 | 説明 |
|---|---|---|
| statement_timeout | 未設定(実質無制限) | SQLステートメントの最大実行時間。超過するとキャンセルされる |
| idle_in_transaction_session_timeout | 86400000ms(24時間) | トランザクション内でアイドル状態のセッションのタイムアウト。超過すると切断される |
どちらも動的パラメータなので再起動なしで変更可能です。ただし、短くしすぎるとバッチ処理やデータ移行がタイムアウトするため、アプリケーション側の要件と合わせて決める必要があります。
statement_timeoutは、想定外の長時間クエリがDBリソースを占有し続けることを防ぎます。idle_in_transaction_session_timeoutは、トランザクションを開いたままのセッションがロックを保持し続けることを防ぎます。
【参考】
4.3 timezone
デフォルト値はUTCです。
| 選択肢 | メリット | デメリット |
|---|---|---|
| UTC(デフォルト) | AWSサービスのログ時刻と統一しやすい | 日本時間との変換が必要 |
| Asia/Tokyo | 日本向けシステムでは直感的に時刻を読める | アプリ側がUTC前提の場合に不整合が起きる可能性がある |
判断基準は、アプリケーション側がどちらのタイムゾーンを前提にしているかです。timezoneはクラスターパラメータグループで設定するため、Writer/Readerで統一されます。
【参考】
4.4 rds.force_ssl
SSL接続を強制するかどうかを制御するパラメータです。
バージョン17以降はデフォルトで有効ですが、16以前を使う場合はセキュリティ要件に応じて明示的に有効化を検討します。
注意点として、Aurora PostgreSQL 16以前から17以降へメジャーバージョンアップすると、rds.force_sslのデフォルト値が0から1に変わります。アプリケーションがSSL接続に対応していない場合は接続失敗につながるため、アップグレード時にも確認が必要です。
| バージョン | デフォルト値 |
|---|---|
| Aurora PostgreSQL 16以前 | 0(無効) |
| Aurora PostgreSQL 17以降 | 1(有効) |
【参考】
5. バックアップ戦略の設計
5.1 自動バックアップ/PITR・スナップショットとpg_dumpの違い
Auroraの自動バックアップ/PITRやスナップショット(AWS Backupで管理するものを含む)とpg_dumpは、復旧粒度が根本的に異なります。
| 観点 | 自動バックアップ/PITR・スナップショット | pg_dump |
|---|---|---|
| 復旧粒度 | クラスター単位 | データベース/スキーマ/テーブル単位 |
| 復旧方法 | 新しいクラスターとして復元 | 既存のクラスターにリストア可能 |
| 取得方法 | Aurora標準の自動バックアップ、手動スナップショット、AWS Backup | 手動またはスクリプトで実行 |
| 取得時の負荷 | DBサービスへの性能影響や中断なし | 論理的な読み取りが発生するためDB負荷あり |
| RPO | PITRなら直近5分以内まで復元可能 | ダンプの実行タイミングに依存 |
使い分けとしては、「クラスター全体の災害復旧」はAuroraの自動バックアップ/PITRやスナップショット、「テーブル単位の論理的な復旧やデータ移行」はpg_dumpです。
実運用では、クラスター単位のバックアップとpg_dumpを併用するのが現実的です。
- 自動バックアップ/PITR・スナップショット: クラスター全体の災害復旧用。必要に応じてAWS Backupで管理する
- pg_dump: テーブル単位の論理的な復旧用。重要テーブルを対象に定期取得する
この方針を基本設計で決めておくことで、「テーブルを誤って消してしまったがpg_dumpがなかった」という事態を防げます。
【参考】
5.2 PITR(Point-in-Time Recovery)で戻せるもの・戻せないもの
PITRを使えば、バックアップ保持期間内(1〜35日)の任意の時点にデータを復元できます。ただし、PITRは既存クラスターを巻き戻すのではなく、指定した時点のデータを持った新しいクラスターを作成する動作です。
既存クラスター上の特定テーブルだけを直接巻き戻す機能ではないため、PITRで別クラスターに復元して必要なデータを取り出すか、事前に取得したpg_dumpをリストアする運用を検討する必要があります。
復元されるもの
- データベース内のデータ(テーブル、行、スキーマなど)
- 指定した時点までに適用されたDDL/DML
復元されないもの
| 項目 | 復元後の状態 | 対応 |
|---|---|---|
| パラメータグループ | 指定しない場合、デフォルトのパラメータグループが適用される | 復元時にカスタムパラメータグループを指定する |
| セキュリティグループ | 指定しない場合、デフォルトのVPCセキュリティグループが適用される | 復元時または復元後に元のセキュリティグループを割り当てる |
| カスタムエンドポイント | 復元に含まれない | 復元後に手動で再作成する |
| クラスターエンドポイント | 新しいクラスターのエンドポイントが発行される | アプリケーション側の接続先を切り替える |
| DBインスタンス(CLIの場合) | クラスターのみ復元され、インスタンスは作成されない |
create-db-instanceで手動追加する |
| タグ | 条件により引き継がれる | 復元手順でタグ指定の要否を決めておく |
PITRで復旧する場合は、データの復元だけでなくクラスター周辺の設定を元通りにする作業も必要です。基本設計の段階で復旧手順を想定しておくと、障害時の対応がスムーズになります。
【参考】
まとめ
Aurora PostgreSQLの基本設計では、デフォルト値を把握したうえで、変更が必要かどうかを判断することが重要です。
特にpgAuditのように後から有効化すると再起動が必要になるものや、PITRのように仕様を誤解すると復旧設計に齟齬が出るものは、早い段階で押さえておくべきです。
この記事がAurora PostgreSQLの基本設計を担当する方の参考になれば幸いです。