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
Hello, Thank you for your help. I ran the query you suggested: select now()
- xact_start from pg_stat_activity order by xact_start asc nulls last limit
1 The result was 00:00:00.96726, which suggests there are no long-running
transactions. Regarding Tom Lane's suggestion, our application does
frequently create temporary tables. This might be a factor contributing to
the constant vacuuming of pg_shdepend. I am ready to provide more
information if you need it to help reproduce the issue. Thank you
2025년 8월 8일 (금) 오전 6:33, Tom Lane <tgl@sss.pgh.pa.us>님이 작성:
Show quoted text
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 usedfor
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