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