Postgres验证vacuum是否维护索引

通过vacuum操作前后，表和索引的大小变化，侧面观察--关掉autovacuum

postgres=# show autovacuum;
autovacuum
------------
on
(1 row)
postgres=# alter system set autovacuum = 'off';
ALTER SYSTEM
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)

--创建测试表，插入数据，创建索引

postgres=# create table t1 (id int,c1 varchar);
CREATE TABLE
postgres=# insert into t1 select generate_series(1,100000),md5(random()::text);
INSERT 0 100000
postgres=# create index i1 on t1(c1);
CREATE INDEX
--记录当前表、索引大小

postgres=# select pg_size_pretty(pg_relation_size('t1'));
pg_size_pretty
----------------
6672 kB
(1 row)
postgres=# select pg_size_pretty(pg_relation_size('i1'));
pg_size_pretty
----------------
5792 kB
(1 row)

--连续两次全量更新表，记录表、索引变化

postgres=# update t1 set c1 = md5(random()::text);
UPDATE 100000
postgres=# select pg_size_pretty(pg_relation_size('t1'));
pg_size_pretty
----------------
13 MB
(1 row)
postgres=# select pg_size_pretty(pg_relation_size('i1'));
pg_size_pretty
----------------
13 MB
(1 row)
postgres=# update t1 set c1 = md5(random()::text);
UPDATE 100000
postgres=# select pg_size_pretty(pg_relation_size('t1'));
pg_size_pretty
----------------
20 MB
(1 row)
postgres=# select pg_size_pretty(pg_relation_size('i1'));
pg_size_pretty
----------------
22 MB
(1 row)

--执行vacuum，查看表、索引大小，再次更新表，查看表、索引大小

postgres=# vacuum t1;
VACUUM
postgres=# select pg_size_pretty(pg_relation_size('t1'));
pg_size_pretty
----------------
20 MB
(1 row)
postgres=# select pg_size_pretty(pg_relation_size('i1'));
pg_size_pretty
----------------
22 MB
(1 row)
postgres=# update t1 set c1 = md5(random()::text);
UPDATE 100000
postgres=# select pg_size_pretty(pg_relation_size('t1'));
pg_size_pretty
----------------
20 MB
(1 row)
postgres=# select pg_size_pretty(pg_relation_size('i1'));
pg_size_pretty
----------------
22 MB
(1 row)
--执行vacuum full查看表、索引大小
postgres=# vacuum full t1;
VACUUM
postgres=# select pg_size_pretty(pg_relation_size('t1'));
pg_size_pretty
----------------
6672 kB
(1 row)
postgres=# select pg_size_pretty(pg_relation_size('i1'));
pg_size_pretty
----------------
5792 kB
(1 row)

通过verbose参数查看细节

postgres=# vacuum (verbose) t1;
INFO: vacuuming "public.t1"
INFO: scanned index "i1" to remove 100000 row versions
DETAIL: CPU: user: 0.07 s, system: 0.00 s, elapsed: 0.08 s
INFO: "t1": removed 100000 row versions in 834 pages
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: index "i1" now contains 100000 row versions in 1632 pages
DETAIL: 100000 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: "t1": found 100000 removable, 100000 nonremovable row versions in 1667
out of 1667 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 803
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.13 s, system: 0.00 s, elapsed: 0.13 s.
INFO: vacuuming "pg_toast.pg_toast_51643"
INFO: "pg_toast_51643": found 0 removable, 0 nonremovable row versions in 0 out
of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 803
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM