テーブルのバキューム処理と分析の手動による実行

データベースが自動バキュームプロセスによってバキューム処理されている場合は、データベース全体で手動によるバキューム処理をあまり頻繁に実行しないようにすることをお勧めします。手動バキュームを行うことで不要な I/O 負荷や CPU スパイクが発生し、デッドタプルの削除に失敗する可能性があります。デッドタプルに対してライブタプルの比率が低い場合や、自動バキューム間のギャップが長い場合など、本当に必要な場合にのみ、テーブルごとに手動バキューム処理を実行してください。さらに、ユーザーアクティビティが最小限の場合は、手動によるバキューム処理を実行する必要があります。

また、自動バキュームではテーブルの統計が最新の状態に保たれます。ANALYZE コマンドを手動で実行すると、これらの統計は更新されるのではなく再構築されます。通常の自動バキュームプロセスによって既に更新されている統計を再構築すると、システムリソースの使用率が増加する可能性があります。

以下のシナリオでは、VACUUM コマンドと ANALYZE コマンドを手動で実行することをお勧めします。

同時データベースアクティビティで VACUUM および ANALYZE コマンドを実行するときの I/O への影響を軽減するには、vacuum_cost_delay パラメータを使用できます。多くの場合、VACUUM や ANALYZE などのメンテナンスコマンドはすぐに終了する必要はありません。ただし、これらのコマンドが他のデータベース操作を実行するシステムの機能を妨げることがあってはなりません。これを防ぐには、vacuum_cost_delay パラメータを使用してコストベースの真空遅延を有効にできます。このパラメータは、手動で発行された VACUUM コマンドではデフォルトで無効になっています。有効にするには、ゼロ以外の値に設定してください。

バキューム操作とクリーンアップ操作を並行して実行する

VACUUM コマンドの PARALLEL オプションでは、インデックスバキュームフェーズとインデックスクリーンアップフェーズに並列ワーカーを使用しますが、これらはデフォルトで無効になっています。並列ワーカーの数 (並列処理の程度) は、テーブル内のインデックスの数によって決定され、ユーザーが指定できます。整数引数を使用せずに VACUUM 並列オペレーションを実行している場合、並列処理の程度はテーブル内のインデックスの数に基づいて計算されます。

次のパラメータは、Amazon RDS for PostgreSQL および Aurora PostgreSQL 互換で並列バキューム処理を設定するのに役立ちます。

次の例は、データベースで VACUUM および ANALYZE を手動で使用する場合のデータベースの動作を図示しています。

以下は、自動バキュームが無効になっているテーブルの例です (説明のみを目的としています。自動バキュームを無効にすることはお勧めしません)。

create table t1 ( a int, b int, c int );
alter table t1 set (autovacuum_enabled=false);

apgl=> \d+ t1
Table "public.t1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
b | integer | | | | plain | |
c | integer | | | | plain | |
Access method: heap
Options: autovacuum_enabled=false

テーブル t1 に 100 万行を追加する:

apgl=> select count(*) from t1;
count
1000000
(1 row)

テーブル t1 の統計情報:

select * from pg_stat_all_tables where relname='t1';
-[ RECORD 1 ]-------+--------
relid         | 914744
schemaname    | public
relname       | t1
seq_scan      | 0
seq_tup_read  | 0
idx_scan      |
idx_tup_fetch |
n_tup_ins     | 1000000
n_tup_upd     | 0
n_tup_del     | 0
n_tup_hot_upd | 0
n_live_tup    | 1000000
n_dead_tup    | 0
n_mod_since_analyze | 1000000
last_vacuum      |
last_autovacuum  |
last_analyze     |
last_autoanalyze |
vacuum_count     | 0
autovacuum_count | 0
analyze_count    | 0
autoanalyze_count | 0

インデックスを追加する:

create index i2 on t1 (b,a);

EXPLAIN コマンドを実行する (プラン 1):

Bitmap Heap Scan on t1 (cost=10521.17..14072.67 rows=5000 width=4)
Recheck Cond: (a = 5)
→ Bitmap Index Scan on i2 (cost=0.00..10519.92 rows=5000 width=0)
Index Cond: (a = 5)
(4 rows)

EXPLAIN ANALYZE コマンドを実行する (プラン 2):

explain (analyze,buffers,costs off) select a from t1 where b = 5;
QUERY PLAN
Bitmap Heap Scan on t1 (actual time=0.023..0.024 rows=1 loops=1)
Recheck Cond: (b = 5)
Heap Blocks: exact=1
Buffers: shared hit=4
→ Bitmap Index Scan on i2 (actual time=0.016..0.016 rows=1 loops=1)
Index Cond: (b = 5)
Buffers: shared hit=3
Planning Time: 0.054 ms
Execution Time: 0.076 ms
(9 rows)

テーブルで自動バキュームが無効になっており、EXPLAIN ANALYZE コマンドが手動で実行されなかったため、EXPLAIN コマンドと ANALYZE コマンドには異なるプランが表示されます。では、テーブルの値を更新し、EXPLAIN ANALYZE プランを再生成しましょう。

update t1 set a=8 where b=5;
explain (analyze,buffers,costs off) select a from t1 where b = 5;

EXPLAIN ANALYZE コマンド (プラン 3) が次のように表示されるようになりました。

apgl=> explain (analyze,buffers,costs off) select a from t1 where b = 5;
QUERY PLAN
Bitmap Heap Scan on t1 (actual time=0.075..0.076 rows=1 loops=1)
Recheck Cond: (b = 5)
Heap Blocks: exact=1
Buffers: shared hit=5
→ Bitmap Index Scan on i2 (actual time=0.017..0.017 rows=2 loops=1)
Index Cond: (b = 5)
Buffers: shared hit=3
Planning Time: 0.053 ms
Execution Time: 0.125 ms

プラン 2 とプラン 3 のコストを比較すると、統計情報をまだ収集していないため、プランと実行時間に相違が見られることがわかります。

次に、テーブルで ANALYZE を手動で実行し、統計情報を確認してプランを再生成しましょう。

apgl=> analyze t1
apgl→ ;
ANALYZE
Time: 212.223 ms

apgl=> select * from pg_stat_all_tables where relname='t1';
-[ RECORD 1 ]-------+------------------------------
relid          | 914744
schemaname     | public
relname        | t1
seq_scan       | 3
seq_tup_read   | 1000000
idx_scan       | 3
idx_tup_fetch  | 3
n_tup_ins      | 1000000
n_tup_upd      | 1
n_tup_del      | 0
n_tup_hot_upd  | 0
n_live_tup     | 1000000
n_dead_tup     | 1
n_mod_since_analyze | 0
last_vacuum       |
last_autovacuum   |
last_analyze      | 2023-04-15 11:39:02.075089+00
last_autoanalyze  |
vacuum_count      | 0
autovacuum_count  | 0
analyze_count     | 1
autoanalyze_count | 0

Time: 148.347 ms

EXPLAIN ANALYZE コマンドを実行する (プラン 4):

apgl=> explain (analyze,buffers,costs off) select a from t1 where b = 5;
QUERY PLAN
Index Only Scan using i2 on t1 (actual time=0.022..0.023 rows=1 loops=1)
Index Cond: (b = 5)
Heap Fetches: 1
Buffers: shared hit=4
Planning Time: 0.056 ms
Execution Time: 0.068 ms
(6 rows)

Time: 138.462 ms

テーブルを手動で分析して統計情報を収集した後、すべてのプランの結果を比較すると、オプティマイザのプラン 4 が他のプランよりも優れており、クエリの実行時間も短縮されていることがわかります。この例では、データベースでメンテナンスアクティビティを実行することの重要性を示しています。

VACUUM FULL を使用してテーブル全体を書き換える

FULL パラメータを使用して VACUUM コマンドを実行すると、テーブルの内容全体が余分なスペースのない新しいディスクファイルに書き換えられ、未使用のスペースがオペレーティングシステムに返されます。この操作によってかなり遅くなるため、各テーブルで ACCESS EXCLUSIVE のロックが必要になります。また、テーブルの新しいコピーが書き込まれ、操作が完了するまで古いコピーは除去されないため、余分なディスク容量が必要となります。

VACUUM FULL は、次のような場合に便利です。:

非プライマリキーテーブルを使用していて、データベースがダウンタイムを許容できる場合は、VACUUM FULL を使用することをお勧めします。

VACUUM FULL は他の操作よりも多くのロックを必要とするため、重要なデータベースで実行するとコストが高くなります。この方法を置き換えるには、次のセクションで説明する pg_repack 拡張機能を使用できます。このオプションは VACUUM FULL に似ていますが、ロックを最小限にする必要があり、Amazon RDS for PostgreSQL と Aurora PostgreSQL 互換の両方でサポートされています。