Vacuum 不会缩小我的PostgreSQL 表

PostgreSQL考试认证中心 2023-03-22

238

你有没有想过为什么vacuum不会让你的PostgreSQL表变小？你有没有想过为什么vacuum不收缩数据文件？好吧，也许这就是你一直在寻找的文章。重点是：通常情况下，空间不会在清理后返回到操作系统，重要的是要了解原因。人们经常对vacuum的内部运作做出错误的假设。深入挖掘并了解真正发生的事情是有意义的。

下面的帖子向用户揭示了关于vacuum的最有用的秘密。

了解元组可见性

要了解PostgreSQL中的vacuum，首先了解PostgreSQL如何处理可见性非常重要。整个概念基于一组隐藏列，这些列是行的一部分。以下是它的工作原理:

test=# CREATE TABLE t_test (id int);
CREATE TABLE
test=# INSERT INTO t_test VALUES (5), (6), (7);
INSERT 0 3
test=# INSERT INTO t_test VALUES (8), (9), (10);
INSERT 0 3

我们刚刚创建的表只包含一列--以保持简单。请注意，数据已使用两个单独的事务加载到表中。每个事务都插入了3行，这在隐藏的列中清楚地表明:

test=# SELECT ctid, xmin, xmax, cmin, cmax, * FROM t_test;
ctid  | xmin | xmax | cmin | cmax | id
------+------+------+------+------+----
(0,1) |  764 |    0 |    0 |    0 | 5
(0,2) |  764 |    0 |    0 |    0 | 6
(0,3) |  764 |    0 |    0 |    0 | 7
(0,4) |  765 |    0 |    0 |    0 | 8
(0,5) |  765 |    0 |    0 |    0 | 9
(0,6) |  765 |    0 |    0 |    0 | 10
(6 rows)

xmin、xmax、cmin和cmax是包含事务Id的隐藏列。如您所见，前三行已由事务号764写入，而其余数据已使用事务号765创建。

隐藏列将处理可见性，PostgreSQL将（通常但不总是）根据这些列确定某一行是否可以被某个事务看到。

运行UPDATE语句将显示这里到底发生了什么:

test=# BEGIN;
BEGIN
test=*# UPDATE t_test SET id = id * 10 WHERE id > 9 RETURNING *;
id
-----
 100
(1 row)
 
UPDATE 1
test=*# SELECT ctid, xmin, xmax, cmin, cmax, * FROM t_test;
ctid  | xmin | xmax | cmin | cmax | id
------+------+------+------+------+-----
(0,1) |  764 |    0 |    0 |    0 | 5
(0,2) |  764 |    0 |    0 |    0 | 6
(0,3) |  764 |    0 |    0 |    0 | 7
(0,4) |  765 |    0 |    0 |    0 | 8
(0,5) |  765 |    0 |    0 |    0 | 9
(0,7) |  766 |    0 |    0 |    0 | 100
(6 rows)

一行已更改。但是，让我们专注于表示磁盘上行的物理位置的CTID。请注意，(0,6)已经消失，因为PostgreSQL必须复制该行。如果我们运行第二个更新，该行将再次复制: UPDATE

test=*# UPDATE t_test SET id = id * 10 WHERE id > 9 RETURNING *;
id
------
 1000
(1 row)
 
UPDATE 1
test=*# UPDATE t_test SET id = id * 10 WHERE id > 9 RETURNING *;
id
-------
 10000
(1 row)
 
UPDATE 1

复制这些行很重要，因为我们必须保留旧行。否则回滚将不起作用-因此旧版本必须保留。

让我们再检查一下该表:

test=*# SELECT ctid, xmin, xmax, cmin, cmax, * FROM t_test;
ctid  | xmin | xmax | cmin | cmax | id
------+------+------+------+------+-------
(0,1) |  764 |    0 |    0 |    0 | 5
(0,2) |  764 |    0 |    0 |    0 | 6
(0,3) |  764 |    0 |    0 |    0 | 7
(0,4) |  765 |    0 |    0 |    0 | 8
(0,5) |  765 |    0 |    0 |    0 | 9
(0,9) |  766 |    0 |    2 |    2 | 10000
(6 rows)
 
test=*# COMMIT;
COMMIT

5到9之间的所有内容都充满了必须删除的死行。

VACUUM:清理行

请记住，提交也不能杀死死行。因此，清理过程必须异步完成。这正是vacuum的作用。让我们运行它，看看会发生什么:

