在PostgreSQL数据库中更新约1亿行的最有效方法？

Question

我有一个带有单个表的数据库。该表将需要每隔几周更新一次。我们需要将第三方数据摄入其中，它将包含100-1.2亿行。因此，基本上是：

1. 从源
2. 检测插入，更新＆amp; 中获取原始数据。删除
3. 可以更新并摄入数据库中

检测和执行更新的最佳方法是什么？ 一些选项是：

1. 将传入数据与当前数据库一一进行比较，然后进行单个更新。这似乎很慢，不可行。
2. 将传入的数据摄入新表中，然后
3. 在当前表中将新表格更新开出旧表。不知道该怎么做。

您建议最好的选择，或者是否有其他选择？

Answer 1

Postgres提供了改善 bulk Loads 的有用指南。从您的描述中，除了散装更新和delete外，还需要执行批量插入。下面是一个大致逐步指南，用于提高此高效：

在操作之前，

ALTER SYSTEM SET max_wal_size = <size>;

您可以完全禁用WAL。

ALTER SYSTEM SET wal_level = 'minimal';
ALTER SYSTEM SET archive_mode = 'off';
ALTER SYSTEM SET max_wal_senders = 0;

请注意，这些更改将需要重新启动数据库才能生效。

开始交易，

您希望所有工作都在一次交易中完成，以防万一出现任何问题。在多个连接之间并行运行副本通常不会增加性能，因为磁盘通常是限制因素。

优化事务级别的其他配置变量，

SET LOCAL maintenance_work_mem = <size>
...

如果您要对Postgres内部的数据进行任何其他特殊处理，则可能需要设置其他配置参数（work_mem通常是最重要的，尤其是在使用后扩展时。）请参阅本指南 对于性能的最重要的配置变量。

创建 a 临时没有没有约束的表。

CREATE TEMPORARY TABLE changes(
  id bigint,
  data text,
) ON COMMIT DROP; --ensures this table will be dropped at end of transaction

批量插入更改使用从复制

从 命令复制以将原始数据插入临时表中。

COPY changes(id,data) FROM .. 

放慢处理的关系

drop可以在TARGET表上 ， drop 所有外键约束，索引和触发器（如有可能）。不要删除主键，因为您需要insert。

将跟踪列添加到target表

添加列<代码>目标表，以确定“ conse：upsert：upsert：

ALTER TABLE target ADD COLUMN seen boolean;

upsert：upsert from 更改表” 目标表：

UPSERT是通过在冲突中添加子句中的标准insert语句来执行的。这样可以防止需要执行两个单独的操作。

INSERT INTO target(id,data,seen) 
  SELECT 
    id,
    data,
    true
  FROM
    changes
  ON CONFLICT (id) DO UPDATE SET data = EXCLUDED.data, seen = true;

delete不在更改中的行表

DELETE FROM target WHERE not seen is true;

更改表

DROP TABLE changes;
ALTER TABLE target DROP COLUMN seen;

跟踪列和临时

drop 所有限制，触发器和索引都被丢弃以提高大量UPSERT性能。

提交事务

批量UPSERT/DELETE已完成，并且应在交易之外执行以下命令。

在目标表上运行真空分析。

这将使查询计划者可以对桌子进行适当的推断，并恢复死去的分组所占的空间。

SET maintenance_work_mem = <size>
VACUUM ANALYZE target;
SET maintenance_work_mem = <original size>

还原数据库配置变量的原始值，

ALTER SYSTEM SET max_wal_size = <size>;
...

您可能需要再次重新启动数据库才能生效。