去年记录过在处理<案例：openGauss/postgreSQL 数据库手动清理膨胀Heap Bloat (dead tup)>时，遇到了一个问题：在opengauss系统中，当删除表后，操作系统并没有立即释放相应可用空间。当时没有深入研究，只是建议通过终止会话或重启来解决。最近发现同事在PostgreSQL中也遇到了类似问题，不过PostgreSQL采用进程模式，可以像Oracle一样通过终止操作系统进程来释放文件句柄。此外，从2021年2月11日发布的版本开始，PostgreSQL已改进了这个问题。然而，Opengauss V5目前仍存在该问题。以下将简单演示。

修复如下:

Ensure that disk space allocated for a dropped relation is released promptly at commit (Thomas Munro)

Previously, if the dropped relation spanned multiple 1GB segments, only the first segment was truncated immediately. Other segments were simply unlinked, which doesn’t authorize the kernel to release the storage so long as any other backends still have the files open.

以前，如果删除的关系跨越多个 1GB 段，则只会立即截断第一个段。其他段只是取消链接，只要任何其他后端仍然打开文件，就不会授权内核释放存储。 修复版本2021-02-11 后 Release的(9.5.25+, 9.6.21+, 10.16+, 11.11+, 12.6+, 13.2+, 14+, 15+, 16+ …);

测试方法

构建1GB以上的测试表：

create unlogged table test1 (id1 int ,id2 int,id3 int,v1 varchar,v2 varchar);

insert into test1 select a,a,a,'hello','hello' from generate_series(1,22000000) a;

PostgreSQL中修复以前

# session 1

postgres=# create unlogged table  test1 (id1 int ,id2 int,id3 int,v1 varchar,v2 varchar);
CREATE TABLE
postgres=# insert into test1 select a,a,a,'hello','hello' from generate_series(1,22000000) a;
INSERT 0 22000000

postgres=# select pg_backend_pid();
         550580
postgres=# select * from test1 where id1=1;
 id1 | id2 | id3 |  v1   |  v2
-----+-----+-----+-------+-------
   1 |   1 |   1 | hello | hello

# session 2
postgres=# drop table test1;
DROP TABLE

# session 3

$ lsof -p 550580|grep delete
postgres 550580 postgres 28u REG 253,0 74186752 672021 /app/pg12/data/base/13672/97283.1 (deleted)

# session 2
postgres=# checkpoint ;
CHECKPOINT

# session 3
$ lsof -p 550580|grep delete
postgres 550580 postgres 27u REG 253,0         0  670183 /app/pg12/data/base/13672/97283 (deleted)
postgres 550580 postgres 28u REG 253,0  74186752  672021 /app/pg12/data/base/13672/97283.1 (deleted)

Note:
这里lsof 只是grep deleted，如grep 文件号会更好，但也能看到其实除了第1个段，drop和checkpoint后其它段并未释放空间给OS。

PostgreSQL修复后

# session 1
-- same above ,ignore

# session 2
postgres=# drop table test1;
DROP TABLE

# session 3
$ lsof -p 3651791|grep delete
postgres 3651791 postgres 39u REG 253,0         0  692750 /app/pg12_8/data/base/13672/16457.1 (deleted)

# session 2
postgres=# checkpoint ;
CHECKPOINT
postgres=#

# session 3
$ lsof -p 3651791|grep delete
postgres 3651791 postgres 38u REG 253,0         0  683618 /app/pg12_8/data/base/13672/16457 (deleted)
postgres 3651791 postgres 39u REG 253,0         0  692750 /app/pg12_8/data/base/13672/16457.1 (deleted)

Note:
除了第一个段以外，XX.n的文件也已经为0 bytes，已释放给OS.

当前的Opengauss v5(Mogdb 5.0.1 )

# session 1
anbob=# create unlogged table  test100 (id1 int ,id2 int,id3 int,v1 varchar,v2 varchar);
CREATE TABLE

anbob=# insert into test100 select a,a,a,'hello','hello' from generate_series(1,22000000) a;
INSERT 0 22000000
db1=# \dt+ test100
                                      List of relations
 Schema |  Name   | Type  | Owner |  Size   |             Storage              | Description
--------+---------+-------+-------+---------+----------------------------------+-------------
 public | test100 | table | omm   | 1102 MB | {orientation=row,compression=no} |
(1 row)

anbob=# select pg_relation_filepath('test100');
 pg_relation_filepath
----------------------
 base/18596/49829

db1=# select count(*) from test100;
  count
----------
 22000000
(1 row)

# session 2

[omm@mogdb1 18596]$ ls -l 49829*
-rw------- 1 omm omm 1073741824 Mar 17 16:21 49829
-rw------- 1 omm omm   81543168 Mar 17 16:20 49829.1
-rw------- 1 omm omm     303104 Mar 17 16:21 49829_fsm
-rw------- 1 omm omm          0 Mar 17 16:19 49829_init

[omm@mogdb1 18596]$ lsof |grep 49829
mogdb      8384                             omm  417u      REG              253,0 1073741824    2844512 /MogDB/data/base/18596/49829
mogdb      8384                             omm  418u      REG              253,0     303104    2844524 /MogDB/data/base/18596/49829_fsm
mogdb      8384                             omm  419u      REG              253,0   81543168    2841034 /MogDB/data/base/18596/49829.1
...

# session 3

anbob=# drop table test100;
DROP TABLE

# session 2

$ ls -l 49829*
-rw------- 1 omm omm 1073741824 Mar 17 17:30 49829

$ lsof |grep 49829
mogdb      8384                             omm  417u      REG              253,0 1073741824    2844512 /MogDB/data/base/18596/49829
mogdb      8384                             omm  418u      REG              253,0     303104    2844524 /MogDB/data/base/18596/49829_fsm (deleted)
mogdb      8384                             omm  419u      REG              253,0   81543168    2841034 /MogDB/data/base/18596/49829.1 (deleted)

# session 3
anbob=# checkpoint;
CHECKPOINT

# session 2
$ ls -l 49829*
ls: cannot access 49829*: No such file or directory

[omm@mogdb1 18596]$ lsof |grep 49829
mogdb      8384                             omm  417u      REG              253,0 1073741824    2844512 /MogDB/data/base/18596/49829 (deleted)
mogdb      8384                             omm  418u      REG              253,0     303104    2844524 /MogDB/data/base/18596/49829_fsm (deleted)
mogdb      8384                             omm  419u      REG              253,0   81543168    2841034 /MogDB/data/base/18596/49829.1 (deleted)

# session 1
 -- query another 
anbob =# select now();
              now
-------------------------------
 2024-03-17 16:33:16.098603+08
(1 row)

# session 2
$ lsof |grep 49829
-- none
[omm@mogdb1 18596]$

Note:
在OPENGAUSS中的表现更糟糕，checkpoint后都没有释放给OS。直到session 1做了一个新的查询。

Summary: