vacuum 初步使用

os:centos 7.4
postgresql: 10.3

版本

postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit
(1 row)

基表

postgres=# create table tmp_t0(
  c0 varchar(100),
  c1 varchar(100),
  c2 varchar(100)
)
;

postgres=# select oid,pc.relname,pc.relfilenode,pc.relpages,pc.reltuples,pc.reltoastrelid
       from pg_class pc
      where 1=1
        and pc.relname like '%tmp_t0%'
      ;
  oid  | relname | relfilenode | relpages | reltuples | reltoastrelid 
-------+---------+-------------+----------+-----------+---------------
 19015 | tmp_t0  |       19015 |        0 |         0 |             0
(1 row)

postgres=# select  pg_relation_size('tmp_t0'::regclass), 
         pg_total_relation_size('tmp_t0'::regclass),
         pg_table_size('tmp_t0'::regclass),
         pg_indexes_size('tmp_t0'::regclass),
         pg_relation_filenode('tmp_t0'::regclass),
         pg_relation_filepath('tmp_t0'::regclass);
         
 pg_relation_size | pg_total_relation_size | pg_table_size | pg_indexes_size | pg_relation_filenode | pg_relation_filepath 
------------------+------------------------+---------------+-----------------+----------------------+----------------------
                0 |                      0 |             0 |               0 |                19015 | base/13451/19015
(1 row)

插入1000w条数据

postgres=# insert into tmp_t0(c0,c1,c2)
 select md5(id::varchar),
        md5(md5(id::varchar)),
        md5(md5(md5(id::varchar)))
   from generate_series(1,10000000) as id  
 ;
INSERT 0 10000000

查看信息

postgres=# select oid,pc.relname,pc.relfilenode,pc.relpages,pc.reltuples,pc.reltoastrelid
       from pg_class pc
      where 1=1
        and pc.relname like '%tmp_t0%'
      ;
  oid  | relname | relfilenode | relpages | reltuples | reltoastrelid 
-------+---------+-------------+----------+-----------+---------------
 19015 | tmp_t0  |       19015 |   163935 |     1e+07 |             0
(1 row)
  
postgres=# select  pg_relation_size('tmp_t0'::regclass), 
         pg_total_relation_size('tmp_t0'::regclass),
         pg_table_size('tmp_t0'::regclass),
         pg_indexes_size('tmp_t0'::regclass),
         pg_relation_filenode('tmp_t0'::regclass),
         pg_relation_filepath('tmp_t0'::regclass);
         
 pg_relation_size | pg_total_relation_size | pg_table_size | pg_indexes_size | pg_relation_filenode | pg_relation_filepath 
------------------+------------------------+---------------+-----------------+----------------------+----------------------
       1342955520 |             1343307776 |    1343307776 |               0 |                19015 | base/13451/19015
(1 row)


$ ls -l base/13451/19015*
-rw------- 1 postgres postgres 1073741824 Aug  7 09:19 base/13451/19015
-rw------- 1 postgres postgres  269213696 Aug  7 09:20 base/13451/19015.1
-rw------- 1 postgres postgres     352256 Aug  7 09:20 base/13451/19015_fsm

从上面可以看出，该表做了 toast。

删除大量数据

postgres=# delete from tmp_t0 where c0 like 'a%' or c0 like 'b%' or c0 like 'c%';
DELETE 1873990

postgres=# delete from tmp_t0 where c0 like 'd%' or c0 like 'e%' or c0 like 'f%';
DELETE 1877499

查看删除后的信息

postgres=# select oid,pc.relname,pc.relfilenode,pc.relpages,pc.reltuples,pc.reltoastrelid
       from pg_class pc
      where 1=1
        and pc.relname like '%tmp_t0%'
      ;
  oid  | relname | relfilenode | relpages |  reltuples  | reltoastrelid 
-------+---------+-------------+----------+-------------+---------------
 19015 | tmp_t0  |       19015 |   163935 | 9.61085e+06 |             0
(1 row)

postgres=# select  pg_relation_size('tmp_t0'::regclass), 
         pg_total_relation_size('tmp_t0'::regclass),
         pg_table_size('tmp_t0'::regclass),
         pg_indexes_size('tmp_t0'::regclass),
         pg_relation_filenode('tmp_t0'::regclass),
         pg_relation_filepath('tmp_t0'::regclass);
         
 pg_relation_size | pg_total_relation_size | pg_table_size | pg_indexes_size | pg_relation_filenode | pg_relation_filepath 
------------------+------------------------+---------------+-----------------+----------------------+----------------------
       1342955520 |             1343315968 |    1343315968 |               0 |                19015 | base/13451/19015
(1 row)

$ ls -l base/13451/19015*
-rw------- 1 postgres postgres 1073741824 Aug  7 09:31 base/13451/19015
-rw------- 1 postgres postgres  269213696 Aug  7 09:31 base/13451/19015.1
-rw------- 1 postgres postgres     352256 Aug  7 09:28 base/13451/19015_fsm
-rw------- 1 postgres postgres       8192 Aug  7 09:31 base/13451/19015_vm

数据删除后，空间大小没什么变化，反而多了一个 19015_vm 文件。

vacuum处理

postgres=# vacuum verbose tmp_t0;
INFO:  vacuuming "public.tmp_t0"
INFO:  "tmp_t0": removed 1780975 row versions in 155570 pages
INFO:  "tmp_t0": found 1780975 removable, 5930272 nonremovable row versions in 155570 out of 163935 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 2055147
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 4.09 s, system: 1.20 s, elapsed: 6.65 s.
VACUUM

查看vacuum后的信息

postgres=# select oid,pc.relname,pc.relfilenode,pc.relpages,pc.reltuples,pc.reltoastrelid
       from pg_class pc
      where 1=1
        and pc.relname like '%tmp_t0%'
      ;
  oid  | relname | relfilenode | relpages |  reltuples  | reltoastrelid 
-------+---------+-------------+----------+-------------+---------------
 19015 | tmp_t0  |       19025 |   163935 | 6.24959e+06 |             0
(1 row)

  
postgres=# select  pg_relation_size('tmp_t0'::regclass), 
         pg_total_relation_size('tmp_t0'::regclass),
         pg_table_size('tmp_t0'::regclass),
         pg_indexes_size('tmp_t0'::regclass),
         pg_relation_filenode('tmp_t0'::regclass),
         pg_relation_filepath('tmp_t0'::regclass);	  
 pg_relation_size | pg_total_relation_size | pg_table_size | pg_indexes_size | pg_relation_filenode | pg_relation_filepath 
------------------+------------------------+---------------+-----------------+----------------------+----------------------
       1342955520 |             1343356928 |    1343356928 |               0 |                19025 | base/13451/19025
(1 row)


$ ls -l base/13451/19015*
-rw------- 1 postgres postgres 1073741824 Aug  7 09:40 19015
-rw------- 1 postgres postgres  269213696 Aug  7 09:40 19015.1
-rw------- 1 postgres postgres     352256 Aug  7 09:40 19015_fsm
-rw------- 1 postgres postgres      49152 Aug  7 09:40 19015_vm

空间完全没有释放!!!
vacuum只是清理了打了删除标记的行数据，并没有释放空间。
如果需要释放空间，需要做 vacuum full tmp_t0 操作。

参考：
http://postgres.cn/docs/10/sql-vacuum.html