BUG #18773: オートバキュームのリトライ時にreltuplesの値が減少する

Started by PG Bug reporting formalmost 2 years ago10 messageshackersbugs
Jump to latest
#1PG Bug reporting form
noreply@postgresql.org
bugs

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を使用して、オートバキュームを起動して、運用を行っています。
オートバキュームの強度を確認したところ、あるテーブルを掴んでいる状態でオートバキュームのリトライが行われると、刀剣情報のreltuplesの値を減少させていることに気づきました。
マニュアル上は、オートバキューム実行時には書き換えられるものかと認識していましたが、リトライ時にも書き換えられており、テストでは、リトライを繰返すたびにあたいど徐々に小さくせいていることがわかりました。

reltuplesの値は、sql実行時に実行計画を作成するときに使用されるところなので、reltuples(テーブル行数)が小さいあたいで実行計画が作成されており、処理が終わりませんでした。analyzeをかけ直して、reltuplesの値を正規の値にしてsqlを実行したところ問題ない時間で処理が完了しています。

■再現テストの実施内容
1.aa_testというテーブルを50000件データで作成。
2.aa_testのテーブルに対して、1件deleteを行い、comitは行わずに掴んだ状態にしておく
3.更にaa_testに対して20000万件のdeleteを行い、こちらはcomitを行い完了しておく
4. 3.で20000万件deleteしたのをきっかけにオートバキュームが起動されるが、2.でテーブルを掴んでいるためリトライを繰返す
5.reltuplesの情報を確認すると、50000からリトライを繰返すたびに減少しており、529まで値が下がった

#2Daniel Gustafsson
daniel@yesql.se
In reply to: PG Bug reporting form (#1)
bugs
Re: BUG #18773: オートバキュームのリトライ時にreltuplesの値が減少する

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. Also note that if the database in question is running Aurora
Postgres then you need to contact AWS support as this bugreport form is only
for open-source PostgreSQL.

--
Daniel Gustafsson

#3Fujii Masao
masao.fujii@oss.nttdata.com
In reply to: Daniel Gustafsson (#2)
bugs
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@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

#4安藤勇次
antengynnnn536@gmail.com
In reply to: Fujii Masao (#3)
bugs
Re: reltuples kept decreasing with each autovacuum run Re: BUG #18773: オートバキュームのリトライ時にreltuplesの値が減少する

ダニエルさん
お世話になっております。

今回の事象はauroraで発生している事象になります。
動きとしてはバグの可能性が高いと考えています。ご確認いただけますと助かります。

必要な資料などあれば連絡ください。

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

よろしくお願いします。

________________________________
From: Fujii Masao <masao.fujii@oss.nttdata.com>
Sent: Thursday, January 16, 2025 1:53 AM
To: Daniel Gustafsson <daniel@yesql.se>; antengynnnn536@gmail.com <antengynnnn536@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: 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@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

#5Michael Paquier
michael@paquier.xyz
In reply to: 安藤勇次 (#4)
bugs
Re: reltuples kept decreasing with each autovacuum run Re: BUG #18773: オートバキュームのリトライ時にreltuplesの値が減少する

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

#6安藤勇次
antengynnnn536@gmail.com
In reply to: Michael Paquier (#5)
bugs
Re: reltuples kept decreasing with each autovacuum run Re: BUG #18773: オートバキュームのリトライ時にreltuplesの値が減少する

お世話になっております。

AWSサポートへ問い合わせを行いました。

結果は、マニュアルに記載されている内容には間違えがないですとの回答をもらいました。

awsサポートでは、マニュアルに記載されている内容が正しいかのみの確認しか行っておらず、
動きについて問題ないかの検証はされないようでした。

すいません、今回の事象は、aurora posgreSQLで動いているオートバキュームのバグの認識です。
AWSサポートでは、マニュアルの記載の内容は間違っていないとのことで、バグとしては受け付けてもらえませんでした。こういった場合、問い合わせ先など他にありますでしょうか?

お手数をおかけしますがよろしくお願いします。

________________________________
差出人: Michael Paquier
送信: 2025 年 1 月 21 日 (火曜日) 16:45
宛先: 勇次 安藤
Cc: Fujii Masao; Daniel Gustafsson; pgsql-bugs@lists.postgresql.org
件名: Re: reltuples kept decreasing with each autovacuum run Re: BUG #18773: オートバキュームのリトライ時にreltuplesの値が減少する

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

#7Masahiko Sawada
sawada.mshk@gmail.com
In reply to: 安藤勇次 (#6)
bugs
Re: reltuples kept decreasing with each autovacuum run Re: BUG #18773: オートバキュームのリトライ時にreltuplesの値が減少する

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

#8Michael Banck
mbanck@gmx.net
In reply to: Fujii Masao (#3)
bugshackers
Re: reltuples kept decreasing with each autovacuum run Re: BUG #18773

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

#9Michael Banck
mbanck@gmx.net
In reply to: Fujii Masao (#3)
bugshackers
Re: reltuples kept decreasing with each autovacuum run Re: BUG #18773

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

#10Michael Banck
mbanck@gmx.net
In reply to: Fujii Masao (#3)
hackersbugs
Re: reltuples kept decreasing with each autovacuum run Re: BUG #18773

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