18.PG的vacuum
通常块中被删除的行是逻辑上删除，物理上还保留在块中，如果长时间不清理，会造成垃圾空间膨胀，而清理的工作一般是由AutoVacuum后台进程来完成
vacuum处理对数据库中的指定表或所有表执行一下任务：
- 移除死元组: 1) 删除死元组并对每个页面的活元组进行碎片管理. 2) 删除指向死元组的索引元组
- 冷冻老的Txid: 1) 必要时冻结老元组的txid 2）更新冻结的与系统目录(pg_database和pg_class)相关的txid 3）如有可能，移除clog中不必要的部分
- 其他： 1）更新已处理表的FSM和VM 2)更新几个统计数据(pg_stat_all_tables等)
autovacuum守护进程：1）默认每分钟执行一次，由autovacuum_naptime参数定义
2）默认调用三个worker进程进程工作，有autovacuum_max_workers参数定义
vacuum和full vacuum
先来看看vacuum -- > 可以清理了块里面无效的空间，但是空间并未释放给系统
full vacuum --> 这种是把块里面有效的数据拿出来放在一个新块里面，然后旧块中无效数据被释放掉了，这种释放物理释放，把释放空间返给了操作系统了
参考
用插件进行模拟对一张大表进行删除之后，所在的块中的空间是否释放
postgres=# create table accounts(aid int,info text,c_time timestamp); CREATE TABLE postgres=# insert into accounts select generate_series(1,100000),md5(random()::text),clock_timestamp(); INSERT 0 100000 postgres=# select count(*) from accounts; count -------- 100000 (1 row) postgres=# select count(*) as "number of pages",pg_size_pretty(cast(avg(avail) as bigint )) as "Av.freespace size", postgres-# round(100 * avg(avail)/8192,2) as "Av,freespace ratio" from pg_freespace('accounts'); number of pages | Av.freespace size | Av,freespace ratio -----------------+-------------------+-------------------- 935 | 36 bytes | 0.43 -->这里用插件pg_freespacec查看到该表占用了935个块，且每个块占用的空间都快满了。 (1 row) postgres=# postgres=# postgres=# postgres=# delete from accounts where aid %10 != 0 or aid < 100; -->这里用delete删除表中的数据 DELETE 90009 postgres=# postgres=# select count(*) as "number of pages",pg_size_pretty(cast(avg(avail) as bigint )) as "Av.freespace size", round(100 * avg(avail)/8192,2) as "Av,freespace ratio" from pg_freespace('accounts'); number of pages | Av.freespace size | Av,freespace ratio -----------------+-------------------+-------------------- 935 | 36 bytes | 0.43 --> 这里发现删除之前还是和删除之后还是一样 (1 row) postgres=# vacuum accounts; --> 这里执行vacuum操作 VACUUM postgres=# postgres=# select count(*) as "number of pages",pg_size_pretty(cast(avg(avail) as bigint )) as "Av.freespace size", round(100 * avg(avail)/8192,2) as "Av,freespace ratio" from pg_freespace('accounts'); number of pages | Av.freespace size | Av,freespace ratio -----------------+-------------------+-------------------- 935 | 6974 bytes | 85.13 --> 这里可以看到执行vacuum之后，占用的块的数量没少，但是块中空间使用率降低了。 (1 row) postgres=# vacuum full accounts; --> 这里执行了full vacuum操作 VACUUM postgres=# postgres=# select count(*) as "number of pages",pg_size_pretty(cast(avg(avail) as bigint )) as "Av.freespace size", round(100 * avg(avail)/8192,2) as "Av,freespace ratio" from pg_freespace('accounts'); number of pages | Av.freespace size | Av,freespace ratio -----------------+-------------------+-------------------- 94 | 0 bytes | 0.00 --> 这里发现这张表占用块的数量减少了，但是每个块的使用率提升 (1 row) postgres=#
插件：
select count(*) as "number of pages",pg_size_pretty(cast(avg(avail) as bigint )) as "Av.freespace size", round(100 * avg(avail)/8192,2) as "Av,freespace ratio" from pg_freespace('表名');
