On Thu, Oct 30, 2025 at 11:55 AM Dimitrios Apostolou <[email protected]> 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!
