目前，在数据库上运行大量的UPDATE
语句集，最多需要24小时，其形式为：
UPDATE table
SET field1 = constant1, field2 = constant2, ...
WHERE id = constid
（我们只是覆盖ID标识的对象的字段。）这些值来自外部数据源（尚未在表的DB中）。
每个表都有少量索引，没有外键约束。
直到最后都没有提交COMMIT。
导入整个数据库的
pg_dump需要2h。这似乎是我们应该合理地瞄准的基线。生产定制程序的某种方式，该程序以某种方式重建了数据集，以便PostgreSQL重新导入，有什么我们可以做的吗？如何使
批量更新性能更接近于导入？ （这是我们认为日志结构的合并树可以很好处理的区域，但是我们很想知道PostgreSQL中是否可以做些什么。）
一些想法：
删除所有非ID索引并随后进行重建？
增加checkpoint_segments，但这实际上有助于持续的
长期吞吐量吗？
使用这里提到的技术？ （将新数据加载为表格，然后
“合并”旧数据，而在新数据中找不到ID）
基本上有很多尝试，但我们不确定
最有效的是，或者我们忽略了其他事情。
接下来几天将进行实验，但我们认为我们也要在这里进行
。
我确实在表上有并发负载，但它读为-仅。
#1 楼
假设由于Q中缺少信息，我将假设：
您的数据来自数据库服务器上的文件。
数据的格式与
COPY输出类似，每行具有唯一的id以匹配目标表。如果不正确，请先对其进行正确格式化，或使用
COPY选项来处理格式。您将更新每个目标表中的单个行或其中的大多数行。
您可以删除并重新创建目标表。
这意味着没有并发访问。否则，请考虑以下相关答案：
在大表中填充新列的最佳方法？
根本没有依赖对象，除索引外。
解决方案
我建议您采用与第三个项目符号链接中概述的类似方法。通过重大优化。
创建临时表的方法更简单，更快捷：
CREATE TEMP TABLE tmp_tbl AS SELECT * FROM tbl LIMIT 0;
临时表中的一个大
UPDATE数据库内部的表比数据库外部的单个更新快几个数量级。 在PostgreSQL的MVCC模型中，
UPDATE意味着创建一个新的行版本并将旧的版本标记为已删除。这大约和INSERT和DELETE的总和一样贵。此外，它还会留下很多死元组。由于无论如何都要更新整个表，因此创建一个新表并删除旧表将整体上更快。如果有足够的RAM，请设置
temp_buffers（仅适用于此会话！）足够高以将临时表保存在RAM中-在执行其他任何操作之前。要估算需要多少RAM，请使用一个小样本运行测试并使用db对象大小函数：
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。该表保持一致，但是并发操作可能会出现异常，因此必须重复执行。有关此答案的详细信息：
在大型表中填充新列的最佳方法？
UPDATE route
如果（必须）执行
UPDATE路由，删除更新过程中不需要的所有索引，然后重新创建它。在一个索引中创建索引要比为每一行更新索引便宜得多。这也可能允许进行HOT更新。我在SO上这个与此密切相关的答案中概述了使用
UPDATE的类似过程。评论
我实际上只是在更新目标表中20％的行-不是全部，而是足够大的一部分以至于合并可能比随机更新要好。
–杨
13年4月28日在19:38
@AryehLeibTaurog：不应发生这种情况，因为DROP TABLE会删除访问独占锁。无论哪种方式，我都已经在答案的顶部列出了先决条件：您可以负担得起并重新创建目标表。在事务开始时锁定表可能会有所帮助。我建议您开始一个新的问题，其中包含您所处情况的所有相关详细信息，以便我们深入探讨这一问题。
–欧文·布兰德斯特（Erwin Brandstetter）
2014年7月9日15:03
@ErwinBrandstetter有趣。它似乎取决于服务器版本。我已经使用psycopg2适配器和psql客户端重现了8.4和9.1上的错误。在9.3上没有错误。在第一个脚本中查看我的评论。我不确定是否要在此处发布问题，但是值得在其中一个postgresql列表上征求一些信息。
– Aryeh Leib Taurog
2014年7月10日下午5:56
我在python中编写了一个简单的帮助程序类，以自动执行该过程。
– Aryeh Leib Taurog
14年7月14日在13:41
非常有用的答案。作为一种稍微的变化，可以创建仅包含要更新的列和引用列的临时表，从原始表中删除要更新的列，然后使用CREATE TABLE tbl_new AS SELECT t。*，u.field1， t。t2中的u.field2自然左联接tmp_tbl u ;，左联接允许保留没有更新的行。当然，可以将NATURAL更改为任何有效的USING（）或ON。
–Skippy le Grand Gourou
2014-09-15 20:02
#2 楼
如果可以在结构化文件中提供数据，则可以使用外部数据包装器读取数据并在目标表上执行合并。评论
“合并到目标表”的具体含义是什么？为什么使用FDW比将COPYing到临时表中更好（如原始问题的第三个项目符号所建议）？
–杨
13年4月27日在20:58
如MERGE sql语句中的“合并”。使用FDW，您可以执行此操作，而无需执行将数据复制到临时表中的额外步骤。我假设您不是要替换整个数据集，并且文件中会有一定数量的数据，这些数据不能代表对当前数据集的更改-如果发生了很大的变化，则可以更改整个数据集。替换表可能是值得的。
–大卫·奥尔德里奇（David Aldridge）
13年4月28日在17:28
@DavidAldridge：虽然在SQL：2003标准中定义了MERGE，但尚未在PostgreSQL中实现。其他RDBMS中的实现相差很大。考虑MERGE和UPSERT的标签信息。
–欧文·布兰德斯特（Erwin Brandstetter）
2013年5月1日17:37
@ErwinBrandstetter [笑]哦，是的。好吧，合并真的是锦上添花。无需导入临时表步骤即可访问数据实际上是FDW技术的症结所在。
–大卫·奥尔德里奇（David Aldridge）
13年5月1日18:43
评论
您的问题中缺少重要信息：您的Postgres版本？价值从何而来？听起来像数据库外的文件，但请澄清一下。您在目标表上有并发负载吗？如果是，那到底是什么？还是您可以负担得起并重新创建？没有外键，好的-但是还有其他依赖对象，例如视图吗？请使用缺少的信息编辑您的问题。不要在评论中挤压它。@ErwinBrandstetter谢谢，更新了我的问题。
我假设您已经通过解释检查了分析是否正在使用索引进行查找？