如何在PostgreSQL中更新大表

本文来源：www.codacy.com/blog/how-to…

在Postgres中更新大型表并不像看起来那样简单。如果您的表包含数亿行，您将发现很难及时进行简单的操作，例如添加列或更改列类型。

在不停机的情况下进行这类操作是一个更大的挑战。在这篇博客文章中，我将尝试概述一些策略，以在管理大型数据集的同时最大程度地减少表不可用性。

一般准则

当您更新列中的值时，Postgres将在磁盘中写入一个新行，弃用旧行，然后继续更新所有索引。此过程等同于INSERT加上每一行后再DELETE，这会占用大量资源。

除此之外，需要更新大表时还应了解的事项列表：

• 从头开始创建新表比更新每一行要快。顺序写比稀疏更新快，并且最后不会出现死行。
• 表约束和索引严重延迟了每次写入。如果可能，应在更新运行时删除所有索引，触发器和外键，并在最后重新创建它们。
• 添加没有默认值的可空列是一种廉价的操作。写入列的实际数据是昂贵的部分。
• 更新行时，不会重写存储在TOAST中的数据
• 从Postgres 9.2开始，在某些数据类型之间进行转换不需要重写整个表。例如：从VARCHAR（32）转换为VARCHAR（64）。

考虑到这一点，让我们看一些可以用来有效更新表中大量数据行的策略：

增量更新

如果您可以使用例如顺序ID对数据进行细分，则可以批量更新行。由于您只需要保持较短时间的锁定，因此可以最大化表的可用性。如果添加新列，则可以将其临时设置为可为空，然后开始逐渐用新值填充它。

这种方法的主要问题是性能，这是一个非常缓慢的过程，因为就地更新成本很高。在迁移期间，它可能还需要更复杂的应用程序逻辑。

创建一个新表

更新大表的最快方法是创建一个新表。

如果可以安全地删除现有表，并且有足够的磁盘空间，则执行更新的最简单方法是将数据插入到新表中，然后对其进行重命名。以下是此操作的基本执行脚本：

create table user_info_copy (LIKE user_info INCLUDING INDEXES INCLUDING COMMENTS);

INSERT INTO user_info_copy
SELECT user_no, idcard_no, real_name, bankcard_no, bind_mobile
     , false, bind_status, user_identity, create_time, creator
     , edit_time, editor, is_del, VERSION, customer_id
     , id_card_type, source_id, platform_no, one_passport_no, bank_code
FROM user_info;

drop TABLE user_info;

alter table user_info_copy rename to user_info;

重新创建现有表

如果由于不想重新创建视图或由于其他限制而不能删除原始表，则可以使用临时表保存新值，截断旧表并在那里重写数据。当您有未决的写请求时，此方法也有一些优点，如我们将在下一部分中看到的。

如果您的表可以容纳在内存中，则应在此事务期间增加temp_buffers属性。使用RAM代替磁盘来存储临时表将明显提高性能：

SET temp_buffers = 3000MB; ----相应地更改此值

# 创建临时表
CREATE TABLE temp_user_info(  
   user_no BIGINT,  
   PRIMARY KEY( user_no )  
);
# 如果需要提速可以从表中删除索引
# 复制数据到临时表中
insert into temp_user_info select user_no from user_info;

# 改变表结构，比如需要添加新列
TRUNCATE user_no;
# 执行插入列字段语句
# 再把数据反写到user_info表

处理并发写入

即使进行了上述优化，重新创建表仍然是缓慢的操作。如果您正在实时数据库中运行查询，则可能需要处理并发写入请求。

最简单的方法是在事务期间在表上强制使用SHARE LOCK, 语句如下

LOCK TABLE user_info IN SHARE MODE;

如果花费太长时间，所有写请求将一直等到锁释放或超时为止。如果未删除原始表，则一旦事务结束，将执行未超时的请求。请注意，即使使用相同的名称创建新表，请求仍将失败，因为它们使用表OID。

根据写请求的性质，您还可以创建自定义规则来存储对表所做的更改。例如，您可以设置一个规则，以在开始数据迁移之前记录已删除的行：

CREATE RULE deleted_rule AS ON DELETE
TO tbl
DO INSERT INTO tbl_deletes VALUES
(
  OLD.id
);

迁移结束时，您只需从tbl_deletes中读取ID，然后在新表上将其删除。可以使用类似的方法来处理其他类型的请求。

结论

一旦达到一定大小，曾经瞬时的操作可能需要几个小时来准备和执行。个人实验结论：

• 用存储过程批量更新 560w ， 1455秒结束
• 用复制表改名方法操作 560w数据， 120秒左右就结束了；