開発および運用に関する推奨事項 - ApsaraDB RDS - Alibaba Cloud ドキュメントセンター

ApsaraDB RDS for PostgreSQL インスタンスのコンプライアンス、安定性、およびパフォーマンスを向上させるためのベストプラクティスです。

コネクションプーリング

コネクションプールのパラメーター設定

SQL ステートメントをコネクションプール内でキャッシュするには、`PreparedStatement` オブジェクトを使用します。これによりハードパースが不要となり、CPU 使用率の低減とインスタンスパフォーマンスの向上が実現されます。

アイドル接続を最小化することで、メモリ使用量を削減し、GetSnapshotData() の効率を改善し、システム全体のパフォーマンスを向上させることができます。

短時間接続に伴うオーバーヘッドを回避するため、アプリケーションでコネクションプーリングを有効化してください。アプリケーションが組み込みのコネクションプーリングをサポートしていない場合は、アプリケーションと RDS インスタンスの間にコネクションプール（例：PgBouncer や Pgpool-II）を配置します。

コネクションプールに対して以下のパラメーターを設定します：

パラメーター	推奨値	説明
`minimumIdle`	1	アイドル接続の最小数です。この値を 1 に設定することで、アイドル接続数を削減できます。
`maxIdle`	1	アイドル接続の最大数です。利用可能な場合のみ適用されますが、ほとんどのコネクションプール実装ではこのパラメーターは削除されています。
`maxLifetime`	60 分	各接続の生存時間（TTL）の上限です。RelCache への頻繁な接続に起因するメモリ不足（OOM）エラーを軽減します。
`maximumPoolSize`	15	プールごとの最大接続数です。ほとんどのワークロードに適しています。ただし、インスタンスがプールで処理可能な接続数を超えるワークロードを処理する場合に限り、データベースクライアント側でこの値を増加させてください。

フレームワーク別の推奨構成

以下は、最も一般的なコネクションプールフレームワークに適用される構成です。`PreparedStatement` の設定は含まれていないため、アプリケーションの要件に応じて別途設定してください。

HikariCP（Java 向け推奨）：

minimumIdle=1, maximumPoolSize=15, idleTimeout=600000 (10 分), maxLifetime=3600000 (60 分)

GORM（Go 向け推奨）：

sqlDB.SetMaxIdleConns(1), sqlDB.SetMaxOpenConns(15), sqlDB.SetConnMaxLifetime(time.Hour)

Druid（Java 向け）：

initialSize=1, minIdle=1, maxIdle=1, maxActive=15, testOnBorrow=false, testOnReturn=false, testWhileIdle=true, minEvictableIdleTimeMillis=600000 (10 分), maxEvictableIdleTimeMillis=900000 (15 分), timeBetweenEvictionRunsMillis=60000 (1 分), maxWait=6000 (6 秒)

パフォーマンスおよび安定性

単一のデータベースは、基盤となるファイルシステム上のフォルダに対応します。テーブル、パーティション、およびインデックスは、そのフォルダ内のファイルに対応します。ファイル数が 2,000 万を超えると、インスタンスで「ディスク領域不足」エラーが報告されます。ワークロードに応じて、データベースを分割するか、テーブルファイルをマージしてください。
オンラインワークロードのインデックスを作成するには、 CREATE INDEX CONCURRENTLY を使用します。これにより、他のセッションでの対象テーブルに対する INSERT、UPDATE、DELETE 操作がブロックされるのを回避できます。
PostgreSQL 12 以降を実行しているインスタンスの場合、インデックスを再構築するには、REINDEX CONCURRENTLY を使用します。PostgreSQL 11 以前の場合、置き換え用のインデックスを作成するには、CREATE INDEX ... CONCURRENTLY を使用し、その後元のインデックスを削除します。
一時テーブルを頻繁に作成および削除することは避けてください。これにより、システムテーブルのオーバーヘッドが増加します。ON COMMIT DROP の使用には注意してください。ほとんどの場合、一時テーブルの代わりに WITH 句を使用します。
PostgreSQL 13 では、パーティションテーブル、HashAggregate 操作（GROUP BY 句用）、および並列クエリのサポートが向上しました。可能な限り、インスタンスを PostgreSQL 13 にアップグレードしてください。詳細については、「ApsaraDB RDS for PostgreSQL インスタンスのメジャーエンジンバージョンをアップグレードする」をご参照ください。
カーソル機能を今後使用しない場合は、無効化してください。
テーブルレベルのデータ削除には、TRUNCATE を DELETE の代わりに使用します — TRUNCATE ははるかに高速です。
DDL 文をトランザクションで囲むことで、必要に応じてロールバックできるようにします。トランザクションは短時間で完了させるよう心がけてください。長時間実行される DDL トランザクションは、ロックされたオブジェクトに対する読み取り操作をブロックします。
バルクデータ書き込みの場合、書き込みスループットを最大化するために、COPY または複数行の INSERT INTO table VALUES (),(),...(); を使用します。