test=# VACUUM VERBOSE t_test
INFO: vacuuming "test.public.t_test"
INFO: finished vacuuming "test.public.t_test": index scans: 0
pages: 0 removed, 1 remain, 1 scanned (100.00% of total)
tuples: 3 removed, 6 remain, 0 are dead but not yet removable
removable cutoff: 767, which was 0 XIDs old when operation ended
new relfrozenxid: 764, which is 1 XIDs ahead of previous value
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 22.790 MB/s, avg write rate: 27.348 MB/s
buffer usage: 6 hits, 5 misses, 6 dirtied
WAL usage: 3 records, 3 full page images, 14224 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM

vacuum正在积极寻找不再被任何人看到的行。这些行可以位于数据文件的中间某处。发生的事情是VACUUM允许PostgreSQL重用该空间-但是，它不会将该空间返回到操作系统。它不能这样做，因为如果你有一个大小为1GB的数据文件，你不能简单地将"文件的中间"返回给操作系统，以防它为空–没有文件系统操作支持这一点。相反，PostgreSQL必须记住这个可用空间，并在以后重用它。

VACUUM规则的例外

但是，该规则有一个例外。考虑以下代码片段:

test=# SELECT pg_relation_size('t_test');
 pg_relation_size
------------------
 8192
(1 row)
 
test=# DELETE FROM t_test;
DELETE 6
test=# SELECT pg_relation_size('t_test');
 pg_relation_size
------------------
 8192
(1 row)

即使在DELETE语句之后，表也会保留其大小。请记住：清理是异步完成的。因此，可以调用VACUUM来删除这些行: DELETEVACUUM

test=# VACUUM t_test;
VACUUM

这是一个特例。规则是：如果从表中的某个位置开始，所有行都死了，VACUUM可以截断表。这正是这个例子中发生的事情:

test=# SELECT pg_relation_size('t_test');
 pg_relation_size
------------------
 0
(1 row)

毕竟，在大表中，数据文件末尾总是有少数行（在正常情况下）。出于这个原因，不要指望VACUUM缩小表。

VACUUM FULL vs. pg_squeeze

对抗表膨胀（这是用于描述表不成比例增长的技术术语）的一种方法是使用vacuum full。但是，问题是VACUUM FULL需要一个表锁，如果您处于关键业务环境中，这可能是一个真正的问题。因此，我们开发了一种在没有广泛锁定的情况下重组大型表的方法。
再更吧。

PG考试咨询

PGCCC，公众号：PostgreSQL考试认证中心PostgreSQL认证考试，2023春节特惠，开始啦！

PGCCC，公众号：PostgreSQL考试认证中心永远都不晚：PostgreSQL认证专家（培训考试-广州站）

PostgreSQL-PCP认证专家-上海站、广州站
PGCCC，公众号：PostgreSQL考试认证中心开班通知-PCP认证专家(上海站)培训开班1106

PostgreSQL-PCP认证专家-北京站-精彩花絮
PGCCC，公众号：PostgreSQL考试认证中心PostgreSQL认证专家考试（培训）（10月16日北京站）精彩花絮

PostgreSQL-PCP认证专家-成都站
公众号：PostgreSQL考试认证中心开班通知-PCP认证专家(成都站)培训开班1016

PostgreSQL-PCP认证专家考试-北京站-考试风采
PGCCC，公众号：PostgreSQL考试认证中心PostgreSQL认证专家考试(培训)-北京站-成功举办

PostgreSQL-PCA认证考试-贵阳站-考试风采
PGCCC，公众号：PostgreSQL考试认证中心PostgreSQL PCA+PCP认证考试在贵阳成功举办

PostgreSQL-PCP认证专家考试-上海站-考试风采
PGCCC，公众号：PostgreSQL考试认证中心PostgreSQL PCP认证考试(上海站)成功举办

PostgreSQL认证专家考试-学员考试总结
薛晓刚，公众号：PostgreSQL考试认证中心难考的PostgreSQL认证考试

PostgreSQL-PCM认证大师考试-天津站-考试风采
PGCCC，公众号：PostgreSQL考试认证中心PostgreSQL-PCM认证大师考试（天津站）成功举办

如何在工业和信息化部教育与考试中心官网查询证书
PG考试认证中心，公众号：PostgreSQL考试认证中心如何在工业和信息化部教育与考试中心查询PostgreSQL证书

中国PostgreSQL考试认证体系
PG考试认证中心，公众号：PostgreSQL考试认证中心中国PostgreSQL考试认证体系

postgresql test

文章转载自PostgreSQL考试认证中心，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Vacuum 不会缩小我的PostgreSQL 表

了解元组可见性

VACUUM:清理行

VACUUM规则的例外

VACUUM FULL vs. pg_squeeze

评论