优化PostgreSQL中的批量更新性能

由于Q中缺少信息,我将假设：

>您的数据来自数据库服务器上的文件.
>数据的格式与COPY输出类似,每行具有唯一的ID以匹配目标表.
如果没有,请先正确格式化,或使用COPY选项处理格式.
>您正在更新目标表中的每一行或其中的大多数行.
>您可以放弃并重新创建目标表.
这意味着没有并发访问.否则请考虑以下相关答案：

> Best way to populate a new column in a large table?

>除索引外,根本没有依赖对象.

解

我建议你采用link from your third bullet中概述的类似方法.进行主要优化.

要创建临时表,有一种更简单,更快捷的方法：

CREATE TEMP TABLE tmp_tbl AS SELECT * FROM tbl LIMIT 0;

来自数据库内的临时表的单个大UPDATE将比来自数据库外部的单个更新快几个数量级.

在PostgreSQL’s MVCC model中,UPDATE表示创建新行版本并将旧版本标记为已删除.这与INSERT和DELETE的组合价格一样高.另外,它会留下很多死元组.由于您无论如何都在更新整个表,因此创建一个新表并删除旧表会更快.

如果有足够的可用RAM,请将temp_buffers(仅适用于此会话！)设置为足以将临时表保存在RAM中 – 然后再执行其他操作.

要估计需要多少RAM,请使用小样本运行测试并使用db object size functions：

SELECT pg_size_pretty(pg_relation_size('tmp_tbl'));  -- complete size of table
SELECT pg_column_size(t) FROM tmp_tbl t LIMIT 10;  -- size of sample rows

完整的脚本

SET temp_buffers = '1GB';        -- example value

CREATE TEMP TABLE tmp_tbl AS SELECT * FROM tbl LIMIT 0;

COPY tmp_tbl FROM '/absolute/path/to/file';

CREATE TABLE tbl_new AS
SELECT t.col1,t.col2,u.field1,u.field2
FROM   tbl     t
JOIN   tmp_tbl u USING (id);

-- Create indexes like in original table
ALTER TABLE tbl_new ADD PRIMARY KEY ...;
CREATE INDEX ... ON tbl_new (...);
CREATE INDEX ... ON tbl_new (...);

-- exclusive lock on tbl for a very brief time window!
DROP TABLE tbl;
ALTER TABLE tbl_new RENAME TO tbl;

DROP TABLE tmp_tbl; -- will also be dropped at end of session automatically

并发负载

表格上的并发操作(我在开始时的假设中排除)将等待,一旦表格被锁定在接近结束并且一旦提交事务就失败,因为表格名称立即被解析为其OID,但是新表具有不同的OID.该表保持一致,但并发操作可能会出现异常并且必须重复.这个相关答案的细节：

> Best way to populate a new column in a large table?

更新路线

如果您(必须)转到UPDATE路由,请删除更新期间不需要的任何索引,然后重新创建它.创建一个索引要比为每个行更新它要便宜得多.这也可以允许HOT updates.

我在this closely related answer on SO中使用UPDATE概述了类似的过程.