在PostgreSQL中估计删除列的效果大小

Question

我在PostgreSQL中有一张表格，其中两列以文本形式包含了图片（这不是我的决定）。现在我不再需要这些列了，打算删除它们。但当我想要估计删除这些列的“影响”时，即表格大小会减少多少，我遇到了一个问题。问题在于PostgreSQL显示旧表格和新表格的大小相同，这非常奇怪，因为我删除了两个“重量级”的列。

以下是对比代码：

-- Create two copies of the table
CREATE TABLE oldwords (LIKE "words" INCLUDING INDEXES); 
INSERT INTO oldwords SELECT * FROM "words";

CREATE TABLE newwords (LIKE "words" INCLUDING INDEXES); 
INSERT INTO newwords SELECT * FROM "words";

-- Drop columns containing images
ALTER TABLE "newwords"
   DROP COLUMN image_black,
   DROP COLUMN image_colored;

-- Update stats of the tables
VACUUM ANALYZE "oldwords";
VACUUM ANALYZE "newwords";

-- Compare size
SELECT
    relname as "Table",
    pg_size_pretty(pg_total_relation_size(relid)) As "Size",
    pg_size_pretty(pg_total_relation_size(relid) - 
    pg_relation_size(relid)) as "External Size"
FROM pg_catalog.pg_statio_user_tables 
WHERE relname LIKE '___words'
ORDER BY pg_total_relation_size(relid) DESC

-- RESULT
Table       Size    External Size
newwords    296 MB  205 MB
oldwords    296 MB  205 MB

我做错了什么吗？为什么大小都一样？正确的方法是什么？

- Rustam Guliev

---

Vacuum full应该重写表格并删除所有已删除的列。 - Sami Kuhmonen

@SamiKuhmonen：不，它不会。 - user330315

正如我在下面提到的，VACUUM FULL 对我很有效。 - Rustam Guliev

Answer 1

从 ALTER TABLE 中:

DROP COLUMN表单并不会物理删除该列，而只是使其对SQL操作不可见。表中随后的插入和更新操作将为该列存储一个空值。

因此， 删除列很快，但它不会立即减少表的磁盘大小，因为被删除列占用的空间不会被回收。当现有行被更新时，该空间将随时间而得到回收。（这些语句不适用于删除系统oid列；这是通过立即重写完成的。）

要强制立即回收被删除列占用的空间，您可以执行ALTER TABLE的某个形式，该形式对整个表执行重写。这将导致重构每一行，将已删除列替换为null值。

- Lukasz Szozda

---

关于“随时间”，VACUUM应立即回收存储空间。 - lathspell

@lathspell 我不相信那是真的。被删除的列将会保留在现有列上。VACUUM 只会删除无效的 行。如果一行被更新，我的理解是新版本的该行将被写入，没有包含被删除的列。然后当旧版本的该行被 VACUUM 清理时，空间将被释放。但是仅在删除列之后立即运行 VACUUM 不会改变太多东西。 - Don Seiler

谢谢！这解释了原因。但是我的表是“静态”的，即表几乎不会更改。所以，我理解的是，我应该通过一些“虚假”的ALTER TABLE查询来强制进行空间回收。但在这种情况下，我可以使用什么查询呢？ - Rustam Guliev

根据 https://dba.stackexchange.com/questions/117510/reclaim-disk-space-from-dropped-column-without-downtime，简单的VACUUM不足以释放磁盘空间，需要使用VACUUM FULL。 - lathspell