PostgreSQLは削除フラグがついて見えなくなっているだけのため、削除データを定期的にきれいにする必要があり、この処理をVACUUMという。
$ psql –version
psql (PostgreSQL) 14.2 (Ubuntu 14.2-1.pgdg20.04+1)
$ sudo -u postgres psql
psql (14.2 (Ubuntu 14.2-1.pgdg20.04+1))
Type “help” for help.
postgres=# \l
postgres=# \c testdb;
testdb=# \d department;
testdb=# select * from department;
testdb=# delete from department where department_code = ‘c’;
testdb=# vacuum verbose department;
INFO: vacuuming “public.department”
INFO: scanned index “pk_department” to remove 1 row versions
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: table “department”: removed 1 dead item identifiers in 1 pages
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: index “pk_department” now contains 5 row versions in 2 pages
DETAIL: 1 index row versions were removed.
0 index pages were newly deleted.
0 index pages are currently deleted, of which 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: table “department”: found 1 removable, 5 nonremovable row versions in 1 out of 1 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 747
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
vacuum ${table_name} でテーブルをvacuumできる。
なるほど。
autovacuumの設定はpostgres.confで設定されている。
