网站设计个人搜索引擎的两个基本方法

基本概念与区别

使用VACUUM、VACUUM FULL和ANALYZE命令定期对每个表进行维护，主要有以下原因：

• VACUUM FULL可回收已更新或已删除的数据所占据的磁盘空间，同时将小数据文件合并。
• VACUUM对每个表维护了一个可视化映射来跟踪包含对别的活动事务可见的数组的页。一个普通的索引扫描首先通过可视化映射来获取对应的数组，来检查是否对当前事务可见。若无法获取，再通过堆数组抓取的方式来检查。因此更新表的可视化映射，可加速唯一索引扫描。VACUUM可避免执行的事务数超过数据库阈值时，事务ID重叠造成的原有数据丢失。
• ANALYZE可收集与数据库中表内容相关的统计信息。统计结果存储在系统表PG_STATISTIC中。查询优化器会使用这些统计数据，生成最有效的执行计划。

📖 VACUUM与VACUUM FULL的主要区别：

• VACUUM命令不会阻塞SELECT和DML语句，但是会阻塞ALTER TABLE语句。
• VACUUM命令只是将被删除的dead tuples（死元组）标记为可重用，因此数据文件占用的实际物理磁盘空间并不会缩小。
• VACUUM FULL命令会阻塞对表的所有读写操作，包括SELECT语句。
• VACUUM FULL会物理删除dead tuples，把释放的空间交还给操作系统。本质上是新建了一个数据文件并把live tuples迁移过去，因此数据文件占用的磁盘空间会缩小到实际的数据量。

手动Vacuum和Analyze

使用VACUUM或VACUUM FULL命令，进行磁盘空间回收：

vacuum TABLE_NAME;                 --清理普通表
vacuum TABLE_NAME partition(P1);   --清理表分区vacuum full TABLE_NAME;  --彻底回收死元组占用的空间 

使用ANALYZE语句更新统计信息:

analyze TABLE_NAME;
analyze verbose TABLE_NAME;  --更新表统计信息并输出相关信息vacuum analyze TABLE_NAME;   --清理死元组的同时更新统计信息

⭐️维护建议：

• 定期对部分大表做VACUUM FULL，在性能下降后为全库做VACUUM FULL，目前暂定每月做一次VACUUM FULL。
• 定期对系统表做VACUUM FULL，主要是PG_ATTRIBUTE。
• 启用系统自动清理线程（AUTOVACUUM）自动执行VACUUM和ANALYZE，回收被标识为删除状态的记录空间，并更新表的统计数据。

查看Vacuum和Analyze记录

查看表的VACCUM和ANALYZE记录：

--所有表
select relname,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze,vacuum_count,autovacuum_count,analyze_count,autoanalyze_count
from pg_stat_all_tables;--仅系统表
select relname,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze,vacuum_count,autovacuum_count,analyze_count,autoanalyze_count
from pg_stat_sys_tables;--仅用户表
select relname,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze,vacuum_count,autovacuum_count,analyze_count,autoanalyze_count
from pg_stat_user_tables;

字段含义：

• last_vacuum：最近一次手动执行vacuum的时间；
• last_autovacuum：最近一次自动执行vacuum的时间；
• vacuum_count：手动执行vacuum的次数；
• autovacuum_count：自动执行vacuum的次数；
• last_analyze：最近一次手动执行表分析的时间；
• last_autoanalyze：最近一次自动执行表分析的时间；
• analyze_count：手动执行表分析的次数；
• autoanalyze_count：自动执行表分析的次数。

Autovacuum配置参数

查看autovacuum相关的参数配置：

--是否开启了vacuum自动清理
SHOW autovacuum;          --控制收集数据库活动的统计数据。开启autovacuum必须开启该参数      
SHOW track_counts;              --能同时运行的自动清理线程的最大数量
SHOW autovacuum_max_workers;    --仅在autovacuum设置为on的场景下生效，它控制autoanalyze或autovacuum的打开情况。
--analyze表示只执行autoanalyze，vacuum表示只执行autovacuum，
--mix表示autoanalyze和autovacuum都执行，none表示二者都不执行。
SHOW autovacuum_mode;           --设置两次自动清理操作的时间间隔，默认60秒。
SHOW autovacuum_naptime;--设置触发VACUUM的阈值。当表上被删除或更新的记录数超过设定的阈值时才会对这个表执行VACUUM操作。
SHOW autovacuum_vacuum_threshold;--设置触发ANALYZE操作的阈值。当表上被删除、插入或更新的记录数超过设定的阈值时才会对这个表执行ANALYZE操作。
SHOW autovacuum_analyze_threshold;