批量CSV导入致pg_attribute膨胀的技术方案咨询

我来逐个解答你的问题，结合PostgreSQL的特性和批量导入的最佳实践来说：

是的，有几种替代方案可以避免频繁创建临时表，从根源上减少pg_attribute的压力：

• 复用固定的非临时中间表：
  提前创建一个UNLOGGED TABLE（性能接近临时表，且不写WAL日志），每次导入前先TRUNCATE清空数据，再执行COPY。这样避免了每次创建/销毁临时表带来的系统表条目变动：

  -- 仅需提前创建一次，无需每次执行都重复创建
  CREATE UNLOGGED TABLE staging_items (
    field1 INTEGER NULL,
    field2 INTEGER NULL,
    ...
  );
  
  -- 每次导入的固定流程
  TRUNCATE staging_items;
  COPY staging_items(field1, field2, ...) FROM 'path\to\data.csv' WITH (FORMAT csv);
  
  -- 后续用staging_items代替原tmp_items执行CTE插入、更新逻辑
  WITH newitems AS (
    INSERT INTO items (field1, field2)
    SELECT s.field1, s.field2 FROM staging_items s WHERE some condition
    RETURNING id AS newid, field1 AS field1
  )
  -- 若需要中间结果，可提前创建另一个unlogged表复用，同样用TRUNCATE清空后插入
  INSERT INTO staging_newitems SELECT * FROM newitems;
  

• 合并CTE逻辑，减少中间临时表：
  如果你的多表插入、更新逻辑可以串联成连续的CTE链，就能省去额外的中间临时表（比如原方案中的tmp_newitems）。直接利用插入返回的结果完成后续操作：

  -- 基于staging_items的连续CTE操作
  WITH newitems AS (
    INSERT INTO items (field1, field2)
    SELECT s.field1, s.field2 FROM staging_items s WHERE some condition
    RETURNING id AS newid, field1 AS field1
  )
  UPDATE other_table ot
  SET ot.some_field = ni.field1
  FROM newitems ni
  WHERE ot.related_id = ni.newid;
  

• 应用层直接处理（不推荐）：
  若数据量极小，可以在应用程序中读取CSV内容后直接构造INSERT语句，但这种方式性能远不如COPY，不适合你当前速度达标的场景。

如果必须保留临时表方案，调整pg_attribute的autovacuum参数是可行的，但需要权衡资源开销：

为什么临时表会引发pg_attribute膨胀？ #

每次创建临时表时，PostgreSQL会在pg_attribute系统表中为临时表的每个字段插入新记录；临时表销毁后，这些记录会变成死元组。默认的autovacuum对系统表的清理触发门槛较高，导致死元组堆积，进而引发膨胀。

如何调整autovacuum力度？ #

可以单独针对pg_attribute调低清理阈值，让autovacuum更及时地清理死元组：

ALTER TABLE pg_attribute SET (
  autovacuum_vacuum_threshold = 50,
  autovacuum_vacuum_scale_factor = 0.01
);

• autovacuum_vacuum_threshold：死元组数量达到该值时触发vacuum，默认值为50；
• autovacuum_vacuum_scale_factor：死元组数量达到表大小的该比例时触发vacuum，默认值为0.2（20%）。
  调低这两个参数后，autovacuum会更频繁地启动清理pg_attribute，避免死元组堆积。

会不会占用更多CPU？ #

是的，更频繁的autovacuum会增加CPU开销，但这种开销是可控的：

1. 调整autovacuum_work_mem，给autovacuum分配更多内存，让清理操作更高效，减少CPU占用的持续时间；
2. 设置autovacuum_vacuum_cost_limit和autovacuum_vacuum_cost_delay，限制autovacuum的资源消耗，避免抢占业务查询的CPU资源：

   ALTER TABLE pg_attribute SET (
     autovacuum_vacuum_cost_limit = 1000,
     autovacuum_vacuum_cost_delay = 10
   );
   

   这样autovacuum在消耗指定的“资源成本”后会短暂延迟，避免影响核心业务。

额外优化：复用临时表而非每次创建销毁 #

如果是同一个数据库会话中重复执行导入操作，可以只创建一次临时表，后续每次导入前用TRUNCATE清空，而非每次CREATE+ON COMMIT DROP：

-- 会话第一次执行时创建临时表
CREATE TEMP TABLE tmp_items (
  field1 INTEGER NULL,
  field2 INTEGER NULL,
  ...
);

-- 后续每次导入的固定流程
TRUNCATE tmp_items;
COPY tmp_items(field1, field2, ...) FROM 'path\to\data.csv' WITH (FORMAT csv);
-- 执行后续CTE逻辑...

这种方式从根源上减少了pg_attribute的写入操作，比单纯调整autovacuum参数更有效。

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