pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
reltuples kept decreasing with each autovacuum run Re: BUG #18773: オートバキュームのリトライ時にreltuplesの値が減少する
4+ messages / 4 participants
[nested] [flat]

* reltuples kept decreasing with each autovacuum run Re: BUG #18773: オートバキュームのリトライ時にreltuplesの値が減少する
@ 2025-01-15 16:53  Fujii Masao <masao.fujii@oss.nttdata.com>
  0 siblings, 2 replies; 4+ messages in thread

From: Fujii Masao @ 2025-01-15 16:53 UTC (permalink / raw)
  To: Daniel Gustafsson <daniel@yesql.se>; antengynnnn536@gmail.com; pgsql-bugs@lists.postgresql.org



On 2025/01/15 22:32, Daniel Gustafsson wrote:
>> On 15 Jan 2025, at 06:59, PG Bug reporting form <noreply@postgresql.org> wrote:
>>
>> The following bug has been logged on the website:
>>
>> Bug reference:      18773
>> Logged by:          勇次 安藤
>> Email address:      antengynnnn536@gmail.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






^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: reltuples kept decreasing with each autovacuum run Re: BUG #18773: オートバキュームのリトライ時にreltuplesの値が減少する
@ 2025-01-21 07:45  Michael Paquier <michael@paquier.xyz>
  parent: Fujii Masao <masao.fujii@oss.nttdata.com>
  1 sibling, 1 reply; 4+ messages in thread

From: Michael Paquier @ 2025-01-21 07:45 UTC (permalink / raw)
  To: 勇次 安藤 <antengynnnn536@gmail.com>; +Cc: Fujii Masao <masao.fujii@oss.nttdata.com>; Daniel Gustafsson <daniel@yesql.se>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

On Tue, Jan 21, 2025 at 02:13:05AM +0000, 勇次 安藤 wrote:
> ダニエルさん
> お世話になっております。

This is a mailing list in English, so it would be suited to reply in
this language.

> 今回の事象はauroraで発生している事象になります。
> 動きとしてはバグの可能性が高いと考えています。ご確認いただけますと助かります。
> 
> 必要な資料などあれば連絡ください。
> 
> まさおさん
> 情報のご連携ありがとうございます。同じ事象が再現できたとのことで、
> 現在のところ対応は、バキュームのリトライが発生した場合に、アナライズ
> を実施する手立てしかない認識で、夜間バッチ時間帯の本番環境で起きてい
> ることから、長時間稼働したものがあればれんらくをもらって手動でアナラ
> イズを行う手立てしかおこなえず、後手後手になっています・・・オンライ
> ン前にすべてのテーブルに対してアナライズを行うことでオンラインへの影
> 響をなくす手立てがあるるかと思うのですが、テーブル数が2000を超えてい
> るので、オンライン開始までに終わらせられるかなど検証を進めている状況
> です・・ 

I do read Japanese, FWIW, and I would recommend to limit yourself in
using PostgreSQL rather than Aurora when doing such investigations for
such reports.  If limiting yourself with Aurora, please contact Aurora
support at AWS first.
--
Michael

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

*  Re: reltuples kept decreasing with each autovacuum run Re: BUG #18773: オートバキュームのリトライ時にreltuplesの値が減少する
@ 2025-01-29 02:16  Masahiko Sawada <sawada.mshk@gmail.com>
  parent: Michael Paquier <michael@paquier.xyz>
  0 siblings, 0 replies; 4+ messages in thread

From: Masahiko Sawada @ 2025-01-29 02:16 UTC (permalink / raw)
  To: 安藤勇次 <antengynnnn536@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; Fujii Masao <masao.fujii@oss.nttdata.com>; Daniel Gustafsson <daniel@yesql.se>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

On Tue, Jan 28, 2025 at 4:30 AM 安藤勇次 <antengynnnn536@gmail.com> wrote:
>
> お世話になっております。
>
> AWSサポートへ問い合わせを行いました。
>
> 結果は、マニュアルに記載されている内容には間違えがないですとの回答をもらいました。
>
> awsサポートでは、マニュアルに記載されている内容が正しいかのみの確認しか行っておらず、
> 動きについて問題ないかの検証はされないようでした。
>
> すいません、今回の事象は、aurora posgreSQLで動いているオートバキュームのバグの認識です。
> AWSサポートでは、マニュアルの記載の内容は間違っていないとのことで、バグとしては受け付けてもらえませんでした。こういった場合、問い合わせ先など他にありますでしょうか?

If you prefer to ask in Japanese, I would recommend posting it in
postgresql-jp slack. You may be able to discuss workarounds for that
there. You can find the information about joining postgresql-jp slack
here:

https://www.postgresql.jp/node/188

Regards,

-- 
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com


