PostgreSQL中vacuum物理文件truncate发生的条件

与我联系： 微信公众号：数据库杂记   个人微信: iiihero 我是iihero. 也可以叫我Sean. iihero@CSDN( blog.csdn.net/iihero)  Sean@墨天轮 ( .modb.pro/u/16258) 数据库领域的资深爱好者一枚。 水木早期数据库论坛发起人 db2@smth就是俺，早期多年水木论坛数据库版版主。 国内最早一批DB2 DBA。前后对Sybase ASE及SQLAnywhere, PostgreSQL,  HANA, Oracle, DB2, SQLite均有涉猎。曾长期担任CSDN相关数据库版版主。 SAP数据库技术专家与开发架构师，PostgreSQL ACE. 代表作有：<<Java2网络协议内幕>> <<Oracle Spatial及OCI高级编程>>  <<Sybase ASE 15.X全程实践>> 兴趣领域：数据库技术及云计算、GenAI 业余专长爱好：中国武术六段 陈式太极拳第13代传人（北京陈式太极拳第5代传人） 职业太极拳教练，兼任北京陈式太极拳研究会副秘书长。 如果想通过习练陈式太极拳强身健体，也可以与我联系。 前言

前段时间，有些同学说到vacuum截断的行为时，认为，只要末尾是空页，无论多少，都会被截断，真是这样的吗？

PostgreSQL当中，由于vacuum的操作并不总能将死元组的空间进行”物理截断”，虽然说是回收了（表示空间可重用），但是真正的物理文件的大小依然不会收缩。那么什么时候这个空间会被真正截断呢？

我们不妨看看源代码：

摘自： src/backend/access/heap/vacuumlazy.c /*  * Space/time tradeoff parameters: do these need to be user-tunable?  *  * To consider truncating the relation, we want there to be at least  * REL_TRUNCATE_MINIMUM or (relsize / REL_TRUNCATE_FRACTION) (whichever  * is less) potentially-freeable pages.  */ #define REL_TRUNCATE_MINIMUM    1000 #define REL_TRUNCATE_FRACTION    16 /*  * should_attempt_truncation - should we attempt to truncate the heap?  *  * Don't even think about it unless we have a shot at releasing a goodly  * number of pages.  Otherwise, the time taken isn't worth it, mainly because  * an AccessExclusive lock must be replayed on any hot standby, where it can  * be particularly disruptive.  *  * Also don't attempt it if wraparound failsafe is in effect.  The entire  * system might be refusing to allocate new XIDs at this point.  The system  * definitely won't return to normal unless and until VACUUM actually advances  * the oldest relfrozenxid -- which hasn't happened for target rel just yet.  * If lazy_truncate_heap attempted to acquire an AccessExclusiveLock to  * truncate the table under these circumstances, an XID exhaustion error might  * make it impossible for VACUUM to fix the underlying XID exhaustion problem.  * There is very little chance of truncation working out when the failsafe is  * in effect in any case.  lazy_scan_prune makes the optimistic assumption  * that any LP_DEAD items it encounters will always be LP_UNUSED by the time  * we're called.  */ static bool should_attempt_truncation(LVRelState *vacrel) {     BlockNumber possibly_freeable;     if (!vacrel->do_rel_truncate || VacuumFailsafeActive)         return false;     possibly_freeable = vacrel->rel_pages - vacrel->nonempty_pages;     if (possibly_freeable > 0 &&         (possibly_freeable >= REL_TRUNCATE_MINIMUM ||          possibly_freeable >= vacrel->rel_pages / REL_TRUNCATE_FRACTION))         return true;     return false; }

从代码里头可以看出，只有不少于1000个空页或者空页比例不低于总页数/16的情况下，才会真正发生truncate。两者必须满足其中之一。

这方面的原理分析，在cc老师的文章里都有谈及 （ mp.weixin.qq /s/ymFYOAGin2kqo96gfNYDnQ），为加深印象，我们可以通过实验来进一步验证。

实验验证 -- 安装几个内置的插件： create extension pg_buffercache; create extension pg_freespacemap; create extension pageinspect; create extension pg_visibility; create extension pgstattuple 准备表及数据 CREATE OR REPLACE FUNCTION random_string( int ) RETURNS TEXT as $$ SELECT string_agg(substring('abcdefghijklmnopqrstuvwxyz', round(random() * 25 + 0.5)::integer, 1), '') FROM generate_series(1, $1);  $$ language sql; postgres=# create table t(id int primary key, col2 varchar(4000)); CREATE TABLE postgres=# insert into t select n, random_string(2000) from generate_series(1, 4000) as n; INSERT 0 4000

