Postgresql ただのVacuumでもOSに領域を返却する事が有る

MBL開発部 Nです。

最近、Postgresqlを運用していて気になった所を調べてみました。
どんな事かと言いますと、10GB程度あるテーブルのデータを大量に削除した際にVacuumをFullオプション付きで行っていないのにもかかわらず、テーブルサイズが縮小し、OSのディスク空き容量も増えるということが二回あり、通常vacuumではOSに領域の返却は行われないと思っていたので一度目は見間違いかなと思っていました。

ドキュメントを見てみると9系のVacuumからこのように記載されています。

(FULLが指定されていない)通常のVACUUMは、単に領域を回収し、そこを再利用可能な状態に変更します。 この形式のコマンドでは排他的ロックが取得されていないため、テーブルへの通常の読み書き操作と並行して実行することができます。 しかし余った領域はオペレーティングシステムには(ほとんどの場合)返されません。 同じテーブル内で再利用できるように保持されるだけです。

ほとんどの場合返却されませんとの事ですので、返却されること自体は稀だけどもおかしくないようです。ではどのような場合に返却されるのでしょうか。

実験用にテーブルを作成します。DBは有りものを利用したので9.4系です。

手動で行いたいのでautovacuumを停止します。

データを投入し、1億5千万レコードで12GB程度のテーブルになりました。

大量に消せば回復するのかを試すためにidが偶数のものを削除して半分のレコードにしてみます。

vacuum analyze して見てみます。

テーブルサイズもindexも変わりませんでした。

さらに半分にしてみるために4で割って余り1になるidを削除しましたが変わらず。

レコードが領域全体に散らばっている状態だと回収されないようです。

ドキュメントには

例外として、テーブルの末尾に完全に空のページが存在し、かつそのテーブルの排他ロックが容易に獲得できるような特殊な場合には、その領域を返却します。

のように記載があるので、現状残っているレコードの後半(idが大きいもの)を半分削除してみます。

vacuum analyzeを行うと

テーブルサイズが縮小されました。しかしレコード数の割にサイズが大きいようです。

再度実験をします。一度全部レコードを削除し、vacuum fullをかけた状態からデータを投入します。

1億2800万レコードで大体10GBになりました。

半分弱(6000万レコード)ほど消してみます。

vacuum後サイズを確認。

サイズが縮小しました。データ後半に空きがあればOSに領域を返却することがあるようです。データがまばらに有る領域が無いので先の実験よりレコード数と領域サイズの比率が良いようです。

その他のパターンも実験した結果下記のような傾向がありそうです。

Vacuum Fullが出来ない(停止出来ない、空き容量が殆ど無い等)場合にチャレンジしてみても良いかもしれません。

再現から始めたので10GB程度のテーブルで実験しましたが、後方領域が空いていれば100MB程度のテーブルでも減るようです。