嘿，针对你这个百万级数据迁移的问题，我来给你拆解下各个方案的优劣，以及如何用COPY来提升效率，同时避开权限坑。

一、COPY真的比当前INSERT方案快吗？答案是肯定的 #

你的当前方案是用PL/pgSQL循环每次插入10000条，虽然已经删了外键和索引，但还是绕不开PostgreSQL对单条INSERT（哪怕批量）的事务日志开销：每次循环的INSERT都会触发WAL写入、事务提交的相关操作。而COPY是PostgreSQL专门为批量数据导入导出优化的命令，它的优势在于：

• 减少事务提交次数：COPY可以一次性处理数万甚至数十万条数据，只需要一次事务提交（默认情况）；
• 更低的WAL开销：如果归档表是UNLOGGED类型（如果你的归档数据不需要灾备的话），COPY可以完全跳过WAL写入；即使是普通LOGGED表，COPY的WAL写入也比批量INSERT更高效；
• 减少执行计划的重复计算：循环INSERT每次都要重新生成执行计划，而COPY的逻辑更简洁，数据库可以做更多优化。

实测下来，百万级数据的话，COPY的速度至少是你当前循环INSERT的3-10倍，数据量越大，差距越明显。

二、要不要解决服务器端COPY的权限问题？没必要，换个方式更简单 #

你遇到的must be superuser...错误是因为服务器端的COPY命令需要写服务器本地文件的权限，这个权限一般不会随便给普通用户，所以没必要纠结去申请权限——咱们可以用psql的\copy命令，或者直接通过STDOUT/STDIN管道来实现表之间的复制，完全不需要超级用户权限！

1. 用psql的\copy命令（客户端操作，无权限要求） #

\copy是psql的客户端命令，它会把数据从服务器拉到你的本地机器（作为中转），再导入到归档表，只要你有源表的读权限和归档表的写权限就行，不需要超级用户。比如：

# 一次性导出并导入（如果数据量太大可能内存不够，建议分批次）
psql -d your_db -c "\copy (SELECT * FROM some_table st WHERE NOT EXISTS (SELECT 1 FROM some_archive_table sat WHERE sat.id = st.id)) TO '/tmp/some_data.csv' WITH CSV HEADER"
psql -d your_db -c "\copy some_archive_table FROM '/tmp/some_data.csv' WITH CSV HEADER"

2. 用STDOUT/STDIN管道（不需要中间文件，更高效） #

如果你不想生成临时CSV文件，可以直接用管道把源表的查询结果通过STDOUT传给归档表的COPY命令，全程在内存中流转：

psql -d your_db -c "COPY (SELECT * FROM some_table st WHERE NOT EXISTS (SELECT 1 FROM some_archive_table sat WHERE sat.id = st.id)) TO STDOUT" | psql -d your_db -c "COPY some_archive_table FROM STDIN"

三、分批次COPY：避免一次性处理大数据的锁表/内存问题 #

如果你的源表数据量特别大（比如上千万条），一次性COPY可能会导致锁表时间过长，或者占用过多内存。这时候可以参考你当前的循环思路，分批次用COPY处理，比如基于主键id分页（比OFFSET更高效）：

#!/bin/bash
DB_NAME="your_db"
BATCH_SIZE=10000
last_id=0

while true; do
  # 导出当前批次的数据到STDOUT，同时导入到归档表
  psql -d $DB_NAME -c "COPY (SELECT * FROM some_table st WHERE st.id > $last_id AND NOT EXISTS (SELECT 1 FROM some_archive_table sat WHERE sat.id = st.id) ORDER BY st.id LIMIT $BATCH_SIZE) TO STDOUT" | psql -d $DB_NAME -c "COPY some_archive_table FROM STDIN"
  
  # 获取本次处理的最大id，用于下一批次的起始点
  new_last_id=$(psql -d $DB_NAME -t -c "SELECT COALESCE(MAX(id), 0) FROM some_table st WHERE st.id > $last_id AND NOT EXISTS (SELECT 1 FROM some_archive_table sat WHERE sat.id = st.id) LIMIT $BATCH_SIZE")
  new_last_id=$(echo $new_last_id | xargs) # 去掉空格换行
  
  # 如果没有新数据，退出循环
  if [ "$new_last_id" -eq "$last_id" ]; then
    break
  fi
  last_id=$new_last_id
  echo "Processed up to id: $last_id"
done

这个脚本会每次处理10000条数据，直到所有未归档的数据都迁移完成，既保留了COPY的高性能，又避免了一次性处理的风险。

四、当前INSERT方案的小优化（如果暂时不想换COPY） #

如果你暂时没法用COPY，也可以优化下你的PL/pgSQL代码：去掉CTE，直接用INSERT ... SELECT LIMIT，因为PostgreSQL的CTE是“优化围栏”，会阻止优化器把CTE和INSERT合并，直接子查询的效率更高：

DO $$
DECLARE
  rec_count INT;
BEGIN
  LOOP
    INSERT INTO some_archive_table
    SELECT * FROM some_table st
    WHERE NOT EXISTS (SELECT 1 FROM some_archive_table sat WHERE sat.id = st.id)
    LIMIT 10000;
    GET DIAGNOSTICS rec_count = ROW_COUNT;
    EXIT WHEN rec_count = 0;
  END LOOP;
END $$;

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