マイナーエンジンバージョン

レプリケーションスロット機能を使用するには、マイナーエンジンバージョンを20201230 以降に更新してください。これにより、論理レプリケーションスロットフェールオーバー機能が有効になり、最大レプリケーションスロット遅延メトリックに対するアラートルールを設定できるようになります。これにより、論理サブスクリプションが遅延したり中断されたりすることを防止できます。論理サブスクリプションが遅延または中断された場合、レプリケーションスロットが失われ、先行書き込みログ (WAL) レコードが蓄積します。詳細については、「論理レプリケーションスロットフェールオーバー」および「ApsaraDB RDS for PostgreSQL インスタンスのアラートルールの管理」をご参照ください。
監査ログ機能または Performance Insight 機能を利用するには、マイナーエンジンバージョンを 20211031 以降 に更新してください。
log_statement を all に設定すると、アクティブ接続数が 50 を超えるシナリオにおいて、パフォーマンスが約 4 倍向上し、CPU 使用率の急激な上昇も防止できます。

モニタリングとアラート

Initiative Alert をオンにして、モニタリングとアラート機能のデフォルトのアラートルールを有効化します。詳細については、「アラートの管理」をご参照ください。
ワークロードの特性に応じて、メモリ使用量のアラートしきい値を 85% ～ 95% の範囲で設定してください。

トラブルシューティング

最もリソースを消費する SQL ステートメント (Top SQL) を特定するには、「最もリソースを消費する SQL ステートメント (Top SQL) の特定」をご参照ください。

最もリソースを消費する SQL ステートメントを特定するには、「最もリソースを消費する SQL ステートメントの特定」をご参照ください。

設計

権限

最小権限の原則（PoLP）に従い、スキーマまたはロール単位で権限を管理します。各インスタンスに対して 2 つのロールを作成します。1 つは読み書き権限を持つロール、もう 1 つは読み取り専用権限を持つロールです。詳細については、「ApsaraDB RDS for PostgreSQL インスタンスでの権限の管理」をご参照ください。

アプリケーション層で読み書き分離を実装する場合は、読み取り専用のデータベースクライアントに読み取り専用ロールを割り当てます。

テーブル

スキーマ内のデータの型を、アプリケーション内のデータの型と一致させ、すべてのテーブルで一貫した入力規則を適用してください。これにより、型不一致エラーを防止し、インデックスが正しく使用されることを保証できます。
定期的にパージされる既存データを含むテーブルの場合、年または月単位でパーティション化します。データを削除するには、子パーティションに対して DROP または TRUNCATE を使用します — 子パーティションに対して DELETE を実行しないでください。
頻繁に更新されるテーブルの場合、作成時に FILLFACTOR を 85 に設定します。これにより、各ページのストレージの 15% がホットアップデート用に確保され、ページ分割が減少します。
```
CREATE TABLE test123(id int, info text) WITH(FILLFACTOR=85);
```
以下の命名規則を使用してください：
- 一時テーブル：名前の先頭に tmp_ を付与します。
- 子パーティションテーブル：名前の末尾にパーティションキーの値を付与します。たとえば、親テーブルが tbl で、年単位でパーティション分割されている場合、子テーブルの名前は tbl_2016、tbl_2017 のようになります。

インデックス

ApsaraDB RDS for PostgreSQL では、B ツリー、ハッシュ、GIN、GiST、SP-GiST、BRIN、RUM、Bloom、PASE の各インデックスタイプがサポートされています。RUM、Bloom、PASE は拡張インデックスタイプです。

インデックスタイプの選択：

B ツリー：デフォルトのインデックスタイプです。インデックスフィールドの合計サイズは 2,000 バイトを超えてはなりません。インデックスフィールドの合計サイズが 2,000 バイトを超える場合は、関数ベースのインデックス（例：ハッシュインデックス）を作成するか、インデックス化前にデータをアナライザで処理してください。
BRIN：カラム値が自然な線形順序（例：タイムスタンプ、自動インクリメント ID、ストリーミングデータなど）を持つ大規模テーブルに使用します。BRIN インデックスはコンパクトであり、範囲クエリを高速化します。
```
CREATE INDEX idx ON tbl USING BRIN(id);
```

大規模データセットのスキャンおよび分析以外では、全表スキャンを避けてください。ほとんどの PostgreSQL のデータ型はインデックスをサポートしています。

インデックスの命名規則：

インデックスタイプ	プレフィックス
主キーインデックス	`pk_`
一意インデックス	`uk_`
通常インデックス	`idx_`

データの型および文字セット

格納するデータに合ったデータの型を選択してください。数値データやツリー構造に自然に適合するデータに対して文字列型を使用しないでください。適切なデータの型を選択することで、クエリ効率が向上します。