相当于是建一个表t, 往里边插入4000条数据。里边使用随机串，主要是为了避免字符串压缩。我们看看相关表大小。

select pg_total_relation_size('t') total, pg_table_size('t') table, pg_indexes_size('t') indexes, pg_table_size('t')+pg_indexes_size('t') as sum, pg_relation_size('t') relation, pg_table_size('t')-pg_relation_size('t') as toast;   total  |  table  | indexes |   sum   | relation | toast ---------+---------+---------+---------+----------+-------  8331264 | 8224768 |  106496 | 8331264 |  8192000 | 32768 (1 row)

我们看到，上边的空间大小主要集中在表本身。因为实际插入长度2000左右，还不会进入到Toast空间。

postgres=# select min(blkno), max(blkno) from pg_freespace('t');  min | max -----+-----    0 | 999 (1 row) postgres=# select pg_relpages('t');  pg_relpages -------------         1000 (1 row) postgres=# select * from pgstattuple('t') \gx -[ RECORD 1 ]------+-------- table_len          | 8192000 tuple_count        | 4000 tuple_len          | 8128000 tuple_percent      | 99.22 dead_tuple_count   | 0 dead_tuple_len     | 0 dead_tuple_percent | 0 free_space         | 20000 free_percent       | 0.24

可以看出，这个表实际占用了1000个页面（每4条记录占用一页，符合预期）。

删除末尾的4条记录

我们就只删除末尾的4条记录，看下情况，相当于是最后一页。

postgres=# delete from t where id >= 3997; DELETE 4 postgres=# select * from pgstattuple('t') \gx -[ RECORD 1 ]------+-------- table_len          | 8192000 tuple_count        | 3996 tuple_len          | 8119872 tuple_percent      | 99.12 dead_tuple_count   | 0 dead_tuple_len     | 0 dead_tuple_percent | 0 free_space         | 28128 free_percent       | 0.34 postgres=# vacuum verbose t; INFO:  vacuuming "public.t" INFO:  table "t": index scan bypassed: 1 pages from table (0.10% of total) have 4 dead item identifiers INFO:  table "t": found 0 removable, 0 nonremovable row versions in 1 out of 1000 pages DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 223185 Skipped 0 pages due to buffer pins, 0 frozen pages. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO:  vacuuming "pg_toast.pg_toast_29708" INFO:  table "pg_toast_29708": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 223186 Skipped 0 pages due to buffer pins, 0 frozen pages. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. VACUUM postgres=# select * from pgstattuple('t') \gx -[ RECORD 1 ]------+-------- table_len          | 8192000 tuple_count        | 3996 tuple_len          | 8119872 tuple_percent      | 99.12 dead_tuple_count   | 0 dead_tuple_len     | 0 dead_tuple_percent | 0 free_space         | 28128 free_percent       | 0.34

上边的信息也可以看到，并没有什么截断发生。统计一下物理空间：

select pg_total_relation_size('t') total, pg_table_size('t') table, pg_indexes_size('t') indexes, pg_table_size('t')+pg_indexes_size('t') as sum, pg_relation_size('t') relation, pg_table_size('t')-pg_relation_size('t') as toast;   total  |  table  | indexes |   sum   | relation | toast ---------+---------+---------+---------+----------+-------  8339456 | 8232960 |  106496 | 8339456 |  8192000 | 40960 (1 row)

表的物理大小，仍然为：8192000

