Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vEW0d-00Cetd-R5 for pgsql-general@arkaria.postgresql.org; Thu, 30 Oct 2025 17:00:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1vEW0c-009idk-Pp for pgsql-general@arkaria.postgresql.org; Thu, 30 Oct 2025 17:00:21 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vEW0c-009idZ-Dj for pgsql-general@lists.postgresql.org; Thu, 30 Oct 2025 17:00:21 +0000 Received: from mail-oi1-x22f.google.com ([2607:f8b0:4864:20::22f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vEW0Z-0056Rh-0R for pgsql-general@lists.postgresql.org; Thu, 30 Oct 2025 17:00:21 +0000 Received: by mail-oi1-x22f.google.com with SMTP id 5614622812f47-44da774cc9dso759551b6e.3 for ; Thu, 30 Oct 2025 10:00:19 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1761843617; x=1762448417; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=oezv2nPCYClJiaBUagm5czLZaCBjp8e0mnFKjyMjiyc=; b=bzJKcpyZ+76JjqD+GJUYqNEQQ+EAw1sIbhyvgXsquUNpIaZ5FD71At3kWRjTstqjvY hCwwymxDb7ImdKkwP1cqkEzuUN9BhIFaGLXK8R42idjQBJ5HKRt7PUhhY9IBPKNdngpV GbOtoFhZ6AZwBLABY3fvIfZiRYNOCOh5ggMq4l95EgkO8WxkKIxWEv7MGY/eS4wi49Qb kiEPnhzO7AEBNfwFIa4+hbkLUhYUu5aCsHNaaXFBzFSNfjJJRg9RYIepTJyYvHZgnuzZ opPziEGiXp3WYPn7yL69ECn2CJwDc7wyF5dAs0rZEokM5ehmwA3YE6Jny3EkXBGmHoey Q3Bg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1761843617; x=1762448417; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=oezv2nPCYClJiaBUagm5czLZaCBjp8e0mnFKjyMjiyc=; b=Q5XvwGwTMju800ga9TRHD4tO6KDTd92QISwA7DEOSABJEPl0KRfuvBLHl1Vm1y/sTJ kkb5N56yMinWCOiIPjk14RiM1ldgftphfuG0xCYvmWqPNzzbjMJQ8sOxt5lt4xrgerRg d6WZzMAW3bup5XeGEKOJPAE2yTXw5py4hRBiX/IH/N9is2qc+FwD/DxC+7LzY620j8c8 V6aKfM4p5a0fd6rqbU627BddSXY60KHUu+8J0HAIwKHRWTLsDKP2gJtV1oxeSwuEYmSw gc4rgPf+/32/WUXXo8yJbRw/uIgZNrkzdSy3C/mbzL1DuPcI1wBL/w4SeMFKz3LpZIIn FeeQ== X-Gm-Message-State: AOJu0YyxY8ZB43UCxRWT63/cpFSTJPRY8UHnsi5VKl6rsH4Ekjcwdpt+ YkJPjcHXPtY2Peb8r/vZoSMOWIgzjBZGsPi27l3IewcAKnQ9KSol7MAhc+P1z8zY/xQZ4SL5uy/ cPie9nz8NvssudPzwaKu1UvaBD4z2VM0= X-Gm-Gg: ASbGncs0puXtxPO0OOk+pcFE4ZcWPNH1Wpzm3kiQDUEFDieJJN5PyBXU/CjtUkwt5VB 6vSj2VymgVXKry3wMztnMgUupuZ4Csfjwaej46cqm9mnnwkn5hUhV4rtFoQkk5l9ri5a45LaniK /ukdqbz0fxE34HbkdFPKjoXtSQxnxFJ+axLwrl4/xtYpHdG4bmDIU71myClPdibZDgUyDmqsWXo xohyUBPpVrSoLA2Y6tS+N/5qrTTmM+SVbCzCBFdwB1Dxo3bzo9GhmWONnOsCQ== X-Google-Smtp-Source: AGHT+IHW8fVhlCNtqWqxyJsadTEQf5FJEyZppTW5R6kar3Xj+5aMRZub6mLylu6/7LTkWf6O/8flPtqqwH/yChiu+Vo= X-Received: by 2002:a05:6808:178f:b0:441:8f74:f15 with SMTP id 5614622812f47-44f95ff8c04mr127448b6e.63.1761843616629; Thu, 30 Oct 2025 10:00:16 -0700 (PDT) MIME-Version: 1.0 References: <26qs98r6-0q81-non7-3n17-0r14o9851pp9@tzk.arg> In-Reply-To: <26qs98r6-0q81-non7-3n17-0r14o9851pp9@tzk.arg> From: Ron Johnson Date: Thu, 30 Oct 2025 13:00:05 -0400 X-Gm-Features: AWmQ_bm4wtrIHS76EsnAcJSMWsnf52RnvfVNpUw2aX3Kf6RkQVwyoTnlQpKITUU Message-ID: Subject: Re: Why isn't my table auto-analyzed/vacuumed? To: Dimitrios Apostolou Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000eb48e20642632ecb" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000eb48e20642632ecb Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, Oct 30, 2025 at 11:55=E2=80=AFAM Dimitrios Apostolou wrote: > Hello list, > > I have a table that is constantly growing, and it's not being > vacuumed/analyzed. I think my problem is rather common, but how to even > debug it if "nothing works"? > > I've already set log_autovacuum_min_duration =3D 0 but the table is never > mentioned in my logs, grep'ing for "vacuum". > > I have run ANALYZE manually once but nothing automatic. > Here is more info: > > > SELECT * FROM pg_stat_user_tables WHERE relname =3D > 'test_runs_summarized_per_function' \gx > -[ RECORD 1 ]-------+---------------------------------- > relid | 780653 > schemaname | public > relname | test_runs_summarized_per_function > seq_scan | 32 > last_seq_scan | 2025-10-19 10:31:08.289922+00 > seq_tup_read | 26484817584 > idx_scan | 4554128 > last_idx_scan | 2025-10-10 22:02:50.987532+00 > idx_tup_fetch | 7418587674 > n_tup_ins | 921064234 > n_tup_upd | 0 > n_tup_del | 0 > n_tup_hot_upd | 0 > n_tup_newpage_upd | 0 > n_live_tup | 6484485348 > n_dead_tup | 0 > n_mod_since_analyze | 423101205 > n_ins_since_vacuum | 921064234 > last_vacuum | > last_autovacuum | > last_analyze | 2025-09-30 18:24:47.550543+00 > last_autoanalyze | > vacuum_count | 0 > autovacuum_count | 0 > analyze_count | 1 > autoanalyze_count | 0 > > > SELECT reltuples FROM pg_class WHERE relname =3D > 'test_runs_summarized_per_function' \gx > -[ RECORD 1 ]----------- > reltuples | 6.061923e+09 > > > SELECT name,setting FROM pg_settings WHERE name ILIKE '%factor%' ; > name | setting > ---------------------------------------+--------- > autovacuum_analyze_scale_factor | 0.1 > 0.1 means 10%. > autovacuum_vacuum_insert_scale_factor | 0.2 > autovacuum_vacuum_scale_factor | 0.2 > recursive_worktable_factor | 10 > n_mod_since_analyze=3D423101205 n_live_tup=3D6484485348 n_mod_since_analyze/n_live_tup =3D 6.5% > How can I get more info from postgres on the autovacuum logic? > I would: 1) manually VACUUM ANALYZE the table, 2) drop the three autovacuum_*_scale_factor values down to 0.03 (i.e. 3%), 3) reload the conf file, 4) add "(1.0*n_mod_since_analyze/n_live_tup)::decimal(6,3)" to the pg_stat_user_tables query, and 4) closely monitor pg_stat_user_tables WHERE relname =3D 'test_runs_summarized_per_function'. https://www.percona.com/blog/importance-of-postgresql-vacuum-tuning-and-cus= tom-scheduled-vacuum-job/ helped me a lot. It also validated my cron job that does "manual" ANALYZE & VACUUM on tables that autovacuum isn't picking up, even though it seems like it should. --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --000000000000eb48e20642632ecb Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, Oct 30, 2025 at 11:55=E2=80=AFAM = Dimitrios Apostolou <jimis@gmx.net&= gt; wrote:
Hello list,

