[postgress]VACUUMとは

postgres製のDBに大量のレコードをINSERT/ UPDATEしたら検索処理が遅くなった!

そんな時は、もしかしたらpostgresqlが追記型DBであることと、独自の機能VACUUM(バキューム)に原因かもしれません。

仕事でこの辺りのことを調べることがあったので、まとめたいと思います。

追記型DBの仕組み

VACUUMとは、DBのデータ操作で不要になった保存領域を回収する機能のことです。

ここでいう「不要になった保存領域」とは、UPDATEやDELETEを行った時に生じるデータのことです。
UPDATEが不要になる領域を作る理由としては、postgresqlが追記型DBであることに起因します。

追記型について簡単に説明すると、レコードの更新の際に「新しいレコードを更新後のレコードとする」仕組みのことをいいます。

| id | name |
|:-----------|------------:|:------------:|
| 1 | hukushima | NULL |
| 2 | kato | NULL |
| 3 | suzuki | NULL |
| 4 | ando | NULL |
| 5 | inoue | NULL |
| 6 | take | NULL |

UPDATE users set name = 'fukushima' WHERE id = 1;

この時、追記型DBであるpostgresでは、以下のような手順でUPDATE処理を行います。

①SQLの値を元に、新しいレコードを作成

| id | name |
|:-----------|------------:|:------------:|
| 1 | hukushima | NULL |
| 2 | kato | NULL |
| 3 | suzuki | NULL |
| 4 | ando | NULL |
| 5 | inoue | NULL |
| 6 | take | NULL |
| 1 | fukushima | NULL |
② ①の完了後、更新前とされるレコードに削除フラグを付ける

| id | name |
|:-----------|------------:|:------------:|
| 1 | hukushima *削除 | 2020:10:04 13:00:00 |
| 2 | kato | NULL |
| 3 | suzuki | NULL |
| 4 | ando | NULL |
| 5 | inoue | NULL |
| 6 | take | NULL |
| 1 | fukushima | NULL |

これにより、残った①のレコードが更新後のレコードとなります。
なお、ここでいう「削除フラグ」は論理削除(deleted_atカラムに値が入る)ではありません。
あくまで追記型DBとしてのpostgreqlの処理で削除フラグを付けるので、DBに対してSELECT文を実行して更新前のレコードを見つけることはできません。

VACUUMについて

役割

追記型DBでデータの更新を行うことにより、「最新状態より前のデータ」がどんどん増えていきます。
「削除フラグがついて削除されたデータ」は、参照されることもないのに保存領域を使用することになるので、DBのストレージをドンドン圧迫していきます。

この不要なデータの保存領域を整理するのがVACUUMです。
PCでも、ゴミ箱に入ったファイルはを見れなくなるのに、HDを圧迫していて、完全に削除することではじめてHDに空きができますね!イメージ的にはコレと同じです。

保存領域を確保することで以下のような効果が期待できます。

• 保存領域のデータ量削減によるコスト削減
• 併せてANALYZEを行うことにより、統計情報の最適化=SQL実行速度を改善

SQL文

-- ①特定のテーブルにVACUUMを実行
VACUUM (VERBOSE, ANALYZE) users;
-- VERBOSE バキュームの詳細ログを出力
-- ANALYZE テーブルの統計情報を更新

-- ②全テーブルに対しVACUUMを実行
VACUUM FULL;
-- バキュームSQLを実行するには テーブル所有者 または スーパーユーザ である必要あり

上記の通り、あるテーブルに対してのみVACUUMを行うor全テーブルに対してVACUUMを行うSQLがあります。
VACUUM FULLを行うと、大幅に保存領域を確保できるのですが、以下のような制限があります。

• 全テーブルに対してVACUUMするため処理に時間がかかる
• 全テーブルのコピーを行うので、コピー元+コピー後データ分の余分なディスク領域が必要になる
• 排他ロック(SELECT/UPDATE/DELETEができない)がかかる

上記のようにかなり使い勝手が悪い上に、
公式のヒントにも、大量の更新・削除をした場合で、通常のVACUUMではパフォーマンスが良くならない時にVACUUM FULLを選択肢に入れるようにと記述しています。

特定のテーブルに対してのみVACUUMを行うのが基本的な使い方になりそうです。

手動VACUUMと自動VACUUM

postgresには自動でVACUUMをしてくれる機能があり、しかもデフォルトで自動VACUUMを実行する設定になっています。
自動バキュームに関する設定はpostgresql.confで設定できます。
この辺りは少し混み入っているので、下記の資料を確認してもらえればと。
公式:24.1.6. 自動バキュームデーモン
Amazon RDS for PostgreSQL 環境の自動バキュームを理解する

補足

Q.定期的にVACUUMを行う必要がある追記型DBの利点はあるの？
A.更新(追記処理)中も更新前のレコードに行ロックがかからず、レコードを参照できるのが利点

Q.メンテナンスにVACUUMを実行するテーブルって、どんなテーブル？
A.頻繁に更新・削除を行うテーブル

Q.VACUUM関連でオススメの資料は?
A.↓ 上からオススメ順に
①今、改めて学ぶVACUUM
2018年postgresカンファレンスの動画。1hかからずVACUUMの基本を知ることができてオススメ

②自動VACUUMの状況を確認する
自動バキュームの実践的なSQL文が載っている