临时表使pg_attribute膨胀[英] Temporary tables bloating pg_attribute

本文是小编为大家收集整理的关于临时表使pg_attribute膨胀的处理方法,想解了临时表使pg_attribute膨胀的问题怎么解决?临时表使pg_attribute膨胀问题的解决办法?那么可以参考本文帮助大家快速定位并解决问题。

问题描述

我正在使用 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....

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

这很好用,而且速度足以满足我们的需求.我们做了很多这样的工作,但我们遇到的问题是 pg_attribute 变得非常臃肿,而且 autovacuum 似乎无法跟上(并且消耗了 很多 的 CPU).

我的问题是:

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

推荐答案

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

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

然后临时表将在会话期间保留,但在每次提交时清空.

这将大大减少 pg_attribute 的膨胀,膨胀不再是问题.

你也可以加入黑暗面(请注意,这是不受支持的):

  • 使用 PostgreSQL 启动

    pg_ctl start -o -O
    

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

  • 以超级用户身份连接并运行

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

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

请注意,该设置将在重大升级后消失.

本文地址:https://www.itbaoku.cn/post/1763858.html