Обсуждение: BUG #19014: Automatic aggressive VACUUM on template0 and template1 pg_shdepend runs every minute

The following bug has been logged on the website:

Bug reference:      19014
Logged by:          양주희
Email address:      1986comatose@gmail.com
PostgreSQL version: 17.4
Operating system:   Ubuntu 22.04.2 LTS Jammy
Description:

We only actively use the ecount database, yet our logs show that an
automatic “aggressive” VACUUM is repeatedly running on
template0.pg_catalog.pg_shdepend and template1.pg_catalog.pg_shdepend at
roughly one-minute intervals. Since the template databases aren’t used for
normal operations, these vacuums seem unnecessary and are consuming system
resources. We believe this behavior is a bug.

Current Environment:


SELECT datname, mxid_age(datminmxid), age(datfrozenxid)
  FROM pg_catalog.pg_database;



 datname           | minmxid_age | frozenxid_age
-------------------+-------------+---------------
 ecount            |           8 |     129852308
 ecount_ecback     |          26 |     461750498
 template0         |           8 |     155458773
 postgres          |          26 |     461774826
 zabbix            |          26 |     461762369
 template1         |           8 |     155455251



Observed VACUUM Logs (every \~1 minute):


2025-08-07 09:56:34.747 KST [448702][][]LOG: automatic aggressive vacuum of
table "template1.pg_catalog.pg_shdepend": index scans: 1
2025-08-07 10:14:12.323 KST [617854][][]LOG: automatic aggressive vacuum of
table "template1.pg_catalog.pg_shdepend": index scans: 1
2025-08-07 10:32:43.043 KST [795650][][]LOG: automatic aggressive vacuum of
table "template1.pg_catalog.pg_shdepend": index scans: 1
2025-08-07 11:00:20.288 KST [1061208][][]LOG: automatic aggressive vacuum of
table "template0.pg_catalog.pg_shdepend": index scans: 1
2025-08-07 11:17:46.053 KST [1228250][][]LOG: automatic aggressive vacuum of
table "template0.pg_catalog.pg_shdepend": index scans: 1
2025-08-07 11:27:01.880 KST [1316659][][]LOG: automatic aggressive vacuum of
table "template0.pg_catalog.pg_shdepend": index scans: 1
2025-08-07 12:01:26.047 KST [1648057][][]LOG: automatic aggressive vacuum of
table "template0.pg_catalog.pg_shdepend": index scans: 1
2025-08-07 12:19:02.339 KST [1816944][][]LOG: automatic aggressive vacuum of
table "template1.pg_catalog.pg_shdepend": index scans: 1
2025-08-07 12:28:02.543 KST [1903394][][]LOG: automatic aggressive vacuum of
table "template1.pg_catalog.pg_shdepend": index scans: 1
2025-08-07 13:23:59.750 KST [2442227][][]LOG: automatic aggressive vacuum of
table "template1.pg_catalog.pg_shdepend": index scans: 1
2025-08-07 13:33:00.448 KST [2528862][][]LOG: automatic aggressive vacuum of
table "template1.pg_catalog.pg_shdepend": index scans: 1
2025-08-07 13:51:07.279 KST [2703165][][]LOG: automatic aggressive vacuum of
table "template0.pg_catalog.pg_shdepend": index scans: 1
2025-08-07 14:08:56.771 KST [2875759][][]LOG: automatic aggressive vacuum of
table "template0.pg_catalog.pg_shdepend": index scans: 1
2025-08-07 14:36:03.730 KST [3135733][][]LOG: automatic aggressive vacuum of
table "template0.pg_catalog.pg_shdepend": index scans: 1
2025-08-07 14:45:04.364 KST [3222224][][]LOG: automatic aggressive vacuum of
table "template0.pg_catalog.pg_shdepend": index scans: 1


Detailed VACUUM Output (example at 13:23:59):


2025-08-07 13:23:59.750 KST [2442227][][]LOG:  automatic aggressive vacuum
of table "template1.pg_catalog.pg_shdepend": index scans: 1
        pages: 0 removed, 10816 remain, 4070 scanned (37.63% of total)
        tuples: 24378 removed, 894511 remain, 159 are dead but not yet
