reltuples kept decreasing with each autovacuum run Re: BUG #18773: オートバキュームのリトライ時にreltuplesの値が減少する

On 2025/01/15 22:32, Daniel Gustafsson wrote:
>> On 15 Jan 2025, at 06:59, PG Bug reporting form <noreply(at)postgresql(dot)org> wrote:
>>
>> The following bug has been logged on the website:
>>
>> Bug reference: 18773
>> Logged by: 勇次 安藤
>> Email address: antengynnnn536(at)gmail(dot)com
>> PostgreSQL version: 13.6
>> Operating system: linax
>> Description:
>>
>> 現在、auroraposgres　SQLを使用して、オートバキュームを起動して、運用を行っています。
>
> Re-submitting this bugreport in English will greatly improve your chances of
> getting help.

+1

BTW, the reported issue is that when a table contains dead tuples that cannot be
removed due to a long-running transaction, repeated autovacuum runs gradually
decrease the table's reltuples value until it reaches a small number. I also
was able to reproduce this issue on the current HEAD as follows:

Session 1:
=# CREATE TABLE t AS SELECT i FROM generate_series(1, 50000) i;
=# BEGIN;
=# DELETE FROM t WHERE i = 1;
-- (Leave this session open)

Session 2:
=# DELETE FROM t WHERE i > 30000;
=# SELECT relpages, reltuples FROM pg_class WHERE relname = 't';

In my test, reltuples kept decreasing with each autovacuum run, as shown below:
------------
30000
15746
8452
4719
2809
1831
1331
1075
944
877
843
825
816
812
810
808
807
------------

After reltuples drops to 807, running ANALYZE restores the correct count of
live tuples. However, repeated autovacuum runs then cause reltuples to
decrease again.

From my review of the code, this seems related to the logic in vac_estimate_reltuples().
When a table has many unremovable dead tuples, autovacuum scans pages containing
mostly dead tuples and finds only a small number of live tuples. These few live
tuples are counted toward reltuples, but this small count is indirectly used to
calculate subsequent reltuples values. This feedback loop causes reltuples to
continually decrease in such scenarios.

I'm not sure if this is a bug, and I currently don't have a good solution
for the issue.

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION