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