[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      |



```sql
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文

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

-- ②全テーブルに対しVACUUMを実行
VACUUM FULL;
-- バキュームSQLを実行するには テーブル所有者 または スーパーユーザ である必要あり
```
上記の通り、あるテーブルに対してのみVACUUMを行うor全テーブルに対してVACUUMを行うSQLがあります。
VACUUM FULLを行うと、大幅に保存領域を確保できるのですが、以下のような制限があります。

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

上記のようにかなり使い勝手が悪い上に、
[公式のヒント](https://www.postgresql.jp/document/10/html/routine-vacuuming.html#AUTOVACUUM)にも、大量の更新・削除をした場合で、通常のVACUUMではパフォーマンスが良くならない時にVACUUM FULLを選択肢に入れるようにと記述しています。

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

#### 手動VACUUMと自動VACUUM
postgresには自動でVACUUMをしてくれる機能があり、しかもデフォルトで自動VACUUMを実行する設定になっています。
自動バキュームに関する設定は`postgresql.conf`で設定できます。
この辺りは少し混み入っているので、下記の資料を確認してもらえればと。
[公式:24.1.6. 自動バキュームデーモン](https://www.postgresql.jp/document/10/html/routine-vacuuming.html)
[Amazon RDS for PostgreSQL 環境の自動バキュームを理解する](https://aws.amazon.com/jp/blogs/news/understanding-autovacuum-in-amazon-rds-for-postgresql-environments/)


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

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

Q.VACUUM関連でオススメの資料は?
A.↓  上からオススメ順に
①[今、改めて学ぶVACUUM](https://www.youtube.com/watch?v=TMAWwonPGmU)
2018年postgresカンファレンスの動画。1hかからずVACUUMの基本を知ることができてオススメ

②[自動VACUUMの状況を確認する](https://qiita.com/mkyz08/items/ffc34c13510bd1c5adb1)
自動バキュームの実践的なSQL文が載っている