长事务影响vacuum和vacuum full

案例一
session 1

postgres=# create table t1 (id int);
CREATE TABLE
postgres=# insert into t1 values(generate_series(1,10000));
INSERT 0 10000
postgres=# begin;
BEGIN
postgres=# select txid_current();
 txid_current 
--------------
          502
(1 row)

session 2

postgres=# \dt+ t1
                    List of relations
 Schema | Name | Type  |  Owner   |  Size  | Description 
--------+------+-------+----------+--------+-------------
 public | t1   | table | postgres | 360 kB | 
(1 row)

postgres=# delete from t1;
DELETE 10000
postgres=# \dt+ t1
                    List of relations
 Schema | Name | Type  |  Owner   |  Size  | Description 
--------+------+-------+----------+--------+-------------
 public | t1   | table | postgres | 360 kB | 
(1 row)

postgres=# vacuum full verbose t1;
INFO:  vacuuming "public.t1"
INFO:  "t1": found 0 removable, 10000 nonremovable row versions in 45 pages
DETAIL:  10000 dead row versions cannot be removed yet.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s.
VACUUM
postgres=# \dt+ t1
                    List of relations
 Schema | Name | Type  |  Owner   |  Size  | Description 
--------+------+-------+----------+--------+-------------
 public | t1   | table | postgres | 360 kB | 
(1 row)

DETAIL: 10000 dead row versions cannot be removed yet.
将session 1 commit后，再次在session 2中执行成功

postgres=# vacuum full verbose t1;
INFO:  vacuuming "public.t1"
INFO:  "t1": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM

案例二

session

postgres=# create table t1 (id int);
CREATE TABLE
postgres=# insert into t1 values(generate_series(1,10000));
INSERT 0 10000
postgres=# begin;
BEGIN
postgres=# select txid_current();
 txid_current 
--------------
          514
(1 row)

postgres=# commit;
COMMIT
postgres=# 

session 2

postgres=# delete from t1 where id <1000;
DELETE 999
postgres=# vacuum verbose t1;
INFO:  vacuuming "public.t1"
INFO:  "t1": found 0 removable, 10000 nonremovable row versions in 45 out of 45 pages
DETAIL:  999 dead row versions cannot be removed yet, oldest xmin: 514
There were 0 unused item identifiers.
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.
VACUUM
postgres=# vacuum verbose t1;
INFO:  vacuuming "public.t1"
INFO:  "t1": removed 999 row versions in 5 pages
INFO:  "t1": found 999 removable, 187 nonremovable row versions in 6 out of 45 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 517
There were 0 unused item identifiers.
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.
VACUUM

session 1 有长事务没提交，导致session 2的vacuum 清理死元组失败，session 1 提交后，vacuum 成功。

表末端的数据如果是死元组，进行vacuum也能把空间释放掉。

postgres=# create table t1 (id int);
CREATE TABLE
postgres=# insert into t1 values(generate_series(1,10000));
INSERT 0 10000
postgres=# delete from t1 where id <5000;
DELETE 4999
postgres=# \dt+ t1
                    List of relations
 Schema | Name | Type  |  Owner   |  Size  | Description 
--------+------+-------+----------+--------+-------------
 public | t1   | table | postgres | 384 kB | 
(1 row)

postgres=# vacuum t1;
VACUUM
postgres=# \dt+ t1
                    List of relations
 Schema | Name | Type  |  Owner   |  Size  | Description 
--------+------+-------+----------+--------+-------------
 public | t1   | table | postgres | 392 kB | 
(1 row)

postgres=# create table t2 (id int);
CREATE TABLE
postgres=# insert into t2 values(generate_series(1,10000));
INSERT 0 10000
postgres=# \dt+ t2;
                    List of relations
 Schema | Name | Type  |  Owner   |  Size  | Description 
--------+------+-------+----------+--------+-------------
 public | t2   | table | postgres | 384 kB | 
(1 row)

postgres=# delete from t2 where id >5000;
DELETE 5000
postgres=# vacuum t2;
VACUUM
postgres=# \dt+ t2;
                    List of relations
 Schema | Name | Type  |  Owner   |  Size  | Description 
--------+------+-------+----------+--------+-------------
 public | t2   | table | postgres | 216 kB | 
(1 row)

表末端的数据如果是死元组，进行vacuum也能把空间释放掉。