Maybe problems with autovaccum?

Hello,

VM with /usr/libexec/qemu-kvm

Linux HOSTNAME 5.14.0-570.37.1.el9_6.x86_64 #1 SMP PREEMPT_DYNAMIC Tue Aug
26 10:33:12 EDT 2025 x86_64 x86_64 x86_64 GNU/Linux

CPU:

processor : 39

vendor_id : GenuineIntel

cpu family : 6

model : 85

model name : Intel Xeon Processor (Skylake, IBRS)

stepping : 4

microcode : 0x1

cpu MHz : 3092.734

cache size : 4096 KB

postgres=# SELECT version();

version

----------------------------------------------------------------------------------------------------------

PostgreSQL 17.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0
20240719 (Red Hat 11.5.0-5), 64-bit

(1 row)

# cat /etc/os-release

NAME="AlmaLinux"

VERSION="9.6 (Sage Margay)"

ID="almalinux"

ID_LIKE="rhel centos fedora"

VERSION_ID="9.6"

PLATFORM_ID="platform:el9"

PRETTY_NAME="AlmaLinux 9.6 (Sage Margay)"

ANSI_COLOR="0;34"

LOGO="fedora-logo-icon"

CPE_NAME="cpe:/o:almalinux:almalinux:9::baseos"

HOME_URL="https://almalinux.org/"

DOCUMENTATION_URL="https://wiki.almalinux.org/"

BUG_REPORT_URL="https://bugs.almalinux.org/"

ALMALINUX_MANTISBT_PROJECT="AlmaLinux-9"

ALMALINUX_MANTISBT_PROJECT_VERSION="9.6"

REDHAT_SUPPORT_PRODUCT="AlmaLinux"

REDHAT_SUPPORT_PRODUCT_VERSION="9.6"

SUPPORT_END=2032-06-01

Postgresql up for ~22 days (enabled over systemctl, so VM runtime normaly
matches postgresql runtime)

# w

13:42:00 up 22 days, 4:06, 1 user, load average: 4.53, 5.59, 5.56

We have a table that has a high frequency on insert, update and delete
statements:

SELECT relname AS table_name, schemaname AS schema_name, coalesce(seq_scan,
0) + coalesce(idx_scan, 0) AS total_selects, n_tup_ins AS inserts,
n_tup_upd AS updates, n_tup_del AS deletes, vacuum_count, analyze_count,
autovacuum_count, autoanalyze_count FROM pg_stat_all_tables WHERE
schemaname = 'public' ORDER by (coalesce(seq_scan,0) + coalesce(idx_scan,0)
+ n_tup_ins + n_tup_upd + n_tup_del) DESC;

table_name | schema_name | total_selects | inserts |
updates | deletes | vacuum_count | analyze_count | autovacuum_count |
autoanalyze_count

--------------------------+-------------+---------------+-----------+-----------+-----------+--------------+---------------+------------------+-------------------

...

TABLE1 | public | 78878577 | 105398387 | 459085
| 105398208 | 3 | 3 | 38719 |
25583

...

our autovacume have near the standard settings, except:

log_autovacuum_min_duration 0 # log all autovacuum

autovacuum_vacuum_scale_factor 0.002

autovacuum_analyze_scale_factor 0.001

The TABLE1 has a maximum of 10000 entrys. Seen with count(1) over the day.

This causes the autovacuum to run 73 times per our (38719 / 22 / 24 =
73,3....)

* Thats interesting because we left the autovacuum_naptime to the default
of 1 min. Why does it vaccum more than once per minute?

Second thing was very strage. Inserts on TABLE1 where stalled for a long
time, our application was stalled to. In the postgressql log I see the
following:

2025-09-20 13:51:34.760 GMT team fsc 10.4.82.131(53190)
68bf3cb2.2180caWARNING: there is no transaction in progress

2025-09-20 14:00:04.782 GMT 68ceb157.1562c1ERROR: canceling autovacuum
task

2025-09-20 14:00:04.782 GMT 68ceb157.1562c1CONTEXT: while truncating
relation "public.TABLE1" to 235 blocks

automatic vacuum of table "DATABASENAME.public.TABLE1"

* And exact on 14:00:04.784 all INSERTs went through. How can I analyse
that, or is there a known bug regarding autovacuum?

Unfortunable I can not reproduce it and it happend for the first time after
an upgrade with pg_upgrade from postgresql16-16.9-3PGDG.rhel9.x86_64 to
postgresql17-17.6-1PGDG.rhel9.x86_64 => 3 weeks ago.

I hope you can help us and we are looking forward to your answers,

regards Philipp