京公网安备11010502063487号“PostgreSQL 表为什么越来越大、VACUUM 之后文件怎么不变小、VACUUM FULL 能不能随便跑” 是最常见的运维疑问。这篇在本机自己起一个 PostgreSQL 16.15,关掉 autovacuum 手动清理,用 pg_relation_size 和 pgstattuple 看每一步的大小和死行。
SQL> create table t(id int primary key, v int, pad text)
CREATE TABLE
SQL> insert into t select g, 0, repeat('x', 100) from generate_series(1, 200000) g
INSERT 0 200000
初始:28254208 字节(26.9 MB),活行 200000,死行 0,死行占 0.0%,空闲空间占 0.6%
SQL> update t set v = v + 1
UPDATE 200000
全表 UPDATE 一次之后:56500224 字节(53.9 MB),活行 200000,死行 200000,死行占 47.1%,空闲空间占 0.5%旧版本不删,新版本追加;页满了就追加新页,全表 UPDATE 表大小翻倍。
SQL> vacuum t
VACUUM
VACUUM 耗时 0.09 s
VACUUM 之后:56500224 字节(53.9 MB),活行 200000,死行 0,死行占 0.0%,空闲空间占 50.1%
SQL> update t set v = v + 1
UPDATE 200000
再 UPDATE 一次之后:56500224 字节(53.9 MB),活行 200000,死行 200000,死行占 47.1%,空闲空间占 0.5%死行变成可重用空间,文件不缩小;后续更新写进这些空位,表停在峰值大小。
SQL> vacuum t
VACUUM
VACUUM FULL 耗时 0.14 s;期间的 SELECT:canceling statement due to statement timeout(等了 0.06 s)
VACUUM FULL 之后:28254208 字节(26.9 MB),活行 200000,死行 0,死行占 0.0%,空闲空间占 0.6%
文件号(relfilenode)也换了: 变了重写成新文件,真的变小;持有 ACCESS EXCLUSIVE 锁,SELECT 也被挡住。
SQL> drop table t
DROP TABLE
SQL> create table t(id int primary key, v int, pad text)
CREATE TABLE
SQL> insert into t select g, 0, repeat('x', 100) from generate_series(1, 200000) g
INSERT 0 200000
SQL> delete from t where id <= 100000
DELETE 100000
SQL> vacuum t
VACUUM
初始 26.9 MB;删掉前一半(id <= 100000)并 VACUUM 之后 26.9 MB
SQL> delete from t where id > 150000
DELETE 50000
SQL> vacuum t
VACUUM
再删掉 id > 150000 的行并 VACUUM 之后 20.2 MB
现在 活行 50000,死行 0,死行占 0.0%,空闲空间占 66.6%只有表尾部连续的空页;删前面的行不会让文件变小。
SQL> drop table t
DROP TABLE
SQL> create table t(id int primary key, v int)
CREATE TABLE
SQL> insert into t select g, 0 from generate_series(1, 100000) g
INSERT 0 100000
SQL> update t set v = 1
UPDATE 100000
老事务还开着时 VACUUM (VERBOSE) 的输出里与死行有关的行:
tuples: 0 removed, 200000 remain, 100000 are dead but not yet removable
removable cutoff: 745, which was 1 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
此时 活行 100000,死行 100000,死行占 44.1%,空闲空间占 0.4%
老事务结束后再 VACUUM (VERBOSE):
tuples: 100000 removed, 100000 remain, 0 are dead but not yet removable
removable cutoff: 746, which was 0 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 443 pages from table (50.06% of total) had 100000 dead item identifiers removed
此时 活行 100000,死行 0,死行占 0.0%,空闲空间占 50.0%一个没结束的 repeatable read 事务会让 VACUUM 报 dead but not yet removable,回收不掉。