PostgreSQL nonremovable row versions message

测试环境:
存在两个事务,: 事务A，事务B
事务A先启动,
事务B后启动,

表结构
rmt_rescue=> create table tbl_test(id int,name text);
CREATE TABLE
rmt_rescue=> create table tbl_test1(id int,name text);
CREATE TABLE

事务A：
rmt_rescue=> begin ;
BEGIN
rmt_rescue=> insert into tbl_test select generate_series(1,10000),'test' ;
INSERT 0 10000

事务B：
rmt_rescue=> insert into tbl_test1 select generate_series(1,10000),'digoal';
INSERT 0 10000
Time: 43.269 ms
rmt_rescue=> analyze tbl_test1;
ANALYZE
Time: 3.198 ms
rmt_rescue=> select pg_relation_size('tbl_test1');
 pg_relation_size
------------------
           450560
(1 row)
Time: 0.322 ms
rmt_rescue=> delete from tbl_test1;
DELETE 10000
Time: 8.925 ms
rmt_rescue=> vacuum analyze verbose tbl_test1;
INFO:  vacuuming "rmt_rescue.tbl_test1"
INFO:  index "idx_test1" now contains 10000 row versions in 30 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "tbl_test1": found 0 removable, 10000 nonremovable row versions in 55 out of 55 pages
DETAIL:  10000 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming "pg_toast.pg_toast_2473792"
INFO:  index "pg_toast_2473792_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_2473792": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "rmt_rescue.tbl_test1"
INFO:  "tbl_test1": scanned 55 of 55 pages, containing 0 live rows and 10000 dead rows; 0 rows in sample, 0 estimated total rows
VACUUM
Time: 13.408 ms
事务B，出现了不可移除的行版本.
rmt_rescue=> vacuum full analyze verbose tbl_test1;
INFO:  vacuuming "rmt_rescue.tbl_test1"
INFO:  analyzing "rmt_rescue.tbl_test1"
INFO:  "tbl_test1": scanned 55 of 55 pages, containing 0 live rows and 10000 dead rows; 0 rows in sample, 0 estimated total rows
VACUUM
Time: 185.097 ms
事务B，VACUUM FULL ANALYZE并没有回收这些PAGE。
rmt_rescue=> select pg_relation_size('tbl_test1');
 pg_relation_size
------------------
           450560
(1 row)
事务A，（经测试rollback和commit都一样,只要结束事务）
rmt_rescue=> rollback;
ROLLBACK

事务B，
rmt_rescue=> vacuum analyze verbose tbl_test1;
INFO:  vacuuming "rmt_rescue.tbl_test1"
INFO:  scanned index "idx_test1" to remove 10000 row versions
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "tbl_test1": removed 10000 row versions in 55 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "idx_test1" now contains 0 row versions in 30 pages
DETAIL:  10000 index row versions were removed.
27 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "tbl_test1": found 10000 removable, 0 nonremovable row versions in 55 out of 55 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "tbl_test1": truncated 55 to 0 pages
DETAIL:  CPU 0.00s/0.01u sec elapsed 0.01 sec.
INFO:  vacuuming "pg_toast.pg_toast_2473792"
INFO:  index "pg_toast_2473792_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_2473792": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "rmt_rescue.tbl_test1"
INFO:  "tbl_test1": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
VACUUM
Time: 35.836 ms
rmt_rescue=> select pg_relation_size('tbl_test1');
 pg_relation_size
------------------
                0
(1 row)
页面被回收。

因此:
当数据库的UPDATE,DELETE操作较为频繁时不要做长事务的操作，因为在此时间段内的其他表不能回收垃圾空间。将造成数据表的膨胀效应。
如果发生了膨胀效应，应在业务低谷时使用VACUUM FULL回收垃圾空间。

紧急处理:
如果事务实在是太长了,并且还在继续，同时其他的表已经膨胀到影响性能的情况下，可以建个临时表,truncate来回收垃圾空间.
repo=> select pg_relation_size('tbl_test1');
 pg_relation_size
------------------
           892928
(1 row)

repo=> begin;
BEGIN
repo=> lock table tbl_test1 in exclusive mode;
LOCK TABLE
repo=> create table tbl_test2 (like tbl_test1);
CREATE TABLE
repo=> insert into tbl_test2 select * from tbl_Test1;
INSERT 0 10000
repo=> truncate table tbl_test1;
TRUNCATE TABLE
repo=> insert into tbl_Test1 select * from tbl_test2;
INSERT 0 10000
repo=> commit;
COMMIT
repo=> select pg_relation_size('tbl_test1');
 pg_relation_size
------------------
           450560
(1 row)

总结 ：
1. 当某个事物中带有DML(insert,update,delete),select 除外,已经其他对数据库有更改的操作；在这个事务处理过程中，vacuum都无法回收FREE 空间。
2. SELECT在数据库端处理过程中(返回结果集阶段不算在内),也会对vacuum回收FREE 空间有堵塞。