Thread
-
Why isn't my table auto-analyzed/vacuumed?
Dimitrios Apostolou <jimis@gmx.net> — 2025-10-30T15:55:18Z
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 = 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 = '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 = '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 autovacuum_vacuum_insert_scale_factor | 0.2 autovacuum_vacuum_scale_factor | 0.2 recursive_worktable_factor | 10 How can I get more info from postgres on the autovacuum logic? Thank you in advance, Dimitris -
Re: Why isn't my table auto-analyzed/vacuumed?
Ron <ronljohnsonjr@gmail.com> — 2025-10-30T17:00:05Z
On Thu, Oct 30, 2025 at 11:55 AM Dimitrios Apostolou <jimis@gmx.net> 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 = 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 = > '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 = > '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=423101205 n_live_tup=6484485348 n_mod_since_analyze/n_live_tup = 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 = 'test_runs_summarized_per_function'. https://www.percona.com/blog/importance-of-postgresql-vacuum-tuning-and-custom-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. -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster!
-
Re: Why isn't my table auto-analyzed/vacuumed?
Dimitrios Apostolou <jimis@gmx.net> — 2025-10-30T18:41:39Z
On Thursday 2025-10-30 18:00, Ron Johnson wrote: >On Thu, Oct 30, 2025 at 11:55 AM Dimitrios Apostolou <jimis@gmx.net> wrote: > > SELECT name,setting FROM pg_settings WHERE name ILIKE '%factor%' ; > name | setting > ---------------------------------------+--------- > autovacuum_analyze_scale_factor | 0.1 > > >0.1 means 10%. Thank you Ron! I was quite sure 0.1 meant 1/1000 because of this: SELECT min_val,max_val FROM pg_settings WHERE name = 'autovacuum_analyze_scale_factor' \gx -[ RECORD 1 ] min_val | 0 max_val | 100 I will adjust the values accordingly and check back. Cheers! Dimitris
-
Re: Why isn't my table auto-analyzed/vacuumed?
Ron <ronljohnsonjr@gmail.com> — 2025-10-30T18:46:17Z
On Thu, Oct 30, 2025 at 2:41 PM Dimitrios Apostolou <jimis@gmx.net> wrote: > On Thursday 2025-10-30 18:00, Ron Johnson wrote: > > >On Thu, Oct 30, 2025 at 11:55 AM Dimitrios Apostolou <jimis@gmx.net> > wrote: > > > > SELECT name,setting FROM pg_settings WHERE name ILIKE '%factor%' ; > > name | setting > > ---------------------------------------+--------- > > autovacuum_analyze_scale_factor | 0.1 > > > > > >0.1 means 10%. > > Thank you Ron! I was quite sure 0.1 meant 1/1000 because of this: > > SELECT min_val,max_val FROM pg_settings WHERE name = > 'autovacuum_analyze_scale_factor' \gx > > -[ RECORD 1 ] > min_val | 0 > max_val | 100 > https://www.postgresql.org/docs/17/runtime-config-autovacuum.html#GUC-AUTOVACUUM-ANALYZE-SCALE-FACTOR "Specifies a fraction of the table size to add to autovacuum_analyze_threshold when deciding whether to trigger an ANALYZE. The default is 0.1 (10% of table size)." -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster!
-
Re: Why isn't my table auto-analyzed/vacuumed?
DINESH NAIR <dinesh_nair@iitmpravartak.net> — 2025-10-31T06:38:54Z
Hi, Since the autovacuum decides whether to vacuum or analyze a table based on thresholds .As the threshold to trigger autovacuum is high so it might not have been triggered . vacuum_trigger_threshold = autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor × n_live_tup) You may check below mentioned parameters: autovacuum = on autovacuum_vacuum_threshold = 50 # min number of row updates before vacuum autovacuum_vacuum_scale_factor = 0.01 # vacuum triggers at 1% autovacuum_naptime = X # time between autovacuum runs Thanks & Regards Dinesh Nair ________________________________ From: Dimitrios Apostolou <jimis@gmx.net> Sent: Thursday, October 30, 2025 9:25 PM To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org> Subject: Why isn't my table auto-analyzed/vacuumed? Caution: This email was sent from an external source. Please verify the sender’s identity before clicking links or opening attachments. 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 = 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 = '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 = '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 autovacuum_vacuum_insert_scale_factor | 0.2 autovacuum_vacuum_scale_factor | 0.2 recursive_worktable_factor | 10 How can I get more info from postgres on the autovacuum logic? Thank you in advance, Dimitris -
Re: Why isn't my table auto-analyzed/vacuumed?
Dimitrios Apostolou <jimis@gmx.net> — 2025-10-31T20:03:39Z
On Thursday 2025-10-30 18:00, Ron Johnson wrote: > > > SELECT reltuples FROM pg_class WHERE relname = > '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=423101205 >n_live_tup=6484485348 > >n_mod_since_analyze/n_live_tup = 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%), Reporting back, after reducing the values, the table has been picked up for both autovacuum and analyze. Thank you for the immediate feedback! Since I had spent some time looking into these values and was "certain" that they were % while they are apparently *not*, I'm wondering if max_val=100 is there because of historical reasons, and if it would make sense to change it to 1. Dimitris
-
Re: Why isn't my table auto-analyzed/vacuumed?
Adrian Klaver <adrian.klaver@aklaver.com> — 2025-10-31T20:52:05Z
On 10/31/25 13:03, Dimitrios Apostolou wrote: > On Thursday 2025-10-30 18:00, Ron Johnson wrote: > >> >> > SELECT reltuples FROM pg_class WHERE relname = >> '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=423101205 >> n_live_tup=6484485348 >> >> n_mod_since_analyze/n_live_tup = 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%), > > Reporting back, after reducing the values, the table has been picked up > for both autovacuum and analyze. Thank you for the immediate feedback! > > Since I had spent some time looking into these values and was "certain" > that they were % while they are apparently *not*, I'm wondering if > max_val=100 is there because of historical reasons, and if it would make > sense to change it to 1. But they are: 0.1/1 is 10% as is 10/100. > > > Dimitris -- Adrian Klaver adrian.klaver@aklaver.com
-
Re: Why isn't my table auto-analyzed/vacuumed?
Ron <ronljohnsonjr@gmail.com> — 2025-10-31T21:16:59Z
On Fri, Oct 31, 2025 at 4:52 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote: > On 10/31/25 13:03, Dimitrios Apostolou wrote: > > On Thursday 2025-10-30 18:00, Ron Johnson wrote: > > > >> > >> > SELECT reltuples FROM pg_class WHERE relname = > >> '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=423101205 > >> n_live_tup=6484485348 > >> > >> n_mod_since_analyze/n_live_tup = 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%), > > > > Reporting back, after reducing the values, the table has been picked up > > for both autovacuum and analyze. Thank you for the immediate feedback! > > > > Since I had spent some time looking into these values and was "certain" > > that they were % while they are apparently *not*, I'm wondering if > > max_val=100 is there because of historical reasons, and if it would make > > sense to change it to 1. > > But they are: > > 0.1/1 is 10% as is 10/100. > And 0.1/100 = 0.1%. Dimitrios is right: it's misleading to have a default of 0.1 that means 10%, but also have the max value be 100 because 10 is 10% of 100. https://www.postgresql.org/docs/17/runtime-config-autovacuum.html#GUC-AUTOVACUUM-ANALYZE-SCALE-FACTOR certainly doesn't mention that you can use either reals (0,1] or integers (0,100]. -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster!