get_all_tables_stats
Retrieve comprehensive statistics for all PostgreSQL database tables, including access patterns, tuple operations, and maintenance history to analyze usage and identify maintenance needs.
Instructions
[Tool Purpose]: Get comprehensive statistics for all tables (including system tables if requested)
[Exact Functionality]:
Show detailed access statistics for all tables in database
Include sequential scans, index scans, and tuple operations
Provide live/dead tuple estimates and maintenance history
Option to include system catalog tables
[Required Use Cases]:
When user requests "all tables stats", "complete table statistics", etc.
When analyzing overall table usage patterns
When investigating table maintenance needs across the database
When getting comprehensive database activity overview
[Strictly Prohibited Use Cases]:
Requests for table maintenance operations (VACUUM, ANALYZE)
Requests for statistics reset or modification
Requests for table optimization actions
Args: database_name: Database name to analyze (uses default database if omitted) include_system: Include system tables in results (default: False)
Returns: Comprehensive table statistics including access patterns and maintenance history
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| database_name | No | ||
| include_system | No |
Implementation Reference
- Helper function in VersionAwareQueries class that generates a version-compatible SQL query to retrieve statistics for all tables (user tables or including system tables). It handles differences between PostgreSQL versions (e.g., presence of n_ins_since_vacuum column in PG13+). This is likely used by the 'get_all_tables_stats' tool handler.async def get_all_tables_stats_query(include_system: bool = False, database: str = None) -> str: """Get all tables statistics query with version compatibility.""" version = await get_postgresql_version(database) view_name = "pg_stat_all_tables" if include_system else "pg_stat_user_tables" # n_ins_since_vacuum is available from PostgreSQL 13+ if version.has_table_stats_ins_since_vacuum: return f""" SELECT schemaname as schema_name, relname as table_name, seq_scan as sequential_scans, seq_tup_read as seq_tuples_read, idx_scan as index_scans, idx_tup_fetch as idx_tuples_fetched, n_tup_ins as tuples_inserted, n_tup_upd as tuples_updated, n_tup_del as tuples_deleted, n_tup_hot_upd as hot_updates, n_live_tup as estimated_live_tuples, n_dead_tup as estimated_dead_tuples, CASE WHEN n_live_tup > 0 THEN ROUND((n_dead_tup::numeric / n_live_tup) * 100, 2) ELSE 0 END as dead_tuple_ratio_percent, n_mod_since_analyze as modified_since_analyze, n_ins_since_vacuum as inserted_since_vacuum, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze, vacuum_count, autovacuum_count, analyze_count, autoanalyze_count FROM {view_name} ORDER BY seq_scan + COALESCE(idx_scan, 0) DESC, schemaname, relname """ else: # PostgreSQL 12 - without n_ins_since_vacuum return f""" SELECT schemaname as schema_name, relname as table_name, seq_scan as sequential_scans, seq_tup_read as seq_tuples_read, idx_scan as index_scans, idx_tup_fetch as idx_tuples_fetched, n_tup_ins as tuples_inserted, n_tup_upd as tuples_updated, n_tup_del as tuples_deleted, n_tup_hot_upd as hot_updates, n_live_tup as estimated_live_tuples, n_dead_tup as estimated_dead_tuples, CASE WHEN n_live_tup > 0 THEN ROUND((n_dead_tup::numeric / n_live_tup) * 100, 2) ELSE 0 END as dead_tuple_ratio_percent, n_mod_since_analyze as modified_since_analyze, NULL::bigint as inserted_since_vacuum, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze, vacuum_count, autovacuum_count, analyze_count, autoanalyze_count FROM {view_name} ORDER BY seq_scan + COALESCE(idx_scan, 0) DESC, schemaname, relname """