診断テーブルとインデックスの肥大化

PostgreSQL のマルチバージョン同時実行制御 (MVCC) を使用すると、データの整合性を維持できます。PostgreSQL MVCC は、トランザクションがコミットまたはロールバックされるまで、更新または削除された行 (タプルとも呼ばれます) の内部コピーを保存することによって機能します。この保存された内部コピーはユーザーからは見えません。ただし、これらの非表示のコピーが VACUUM または AUTOVACUUM ユーティリティによって定期的にクリーンアップされない場合、テーブルが肥大化する可能性があります。テーブルの肥大化をチェックしないと、ストレージコストの増加や処理速度の低下につながる可能性があります。

多くの場合、Aurora の VACUUM または AUTOVACUUM のデフォルト設定は、不要なテーブルの肥大化を処理するのに十分なものです。ただし、次のような状況がアプリケーションで発生している場合は、肥大化がないかどうかを確認することをお勧めします。

はじめに、dead タプルでどの程度のスペースを使用しているか、テーブルとインデックスの肥大化をクリーンアップすることでどれだけ回復が見込めるか、最大限正確な情報を収集してください。そのためには、pgstattuple 拡張機能を使用して、Aurora クラスターの統計情報を収集します。詳細については、「pgstattuple」を参照してください。pgstattuple 拡張機能を使用する権限は、pg_stat_scan_tables ロールとデータベースのスーパーユーザーに限定されます。

Aurora で pgstattuple 拡張機能を作成するには、クライアントセッション (psql や pgAdmin など) をクラスターに接続し、次のコマンドを使用します。

CREATE EXTENSION pgstattuple;

プロファイルする各データベースで、拡張機能を作成します。拡張機能を作成したら、コマンドラインインターフェイス (CLI) を使用して、使用できないスペースをどの程度再利用できるかを測定します。統計を収集する前に、AUTOVACUUM を 0 に設定してクラスターパラメータグループを変更します。0 に設定すると、アプリケーションによって残されたデットタプルを Aurora が自動的にクリーンアップすることを防止しますが、結果の精度に影響を与える可能性があります。次のコマンドを入力して、単純なテーブルを作成します。

postgres=> CREATE TABLE lab AS SELECT generate_series (0,100000);
SELECT 100001

次の例では、DB クラスターの AUTOVACUUM をオンにしてクエリを実行しています。dead_tuple_count は 0 で、これは AUTOVACUUM が PostgreSQL データベースから古いデータやタプルを削除したことを示します。

pgstattuple を使用してテーブルに関する情報を収集するには、クエリにテーブルの名前またはオブジェクト識別子 (OID) を指定します。

postgres=> SELECT * FROM pgstattuple('lab');

    
    
table_len  | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
3629056    | 100001      | 2800028   | 77.16         | 0                | 0              | 0                  | 16616      | 0.46
(1 row)
        
    

次のクエリでは、AUTOVACUUM をオフにして、テーブルから 25,000 行を削除するコマンドを入力しています。その結果、dead_tuple_count は 25000 に増加しました。

postgres=> DELETE FROM lab WHERE generate_series < 25000;    

DELETE 25000  
   

SELECT * FROM pgstattuple('lab');
    

        
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
3629056 | 75001 | 2100028 | 57.87 | 25000 | 700000 | 19.29 | 16616 | 0.46
(1 row)        
    
    

これらのデットタプルを再利用するには、VACUUM プロセスを開始します。

アプリケーションを中断せずに肥大化をモニタリングする

Aurora クラスターの設定は、ほとんどのワークロードでベストプラクティスが実現できるように最適化されています。ただし、アプリケーションや使用パターンに合わせてクラスターを最適化する場合もあります。この場合、ビジー状態のアプリケーションを中断することなく pgstattuple 拡張機能を使用できます。そのためには、次のステップを実行します。

アプリケーションを実行して結果を確認したら、復元したコピーに pg_repack または VACUUM FULL を使用して違いを比較します。dead_tuple_count、dead_tuple_len、dead_tuple_percent のいずれかが大幅に減少している場合は、本稼働クラスターのバキュームスケジュールを調整して肥大化を最小限に抑えています。

テンポラリテーブルでの肥大化の回避

アプリケーションでテンポラリテーブルを作成する場合、そのテンポラリテーブルが不要になった場合はアプリケーションから削除してください。自動バキュームプロセスでは、テンポラリテーブルを検索しません。テンポラリテーブルをそのままにしておくと、データベースがすぐに肥大化してしまう可能性があります。さらに、この肥大化はシステムテーブルにまで及ぶ可能性があります。システムテーブルは、pg_attribute や pg_depend などの PostgreSQL オブジェクトや属性を追跡する内部テーブルです。

テンポラリテーブルが不要になったら、TRUNCATE ステートメントを使用してテーブルを空にして、スペースを空けることができます。次に、pg_attribute テーブルと pg_depend テーブルのバキューム処理を手動で実行します。これらのテーブルをバキューム処理することで、テンポラリテーブルを継続的に作成、切り捨て、削除しても、タプルが増えることでシステムが肥大化することがなくなります。

次の構文を追加して、コンテンツがコミットされた際に新しい行を削除することで、テンポラリテーブルを作成する際にこの問題を回避できます。

CREATE TEMP TABLE IF NOT EXISTS table_name(table_description) ON COMMIT DELETE ROWS;

ON COMMIT DELETE ROWS 句は、トランザクションがコミットされたときにテンポラリテーブルを切り捨てます。

インデックスの肥大化の回避

テーブルのインデックス付きフィールドを変更した場合、インデックスを更新すると、そのインデックスに 1 つまたは複数のデットタプルが生成されます。デフォルトでは、自動バキューム処理によってインデックスの肥大化をクリーンアップしますが、このクリーンアップにはかなりの時間とリソースが必要です。テーブルの作成時にインデックスクリーンアップ設定を指定するには、vacuum_index_cleanup 句を含めてください。デフォルトでは、テーブルの作成時にこの句は AUTO に設定されます。つまり、サーバーは、テーブルのバキューム処理時にインデックスのクリーンアップが必要かどうかを判断します。句を ON に設定すると、特定のテーブルのインデックスクリーンアップを有効になります。OFF に設定すると、そのテーブルのインデックスクリーンアップを無効にできます。インデックスのクリーンアップをオフにすると時間を節約できる可能性がありますが、インデックスが肥大化する可能性があることに注意してください。

コマンドラインでテーブルのバキューム処理を行うと、インデックスのクリーンアップを手動で制御できます。テーブルをバキュームしてデッドタプルをインデックスから削除するには、INDEX_CLEANUP 句を ON の値とテーブル名で記述します。

acctg=> VACUUM (INDEX_CLEANUP ON) receivables;
        
INFO: aggressively vacuuming "public.receivables"
VACUUM

インデックスを消去せずにテーブルをバキューム処理するには、OFF の値を指定します。

acctg=> VACUUM (INDEX_CLEANUP OFF) receivables;
        
INFO: aggressively vacuuming "public.receivables"
VACUUM