Hi all, While investigating some potential vacuum improvements to make to a table I happened to notice that one table (along with others) will suddenly increase the number of n_dead_tup reported in pg_stat_user_tables without a corresponding increase in the inserts, updates, or deletes.
For instance, running this query in a 1 second loop select * from pg_stat_user_tables where relname = 'casino_account_history_lines'; I can see the n_dead_tup column increases until which time the autovacuum process finishes vacuuming the table. Example: -[ RECORD 1 ]-------+------------------------------ relid | 33378 schemaname | public relname | casino_account_history_lines seq_scan | 1122 seq_tup_read | 178229588443 idx_scan | 456779105 idx_tup_fetch | 5539267637 n_tup_ins | 45093031 n_tup_upd | 47289203 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 1646966715 n_dead_tup | 1356331 n_mod_since_analyze | 11498 n_ins_since_vacuum | 6288 last_vacuum | 2025-06-03 14:57:43.46009+00 last_autovacuum | 2025-06-03 19:09:21.595322+00 last_analyze | 2025-06-03 14:57:54.848185+00 last_autoanalyze | 2025-06-03 19:09:48.390396+00 vacuum_count | 2 autovacuum_count | 3973 analyze_count | 6 autoanalyze_count | 3078 -- -- At this point the table is no longer in pg_stat_progress_vacuum and `n_dead_tup` has dropped from 1356331 to 4302 -- -[ RECORD 1 ]-------+------------------------------ relid | 33378 schemaname | public relname | casino_account_history_lines seq_scan | 1122 seq_tup_read | 178229588443 idx_scan | 456779364 idx_tup_fetch | 5539267804 n_tup_ins | 45093063 n_tup_upd | 47289232 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 1646961282 n_dead_tup | 4302 n_mod_since_analyze | 11559 n_ins_since_vacuum | 2 last_vacuum | 2025-06-03 14:57:43.46009+00 last_autovacuum | 2025-06-03 19:12:48.107816+00 last_analyze | 2025-06-03 14:57:54.848185+00 last_autoanalyze | 2025-06-03 19:09:48.390396+00 vacuum_count | 2 autovacuum_count | 3974 analyze_count | 6 autoanalyze_count | 3078 -- This seems normal to me, however, while still looking at pg_stat_user_tables in a loop, `n_dead_tup` steadily increases to, in this latest run, `5038` at which point, one second later the number jumps to above 1.2 million: Tue 03 Jun 2025 07:13:11 PM UTC (every 1s) -[ RECORD 1 ]-------+------------------------------ relid | 33378 schemaname | public relname | casino_account_history_lines seq_scan | 1122 seq_tup_read | 178229588443 idx_scan | 456784246 idx_tup_fetch | 5539271612 n_tup_ins | 45093719 n_tup_upd | 47289968 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 1646961938 n_dead_tup | 5038 n_mod_since_analyze | 12951 n_ins_since_vacuum | 658 last_vacuum | 2025-06-03 14:57:43.46009+00 last_autovacuum | 2025-06-03 19:12:48.107816+00 last_analyze | 2025-06-03 14:57:54.848185+00 last_autoanalyze | 2025-06-03 19:09:48.390396+00 vacuum_count | 2 autovacuum_count | 3974 analyze_count | 6 autoanalyze_count | 3078 Tue 03 Jun 2025 07:13:12 PM UTC (every 1s) -[ RECORD 1 ]-------+------------------------------ relid | 33378 schemaname | public relname | casino_account_history_lines seq_scan | 1122 seq_tup_read | 178229588443 idx_scan | 456784464 idx_tup_fetch | 5539271752 n_tup_ins | 45093746 n_tup_upd | 47289993 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 1647255972 n_dead_tup | 1290579 n_mod_since_analyze | 2 n_ins_since_vacuum | 685 last_vacuum | 2025-06-03 14:57:43.46009+00 last_autovacuum | 2025-06-03 19:12:48.107816+00 last_analyze | 2025-06-03 14:57:54.848185+00 last_autoanalyze | 2025-06-03 19:13:12.125828+00 vacuum_count | 2 autovacuum_count | 3974 analyze_count | 6 autoanalyze_count | 3079 I don't understand where this large increase is coming from when there are no corresponding inserts, updates, or deletes (at the magnitude). This entire process repeats itself and, as mentioned, the same thing is happening on other observed tables. I'm running version 'PostgreSQL 15.6 (Ubuntu 15.6-1.pgdg22.04+1)' Thanks, Matt
