我们目前需要24小时才能运行大量UPDATE
数据库上的语句,其形式如下：
UPDATE table SET 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 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概述了类似的过程.
