Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
For information on using these queries in the Azure portal, see Log Analytics tutorial. For the REST API, see Query.
Bloat ratio
Summary of the current bloat ratio per database.
// If required to get the snapshot on a certain date and time just filter on TimeGenerated column, like:
// where TimeGenerated between (datetime('2023-03-01 16:00') .. datetime('2023-03-01 19:00'))
PGSQLAutovacuumStats
| extend ts=bin(TimeGenerated,5m)
| summarize arg_max(ts,LiveRowsCount,DeadRowsCount) by DatabaseName, SchemaName
| where DatabaseName !in ('azure_maintenance', 'azure_sys') //exclude system database
| where SchemaName !in ('pg_catalog','information_schema', 'pg_toast') //exclude system schemas
| summarize TotalLiveRows=sum(LiveRowsCount), TotalDeadRows=sum(DeadRowsCount) by DatabaseName
| extend BloatRatio=toreal(TotalDeadRows)/toreal(TotalLiveRows)*100
| extend BloatRatio = iff( isnan(BloatRatio), 0.0, BloatRatio)
| project DatabaseName,TotalLiveRows,TotalDeadRows, BloatRatio
| order by BloatRatio desc
Vacuum statistics
Summary of total tables that were vacuumed per database.
// If required to get the snapshot on a certain date and time just filter on TimeGenerated column, like:
// where TimeGenerated between (datetime('2023-03-01 16:00') .. datetime('2023-03-01 19:00'))
PGSQLAutovacuumStats
| extend ts=bin(TimeGenerated,5m)
| summarize arg_max(ts,TablesCount,TablesVacuumedCount,TablesAutovacuumedCount) by DatabaseName, SchemaName
| where DatabaseName !in ('azure_maintenance', 'azure_sys') //exclude system database
| where SchemaName !in ('pg_catalog','information_schema', 'pg_toast') //exclude system schemas
| summarize TotalTables=sum(TablesCount), TablesVaccumed=sum(TablesVacuumedCount), TablesAutoVaccumed=sum(TablesAutovacuumedCount) by DatabaseName
| order by TotalTables desc
Analyze statistics
Summary of total tables that were analyzed per database.
// If required to get the snapshot on a certain date and time just filter on TimeGenerated column, like:
// where TimeGenerated between (datetime('2023-03-01 16:00') .. datetime('2023-03-01 19:00'))
PGSQLAutovacuumStats
| extend ts=bin(TimeGenerated,5m)
| summarize arg_max(ts,TablesCount,TablesAnalyzedCount,TablesAutoanalyzedCount) by DatabaseName, SchemaName
| where DatabaseName !in ('azure_maintenance', 'azure_sys') //exclude system database
| where SchemaName !in ('pg_catalog','information_schema', 'pg_toast') //exclude system schemas
| summarize TotalTables=sum(TablesCount), TablesAnalyzed=sum(TablesAnalyzedCount), TablesAutoAnalyzed=sum(TablesAutoanalyzedCount) by DatabaseName
| order by TotalTables desc