^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: reltuples kept decreasing with each autovacuum run Re: BUG #18773
@ 2026-10-16 17:09  Michael Banck <mbanck@gmx.net>
  parent: Fujii Masao <masao.fujii@oss.nttdata.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Michael Banck @ 2026-10-16 17:09 UTC (permalink / raw)
  To: Fujii Masao <masao.fujii@oss.nttdata.com>; +Cc: Daniel Gustafsson <daniel@yesql.se>; pgsql-bugs@lists.postgresql.org; pgsql-hackers@lists.postgresql.org

Hi,

bumping this and CCing -hackers as we had a customer having a similar
issue (see below) tanking their customer's databases.

What they reported was that they saw reltuples for catalog tables, in
particular pg_class, pg_depend and (sometimes) pg_attribute set to 1,
which led to sequential scans for catalog queries and high CPU load /
massively increased latency in the application they are a vendor for on
some of their customers. Running ANALYZE manually fixes the problem, at
least temporarily.

On Thu, Jan 16, 2025 at 01:53:44AM +0900, Fujii Masao wrote:
> 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.

This still reproduces with PG19Rc1.

807 is the low bound there, it will not go lower with the above 'DELETE
FROM t WHERE i > 30000'. That one leaves quite a few tuples on the last
page though:

postgres=# SELECT ctid, i FROM t ORDER BY ctid DESC LIMIT 1; 
   ctid    |   i   
-----------+-------
 (132,168) | 30000

If you further delete those 168 tuples, reltuples gets
lowered to 463:

postgres=# DELETE FROM t WHERE i > 29832;
DELETE 168

<wait>

postgres=# SELECT reltuples FROM pg_class WHERE relname = 't';
 reltuples 
-----------
       463

If you don't delete the first i, but the last i, reltuples goes into
single-digits:

Session 1:
postgres=# BEGIN;
BEGIN
postgres=*# DELETE FROM t WHERE i = 50000;
DELETE 1

Session 2:
postgres=# DELETE FROM t WHERE i > 29832 AND i < 50000;
DELETE 20167

<wait>

postgres=# SELECT reltuples FROM pg_class WHERE relname = 't';
 reltuples 
-----------
         3

To get reltuples all the way to 1, it looks like one needs to delete
more, the most one can delete is all but the lower 31 pages:

postgres=# DELETE FROM t WHERE i > 7232 AND i < 50000;
DELETE 22600
postgres=# SELECT ctid, i FROM t ORDER BY ctid DESC LIMIT 2;
   ctid   |   i   
----------+-------
 (221,54) | 50000
 (31,226) |  7232
(2 rows)

postgres=# SELECT reltuples FROM pg_class WHERE relname = 't';
 reltuples 
-----------
         1

The customer I mentioned above said they create and delete a lot of temp
tables while also having cursors open for a longer time. I could
reproduce the above issue with pg_class itself as follows:

Session 1:
CREATE TEMP TABLE test();
SELECT nspname FROM pg_namespace WHERE oid = pg_my_temp_schema(); 

<note temp schema>

DO $$  
DECLARE
    i INT; 
BEGIN
    FOR i IN SELECT generate_series(1, 5500)
    LOOP
        EXECUTE format(
            'CREATE TEMP TABLE IF NOT EXISTS temp_table_%s (id INT)',
            i
        );
    END LOOP;
END $$; 
BEGIN;
DROP TABLE temp_table_5500;

Session 2:
DO $$
DECLARE
    i INT;
BEGIN
    FOR i IN SELECT generate_series(949,5499)
    LOOP
        EXECUTE format(
            'DROP TABLE IF EXISTS pg_temp_$num.temp_table_%s',
            i
        );
    END LOOP;
END $$;
DO
postgres=# SELECT ctid, relname FROM pg_class ORDER BY ctid DESC LIMIT
2;
  ctid   |     relname     
---------+-----------------
 (133,7) | temp_table_5500
 (31,45) | temp_table_948
j
<wait>

postgres=# SELECT reltuples FROM pg_class WHERE relname = 'pg_class';
 reltuples 
-----------
         1
(1 row)

> 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.

I am not sure whether it is a bug either, but it is certainly
unfortunate behaviour for some :-/


Michael






^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2026-10-16 17:09 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-01-15 16:53 reltuples kept decreasing with each autovacuum run Re: BUG #18773: オートバキュームのリトライ時にreltuplesの値が減少する Fujii Masao <masao.fujii@oss.nttdata.com>
2025-01-21 07:45 ` Michael Paquier <michael@paquier.xyz>
2025-01-29 02:16   ` Masahiko Sawada <sawada.mshk@gmail.com>
2026-10-16 17:09 ` Re: reltuples kept decreasing with each autovacuum run Re: BUG #18773 Michael Banck <mbanck@gmx.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox