优化PostgreSQL中的批量更新性能

在Ubuntu 12.04上使用PG 9.1.

我们目前需要24小时才能运行大量UPDATE
数据库上的语句,其形式如下：

UPDATE tableSET field1 = constant1, field2 = constant2, ...WHERE id = constid

(我们只是覆盖由ID标识的对象的字段.)值来自外部数据源(不在表中的DB中).

这些表每个都有一些索引,没有外键约束.
直到最后才进行COMMIT.

导入整个数据库的pg_dump需要2小时.这似乎是一个
基线我们应该合理地定位.

生成一个以某种方式重建数据集的自定义程序
对于PostgreSQL重新导入,有什么我们可以做的来带来
批量更新性能更接近导入？ (这是一个区域
我们相信日志结构合并树处理得很好,但我们是
想知道在PostgreSQL中我们能做些什么.)

一些想法：

>之后放弃所有非ID指数并重建？
>增加checkpoint_segments,但这实际上有助于持续
长期吞吐量？
>使用the techniques mentioned here？ (然后将新数据加载为表格
“合并”旧数据,其中在新数据中找不到ID)

基本上有很多东西要尝试,我们不确定是什么
最有效的是,或者如果我们忽视其他事情.我们会的
接下来的几天试验,但我们认为我们会在这里问
同样.

我确实在表上有并发加载,但它是只读的.

假设

由于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 tableSELECT pg_column_size(t) FROM tmp_tbl t LIMIT 10;  -- size of sample rows

完整的脚本

SET temp_buffers = '1GB';        -- example valueCREATE TEMP TABLE tmp_tbl AS SELECT * FROM tbl LIMIT 0;COPY tmp_tbl FROM '/absolute/path/to/file';CREATE TABLE tbl_new ASSELECT t.col1, t.col2, u.field1, u.field2FROM   tbl     tJOIN   tmp_tbl u USING (id);-- Create indexes like in original tableALTER 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概述了类似的过程.