postgres=# select pg_relation_filepath('t');  pg_relation_filepath ----------------------  base/13236/29708  \! stat postgres/data/base/13236/29708 16777234 104752459 -rw------- 1 ***** ***** 0 8192000 "Feb  4 06:40:12 2024" "Feb  4 06:44:03 2024" "Feb  4 06:44:03 2024" "Feb  4 06:40:12 2024" 4096 16512 0 postgres/data/base/13236/29708 继续删除12条记录： postgres=# delete from t where id >= 4000 - 16 + 1; DELETE 12 INFO:  vacuuming "public.t" INFO:  table "t": index scan bypassed: 4 pages from table (0.40% of total) have 16 dead item identifiers INFO:  table "t": found 16 removable, 3984 nonremovable row versions in 1000 out of 1000 pages DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 223205 Skipped 0 pages due to buffer pins, 0 frozen pages. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO:  vacuuming "pg_toast.pg_toast_29729" INFO:  table "pg_toast_29729": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 223205 Skipped 0 pages due to buffer pins, 0 frozen pages. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. VACUUM postgres=# select pg_total_relation_size('t') total, pg_table_size('t') table, pg_indexes_size('t') indexes, pg_table_size('t')+pg_indexes_size('t') as sum, pg_relation_size('t') relation, pg_table_size('t')-pg_relation_size('t') as toast;   total  |  table  | indexes |   sum   | relation | toast ---------+---------+---------+---------+----------+-------  8339456 | 8232960 |  106496 | 8339456 |  8192000 | 40960 (1 row) 累计16个页面: postgres=# delete from t where id >= 4000 - 64 + 1; DELETE 32 postgres=# vacuum verbose t; INFO:  vacuuming "public.t" INFO:  table "t": index scan bypassed: 16 pages from table (1.60% of total) have 64 dead item identifiers INFO:  table "t": found 32 removable, 0 nonremovable row versions in 16 out of 1000 pages DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 223209 Skipped 0 pages due to buffer pins, 0 frozen pages. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO:  vacuuming "pg_toast.pg_toast_29729" INFO:  table "pg_toast_29729": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 223210 Skipped 0 pages due to buffer pins, 0 frozen pages. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. VACUUM

还是一点动静都没有。

那么要多少个空页，才有效：1000/16 = 62页？　

我们不妨逐步推进这个实验，累计16一直往上，看到底多少次以后，开始有截断？

。。。。。。

发现，删除80条记录（20个页面），结果不变

删除84条记录时，结果仍不变。

删除88条记录的时候（22个页面），这个时候会截断20个页面。（INFO:  table "t": truncated 1000 to 980 pages）

postgres=# delete from t where id >= 4000 - 88 + 1; DELETE 8 postgres=# vacuum verbose t; INFO:  vacuuming "public.t" INFO:  table "t": index scan bypassed: 2 pages from table (0.20% of total) have 8 dead item identifiers INFO:  table "t": found 8 removable, 0 nonremovable row versions in 22 out of 1000 pages DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 223218 Skipped 0 pages due to buffer pins, 0 frozen pages. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO:  table "t": truncated 1000 to 980 pages DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s INFO:  vacuuming "pg_toast.pg_toast_29736" INFO:  table "pg_toast_29736": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 223219 Skipped 0 pages due to buffer pins, 0 frozen pages. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. VACUUM postgres=# select pg_relpages('t');  pg_relpages -------------          980 (1 row)

事实上，我们发现，在删除87条记录的时候，仍然不会发生截断：

postgres=# delete from t where id >= 4000 - 87+1; vacuum verbose t; DELETE 1 INFO:  vacuuming "public.t" INFO:  table "t": index scan bypassed: 1 pages from table (0.10% of total) have 3 dead item identifiers INFO:  table "t": found 1 removable, 1 nonremovable row versions in 22 out of 1000 pages DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 223228 Skipped 0 pages due to buffer pins, 0 frozen pages. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO:  vacuuming "pg_toast.pg_toast_29743" INFO:  table "pg_toast_29743": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 223228 Skipped 0 pages due to buffer pins, 0 frozen pages. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. VACUUM autovacuum禁掉，去除影响

我们禁掉autovacuum, 并进行连续操作

drop table t;create table t(id int, col2 varchar(4000)); alter table t SET (autovacuum_enabled = off); insert into t select n, random_string(2000) from generate_series(1, 4000) as n; postgres=# insert into t select n, random_string(2000) from generate_series(1, 4000) as n; INSERT 0 4000 postgres=# delete from t where id >= 4000 - 88+1; vacuum verbose t; DELETE 88 INFO:  vacuuming "public.t" INFO:  table "t": removed 88 dead item identifiers in 22 pages INFO:  table "t": found 88 removable, 3912 nonremovable row versions in 1000 out of 1000 pages DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 223240 Skipped 0 pages due to buffer pins, 0 frozen pages. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO:  vacuuming "pg_toast.pg_toast_29756" INFO:  table "pg_toast_29756": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 223240 Skipped 0 pages due to buffer pins, 0 frozen pages. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. VACUUM postgres=# select pg_relpages('t');  pg_relpages -------------         1000 (1 row)

如果我们快速进行处理：

