PostgreSQLFlexTableStats — Autovacuum & schema statistics

Per-table activity from pg_stat_user_tables, plus per-collection rollups. Useful for bloat detection and autovacuum tuning.

Kusto query

let server = "my-pg-prd-1";
AzureDiagnostics
| where Category == "PostgreSQLFlexTableStats" and LogicalServerName_s == server
| project DatabaseName_s, Schemaname_s,
          Seq_scan_d, Seq_tup_read_d, Idx_scan_d, Idx_tup_fetch_d,
          N_tup_ins_d, N_tup_upd_d, N_tup_del_d, N_tup_hot_upd_d,
          N_live_tup_d, N_dead_tup_d, N_mod_since_analyze_d,
          Vacuum_count_d, Autovacuum_count_d, Analyze_count_d, Autoanalyze_count_d,
          Tables_counter_d, Tables_vacuumed_d, Tables_autovacuumed_d,
          Tables_analyzed_d, Tables_autoanalyzed_d

Fields

Table identity

• DatabaseName_s (string) — database name.
• Schemaname_s (string) — schema name.

Access patterns

• Seq_scan_d (number) — sequential scans on the table.
• Seq_tup_read_d (number) — live rows fetched by sequential scans.
• Idx_scan_d (number) — index scans.
• Idx_tup_fetch_d (number) — live rows fetched by index scans.

Row churn

• N_tup_ins_d / N_tup_upd_d / N_tup_del_d (number) — rows inserted / updated / deleted.
• N_tup_hot_upd_d (number) — HOT (heap-only tuple) updates.
• N_live_tup_d / N_dead_tup_d (number) — estimated live / dead tuples.
• N_mod_since_analyze_d (number) — rows changed since the last analyze.

Maintenance counters (per table)

• Vacuum_count_d / Autovacuum_count_d (number) — manual / automatic vacuums.
• Analyze_count_d / Autoanalyze_count_d (number) — manual / automatic analyzes.

Collection rollups (per snapshot, across all tables)

• Tables_counter_d (number) — number of tables in the snapshot.
• Tables_vacuumed_d / Tables_autovacuumed_d (number) — tables vacuumed manually / automatically.
• Tables_analyzed_d / Tables_autoanalyzed_d (number) — tables analyzed manually / automatically.

Use cases — troubleshooting

• Table bloat — high N_dead_tup_d relative to N_live_tup_d signals bloat hurting performance/storage.
• Autovacuum not keeping up — growing N_dead_tup_d with stale/zero Autovacuum_count_d, or low Tables_autovacuumed_d per cycle.
• Stale statistics → bad plans — high N_mod_since_analyze_d with old Autoanalyze_count_d means the planner is using stale stats.
• Missing indexes / full scans — high Seq_scan_d and Seq_tup_read_d vs low Idx_scan_d on big tables.
• Unused indexes — Idx_scan_d near zero (candidate to drop).
• Write-heavy hotspots — large N_tup_ins_d/N_tup_upd_d/N_tup_del_d; low N_tup_hot_upd_d ratio hints at poor HOT-update efficiency (fillfactor/index churn).
• Vacuum coverage — verify whether maintenance is actually touching the tables that need it.