Thread
-
BUG #19014: Automatic aggressive VACUUM on template0 and template1 pg_shdepend runs every minute
PG Bug reporting form <noreply@postgresql.org> — 2025-08-07T07:47:58Z
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. -
Re: BUG #19014: Automatic aggressive VACUUM on template0 and template1 pg_shdepend runs every minute
hubert depesz lubaczewski <depesz@depesz.com> — 2025-08-07T13:30:23Z
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
-
Re: BUG #19014: Automatic aggressive VACUUM on template0 and template1 pg_shdepend runs every minute
Tom Lane <tgl@sss.pgh.pa.us> — 2025-08-07T21:33:34Z
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
-
Re: BUG #19014: Automatic aggressive VACUUM on template0 and template1 pg_shdepend runs every minute
양주희 <1986comatose@gmail.com> — 2025-08-11T09:09:09Z
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>님이 작성: > 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 >