postgres=# drop table t;create table t(id int, col2 varchar(4000)); alter table t SET (autovacuum_enabled = off); insert into t select n, random_string(2000) from generate_series(1, 4000) as n; DROP TABLE CREATE TABLE ALTER TABLE INSERT 0 4000 postgres=# delete from t where id >= 4000 - 247+1; vacuum verbose t; select pg_relpages('t'); DELETE 247 INFO:  vacuuming "public.t" INFO:  table "t": removed 247 dead item identifiers in 62 pages INFO:  table "t": found 247 removable, 3753 nonremovable row versions in 1000 out of 1000 pages DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 223267 Skipped 0 pages due to buffer pins, 0 frozen pages. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO:  vacuuming "pg_toast.pg_toast_29776" INFO:  table "pg_toast_29776": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 223267 Skipped 0 pages due to buffer pins, 0 frozen pages. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. VACUUM  pg_relpages -------------         1000 (1 row) -- 再执行一次 postgres=# delete from t where id >= 4000 - 247+1; vacuum verbose t; select pg_relpages('t'); DELETE 0 INFO:  vacuuming "public.t" INFO:  table "t": found 0 removable, 0 nonremovable row versions in 1 out of 1000 pages DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 223267 Skipped 0 pages due to buffer pins, 60 frozen pages. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO:  table "t": truncated 1000 to 939 pages DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s INFO:  vacuuming "pg_toast.pg_toast_29776" INFO:  table "pg_toast_29776": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 223268 Skipped 0 pages due to buffer pins, 0 frozen pages. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. VACUUM  pg_relpages -------------          939 (1 row)

这里会因为:  Skipped 0 pages due to buffer pins, 60 frozen pages.,  最后还是触发truncate. 但是从实验结果来看，在没有到达62个页面之前，第一次插，确实没有被trunate.

删除62个页面对应的记录： postgres=# drop table t;create table t(id int primary key, col2 varchar(4000)); alter table t SET (autovacuum_enabled = off); insert into t select n, random_string(2000) from generate_series(1, 4000) as n; DROP TABLE CREATE TABLE ALTER TABLE INSERT 0 4000 postgres=# delete from t where id >= 4000 - 248+1; vacuum verbose t; select pg_relpages('t'); DELETE 248 INFO:  vacuuming "public.t" INFO:  scanned index "t_pkey" to remove 248 row versions DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s INFO:  table "t": removed 248 dead item identifiers in 62 pages DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s INFO:  index "t_pkey" now contains 3752 row versions in 13 pages DETAIL:  248 index row versions were removed. 0 index pages were newly deleted. 0 index pages are currently deleted, of which 0 are currently reusable. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO:  table "t": found 248 removable, 3752 nonremovable row versions in 1000 out of 1000 pages DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 223284 Skipped 0 pages due to buffer pins, 0 frozen pages. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO:  table "t": truncated 1000 to 938 pages DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s INFO:  vacuuming "pg_toast.pg_toast_29795" INFO:  table "pg_toast_29795": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 223285 Skipped 0 pages due to buffer pins, 0 frozen pages. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. VACUUM  pg_relpages -------------          938 postgres=# select pg_total_relation_size('t') total, pg_table_size('t') table, pg_indexes_size('t') indexes, pg_table_size('t')+pg_indexes_size('t') as sum, pg_relation_size('t') relation, pg_table_size('t')-pg_relation_size('t') as toast;   total  |  table  | indexes |   sum   | relation | toast ---------+---------+---------+---------+----------+-------  7831552 | 7725056 |  106496 | 7831552 |  7684096 | 40960 (1 row)          小结

vacuum一个表，产生物理截断，默认情况下，需要空页达到一定条件。上边的实验表明，在空页没达到1000个页同时也没达到总页数/16的情况下，第一次尝试截断，并不会真正发生。（autovacuum关闭，是为了屏蔽自动vacuum的影响）因为autovacuum还会触发freeze等其它动作，会间接产生影响。

上边的实验，禁掉auto vacuum, 在删除最后62个页面的情况下，会发生截断。

如果不禁掉auto vacuum， 你会发现在20个页面被删除的情况下，会发生截断，那刚好是autovacuum默认触发的条件（20%）。而它本身又会触发与freeze action相关的操作，最终会引发截断。

一个小实验，结合代码，可以引发很多思考。希望这个实验对vacuum原理感兴趣的人有所帮助。

与我联系：