如果我正确理解Postgres,那么更新在技术上是一个删除和插入,所以这基本上是重建整个2.2 gb表.我们也有一个奴隶跑,所以我们不希望它落后.
有没有比编写一个慢慢填充它的脚本更好的方法？
如果磁盘上有足够的可用空间(至少为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?
