PostgreSQL存储空间复用测试

最近有朋友问如下问题：
一个表中的数据经过增删改后并执行vacuum table操作后，发现后续插入的记录的ctid比老的数据还要小，这是为什么。
原因：这是空间重复利用的结果，是正常的。由于数据的记录是存储在某个块中的，当数据被删除后该记录的地方在块中就会形成空洞，如果没有任何事物再需要这个记录的时候，经过vacuum操作后，就会标识这条记录的空间可以被重复利用了，也就是说新插入的记录是可以放到该位置了，这个地方的ctid比老记录的ctid小就是正常的了。
测试结果：

1. 创建一张测试表，并向表中插入100w条测试记录。

sde=# create table t4 (id int,info text);

CREATE TABLE

sde=# insert into t4 select generate_series(1,1000000),md5(random()::text);

INSERT 0 1000000

2. 查询记录id从1000到1010这几条记录的ctid。

sde=# select ctid from t4 where id>10000 and id<10010;

  ctid

---------

 (83,41)

 (83,42)

 (83,43)

 (83,44)

 (83,45)

 (83,46)

 (83,47)

 (83,48)

 (83,49)

(9 行记录)

3. 删除这几条记录，并vacuum。

sde=# delete  from t4 where id>10000 and id<10010;

DELETE 9

sde=# vacuum verbose t4;

INFO:  vacuuming "sde.t4"

INFO:  "t4": removed 9 row versions in 1 pages

INFO:  "t4": found 9 removable, 999991 nonremovable row versions in 8334 out of 8334 pages

描述:  0 dead row versions cannot be removed yet, oldest xmin: 873827

There were 0 unused item pointers.

Skipped 0 pages due to buffer pins, 0 frozen pages.

0 pages are entirely empty.

CPU: user: 0.25 s, system: 0.00 s, elapsed: 0.25 s.

INFO:  vacuuming "pg_toast.pg_toast_52633"

INFO:  index "pg_toast_52633_index" now contains 0 row versions in 1 pages

描述:  0 index row versions were removed.

0 index pages have been deleted, 0 are currently reusable.

CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.

INFO:  "pg_toast_52633": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages

描述:  0 dead row versions cannot be removed yet, oldest xmin: 873827

There were 0 unused item pointers.

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

发现清除了9条记录。
5. 继续插入几条记录

sde=# insert into t4 values (1000001,md5(random()::text));

INSERT 0 1

sde=# insert into t4 values (1000002,md5(random()::text));

INSERT 0 1

sde=# insert into t4 values (1000003,md5(random()::text));

INSERT 0 1

sde=# insert into t4 values (1000004,md5(random()::text));

INSERT 0 1

sde=# insert into t4 values (1000005,md5(random()::text));

INSERT 0 1

sde=# select ctid from t4 where id>=1000001 ;

  ctid

---------

 (83,41)

 (83,42)

 (83,43)

 (83,44)

 (83,45)

(5 行记录)

可以看到新插入的记录放到了刚才删除的那几条记录的位置上，继续插入

sde=# insert into t4 values (1000006,md5(random()::text));

INSERT 0 1

sde=# insert into t4 values (1000007,md5(random()::text));

INSERT 0 1

sde=# insert into t4 values (1000008,md5(random()::text));

INSERT 0 1

sde=# insert into t4 values (1000009,md5(random()::text));

INSERT 0 1

sde=# insert into t4 values (10000010,md5(random()::text));

INSERT 0 1

sde=# select ctid from t4 where id>=1000001 ;

   ctid

-----------

 (83,41)

 (83,42)

 (83,43)

 (83,44)

 (83,45)

 (83,46)

 (83,47)

 (83,48)

 (83,49)

 (8333,41)

(10 行记录)

当把块内的空洞沾满后，新插入的记录会放到最后一个块上了。

文章来源：https://blog.csdn.net/liufeng1980423/article/details/81163952