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

TableJSON Schema

Name	Required	Description	Default
`database_name`	No
`include_system`	No

Implementation Reference

src/mcp_postgresql_ops/version_compat.py:403-476 (helper)

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
        """

MCP PostgreSQL Operations