ApsaraDB RDS for PostgreSQL では、数値、浮動小数点、Monetary（購入金額）、文字列、文字、バイナリ、日付／時刻、ブール値、列挙型、ジオメトリ、ネットワークアドレス、ビット列、テキスト検索、UUID、XML、JSON、配列、複合型、範囲、オブジェクト識別子、行番号、ラージオブジェクト、ltree 構造、Data Cube、地理情報、H-Store、pg_trgm モジュール、PostGIS、HyperLogLog など、幅広いデータの型がサポートされています。PostGIS では、ポイント、ラインセグメント、サーフェス、パス、緯度、経度、ラスタ、トポロジーなどのタイプをサポートしています。HyperLogLog は、可変精度で異なる値の個数をカウントする固定サイズの集合型データ構造です。

LC_COLLATE を UTF8 ではなく C に設定します。 C 照合順序は UTF8 照合順序よりもパフォーマンスが優れています。 UTF8 照合順序を使用する場合、LIKE クエリをサポートするには、インデックスで text_pattern_ops オペレータークラスを指定する必要があります。

ストアドプロシージャ

アプリケーションとデータベース間で多数の往復通信を必要とする複雑なビジネスロジックには、ストアドプロシージャ（例：PL/pgSQL ベースのプロシージャ）またはビルトイン関数を使用して、アプリケーションとデータベースのやり取りを削減してください。PostgreSQL では、分析関数、集計関数、ウィンドウ関数、数学関数、幾何学関数がサポートされています。

データクエリ

COUNT(*) を使用し、COUNT(column_name) や COUNT(constants) は使用しないでください。COUNT(*) は、行のカウントに使用される SQL-92 標準であり、NULL 値を含みます。COUNT(column_name) は NULL 値を除外し、異なる結果を生成します。
COUNT(DISTINCT) を複数の列にわたって使用する場合、列リストをかっこで囲みます：
```
COUNT( (col1, col2, col3) )
```
COUNT(DISTINCT) はすべての NULL 値を含むため、COUNT(*) と同じ結果を生成します。
SELECT * FROM t を使用しないでください。不要なデータの返却を防ぐために、必要な列のみを指定してください。
抽出・変換・書き出し（ETL）操作を除き、データベースクライアントに大規模な結果セットを返さないでください。クエリが異常に大規模な結果セットを返す場合は、クエリの実行計画が最適であるかどうかを確認してください。
範囲クエリには、GiST インデックス付きの範囲データ型を使用して、パフォーマンスを向上させます。
アプリケーションで多数の行を返すクエリを頻繁に実行する場合は、結果をバッチ単位で集約してください。たとえば、クエリが 100 行を返す場合、それらを 1 つの結果セットに集約します。アプリケーションが ID を使用して結果にアクセスする場合は、ID 単位で定期的に集約します。小さな結果セットは応答時間を短縮します。

インスタンス管理

SQL Explorer および監査機能を有効化すると、照会されたデータベース、実行ステータス、実行時間などの SQL 実行情報を照会およびエクスポートできます。この機能を使用して、SQL の健全性を診断したり、パフォーマンスの問題をトラブルシューティングしたり、トラフィックを分析できます。詳細については、「ApsaraDB RDS for PostgreSQL インスタンスで SQL Explorer および監査機能を使用する」をご参照ください。
Alibaba Cloud アカウント内の活動（コンソール、API、および開発者ツールへのアクセスを含む）をモニターおよび記録するには、ActionTrail を使用します。ActionTrail はこれらの操作をイベントとして記録し、ActionTrail コンソールからダウンロードしたり、Log Service の Logstore または Object Storage Service (OSS) バケットに配信したりできます。これは、セキュリティ分析、リソース変更追跡、またはコンプライアンス監査を目的としています。詳細については、「What is ActionTrail?」をご参照ください。
DDL 操作を実行する前に、内容をすべて確認し、非ピーク時間帯に DDL 変更をスケジュールしてください。
データを削除または変更するトランザクションをコミットする前に、対象となる行を確認するために SELECT 文を実行します。ビジネスロジックで正確に 1 行を更新する必要がある場合は、LIMIT 1 を追加します。
DDL 操作およびロックを取得する他の操作 — たとえば VACUUM FULL や CREATE INDEX — では、クエリを無期限にブロックすることを防ぐためにロックタイムアウトを設定します：
```
BEGIN;
SET LOCAL lock_timeout = '10s';
-- DDL 文;
END;
```
クエリの実行計画を確認するには、EXPLAIN ANALYZE を使用します。EXPLAIN とは異なり、EXPLAIN ANALYZE はクエリを実際に実行します。実行計画に DML 操作 (UPDATE、INSERT、または DELETE) が含まれる場合、ステートメントをトランザクションでラップし、検査後にロールバックして、意図しないデータ変更を回避します：
```
BEGIN;
EXPLAIN (ANALYZE) <DML (UPDATE/INSERT/DELETE) SQL>;
ROLLBACK;
```
大規模な削除または更新処理では、データをバッチ単位で処理してください。各バッチを個別のトランザクションで実行します。単一のトランザクションで全行を削除または更新すると、大量のジャンクデータが生成されます。