I have a table that is constantly growing, and it's not being
vacuumed/analyzed. I think my problem is rather common, but how to even debug it if "nothing works"?

I've already set log_autovacuum_min_duration =3D 0 but the table is nev= er
mentioned in my logs, grep'ing for "vacuum".

I have run ANALYZE manually once but nothing automatic.
Here is more info:

> SELECT * FROM pg_stat_user_tables WHERE relname =3D
'test_runs_summarized_per_function' \gx
-[ RECORD 1 ]-------+----------------------------------
relid=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 780653
schemaname=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | public
relname=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| test_runs_summariz= ed_per_function
seq_scan=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 32
last_seq_scan=C2=A0 =C2=A0 =C2=A0 =C2=A0| 2025-10-19 10:31:08.289922+00
seq_tup_read=C2=A0 =C2=A0 =C2=A0 =C2=A0 | 26484817584
idx_scan=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 4554128
last_idx_scan=C2=A0 =C2=A0 =C2=A0 =C2=A0| 2025-10-10 22:02:50.987532+00
idx_tup_fetch=C2=A0 =C2=A0 =C2=A0 =C2=A0| 7418587674
n_tup_ins=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 921064234
n_tup_upd=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 0
n_tup_del=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 0
n_tup_hot_upd=C2=A0 =C2=A0 =C2=A0 =C2=A0| 0
n_tup_newpage_upd=C2=A0 =C2=A0| 0
n_live_tup=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 6484485348
n_dead_tup=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 0
n_mod_since_analyze | 423101205
n_ins_since_vacuum=C2=A0 | 921064234
last_vacuum=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
last_autovacuum=C2=A0 =C2=A0 =C2=A0|
last_analyze=C2=A0 =C2=A0 =C2=A0 =C2=A0 | 2025-09-30 18:24:47.550543+00
last_autoanalyze=C2=A0 =C2=A0 |
vacuum_count=C2=A0 =C2=A0 =C2=A0 =C2=A0 | 0
autovacuum_count=C2=A0 =C2=A0 | 0
analyze_count=C2=A0 =C2=A0 =C2=A0 =C2=A0| 1
autoanalyze_count=C2=A0 =C2=A0| 0

