在大型数据集上删除 Postgres 中的列 [英] Dropping column in Postgres on a large dataset

问题描述

所以我有一个包含大型数据集的表，并且该表包含三列，我想删除它.
问题是:Postgres 将如何处理?

So I have a table with a large dataset and this table has a three columns that I would like to drop.
The question is: how will Postgres deal with it?

它会遍历每个条目还是只是在没有太多开销的情况下更新映射信息?我可以只制作一个 ALTER TABLE 还是应该在这种特殊情况下使用交换表?

Will it walk through every entry or will it just update mapping info without much overhead? Can I just make an ALTER TABLE or should I use swap-table in this particular case?

而且，如果有任何区别，所有三列的长度都是固定的(两个整数和一个数字).

And, if it makes any difference, all three columns have fixed length (two integers and one numeric).

抱歉，如果已经有人问过了，但谷歌找不到任何相关的问题/文章......

I'm sorry if it's been asked already, but Google couldn't find any related questions / articles ...

推荐答案

ALTER TABLE DROP COLUMN 只会禁用系统表中的列.它非常快，但它不会从堆文件中删除数据.您必须稍后执行 VACUUM FULL 以压缩分配的文件空间.所以 ALTER TABLE DROP COLUMN 非常快.并且要压缩文件，您必须调用较慢的(带有独占锁定)VACUUM FULL.

ALTER TABLE DROP COLUMN does just only disabling columns in system tables. It is very fast, but it doesn't remove data from heap files. You have to do VACUUM FULL later to compact allocated file space. So ALTER TABLE DROP COLUMN is very fast. And to compact files, you have to call the slower (with exclusive LOCK) VACUUM FULL.

这篇关于在大型数据集上删除 Postgres 中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！