涉及命令和机制介绍
MVCC机制
一般MVCC有2种实现方法：
1、写新数据时，把旧数据转移到一个单独的地方，如回滚段中，其他人读数据时，从回滚段中把旧的数据读出来，如Oracle数据库和MySQL中的innodb引擎。
2、写新数据时，旧数据不删除，而是把新数据插入。PostgreSQL就是使用的这种实现方法。
PostgreSQL的MVCC优点
无论事务进行了多少操作，事务回滚可以立即完成
数据可以进行很多更新，不必像Oracle和MySQL的Innodb引擎那样需要经常保证回滚段不会被用完，也不会像oracle数据库那样经常遇到“ORA-1555”错误的困扰
PostgreSQL的MVCC缺点
旧版本的数据需要清理。当然，PostgreSQL 9.x版本中已经增加了自动清理（AUTOVACUUM）的辅助进程来定期清理
旧版本（dead tuple）的数据可能会导致查询需要扫描的数据块增多，从而导致查询变慢
VACUUM命令
VACUUM收回由dead tuple占用的存储空间。在通常的PostgreSQL操作中，被删除或者被更新废弃的元组并没有在物理上从它们的表中移除，它们将一直存在直到一次VACUUM被执行。
VACUUM ANALYZE命令
VACUUM ANALYZE对每一个选定的表ANALYZE，更新优化器用以决定最有效执行一个查询的方法的统计信息
VACUUM FULL 命令
选择“完全”清理，它可以收回更多空间，并且需要更长时间和表上的排他锁。这种方法还需要额外的磁盘空间，因为它会创建该表的一个新拷贝，并且在操作完成之前都不会释放旧的拷贝。通常这种方法只用于需要从表中收回数量庞大的空间时。
VACUUMDB 命令
用于清理一个PostgreSQL数据库的工具。vacuumdb也将产生由PostgreSQL查询优化器所使用的内部统计信息。
是 SQL 命令VACUUM的一个包装器。在通过这个工具和其他方法访问服务器来清理和分析数据库之间没有实质性的区别。
Vacuumdb 相当于vacuum
Vacuumdb -z 相当于 vacuum analyze
Vacuumdb -f 相当于 vacuum full
AUTOVACUUM后台进程
PostgreSQL有一个可选的但是被高度推荐的特性autovacuum，它的目的是自动执行VACUUM和ANALYZE 命令。当它被启用时，自动清理会检查被大量插入、更新或删除元组的表。在默认配置下，自动清理是被启用的并且相关配置参数已被正确配置。
恢复磁盘空间不缩小文件
使用vacuum命令
jzq=# \d List of relations Schema | Name | Type | Owner --------+----------+-------+---------- public | newtab01 | table | postgres public | t1 | table | postgres (2 rows) jzq=# vacuum t1; VACUUM |
VACUUM的标准形式移除表和索引中的死亡行版本并将该空间标记为可在未来重用。不过，它将不会把该空间交还给操作系统。
使用vacuumdb命令
[postgres@c7slave ~]$ vacuumdb -U postgres --dbname=jzq --table=t1 -z -v vacuumdb: vacuuming database "jzq" INFO: vacuuming "public.t1" INFO: "t1": found 0 removable, 2 nonremovable row versions in 1 out of 1 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 584 There were 1 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. INFO: analyzing "public.t1" INFO: "t1": scanned 1 of 1 pages, containing 2 live rows and 0 dead rows; 2 rows in sample, 2 estimated total rows |
使用autovacuum后台进程（推荐）
打开配置后自动周期性的运行，无需人为干预。9.4及以上默认都是开启的。
autovacuum = on #默认为on track_counts = on #默认为on autovacuum_max_workers = 3 #默认为3，可以根据自身业务调整 autovacuum_naptime = 1min #默认1min，指定自动清理在任意给定数据库上运行的最小延迟，可自调 autovacuum_vacuum_threshold = 50 #默认50，指定能在一个表上触发VACUUM的被插入、被更新或被删除元组的最小数量 |
瘦身磁盘文件（完全清理）
使用vacuum full 命令
postgres=# \c jzq You are now connected to database "jzq" as user "postgres". jzq=# vacuum full t1; VACUUM jzq=# |
使用vacuumdb -f 命令
[postgres@c7slave ~]$ vacuumdb -U postgres --dbname=jzq --table=t1 -z -v -f vacuumdb: vacuuming database "jzq" INFO: vacuuming "public.t1" INFO: "t1": found 0 removable, 2 nonremovable row versions in 1 pages DETAIL: 0 dead row versions cannot be removed yet. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO: analyzing "public.t1" INFO: "t1": scanned 1 of 1 pages, containing 2 live rows and 0 dead rows; 2 rows in sample, 2 estimated total rows |
完全清理的过程
1）创建新的表文件
表上获取AccessExclusiveLock锁，并创建一个大小为8KB的新的表文件，原表不允许任何访问。
2）将活元组（有效数据）复制到新表
3）删除旧文件，重建索引并更新统计信息FSM和VM
完全清理的限制与注意项
1）当执行完整清理时，没有人可以访问（读/写）表。
2）最多会临时使用两倍于表的磁盘空间；因此在处理大表时，有必要检测剩余磁盘容量
何时需要瘦身的建议
没有固定最佳建议，可以使用pg_freespacemap来查询判断
jzq=# insert into t1 (id,name) SELECT i, 'text:' || i FROM generate_series(1,10000) i; INSERT 0 10000 jzq=# select count(*) from t1; count ------- 10002 (1 row) jzq=# update t1 set id = random() ; UPDATE 10002 jzq=# update t1 set id = random() ; UPDATE 10002 jzq=# update t1 set id = random() ; UPDATE 10002 jzq=# update t1 set id = random() ; UPDATE 10002 jzq=# update t1 set id = random() ; UPDATE 10002 jzq=# create extension pg_freespacemap; CREATE EXTENSION jzq=# jzq=# jzq=# \dxS+ pg_freespacemap; Objects in extension "pg_freespacemap" Object description ---------------------------------------- function pg_freespace(regclass) function pg_freespace(regclass,bigint) (2 rows) jzq=# SELECT count(*) as "number_of_pages",pg_size_pretty(cast(avg(avail) as bigint)) "Av_freespace_size" ,round(100 * avg(avail)/8192 ,2) as "Av_freespace_ratio" FROM pg_freespace('t1'); number_of_pages | Av_freespace_size | Av_freespace_ratio -----------------+-------------------+-------------------- 161 | 12 kB | 150.84 (1 row) jzq=# vacuum full t1; VACUUM jzq=# SELECT count(*) as "number_of_pages",pg_size_pretty(cast(avg(avail) as bigint)) "Av_freespace_size" ,round(100 * avg(avail)/8192 ,2) as "Av_freespace_ratio" FROM pg_freespace('t1'); number_of_pages | Av_freespace_size | Av_freespace_ratio -----------------+-------------------+-------------------- 27 | 0 bytes | 0.00 (1 row) |
可以看出目前t1表原来是用161个page，平均每个page的空闲大小为12kB，整个表的空闲大小大约161*12kB = 1932kB，在vacuum full瘦身后，只使用了17个page并且全部填充满了
开源插件
pg_repack https://reorg.github.io/pg_repack/ 兼容12
通过记录完全清理中的表的变更到日志中，达到不锁定表的效果。
需要额外剩余足够的磁盘空间（原表大小 + 索引 + 额外的日志表空间）
pgcompacttable https://github.com/dataegret/pgcompacttable 已无更新
总结
Ø基于频繁更新的表的dead tuple清理重复使用和调整执行计划，达到sql执行计划准确的目的，推荐使用autovacuum后台自动运行进程，无需人工干预，不会对生成环境造成性能影响。手动的去执行vacuum命令意义不大，autovacuum更加智能，并且可通过配置优化调整。
Ø基于瘦身磁盘，使用完全清理需要在非生产时间执行，并且保证清理对象的双倍磁盘空间
参考资料：
http://mysql.taobao.org/monthly/2017/10/01/
《PostgreSQL指南：内幕探索》第六章
PostgreSQL中文社区中文文档
