PostgreSQL 14批量去重导入大文本数据的性能优化咨询

针对数百万条大文本数据的去重插入场景，当前方案的核心瓶颈在于IO开销过大（大文本全表扫描、冲突检查时的索引查询）以及重复计算冗余，以下是针对性优化手段：

1. 先在源表内去重，削减待处理数据量 #

原SQL会扫描所有非空文本（含大量重复项），先通过DISTINCT在源表层面去重，避免重复计算MD5和冲突检查：

WITH txt_data AS (
    SELECT DISTINCT html_text
    FROM many_texts
    WHERE html_text IS NOT NULL
)
INSERT INTO txt_document(doc_hash, doc_text)
SELECT md5(html_text)::uuid, html_text
FROM txt_data
ON CONFLICT DO NOTHING;

若源表数据更新不频繁，可为many_texts.html_text创建哈希索引加速DISTINCT计算：

CREATE INDEX idx_many_texts_html_text_hash ON many_texts USING hash (html_text);

注：大文本哈希索引会占用较多存储空间，但能显著降低去重耗时。

2. 临时调整数据库配置，提升写入吞吐量 #

批量插入时，临时关闭或调整以下配置（操作完成后务必恢复），减少WAL写入和检查点开销：

-- 会话级别临时调整，根据服务器硬件配置调整参数值
SET fsync = off;
SET synchronous_commit = off;
SET checkpoint_timeout = '30min';
SET max_wal_size = '10GB';
SET maintenance_work_mem = '1GB'; -- 32GB内存服务器可设为4GB

执行插入语句后恢复默认配置：

RESET fsync;
RESET synchronous_commit;
RESET checkpoint_timeout;
RESET max_wal_size;
RESET maintenance_work_mem;

注：关闭fsync会带来数据丢失风险，仅在批量导入且可接受临时风险时使用。

3. 优化冲突检查与写入效率 #

• 删除冗余索引：doc_hash的UNIQUE约束会自动创建唯一索引，无需额外创建idx_txt_document__hash，删除该冗余索引可减少写入时的索引维护开销：

  DROP INDEX idx_txt_document__hash;
  

• 分批处理数据：一次性处理数百万条数据会占用大量内存，改为分批插入（比如每批10万条），降低内存压力和冲突检查的单次开销：

  -- 示例：分批处理，需多次调整OFFSET值
  WITH txt_data AS (
      SELECT DISTINCT html_text
      FROM many_texts
      WHERE html_text IS NOT NULL
      LIMIT 100000
      OFFSET 0
  )
  INSERT INTO txt_document(doc_hash, doc_text)
  SELECT md5(html_text)::uuid, html_text
  FROM txt_data
  ON CONFLICT DO NOTHING;
  

  若要避免OFFSET的性能衰减，可使用游标分批处理：

  $$
  DECLARE
      cur CURSOR FOR
          SELECT DISTINCT html_text
          FROM many_texts
          WHERE html_text IS NOT NULL;
      rec record;
      batch_size INT := 100000;
      count INT := 0;
  BEGIN
      OPEN cur;
      LOOP
          FETCH cur INTO rec;
          EXIT WHEN NOT FOUND;
          
          INSERT INTO txt_document(doc_hash, doc_text)
          SELECT md5(rec.html_text)::uuid, rec.html_text
          ON CONFLICT DO NOTHING;
          
          count := count + 1;
          IF count >= batch_size THEN
              COMMIT;
              count := 0;
          END IF;
      END LOOP;
      COMMIT;
      CLOSE cur;
  END;
  $$ LANGUAGE plpgsql;
  

4. 预计算哈希值，避免重复计算 #

若源表后续仍有导入需求，可添加哈希列预计算并存储MD5值，避免每次插入都重新计算大文本的哈希：

-- 添加并计算哈希列
ALTER TABLE many_texts ADD COLUMN doc_hash uuid;
UPDATE many_texts SET doc_hash = md5(html_text)::uuid WHERE html_text IS NOT NULL;
-- 创建哈希索引加速去重
CREATE INDEX idx_many_texts_doc_hash ON many_texts(doc_hash);

后续插入时直接使用预计算的哈希，按哈希去重比按大文本去重效率更高：

WITH txt_data AS (
    SELECT DISTINCT doc_hash, html_text
    FROM many_texts
    WHERE html_text IS NOT NULL
)
INSERT INTO txt_document(doc_hash, doc_text)
SELECT doc_hash, html_text
FROM txt_data
ON CONFLICT (doc_hash) DO NOTHING;

5. 启用并行扫描加速源表读取 #

PostgreSQL 14支持并行全表扫描，调整参数开启多核心扫描：

-- 会话级别临时调整，根据CPU核心数设置，8核心服务器可设为6
SET max_parallel_workers_per_gather = 6;

调整后执行计划会使用并行扫描，利用多CPU核心加速源表数据读取。

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