PostgreSQLでVACUUM FULLを実施したいテーブルをそれとなく抽出するSQLを作ってみた

はじめに

DBに対してVACUUM FULLを実施できないので、テーブルに対してVACUUM FULLを実施したかった。
ただ、全テーブルにVACUUM FULLはなかなか厳しいので、VACUUMを実施する対象のテーブルを抽出するためにこんな長ったらしいSQLを作った。
もう少しシンプルにできる気がする。

SQL

実際に動かしているSQLからちょっと変えているが、こんな感じ。

SELECT
     tablename                               -- テーブル名
    ,modification                            -- 更新日時
    ,last_ana                                -- 最後にanalyzeされた日
    ,last_vac                                -- 最後にvacuumされた日
    ,idxcnt                                  -- インデックスの数
    ,n_live_tup                              -- 生きているタプルの数
    ,n_dead_tup                              -- デッドタプルの数
    ,par                                     -- デッドタプルの割合
    ,size                                    -- テーブルサイズ(byte)
    ,total_pretty                            -- テーブルサイズ
    ,ROUND(size * (par / 100.0)) as deadsize -- デッドタプルのサイズ
    ,cmd_vac                                 -- VACUUM FULLのコマンド
    ,cmd_ana                                 -- ANALYZEのコマンド
FROM(
    SELECT
         tbl.tablename
        ,pg_relation_filepath(tablename::text) as filepath
        ,(pg_stat_file(pg_relation_filepath(tablename::text))).modification
        ,toast.relkind
        ,pg_total_relation_size(tablename::text) as size
        ,pg_size_pretty(pg_total_relation_size(tablename::text)) as total_pretty
        ,CASE WHEN cl.reltoastrelid IS NULL THEN idxcnt ELSE idxcnt + 1 END AS idxcnt
        ,CASE WHEN COALESCE(sut.last_analyze, '2000/01/01 00:00:00') > COALESCE(sut.last_autoanalyze, '2000/01/01 00:00:00')
              THEN COALESCE(sut.last_analyze, '2000/01/01 00:00:00')
              ELSE COALESCE(sut.last_autoanalyze, '2000/01/01 00:00:00')
         END AS last_ana
        ,CASE WHEN COALESCE(sut.last_vacuum, '2000/01/01 00:00:00') > COALESCE(sut.last_autovacuum, '2000/01/01 00:00:00')
              THEN COALESCE(sut.last_vacuum, '2000/01/01 00:00:00')
              ELSE COALESCE(sut.last_autovacuum, '2000/01/01 00:00:00')
         END AS last_vac
        ,sut.n_live_tup
        ,sut.n_dead_tup
        ,CASE WHEN n_live_tup + n_dead_tup = 0 THEN 0 ELSE ROUND(n_dead_tup / (n_live_tup + n_dead_tup) * 100.0, 2) END AS par
        ,'SELECT pg_size_pretty(pg_total_relation_size(''' || sut.relname || '''));' || chr(13) || chr(10) ||
         'VACUUM FULL ANALYZE ' || sut.relname || ';' || chr(13) || chr(10) ||
         'SELECT pg_size_pretty(pg_total_relation_size(''' || sut.relname || '''));' || chr(13) || chr(10) AS cmd_vac
        ,'ANALYZE ' || sut.relname || ';' as cmd_ana
    FROM pg_tables tbl
    INNER JOIN pg_class cl ON cl.relname = tbl.tablename
    INNER JOIN pg_stat_user_tables sut ON sut.relname = tbl.tablename
    LEFT JOIN (SELECT indrelid, count(*) as idxcnt FROM pg_index GROUP BY indrelid) as idxcnt ON idxcnt.indrelid = cl.oid
    LEFT JOIN pg_class toast ON cl.reltoastrelid = toast.oid
    -- ここの条件は環境・目的に合わせて設定する
    -- 存在しない・参照できないテーブルはここでフィルタリングするといい
    --WHERE tbl.schemaname='public'
    WHERE tablename NOT LIKE 'sql_%'
) as tbl
WHERE (pg_stat_file(filepath)).isdir = false
AND   (
    -- 断片化のサイズが100Mbyteを超える
       ROUND(size * (par / 100.0)) > 1024 * 1024 * 100
    -- 断片化が20%以上
    OR par >= 20
)
ORDER BY deadsize;

ポイント

たいしたポイントはないけれど

• idxcnt

  • インデックスの数が多いと、VACUUM FULLの時間がかかる気がするので、結構大事な情報

• par

  • 求め方が正しいか若干不安がある。ただ、100を超えるようなテーブルはVACUUM FULL欠けたほうがいいのは確かだと思う。

• ROUND(size * (par / 100.0)) as deadsize

  • かなり雑なデッドタプルのサイズ計算。VACUUM FULL後のサイズを見ると意外といい感じの値になってたりする。
    まぁ、参考程度で。

• (pg_stat_file(pg_relation_filepath(tablename::text))).modification

  • VACUUMでも日付が変わるので参考情報として参照すべき

• WHEREの(pg_stat_file(filepath)).isdir = false

  • いらない気がしているけど、念のため。

最後に

手段として正しいかどうかは疑問ですが、とりあえず、VACUUM FULLをどのテーブルに対して実施するかの参考資料的な結果は得られると思います。
あと、VACUUMじゃなくてpg_repack使うほうがいいんだろうけど、仕事の環境には入っていないのでVACUUMを使っている。
これでVACUUM FULLを実施して、テーブルサイズ小さくして、サーバーのディスクの空き容量を確保したりもしている。
だって、ひどいテーブルだと、数Gbyteのテーブルが1/10になったりするんだよね。
まぁ、これは更新処理の問題な気がするけど^^;