gpt4 book ai didi

postgresql - Postgres 上的表膨胀

转载 作者:行者123 更新时间:2023-12-04 04:20:34 26 4
gpt4 key购买 nike

我有一个在 AWS RDS 实例上的 Postgres 9.5.15 上运行的小型 (~200GB) 数据仓库。

为了稳健性,我将新数据插入到分析模式(ELT 的结果)中,如下所示:

  1. 插入新切片
  2. 使用删除命令删除旧切片
  3. 真空

我知道当您执行删除或更新命令时,Postgres 会软删除元组。这不是给出表格大小的问题。问题是死元组不会通过 (3) 上的显式真空或常规自动真空来移除。因此,如果管道执行多次,我最终会得到巨大的表膨胀,这会严重影响表性能,更不用说浪费额外的存储空间了。

此外,当我开始调查时,我发现即使是系统表也有这个问题:

schemaname | relname | n_live_tup | n_dead_tup | ratio%
pg_catalog | pg_attribute | 46081 | 8339587 | 18097
pg_catalog | pg_depend | 27375 | 2490507 | 9097
pg_catalog | pg_statistic20094 | 1208474 | 6013

这可能会以我无法想象的方式使实例的总体性能变差。当我尝试做 VACUUM FULL VERBOSE pg_catalog.pg_attribute 它给了我这个:

"pg_attribute": found 0 removable, 8387117 nonremovable row versions in 152494 pages

我读过“表膨胀的 3 个原因”之类的文章,但都不适用(我没有进行复制,我没有挂起的事务等)。我可以使用 pg_repack 之类的东西在某个时间表中摆脱膨胀,但我想了解它发生的原因。我也不想肯定地重新打包系统表。

我唯一的假设是 vacuum 要求所有死元组都适合内存,内存限制为 maintenance_work_mem 设置(我们的实例为 127MB),我们需要增加它,但我首先需要一个侧面意见.

最佳答案

我最终编写了自己的函数来重新打包数据并按计划运行它们:

-- repack an individual table
CREATE OR REPLACE FUNCTION admin.repack_table(text)
RETURNS text
AS $$
DECLARE SQL text;
BEGIN

SELECT
'CREATE TEMP TABLE t1 (LIKE '||$1||');'||chr(10)||
'INSERT INTO t1 SELECT * FROM '||$1||';'||chr(10)||
'TRUNCATE TABLE '||$1||';'||chr(10)||
'INSERT INTO '||$1||' SELECT * FROM t1;'||chr(10)||
'DROP TABLE t1;'||chr(10)||
'ANALYZE '||$1||';'
INTO SQL;
EXECUTE SQL;
RETURN $1;
END;
$$ LANGUAGE plpgsql;

-- repack all tables in certain schema (with an optional threshold for N of dead tuples)
CREATE OR REPLACE FUNCTION admin.repack_schema(text,int default 5000)
RETURNS table (table_name text)
AS $$
DECLARE SQL text;
BEGIN
RETURN QUERY (
with
schema as (select $1)
select admin.repack_table(t.table_schema||'.'||t.table_name)
from information_schema.tables t
where t.table_schema=(select * from schema)
and t.table_name in (
select relname
from pg_stat_all_tables
where schemaname=(select * from schema)
and n_dead_tup>$2
and n_live_tup<1000000 -- avoid repacking too large tables
)
);
END;
$$ LANGUAGE plpgsql;

关于postgresql - Postgres 上的表膨胀,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/59456694/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com