何故VACUUMを実行するのか？
PostgreSQLのテーブルで、行がUPDATEやDELETEが実行されるいかなる時も、dead rowsは置き去りにされています。 VACUUMはそれらを削除し、その領域が再利用できるようにしています。 もしテーブルがVACUUMされない場合には、そのテーブルは肥大化し、ディスク領域やシーケンシャルなテーブルスキャンの速度低下させます。（そしてわずかではありますが、index scanも）
VACUUMはまたテーブル行の凍結について面倒を見ており、Transaction IDカウンターの周回が発生した時の問題を回避するようになっていますが、これは別の話題です。
通常、これについて心配する必要はありません。なぜなら、PostgreSQLに組み込まれているautovacuumデーモンが代わりに面倒を見てくれるからです。
© xkcd.xom (Randall Munroe) under the Creative Commons Attribution-NonCommercial 2.5 License
問題
もしテーブルが肥大化していたら、autovacuumが動作しているかどうか確かめるのがまず始めにすることです。
SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_all_tables
ORDER BY n_dead_tup
/ (n_live_tup
* current_setting('autovacuum_vacuum_scale_factor')::float8
+ current_setting('autovacuum_vacuum_threshold')::float8)
DESC
LIMIT 10;
もし肥大化テーブルがこの実行結果に現れなかった場合には、n_dead_tupが0、かつlast_autovacuumはNULLとなっており、恐らく統計コレクタの問題であるかもしれません。
もし肥大化テーブルがこの実行結果の一番上であるが、last_autovacuumがNULLである場合には、autovacuumの設定をよりアグレッシブなものにして、そのテーブルに対するautovacuum動作を完了させる必要があるかもしれません。
しかし、時々この結果は次のようになります。
schemaname | relname | n_live_tup | n_dead_tup | last_autovacuum
------------+--------------+------------+------------+---------------------
laurenz | vacme | 50000 | 50000 | 2018-02-22 13:20:16
pg_catalog | pg_attribute | 42 | 165 |
pg_catalog | pg_amop | 871 | 162 |
pg_catalog | pg_class | 9 | 31 |
pg_catalog | pg_type | 17 | 27 |
pg_catalog | pg_index | 5 | 15 |
pg_catalog | pg_depend | 9162 | 471 |
pg_catalog | pg_trigger | 0 | 12 |
pg_catalog | pg_proc | 183 | 16 |
pg_catalog | pg_shdepend | 7 | 6 |
(10 rows)
こちらでは、autovacuumが最近実行されましたが、deadタプルを解放していません！
vacuum (verboseモード)で実行して、この問題を確認可能です。
test=> VACUUM (VERBOSE) vacme;
INFO: vacuuming "laurenz.vacme"
INFO: "vacme": found 0 removable, 100000 nonremovable row versions in
443 out of 443 pages
DETAIL: 50000 dead row versions cannot be removed yet,
oldest xmin: 22300
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s.
なぜdead rowsをvacuumは削除しないのか
vacuumはこれら行バージョン(またはタプルと知られている)で、もう必要なくなったもののみを削除することが出来ます。 もし削除トランザクションのtransaction ID（システム列のxmaxに保存されている）が、PostgreSQLデータベース(もしくは、クラスター全体での共有テーブル）の中で、アクティブなままで最も古いトランザクションよりも古い場合に、タプルは必要なくなります。
この値（上述のvacuum実行結果の22300）は"xmin horizon"と呼ばれています。[注1]
以下の３つはPostgreSQLクラスターの中で、このxmin horizonを押しとどめ得るものです。
1.ロングトランザクション
ロングトランザクションとそのxminの値は以下のクエリで確認することが出来ます。
SELECT pid, datname, usename, state, backend_xmin
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;
vacuumをブロックしているデータベースセッションを終了させるには、pg_terminate_backend()関数が使用できます。
2.使われていないレプリケーションスロット
レプリケーションスロットとは、スタンドバイサーバーがプライマリに追いつくために、まだ必要であった情報をPostgreSQLサーバーに破棄されるのを止めるデータ構造です。
もしレプリケーションが遅延し、もしくはスタンドバイサーバーが停止した場合に、レプリケーションスロットはvacuumが古い行を削除するのを防ぎます。
全てのレプリケーションスロットとそのxminの値は以下のクエリで確認出来ます。
SELECT slot_name, slot_type, database, xmin
FROM pg_replication_slots
ORDER BY age(xmin) DESC;
もう必要なくなったレプリケーションスロットは、pg_drop_replication_slot()関数を使って削除してください。
注意点: これはhot_standby_feedback=onの物理レプリケーションの場合のみ起こりえます。論理レプリケーションでは似たような危険はありますが、しかしながらシステムカタログのみ影響を受けます。その場合には、catalog_xminのカラムを観察してください。
3. 孤児となったprepared transaction
二層コミット中では、分散されたトランザクションはまず始めにPREPAREステートメントによって準備され、それからCOMMIT PREPAREDステートメントでコミットされます。
一度トランザクションが準備されると、これはコミットもしくは中断されるまで居座り続けます。 これはサーバーの再起動が実行されても生き続けるのです！通常、トランザクションはそれほど長くpreparedの状態で残ることはありませんが、時々何かが間違っていて、preparedトランザクションは管理者によって手動で削除されなくてはなりません。
preparedステートメントとそのxminの値は以下のクエリで確認出来ます。
SELECT gid, prepared, owner, database, transaction AS xmin
FROM pg_prepared_xacts
ORDER BY age(transaction) DESC;
preparedステートメントを削除するのは、ROLLBACK PREPAREDのSQLステートメントを使用してください。
[注1]: 翻訳者注釈。xmin horizonは現在最も古いtransacation IDが見ているxminの値。 本家英語版ドキュメントではpg_stat_activityのbackend_xminの説明に"xmin horizon"が使用されている。
