postgres-list-table-stats

About

The postgres-list-table-stats tool queries pg_stat_all_tables to provide comprehensive statistics about tables in the database. It calculates useful metrics like index scan ratio and dead row ratio to help identify performance issues and table bloat.

Compatible sources:

• alloydb-postgres
• cloud-sql-postgres
• postgres

The tool returns a JSON array where each element represents statistics for a table, including scan metrics, row counts, and vacuum history. Results are sorted by sequential scans by default and limited to 50 rows.

Example

tools:
  list_table_stats:
    kind: postgres-list-table-stats
    source: postgres-source
    description: "Lists table statistics including size, scans, and bloat metrics."

Example Requests

List default tables in public schema:

{}

Filter by specific table name:

{
  "table_name": "users"
}

Filter by owner and sort by size:

{
  "owner": "app_user",
  "sort_by": "size",
  "limit": 10
}

Find tables with high dead row ratio:

{
  "sort_by": "dead_rows",
  "limit": 20
}

Example Response

[
  {
    "schema_name": "public",
    "table_name": "users",
    "owner": "postgres",
    "total_size_bytes": 8388608,
    "seq_scan": 150,
    "idx_scan": 450,
    "idx_scan_ratio_percent": 75.0,
    "live_rows": 50000,
    "dead_rows": 1200,
    "dead_row_ratio_percent": 2.34,
    "n_tup_ins": 52000,
    "n_tup_upd": 12500,
    "n_tup_del": 800,
    "last_vacuum": "2025-11-27T10:30:00Z",
    "last_autovacuum": "2025-11-27T09:15:00Z",
    "last_autoanalyze": "2025-11-27T09:16:00Z"
  },
  {
    "schema_name": "public",
    "table_name": "orders",
    "owner": "postgres",
    "total_size_bytes": 16777216,
    "seq_scan": 50,
    "idx_scan": 1200,
    "idx_scan_ratio_percent": 96.0,
    "live_rows": 100000,
    "dead_rows": 5000,
    "dead_row_ratio_percent": 4.76,
    "n_tup_ins": 120000,
    "n_tup_upd": 45000,
    "n_tup_del": 15000,
    "last_vacuum": "2025-11-26T14:22:00Z",
    "last_autovacuum": "2025-11-27T02:30:00Z",
    "last_autoanalyze": "2025-11-27T02:31:00Z"
  }
]

Parameters

Output Fields Reference

Interpretation Guide

Index Scan Ratio (idx_scan_ratio_percent)

• High ratio (> 80%): Table queries are efficiently using indexes. This is typically desirable.
• Low ratio (< 20%): Many sequential scans indicate missing indexes or queries that cannot use existing indexes effectively. Consider adding indexes to frequently searched columns.
• 0%: No index scans performed; all queries performed sequential scans. May warrant index investigation.

Dead Row Ratio (dead_row_ratio_percent)

• < 2%: Healthy table with minimal bloat.
• 2-5%: Moderate bloat; consider running VACUUM if not recent.
• > 5%: High bloat; may benefit from manual VACUUM or VACUUM FULL.

Vacuum History

• Null last_vacuum: Table has never been manually vacuumed; relies on autovacuum.
• Recent last_autovacuum: Autovacuum is actively managing the table.
• Stale timestamps: Consider running manual VACUUM and ANALYZE if maintenance windows exist.

Performance Considerations

• Statistics are collected from pg_stat_all_tables, which resets on PostgreSQL restart.
• Run ANALYZE on tables to update statistics for accurate query planning.
• The tool defaults to limiting results to 50 rows; adjust the limit parameter for larger result sets.
• Filtering by schema, table name, or owner uses LIKE pattern matching (supports partial matches).

Use Cases

• Finding ineffective indexes: Identify tables with low idx_scan_ratio_percent to evaluate index strategy.
• Detecting table bloat: Sort by dead_rows to find tables needing VACUUM.
• Monitoring growth: Track total_size_bytes over time for capacity planning.
• Audit maintenance: Check last_autovacuum and last_autoanalyze timestamps to ensure maintenance tasks are running.
• Understanding workload: Examine seq_scan vs idx_scan ratios to understand query patterns.