没有更新/删除的死元组 [英] Dead Tuples without updates/deletes

问题描述

有可能吗?我有一个包含快速增长的死元组的表，但白天我看不到表的任何更新或删除，只能插入和选择.Autovacuum 每 10 分钟运行一次，n_dead_tup 几乎为零并再次开始增长.

Is it possible? I have a table with fast growing dead tuples, but I can't see any update or delete to the table during the day, just inserts and selects. Autovacuum runs each 10 min, the n_dead_tup goes almost to zero and start growing again.

此表中有一个删除，用于清除日期为 15 天前的行，每天仅运行一次(三重检查，每天仅执行一次).

There is one delete at this table, for purging rows with date 15 days ago, running only once per day (triple-checked that is done just once per day).

在当天剩下的时间里，只在这个表上运行插入和选择.

In the rest of the day, only inserts and selects run on this table.

推荐答案

尝试插入数据失败可能会导致死元组.示例:

A failed attempt to insert data may cause dead tuples. Example:

create table test(id serial primary key, str text);

insert into test (str) values ('abc');

select pg_stat_get_dead_tuples('test'::regclass);

 pg_stat_get_dead_tuples 
-------------------------
                       0
(1 row)

insert into test values (1, 'def');

ERROR:  duplicate key value violates unique constraint "test_pkey"
DETAIL:  Key (id)=(1) already exists.

select pg_stat_get_dead_tuples('test'::regclass);

 pg_stat_get_dead_tuples 
-------------------------
                       1
(1 row)

这也适用于因回滚而中止的插入.

This also applies to the inserts aborted due to rollback.

这篇关于没有更新/删除的死元组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！