autovacuum

相关查询语句

select relname,reltuples  from pg_class where relname='pgbench_accounts';
show autovacuum_vacuum_scale_factor ;
select count(*) from pgbench_accounts;
\dt+ pgbench_accounts
SELECT * FROM pgstattuple('pgbench_accounts'); --需要开启插件
SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables;

表膨胀

揭开表膨胀的神秘面纱

0424

1、在进行autovacuum时，如果涉及到长事务的表，系统会等待该事务完成后再执行autovacuum。这是因为长事务可能持有表上的锁，并且正在进行修改操作，如果在此时执行autovacuum可能会导致数据一致性问题。

2、idle状态只是表示当前连接的空闲状态，并不能确定该连接是否涉及长事务。一个连接可以在idle状态下等待用户输入或者执行简单查询，也可以在此期间涉及复杂的长时间运行的事务。因此，仅凭idle状态无法确定是否存在长事务。

要查看长事务，可以通过检查数据库系统中存储的相关信息来判断。autovacuum会根据其触发条件自动执行，通常不会发生无法及时处理的情况。但是，建议对于需要更精确控制和监视的长事务，可以使用其他工具或查询系统视图来获取更详细的信息，从而有效优化和改善数据库性能。

select pid, backend_start, xact_start, query_start, waiting, state, query, usename 
from pg_stat_activity where now() - xact_start > interval '30 min';

SELECT pid, backend_start, xact_start, query_start, waiting, state, query, usename 
FROM pg_stat_activity 
WHERE now() - xact_start > interval '30 min'
  AND state = 'idle'
  AND query NOT ILIKE '%autovacuum%';

select * from pg_stat_all_tables;

优化方法：

1. 将批量删除操作与后续的DML操作分开放置在不同的事务中。这样可以避免删除操作对后续DML操作的影响，减少表膨胀问题的发生。

2. 在执行完批量删除后，手动执行VACUUM TABLE命令。这个命令可以回收被删除记录所占用的空间，并且不会对表进行锁定。通过手动执行该命令，可以重用已删除记录的空间，减少额外物理空间的占用。

3. 考虑在批量删除操作之后设置一段适当的时间间隔，再执行下一次的批量操作。这样以一定的频率进行批量删除，可以更好地优化表的性能并完善整个流程。根据实际情况调整时间间隔，以平衡删除操作和其他DML操作之间的影响。