PostgreSQL VACUUM対象を抽出
VACUUM対象となるテーブルをpg_stat_user_tablesより参照
SQL
--VACUUM対象を抽出
SELECT
relname,--テーブル名
n_dead_tup,--不要行（推定）
last_vacuum,--手動VACUUM最終時刻（FULLを除く）
vacuum_count --手動VACUUM回数（FULLを除く）
FROM
pg_stat_user_tables
WHERE
n_dead_tup > 0;
VACUUM対象
pgdb=# SELECT relname, n_dead_tup, last_vacuum, vacuum_count FROM pg_stat_user_tables WHERE n_dead_tup > 0;
relname | n_dead_tup | last_vacuum | vacuum_count
---------+------------+-------------+--------------
foo | 8040 | | 0
bar | 16080 | | 0
baz | 24120 | | 0
(3 行)
VACUUM実行
SQL
VACUUM foo
VACUUM実行後
pgdb=# SELECT relname, n_dead_tup, last_vacuum, vacuum_count FROM pg_stat_user_tables;
relname | n_dead_tup | last_vacuum | vacuum_count
---------+------------+----------------------------+--------------
foo | 0 | 2014-07-01 12:00:00.444+09 | 1
bar | 16080 | | 0
baz | 24120 | | 0
(3 行)
