pg_profile: Vacuum related statistics

March 17, 2024

Summary: This report section contains top tables and indexes in cluster, related to vacuum activities.

This section contains the following subsections:

  1. Top tables by vacuum operations
  2. Top tables by analyze operations
  3. Top indexes by estimated vacuum load
  4. Top tables by dead tuples ratio
  5. Top tables by modified tuples ratio

Top tables by vacuum operations

Top tables sorted by vacuums (manual and automatic) processed

  • DB - database name of the table
  • Tablespace - tablespace name, where the table is located
  • Schema - schema name of the table
  • Table - table name
  • Vacuum count - number of times this table has been manually vacuumed (not counting VACUUM FULL) (vacuum_count field)
  • Autovacuum count - number of times this table has been vacuumed by the autovacuum daemon (autovacuum_count field)
  • Ins - number of rows inserted (n_tup_ins field)
  • Upd - number of rows updated (including HOT) (n_tup_upd field)
  • Del - number of rows deleted (n_tup_del field)
  • Upd(HOT) - number of rows HOT updated (n_tup_hot_upd field)

Top tables by analyze operations

Top tables sorted by analyze run (manual and automatic) count

  • DB - database name of the table
  • Tablespace - tablespace name, where the table is located
  • Schema - schema name of the table
  • Table - table name
  • Analyze count - number of times this table has been manually analyzed (analyze_count field)
  • Autoanalyze count - number of times this table has been analyzed by the autovacuum daemon (autoanalyze_count field)
  • Ins - number of rows inserted (n_tup_ins field)
  • Upd - number of rows updated (including HOT) (n_tup_upd field)
  • Del - number of rows deleted (n_tup_del field)
  • Upd(HOT) - number of rows HOT updated (n_tup_hot_upd field)

Top indexes by estimated vacuum load

This table provides estimation of implicit vacuum load caused by table indexes. Here is top indexes sorted by count of vacuums performed on underlying table multiplied by index size. When there is no relation sizes collected with pg_relation_size(), estimation will be calculated based on pg_class.relpages field. This will be less accurate, so such values will be provided in square brackets.

  • DB - database name of the index
  • Tablespace - tablespace name, where the index is located
  • Schema - schema name of the index
  • Table - table name
  • Index - index name
  • ~Vacuum bytes - vacuum load estimation calculated as (vacuum_count + autovacuum_count) * index_size
  • Vacuum cnt - number of times this table has been manually vacuumed (not counting VACUUM FULL) (vacuum_count field)
  • Autovacuum cnt - number of times this table has been vacuumed by the autovacuum daemon (autovacuum_count field)
  • IX size - average index size during report interval
  • Relsize - average relation size during report interval

Top tables by dead tuples ratio

This section contains modified tables with last vacuum run. Statistics is valid for last sample in report interval. Based on pg_stat_all_tables view. When there is no relation sizes collected with pg_relation_size(), size will be provided from pg_class.relpages field. This will be less accurate, so such values will be provided in square brackets.

Top tables, sized 5 MB and more, sorted by dead tuples ratio.

  • DB - database name of the table
  • Schema - schema name of the table
  • Table - table name
  • Live - estimated number of live rows (n_live_tup)
  • Dead - estimated number of dead rows (n_dead_tup)
  • %Dead - dead rows of the table as a percentage of all rows in the table
  • Last AV - last time when this table was vacuumed by the autovacuum daemon (last_autovacuum)
  • Size - table size, as it was at the moment of last report sample.

Top tables by modified tuples ratio

This section contains modified tables with last vacuum run. Statistics is valid for last sample in report interval. Based on pg_stat_all_tables view. When there is no relation sizes collected with pg_relation_size(), size will be provided from pg_class.relpages field. This will be less accurate, so such values will be provided in square brackets.

Top tables, sized 5 MB and more, sorted by modified tuples ratio.

  • DB - database name of the table
  • Schema - schema name of the table
  • Table - table name
  • Live - estimated number of live rows (n_live_tup)
  • Dead - estimated number of dead rows (n_dead_tup)
  • Mod - estimated number of rows modified since this table was last analyzed (n_mod_since_analyze)
  • %Mod - modified rows of the table as a percentage of all rows in the table
  • Last AA - last time when this table was analyzed by the autovacuum daemon
  • Size - table size, as it was at the moment of last report sample.

See more

pg_profile: PostgreSQL historical workload reports

comments powered by Disqus