postgresql 在大型数据集上删除 Postgres 中的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15699989/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dropping column in Postgres on a large dataset
提问by nikita2206
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?
所以我有一个包含大型数据集的表，并且该表有三列我想删除。
问题是：Postgres 将如何处理它？
Will it walk through every entry or will it just update mapping info without much overhead?
Can I just make an ALTER TABLEor should I use swap-table in this particular case?
它会遍历每个条目还是只是更新映射信息而没有太多开销？ALTER TABLE在这种特殊情况下，我可以制作或应该使用交换表吗？
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 ...
如果已经有人问过，我很抱歉，但谷歌找不到任何相关的问题/文章......
回答by Erwin Brandstetter
Google may be useless for this question, but the manual rarely fails:
谷歌可能对这个问题没用，但手册很少失败：
The
DROP COLUMNform does not physically remove the column, but simply makes it invisible to SQL operations. Subsequent insert and update operations in the table will store a null value for the column. Thus, dropping a column is quick but it will not immediately reduce the on-disk size of your table, as the space occupied by the dropped column is not reclaimed. The space will be reclaimed over time as existing rows are updated.
该
DROP COLUMN表单不会物理删除该列，而只是使其对 SQL 操作不可见。表中的后续插入和更新操作将为该列存储一个空值。因此，删除列很快，但不会立即减少表的磁盘大小，因为删除列占用的空间不会被回收。随着现有行的更新，随着时间的推移，空间将被回收。
And:
和：
To force an immediate rewrite of the table, you can use VACUUM FULL, CLUSTER or one of the forms of ALTER TABLE that forces a rewrite. This results in no semantically-visible change in the table, but gets rid of no-longer-useful data.
要强制立即重写表，您可以使用 VACUUM FULL、CLUSTER 或强制重写的 ALTER TABLE 形式之一。这会导致表中没有语义上可见的更改，但会删除不再有用的数据。
Specifically, the column attisdroppedin the system catalog table pg_attributeis set to TRUE.
具体来说，attisdropped系统目录表中的列pg_attribute设置为TRUE。
回答by Pavel Stehule
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.
ALTER TABLE DROP COLUMN 只禁用系统表中的列。它非常快，但它不会从堆文件中删除数据。您必须稍后执行 VACUUM FULL 以压缩分配的文件空间。所以 ALTER TABLE DROP COLUMN 非常快。并且要压缩文件，您必须调用较慢的（带有独占 LOCK）的 VACUUM FULL。
