在PostgreSQL中删除大型数据集中的列

Question

我有一个包含大量数据的表格，其中有三列我想要删除。

问题是：Postgres会怎样处理？

它会遍历每个条目还是只更新映射信息而没有太多开销？

我是否可以使用ALTER TABLE或者在这种情况下应该使用交换表方式？

如果有任何不同，那么所有三列都具有固定长度（两个整数和一个数字）。

非常抱歉如果此问题已经被问过，但谷歌找不到相关的问题/文章...

- nikita2206

Answer 1

ALTER TABLE DROP COLUMN仅仅是在系统表中禁用列。这很快，但它不会从堆文件中删除数据。您需要稍后执行VACUUM FULL以压缩分配的文件空间。因此，ALTER TABLE DROP COLUMN非常快。而要压缩文件，您必须调用速度较慢（使用排他锁）的VACUUM FULL。

- Pavel Stehule

Answer 2

谷歌可能无法回答这个问题，但手册很少会失败：

DROP COLUMN表单不会物理上删除列，而只是使其在SQL操作中不可见。表中的后续插入和更新操作将为该列存储空值。因此，删除列很快，但它不会立即减少表的磁盘大小，因为被删除列占用的空间不会被回收。随着现有行的更新，空间会随时间被回收。

并且：

要强制立即重写表，您可以使用 VACUUM FULL、CLUSTER 或强制重写的 ALTER TABLE 表单之一。这不会对表产生语义上可见的更改，但可以摆脱不再有用的数据。

具体来说，系统目录表pg_attribute中的 attisdropped 列设置为 true。

副作用

有一些小的副作用（正如Chris所指出的）：

• 更新或新插入的行仍然存储不可见的空值，这会强制为每个新行创建一个空值位图，即使在可见列中没有空值。不影响现有的行，因为它们保留了原始（现在不可见）列值。

• 空值位图必须足够大以覆盖所有可见和删除的列。在某些极端情况下，这可能会扩大空值位图。关于有效大小：

  • 在PostgreSQL中，可空列是否占用额外空间？

删除的列计入允许的最大值(你本来就不应该抓取)。

目前(Postgres 13)没有简单的方法完全摆脱僵尸列。上述提到的表重写将看不见的值替换为NULL(这能够释放几乎所有的空间)，但是它并未从系统目录中清除已删除的列，即使使用TRUNCATE也不行。只有创建一个新的表(或使用备份/恢复循环)才能做到。

- Erwin Brandstetter

---

有趣的是，由于行更新现在会在已删除列中“存储”NULL值。而且由于PostgreSQL通过在可选的“null bitmap”中设置位来存储null值，每一行现在都必须具有null bitmap（对于每八个已删除或未删除列，大小为一个字节），即使所有可见行都不为空。 - Chris Cogdon