在大表中填充新列的最佳方法？

#1 楼

这很大程度上取决于您的设置和要求的详细信息。

请注意，自Postgres 11起，仅添加具有易失性DEFAULT的列仍会触发表重写。不幸的是，这是您的情况。

如果磁盘上有足够的可用空间（至少为pg_size_pretty((pg_total_relation_size(tbl))的110％），并且可以在一段时间内提供共享锁，并在很短的时间内提供独占锁，然后使用uuid创建一个包含CREATE TABLE AS列的新表。为什么？


是什么原因导致大型INSERT减慢速度和磁盘使用量爆炸？

下面的代码使用来自附加uuid-oss模块的功能。


锁定表以防止在SHARE模式下进行并发更改（仍然允许并发读取）。尝试写入表将等待并最终失败。见下文。
复制整个表，同时动态填充新列-可能同时对表进行排序。如果要对行进行重新排序，请确保将work_mem设置得足够高以在RAM中进行排序或尽可能高的价格（仅针对您的会话，而不是全局）。
然后将约束，外键，索引，触发器等添加到新表中。更新表的大部分时，从头开始创建索引比迭代添加行要快得多。手册中的相关建议。
准备好新表后，放下旧表并重命名新表以使其成为即插即用的替代品。只有最后一步才能在剩余的事务中获得旧表的排他锁-现在应该很短。
它还需要根据表类型（视图，使用该表的函数）删除任何对象键入签名，...），然后重新创建它们。
在一个事务中完成所有操作，以避免状态不完整。

BEGIN;
LOCK TABLE tbl IN SHARE MODE;

SET LOCAL work_mem = '???? MB';  -- just for this transaction

CREATE TABLE tbl_new AS 
SELECT uuid_generate_v1() AS tbl_uuid, <list of all columns in order>
FROM   tbl
ORDER  BY ??;  -- optionally order rows favorably while being at it.

ALTER TABLE tbl_new
   ALTER COLUMN tbl_uuid SET NOT NULL
 , ALTER COLUMN tbl_uuid SET DEFAULT uuid_generate_v1()
 , ADD CONSTRAINT tbl_uuid_uni UNIQUE(tbl_uuid);

-- more constraints, indices, triggers?

DROP TABLE tbl;
ALTER TABLE tbl_new RENAME tbl;

-- recreate views etc. if any
COMMIT;

这应该是最快的。任何其他就地更新方法都必须以更昂贵的方式重写整个表。如果磁盘上没有足够的可用空间或者负担不起锁定整个表或为并发写入尝试生成错误，您只会走这条路。

并发写入会发生什么？

其他事务（在其他会话中）在事务获取INSERT锁之后尝试在同一表中访问UPDATE / DELETE / SHARE，将等待该锁释放或超时触发，以先到者为准。它们将以任何一种方式失败，因为要尝试写入的表已从它们下面删除。

新表具有新的表OID，但并发事务已将表名解析为上表的OID。最终释放锁定后，他们会尝试在写入表之前先锁定表，然后发现表已消失。 Postgres将回答：


ERROR: could not open relation with OID 123456


123456是旧表的OID。您需要捕获该异常并在您的应用程序代码中重试查询以避免该异常。

如果您无法承受这种情况，则必须保留原始表。

保持现有表，替代方法1

在添加NOT NULL约束之前，进行适当的更新（可能一次在小段上运行更新）。添加具有NULL值且没有NOT NULL约束的新列很便宜。
自Postgres 9.2起，您还可以使用CHECK创建一个NOT VALID约束：后续插入或更新


这样，您可以在多个单独的事务中更新peuàpeu行。这样可以避免将行锁保持太长时间，并且还可以重用死行。 （如果之间没有足够的时间来插入自动真空，则必须手动运行VACUUM。）最后，添加NOT NULL约束并删除NOT VALID CHECK约束：

ALTER TABLE tbl ADD CONSTRAINT tbl_no_null CHECK (tbl_uuid IS NOT NULL) NOT VALID;

-- update rows in multiple batches in separate transactions
-- possibly run VACUUM between transactions

ALTER TABLE tbl ALTER COLUMN tbl_uuid SET NOT NULL;
ALTER TABLE tbl ALTER DROP CONSTRAINT tbl_no_null;

详细讨论NOT VALID的相关答案：


还原转储时禁用所有约束和表检查

保留现有表，替代2
/>在临时表中准备新状态，然后在临时表中重新填充。一站式交易。在准备新表之前，您仍然需要进行TRUNCATE锁定，以防止丢失并发写入。

关于SO的以下相关答案中的详细信息：


通过ID删除数百万行
添加没有表锁的新列？

#2 楼

我没有``最佳''答案，但有一个``最差''答案，它可能会让您以合理的速度完成工作。

我的表有2MM行，并且更新性能在我尝试添加默认为第一列的辅助时间戳列。

ALTER TABLE mytable ADD new_timestamp TIMESTAMP ;
UPDATE mytable SET new_timestamp = old_timestamp ;
ALTER TABLE mytable ALTER new_timestamp SET NOT NULL ;

挂起40分钟后，我尝试了一小段尝试以了解这可能需要多长时间-预测大约是8个小时。

公认的答案肯定会更好-但此表在我的数据库中使用率很高。 FKEY上有几十张桌子；我想避免在这么多表上切换FOREIGN KEYS。然后有视图。

搜索了一些文档，案例研究和StackOverflow，然后我得到了“ A-Ha！”。时刻。消耗不是核心UPDATE，而是所有INDEX操作。我的表上有12个索引-一些用于唯一的约束，一些用于加快查询计划程序的速度，还有一些用于全文本搜索。

更新的每一行不仅在处理

我的解决方案是删除每个索引和约束，更新表，然后重新添加所有索引/约束。

我的解决方案是删除所有索引和约束。 br />
花费了3分钟编写一个执行以下操作的SQL事务：


BEGIN;
删除索引/内容
更新表
重新添加索引/约束
COMMIT;

该脚本运行了7分钟。

公认的答案肯定是更好，更合适的……而且实际上消除了停机时间。但就我而言，要使用该解决方案将花费更多的“开发人员”工作，并且有30分钟的预定停机时间可以实现。我们的解决方案在10中解决了这个问题。