postgresql - 临时表膨胀 pg_attribute

Question

我正在使用 COPY 将大量数据从 CSV 文件插入到我们的数据库中。插入看起来像这样:

-- This tmp table will contain all the items that we want to try to insert
CREATE TEMP TABLE tmp_items
(
    field1 INTEGER NULL,
    field2 INTEGER NULL,
    ...
) ON COMMIT DROP;

COPY tmp_items(
    field1,
    field2,
    ...
) FROM 'path\to\data.csv' WITH (FORMAT csv);

-- Start inserting some items
WITH newitems AS (
    INSERT INTO items (field1, field2)
    SELECT tmpi.field1, tmpi,field2
    FROM tmp_items tmpi
    WHERE some condition

    -- Return the new id and other fields to the next step
    RETURNING id AS newid, field1 AS field1
)
-- Insert the result into another temp table
INSERT INTO tmp_newitems SELECT * FROM newitems;

-- Use tmp_newitems to update other tables
etc....

然后什么时候使用tmp_items中的数据在多个表中进行多次插入。我们在插入之前检查重复项并以几种方式操作数据，因此并非 tmp_items 中的所有内容都将按原样使用或插入。我们通过组合 CTE 和更多临时表来做到这一点。

这非常有效，而且速度足以满足我们的需求。我们做了很多这些，我们遇到的问题是 pg_attribute 很快变得非常臃肿，autovacuum 似乎无法跟上(并且消耗很多 CPU)。

我的问题是:

1. 是否可以在不使用临时表的情况下执行这种插入？
2. 如果不是，我们是否应该让 pg_attribute 的 autovacuum 更具攻击性？这不会占用同样多或更多的 CPU 吗？

Answer 1

最好的解决方案是在 session 开始时创建临时表

CREATE TEMPORARY TABLE ... (
   ...
) ON COMMIT DELETE ROWS;

然后临时表将在 session 期间保留，但在每次提交时清空。

这将大大减少pg_attribute 的膨胀，并且膨胀不再是问题。

你也可以加入黑暗面(警告，这是不受支持的):

• 启动 PostgreSQL

  pg_ctl start -o -O
  

  以便您可以修改系统目录。

• 以 super 用户身份连接并运行

  UPDATE pg_catalog.pg_class
  SET reloptions = ARRAY['autovacuum_vacuum_cost_delay=0']
  WHERE oid = 'pg_catalog.pg_attribute'::regclass;
  

现在 autovacuum 将在 pg_attribute 上更积极地运行，这可能会解决您的问题。

请注意，重大升级后该设置将消失。