ALTER TABLE在有限的磁盘空间上添加新列 [英] ALTER TABLE to add new column on limited disk space

问题描述

我有一个关于操作主表的问题:我需要在其中添加一个新列，但是已经有超过5000万个条目要更新.
如果我不将数据存储在已经由数据库填充一半以上的小型ssd上，那将与更改表一样容易.
alter table无法完成，因为它创建了该表的副本，然后才替换原始表.

两个副本不能放在我的ssd驱动器上，因此可以直接在原始表上运行，也可以在临时表上使用另一个驱动器吗?还是我应该使用另一台计算机来更改更大驱动器上的表，然后仅转移回我的ssd服务器?

提前谢谢！

PS:时间并不是真正的问题，它是一个几乎没有用户的非实时Alpha系统，因此我被允许在数小时内关闭所有内容(感谢互联网之神！)

解决方案

如果该列没有 DEFAULT 并且为空，则PostgreSQL可以就地执行 ADD COLUMN ./p>

因此:在高并发情况下，使用与您用于处理它的方法相同的方法，但没有使用并发来处理它的触发器.

• ALTER TABLE ... ADD COLUMN ... ，没有任何 DEFAULT 或 NOT NULL
• ALTER TABLE ... ALTER COLUMN ... DEFAULT ... 添加 DEFAULT (如果有)
批量
• UPDATE 在表中设置值. VACUUM 每批之间的表格.不不要使用 VACUUM FULL .每个批次必须是在 VACUUM 之前提交的新的独立事务.
• 如果需要，在所有行都设置了值之后， ALTER TABLE ... ALTER COLUMN ... NOT NULL

I have a question about manipulating my main table : I need to add a new column into it, but there is already more than 50 million entries to update.
It would be as easy as an alter table if I wasn't storing my data on a small ssd that is already filled a bit more than half by the database.
The alter table was not able to finish, because it creates a copy of the table and only then replaces the original.

Two copies can't fit on my ssd drive, so is it possible to either operate directly on the original table, or to use another drive for the temporary table ? Or should I use another computer to alter my table on a bigger drive, then only transfer back to my ssd server ?

Thanks in advance !

PS: Time is not really an issue, it's a non realtime system in alpha with almost no user, so I am allowed to take down everything for a few hours (thank the Internet gods !)

解决方案

PostgreSQL can do in-place ADD COLUMN if the column has no DEFAULT and is nullble.

So: Use the same method you'd use to handle it in a high-concurrency situation, but without the triggers you'd use to handle it with concurrency.

• ALTER TABLE ... ADD COLUMN ... without any DEFAULT or NOT NULL
• ALTER TABLE ... ALTER COLUMN ... DEFAULT ... to add the DEFAULT if any
• UPDATE the table in batches of rows to set the value. VACUUM the table between each batch. Do not use VACUUM FULL. Each batch must be a new, separate transaction that commits before the VACUUM.
• If desired, ALTER TABLE ... ALTER COLUMN ... NOT NULL after all rows have a value set

这篇关于ALTER TABLE在有限的磁盘空间上添加新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！