PostgreSQL中drop table后磁盘空间未及时释放问题

mahan

问题描述
之前在使用pg12.3版本的时候遇到一个问题，会话1查询了一个大于1G的表之后未做其他操作且会话一直保持，会话2执行drop table后，磁盘空间一直未释放，直到会话1再执行其他操作或者结束会话，磁盘才会释放。

今天在pg15.0版本再次验证该问题不存在，正好本地还有一个12.8版本的环境，又在pg12.8版本验证问题也不存在。

在官网发布说明中搜索了一下，在12.6的发布说明中有该问题的相关描述

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.

百度翻译一下：
确保为丢弃的关系分配的磁盘空间在提交时立即释放（Thomas Munro）
以前，如果丢弃的关系跨越多个1GB段，则只有第一个段会立即被截断。其他段只是简单地取消了链接，这不会授权内核释放存储，只要任何其他后端仍然打开文件。
问题复现
pg12.3
####会话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=# \q
[postgres@mydb1b ~]$ psql
psql (12.3)
Type "help" for help.
postgres=# select now();
2024-03-11 15:15:55.982282+08
postgres=# select pg_backend_pid();
         550580
postgres=# select * from test1 where id1=1;
id1 | id2 | id3 |  v1   |  v2
-----+-----+-----+-------+-------
   1 |   1 |   1 | hello | hello
(1 row)
postgres=# select now();
2024-03-11 15:17:24.424136+08
####会话2####
postgres=# select now();
2024-03-11 15:16:44.614525+08
postgres=# drop table test1;
DROP TABLE
postgres=# select now();
2024-03-11 15:17:02.950304+08
postgres=# checkpoint ;
CHECKPOINT
####监控窗口####
[root@mydb1b ~]# date
2024年 03月 11日 星期一 15:16:54 CST
[root@mydb1b ~]# lsof -p 550580|grep delete
postgres 550580 postgres 28u REG 253,0  74186752  672021 /app/pg12/data/base/13672/97283.1 (deleted)
[root@mydb1b ~]# date
2024年 03月 11日 星期一 15:17:08 CST
[root@mydb1b ~]# 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)
[root@mydb1b ~]# date
2024年 03月 11日 星期一 15:17:28 CST
[root@mydb1b ~]# lsof -p 550580|grep delete
[root@mydb1b ~]#
1、2024-03-11 15:15:55会话1执行select

2、2024-03-11 15:16:44会话2执行drop，监控窗口15:16:54看到

​ /app/pg12/data/base/13672/97283.1 (deleted)，大小74186752

3、2024-03-11 15:17:02会话2执行checkpoint，监控窗口15:17:08看到

​ /app/pg12/data/base/13672/97283 (deleted)，大小0；

​ /app/pg12/data/base/13672/97283.1 (deleted)，大小74186752

4、2024-03-11 15:17:24会话1执行select now()，监控窗口看到文件已释放

pg12.8
####drop table####
postgres=# drop table test1;
DROP TABLE
postgres=# checkpoint ;
CHECKPOINT
postgres=#
####监控窗口####
[root@mydb1a ~]# lsof -p 3651791|grep delete
postgres 3651791 postgres 39u REG 253,0         0  692750 /app/pg12_8/data/base/13672/16457.1 (deleted)
[root@mydb1a ~]# 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)
在12.8版本中可以看到执行drop和checkpoint之后

/app/pg12_8/data/base/13672/16457 (deleted)和/app/pg12_8/data/base/13672/16457.1 (deleted)的大小都是0，磁盘空间已经释放

总结
1、该问题触发条件，会话1打开了table1且后续无其他操作，其他会话drop table

2、pg12.6，pg13.2及之后的版本应该都是不存在该问题的，如果使用的版本更老，需要注意该问题