PostgreSQL中高效合并150张大空间数据表的最优方案

针对150张结构一致、单表达数十GB的大表合并需求，从速度和存储效率出发，最优方案是新建目标表+批量并行导入+临时关闭开销项，以下是具体步骤和优化技巧：

一、核心实施步骤 #

1. 创建优化后的目标表 #

避免直接往现有表插入（会持续维护索引/约束，拖慢速度），新建与源表结构一致的目标表，同时调整fid为自增序列（解决原表fid重复问题）：

-- 复制源表结构（不含数据）
CREATE TABLE merged_table AS SELECT * FROM table_1 LIMIT 0;
-- 删除原fid，替换为自增主键
ALTER TABLE merged_table DROP COLUMN fid;
ALTER TABLE merged_table ADD COLUMN fid SERIAL PRIMARY KEY;

2. 临时关闭索引、约束与日志开销 #

插入前关闭非必要的性能消耗项：

-- 禁用所有非主键索引（插入后重建）
ALTER INDEX idx_merged_geom DISABLE;
-- 禁用触发器/外键约束（若有）
ALTER TABLE merged_table DISABLE TRIGGER ALL;
-- 临时降低WAL日志级别（减少日志生成，插入后恢复原配置）
SET wal_level = minimal;
SET max_wal_size = 16GB; -- 按服务器内存调整
-- 临时关闭自动清理进程
SET autovacuum = off;

3. 批量并行插入数据 #

用动态SQL循环插入所有源表，避免手动编写150条语句：

DO $$
DECLARE
    table_name text;
BEGIN
    -- 遍历所有符合命名规则的源表（需根据实际表名调整LIKE条件）
    FOR table_name IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND tablename LIKE 'table_%' LOOP
        EXECUTE format('INSERT INTO merged_table (column_1, column_n, geom) SELECT column_1, column_n, geom FROM %I', table_name);
    END LOOP;
END $$;

4. 恢复索引与统计信息 #

插入完成后，恢复所有禁用项并更新表统计：

-- 重建并启用索引
ALTER INDEX idx_merged_geom ENABLE;
REINDEX INDEX idx_merged_geom;
-- 启用触发器/外键约束
ALTER TABLE merged_table ENABLE TRIGGER ALL;
-- 恢复WAL与自动清理配置
SET wal_level = replica; -- 恢复原默认值
SET autovacuum = on;
-- 更新表统计信息，优化后续查询
ANALYZE merged_table;

二、极致提速与存储优化技巧 #

1. 并行导入工具（适合超大规模数据） #

用pg_dump+pg_restore并行导入，比单条SQL插入速度更快：

# 导出所有源表数据（仅导数据，不导结构）
pg_dump -d your_database -t 'table_*' --data-only -f tables_data_dump.sql
# 并行导入到目标表（-j 后接CPU核心数，如8）
pg_restore -d your_database -j 8 -c tables_data_dump.sql

2. 非日志表临时加速（可选） #

若能接受数据库崩溃后临时数据丢失，可先创建UNLOGGED表插入，完成后转为普通表：

CREATE UNLOGGED TABLE merged_table AS SELECT * FROM table_1 LIMIT 0;
-- 插入数据...
-- 转为普通表，恢复日志保护
ALTER TABLE merged_table SET LOGGED;

3. 分区表适配超大合并表（可选） #

如果合并后表规模仍达TB级，可将目标表建成分区表，按fid范围或区域字段分区，后续查询仅扫描相关分区，提升性能：

-- 创建分区表模板
CREATE TABLE merged_table (
    fid SERIAL,
    column_1 text,
    column_n text,
    geom geometry
) PARTITION BY RANGE (fid);
-- 创建分区（按每100万行划分，按需调整）
CREATE TABLE merged_table_p1 PARTITION OF merged_table FOR VALUES FROM (1) TO (1000000);
CREATE TABLE merged_table_p2 PARTITION OF merged_table FOR VALUES FROM (1000001) TO (2000000);

三、避免原方案的弊端 #

直接往现有表插入其他表数据的问题：

• 持续维护索引/约束，插入速度极慢
• 生成大量WAL日志，占用额外存储，且事务回滚风险高

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