> SELECT reltuples FROM pg_class WHERE relname =3D
'test_runs_summarized_per_function' \gx
-[ RECORD 1 ]-----------
reltuples | 6.061923e+09

> SELECT name,setting FROM pg_settings WHERE name ILIKE '%factor%= 9; ;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 name=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | setting
---------------------------------------+---------
=C2=A0 autovacuum_analyze_scale_factor=C2=A0 =C2=A0 =C2=A0 =C2=A0| 0.1
<= /blockquote>

0.1 means 10%.
=C2=A0
=C2=A0 autovacuum_vacuum_insert_scale_factor | 0.2
=C2=A0 autovacuum_vacuum_scale_factor=C2=A0 =C2=A0 =C2=A0 =C2=A0 | 0.2
=C2=A0 recursive_worktable_factor=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = | 10

n_mod_since_analyze=3D423101205
n_live_tup=3D6484485348

n_mod_since_analyz= e/n_live_tup =3D 6.5%
=C2=A0
How can I get more info from postgres on the autovacuum = logic?

I would:
1) manually V= ACUUM ANALYZE the table,
2) drop the three autovacuum_*_scale_fac= tor values down to 0.03 (i.e. 3%),
3) reload the conf file,
=
4) add "(1.0*n_mod_since_analyze/n_live_tup)::decimal(6,3)" = to the pg_stat_user_tables query, and
4) closely monitor pg= _stat_user_tables WHERE relname =3D
'test_runs_summarized_per_func= tion'.

https:/= /www.percona.com/blog/importance-of-postgresql-vacuum-tuning-and-custom-sch= eduled-vacuum-job/ helped me a lot.=C2=A0 It also validated my cron job= that does "manual" ANALYZE & VACUUM on tables that autovacuu= m isn't picking up, even though it seems like it should.

--
Death to <Redacted>, a= nd butter sauce.
Don't boil me, I'm still alive.
&= lt;Redacted> lobster!
--000000000000eb48e20642632ecb--