removable
        removable cutoff: 1114343151, which was 1011 XIDs old when operation
ended
        new relfrozenxid: 1114343151, which is 578336 XIDs ahead of previous
value
        frozen: 126 pages from table (1.16% of total) had 1905 tuples frozen
        index scan needed: 3927 pages from table (36.31% of total) had 29967
dead item identifiers removed
        index "pg_shdepend_depender_index": pages: 29331 total, 192 newly
deleted, 3307 currently deleted, 3115 reusable
        index "pg_shdepend_reference_index": pages: 2040 total, 15 newly
deleted, 1234 currently deleted, 1219 reusable
        avg read rate: 31.500 MB/s, avg write rate: 3.503 MB/s
        buffer usage: 39704 hits, 12796 misses, 1423 dirtied
        WAL usage: 12474 records, 2780 full page images, 3602583 bytes
        system usage: CPU: user: 0.14 s, system: 0.32 s, elapsed: 3.17 s


Relevant autovacuum settings (from postgresql.conf):


autovacuum = on
autovacuum_analyze_scale_factor = 0
autovacuum_analyze_threshold = 100000000
autovacuum_freeze_max_age = 500000000
autovacuum_max_workers = 2
autovacuum_multixact_freeze_max_age = 400000000
autovacuum_naptime = 60
autovacuum_vacuum_cost_delay = 50
autovacuum_vacuum_cost_limit = -1
autovacuum_vacuum_insert_scale_factor = 0
autovacuum_vacuum_insert_threshold = 30000
autovacuum_vacuum_scale_factor = 0.1
autovacuum_vacuum_threshold = 50
autovacuum_work_mem = -1
log_autovacuum_min_duration = 10
vacuum_buffer_usage_limit = 102400
vacuum_cost_delay = 50
vacuum_cost_limit = 2000
vacuum_cost_page_dirty = 20
vacuum_cost_page_hit = 1
vacuum_cost_page_miss = 2
vacuum_failsafe_age = 1600000000
vacuum_freeze_min_age = 50000000
vacuum_freeze_table_age = 150000000
vacuum_multixact_failsafe_age = 1600000000
vacuum_multixact_freeze_min_age = 5000000
vacuum_multixact_freeze_table_age = 150000000


Please let me know if you need any further information or reproducer steps.

On Thu, Aug 07, 2025 at 07:47:58AM +0000, PG Bug reporting form wrote:
> The following bug has been logged on the website:
> We only actively use the ecount database, yet our logs show that an
> automatic “aggressive” VACUUM is repeatedly running on
> template0.pg_catalog.pg_shdepend and template1.pg_catalog.pg_shdepend at
> roughly one-minute intervals. Since the template databases aren’t used for
> normal operations, these vacuums seem unnecessary and are consuming system
> resources. We believe this behavior is a bug.

Please check what is:
select now() - xact_start from pg_stat_activity order by xact_start asc nulls last limit 1

I bet you have some long transaction somewhere, and this table is shared
(same table visible in all databases).

Best regards,

depesz

hubert depesz lubaczewski <depesz@depesz.com> writes:
> On Thu, Aug 07, 2025 at 07:47:58AM +0000, PG Bug reporting form wrote:
>> We only actively use the ecount database, yet our logs show that an
>> automatic “aggressive” VACUUM is repeatedly running on
>> template0.pg_catalog.pg_shdepend and template1.pg_catalog.pg_shdepend at
>> roughly one-minute intervals. Since the template databases aren’t used for
>> normal operations, these vacuums seem unnecessary and are consuming system
>> resources. We believe this behavior is a bug.

> I bet you have some long transaction somewhere, and this table is shared
> (same table visible in all databases).

Another theory, again remembering this is a shared catalog, is that
there is enough update traffic on pg_shdepend to justify constant
vacuuming.  That could be plausible if there's a lot of object
creation and deletion going on (even just temporary tables, I think).

Anyway, I don't see any such behavior here after setting
log_autovacuum_min_duration = 0.  We're going to need more
information to reproduce the problem.

            regards, tom lane