PostgreSQL Vaccum 比较

VACUUM 是PostgreSQL提供的回收垃圾空间的SQL命令。

8.4的语法如下：

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ] 

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]

9.0的语法如下：

VACUUM [ ( { FULL | FREEZE | VERBOSE | ANALYZE } [, ...] ) ] [ table [ (column [, ...] ) ] ] VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ] 

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]

而8.4以后，pg_autovacuum的表已经没有了

9.0的vacuum full 可以回收INDEX的PAGES，而8.3 & 8.4是不可以的，下面看一个例子：

创建测试表

create table tbl_test1 (id serial8 primary key,pid int8);

插入测试数据

postgres=#  insert into tbl_test1 (pid) select generate_series(333333,100000000000);

^C 中途退出

Cancel request sent

ERROR:  canceling statement due to user request

回收垃圾

postgres=#  VACUUM  VERBOSE tbl_test1;

INFO:  vacuuming "public.tbl_test1"

INFO:  scanned index "tbl_test1_pkey" to remove 2458496 row versions

DETAIL:  CPU 0.00s/0.00u sec elapsed 0.01 sec.

INFO:  "tbl_test1": removed 2458496 row versions in 13290 pages

DETAIL:  CPU 0.00s/0.06u sec elapsed 0.06 sec.

INFO:  index "tbl_test1_pkey" now contains 0 row versions in 7635 pages

DETAIL:  0 index row versions were removed.

7631 index pages have been deleted, 7631 are currently reusable.

CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  "tbl_test1": found 0 removable, 0 nonremovable row versions in 15047 out of 15047 pages

DETAIL:  0 dead row versions cannot be removed yet.

There were 325045 unused item pointers.

0 pages are entirely empty.

CPU 0.02s/0.12u sec elapsed 0.15 sec.

INFO:  "tbl_test1": truncated 15047 to 0 pages

DETAIL:  CPU 0.03s/0.03u sec elapsed 0.07 sec.

VACUUM

再来一次

postgres=#  VACUUM  VERBOSE tbl_test1;

INFO:  vacuuming "public.tbl_test1"

INFO:  index "tbl_test1_pkey" now contains 0 row versions in 7635 pages

DETAIL:  0 index row versions were removed.

7631 index pages have been deleted, 7631 are currently reusable.

CPU 0.00s/0.01u sec elapsed 0.01 sec.

INFO:  "tbl_test1": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages

DETAIL:  0 dead row versions cannot be removed yet.

There were 0 unused item pointers.

0 pages are entirely empty.

CPU 0.00s/0.01u sec elapsed 0.01 sec.

VACUUM

可看出 VACUUM 不回收index的空闲页面

postgres=# vacuum full verbose tbl_test1;

INFO:  vacuuming "public.tbl_test1"

VACUUM

postgres=#  VACUUM  VERBOSE tbl_test1;

INFO:  vacuuming "public.tbl_test1"

INFO:  index "tbl_test1_pkey" now contains 0 row versions in 1 pages

DETAIL:  0 index row versions were removed.

0 index pages have been deleted, 0 are currently reusable.

CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  "tbl_test1": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages

DETAIL:  0 dead row versions cannot be removed yet.

There were 0 unused item pointers.

0 pages are entirely empty.

CPU 0.00s/0.00u sec elapsed 0.00 sec.

VACUUM

从 VACUUM FULL可以看出9.0已经可以回收了index的空闲空间。

8.4用例：

postgres=# VACUUM VERBOSE tbl_test1;

INFO:  vacuuming "public.tbl_test1"

INFO:  scanned index "tbl_test1_pkey" to remove 1355152 row versions

DETAIL:  CPU 0.04s/0.29u sec elapsed 0.59 sec.

INFO:  "tbl_test1": removed 1355152 row versions in 7326 pages

DETAIL:  CPU 0.06s/0.06u sec elapsed 2.12 sec.

INFO:  index "tbl_test1_pkey" now contains 0 row versions in 3718 pages

DETAIL:  929860 index row versions were removed.

3706 index pages have been deleted, 1166 are currently reusable.

CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  "tbl_test1": found 0 removable, 0 nonremovable row versions in 7326 out of 7326 pages

DETAIL:  0 dead row versions cannot be removed yet.

There were 0 unused item pointers.

0 pages are entirely empty.

CPU 0.14s/0.36u sec elapsed 2.76 sec.

INFO:  "tbl_test1": truncated 7326 to 0 pages

DETAIL:  CPU 0.04s/0.00u sec elapsed 0.06 sec.

VACUUM

postgres=# VACUUM VERBOSE tbl_test1;

INFO:  vacuuming "public.tbl_test1"

INFO:  index "tbl_test1_pkey" now contains 0 row versions in 3718 pages

DETAIL:  0 index row versions were removed.

3714 index pages have been deleted, 3714 are currently reusable.

CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  "tbl_test1": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages

DETAIL:  0 dead row versions cannot be removed yet.

There were 0 unused item pointers.

0 pages are entirely empty.

CPU 0.00s/0.00u sec elapsed 0.00 sec.

VACUUM

postgres=# VACUUM FULL VERBOSE tbl_test1;

INFO:  vacuuming "public.tbl_test1"

INFO:  "tbl_test1": found 0 removable, 0 nonremovable row versions in 0 pages

DETAIL:  0 dead row versions cannot be removed yet.

Nonremovable row versions range from 0 to 0 bytes long.

There were 0 unused item pointers.

Total free space (including removable row versions) is 0 bytes.

0 pages are or will become empty, including 0 at the end of the table.

0 pages containing 0 free bytes are potential move destinations.

CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "tbl_test1_pkey" now contains 0 row versions in 3718 pages

DETAIL:  3714 index pages have been deleted, 3714 are currently reusable.

CPU 0.00s/0.00u sec elapsed 0.00 sec.

VACUUM

与9.0的功能一样，但输出比9.0少，并且VACCUM FULL没有回收空闲的index空间。

8.4，8.3 在TRUNCATE 后回收了表空间。

postgres=# TRUNCATE table tbl_test1;

TRUNCATE TABLE

Time: 53.141 ms

postgres=# VACUUM VERBOSE tbl_test1;

INFO:  vacuuming "public.tbl_test1"

INFO:  index "tbl_test1_pkey" now contains 0 row versions in 1 pages

DETAIL:  0 index row versions were removed.

0 index pages have been deleted, 0 are currently reusable.

CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  "tbl_test1": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages

DETAIL:  0 dead row versions cannot be removed yet.

There were 0 unused item pointers.

0 pages are entirely empty.

CPU 0.00s/0.00u sec elapsed 0.00 sec.

VACUUM

Time: 11.222 ms