PostgreSQL 8000万无索引表按日期筛选复制至新表的最优方案

首先直接回应你的两个核心问题：

1. 先为列d创建索引再执行复制是否更高效？ #

大概率不会，甚至可能更慢。原因有两点：

• 建索引本身需要全表扫描8000万条记录，这个过程本身就会消耗大量时间和IO资源。
• 如果d > '2018-01-01'的记录占比不低（比如超过30%），PostgreSQL优化器会直接选择全表扫描，而不是走索引——因为索引扫描需要先遍历索引找到符合条件的行，再回表读取全量数据，这个过程的IO开销反而比直接全表扫描更大。只有当符合条件的记录占比很低时，索引才能体现优势。

所以如果你的目标是复制符合条件的记录，先建索引是得不偿失的。

2. 删除表中所有d < '2018-01-01'的记录是否会更快？ #

绝对不推荐，除非符合条件的待删除记录占比极低（比如不到5%）。PostgreSQL的删除操作是"标记式删除"，并不会立即释放磁盘空间，反而会产生大量的WAL日志和死元组：

• 如果待删除的记录占比很高，删除过程会持续数小时甚至更久，期间还会持有表级锁（虽然是SHARE UPDATE EXCLUSIVE锁，但会阻塞其他写操作）。
• 删除完成后，表会出现严重的膨胀，你需要执行VACUUM FULL来回收空间，而这个操作会锁表，导致整个表在操作期间无法被读写，耗时可能比最初的复制操作还长。

更高效的替代方案 #

方案一：启用并行扫描加速CREATE TABLE AS #

PostgreSQL 10及以上版本支持全表扫描的并行执行，你可以把原来的SELECT INTO换成CREATE TABLE AS，并强制启用并行：

CREATE TABLE new_table AS
SELECT * FROM big_table WHERE d > '2018-01-01'
WITH (PARALLEL 4); -- 根据你的CPU核心数调整并行度，比如4或8

相比SELECT INTO，CREATE TABLE AS更灵活，而且并行扫描能利用多核CPU加速全表过滤过程。

方案二：分批复制数据 #

如果一次性复制的压力太大，可以分批处理，比如按d列的范围拆分，每批次复制一部分数据，同时能看到进度：

-- 先创建空表，结构和原表一致
CREATE TABLE new_table (LIKE big_table INCLUDING ALL);

-- 分批插入，比如每次处理100万条
INSERT INTO new_table
SELECT * FROM big_table 
WHERE d > '2018-01-01'
AND d <= '2019-01-01'; -- 调整时间范围分批次

-- 重复上述语句，逐步覆盖所有目标时间范围

这种方式的好处是可以通过查看new_table的行数来监控进度，也能避免一次性占用过多的IO资源。

方案三：使用pg_dump导出筛选后的数据再导入 #

如果你的服务器允许离线操作，用pg_dump的--where参数导出符合条件的数据，再导入到新表，有时候会比直接在数据库内操作更高效：

pg_dump -d your_db -t big_table --where="d > '2018-01-01'" -f filtered_data.sql
psql -d your_db -f filtered_data.sql

这个方法的优势是pg_dump的导出效率很高，而且可以通过命令行的输出看到进度。

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