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

问题描述

所以我有一个带有大数据集的表，该表有三列要删除。

问题是：Postgres将如何处理它？<​​/ p>

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

并且，如果有什么区别，那么三列的长度都是固定的（两个整数和一个数字）。

很抱歉，是否已经有人问过，但是Google找不到任何相关问题/文章...

解决方案

ALTER TABLE DROP COLUMN仅禁用系统表中的列。速度非常快，但不会从堆文件中删除数据。您稍后必须进行VACUUM FULL压缩分配的文件空间。因此，ALTER TABLE DROP COLUMN非常快。而要压缩文件，您必须调用速度较慢（带有独占锁定）的VACUUM FULL。

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?

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 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屋！