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

Question

我有一个只有一张桌子的数据库。这个表格需要每隔几周更新一次。我们需要摄入第三方数据，它将包含1亿到1.2亿行数据。因此，流程基本上是：

1. 从源获取原始数据
2. 检测插入、更新和删除
3. 对数据库进行更新和摄取

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

1. 将输入的数据与当前数据库逐一进行比较，并进行单个更新。这似乎非常缓慢，也不可行。
2. 将传入的数据摄取到新表中，然后用新表切换出旧表。
3. 当前表中的大容量更新。不知道该怎么做。

你认为最好的选择是什么，或者如果有不同的选择？

Answer 1

Postgres对提高散装负荷的性能有很好的指导作用。根据您的描述，除了批量INSERT和DELETE之外，还需要执行大容量UPDATE。下面是一个大致一步一步的指南，以提高这一效率：

操作之前配置全局数据库配置变量

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通常是最重要的，特别是在使用Postgis扩展时)。有关性能的最重要配置变量，请参见本指南。

CREATE没有约束的TEMPORARY表。

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

使用changes大容量插入COPY FROM

使用COPY FROM命令将原始数据大容量插入临时表中。

COPY changes(id,data) FROM .. 

可以减慢处理速度的DROP关系

在target表上，DROP所有外键约束、索引和触发器(如果可能)。不要丢下主键，因为INSERT需要这样做。

向target表中添加跟踪列

向target表中添加一列，以确定“更改”表中是否存在行：

ALTER TABLE target ADD COLUMN seen boolean;

从changes表到target表的UPSERT：

UPSERT是通过向标准的ON CONFLICT语句添加一个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表中的changes行

DELETE FROM target WHERE not seen is true;

DROP跟踪列和临时changes表

DROP TABLE changes;
ALTER TABLE target DROP COLUMN seen;

为性能添加删除的关系

添加删除的所有约束、触发器和索引，以提高大容量插入的性能。

提交事务

大容量插入/删除已完成，以下命令应在事务之外执行。

在VACUUM ANALYZE表上运行target。

这将允许查询规划者对表进行适当的推断，并回收死元组占用的空间。

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

恢复数据库配置变量的原始值

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

为了使这些设置生效，您可能需要再次重新启动数据库。