Maybe problems with autovaccum? - Mailing list pgsql-bugs

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