PostgreSQL VACUUMコマンドによるメンテナンス

【PostgreSQL】メンテナンス　ー　VACUUM（基礎）
1. VACUUMとは？
【PostgreSQL】VACUUMの種類と実行方法
Autovacuumの設定とカスタマイズ
pgAgentを使ったVACUUMのスケジューリング実行
1. pgAgentのインストールとスケジュールの設定
まとめ

【PostgreSQL】メンテナンス　ー　VACUUM（基礎）

PostgreSQLはデータベースのパフォーマンスを維持するために定期的なメンテナンスが必要です。その中でも特に重要なのがVACUUMコマンドによる不要データの回収です。

本ブログではWindows環境におけるVACUUMの基本から運用方法までを紹介します。

VACUUMとは？

PostgreSQLはMVCC（Multi-Version Concurrency Control）を採用しており、更新や削除されたデータはすぐには物理的に消えず不要な領域（デッドタプル）として蓄積されていきます。

データベース操作で不要になったデータ（デッドタプル）を再利用可能領域としてマークを付加し、ストレージの効率を最適化する機能がVACUUMコマンドです。

VACUUMを実行することでデッドタプルを整理し、ディスク使用量の増加やパフォーマンスの低下を防ぐ事が可能となります。

図で示すと以下のようになります
- ID 002レコードの更新（テーブルA 変更後）
  - トランザクションコード106(XMAX)の処理でデータは更新されますが、レコード領域は残り、新たに ID 002のレコードが追加されます。
- ID 004レコードの削除（テーブルA 変更後）
  - ID 004のレコードは、トランザクション107(XMAX)で削除されますがレコード領域は残ります。
VACUUMを実行すると、更新/削除された領域は削除されませんが再利用可能となります。

ＶACUUMコマンド実行後
ＶACUUMを実行すると、FSMに空き領域として登録され再利用されます。

【PostgreSQL】VACUUMの種類と実行方法

基本的なVACUUM

VACUUMコマンドを実行する事によってデッドタプルを再利用できるようにします。ただし、ディスク領域自体の解放は行いません。

データベース内の全てのテーブルを対象とする場合

postgres-# VACUUM
postgres-#

特定のテーブルを対象とする場合

postgres=# VACUUM customers;
VACUUM
postgres=#

ANALYZEを含めたVACUUM

ANALYZEはPostgreSQLの統計情報を更新するコマンドです。SQLクエリが最適な実行計画を選択できるようテーブルのデータ分布を分析します。

postgres=# VACUUM ANALYZE;
VACUUM
postgres=#

完全なVACUUM（VACUUM FULL）

VACUUM FULLを実行すると未使用領域を解放し、テーブルのサイズを縮小できます。ただし、VACUUM FULL はデッドタプルを削除するだけでなく、新しい物理テーブルを作成しデータを再配置 します。

そのため、テーブル全体が一時的にロック され、他のトランザクションがそのテーブルを読み書きできなくなり、処理負荷が大きくなるため頻繁な使用は避けましょう。

postgres=# VACUUM FULL;
VACUUM
postgres=#

Windows環境でのVACUUM実行方法

Windows環境で手動でVACUUMを実行するには以下の方法があります。

pgAdmin 4
- 「Query Tool」を開き、上記のVACUUMコマンドを実行します。
psqlコマンドライン
- psqlを開き、データベースに接続後、VACUUMコマンドを実行します。

pgAgentを使用したスケジューリング実行（後述）

Autovacuumの設定とカスタマイズ

PostgreSQLでは、自動的にVACUUMを実行する「Autovacuum」がデフォルトで有効になっています。

自動VACUUMは不要領域の割合が高くなったテーブルや、最後にANALYZEを実行してからの累計変更行数が多くなったテーブルに対して自動的に実行されます。

postgresql.confのパラメータを調整する事で自動実行時間を調整できますが、自動実行の間隔によってはパフォーマンスに影響する為注意が必要です。

# Autovacuumの間隔を短くする
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50

# Autovacuumプロセスの最大並列実行数
autovacuum_max_workers = 3

pgAgentを使ったVACUUMのスケジューリング実行

Windows環境では、pgAgentを使用してVACUUMをスケジュール実行できます。

pgAgentのインストールとスケジュールの設定

pgAgentは、pgAdmin4のスタックビルダからインストールします。具体的なインストール方法とジョブのスケジューリングは以下のブログを参照下さい。

参考ブログ：https://www.kemmy-it.com/2024/08/19/pgadmin_3/

VACUUM ANALYZE コマンドを実行するバッチファイルの例。

@echo off
"C:\Program Files\PostgreSQL\bin\psql.exe" -U postgres -d test_db -c "VACUUM ANALYZE;"

まとめ

Windows環境でPostgreSQLを運用する際、VACUUMを適切に管理することは重要です。

基本的なVACUUM
手動で実行する方法
Autovacuumの設定
自動メンテナンスの最適化
pgAgentによるスケジュール実行
運用の効率化
バッチファイルとタスクスケジューラを活用

これらを適切に組み合わせることで、データベースのパフォーマンスを維持し、効率的な運用が可能になります。

【PostgreSQL】メンテナンス ー VACUUM（基礎）