postgresql 因存在长事务而导致vacuum失效

一.问题

        在数据库中查询某表时，已经走索引了，但是速度仍然慢。查询后发现该表占用磁盘较大，但是数据条数并不多，经查询后发现死元组过多（

SELECT n_dead_tup 
FROM pg_stat_user_tables 
WHERE relname = 'table name';

）。使用vacuum和vacuum full 命令回收表空间后，死元组并不能被删除。

二.原因

        vacuum命令会回收重用那些对所有事务不可见的死亡元组。当存在事务对某表中的死元组可见的情况下，vacuum就会失效，最终导致表膨胀。

       1.某长事务对表进行（如:insert、delete、update、alter table等操作并且未提交，那么vacuum操作会失效）；因为此时的死元组对该事务是可见的，尽管对其他事务不可见，但仍然无法删除死元组。

       2.某长事务不进行任何操作或者只进行简单的select查询操作，此时该长事务不会影响到vacuum操作，可以正常删除死元组。因为此时被回收表的死元组对于长事务是不可见的，其他事务在进行更改操作时，数据库会基于MVCC机制为该长事务维护一个更改前的数据版本。

三.解决方法

       kill掉长时间不活跃的事务（idle_in_transaction）。

      1.设置合理的 idle_in_transaction_session_timeout 参数值；

      2.手动查询到长事务，并kill；

--在服务器中查询长事务id -- oldest xmin
VACUUM verbose tablename;

--通过长事务id查询 pid
SELECT *
FROM pg_stat_activity 
WHERE backend_xmin = 'oldest xmin'
;

--杀死进程
--强杀 向后台发送sigterm信号，关闭当前后台进程，需要有超级用户权限，超级用户可以关闭所有后台进程，事务回滚
select pg_terminate_backend(pid)
;

--表回收
vacuum full tablename
;

至此，vacuum 命令失效的问题得到解决。