使用libpqxx优化PostgreSQL Upsert性能求助

1. 内存预聚合+超大批量写入 #

当前每2000局的小批量写入效率极低，核心问题是重复Upsert同一用户。先在本地内存做聚合：

• 用C++的unordered_map缓存每个用户的defeated最高评分和lostto最高评分，每处理N局游戏后，合并哈希表中同一用户的记录（只保留最大值），再批量写入数据库。
• 替换单条/小批量预准备语句，改用批量INSERT + ON CONFLICT，或结合COPY导入临时表后再合并：
  批量Upsert示例SQL：

  INSERT INTO users (username, defeated, lostto)
  VALUES
    ('user1', 2500, 2600),
    ('user2', 2400, 2550),
    ... -- 几千到几万个用户记录
  ON CONFLICT (username) DO UPDATE SET
    defeated = GREATEST(users.defeated, EXCLUDED.defeated),
    lostto = GREATEST(users.lostto, EXCLUDED.lostto);
  

  更高效的COPY导入方式：
  1. 创建临时表：

     CREATE TEMP TABLE temp_users (username text, defeated int, lostto int);
     

  2. 通过libpqxx的copy接口将内存聚合的数据快速导入临时表；
  3. 一次性合并到主表：

     INSERT INTO users SELECT * FROM temp_users
     ON CONFLICT (username) DO UPDATE SET
       defeated = GREATEST(users.defeated, temp_users.defeated),
       lostto = GREATEST(users.lostto, temp_users.lostto);
     

2. 数据库核心配置优化 #

针对AWS EC2 r6i.xlarge（32GB内存），调整postgresql.conf参数：

• shared_buffers = 8GB（内存的1/4，最大化内存缓存）
• wal_buffers = 64MB（减少WAL写入频率）
• checkpoint_completion_target = 0.9（平滑检查点，降低IO峰值）
• max_wal_size = 64GB（减少检查点触发次数）
• synchronous_commit = off（牺牲少量持久性换写入性能，若数据可重新生成则优先开启；否则设为remote_write）
• work_mem = 64MB（避免排序/聚合时使用磁盘临时文件）
• maintenance_work_mem = 2GB（加速索引维护）
• 批量写入期间关闭autovacuum = off（避免后台清理带来的额外开销，写完后再开启）

3. 表结构与索引优化 #

• 仅保留username的唯一约束/主键（Upsert依赖的唯一键），暂时删除所有其他索引，等全量数据写入后再重建索引（索引会大幅拖慢写入速度）。
• 使用紧凑数据类型：评分用int而非bigint，用户名用varchar(64)（匹配LiChess用户名长度限制）避免不必要的空间占用。
• 若数据量极大，可考虑使用UNLOGGED TABLE创建临时写入表（不生成WAL日志，写入速度提升数倍），全量写入完成后再转为普通表（若需要持久性）：

  CREATE UNLOGGED TABLE users (
    username text PRIMARY KEY,
    defeated int,
    lostto int
  );
  -- 写入完成后转为普通表
  ALTER TABLE users SET LOGGED;
  

4. 事务与写入模式优化 #

• 减少事务提交次数：不要每2000局提交一次，改为每处理10万-100万条聚合后的用户记录提交一次事务（事务提交的固定开销很大，越少越高效）。
• 绝对禁止每局游戏执行一次Upsert，这会导致40亿次数据库交互，完全无法完成。

5. 硬件与存储优化 #

• 改用本地NVMe磁盘（比如AWS EC2 r6id.xlarge实例），本地磁盘的随机IO和吞吐量远高于EBS gp3，适合超大批量写入。
• 若使用EBS，将gp3磁盘的IOPS调至16000、吞吐量调至1000MB/s（最大化磁盘性能）。
• 读取LiChess文本文件时，优先下载到本地磁盘再处理，避免网络IO瓶颈。

内容的提问来源于stack exchange，提问作者aaron mei