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 操作。
