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

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

> What causes large INSERT to slow down and disk usage to explode?

以下代码使用function from the additional uuid-oss module.

> Lock the table against concurrent changes in SHARE mode(仍然允许并发读取).尝试写入表将等待并最终失败.见下文.
>在动态填充新列时复制整个表 – 可能在排队时有利地排序行.
如果要重新排序行,请确保将work_mem设置为您能够承受的最高值(仅适用于您的会话,而不是全局).
>然后将约束,外键,索引,触发器等添加到新表中.更新表的大部分时,从头开始创建索引比迭代地添加行要快得多.
>当新表准备就绪时,删除旧表并重命名新表以使其成为替代品.只有这最后一步才能获得旧表上的剩余交易的独占锁定 – 现在应该非常短.
它还要求您根据表类型(视图,使用签名中的表类型的函数,…)删除任何对象,然后重新创建它们.
>在一次交易中完成所有操作以避免不完整的状态.

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;

这应该是最快的.任何其他更新方法都必须以更昂贵的方式重写整个表格.如果磁盘上没有足够的可用空间,或者无法锁定整个表或为并发写入尝试生成错误,则只能使用该路由.

并发写入会发生什么？

在事务处理了SHARE锁之后,在同一个表中尝试INSERT / UPDATE / DELETE的其他事务(在其他会话中)将等到锁被释放或超时开始,以先到者为准.它们将以任何一种方式失败,因为他们试图写入的表已从它们下面删除.

新表有一个新表OID,但并发事务已将表名解析为上一个表的OID.当锁最终被释放时,他们会在写入之前尝试锁定表,并发现它已经消失了. Postgres将回答：

ERROR: could not open relation with OID 123456

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

如果你负担不起,你必须保留原来的表格.

保留现有表格的两种选择

>在添加NOT NULL约束之前就地更新(可能一次在小段上运行更新).添加具有NULL值且没有NOT NULL约束的新列很便宜.
从Postgres 9.2开始,你也可以创建一个CHECK constraint with NOT VALID：

The constraint will still be enforced against subsequent inserts or updates

这允许您在多个单独的事务中更新行peuàpeu.这样可以避免长时间保持行锁定,并且还可以重复使用死行. (如果没有足够的时间让autovacuum启动,你必须手动运行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：

> Disable all constraints and table checks while restoring a dump

>在临时表中准备新状态,TRUNCATE原始状态并从临时表中重新填充.一次性交易.在准备新表之前,您仍需要进行SHARE锁定以防止丢失并发写入.

有关SO的相关答案的详细信息：

> Best way to delete millions of rows by ID
> Add new column without table lock?