VACUUM

追記型データベース管理システムとVACUUM

PostgreSQLは追記型データベース管理システムである。
追記型とは次のような性質を言う。

  1. 行を削除した場合
    その行に対応する物理的な領域が不使用になる。

  2. 行を変更した場合
    変更後の行に対応する領域が新たに追加され、変更前の行に対応する領域が不使用になる。

不使用となった領域は再利用することもできないので、増え続けるとディスクを圧迫する。
これを制御するのがVACUUMである。
VACUUMは大きく分類して通常のVACUUMとFULL VACUUMの2つがある。
通常のVACUUMは不使用な領域を再利用可能にするだけ。
FULL VACUUMは不使用領域を物理的に削除してテーブルに対応するファイル自体を小さくする。
その他VACUUMには次のような機能がある。

・統計情報の更新
・トランザクションIDの周回エラーの防止

VACUUMはPostgreSQLを管理する上で、必ず定期的に実行する必要がある。

VACUUM [FULL] [ANALYZE] [テーブル名];

ANALYZE指定は統計情報の更新

自動VACUUM

バージョン8.0以前は、VACUUMは手動で行う必要があった。
バージョン8.1以降、自動VACUUMという機能が追加され、自動で行うことが可能となった。
バージョン8.3以降、自動VACUUMはデフォルトで有効になった。(それ以前はデフォルトで無効)

準備

  1. 次のSQLをファイルとして作成。(setup201.sql)

    
    
  2. ログイン画面から実行。

    oddtbs=> \i setup201.sql
    ...
    
  3. t1テーブルの確認。

    oddtbs=> select * from t1 limit 10;
     id | num
    ----+-----
      1 |   2
      2 |   4
      3 |   6
      4 |   8
      5 |  10
      6 |  12
      7 |  14
      8 |  16
      9 |  18
     10 |  20
    (10 rows)
    
    oddtbs=> select * from t1 order by id desc, num desc limit 10;
      id   |  num
    -------+-------
     10000 | 20000
      9999 | 19998
      9998 | 19996
      9997 | 19994
      9996 | 19992
      9995 | 19990
      9994 | 19988
      9993 | 19986
      9992 | 19984
      9991 | 19982
    (10 rows)
    
    oddtbs=> select count(*) from t1;
     count
    -------
     10000
    (1 row)
    

以降、2画面から操作

・自動VACUUMが動作する可能性があるので、画面①操作後の画面②の操作をすばやく行う。
・画面①のVACUUMを実行しなくても、同じ結果になる可能性が高い。
・ただし、VACUUM FULLは手動で実行しない限り動作しない。

画面①画面②
> psql oddtbs postgres
Password for user postgres: himitu
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
...

oddtbs=# \x
Expanded display is on.
oddtbs=# select * from pgstattuple('t1');
-[ RECORD 1 ]------+-------
table_len          | 368640
tuple_count        | 10000
tuple_len          | 320000
tuple_percent      | 86.81
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 7380
free_percent       | 2
oddtbs=> update t1 set num = num * 2;
UPDATE 10000
oddtbs=# select * from pgstattuple('t1');
-[ RECORD 1 ]------+-------
table_len          | 729088
tuple_count        | 10000
tuple_len          | 320000
tuple_percent      | 43.89
dead_tuple_count   | 10000
dead_tuple_len     | 320000
dead_tuple_percent | 43.89
free_space         | 6596
free_percent       | 0.9

※ 不使用領域が大幅に増えている。
oddtbs=> vacuum;
...
oddtbs=# select * from pgstattuple('t1');
oddtbs=# select * from pgstattuple('t1');
-[ RECORD 1 ]------+-------
table_len          | 729088
tuple_count        | 10000
tuple_len          | 320000
tuple_percent      | 43.89
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 326596
free_percent       | 44.8

※ 不使用領域が再利用可能領域になった。
oddtbs=> vacuum full;
...
oddtbs=# select * from pgstattuple('t1');
table_len          | 368640
tuple_count        | 10000
tuple_len          | 320000
tuple_percent      | 86.81
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 6700
free_percent       | 1.82

※ サイズ自体が小さくなった。
oddtbs=> delete from t1 where id > 5000;
DELETE 5000
oddtbs=# select * from pgstattuple('t1');
-[ RECORD 1 ]------+-------
table_len          | 368640
tuple_count        | 5000
tuple_len          | 160000
tuple_percent      | 43.4
dead_tuple_count   | 5000
dead_tuple_len     | 160000
dead_tuple_percent | 43.4
free_space         | 6700
free_percent       | 1.82

※ 不使用領域が大幅に増えている。
oddtbs=> vacuum;
...
oddtbs=# select * from pgstattuple('t1');
oddtbs=# select * from pgstattuple('t1');
-[ RECORD 1 ]------+-------
table_len          | 368640
tuple_count        | 5000
tuple_len          | 160000
tuple_percent      | 43.4
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 166700
free_percent       | 45.22

※ 不使用領域が再利用可能領域になった。
oddtbs=> vacuum full;
...
oddtbs=# select * from pgstattuple('t1');
-[ RECORD 1 ]------+-------
table_len          | 188416
tuple_count        | 5000
tuple_len          | 160000
tuple_percent      | 84.92
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 6980
free_percent       | 3.7

※ サイズ自体が小さくなった。